Using XML to read data from SQL Server

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
    - 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: Using XML to read data from SQL Server Tag: 124288
  - 2
    - 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: Using XML to read data from SQL Server Tag: 124282
  - 3
    - 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: Using XML to read data from SQL Server Tag: 124275
  - 4
    - 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: Using XML to read data from SQL Server Tag: 124274
  - 5
    - 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: Using XML to read data from SQL Server Tag: 124270
  - 6
    - 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: Using XML to read data from SQL Server Tag: 124256
  - 7
    - 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: Using XML to read data from SQL Server Tag: 124248
  - 8
    - 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: Using XML to read data from SQL Server Tag: 124247
  - 9
    - 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: Using XML to read data from SQL Server Tag: 124246
  - 10
    - 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: Using XML to read data from SQL Server Tag: 124244
  - 11
    - 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: Using XML to read data from SQL Server Tag: 124229
  - 12
    - 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: Using XML to read data from SQL Server Tag: 124214
  - 13
    - 0(n ***Hot stuff - check this out !!! 0(n http://www.kaneva.com/checkout/stream.aspx?assetId=2017&free=0 Tag: Using XML to read data from SQL Server Tag: 124206
  - 14
    - 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: Using XML to read data from SQL Server Tag: 124205
  - 15
    - 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: Using XML to read data from SQL Server Tag: 124203
  - 16
    - 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: Using XML to read data from SQL Server Tag: 124201
  - 17
    - 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: Using XML to read data from SQL Server Tag: 124199
  - 18
    - 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: Using XML to read data from SQL Server Tag: 124198
  - 19
    - 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: Using XML to read data from SQL Server Tag: 124197
  - 20
    - "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: Using XML to read data from SQL Server Tag: 124193
  - 21
    - 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: Using XML to read data from SQL Server Tag: 124188
  - 22
    - 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: Using XML to read data from SQL Server Tag: 124182
  - 23
    - 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: Using XML to read data from SQL Server Tag: 124178
  - 24
    - 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: Using XML to read data from SQL Server Tag: 124177
  - 25
    - 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: Using XML to read data from SQL Server Tag: 124167
- Next
  - 1
    - 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: Using XML to read data from SQL Server Tag: 124166
  - 2
    - [.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: Using XML to read data from SQL Server Tag: 124165
  - 3
    - 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: Using XML to read data from SQL Server Tag: 124162
  - 4
    - 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: Using XML to read data from SQL Server Tag: 124159
  - 5
    - 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: Using XML to read data from SQL Server Tag: 124158
  - 6
    - 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: Using XML to read data from SQL Server Tag: 124156
  - 7
    - 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: Using XML to read data from SQL Server Tag: 124155
  - 8
    - 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: Using XML to read data from SQL Server Tag: 124150
  - 9
    - 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: Using XML to read data from SQL Server Tag: 124149
  - 10
    - 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: Using XML to read data from SQL Server Tag: 124145
  - 11
    - 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: Using XML to read data from SQL Server Tag: 124144
  - 12
    - 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: Using XML to read data from SQL Server Tag: 124137
  - 13
    - 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: Using XML to read data from SQL Server Tag: 124136
  - 14
    - 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: Using XML to read data from SQL Server Tag: 124132
  - 15
    - 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: Using XML to read data from SQL Server Tag: 124120
  - 16
    - 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: Using XML to read data from SQL Server Tag: 124119
  - 17
    - 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: Using XML to read data from SQL Server Tag: 124113
  - 18
    - copy selected row from a datagridview to another I'm trying to copy seleced rows from dgv1 datagridview, bound to dsOrders1 dataset to dgb2 datagridview, bound to dsOrders2 dataset both dsOrders1 and dsOrders2 are instance of dsOrders dataset schema. Can you help me? Thanks. Tag: Using XML to read data from SQL Server Tag: 124112
  - 19
    - question on sql Dear all 1.)How can i save the stored procedure in sql 2005 server, into server and NOT in file location 2.)How can create folder in stored procedure to separate categories? 3.)How can view the created date and modify date of stored pro Tag: Using XML to read data from SQL Server Tag: 124111
  - 20
    - TransactionScope and DataSets Hi, I'm wondering what the official Microsoft recommendation is for handling TransactionScope transactions when using TableAdapters within a DataSet. I have a dataset that various areas of my business logic uses, and at a higher level I'd like to wrap several business functions in a TransactionScope transaction. The problem is, each TableAdapter has an InitConnection method generated that creates a _new_ Connection to the database. If a second connection gets used within the TransactionScope, the transaction will needlessly be promoted to a distributed transaction. My current workaround is to alter the Dataset.Designer.cs generated file manually to change how Connections are created, but that's somewhat painful sin ce the file gets regenerated automatically. Thanks for any advice, Kirk Tag: Using XML to read data from SQL Server Tag: 124103
  - 21
    - What causes SqlCommandBuilder to not build commands? Language is C++ and my code is pretty much right out of the documentation example, yet I'm not ending up with any generated commands. In the following, assume the relevant objects are members of a form class and this is happening on the form's load event: m_SqlConnection = gcnew SqlConnection( strConnection ); m_SqlAdapter = gcnew SqlDataAdapter( strQuery, m_SqlConnection ); m_SqlCommandBuilder = gcnew SqlCommandBuilder( m_SqlAdapter ); m_DataSet = gcnew DataSet(); m_SqlAdapter->Fill( m_DataSet, "SomeTable" ); My dataset is correctly populated; I can see the data both in the debugger and in the controls I bind to the dataset. My adapter, however, has "undefined value" for every command except the SelectCommand, which I provided myself in the constructor with strQuery. Does anyone know why this might be the case? No exceptions are thrown from the above code so there's no indication why the thing failed. Tag: Using XML to read data from SQL Server Tag: 124100
  - 22
    - OLEDB Exception: The search key was not found in any record Greetings! I've written a console application to perform some trivial database routines. When I run the application, it catches the an OleDbException that states "The search key was not found in any record." My C# code follows: =============== snip ========================== OleDbCommand cmd = new OleDbCommand(sqlstmt, new OleDbConnection(cnct)); try { cmd.Connection.Open(); cmd.ExecuteNonQuery(); cmd.Connection.Close(); } catch(Exception e) { Console.WriteLine(e.Message); Console.Read(); // used to pause application long enough to read message } cmd.Dispose(); =============== snip ========================== sqlstmt = "DELETE FROM Accounts WHERE AccountID > 3" cnct = "PROVIDER=MICROSOFT.JET.OLEDB.4.0;DATA SOURCE=data_source;Persist Security Info=False;Jet OLEDB:Database Password=password" where data_source is the fully qualified local path of the database and password is, well, obvious (I hope). I've checked the database and it contains the Accounts table and AccountID column. The AccountID column does have numbers greater than three. I have run this application before in the past with success. It is an enigma to me why it is no longer functioning. I did recently install the .NET Framework 2.0 update, but even after uninstalling it, I still received the exception. Any assistance that could be provided would be greatly appreciated. Thanks. Tim Tag: Using XML to read data from SQL Server Tag: 124095
  - 23
    - "Specified cast is not valid" when generating dataset I have a dataset defined as follows: <?xml version="1.0" standalone="yes" ?> <xs:schema id="dsEscheatment" targetNamespace="http://www.tempuri.org/dsEscheatment.xsd" xmlns:mstns="http://www.tempuri.org/dsEscheatment.xsd" xmlns="http://www.tempuri.org/dsEscheatment.xsd" xmlns:xs="http://www.w3.org/2001/XMLSchema" xmlns:msdata="urn:schemas-microsoft-com:xml-msdata" attributeFormDefault="qualified" elementFormDefault="qualified"> <xs:element name="dsEscheatment" msdata:IsDataSet="true"> <xs:complexType> <xs:choice maxOccurs="unbounded"> <xs:element name="Payee"> <xs:complexType> <xs:sequence> <xs:element name="PayeeID" type="xs:int" /> <xs:element name="PayeeFirstName" type="xs:string" minOccurs="0" /> <xs:element name="PayeeLastName" type="xs:string" minOccurs="0" /> <xs:element name="PayeeFullName" type="xs:string" minOccurs="0" /> <xs:element name="PayeeStreetAddress1" type="xs:string" minOccurs="0" /> <xs:element name="PayeeStreetAddress2" type="xs:string" minOccurs="0" /> <xs:element name="PayeeCity" type="xs:string" minOccurs="0" /> <xs:element name="PayeeState" type="xs:string" /> <xs:element name="PayeeZip" type="xs:string" minOccurs="0" /> <xs:element name="PayeeTIN" type="xs:string" minOccurs="0" /> </xs:sequence> </xs:complexType> </xs:element> <xs:element name="FundTransfer"> <xs:complexType> <xs:sequence> <xs:element name="TransferID" msdata:ReadOnly="true" msdata:AutoIncrement="true" type="xs:int" /> <xs:element name="UnitNumber" type="xs:string" /> <xs:element name="BankName" type="xs:string" /> <xs:element name="BankAccountNumber" type="xs:string" /> <xs:element name="TransferType" type="xs:string" /> <xs:element name="RemitCheckNumber" type="xs:int" /> <xs:element name="RemitCheckAmount" type="xs:decimal" /> <xs:element name="PreparedByNetworkName" type="xs:string" /> <xs:element name="PreparedByName" type="xs:string" /> <xs:element name="DateSubmitted" type="xs:dateTime" minOccurs="0" /> <xs:element name="Status" type="xs:string" minOccurs="0" /> </xs:sequence> </xs:complexType> </xs:element> <xs:element name="FundTransferPayee"> <xs:complexType> <xs:sequence> <xs:element name="TransferID" type="xs:int" minOccurs="0" /> <xs:element name="PayeeID" type="xs:int" minOccurs="1" maxOccurs="2" /> <xs:element name="AmountRemitted" type="xs:decimal" /> <xs:element name="DueDiligenceDate" type="xs:date" nillable="true" /> <xs:element name="OutstandingCheckDate" type="xs:date" minOccurs="0" /> <xs:element name="OutstandingCheckNumber" type="xs:int" minOccurs="0" /> <xs:element name="OutstandingCaseNumber" type="xs:string" minOccurs="0" /> <xs:element name="EscrowDate" type="xs:date" minOccurs="0" /> <xs:element name="EscrowCaseNumber" type="xs:string" minOccurs="0" /> <xs:element name="CareOfName" type="xs:string" minOccurs="0" /> <xs:element name="StateOwnerCode" type="xs:string" minOccurs="0" /> </xs:sequence> </xs:complexType> </xs:element> </xs:choice> </xs:complexType> <xs:unique name="FundTransfer_Constraint1" msdata:ConstraintName="Constraint1" msdata:PrimaryKey="true"> <xs:selector xpath=".//mstns:FundTransfer" /> <xs:field xpath="mstns:TransferID" /> </xs:unique> <xs:keyref name="FundTransferFundTransferPayee" refer="mstns:FundTransfer_Constraint1" msdata:IsNested="true" id="PayeeRelation"> <xs:selector xpath=".//mstns:FundTransferPayee" /> <xs:field xpath="mstns:TransferID" /> </xs:keyref> <xs:key name="PayeeIDKey" msdata:PrimaryKey="true"> <xs:selector xpath=".//mstns:Payee" /> <xs:field xpath="mstns:PayeeID" /> </xs:key> <xs:keyref name="ItemPayee" refer="mstns:PayeeIDKey"> <xs:selector xpath=".//mstns:FundTransferPayee" /> <xs:field xpath="mstns:PayeeID" /> </xs:keyref> </xs:element> </xs:schema> when I compile my program I receive the following message: "Custom tool error: Unable to convert input xml file content to a DataSet. Specified cast is not valid." It will compile successfully if I remove the "ItemPayee" relation. What am I missing here? Thanks, Preston Tag: Using XML to read data from SQL Server Tag: 124091
  - 24
    - JOIN on 2 datasets? Can I code a JOIN on 2 datasets the same as 2 tables in a database or do I have to retrieve the table by index or something of the table and JOIN those? There's only 1 table in each ds... Tag: Using XML to read data from SQL Server Tag: 124089
  - 25
    - Default DateTime Value in a column Hi, Im creating an in memory DataTable. I have a "dtCreated" column that I want to be populated with the current date and time when a record is added. I dont want to code this every time I add a record. Is there a way to set the defaultValue of the column so that is always inserts the current date time when a record is added. I want this to work like a SQL Server column that has "getdate()" as the default value. Thanks in advance... Tag: Using XML to read data from SQL Server Tag: 124084

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.

Top