Download to Excel from SQL stored procedure

TheMSsForum.com: The Microsoft Software Forum

The MSS Forum ‹ Ado
- Archive
  - Biz
  - MCSE
  - CRM
  - Drivers
  - Framework
  - ADO
  - ASP
  - Compact
  - Forms
  - Dotnet
  - C#
  - VB
  - FontpageGen
  - Excel
  - WorkSheet
  - Exchange
  - Setup
  - Fox
  - Fontpage
  - ASP
  - IIS
  - Entourage
  - Money
  - Messanger
  - PocketPC
  - Powerpoint
  - Project
  - Publisher
  - Excel
  - VB
  - Security
  - Portal
  - Services
  - SQLServerDev
  - SVCS
  - SQLServer
  - VB
  - VC
  - MFC
  - ExcelGen

- Previous
  - 1
    - Persistent DataSet data entry problem Hi Everyone: This problem has been plaguing me for three days, and I canâ??t for the life of me find a logical reason why this problem occurs. My problem occurs when I try to write data into a dataset that contains nothing more than the table schema. This subroutine is supposed to write a new record into the dataset. Private Sub New_Bindings(ByVal intOrdinal As Integer, _ ByVal strCode As String, _ ByVal strName As String) tblBindings = MyDataSet.Tables("tblBindings") drCurrent = tblBindings.NewRow() drCurrent("Ordinal") = intOrdinal drCurrent("BindingCode") = strCode drCurrent("BindingName") = strName tblBindings.Rows.Add(drCurrent) 'Call Clear_Form() End Sub I write new data into the dataset when this subroutine is activated. It is activated when the Private Sub BindingNavigatorAddNewItem_Click event is triggered. The AddNew Button on the BindingNavigator control on the form. Private Sub BindingNavigatorAddNewItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles BindingNavigatorAddNewItem.Click Call New_Bindings(1, "SDLS", "Saddle-stitch") Call New_Bindings(2, "SPRL", "Spiral Bound") Call New_Bindings(3, "STUL", "Staple Upper Left") Call New_Bindings(4, "STUR", "Staple Upper Right") Call New_Bindings(5, "ST2L", "Two Staples on left side") End Sub The datasetâ??s data should read like this: BindingID â?? 1 Ordinal â?? 1 Code â?? SDLS Name â?? Saddle-stitch BindingID â?? 2 Ordinal â?? 2 Code â?? SPRL Name â?? Spiral Bound BindingID â?? 3 Ordinal â?? 3 Code â?? STUL Name â?? Staple Upper Left BindingID â?? 4 Ordinal â?? 4 Code â?? STUP Name â?? Staple Upper Right BindingID â?? 5 Ordinal â?? 5 Code â?? ST2l Name â?? Two Staples on Left Side What I am actually getting is: BindingID â?? 0 Ordinal â?? 1 Code â?? SDLS Name â?? Saddle-stitch BindingID â?? 1 Ordinal â?? 2 Code â?? SPRL Name â?? Spiral Bound BindingID â?? 2 Ordinal â?? 3 Code â?? STUL Name â?? Staple Upper Left BindingID â?? 3 Ordinal â?? 4 Code â?? STUP Name â?? Staple Upper Right BindingID â?? 4 Ordinal â?? 5 Code â?? ST2l Name â?? Two Staples on Left Side For some reason when the first record is written out to the dataset the auto increment field (BindingID) is not incremented to one when the record is written. It has an ID of â??0â?? which logically canâ??t happen when the column has been defined to be an auto increment starting at one and incrementing by one every time a new record is added. Can anyone tell me why this is happening and what I need to do to correct it? Thanks, -- Mark Tag: Download to Excel from SQL stored procedure Tag: 139563
  - 2
    - Event not firing (Sorry about multi-posting this to "microsoft.public.dotnet.languages.csharp") Hi there, Does anyone know why the "ColumnChanged" event isn't fired when "Merge()" is called below. "OnRowChanged()" is fired however: Thanks in advance. private static void Test() { DataSet ds1 = new DataSet(); DataTable dt1 = ds1.Tables.Add("MyTable"); DataColumn primaryKeyColumn = dt1.Columns.Add("Key", typeof(int)); dt1.PrimaryKey = new DataColumn[] { primaryKeyColumn }; dt1.Columns.Add("Name", typeof(string)); DataRow dr1 = dt1.Rows.Add(1, "John"); ds1.AcceptChanges(); DataSet ds2 = ds1.Copy(); ds2.Tables["MyTable"].Rows[0]["Name"] = "David"; dt1.RowChanged += OnRowChanged; dt1.ColumnChanged += OnColumnChanged; ds1.Merge(ds2); } private static void OnRowChanged(object sender, DataRowChangeEventArgs e) { } private static void OnColumnChanged(object sender, DataColumnChangeEventArgs e) { } Tag: Download to Excel from SQL stored procedure Tag: 139558
  - 3
    - TreeView I have a table that I would like to display in a tree view. The table has parent/child relationships (there is a primary key, and another field that points to the parent's primary key). This table is in an SQL Server database (2005). The table is essentially an organization structure (CEO who has senior managers, who then have department managers, who have section managers, etc). How do I get this structure into a tree view? Thanks. Tag: Download to Excel from SQL stored procedure Tag: 139557
  - 4
    - Field data type in VB.net How can we find a Field data types from DataReader/Dataset/Datatable in VB.net ??? For example code in VB6 where rstRecName is a recordset and j is a column index: If rstRecName.Fields(j).type = adLongVarChar Then (do something) end if Thanks for your help Vanny Tag: Download to Excel from SQL stored procedure Tag: 139550
  - 5
    - DBConcurrencyException Not Thrown For INSERT Command In ADO.NET 2.0, it seems that when the TableAdapter (or its underlying DataAdapter even) doesn't throw a DBConcurrencyException when the InsertCommand SPROC returns Zero Rows Affected. The MSDN Documentation Says: "The DataAdapter examines the number of rows affected by the execution of each insert, update, or delete operation, and throws this exception if the number equals zero. This exception is generally caused by a concurrency violation." Which rather insinuates that it should! All I've done is manually created the CRUD sprocs for a table with optimistic concurrency (using the SQL TimeStamp Method). I added an additional feature to my Add SPROC - which has an exists check on the INSERT statement to verify that the row to be inserted doesn't already exist (i.e. a Concurrency violation). In that case the INSERT statement would return 0 rows affected and hence I would expect it to raise a DBConcurrencyException from the TableAdapter's Update Method. However, this is what actually happens if I try and add a row that already exists in the table: - The DataAdapter returns 0 Rows Affected (I can check this by creating a custom partial class for the tableadapter and catching the '_adapter_RowUpdated' event) - The DataRow's RowState remains 'Added' - The TableAdapter returns 0 - The Row's Error property is not set (and HasErrors returns false) - No DBConcurrencyException is thrown The first thought is that there was something wrong with the SPROC, so it was correct and also that is contained SET NOCOUNT OFF. To further test what was going on, I inverted over the Add and Modify SPROCs in the TableAdapters UPDATE and INSERT commands, and then tried calling the Add SPROC (by modifying an existing row) and this time it correctly raised the DBConcurrencyException. However, this time Modify SPROC did not raise the event when I executed it by saving a new DataRow to the Database (it should have done because it would be trying to modify a row that didn't exist as the key was new). So it would seem from this that the DataAdapter's InsertCommand does not Raise a DBConcurrencyException when the SQL Command returns 0 Rows Affected I have already installed the MS Hotfix 916002: http://support.microsoft.com/kb/915880/en-us?spid=8291&sid=200 And that doesn't help, but I wouldn't expect it to as I'm only doing single batch updates. Please, does anybody have any ideas where I can go from here? (I can easily provide example code if that would help.) Tag: Download to Excel from SQL stored procedure Tag: 139548
  - 6
    - SqlDataAdapter, SqlConnection, SqlCommand - Thread Safety Hello, I'm using a lot of threads, each one using it's own instance of my TableAdapter, Connection and DataTables. But all of them will access the same table at the same sql server maybe at the same time (optimistic concurrency, etc.) I found the following in the Documentation: Any public static (Shared in Visual Basic) members of this type are thread safe. Any instance members are not guaranteed to be thread safe. Well, what does this mean exactly? As I understand, this may not be any problem for me. Only, if I would use e.g. the same instance of a SqlDataAdapter in more than one threads (what I'm doing not) I may run into problems. Is this correct? Thanks in advance, Robert Witter Tag: Download to Excel from SQL stored procedure Tag: 139547
  - 7
    - SqlCommandBuilder and optimistic concurrency Hey ya, Using .NET 2.0 and SqlCommandBuilder, say I have a customer row, with an address and name fields. I need 2 different clients to be able to change 1 field each and not get ConcurencyExeption on 2nd Update(). Is it possible to do? Thanks Tag: Download to Excel from SQL stored procedure Tag: 139544
  - 8
    - SQLHelper ExecuteReader not closing connections Hi There, I am using the SQLHelper class and have implemented runsp, executescalar, executedataset, and executereader. I am using connection pooling between IIS and SQL Server. All these clases except for ExecuteReader are re-using connections properly, ExecuteReader for some reason keeps creating new connections as it is not closing the previous one properly. Any ideas? I do issue a datareader.close command. But as I am using SQLHelper in a data layer, I am passing the datareader back to the business layer but I can't close the connection at this point as I wont be able to use my reader. Is the reader.close suppose to close the connection or just the reader? How can I close the connection outside the data layer. Any help much appreciated. Tag: Download to Excel from SQL stored procedure Tag: 139540
  - 9
    - ANN: .NET Provider for XML For those who are interested, I've just released a trial version of a .NET Provider for XML. Based on the successful, QueryADataset SQL engine for Datasets and XML documents, this assembly supports the standard .NET provider classes: XmlClientFactory - standard factory classes XmlConnection - connect to a XML file or Dataset XmlConnectionStringBuilder - standard connection string builder XmlCommand - issue SQL commands against a Dataset or XML file, supports all behaviors XmlCommandBuilder - build matching INSERT, UPDATE and DELETE commands for a given SELECT statement XmlTransaction - support a single transaction, commit and rollback XmlParameterCollection - standard parmeter collection XmlParameter - standard parameter XmlDataReader - standard data reader with full schema support XmlDataAdapter - standard data adapter Parameterized SQL statements are over 3000 times faster than inline statements with approx. 25000 statements / second. The underlying query engine supports standard SELECT/UNION, INSERT, UPDATE, DELETE, TRUNCATE, CREATE and DROP commands. Multiple statements can be issued together using ';' to seperate them. The SELECT statement supports all join types, sub-queries, group by, having, order by, built-in and aggregate functions and PIVOT/UNPIVOT table sources. If you are interested, you can download the trial version from http://www.queryadataset.com. Thanks Adrian Moore Microsoft MVP - Windows Networking http://www.queryadataset.com Tag: Download to Excel from SQL stored procedure Tag: 139538
  - 10
    - Problem in reteriving Data from database Hi EveryBody: I made web site using asp.net 2.0 Vb.Net. The project depends on database in the local machine. The web site has â?¢ Create User Wizard and â?¢ Login form When the user is created the username and password saved in the database. And the code is as follows: Protected Sub CreateUserWizard1_CreatedUser(ByVal sender As Object, ByVal e As System.EventArgs) Handles CreateUserWizard1.CreatedUser Dim husam As String = CreateUserWizard1.UserName Dim password As String = CreateUserWizard1.Password Dim cmd As SqlCommand Dim connectionString As String = "Integrated Security=SSPI;" + "Initial Catalog=Husam;" + "Data Source=localhost;" Dim connection As New SqlConnection(connectionString) Dim sql As String Try connection.Open() sql = "INSERT INTO husam_Tab(user_Name,user_Password)" + "Values('" & husam & "','" & password & "')" cmd = New SqlCommand(sql, connection) cmd.ExecuteNonQuery() connection.Close() Catch ex As Exception End Try End Sub But I face problem when I retrieve the username and password from the database to perform the authentication in the login form. The login form retrieves the data saved in the App_Data File in the solution not from the database. The code for retrieving data from database as follows: Protected Sub Login1_Authenticate(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.AuthenticateEventArgs) Handles Login1.Authenticate Dim husam As String = Login1.UserName Dim password As String = Login1.Password Dim cmd As SqlCommand Dim connectionString As String = "Integrated Security=SSPI;" + "Initial Catalog=Husam;" + "Data Source=localhost;" Dim connection As New SqlConnection(connectionString) Dim sql As String Dim rawan As New List(Of String) Dim rawan1 As New List(Of String) Try connection.Open() sql = "SELECT user_Name,user_Password FROM husam_Tab" cmd = New SqlCommand(sql, connection) cmd.ExecuteNonQuery() Dim myReader As SqlDataReader = cmd.ExecuteReader() While (myReader.Read()) rawan.Add(myReader.GetString(0)) rawan1.Add(myReader.GetString(1)) End While myReader.Close() connection.Close() If rawan.Contains(husam) Then If rawan1.Contains(password) Then 'Login1.DestinationPageUrl = "" Else Login1.FailureText = "Your password is not correct" Login1.FailureAction = LoginFailureAction.Refresh End If Else MsgBox("Your user name is not correct") End If Catch ex As Exception End Try End Sub So any help or redirection to make the authentication from the database not from the App_Data will be appreciated. Regardâ??s Husam Tag: Download to Excel from SQL stored procedure Tag: 139532
  - 11
    - ASP.NET & MySQL I want to use asp.net & mysql. I am trying to download mysql db and tools from www.mysql.com but i can find everything what I need. Can somebody tell me where I can download DB, Tools, OleDB provider and everything what I need for use mysql & asp.net 2.0? BTW, what is better, use MySQLConnection, MySQLDataAdapter, MySQLDataset etc... or OleDB objects (install oledb provider for mysql)? Tag: Download to Excel from SQL stored procedure Tag: 139530
  - 12
    - How do I tell if the Oracle client is installed Hi; The class OracleConnection is part of .NET and so is always available. However, it does not work if the Oracle client software is not installed on a user's system. How can I test for that being installed so I don't offer Oracle as a choice if it can't be used? -- thanks - dave david_at_windward_dot_net http://www.windwardreports.com Cubicle Wars - http://www.windwardreports.com/film.htm Tag: Download to Excel from SQL stored procedure Tag: 139525
  - 13
    - AccessDataSource not working I am using MS Visual Web Developer 2005 Express Edition, MS Access DB, XP Pro and IIS 5.1 on my local XP Pro. Why does this work: <asp:DropDownList ID="cmbcboFiltLanguageID" Style="z-index: 201; left: 640px; position: absolute; top: 155px" runat="server" Width="377px" Height="20px" BackColor="8454016" ForeColor="0" AutoPostBack="true" DataSourceID="AccessDataSource4" DataTextField="FilterLanguageID" OnSelectedIndexChanged="cmbcboFiltLanguageID_SelectedIndexChanged" AppendDataBoundItems="True"> <asp:ListItem Value="-1">-- Choose a Language Name --</asp:ListItem> </asp:DropDownList> <asp:AccessDataSource ID="AccessDataSource4" runat="server" DataFile="~/App_Data/pub_db_4_be.mdb" SelectCommand="SELECT LanguageName & EthnologueCode & EthnologueCodesOld & LanguageID AS FilterLanguageID FROM Languages WHERE (LanguageName <> '' AND LanguageName IS NOT NULL AND LanguageNameAlt <> '' AND LanguageNameAlt IS NOT NULL AND EthnologueCode <> '' AND EthnologueCode IS NOT NULL AND EthnologueCodesOld <> '' AND EthnologueCodesOld IS NOT NULL) ORDER BY LanguageName"> </asp:AccessDataSource> <asp:DropDownList ID="cmbcboFiltEthCode" Style="z-index: 101; left: 641px; position: absolute; top: 202px" runat="server" Width="376px" Height="20px" BackColor="8454016" ForeColor="black" AutoPostBack="true" DataSourceID="AccessDataSource3" DataTextField="FilterEthCode" OnSelectedIndexChanged="cmbcboFiltEthCode_SelectedIndexChanged" AppendDataBoundItems="True"> <asp:ListItem Value="-1">-- Choose an Ethnologue Code --</asp:ListItem> </asp:DropDownList> <asp:AccessDataSource ID="AccessDataSource3" runat="server" DataFile="~/App_Data/pub_db_4_be.mdb"> </asp:AccessDataSource> and this: <asp:DropDownList ID="cmbcboFiltLanguageID" Style="z-index: 201; left: 640px; position: absolute; top: 155px" runat="server" Width="377px" Height="20px" BackColor="8454016" ForeColor="0" AutoPostBack="true" DataSourceID="AccessDataSource4" DataTextField="FilterLanguageID" OnSelectedIndexChanged="cmbcboFiltLanguageID_SelectedIndexChanged" AppendDataBoundItems="True"> <asp:ListItem Value="-1">-- Choose a Language Name --</asp:ListItem> </asp:DropDownList> <asp:AccessDataSource ID="AccessDataSource4" runat="server" DataFile="~/App_Data/pub_db_4_be.mdb"> </asp:AccessDataSource> <asp:DropDownList ID="cmbcboFiltEthCode" Style="z-index: 101; left: 641px; position: absolute; top: 202px" runat="server" Width="376px" Height="20px" BackColor="8454016" ForeColor="black" AutoPostBack="true" DataSourceID="AccessDataSource3" DataTextField="FilterEthCode" OnSelectedIndexChanged="cmbcboFiltEthCode_SelectedIndexChanged" AppendDataBoundItems="True"> <asp:ListItem Value="-1">-- Choose an Ethnologue Code --</asp:ListItem> </asp:DropDownList> <asp:AccessDataSource ID="AccessDataSource3" runat="server" DataFile="~/App_Data/pub_db_4_be.mdb" SelectCommand="SELECT EthnologueCode & EthnologueCodesOld & LanguageName & LanguageID AS FilterEthCode FROM Languages WHERE (LanguageName <> '' AND LanguageName IS NOT NULL AND LanguageNameAlt <> '' AND LanguageNameAlt IS NOT NULL AND EthnologueCode <> '' AND EthnologueCode IS NOT NULL AND EthnologueCodesOld <> '' AND EthnologueCodesOld IS NOT NULL) ORDER BY EthnologueCode"> </asp:AccessDataSource> But not this: <asp:DropDownList ID="cmbcboFiltLanguageID" Style="z-index: 201; left: 640px; position: absolute; top: 155px" runat="server" Width="377px" Height="20px" BackColor="8454016" ForeColor="0" AutoPostBack="true" DataSourceID="AccessDataSource4" DataTextField="FilterLanguageID" OnSelectedIndexChanged="cmbcboFiltLanguageID_SelectedIndexChanged" AppendDataBoundItems="True"> <asp:ListItem Value="-1">-- Choose a Language Name --</asp:ListItem> </asp:DropDownList> <asp:AccessDataSource ID="AccessDataSource4" runat="server" DataFile="~/App_Data/pub_db_4_be.mdb" SelectCommand="SELECT LanguageName & EthnologueCode & EthnologueCodesOld & LanguageID AS FilterLanguageID FROM Languages WHERE (LanguageName <> '' AND LanguageName IS NOT NULL AND LanguageNameAlt <> '' AND LanguageNameAlt IS NOT NULL AND EthnologueCode <> '' AND EthnologueCode IS NOT NULL AND EthnologueCodesOld <> '' AND EthnologueCodesOld IS NOT NULL) ORDER BY LanguageName"> </asp:AccessDataSource> <asp:DropDownList ID="cmbcboFiltEthCode" Style="z-index: 101; left: 641px; position: absolute; top: 202px" runat="server" Width="376px" Height="20px" BackColor="8454016" ForeColor="black" AutoPostBack="true" DataSourceID="AccessDataSource3" DataTextField="FilterEthCode" OnSelectedIndexChanged="cmbcboFiltEthCode_SelectedIndexChanged" AppendDataBoundItems="True"> <asp:ListItem Value="-1">-- Choose an Ethnologue Code --</asp:ListItem> </asp:DropDownList> <asp:AccessDataSource ID="AccessDataSource3" runat="server" DataFile="~/App_Data/pub_db_4_be.mdb" SelectCommand="SELECT EthnologueCode & EthnologueCodesOld & LanguageName & LanguageID AS FilterEthCode FROM Languages WHERE (LanguageName <> '' AND LanguageName IS NOT NULL AND LanguageNameAlt <> '' AND LanguageNameAlt IS NOT NULL AND EthnologueCode <> '' AND EthnologueCode IS NOT NULL AND EthnologueCodesOld <> '' AND EthnologueCodesOld IS NOT NULL) ORDER BY EthnologueCode"> </asp:AccessDataSource> The only lines that are different is the SelectCommand's. But they are both the same as version 1 and 2 in the third version. How is this possible? Scott Tag: Download to Excel from SQL stored procedure Tag: 139524
  - 14
    - "DataSet.Merge()" simple problem Hi there, Can anyone explain the following (very) simple scenario. 1) I make an exact copy of my "DataSet" and delete one record from a given table (in the copy) 2) I invoke "DataSet.GetChanges()" on the above copy and pass the results to "DataSet.Merge()" on the original copy 3) If I now inspect the original copy, it shows that the record has *not* been deleted from the table. However, a call to "GetChanges()" does show my deleted record. Why doesn't the record get deleted from the original table after the merge in step 2? Note that I've inspected my original "DataSet" after step 2 as well as the "DataSet" returned by "DataSet.GetChanges()". Apparently "DataSet.GetChanges()" returns a "DataSet" that's completely empty of data except for the one affected table which contains the single deleted record. That record is then simply added to the table in the original "DataSet" when "DataSet.Merge()" is called. The original table therefore winds up with two copies of the record, the origiinal and the deleted. Does anyone know what's going on here. Thanks in advance. Tag: Download to Excel from SQL stored procedure Tag: 139517
  - 15
    - Getting next autoincremented id from typed DataTable What's the alternative to the following? void m_BindingSource_AddingNew(object sender, AddingNewEventArgs e) { MyTypedDataSet.TypedTableRow newRow = this.m_DataTable.NewTypedTableRow(); this.m_DataTable = new MyTypedDataSet.VendorsDataTable(); DataRow[] existingRows = this.m_DataTable.Select("ID < 0", "ID ASC"); if (existingRows.Length > 0) { object lowestRowID = existingRows[0]["ID"]; newRow.ID = ((int)lowestRowID) - 1; } else { newRow.ID = -1; } e.NewObject = newRow; } Tag: Download to Excel from SQL stored procedure Tag: 139500
  - 16
    - DB Design Got a puzzler for anyone wanting to take on a challenge. I have some data, which represents something like an insurance policy. The customer may call up and change the level of coverage at any time. These changes may or may not produce a change in premium but may produce a change in legal obligation for my company. In the event that a customer places a claim on their policy, my company must first verify that there was coverage on the policy for the date of the claim. What this boils down to is that I need to intellegently preserve transactional state of the policy each time a change is made. I am looking for an effiecient way to store this data in the database and relate to it in my business logic. Currently what I am doing is creating a parent table for each of my entities that act as a place holder for each entity and then I create a details table to house the data points of interest. I relate the data via a parent transaction table and a child transaction details table. A record is created in the transaction detail table for each row in a detail table that is inserted. Given the insurance example: Policy PK PolicyID Policy Detail FK PolicyID FK TransactionDetailID Driver PK DriverID FK PolicyID Driver Detail FK DriverID FK TransactionDetailID Vehicle PK VehicleID FK PolicyID Vehicle Detail FK VehicleID FK TransactionDetailID Transaction PK TransactionID FK PolicyID Transaction Detail FK TransactionID In this model there are three types of allowed changes: -- A correction, which allows updates and are not transactional (highly critical of data allowed to be modified.) -- A transactional change in policy coverage, (clone business objects, change desired data points, insert new row) -- A reversal, which allows a transactional change to be reversed. Reversals throw out the biggest challenge, because the transaction it affected is no longer valid, so the one before it becomes the current transaction again. Reversals have the affect of never happened, and will be ignored when checking for coverage. Reverals will display in the transaction log (for Q/A purposes). The only time a reversal is not authorized is: --The original issuance (That requires a cancellation of the policy) While the underwriter can only create a change against the most relevant transaction, reversals place an earlier transaction in that billet, then once a change is made, it too can be reversed again making a prior transaction most relevant. Some of the issues that I am having with this implementation are: -- querying against the tables can be laborious with multiple joins, code table lookups and so forth. -- determining the most recent version of the policy is often convoluted for an active policy. --Transversing the transactional tree for a policy can get confusing. Not all relevant data for a given entity was modified during the target transaction. --Batch processing is complex when using native SQL, and slow when using the business API. Granted batch processing occurs at night, but computers need sleep too. (Currently, activity reports are taking 1.5 hrs to process via business API's and this is still a prototype system). Does anyone have an alternate solution for storing this data? I have been idlely browsing the web for possible solutions, I don't even know how to phrase the question. "Versioned Data", "Transactional Data", Transactional Auditing. (Append methodology, theory, concept). This concept cannot be unique to me alone. My counterpart mainframe group "stacks" the transactional history and deletes the data when a reversal is requested, this is a manual process for them. Our requirment was to preserve this historical change. Thanks in advance Tag: Download to Excel from SQL stored procedure Tag: 139498
  - 17
    - set a nullable property in object I have a business object (class) with some nullable properties declared like this: Private _MyProp as Nullable(of Int16) public Property MyProp as Nullable(Of Int16) get Return _MyProp end get set(ByVal Value as Nullable(Of Int16)) If not _MyProp.Equals(value) then _MyProp = Value NotifyPropertyChanged("MyProp") ' marks the property changed end if end set I can read from my database with no problem for both MyProp's with or without a value. When MyProp = 1 and I try to set it to Null in a Windows TextBox, the form will not even let me move to another control. If I change rows in a datagridview, the value is set back to 0. So, I added an OnValidate event like this: if String.IsNullOrEmpty(myControl.Text) then _ myBusinessObject.MyProperty = Nothing Now I can set the nullable property to "Nothing" and it works as expected. My question is - Is this the normal procedure to set a nullable value or am I missing some shortcut where I don't have to add the OnValidate event? Rick Tag: Download to Excel from SQL stored procedure Tag: 139492
  - 18
    - DSN Connection for Microsoft Access in ASP.NET Hi, I want to use a DSN connection to connect to MS Access Database. The ODBC DSN has been configured as MIT. I use the following connection string to connect (using VB.NET): Dim cn As New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0; DSN=MIT") This throws up an error: Could not find installable ISAM [OleDbException (0x80004005): Could not find installable ISAM.] System.Data.OleDb.OleDbConnection.ProcessResults(Int32 hr) +20 System.Data.OleDb.OleDbConnection.InitializeProvider() +57 System.Data.OleDb.OleDbConnection.Open() +203 Can some one please let me know how to make a DSN connection to Microsoft Access 2000 database in ASP.NET 1.1? Thanks in advance, Sandeep Gupta Tag: Download to Excel from SQL stored procedure Tag: 139489
  - 19
    - ASP.Net, Repeater Control - 3 Columns - Unlimited Rows Hello, I wanna list my stocks in a web page according to search result, this listing must be in a (3 columns and unlimited size) table, what I wanna list in a cell is stock name, stock price and stock picture I tried repeater control but it list data only row by row I think, I couldnt make first 3 datarow in first row, and second three row in second row, Data comes from a table, and paging is a must, Something like this >>> ------------------------------------------------------------------------------ Stocks ------------------------------------------------------------------------------ Stock 1 Stock 2 Stock 3 10,56 USD 20 USD 30,11 USD Picture Picture Picture Stock 4 Stock 5 Stock 6 10,56 USD 20 USD 30,11 USD Picture Picture Picture Stock 7 Stock 8 Stock 9 10,56 USD 20 USD 30,11 USD Picture Picture Picture ------------------------------------------------------------------------------ 1 - 2 - 3 - 4 - 5 - 6 - 7 - Paging - Next.... ------------------------------------------------------------------------------ How can I do this ? Repeater o anoher way ? Thank you Tag: Download to Excel from SQL stored procedure Tag: 139488
  - 20
    - Read (and Visualize) DB Schema from SQL Server Hi experts, which approaches would you suggest for: - Reading a database schema (tables, fields, relationships) from SQL Server 2005? - Visualizing the DB schema? For developing a DB tool the schema should be read from the server and then be visualized, e.g. like the SQL Server BI Development Studio does when importing the whole schema to a data source view. Any ideas and hints on how to start approaching this (either reading schema and/or visualizing it) using .NET (preferably C#) are welcome! Regards, Max Tag: Download to Excel from SQL stored procedure Tag: 139484
  - 21
    - create a data provider I would like to create my own data provider in .NET which is able to deliver data to all apps supporting oledb or odbc. Information about this is very rare in the Web. Are there any code samples around or books from which I could learn how to do this? Important, again, is the part which makes the provider visible to Excel etc. - Klemens Tag: Download to Excel from SQL stored procedure Tag: 139475
  - 22
    - Once more - Pessimistic Locking ADO.NET 2.0 SQL Server 2005 Hello ! I read any articles about locking so far, but I am not able to make a solution for my project. (I really want pessimistic locking - no such optimistic/pessimistic discussion please) This is my idea: (I made some experiencies with a new field "locked" in the orders-table - read committed - this worked, but now I want an other solution): I created a separate locking table. If anyone opens a datarow e.g. in the table orders in editmode, a locking datarow is inserted in the locking table, when storing or canceling, the row is deleted. This is my code: Protected m_objSQLConnection As SqlConnection Protected m_objSQLCommand As SqlCommand Protected m_objSQLTransaction As SqlTransaction Try m_objSQLConnection.Open() m_objSQLConnection.BeginTransaction 'Isolationlevel necessary ???? m_objSQLCommand.Transaction = Me.m_objSQLTransaction Dim strSQL As String strSQL = "SELECT * FROM tblSperrungen " '"WITH (HOLDLOCK, ROWLOCK etc necessary ???) " strSQL &= " WHERE (tablename = @tablename) AND (lineID = @lineID)" m_objSQLCommand.CommandText = strSQL m_objSQLCommand.Parameters.Clear() m_objSQLCommand.Parameters.AddWithValue("@tablename", ...) m_objSQLCommand.Parameters.AddWithValue("@lineID", ...) Dim objRd As SqlDataReader objRd = m_objSQLCommand.ExecuteReader If objRd.Read Then 'Sorry row is locked Else blnErg = True End If objRd.Close() System.Threading.Thread.Sleep(intPause) ' only for testing others try to lock and have success - why ?? If blnErg Then strSQL = "INSERT INTO tblSperrungen(tablename,lineID,GesperrtVon,GesperrtAm) VALUES (@tablename,@lineID,@GesperrtVon,@GesperrtAm)" m_objSQLCommand.CommandText = strSQL m_objSQLCommand.Parameters.Clear() m_objSQLCommand.Parameters.AddWithValue("@tablename", ...) m_objSQLCommand.Parameters.AddWithValue("@lineID", ...) m_objSQLCommand.Parameters.AddWithValue("@GesperrtVon", ...) m_objSQLCommand.Parameters.AddWithValue("@GesperrtAm", ...) m_objSQLCommand.ExecuteNonQuery() 'Locking OK End If TransCommit() CnClose() Catch objE As Exception TransRollback() cWindows.FehlerWin("Fehler bei Sperren-Funktion " & objE.Message, True) End Try My problem now is, that I have to guarantee that between the 2 SQL- Statements (SELECT and UPDATE) no other user can select the row. How can I prevent this row from reading. I played with some isolation levels and sql hints, but with no success. (stored procedures are not allowed in my project) Many Thanks aaapaul Tag: Download to Excel from SQL stored procedure Tag: 139473
  - 23
    - MS Access DB, XP Pro and IIS 5.1... I am using MS Visual Web Developer 2005 Express Edition, MS Access DB, XP Pro and IIS 5.1 on my local XP Pro. This is a Newbie question with a very simple answer (I think). When I run the page it loads up just fine (with all of DB fields in the dropdown boxes, etc.). The problem is when I go to select an item from any of the dropdown boxes the IE says, "Internet Explorer cannot display the webpage" and FireFox says, "The connection to the server was reset while the page was loading." I believe the error happens right after OnSelectedIndexChanged and before Protected Sub cmbRecordLookup_SelectedIndexChanged(...) because the program isn't getting to the breakpoint I put at the beginning of the Sub. I have programmed four of the pages and three of them work. Strange... Here is a part of my code: <asp:DropDownList ID="cmbRecordLookup" Style="z-index: 201; left: 200px; position: absolute; top: 203px" runat="server" Width="396px" Height="20px" BackColor="Yellow" Font-Names="Doulos SIL" ForeColor="0" DataSourceID="AccessDataSource1" DataTextField="forBookID" OnSelectedIndexChanged="cmbRecordLookup_SelectedIndexChanged" AutoPostBack="true" AppendDataBoundItems="True"> <asp:ListItem Value="-1">-- Choose a Book ID --</asp:ListItem> </asp:DropDownList> <asp:AccessDataSource ID="AccessDataSource1" runat="server" DataFile="~/App_Data/pub_db_4_be.mdb" SelectCommand="SELECT Books.BookID & Books.TitleNormalized & Languages.LanguageName & Languages.EthnologueCode AS forBookID FROM Books INNER JOIN Languages ON Languages.LanguageID=Books.LanguageID ORDER BY Books.BookID"> </asp:AccessDataSource> What is happening and how should I do this? I would appreciate any help. TIA Scott Tag: Download to Excel from SQL stored procedure Tag: 139472
  - 24
    - DataView filter bug? Simple problem. If the company is null, replace it with last name and first name: /* Replace null companies with last and first names */ DataView dv = new DataView((DataTable)this.Invoice, "Company = ''", "InvoiceID", DataViewRowState.CurrentRows); foreach (DataRowView drv in dv) { drv.Row["Company"] = drv.Row["LastName"] + ", " + drv["FirstName"]; } this.AcceptChanges(); Three rows were found in the DataView. While three replacements were made, 1 row received two replacements and 1 row wasn't changed at all. Is this a bug in ADO.NET or can you not change column values included in the filter while iterating through the rows? The following code worked: /* Replace null companies with last and first names */ DataView dv = new DataView((DataTable)this.Invoice, "Company = ''", "InvoiceID", DataViewRowState.CurrentRows); if (dv.Count > 0) { List<int> list = new List<int>(); foreach (DataRowView drv in dv) { list.Add((int)drv.Row["InvoiceID"]); } for (int i = 0; i < list.Count; i++) { InvoiceRow row = this.Invoice.FindByInvoiceID(list[i]); row.Company = row.LastName + ", " + row.FirstName; } } this.AcceptChanges(); Tag: Download to Excel from SQL stored procedure Tag: 139471
  - 25
    - Typed DataSets in MS Access with Stored Procedures I am using VS 2005 and a MS Access 2003 DB. In VS I use the server explorer to drag and drop tables and existing queries into the dataset designer window. I am having several problems. 1. In the Server Explorer tables are under the Tables folder but queries are under the Views folder or if they have parameters they will be under the Functions folder but never under the Stored Procedures folder. How does the Server explorer classify MS Access queries? 2. For queries (i.e. "Views" or "Functions") in the dataset designer whenever I use the "TableAdapter Configuration WIzard" Stored Procedures is grayed out and only the "Use SQL option is available". How can I make those other options available? 3. The main problem is queries which already exist in my database. I cannot seem to fill the DataTableAdapter with any data. There are no errors but no data either. I tried the same query inside MS Access and it does return data. The datatable adapter for my tables works fine. I did use the dataset properties window for the query to change the command type to "stored procedure" but it still cannot retrieve any data. Why? By the way if I manually code a non-typed dataset for a "stored procedure" it works fine. -- JT Tag: Download to Excel from SQL stored procedure Tag: 139469
- Next
  - 1
    - Problem with Expression in DataColumn Hi, I get errors when I try the following expression in my strongly typed dataset: "Sum(Parent.Child(Relation1).Value)" The error I get is: "Syntax error in aggregate argument: Expecting a single column argument with possible 'Child' qualifier." What I want to do is to navigate from the current row to its parent, and then use one of the parents child relations to do a sum. It seems like navigating more than one step is not supported. Can this be true? regards Carl Tag: Download to Excel from SQL stored procedure Tag: 139461
  - 2
    - How does GetSchema get foreign key constraints? The GetSchema documentation hurts, specifically: "Understanding the Provider-Specific Schema Collections" at http://msdn2.microsoft.com/en-us/library/ms254969(VS.80).aspx I'm trying to create a DataSet that matches an SQL Server database, and I've gotten close. I'm calling SqlConnection's GetSchema( "Tables"), and then for each table name, I'm doing: sqlDataAdapter = new SqlDataAdapter( "SELECT * FROM " + dataTableName, sqlConnection); sqlDataAdapter.FillSchema( dataSet, SchemaType.Mapped, dataTableName); Doing so initializes the data tables of the data set, including primary keys. What remains to be done is to create the foreign key constraints. Calling GetSchema( "ForeignKeys") returns the following columns: CONSTRAINT_CATALOG CONSTRAINT_SCHEMA CONSTRAINT_NAME TABLE_CATALOG TABLE_SCHEMA TABLE_NAME CONSTRAINT_TYPE IS_DEFERRABLE INITIALLY_DEFERRED What I expected to see was a local table name, the local field of the foreign key, the referenced table name, and the primary key field of the referenced table. Where is that? Thanks in advance. Tag: Download to Excel from SQL stored procedure Tag: 139457
  - 3
    - XSD generated by the xsd tool vs The Designer Hi I created an xsd file by assign a sample xml file to the xsd.exe tool I opened the xsd file in the vs 2005(designer) and saw that the data entities were represented as an element (E sign on the top, left of each data entity) When I create data entities by my self they represented as datatables 1) Why can't I get the XSD by tool in the same way 2) I thought to workaround that problem by create the diagram from the tool based xsd to the manual created digram, but it's not possible(probably because one is elemnt and the other datatable). Is there any other workaround? 3)If I create the diagram by myself, can I be assure tthat xml serialization will give me same structure? Tag: Download to Excel from SQL stored procedure Tag: 139456
  - 4
    - TransactionScope Problem Enterprise Library 2.0 We have a web application that calls a business/data layer to do database updates. There is a lot of coordination in the web page to handle id's updated from one class to be used as child id's in other classes. Here's the scenario. Using EnterpriseLibrary 2.0. Surround all class updates with TransactionScope. Call Update on ClassA - works correctly Set ChildID (ForeignKey) on ClassB and Call Update - works correctly Commit Transaction Scope - works correctly. If there are never any problems with the inner update, it works as planned, the problem occurs when there is a problem with the update on ClassB. Call Update on ClassA - works correctly Set ChildID (ForeignKey) on ClassB and Call Update - problem with other data (invalid cast, etc) Dispose Transaction Scope - works correctly. In this case, it works as planned, neither record is saved to db, but when we bring back the page to the user, and they correct the data, on this second attempt is when the problem occurs. Call Update on ClassA - works correctly Set ChildID (ForeignKey) on ClassB and Call Update - problem with other data (invalid cast, etc) Dispose Transaction Scope - works correctly. Display error to user, and they correct bad data Call Update on ClassA - works correctly Set ChildID (ForeignKey) on ClassB and Call Update - Get a FOREIGN KEY constraint violation 'The INSERT statement conflicted with the FOREIGN KEY constraint ...' I'm not sure why the inner update fails the second time because the id generated from the first update is not recognized, like they are in different scopes. fyi, I am using a SCOPE_IDENTITY() to grab the newly inserted id's Tag: Download to Excel from SQL stored procedure Tag: 139454
  - 5
    - VersionNotFoundException Hello, I have a DataGridView, bound to a typed DataTable (without any TableAdapter etc.). The values of that DataTable are changed in a second thread using the following code: DataSetOrderManager.DbOrderManagerWlanRow row = dtWlan.FindByHardwareID(HardwareID); if (row != null) row.LastVitalSign = DateTime.Now; row.LastIP = FromIpAddress.ToString(); row.LastAccessPoint = AccessPointMac; } Mostly, this is working fine, but sometimes I get an DataGridView.DataError with the VersionNotFoundException. I think, this happans, because the row(s) are changed in a different thread!?? I don't want to "Invoke" to the main thread, because the row(s) will be changed very often, at worst, maybe 100 times per second! My first attempt was just catching the DataError Event and ignoring the error, but then, sometimes I saw wrong values in the datagrid!! So I added a InvalidateCell in the DataError Event. Well, it seems to work, but I think, avoiding that error is better! Now the questions are: 1. Is it OK what I do in the DataError Event, or may this produce problems? 2. The better way: How to avoid the VersionNotFoundException? Thanks in advance, Robert Witter Tag: Download to Excel from SQL stored procedure Tag: 139453
  - 6
    - Need help with Cinema System I have a form called frmSales which has three datagridviews on it which are linked to a Cinema.mdb database using SQL. The first datagridview (Film) displays the FilmNumber(Unique Key) and FullTitle of the Film, the second datagridview (Date) displays the days the film is on for and the third datagridview (Session) displays SessionNo(Unique Key), Time, Cinema and SeatNumbers. I have two buttons on this form called Sales and the other called Return. What I need help with is the code for the buttons so that when the user has selected the three datagridviews and depending on whether Cinema 1 or Cinema 2 was selected in datagridview Session then when either the Sales or Return button is pressed they will be taken to the corresponding Cinema form (In this case either frmCinema1 or frmCinema2). Any help would be appreciated. Cheers, Kryt0n1t3 Tag: Download to Excel from SQL stored procedure Tag: 139449
  - 7
    - Need help with Cinema System Hi I am having trouble with a Cinema System I am creating in VB.NET. I have a form called frmSales which has three datagridviews on it which are linked to a Cinema.mdb database using SQL. The first datagridview (Film) displays the FilmNumber(Unique Key) and FullTitle of the Film, the second datagridview (Date) displays the days the film is on for and the third datagridview (Session) displays SessionNo(Unique Key), Time, Cinema and SeatNumbers. I have two buttons on this form called Sales and the other called Return. What I need help with is the code for the buttons so that when the user has selected the three datagridviews and depending on whether Cinema 1 or Cinema 2 was selected in datagridview Session then when either the Sales or Return button is pressed they will be taken to the corresponding Cinema form (In this case either frmCinema1 or frmCinema2). Any help would be appreciated. Cheers, Kryt0n1t3 Tag: Download to Excel from SQL stored procedure Tag: 139448
  - 8
    - Problem with Oracle using ADO.NET Hi, I have a problem to call a stored function with a C# application. I have tested the function directly on Oracle Server and the function is well compiled and it runs on SQL*PLUS. I think the problem is due on the RETURN parmeter, because if I create a procedure without a return valule the procedure can be called by my C# application. So the Oracle Function is returning an INT (Oracle). To call it on the console (it runs), I'm using the following code : --------------------------------------------------------------- DECLARE STATUS INT; BEGIN STAUS := MYFUNCTION(); END; ---------------------------------------------------------------- The code inserted in the C# application is the following: ----------------------------------------------------------------- OracleParameter oF = new OracleParameter(); ... oF = cmd.Parameters.Add( "RETOUR", OracleDbType.Int32, ParameterDirection.ReturnValue); cmd.CommandText = procName; cmd.CommandType = CommandType.StoredProcedure; Connection.Open(); cmd.ExecuteNonQuery(); Console.WriteLine(oF.Value); Connection.Close(); ... ----------------------------------------------------------------- When "cmd.ExecuteNonQuery()" is called I obtained the message: ORA-06502: PL/SQL : numeric error or error on a value : conversion error from characters to numbers. If I don't declare the "oF" parameter, I obtain: PLS-00221: 'MYFUNCTION' isn't a procedure or is undefined. If someone had the same problem, thanks to give me more information. Tag: Download to Excel from SQL stored procedure Tag: 139445
  - 9
    - Get extern changes back into dataset without completely refill Hello, I have a (typed) dataset for some tables in a sql-server express. Well, loading the datas with Fill and sending changes to the sql server with Update works fine. What I like to do is, sending the changes in the sql server (done by an other instance of my application on a different pc) back into the dataset WITHOUT having to fill the whole dataset again with the Fill-command. My sollution at the time is, that every row has a timestamp value and I ceep track of the highest timestamp value of any table. So I can fill with "WHERE timestamp > oldtimestamp". That works good, but what about deleted rows? Saving them in a own table (cause my own application delets them, I can do this), read that table and remove the deleted rows from dataset... Well, since I'm shure, I'm not the only one wanting to do this, I hope, there is a better, easier, elegant way doing this?? Hoping for help and new ideas, Robert Witter Tag: Download to Excel from SQL stored procedure Tag: 139443
  - 10
    - object-relational mapping Hi, We're looking for recommendations for an object-relational mapping product that will work with Microsoft's Data Access Layer and is either free or very reasonable. We have a very complex database and would need a robust product able to map complex objects. We are currently using a custom framework that turns our object "reads" into hundreds of "select" statements. We'd prefer to use a stored procedure approach which is driving our decision to "shop". We've done a tiny bit of research and plan to check out nHibernate and .NetTiers first. We would love feedback from anyone who has used either of these. We're just beginning the search and will be downloading trials/demos, so please save us some time if we should stay away from any really bad ones! thanks, Deanna Tag: Download to Excel from SQL stored procedure Tag: 139440
  - 11
    - Renaming files in SSIS Hi, I need to transfer data from a .dat file to different tables in SQL DB. After the transfer i need to rename the .dat file appending current date. I have been successfull in transferfing data. I have problems renaming the file. Can anybody help me with this? Regards, Rocksie *** Sent via Developersdex http://www.developersdex.com *** Tag: Download to Excel from SQL stored procedure Tag: 139436
  - 12
    - Help, Oledb connection problem Hi,all I met a strange problem, I connected to access database using oledb connection, connection string like "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=\\Server\Public\QAD\MDB\QADData.mdb;User ID='apiprogram';Password='35msaccessAPI'; Jet OLEDB:Database Password='1';Jet OLEDB:System database=\\Server\Public\QAD\MDB\TFSC_BJ.mdw; Mode=1" In access database, there exist tables linked to antother data server throught ODBC. When exeucting program, I found if there is less records,for example less than 1000, the data read without problem, but if there was large scale records count, the program prompt "odbc error". Why? if I should increase the ODBC Command Time Out or Max Locks Per File properties liking in ADO connection. But in ADO.Net, it does not support this manner, so what shall I do? Tag: Download to Excel from SQL stored procedure Tag: 139435
  - 13
    - datareader.getstring(0) doesn't return the whole string. Hi, I am using an Access DB and a datareader to retrieve a memo field. When I execute the following code the datareader only ever returns the first 255 characters, even though I have used a string builder (and a regular string) which I believe can hold more than 255 characters. Is there an issue with the datareader or the memo field of an access database. If I use a dataset and an OleDBDataAdapter then all of the characters (i.e. > 255) are read in and can be viewed in a datagrid. Many thanks, Jason. Dim cmd As New OleDbCommand("SELECT tblBOMS.bBOM, Count(tblBOMS.bBOM) AS CountOfbBOM FROM tblBOMS GROUP BY tblBOMS.bBOM HAVING (((Count(tblBOMS.bBOM))>=2)) ORDER BY Count(tblBOMS.bBOM) DESC;", cn4) Dim f As New FileInfo("C:\Duplicate Boms.txt") Dim s As StreamWriter = f.CreateText() If cn4.State = ConnectionState.Closed Then cn4.Open() End If Dim dr As OleDbDataReader = cmd.ExecuteReader(CommandBehavior.Default) While dr.Read Dim str As New StringBuilder(dr.GetString(0)) s.WriteLine("=============================================================") s.WriteLine(str) s.WriteLine("=============================================================") ' Find all catalogue number with this BOM Dim dv As New DataView dv.Table = ds2.Tables("BOMS") dv.RowFilter = "bBOM like '" & str.ToString() & "'" Dim drv As DataRowView For Each drv In dv Dim strA As String = drv("bName") s.WriteLine(strA) Next End While s.Close() s.Dispose() Tag: Download to Excel from SQL stored procedure Tag: 139434
  - 14
    - Prepare yourself for ASP.Net Certification Excellent articles/material for web application (ASP.Net). Complete reference available all in one place. You should visit following URL for vast information about web application. http://www.top-itarticles.com/mcsd/mcsdweb.asp Tag: Download to Excel from SQL stored procedure Tag: 139431
  - 15
    - partial merge? Hi, I'm using DataTable.Merge() to merge values from a source DT into a destination DT which are in seperate Datasets. The source DT has columns A,B,C and the destination DT has columns A,B,D. column A is primary key for both of them. So, basically, my target is to copy all the values from column B in the source DT to column B in the destination DT. So far so good (Merge with MissingSchemaAction.Ignore does the job). However, The source DT has 1000 rows and the destination DT has 50 rows. The Merge action copy all the 1000 rows into the destination DT, where I'm only interested in updating the existing 50 rows in the destination DT. (Just update, no Insert). From what I've read, if merge doesn't find a corresponding PK it adds the new rows. Is there any way to go around this with merge? any other way, without using merge? (I tried to save looping over all the rows in one DT and locating them in the other one) Thanks in advance Eran Tag: Download to Excel from SQL stored procedure Tag: 139428
  - 16
    - Changing Database Name and Owner (but not Table Name)in a TypedDat I have a TypedDataSet whose source tables live on a SQL Server. The DataTables in the TypedDataSet have lots of customized TableAdapters to execute special SELECTS. Took a lot of work. I have to change the Database name and Owner (schema actually on SQL 2005) but not the name of the tables on the SQL server. This wreaks havoc as you can imagine with the DataSource Wizard. If I add the tables anew to the TypedDataSet, I loose all the modifications to the original DataTables. I want to just modify the definitions of the existing DataTables in the TypedDataSet to reflect the changes in the underlying SQL database. It's not as simple as changing the Connection properties! It seems that the only way to change the Database Name and Owner is to Find/Replace within the .xsd file itself. A bit scary. Is there a better way? Is it safe just to make the suggested replacement? -- Michael Tag: Download to Excel from SQL stored procedure Tag: 139425
  - 17
    - OleDb and x64 Hi, Since there *appears* to be no 64-bit support for any data source other than SQL Server (and I wouldn't use OleDb to interface with SQL Server anyway as there's a native .NET data provider for that), what is the "accepted wisdom" for porting 32-bit apps to 64-bit when they require OleDb support...? Can it simply not be done...? E.g. let's say I have an existing 32-bit WinForms app written in VS.NET 2005 (C#) and my client has now asked me to create a 64-bit version, but the existing 32-bit app uses Jet as its back-end database... What are my options...? Maybe the WinForms app is a data consolidation tool which receives remote data feeds in the form of emails with attached Jet databases or Excel spreadsheets...? Jet and Excel remain hugely popular file formats in the corporate world, and that seems unlikely to change in the near future, especially Excel... Do I have to tell my client that, regrettably, it's just not possible to create a 64-bit version of any app which needs to interface with Jet or Excel...? Any assistance gratefully received. Mark -- http://www.markrae.net Tag: Download to Excel from SQL stored procedure Tag: 139424
  - 18
    - CFront 3.01 Do somebody know how to connect to CFront 3.01 from a Windows Mobile, Using Visual Basic .net 2003 ? or a tool data acces to Cfront? Tag: Download to Excel from SQL stored procedure Tag: 139422
  - 19
    - Query in memory dataset c# 2 Is there a way to query an in-memory dataset with sql or can you only work with the individual table through DataTable.Select()? How can you go about getting the identity of a new record if you use an autoincrement primary key on the datatable? Thanks Tag: Download to Excel from SQL stored procedure Tag: 139418
  - 20
    - How to populate an empty table in DB? Hi, I am wondering how I could populate the ampty table in the database from the DataSet or DataTable. So far I am using SqlBulkCopy. Is there any other way, or is it I should be using? I could make Insert row by row, but it is slow, I guess. Thanks, Lubomir Tag: Download to Excel from SQL stored procedure Tag: 139414
  - 21
    - SQLCLR Hi, I have a stored procedure, for example like the following: Public static void MyProc() { using ( SqlConnection conn = new SqlConnection(â??Context connection = trueâ??)) { String mySql = â??â?¦..â?? SqlCommand cmd = new SqlCommand (mySql, conn); conn.Open(); cmd.ExecuteNonQuery (); } } I can call this procedure from the application : using ( SqlConnection conn = new SqlConnection(MyConnString)) { SqlCommand cmd = â?¦ cmd.CommandType = CommandType.Storedprocedure; cmd.CommandText = â??MyProcâ??; conn.Open(); cmd.ExecuteNonQuery(); } This code is pretty redundant with the code in the stored procedure. Is there any other way how I could call the store procedure? Thanks, Lubomir Tag: Download to Excel from SQL stored procedure Tag: 139412
  - 22
    - SQLDataReader.GetOrdinal() fails rarely with IndexOutOfRange Hi everybody, In last month I'm experiencing a very strange problem and I think it's linked to ado.net. My team and I developed a lot of WebApplications and our environment is on .NET 2.0 and Sql Server 2000. (All Service packs have been installed) Most of time (99%) everything is running well, but sometimes (when exactly, we don't know) the same Web Applications (that worked 100000 times) fail. It seems sp doesn't return right fields or something similar. This happens occasionally and, when it occurs, a lot of components have the same problem. In Event Viewer warning messages appear about our libraries like: System.IndexOutOfRangeException: Site_Uid at System.Data.ProviderBase.FieldNameLookup.GetOrdinal(String fieldName) at System.Data.SqlClient.SqlDataReader.GetOrdinal(String name) at USNext.CommonUserControls.ContactInfo.GetLoginSites(SqlConnection sqlCnn) DataBinding: 'System.Data.Common.DbDataRecord' does not contain a property with the name 'name'. I'm pretty sure that the code and the stored procedure are fine also because they are old and executed a lot of times (more than 10000) every day. We started to have this type of problem a couple of months ago, initially sometime then every day around same hour (during the morning, when the server load is high). One month ago I increased the Max Pool Size setting of connection string to 500 and now it happens one time every 7-10 days. When it occurs I check the connections opened on SQL and they are not a lot. The only solutions we found: - Wait a few minutes then everything return to work fine - Restart IIS This post has been opened in Nov 2005 on Microsoft MSDN forums http://forums.microsoft.com/msdn/showpost.aspx?postid=137373&SiteID=1 and it's still open. Does someone know how to solve this problem? Does someone experiencing same problem? Thanks, Emanuele Tag: Download to Excel from SQL stored procedure Tag: 139409
  - 23
    - Help, Connection to Access Database Hi,all I met one problem of connecting Access database in asp.net web program. I use ODBC link to Access, the connection string like "PWD=12345;DSN=QADLinkMDB;DriverId=25;MaxBufferSize=2048;FIL=MS Access;PageTimeout=5;UID=apiprogram" while runing program, system prompt error: the workgoup file is missing or open exculsive by anthor user. My access database located in another server,if I copy the Access database to my local place, the program run without problem, but now always display above error. Then what's the problem? Anyone could give me a hand? Thanks! Tag: Download to Excel from SQL stored procedure Tag: 139406
  - 24
    - TableAdapter not updating table if the table is empty when loaded? I am having problems with the table adapter not updating (inserting a new record) the sql server table if I open the form with out any records already in the sql server table. If I add on record in sql server management studio, the the table adapter will work correctly, but there has to be at least on record. What is going on?? There are not any exceptions being thrown. I checked the table adapter commands (delete, insert, select, update) and they match the other table adapter that are working correctly. (just different fields and parameters) What else can I check for? Thanks Tag: Download to Excel from SQL stored procedure Tag: 139404
  - 25
    - Ole DB Jet 4.0 Provider corrupts recipient Excel database predicta VS2005 C# 2.0 I'm using ADO.NET and the Jet 4.0 OLE DB provider to append a row to an Excel spreadsheet. I have a data table of type System.Data.DataTable, and a table adapter of type System.Data.OleDb.OleDbDataAdapter to fill the data table. These are generated by the VS2005 dataset designer. My connection string, created using the Data Links wizard is Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:\FootAnalNovel.xls; Extended Properties="Excel 8.0;HDR=Yes";Persist Security Info=False 1. faAdapter.Fill() to fill the table from the worksheet 2. Create a new empty row for the table. 3. Set values for elements of the row. 4. Add the row to the table. 5. Call faAdapter.Update() to send results back to the spreadsheet In the target spreadsheet I observe: 1. The row was added. The first column was autoincremented as desired. 2. All numeric columns in the sheet, including dates, are correct. 3. The header line has not been modified - that is correct. 4. All text columns in the table have been corrupted. But, in a very particular way: they are filled in row by row by text that was originally in the sheet, in the order that it appeared originally in the sheet. Both originally empty cells and filled cells are overwritten. Thanks in advance for your help. Jim Tag: Download to Excel from SQL stored procedure Tag: 139402

Can anyone give me a simple bit of code to run on my website offering
a download to excel facility from a SQL stored procedure? I know how
to get the data from the stored procedure, but how do I let the user
download it to Excel, as I have seen done on some other websites?

Top