Generating an SqlParameter from a value in a DataSet

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
    - How do I set the DateTimeMode property when filling a DataTable [ I posted this in Microsoft.Public.Data.Ado, but I haven't had a response. I thought I would try here too. ] I am investigating how best to manage UTC timestamps in ADO.Net. We ran into a lot of issues with UTC DateTime serialization in .NET 1.1. With .NET 2.0, there are new properties to help identify when a DateTime (as part of a DataTable or as a regular object) is UTC or local. I have been able to handle all cases so far except one. In our database we store all timestamps in UTC. I want to retrieve the data into a DataTable whose column has the DateTimeMode property set to DataSetDateTime.Utc. I can't figure out how to do this. Any suggestions? I have tried setting the DateTimeMode after the DataTable has been populated, but that causes an exception. I haven't been able to find anything in SQL Server 2005 that would allow me to specify that a DateTime column is a "UTC" DateTime. The only way I've been able to do it is by adding the columns manually before calling adapter.Fill(). I hope there is a better way. Here is my test code. I know it is poor coding, but it is only a test. For simplicity, I just use a raw SQL statement. SqlConnection conn = new SqlConnection(@"server=(local);database=master;uid=sa;pwd=password;"); SqlCommand cmd = new SqlCommand("SELECT GetDate() AS local, GetUTCDate() AS utc", conn); cmd.CommandType = CommandType.Text; SqlDataAdapter adapter = new SqlDataAdapter(cmd); DataTable dt = new DataTable("TryIt"); dt.Columns.Add("utc", typeof(DateTime)); dt.Columns["utc"].DateTimeMode = DataSetDateTime.Utc; dt.Columns.Add("local", typeof(DateTime)); dt.Columns["local"].DateTimeMode = DataSetDateTime.Local; try { adapter.Fill(dt); dt.Columns["utc"].DateTimeMode = DataSetDateTime.Utc; dt.Columns["local"].DateTimeMode = DataSetDateTime.Local; } catch (Exception) { throw; } DateTime local = (DateTime)dt.Rows[0]["local"]; DateTime utc = (DateTime)dt.Rows[0]["utc"]; Tag: Generating an SqlParameter from a value in a DataSet Tag: 124327
  - 2
    - How can I find the BindingSource has some changed data Hi, In my WinForms application, I have a set of controls that are bound to a BindingSource control. When the user closes the form, I need to ask him if he wants to save data. The problem is I cannot find anyway to check if the value of any textboxes is changed! Obviously checking DataSet.HasChange is not adequate. The reason is that the user might be in middle of adding or editing a record so the record has not been reflected to underlying dataset yet, but user accidentally clicks on form close button. Any help would be appreciated, Alan Tag: Generating an SqlParameter from a value in a DataSet Tag: 124326
  - 3
    - Bug in TableAdapter Code Generator? I've run into problems using TableAdapters under VS2005 where the underlying Fill() method fails due to a constraint violation. This occurs when there was no change to the data of the underlying table -- hence there shouldn't be a constraint problem, so far as I can see -- but where the width of an nvarchar field changes (this occurs with SqlServer2005). Apparently, the TableAdapter code is sensitive to width mismatches between the field in the table and the field as it is described in the TableAdapter. That's not necessarily a bug...but the fact that reconfiguring the TableAdapter (by running its Configure context menu command) doesn't solve the problem surely is. Running the configuration command on a table where an nvarchar field's width has changed does not update the width specification for the column in the TableAdapter. You have to go in and do that manually (or recreate the entire TableAdapter from scratch, which is what I was doing early on...but that's a royal pain). Needless to say, having to manually update for field width changes is a pain. - Mark Tag: Generating an SqlParameter from a value in a DataSet Tag: 124325
  - 4
    - SQLAdapter Timeout doesn't work Hi everybody, i've a problem with loading a dataset: i need to load entire contents of various tables into dataset, however i don't know size of this table. If table size is too big simply i need a timeout exception and go on. The problem is that when the table is too big, the Fill method avanza without any timeout. Why? Mehod code is this: private DataSet LoadDataSet ( int Table, DBtype db, out string ErrorMsg) { DataSet ds = new DataSet(); SqlDataAdapter adapter = null; string cmd; string TableName; ErrorMsg = ""; try { // Table name TableName = GetTableName ( Table ); // Compose Select statement cmd = "SELECT * FROM " + TableName; // SqlDataAdapter (GetConnectionString compose the string and set connet timeout = 10) adapter = new SqlDataAdapter( cmd, GetConnectionString()); adapter.SelectCommand. // Ten seconds for the command adapter.SelectCommand.CommandTimeout = 10; // And ten seconds for the connection (i think i don't need because i set before) adapter.SelectCommand.Connection.Timeout = 10; // // Execute // adapter.Fill( ds ); return ds; } catch ( System.Exception ex ) { ErrorMsg = ex.ToString(); return null; } finally { if ( adapter != null ) adapter.Dispose(); } } Tag: Generating an SqlParameter from a value in a DataSet Tag: 124313
  - 5
    - Web Service SQL Connection Model Hi All, I am near completion of a project consisting (broadly) of a web app, a web service and a SQL server - the usual sort of configuration. Both the SQL server and the web service are tasked with dealing with large amounts of data per user; and a large number of users. At the moment I am using a new SqlConnection object for each request to the web service. Each of these connections is disposed of properly ... using (SqlConnection cn = DataService.CreateNewConnection()) { ... } Should I, however, be opening 1 single connection at Application startup and leaving that open for all requests from all Sessions? What are the pros & cons of both of these approaches? Many thanks, Chris Tag: Generating an SqlParameter from a value in a DataSet Tag: 124309
  - 6
    - Dataset creation problem......... Hi All, I have two access databases. First contains a table which contains userids. The second one contains the users records. I tried to create a dataset which will query in these two databases and get two datatables. I tried to add relation between these two datatables. But while setting the relationship it gives error because each & every records in the first table does not contain related records in the second table. e.g. Table 1 Table 2 --------------------------- -------------------------------------- Id UserId UserId UserName --------------------------- ------------------------------------- 1 1001 1001 User One 2 1001 1002 User Two 3 1002 1003 User Three 1004 User Four Can anyone help me in this issue............... Thanks, Biren. Tag: Generating an SqlParameter from a value in a DataSet Tag: 124306
  - 7
    - Get Rich Take advantage of a great deal Tag: Generating an SqlParameter from a value in a DataSet Tag: 124302
  - 8
    - Using XML to read data from SQL Server Does anyone have a good, complete example code that would show the necessary steps for using XML to read data from a SQL Server database? I'm trying to switch from DataReaders and I have very little knowledge in the XML area. I would be very grateful and I suspect it would help many others to see such a posting in this news group. Tag: Generating an SqlParameter from a value in a DataSet Tag: 124292
  - 9
    - Unable to insert BLOB in Oracle Hello, I am unable to insert BLOB objects into Oracle 9i database from .NET. I get the following exception: "ORA-01460: unimplemented or unreasonable conversion requested". I have a table in the database that accepts a number and blob object. The blob object i am trying to insert is a compressed dataset. Every thing seems to work fine if i insert small amount of data (compressing around 500 records) but i get an exception if i try to store data that has more than 500 records compressed. Any ideas as to why this may happen? Thanks Bala Tag: Generating an SqlParameter from a value in a DataSet Tag: 124288
  - 10
    - ADO.NET 2.0, WebMethods and datatable strange behavior Hi, I am using VS2005 and .NET 2.0 I have a WebMethod that accept a typed data table. I realized that whatever rows that client sends to the SOAP server through the datatable, the webmethod received an empty datatable!! After spending several hours, I found that the only kind of data table that I can pass it to a webmethod is detached datatable. If data table is attached and part of a dataset, then the webmethod receives an empty datatable! Is that true? Or I am missing something? Thank you, Alan Tag: Generating an SqlParameter from a value in a DataSet Tag: 124282
  - 11
    - Saving contents of datatable to Access table without writing SQL Hello, In ADO.NET is there anyway to save the contents of an datatable directly to an Access table without having to loop through and generate a SQL insert query string? I figure there's probably a way to do it as one chunk. I'm using VB.NET 1.1. Thanks, Eric Tag: Generating an SqlParameter from a value in a DataSet Tag: 124275
  - 12
    - DataSet Question Hi, I have a dataset that is filled with data from the db. The user has modified that data in one of the tables where some of fields should now be null. I have played around with delete the row and adding a new row but have problems with the update. Do you think I should just reset the row by looping through the row and setting all the values to null before I update changes to the row? Thanks Tag: Generating an SqlParameter from a value in a DataSet Tag: 124274
  - 13
    - SqlDataSource and Optimistic Concurrency Problems Hi, I'm using a detailsview control with an SqlDataSource control. My Update query isn't working, and I've narrowed it down to the optimistic concurrency parameters - i.e. when I comment them out of the query, it works. What seems to be happening is that if a field is NULL when it is read in through the select statement, it's causing the update statment not to find a match. I've got ConvertEmptyStringToNull not set (so it should default to true), but I can't get the update to work all the time and it's driving me crazy. Here's my code: Stored Procedure for Select as follows (the Update's going in a Stored Procedure when I can get it to work...): SELECT Customers.CustID, Customers.CustDisplayName, Customers.CustFirstName, Customers.CustSurname, Customers.CustType, Customers.CustNotes, Customers.CustIsBillingSame, Customers.CustNumEmployees, Customers.CustIsProspect, Customers.CustOnHold, Customers.CustPersonID, Customers.CustUserFieldB1, Customers.CustUserFieldB2, Customers.CustUserFieldB3, Customers.CustUserFieldD1, Customers.CustUserFieldD2, Customers.CustUserFieldD3, Customers.CustUserFieldI1, Customers.CustUserFieldI2, Customers.CustUserFieldI3, Customers.CustCategory, Customers.CustInactive, CustomerContactDetails_1.CustContactStreet1, CustomerContactDetails_1.CustContactStreet2, CustomerContactDetails_1.CustContactStreet3, CustomerContactDetails_1.CustContactTown, CustomerContactDetails_1.CustContactCounty, CustomerContactDetails_1.CustContactPostCode, CustomerContactDetails_1.CustContactCountry, CustomerContactDetails_1.CustContactTelephone, CustomerContactDetails_1.CustContactMobile, CustomerContactDetails_1.CustContactEMail, CustomerContactDetails_1.CustContactFax, CustomerContactDetails.CustContactStreet1 AS CustBContactStreet1, CustomerContactDetails.CustContactStreet2 AS CustBContactStreet2, CustomerContactDetails.CustContactStreet3 AS CustBContactStreet3, CustomerContactDetails.CustContactTown AS CustBContactTown, CustomerContactDetails.CustContactCounty AS CustBContactCounty, CustomerContactDetails.CustContactPostCode AS CustBContactPostCode, CustomerContactDetails.CustContactCountry AS CustBContactCountry, CustomerContactDetails.CustContactTelephone AS CustBContactTelephone, CustomerContactDetails.CustContactMobile AS CustBContactMobile, CustomerContactDetails.CustContactEMail AS CustBContactEMail, CustomerContactDetails.CustContactFax AS CustBContactFax FROM Customers LEFT OUTER JOIN CustomerContactDetails AS CustomerContactDetails_1 ON Customers.CustAddressID = CustomerContactDetails_1.CustContactID LEFT OUTER JOIN CustomerContactDetails ON Customers.CustBillingID = CustomerContactDetails.CustContactID WHERE (Customers.CustID = @CustID) <asp:SqlDataSource ID="SqlDataSource2" runat="server" ConflictDetection="CompareAllValues" ConnectionString="<%$ ConnectionStrings:FocusSystemsConnectionString %>" OldValuesParameterFormatString="original_{0}" ProviderName="<%$ ConnectionStrings:FocusSystemsConnectionString.ProviderName %>" SelectCommand="SelectCustomersAndAddresses" SelectCommandType="StoredProcedure" UpdateCommand="UPDATE [Customers] SET [CustDisplayName] = @CustDisplayName, [CustFirstName] = @CustFirstName, [CustSurname] = @CustSurname, [CustType] = @CustType, [CustNotes] = @CustNotes, [CustIsBillingSame] = @CustIsBillingSame, [CustNumEmployees] = @CustNumEmployees, [CustIsProspect] = @CustIsProspect, [CustOnHold] = @CustOnHold, [CustPersonID] = @CustPersonID, [CustUserFieldB1] = @CustUserFieldB1, [CustUserFieldB2] = @CustUserFieldB2, [CustUserFieldB3] = @CustUserFieldB3, [CustUserFieldD1] = @CustUserFieldD1, [CustUserFieldD2] = @CustUserFieldD2, [CustUserFieldD3] = @CustUserFieldD3, [CustUserFieldI1] = @CustUserFieldI1, [CustUserFieldI2] = @CustUserFieldI2, [CustUserFieldI3] = @CustUserFieldI3, [CustCategory] = @CustCategory, [CustInactive] = @CustInactive WHERE [CustID] = @original_CustID AND [CustDisplayName] = @original_CustDisplayName AND [CustFirstName] = @original_CustFirstName AND [CustSurname] = @original_CustSurname AND [CustType] = @original_CustType AND [CustIsBillingSame] = @original_CustIsBillingSame AND [CustNumEmployees] = @original_CustNumEmployees AND [CustIsProspect] = @original_CustIsProspect AND [CustOnHold] = @original_CustOnHold AND [CustPersonID] = @original_CustPersonID AND [CustCategory] = @original_CustCategory AND [CustInactive] = @original_CustInactive" EnableViewState="true"> <UpdateParameters> <asp:Parameter Name="CustDisplayName" Type="String" /> <asp:Parameter Name="CustFirstName" Type="String" /> <asp:Parameter Name="CustSurname" Type="String" /> <asp:Parameter Name="CustType" Type="String" /> <asp:Parameter Name="CustNotes" Type="String" /> <asp:Parameter Name="CustIsBillingSame" Type="Boolean" /> <asp:Parameter Name="CustNumEmployees" Type="Int32" /> <asp:Parameter Name="CustIsProspect" Type="Boolean" /> <asp:Parameter Name="CustOnHold" Type="Boolean" /> <asp:Parameter Name="CustPersonID" /> <asp:Parameter Name="CustUserFieldB1" Type="String" /> <asp:Parameter Name="CustUserFieldB2" Type="String" /> <asp:Parameter Name="CustUserFieldB3" Type="String" /> <asp:Parameter Name="CustUserFieldD1" Type="String" /> <asp:Parameter Name="CustUserFieldD2" Type="String" /> <asp:Parameter Name="CustUserFieldD3" Type="String" /> <asp:Parameter Name="CustUserFieldI1" Type="String" /> <asp:Parameter Name="CustUserFieldI2" Type="String" /> <asp:Parameter Name="CustUserFieldI3" Type="String" /> <asp:Parameter Name="CustCategory" Type="String" /> <asp:Parameter Name="CustInactive" Type="Boolean" /> <asp:Parameter Name="original_CustID" /> <asp:Parameter Name="original_CustDisplayName" /> <asp:Parameter Name="original_CustFirstName" /> <asp:Parameter Name="original_CustSurname" /> <asp:Parameter Name="original_CustType" /> <asp:Parameter Name="original_CustNotes" /> <asp:Parameter Name="original_CustIsBillingSame" /> <asp:Parameter Name="original_CustNumEmployees" /> <asp:Parameter Name="original_CustIsProspect" /> <asp:Parameter Name="original_CustOnHold" /> <asp:Parameter Name="original_CustPersonID" /> <asp:Parameter Name="original_CustUserFieldB1" /> <asp:Parameter Name="original_CustUserFieldB2" /> <asp:Parameter Name="original_CustUserFieldB3" /> <asp:Parameter Name="original_CustUserFieldD1" /> <asp:Parameter Name="original_CustUserFieldD2" /> <asp:Parameter Name="original_CustUserFieldD3" /> <asp:Parameter Name="original_CustUserFieldI1" /> <asp:Parameter Name="original_CustUserFieldI2" /> <asp:Parameter Name="original_CustUserFieldI3" /> <asp:Parameter Name="original_CustCategory" /> <asp:Parameter Name="original_CustInactive" /> <asp:Parameter Name="CustContactStreet1" Type="String" /> <asp:Parameter Name="CustContactStreet2" Type="String" /> <asp:Parameter Name="CustContactStreet3" Type="String" /> <asp:Parameter Name="CustContactTown" Type="String" /> <asp:Parameter Name="CustContactCounty" Type="String" /> <asp:Parameter Name="CustContactPostCode" Type="String" /> <asp:Parameter Name="CustContactCountry" Type="String" /> <asp:Parameter Name="CustContactTelephone" Type="String" /> <asp:Parameter Name="CustContactMobile" Type="String" /> <asp:Parameter Name="CustContactEMail" Type="String" /> <asp:Parameter Name="CustContactFax" Type="String" /> <asp:Parameter Name="original_CustContactStreet1" Type="String" /> <asp:Parameter Name="original_CustContactStreet2" Type="String" /> <asp:Parameter Name="original_CustContactStreet3" Type="String" /> <asp:Parameter Name="original_CustContactTown" Type="String" /> <asp:Parameter Name="original_CustContactCounty" Type="String" /> <asp:Parameter Name="original_CustContactPostCode" Type="String" /> <asp:Parameter Name="original_CustContactCountry" Type="String" /> <asp:Parameter Name="original_CustContactTelephone" Type="String" /> <asp:Parameter Name="original_CustContactMobile" Type="String" /> <asp:Parameter Name="original_CustContactEMail" Type="String" /> <asp:Parameter Name="original_CustContactFax" Type="String" /> <asp:Parameter Name="CustBContactStreet1" Type="String" /> <asp:Parameter Name="CustBContactStreet2" Type="String" /> <asp:Parameter Name="CustBContactStreet3" Type="String" /> <asp:Parameter Name="CustBContactTown" Type="String" /> <asp:Parameter Name="CustBContactCounty" Type="String" /> <asp:Parameter Name="CustBContactPostCode" Type="String" /> <asp:Parameter Name="CustBContactCountry" Type="String" /> <asp:Parameter Name="CustBContactTelephone" Type="String" /> <asp:Parameter Name="CustBContactMobile" Type="String" /> <asp:Parameter Name="CustBContactEMail" Type="String" /> <asp:Parameter Name="CustBContactFax" Type="String" /> <asp:Parameter Name="original_CustBContactStreet1" Type="String" /> <asp:Parameter Name="original_CustBContactStreet2" Type="String" /> <asp:Parameter Name="original_CustBContactStreet3" Type="String" /> <asp:Parameter Name="original_CustBContactTown" Type="String" /> <asp:Parameter Name="original_CustBContactCounty" Type="String" /> <asp:Parameter Name="original_CustBContactPostCode" Type="String" /> <asp:Parameter Name="original_CustBContactCountry" Type="String" /> <asp:Parameter Name="original_CustBContactTelephone" Type="String" /> <asp:Parameter Name="original_CustBContactMobile" Type="String" /> <asp:Parameter Name="original_CustBContactEMail" Type="String" /> <asp:Parameter Name="original_CustBContactFax" Type="String" /> </UpdateParameters> <SelectParameters> <asp:ControlParameter ControlID="CustNameSelect" Name="CustID" PropertyName="SelectedValue" /> </SelectParameters> </asp:SqlDataSource> </td> </tr> <tr><td> <asp:DetailsView ID="DetailsView1" runat="server" AutoGenerateRows="False" DataSourceID="SqlDataSource2" Height="50px" Width="125px" DataKeyNames="CustID"> <Fields> <asp:BoundField DataField="CustID" HeaderText="CustID" ReadOnly="True" SortExpression="CustID" /> <asp:BoundField DataField="CustDisplayName" HeaderText="CustDisplayName" SortExpression="CustDisplayName" /> <asp:BoundField DataField="CustFirstName" HeaderText="CustFirstName" SortExpression="CustFirstName" /> <asp:BoundField DataField="CustSurname" HeaderText="CustSurname" SortExpression="CustSurname" /> <asp:BoundField DataField="CustType" HeaderText="CustType" SortExpression="CustType" /> <asp:BoundField DataField="CustNotes" HeaderText="CustNotes" SortExpression="CustNotes" /> <asp:CheckBoxField DataField="CustIsBillingSame" HeaderText="CustIsBillingSame" SortExpression="CustIsBillingSame" /> <asp:BoundField DataField="CustNumEmployees" HeaderText="CustNumEmployees" SortExpression="CustNumEmployees" /> <asp:CheckBoxField DataField="CustIsProspect" HeaderText="CustIsProspect" SortExpression="CustIsProspect" /> <asp:CheckBoxField DataField="CustOnHold" HeaderText="CustOnHold" SortExpression="CustOnHold" /> <asp:BoundField DataField="CustPersonID" HeaderText="CustPersonID" SortExpression="CustPersonID" /> <asp:BoundField DataField="CustUserFieldB1" HeaderText="CustUserFieldB1" SortExpression="CustUserFieldB1" /> <asp:BoundField DataField="CustUserFieldB2" HeaderText="CustUserFieldB2" SortExpression="CustUserFieldB2" /> <asp:BoundField DataField="CustUserFieldB3" HeaderText="CustUserFieldB3" SortExpression="CustUserFieldB3" /> <asp:BoundField DataField="CustUserFieldD1" HeaderText="CustUserFieldD1" SortExpression="CustUserFieldD1" /> <asp:BoundField DataField="CustUserFieldD2" HeaderText="CustUserFieldD2" SortExpression="CustUserFieldD2" /> <asp:BoundField DataField="CustUserFieldD3" HeaderText="CustUserFieldD3" SortExpression="CustUserFieldD3" /> <asp:BoundField DataField="CustUserFieldI1" HeaderText="CustUserFieldI1" SortExpression="CustUserFieldI1" /> <asp:BoundField DataField="CustUserFieldI2" HeaderText="CustUserFieldI2" SortExpression="CustUserFieldI2" /> <asp:BoundField DataField="CustUserFieldI3" HeaderText="CustUserFieldI3" SortExpression="CustUserFieldI3" /> <asp:BoundField DataField="CustCategory" HeaderText="CustCategory" SortExpression="CustCategory" /> <asp:CheckBoxField DataField="CustInactive" HeaderText="CustInactive" SortExpression="CustInactive" /> <asp:BoundField DataField="CustContactStreet1" HeaderText="CustContactStreet1" SortExpression="CustContactStreet1" /> <asp:BoundField DataField="CustContactStreet2" HeaderText="CustContactStreet2" SortExpression="CustContactStreet2" /> <asp:BoundField DataField="CustContactStreet3" HeaderText="CustContactStreet3" SortExpression="CustContactStreet3" /> <asp:BoundField DataField="CustContactTown" HeaderText="CustContactTown" SortExpression="CustContactTown" /> <asp:BoundField DataField="CustContactCounty" HeaderText="CustContactCounty" SortExpression="CustContactCounty" /> <asp:BoundField DataField="CustContactPostCode" HeaderText="CustContactPostCode" SortExpression="CustContactPostCode" /> <asp:BoundField DataField="CustContactCountry" HeaderText="CustContactCountry" SortExpression="CustContactCountry" /> <asp:BoundField DataField="CustContactTelephone" HeaderText="CustContactTelephone" SortExpression="CustContactTelephone" /> <asp:BoundField DataField="CustContactMobile" HeaderText="CustContactMobile" SortExpression="CustContactMobile" /> <asp:BoundField DataField="CustContactEMail" HeaderText="CustContactEMail" SortExpression="CustContactEMail" /> <asp:BoundField DataField="CustContactFax" HeaderText="CustContactFax" SortExpression="CustContactFax" /> <asp:BoundField DataField="CustBContactStreet1" HeaderText="CustBContactStreet1" SortExpression="CustBContactStreet1" /> <asp:BoundField DataField="CustBContactStreet2" HeaderText="CustBContactStreet2" SortExpression="CustBContactStreet2" /> <asp:BoundField DataField="CustBContactStreet3" HeaderText="CustBContactStreet3" SortExpression="CustBContactStreet3" /> <asp:BoundField DataField="CustBContactTown" HeaderText="CustBContactTown" SortExpression="CustBContactTown" /> <asp:BoundField DataField="CustBContactCounty" HeaderText="CustBContactCounty" SortExpression="CustBContactCounty" /> <asp:BoundField DataField="CustBContactPostCode" HeaderText="CustBContactPostCode" SortExpression="CustBContactPostCode" /> <asp:BoundField DataField="CustBContactCountry" HeaderText="CustBContactCountry" SortExpression="CustBContactCountry" /> <asp:BoundField DataField="CustBContactTelephone" HeaderText="CustBContactTelephone" SortExpression="CustBContactTelephone" /> <asp:BoundField DataField="CustBContactMobile" HeaderText="CustBContactMobile" SortExpression="CustBContactMobile" /> <asp:BoundField DataField="CustBContactEMail" HeaderText="CustBContactEMail" SortExpression="CustBContactEMail" /> <asp:BoundField DataField="CustBContactFax" HeaderText="CustBContactFax" SortExpression="CustBContactFax" /> <asp:CommandField ShowEditButton="True" /> </Fields> </asp:DetailsView> Tag: Generating an SqlParameter from a value in a DataSet Tag: 124270
  - 14
    - DataColumn size Hi everybody! Do someone know the easiest way to obtain the datacolumn size in code (I mean the size that you define creating a text field in a table)? In VB6 you could get it by means of Recordset.Field. DefinedSize property. I have been searchin for something similar in ADO.NET but without luck. Thanks beforehand Tag: Generating an SqlParameter from a value in a DataSet Tag: 124256
  - 15
    - AD.NET 2.0: BindingSource and Parent Child relationship Hi, I have two BindingSource(s) in my form, one for parent and one for child area. The unique situation in my form is that the child area chooses its parent by a ComboBox. The problem is when I use AndNew method in the child BindingSource, I cannot choose the parent record through the combobox and parentDataSource.Position. The parent BindingSource complains about [Not Null] columns and throws exception. This doesn't make any sense to me. I shouldn't have to use DataSet.EnforceConstraints because I do not have any NULL value at that specific parent record and position! I am sure that I am missing an important points, what is it? Any help would be appreciated, Alan Tag: Generating an SqlParameter from a value in a DataSet Tag: 124248
  - 16
    - How to write generic code access in ADO.NET M. Chand is a .NET consultant, author and the admin and founder of C# Corner. He has been working with .NET technology since pre beta releases. http://www.dotnetwire.com/frame_redirect.asp?newsid=3171 using System; using System.Data; using System.Data.Common; using System.Data.OleDb; using System.Data.SqlClient; using Microsoft.Data.Odbc; namespace GenericDataAccessApp { public class GenericAdoNetComp { private IDbConnection idbConn = null; private IDbDataAdapter idbAdapter = null; private DbDataAdapter dbAdapter = null; private IDataReader iReader = null; public GenericAdoNetComp() { } // GetConnection returns IDbConnection public IDbConnection GetConnection(int connType, string connString) { switch (connType) { case 1: // OleDb Data Provider idbConn = new OleDbConnection(connString); break; case 2: // Sql Data Provider idbConn = new SqlConnection(connString); break; case 3: // ODBC Data Provider idbConn = new OdbcConnection(connString); break; // case 3: // Add your custom data provider default: break; } return idbConn; } // GetDataAdapter returns IDbDataAdapter public IDbDataAdapter GetDataAdapter(int connType, string connString, string sql) { switch (connType) { case 1: // OleDb Data Provider idbAdapter = new OleDbDataAdapter(sql, connString); break; case 2: // Sql Data Provider idbAdapter = new SqlDataAdapter(sql, connString); break; case 3: // ODBC Data Provider idbAdapter = new OdbcDataAdapter(sql, connString); break; // case 3: // Add your custom data provider default: break; } return idbAdapter; } } } public class Client { private void ConnectBtn_Click(object sender, System.EventArgs e) { GenericAdoNetComp genDP = new GenericAdoNetComp(); sql = "SELECT * FROM Employees"; if (radioButton1.Checked) { connString = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=c:\\Northwind.mdb"; conn = genDP.GetConnection(1, connString); adapter = genDP.GetDataAdapter(1, connString, sql); } else if (radioButton2.Checked) { connString = "Data Source=MCB;Initial Catalog=Northwind;user id=sa;password=;"; conn = genDP.GetConnection(2, connString); adapter = genDP.GetDataAdapter(2, connString, sql); } else if (radioButton3.Checked) { // Construct your connection string here conn = genDP.GetConnection(3, connString); adapter = genDP.GetDataAdapter(3, connString, sql); } try { conn.Open(); // Fill a DataSet DataSet ds = new DataSet(); adapter.Fill(ds); dataGrid1.DataSource = ds.Tables[0].DefaultView; } catch (Exception exp) { MessageBox.Show(exp.Message); } finally { conn.Close(); } } } Tag: Generating an SqlParameter from a value in a DataSet Tag: 124247
  - 17
    - SQLconnection problem I'm pretty new to asp.net and I'm having trouble just trying to set up a basic SQLconnection within visual studio, I'm sure the problem must be something basic but I can't seem to find it. The problem is that the connection string ("Integrated Security=yes;Initial Catalog=Northwind;Data Source=(local)") is underlined within the code editor and provides the error text 'Too many arguements for 'Public Sub New()' when you hover over the text. a snippet of the code is below: Imports System.Data Imports System.Data.SqlClient ' ----- within the page_load function ------ Dim sqlConnection As SQLConnection sqlConnection = New SqlConnection("Integrated Security=yes;Initial Catalog=Northwind;Data Source=(local)") Tag: Generating an SqlParameter from a value in a DataSet Tag: 124246
  - 18
    - Binary serializing of dataset in framework 2.0 I was eager to try the new binary serialization of dataset in version 2.0 of the framework. The new serialization seems to be very fast comparede to the "binary" serialization in framework 1.1. However I have a few problems consering columns of the type Date: Columns set to the value of Date.MinValue and Date.MaxValue get corruptede depending on the what time zone my computer is set to: 1. When time zone is set to Greenwich Mean Time (GMT) everything works just fine. 2. When time zone is set to something east of GMT for example GMT +1 (Denmark) coulms with the value Date.MinValue are corrupted during the serialization and deserialization. 3. When time zone is set to something west of GMT for example GMT -6 (Central Time US) coulms with the value Date.MaxValue are corrupted during the serialization and deserialization. I also expirience problems with null valus: Columns with null values will be set to Date.MinVaule after deserialization when time time zone is east of GMT. Is this a bug or am i doing something worg? I use the code below to demostrate this behaviour: 'Create a dataset for test purpose '-------------------------------------- Dim drNew As DataRow Me.DatasetOriginal = New DataSet Me.DatasetOriginal.Tables.Add("TEST") Me.DatasetOriginal.Tables("TEST").Columns.Add("Id", GetType(Integer)) Me.DatasetOriginal.Tables("TEST").Columns.Add("Name", GetType(String)) Me.DatasetOriginal.Tables("TEST").Columns.Add("Date", GetType(Date)) Me.DatasetOriginal.Tables("TEST").Columns.Add("Number", GetType(Integer)) Me.DatasetOriginal.Tables("TEST").Columns.Add("Bool", GetType(Boolean)) drNew = Me.DatasetOriginal.Tables("TEST").NewRow() drNew.Item("Id") = 1 drNew.Item("Name") = "My name" drNew.Item("Date") = Date.MaxValue drNew.Item("Number") = 13 drNew.Item("Bool") = True Me.DatasetOriginal.Tables("TEST").Rows.Add(drNew) drNew = Me.DatasetOriginal.Tables("TEST").NewRow() drNew.Item("Id") = 2 drNew.Item("Date") = Date.MinValue drNew.Item("Number") = 13 drNew.Item("Bool") = True Me.DatasetOriginal.Tables("TEST").Rows.Add(drNew) drNew = Me.DatasetOriginal.Tables("TEST").NewRow() drNew.Item("Id") = 3 drNew.Item("Name") = "My name" drNew.Item("Number") = 13 drNew.Item("Bool") = True Me.DatasetOriginal.Tables("TEST").Rows.Add(drNew) drNew = Me.DatasetOriginal.Tables("TEST").NewRow() drNew.Item("Id") = 4 drNew.Item("Name") = "My name" drNew.Item("Date") = Date.Now drNew.Item("Bool") = True Me.DatasetOriginal.Tables("TEST").Rows.Add(drNew) drNew = Me.DatasetOriginal.Tables("TEST").NewRow() drNew.Item("Id") = 5 drNew.Item("Name") = "My name" drNew.Item("Date") = Date.Now drNew.Item("Number") = 13 Me.DatasetOriginal.Tables("TEST").Rows.Add(drNew) 'Serialize dataset '------------------- Dim bin As New System.Runtime.Serialization.Formatters.Binary.BinaryFormatter Dim Writer2 As New System.IO.StreamWriter("C:\DatasetSerialized.dat") Try DatasetOriginal.WriteXml("C:\Serialized.xml", XmlWriteMode.WriteSchema) DatasetOriginal.RemotingFormat = SerializationFormat.Binary bin.Serialize(Writer2.BaseStream, DatasetOriginal) Writer2.Close() Writer2 = Nothing Catch ex As Exception MessageBox.Show(ex.Message) End Try 'DeSerializer dataset '----------------------- Dim bin As New System.Runtime.Serialization.Formatters.Binary.BinaryFormatter Dim Reader As New System.IO.StreamReader("C:\DatasetSerialized.dat") Try DatasetSerialized = New DataSet DatasetSerialized.RemotingFormat = SerializationFormat.Binary DatasetSerialized = bin.Deserialize(Reader.BaseStream) Reader.Close() Reader = Nothing DatasetSerialized.WriteXml("C:\DeSerialized.xml", XmlWriteMode.WriteSchema) Catch ex As Exception MessageBox.Show(ex.Message) End Try Tag: Generating an SqlParameter from a value in a DataSet Tag: 124244
  - 19
    - DataColumn.Exression and string formatting Hi, I am truing to add a calculated column to my data table. The column must show something like: "Date: 2005-01-23" I underestand that we can use Convert to convert a DateTime value to string. How can I convert it in specific date format? Thank you, Alan Tag: Generating an SqlParameter from a value in a DataSet Tag: 124229
  - 20
    - excel datasource and gridview sorting Hi! I am somewhat befuddled. I have a gridview that I have filled using oledb datareader with data from an excel sheet.(code at bottom) My problem is that it does not sort and I can't figure out why. If I import to access and create a datasource using the table in access I imported from all is well. But when I fill it with the reader sorting has no effect. the code below works for gridview1 which uses a access datasource but the first one is uneffected. I have set allowsorting = true on both. But only the second one is sorting both by the button and by clicking on the hyperlink in the header. Can anyone tell me what I need to get the excel to sort? Thanks for your interest! Len Protected Sub btnSort_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnSort.Click Dim direction As SortDirection direction = SortDirection.Ascending ExcelOut.Sort("Attending", direction) AccessGridView1.Sort("Attending", direction) ========PageLoad===================================================== Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load If Not Page.IsPostBack Then Dim DBConnection = New OleDbConnection( _ "Provider=Microsoft.Jet.OLEDB.4.0;" & _ "Data Source=" & Me.MapPath("Upload/DailyInfo.xls") & ";" & _ "Extended Properties=""Excel 8.0;HDR=Yes""") DBConnection.Open() Dim strConnString As String = Me.MapPath("Upload/DailyInfo.xls") Dim SQLString As String = "SELECT Hospital,[Room],[Patient Name],DOB,Symptom,Notes,Attending,Consulting ,[Initial Date Seen],FU FROM [INPATIENT PATIENT SVCS$]" Dim DBCommand = New OleDbCommand(SQLString, DBConnection) Dim DBReader As OleDbDataReader = DBCommand.ExecuteReader() ExcelOut.DataSource = DBReader ExcelOut.DataBind() DBReader.Close() DBConnection.Close() End If End Sub Html:================================ <asp:GridView ID="ExcelOut" runat="server" Width="800px" CellPadding="4" ForeColor="#333333" GridLines="None" AllowSorting="True" AutoGenerateColumns="False"> <Columns> <asp:BoundField DataField="HOSPITAL" HeaderText="HOSPITAL" SortExpression="HOSPITAL" /> <asp:BoundField DataField="Room" HeaderText="Room" SortExpression="Room" /> <asp:BoundField DataField="Patient Name" HeaderText="Patient Name" SortExpression="Patient Name" /> <asp:BoundField DataField="DOB" HeaderText="DOB" SortExpression="DOB" /> <asp:BoundField DataField="Symptom" HeaderText="Symptom" SortExpression="Symptom" /> <asp:BoundField DataField="Notes" HeaderText="Notes" SortExpression="Notes" /> <asp:BoundField DataField="Attending" HeaderText="Attending" SortExpression="Attending" /> <asp:BoundField DataField="Consulting" HeaderText="Consulting" SortExpression="Consulting" /> <asp:BoundField DataField="Initial Date Seen" HeaderText="Initial Date Seen" SortExpression="Initial Date Seen" /> <asp:BoundField DataField="FU" HeaderText="FU" SortExpression="FU" /> </Columns> <FooterStyle BackColor="#990000" Font-Bold="True" ForeColor="White" /> <RowStyle BackColor="#FFFBD6" ForeColor="#333333" /> <SelectedRowStyle BackColor="#FFCC66" Font-Bold="True" ForeColor="Navy" /> <PagerStyle BackColor="#FFCC66" ForeColor="#333333" HorizontalAlign="Center" /> <HeaderStyle BackColor="#990000" Font-Bold="True" ForeColor="White" /> <AlternatingRowStyle BackColor="White" /> </asp:GridView> a.. b.. c.. Report abuse -------------------------------------------------------------------------------- Tag: Generating an SqlParameter from a value in a DataSet Tag: 124214
  - 21
    - 0(n ***Hot stuff - check this out !!! 0(n http://www.kaneva.com/checkout/stream.aspx?assetId=2017&free=0 Tag: Generating an SqlParameter from a value in a DataSet Tag: 124206
  - 22
    - Accessing a field in untyped DS Hello, I have an copied data set. I'm trying to access a field. daOrder.Fill(dsOrderSheet); DataSet dsCopy = new DataSet(); dsCopy = dsOrderSheet.Copy(); // How do you do this part? output = dsCopy.Tables["orders"].Rows[0].ItemArray["orderDate"]; //or output = dsCopy.Tables[0].Rows[0].ItemArray[1]; //neither work. How is this done? Thanks kindly for any advice on this Ant Tag: Generating an SqlParameter from a value in a DataSet Tag: 124205
  - 23
    - Adding a Datatable to a dataset Hi, I'm currently getting up to speed with ADO.NET & have a few questions on it. This question relates to why a data table is added to a data set such: DataTable dtEmp = ds.Tables.Add["employees"]; // From book i would have thought it would be more like: DataTable dtEmp = New DataTable("employees"); ds.Tables.Add(dtEmp); // The way I think it should be. Works ok So what is going on in the first line? (from the book) Is dtEmp being referenced to the "employees" table that is being added to the dataset? (As opposed to dtEmp actually being added?). It doesn't seem intuitutive to me... Thanks for any comments Ant Tag: Generating an SqlParameter from a value in a DataSet Tag: 124203
  - 24
    - How do I assign a typed DataTable to its typed DataSet? Hi, The following code: TypedDataSet typedDS = new TypedDataSet(); TypedDataSet.TypedDataTable typedDT; // // Code to fill typedDT // typedDS.TypedDataTable = typedDT; // Colmpiler error! gives me a compiler error: Property or indexer cannot be assigned to -- it is read only What is the proper way to assign a typed DataTable to its typed DataSet? Thank you, Alan Tag: Generating an SqlParameter from a value in a DataSet Tag: 124201
  - 25
    - Datagrid calculated column not updating I have a datagrid (C# Windows Forms App) bound to a dataset. One column in the dataset is a calculated column based on an expression that simply subtracts the value in one column from the value in another column. I created the dataset using a dataset control and the properties dialog boxes. The dataset is not populated from a database - the data is entered into the grid by the user. By adding the following line of code in the form load event handler: dtChequeNos.Columns["line_count"].Expression = "to_seq_no - from_seq_no"; I was able to get the column to perform the calculation but it only displays the result when the user moves to a new row in the grid. I need the calculated column to display the result before the user moves to a new row. Is this possible? Tag: Generating an SqlParameter from a value in a DataSet Tag: 124199
- Next
  - 1
    - Q: Updating the correct keys Hi I'm hoping somebody can help me with the following problem that has occurred to me. Suppose I have two tables in an SQL Server database. Let's call these tables A and B. Assume that A has two fields: a primary key and another holding a string. In table B there are three fields: a primary key, a foreign key (which links to the primary key in A) and other field holding a string. Suppose I load these tables into a DataSet. I populate table A with some rows. For each of these rows I create some rows in B which are linked by the foreign key to A i.e. there is a one to many relationship between A and B. Once this is done, I update the DataSet via a DataAdaptor such that these tables are transferred to the SQL database. My question is this. As far as I can see, the values in the primary key of A and the corresponding keys in B will not necessarily be the same in the DataSet and the SQL database! Do you see what I mean? I can't see how the relationship specified is still valid after the Update. Can anybody explain this to me? Thanks in advance Geoff Tag: Generating an SqlParameter from a value in a DataSet Tag: 124198
  - 2
    - ODBCDataReader NO_DATA error when reading from CSV file This is a strange one. In ASP.Net v1.1 using VB, I'm using an ODBCDataReader to read from a CSV file. Most of the time it works, but if the "description" field contains more than 2046 chars, I get a NO_DATA error (see below for full error) when trying to read that row. If this field contains 2046 chars or less, then everything works fine. There are about 6800 rows or lines in the CSV file. However, if I reduce the amount of lines in the CSV to, say 25, then the description field can contain more than 2046 chars without causing the error. Here's a sample from my code: 'Save uploaded file to disk. filCSV.PostedFile.SaveAs(server.MapPath("import/properties.csv")) 'Import data from CSV into MSSQL. dim strTxtConString as string = "Driver={Microsoft Text Driver (*.txt; *.csv)};Dbq=" & Server.MapPath("import/") & ";" dim conTxt as ODBCConnection = New ODBCConnection(strTxtConString) dim cmdTxt as ODBCCommand dim drTxt as ODBCDataReader conTxt.Open() cmdTxt = conTxt.CreateCommand() cmdTxt.CommandText = "SELECT * FROM [properties.csv]" cmdTxt.CommandType = CommandType.Text drTxt = cmdTxt.ExecuteReader() Do While drTxt.Read() try response.write("<br>" & drTxt("Number of characters").ToString() & " " & drTxt("Description").ToString()) catch response.write("<br>Error at " & drTxt("Number of characters").ToString() & " characters") exit do finally end try Loop drTxt.Close() conTxt.Close() Here's some code to generate the CSV file that causes the error: dim i as integer dim strDescription as string for i = 1 to 2000 strDescription = strDescription & "a" next response.write(vbcrlf & """Number of characters"", ""Description""" & vbcrlf) for i = 1 to 1000 strDescription = strDescription & "a" response.write(len(strDescription) & ", """ & strDescription & """" & vbcrlf) next And here's the error message that occurs when the number of characters in the description field of the CSV reaches 2047 chars: NO_DATA - no error information available Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. Exception Details: System.Data.Odbc.OdbcException: NO_DATA - no error information available Source Error: Line 53: Do While drTxt.Read() Line 54: 'try Line 55: response.write("<br>" & drTxt("Number of characters").ToString() & " " & drTxt("Description").ToString()) Line 56: 'catch Line 57: response.write("<br>Error at " & drTxt("Number of characters").ToString() & " characters") Thanks very much to anyone who can shed any light on this. Simon Tag: Generating an SqlParameter from a value in a DataSet Tag: 124197
  - 3
    - "EOF" in Data table Hi, I'm just coming from classic ADO (& still thinking that way) & I'm wondering how you can determine when you get to the EOF in a data table. This is what I've done below. It works, but I don't think it's the most elegant way of doing it. How is this normally done? Am I thinking the totally wrong way for ADO.NET? Thanks for any thoughts. Ant int i = 0; private void button1_Click(object sender, System.EventArgs e) { daEmployees.Fill(dsEmployees); try { string empName = dsEmployees.Employees[i++].FirstName; txtEmpName.Text = empName; } catch { // do nothing here } } Tag: Generating an SqlParameter from a value in a DataSet Tag: 124193
  - 4
    - Adding commands at runtime to generated adapters So Iâ??ve got this dataset that was built with the designer. All is well except that the Select command needs to be generated at runtime for app-specific reasons. I thought I would do something clever, which was add a version of Fill() to the dataset class that takes the SQL string as a parameter. But much to my dismay, my designer-built dataset class is frequently rebuilt, presumably to keep it in synch with changes to the table structure. Thatâ??s actually quite a neat little feature but now Iâ??m in a bind. The first line in the generated TableAdapterâ??s Fill() does this: this->Adapter->SelectCommand = this->CommandCollection[0]; Both CommandCollection and the underlying array _commandCollection are private, as is the underlying SqlAdapter of my TableAdapter. Since I canâ??t say CommandCollection[0]=this or Adapter->SelectCommand=that Iâ??m at a bit of a loss as to how Iâ??m supposed to provide a Select statement at runtime. Deriving yet another class from the generated TableAdapter whose sole purpose is to override Fill() seems a little silly. What am I missing here? -- Scott Bruno Shiny Entertainment Tag: Generating an SqlParameter from a value in a DataSet Tag: 124188
  - 5
    - Database with diverse data Can anyone recommend a good whitepaper, strategy, or book on this... I am going to implement a project where I will be getting several very different types of data from third-party vendors; the data will often be in a format only understood by the third-party (encoded). I would like to be able to store the data in whatever form I get it in, plus a few key fields for retrieval (e.g. date). I'm curious about different architectures to use when extracting the data...and at which points to allow algorithms (aka plug-ins) to unencode the data for either presentation or transport to the third-party via web services. Loose coupling of the unencode algorithms from the data is highly desireable since I may have to support lots of unique data sets. Amil Tag: Generating an SqlParameter from a value in a DataSet Tag: 124182
  - 6
    - Where to use connection, dataset, datareader ? Hi, I have some difficulties in understanding how to use the connection, dataset, datareader in a form. Let's suppose I want to create a datareader and when I push a button to bring another row from the dataset. Supposedly I create under MyForm_Load event the connection, then a command and the reader. SqlConnection conn = new SqlConnection(...); conn.open(); SqlCommand cmd = conn.CreateCommnad(); cmd.CommandType = ... cmdCommnadText = .... SqlReader dr = cmd.ExecuteReader(); If I try to use dr.Read() in side a ButtonRead_Click event, dr is not recognized. So, what is the approach here, where I create and use the components to make them work? Thanks, Doru Tag: Generating an SqlParameter from a value in a DataSet Tag: 124178
  - 7
    - EndCurrentEdit does not push changes into the dataset I have a Windows Form written in C# with a non-edit DataGrid showing a list of records. When the user selects a row in the grid, the full details of the record display in a panel which contains textboxes and comboboxes. The underlying datatable that the detail panel is bound to only ever has one record at a given time. When the user edits the detail record and then issues a save command by clicking the save button, all works well. This is because the focus moves from the current control and current record to the button. However if the user issues a Save by pressing the F2 key (caught by the form's ProcessCmdKey), the focus remains in the current control and current record. Under these circumstances, the EndCurrentEdit will not push the changes into the dataset. I tried calling EndCurrentEdit() on each control (in the control leave event) in the following ways: control.BindingContext[dataset.Tables[datatablename]].EndCurrentEdit(); if (control.DataBindings.Count > 0) { control.DataBindings[0].BindingManagerBase.EndCurrentEdit(); } I tried calling EndEdit() on the datatable row dataset.Tables[datatablename].Rows[rownumber].EndEdit() I then implemented a BindingManagerBase as follows: bindMgrBase = this.BindingContext [mydataset, datatablename]; and on save called bindMgrBase.EndCurrentEdit() I tried 1) moving programmatically to the next textbox control 2) moving programmatically back to the datagrid 3) moving programmatically to a button I suspect that the EndCurrentEdit() requires the user to move off the current record. That is difficult to test as the record detail panel only ever has one record at a given time. Is there any way to make this work? Anyone have any ideas for me? ...Susan Tag: Generating an SqlParameter from a value in a DataSet Tag: 124177
  - 8
    - Middle tier Data access object in .net assembly or COM? Hi, I am new in VS.net. We try to migrate our VB6 application to .net. In our VB6 application, we have a middle tier data access object to handle the connection and stored procedures' calls. Do we still do middle tier DO in COM? or in just Assembly? why? Thanks in advance, DW Tag: Generating an SqlParameter from a value in a DataSet Tag: 124167
  - 9
    - Manipulating data on input w/ TableAdapter & Dataset. I know this is easier to do with a datareader and a listview, but can it be done with a tableadapter and a dataset? I want to manipulate the data prior to it reaching the dataset, For instance, data is stored as Firstname and Lastname and sometimes there will be no firstname, so appending a comma in this instance looks stupid. So I need to check if firstname exists then return lastname & ", " & firstname or if not exist, just return lastname. I was trying to do this with an IIF statement in the SQL code but it wouldn't run saying it was an unrecognized command although it is described in the BOL documentation for SQL Server 2000. I also tried to do it with partial class of the dataset. First on RowChanging, e.row = "processed data", error = cannot change proposed value. Next on RowChanged, also met with error. Any ideas, or is this not meant to be? Tag: Generating an SqlParameter from a value in a DataSet Tag: 124166
  - 10
    - [.NET 2.0]Problem with DataTable.Load (dt,LoadOptions.Upsert) Hi, I would like to update an Sql Server database with Excel data. For this, I use2 identical typed datatables: dtSql, and dtXls. Here's my code simplified: TypedDataTable dtXls = new TypedDataTable; TypedDataTable dtSql = new TypedDataTable; dataAdapterXls.Fill (dtXls); dataAdapterSql.Fill (dtSql); DataTableReader drXls = dtXls.CreateDataReader(); dtSql.Load(drXls, LoadOption.Upsert); Some rows in Excel have a primary key (PK1) value that is already present on the Sql database and therefore in dtSql, and I get this error message on those rows: "Column 'PK1'' is constrained to be unique. Value 'xxx'' is already present." The documentation states that when using the option "Upsert" of the Load member : "The incoming values for this row will be written to the current version of each column. The original version of each column's data will not be changed." So to me, those rows should have a current value taken from Excel and a rowstate equal to "Modified"... I don't really understand... Cheers Tag: Generating an SqlParameter from a value in a DataSet Tag: 124165
  - 11
    - TransactionManagerCommunicationException using TransactionScope I'm writing a Windows service that uses the new TableAdapter in .NET 2.0. I have several adapters that must be updated as an atomic operation, so I have wrapped them using the TransactionScope. When I run my code I get a TransactionManagerCommunicationException with the following message "Network access for Distributed Transaction Manager (MSDTC) has been disabled. Please enable DTC for network access in the security configuration for MSDTC using the Component Services Administrative tool." I looked at the server and found a security configuration dialog for MSDTC. When looking at the settings, "Network DTC Access' was enabled. I even tried "No Authentication Required" without any luck. I'm running SQL2005 on 2003 Server. Tag: Generating an SqlParameter from a value in a DataSet Tag: 124162
  - 12
    - SQL Table to DataSet to Dataview, to DataSet to XML! Hi everybody, i've a question for this group: i've developed a windows service that every x minutes count records present in a sql table and if those records are bigger than a fixed threshold start with clean operations. Workflow is this: 1) Load records in a DataSet: DataSet ds = new DataSet(); cmd = "SELECT * FROM " + TableName; ........ adapter.Fill( ds ); 2)Generate a DataView form table inside dataset and order it (i must order. I tryed to order before load table in DataSet, but table is very big and often i had timeout) DataView dv = new DataView(ds.Tables[0]); string order = GetTimeFieldName ( Table ) + " ASC"; dv.Sort = order; 3) Now i need convert data to XML, but not only one XML file, N XML file of 10000 records. To do this i generate N DataTable from DataView, add each DataTable to a New DataSet and call WriteXML method N time. The workflow working fine, but i think is not optimal solution (load dataset, copy to dataview, order dataview, copy again ....... ) , someone have a better idea, or some suggestion to gave me? Tag: Generating an SqlParameter from a value in a DataSet Tag: 124159
  - 13
    - DataSet to Dataview to Dataset to XML! Hi everybody, i've a question for this group: i've developed a windows service that every x minutes count records present in a sql table and if those records are bigger than a fixed threshold start with clean operations. Workflow is this: 1) Load records in a DataSet: DataSet ds = new DataSet(); cmd = "SELECT * FROM " + TableName; ........ adapter.Fill( ds ); 2)Generate a DataView form table inside dataset and order it (i must order. I tryed to order before load table in DataSet, but table is very big and often i had timeout) DataView dv = new DataView(ds.Tables[0]); string order = GetTimeFieldName ( Table ) + " ASC"; dv.Sort = order; 3) Now i need convert data to XML, but not only one XML file, N XML file of 10000 records. To do this i generate N DataTable from DataView, add each DataTable to a New DataSet and call WriteXML method N time. The workflow working fine, but i think is not optimal solution (load dataset, copy to dataview, order dataview, copy again ....... ) , someone have a better idea, or some suggestion to gave me? Tag: Generating an SqlParameter from a value in a DataSet Tag: 124158
  - 14
    - DataAdapter error Hi, I've created a relational group of tables using the designer for the Data Adapter. It decided to cross join all the tables, so I manually did it using Inner joins, nothing special just PK to FK. When I click preview data, then try to fill a dataset created by the Generate Dataset button of the DataAdapter, I get the following error: "The data adapter could not return the data from the data source." "Syntax error converting the varchar value 'myStringValue' to a column of data type int". When I look at the schema of the Dataset, all the columns are varchar. Why is it doing this? Any suggestions/past experiences very welcome & thanks in advance. Ant Tag: Generating an SqlParameter from a value in a DataSet Tag: 124156
  - 15
    - Fitting TableAdapter into Remoting Infrastructure Short question: how to fit TableAdapters into (true) n-tier development. More Info: In VS2003 in terms of *physical* n-tier development, I had a fairly nice pattern going. My DAL exists on a separate server and accessed via remoting. In order for the client machine to "know" about the classes and methods on the server (rather than going the lazy route and installing the server dll's on the client) I have a very lightweight dll that contains "Types and interfaces" that is installed on both the server and client. This DLL acts as a WSDL file of sorts.... it contains datasets, classes, and most importantly *interfaces* for the data access methods on the server (all methods on the server must implement a particular Interface). This is not hard to do and it's great because I can write a new server from the ground up that contains the same methods but totally different implementation without ever touching the client (at all!). But, now in VS2005 / ADO.NET 2.0 I have a problem. I really really really like the TableAdapter classes and the IDE designer for it. But am purplexed that I can't PHYSICALLY separate it from the Dataset. My old pattern called for the Datasets to exist in the "Types and Interfaces" DLL (because the client uses them too) and the DataAdapters to exist on the remoted Server DLL. Anyone have any ideas on how I can adapt TableAdapters to this paradigm? I absolutely hate the idea of putting my data access code on the client... even if it's only going to get executed on the server. It violates the whole notion of N-TIER development (logical layers is not the same as N-Tier... though everyone seems to have forgotten this... including Microsoft). P.S. I know I can continue to use DataAdapters. But, TableAdapters are way easier to configure. Tag: Generating an SqlParameter from a value in a DataSet Tag: 124155
  - 16
    - Table name in Dataset Hi, When I create a typed dataset containing multiple relational tables using Generate dataset, it creates a single set of records based on what was chosen when setting up the data adapter. If I have an orders table, an orderItems table, a customer table etc. set up in the DataAdapter, the dataset chooses one of these tables names as the name that intellisense exposes as the set of records. This can be changed manually when viewing the schema. My question is, how does it choose which table name to use for this combined set of columns? Thanks for your thoughts on this Ant Tag: Generating an SqlParameter from a value in a DataSet Tag: 124150
  - 17
    - General network error Hi I am supporting a client that gets 5-10 of the following errors each day: General network error. Check your network documentation. ----------------------------- Stack trace: at System.Data.SqlClient.ConnectionPool.GetConnection(Boolean& isInTransaction) at System.Data.SqlClient.SqlConnectionPoolManager.GetPooledConnection(SqlConnectionString options, Boolean& isInTransaction) at System.Data.SqlClient.SqlConnection.Open() They are running SQLServer 2000 on a Windows 2003 Server domain. The applications always use the following connection string: "Server=MySqlServer;Integrated Security=SSPI;Pooling=true;Initial Catalog=MyDatabase" Someone has verified that: - every SqlConnection.Open() has a corresponding SqlConnection.Close() - every SqlReader.ExecuteReader() has a corresponding SqlReader.Close() All code is enclosed in try/catch blocks - there are no other exceptions occurring One blog I found suggested turning off pooling, another suggested setting Max Pool Count to some very high number like 5000. I would very much like to understand the cause of the problem before I start trying things at random. Can anyone help? Thanks in advance. Valerie Hough Tag: Generating an SqlParameter from a value in a DataSet Tag: 124149
  - 18
    - GetChanges question Can someone please tell me how the following is possible? When I do this: System.Data.DataSet addedAndModified = myDataset.GetChanges( DataRowState.Added | DataRowState.Modified ); some of the rows in the resulting dataset have a RowState of DataRowState.Deleted. Tag: Generating an SqlParameter from a value in a DataSet Tag: 124145
  - 19
    - DBAccess in WinForms Hi EveryOne, What is the best approach for data access while developing applications using Windows Forms ? Is it opening, working with and closing a new SQLconnection and SQLcommand object everytime a DB access is needed , or maintaining a single open connection/command object at the application level ( one for each user ) and then just reusing it again and again. The connection string that the connection will use can be assumed to be static. -- Vedanshu Mandvikar Tag: Generating an SqlParameter from a value in a DataSet Tag: 124144
  - 20
    - Error saving typed dataset (.NET 2.0) - Object not set to referenc I have an ASP.NET project with a typed dataset (with table adapters). The table adapters' connection string property was set to get the connection strings out of the web.config file. I was able to add new table adapters, and all worked well for a while. Now I need to add another table adapter and cannot do it. When I try to add a new one, the connection strings from the web.config file no longer show up in the wizard's connection string dropdown. Also, all of the existing table adapters' connection string properties say "Unable to find connection 'dbname' for object 'web.config'. The connection string could not be found in application settings, or the data provider associated with the connection string could not be loaded. Oddly enough, if I run the project, the existing table adapters have no problem connecting to the database. Since I couldn't add a new table adapter, I figured I'd try it the old-fashioned way and just add a DataTable and update it in the code (I've had to work around many bugs in VS2005, so I'm used to it). However, when I try to add a DataTable and click "Save", I get an "Object not set to a reference of an object." messagebox. When I click on "View Code" for the dataset, I get a couple more "Object not set..." message boxes, then the schema loads. Then I notice there are over 100 warnings aobut attributes not being declared. I can delete the entire dataset and rebuild it from scratch, and it will work fine for a little while. After a few days though, it gets back to this error. Any ideas? Tag: Generating an SqlParameter from a value in a DataSet Tag: 124137
  - 21
    - SqlTransaction problems Hi Everyone, I'm currently working on a form that requires 3 stored procs to run (one of them several times), but these stored procs are in different functions (see below). The problem is that after the commit, the database doesn't reflect any of the changes. I open a new connection before calling a proc, and then pass the same connection to each function and within the function, I set the transaction to the Command object. Here is the code shortened: Private Sub cmdSave_Click( ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdSave.Click Dim cnSQL as SqlConnection Dim Success as Boolean = True try cnSQL = OpenConnection() Success = SaveAdmissionNursingAssessment1(cnSQL) if Success = True then Success = SaveFamilyHistory(cnSQL) if not Success then SaveTrans.Dispose else Success = SaveAdmissionNursingAssessment2(cnSQL) if not Success then SaveTrans.Dispose End If End If else cnSQL.Dispose end if cmdAdd.Enabled = True cmdSave.Enabled = False cmdCancel.Enabled = False Catch ex As Exception msgbox(ex.Message ) End Try End Sub Private Function SaveAdmissionNursingAssessment1(cnSQL As SqlConnection) as Boolean Dim success as Boolean = true Dim cmSQL As SqlCommand Try SaveTrans = cnSQL.BeginTransaction if isnew then cmSQL = New SqlCommand("intake_AddAdminNurseAssess1", cnSQL) cmsql.Transaction = SaveTrans else cmSQL = New SqlCommand("intake_UpdAdmissionNursingAssessment1", cnSQL) cmsql.Parameters.Add("KeyID", sqldbtype.Int ).Value = fAssessId cmsql.Transaction = SaveTrans end if cmsql.CommandType = CommandType.StoredProcedure cmsql.Parameters.Add("PatientID", sqldbtype.Varchar, 20 ).Value = fPatientId cmsql.Parameters.Add("PreparedDate", sqldbtype.SmallDateTime ).Value = Now() cmSQL.ExecuteNonQuery() Catch Exp As SqlException MsgBox(Exp.Message, MsgBoxStyle.Critical, "SQL Error") SaveTrans.Rollback success = False Catch Exp As Exception MsgBox(Exp.Message, MsgBoxStyle.Critical, "General Error") SaveTrans.Rollback success = False finally ' Close and Clean up objects cmSQL.Dispose() SaveAdmissionNursingAssessment1 = success End Try End Function Private Function SaveAdmissionNursingAssessment2(cnSQL As SqlConnection) as Boolean Dim success as Boolean = True Dim cmSQL As SqlCommand Try if isnew then cmSQL = New SqlCommand("intake_AddAdminNurseAssessment2", cnSQL) cmsql.Transaction = SaveTrans isnew = False else cmSQL = New SqlCommand("intake_UpdAdmissionNursingAssessment2", cnSQL) cmsql.Parameters.Add("KeyID", sqldbtype.Int ).Value = fAssessId cmsql.Transaction = SaveTrans isDirty = False end if cmsql.CommandType = CommandType.StoredProcedure cmsql.Parameters.Add("PatientID", sqldbtype.Varchar, 20 ).Value = fPatientId cmSQL.ExecuteNonQuery() SaveTrans.Commit Catch Exp As SqlException MsgBox(Exp.Message, MsgBoxStyle.Critical, "SQL Error") SaveTrans.Rollback success = False Catch Exp As Exception MsgBox(Exp.Message, MsgBoxStyle.Critical, "General Error") SaveTrans.Rollback success = False finally ' Close and Clean up objects cnSQL.Close() cmSQL.Dispose() cnSQL.Dispose() SaveTrans.Dispose SaveAdmissionNursingAssessment2 = success End Try End Function Private Function SaveFamilyHistory(cnSQL As SqlConnection) as Boolean 'Dim cnSQL As SqlConnection Dim success as Boolean = true Dim cmSQL As SqlCommand Dim i as Integer Try For i = 2 To flxFamilyHistory1.Rows.Count - 1 if len(trim(flxFamilyHistory1(i, 1))) > 0 or len(trim(flxFamilyHistory1(i, 3))) > 0 then if not isnumeric(flxFamilyHistory1(i, 6)) then cmSQL = New SqlCommand("intake_AddANFamilyHistory", cnSQL) cmsql.Transaction = SaveTrans else cmSQL = New SqlCommand("intake_UpdANFamilyHistory", cnSQL) cmsql.Parameters.Add("@HistId", sqldbtype.int).Value = flxFamilyHistory1(i, 0) cmsql.Transaction = SaveTrans end if cmsql.CommandType = CommandType.StoredProcedure cmsql.Parameters.Add("@AssessId", sqldbtype.Int ).Value = fAssessId cmsql.Parameters.Add("@PatientID", sqldbtype.Varchar, 20 ).Value = fPatientId cmsql.Parameters.Add("@Age", sqldbtype.Varchar, 5 ).Value = flxFamilyHistory1(i, 1) cmsql.Parameters.Add("@Health", sqldbtype.Varchar, 25 ).Value = flxFamilyHistory1(i, 2) cmsql.Parameters.Add("@AgeOfDeath", sqldbtype.Varchar, 50 ).Value = flxFamilyHistory1(i, 3) cmsql.Parameters.Add("@CauseOfDeath", sqldbtype.Varchar, 50 ).Value = flxFamilyHistory1(i, 4) cmsql.Parameters.Add("@Who", sqldbtype.Varchar, 50 ).Value = flxFamilyHistory1(i, 5) cmsql.Parameters.Add("@SortOrder", sqldbtype.Varchar, 50 ).Value = i cmSQL.ExecuteNonQuery() End If Next Catch Exp As SqlException MsgBox(Exp.Message, MsgBoxStyle.Critical, "SQL Error") SaveTrans.Rollback success = False Catch Exp As Exception MsgBox(Exp.Message, MsgBoxStyle.Critical, "General Error") SaveTrans.Rollback success = False finally cmSQL.Dispose() SaveFamilyHistory = success End Try end function Thanks for any info. Is there a better way to handle this when crossing function with our code. Thanks so much for any info. Michael Tag: Generating an SqlParameter from a value in a DataSet Tag: 124136
  - 22
    - cannot connect to an oracle Hi I have problems when I try to connect to my Oracle db from VS 2003. I get this error: ORA-12154: TNS:could not resolve the connect identifier specified My tnsnames.ora-file I have this text: MALL = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = Z9100774.ne.ad.ner.capgemini.com)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = mall) ) ) What can be the problem? Thanks Julia Tag: Generating an SqlParameter from a value in a DataSet Tag: 124132
  - 23
    - DataReader Limits data returned! I have a stored procedure that returns xml data (using For XML), it returns about 9000 records. I use a datareader to write the data to a XML file. For some reason, the datareader doesn't write all data, it seems like the data returned has some size limit. here is my code, my code is based on a msdn example for using getBytes were it writes data to the stream by chunks based on the buffer size: '----some code here to fill in the stored procedure name and parameters. Dim mydatareader As SqlDataReader connectionObj.Open() mydatareader = CommandObj.ExecuteReader Dim fs As FileStream Dim bw As BinaryWriter Dim bufferSize As Integer = 100 Dim outChar(bufferSize - 1) As Char Dim retval As Long Dim startindex As Long = 0 If mydatareader.Read Then fs = New FileStream("c:\test.xml", FileMode.OpenOrCreate, FileAccess.Write) bw = New BinaryWriter(fs) retval = mydatareader.GetChars(0, startindex, outChar, 0, 100) ' Continue reading and writing while there are bytes beyond the size of the buffer. Do While retval = bufferSize 'bw.Write(outByte) bw.Write(outChar) bw.Flush() ' Reposition the start index to the end of the last buffer and fill the buffer. startindex += bufferSize retval = mydatareader.GetChars(0, startindex, outChar, 0, bufferSize) Loop 'Write the remaining buffer. bw.Write(outChar, 0, CType(retval - 1, Integer)) bw.Flush() ' Close the output file. bw.Close() fs.Close() End If connectionObj.Close() connectionObj.Dispose() CommandObj.Dispose() Tag: Generating an SqlParameter from a value in a DataSet Tag: 124120
  - 24
    - click event handler executed twice Hi everyone: I got simple aspx with a user control in it that posts some entered data to a database. The problem I'm seeing is that when the submit button is clicked the code is executed twice and I get duplicate rows in the database. The including aspx page is this: <%@ Page language="c#" Codebehind="test.aspx.cs" AutoEventWireup="false" Inherits="DirectedInsight.test" %> <%@ Register TagPrefix="uc1" TagName="comments" Src="includes/comments.ascx" %> <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN" > <html> <head> <title>test</title> <meta name="GENERATOR" content="Microsoft Visual Studio .NET 7.1"> <meta name="CODE_LANGUAGE" content="C#"> <meta name="vs_defaultClientScript" content="JavaScript"> <meta name="vs_targetSchema" content="http://schemas.microsoft.com/intellisense/ie5"> </head> <body ms_positioning="GridLayout"> <form id="Form1" method="post" runat="server"> <uc1:comments id="Comments1" runat="server"></uc1:comments> </form> </body> </html> The user control looks like this: <%@ Control Language="c#" AutoEventWireup="false" Codebehind="comments.ascx.cs" Inherits="DirectedInsight.includes.comments" TargetSchema="http://schemas.microsoft.com/intellisense/ie5"%> <table class="normalText"> <tr> <td>Your name: </td> <td><asp:textbox id="submitName" runat="server"></asp:textbox></td> <td>Your email: </td> <td><asp:textbox id="submitEmail" runat="server"></asp:textbox></td> </tr> <tr> <td>Comment: </td> <td colspan="3"><asp:textbox id="comment" runat="server" textmode="MultiLine" rows="6" columns="50"></asp:textbox></td> </tr> <tr> <td align="center" colspan="4"> <asp:button id="Button1" onclick="submitComment" runat="server" cssclass="commonButton" text="Submit Comment"></asp:button> </td> </tr> </table> And the event handler is this: public void submitComment(object sender, System.EventArgs e) { string connStr = "SERVER=SQLServer;UID=USER;PWD=Password;DATABASE=Database;"; SqlConnection conn = new SqlConnection(connStr); conn.Open(); SqlCommand cmd = new SqlCommand(); cmd.Connection = conn; cmd.CommandText = "insertComment"; cmd.CommandType = CommandType.StoredProcedure; SqlParameter param = cmd.Parameters.Add("@articleID",SqlDbType.Int, 4); param.Direction = ParameterDirection.Input; param.Value = cArticleID; param = cmd.Parameters.Add("@submitName",SqlDbType.VarChar, 30); param.Direction = ParameterDirection.Input; param.Value = submitName.Text; param = cmd.Parameters.Add("@submitEmail",SqlDbType.VarChar, 100); param.Direction = ParameterDirection.Input; param.Value = submitEmail.Text; param = cmd.Parameters.Add("@comment",SqlDbType.Text, 1000); param.Direction = ParameterDirection.Input; param.Value = comment.Text; cmd.ExecuteNonQuery(); } The stored procedure is single insert statement. Everytime I click the submit button I end up with two identical rows in the database. I've googled and can find nothing similar on it. Any help would be greatly appreciated!! thanks in advance Dave Tag: Generating an SqlParameter from a value in a DataSet Tag: 124119
  - 25
    - Document locking concept ? Hi ! I have problem with locking concept in my application (ADO.NET , C#). Application has several documents which looks like Order.My question is what and how should I lock those documents. 1.Lock document as a whole (Order + OrderLines) so another user can't work on this document (pessimistic lock)? 1.1. How to implement this on SQL server ? 2.Should I use optimistic locking on Order and OrderLines ? I3. If I use optimistic locking on OrderLines should I each time update timestamp of Order and by each insert, update and read of OrderLine check whether somebody has changed this Order and/or OrderLine which is both possible ? Any thoughts are appreciated! Tag: Generating an SqlParameter from a value in a DataSet Tag: 124113

How can I generate a Parameter if I cannot retrieve the SqlDbType from
an item in a row of a DataSet?
Is it safe to just take the object I retrieve from the DataSet and take
the following constructor for the Parameter:
SqlParameter(string parameterName, object value)???
Regards
Stephan

Top

Re: Generating an SqlParameter from a value in a DataSet by W

W
Tue Jan 31 11:51:05 CST 2006

Yep, that's 'safe'
"Stephan Zaubzer" <stephan.zaubzer@schendl.at> wrote in message
news:eoVrRfoJGHA.3944@tk2msftngp13.phx.gbl...
> How can I generate a Parameter if I cannot retrieve the SqlDbType from an
> item in a row of a DataSet?
> Is it safe to just take the object I retrieve from the DataSet and take
> the following constructor for the Parameter:
> SqlParameter(string parameterName, object value)???
> Regards
> Stephan

Top