Question Inserting XML to SQL server via Dataset

tknight

Member
Joined
Sep 20, 2010
Messages
7
Programming Experience
Beginner
Hi, as you can probably tell from post #1 i'm a bit of a .net newbie but i'm used to coding and forums and the like so hopefully I won't have to bug you guys too much!

Anyway, my first real foray into a win forms app has not been the gentlest introduction but i'm almost there. I need to connect to a web service (through proxy), retreive XML, and insert into SQL server DB. The web service bit is all sorted and I have the XML but now I am having trouble actually getting it into the SQL DB. I decided to create an empty DataTable from the SQL table I want to populate (becuase it's currently empty), then populate with the XML and then update back to the DB with a DataAdapter. Here's what I have so far:

VB.NET:
 Dim Connection As New SqlConnection(".....Connection....String.....")
    
        Dim da As SqlDataAdapter
        Dim ds As DataSet
        Dim dt As DataTable

        Connection.Open()
        da = New SqlDataAdapter()
        da.SelectCommand = New SqlCommand("SELECT Transactions.* FROM tknight.Transactions as Transactions", Connection)

        Dim cb As SqlCommandBuilder = New SqlCommandBuilder(da)

        ds = New DataSet
        da.Fill(ds, "Transactions")
        dt = New DataTable
        dt = ds.Tables("Transactions")
      
[COLOR="green"]' web service code here irrelevant will pick up xml file locally instead for now[/COLOR]

        ds.ReadXml("C:\DotNetTransactions.xml")
        ds.AcceptChanges()
        
[COLOR="green"]' Check that "Transactions" has been populated
' Correctly Returns 1 row and the ID of that row[/COLOR]
        Debug.Print(ds.Tables("Transactions").Rows.Count.ToString())
        Debug.Print(ds.Tables("Transactions").Rows(0).Item("UniqueID").ToString())

        da.Update(ds, "Transactions")

        Connection.Close()

        MsgBox("Done!")

The code doesn't error but it doesn't create the record in the SQL DB either. If I use

VB.NET:
Debug.Print(cb.GetInsertCommand().CommandText)

I can see that it writes a correct insert statement e.g. INSERT INTO [tknight].[Transactions] ([UniqueID], [PeopleID], [SiteLoginID], [SiteLogoutID].......VALUES (@P1, @P2, @P3....

And as the column order of the XML is identical to the SQL table I was hoping .net would be clever enough to just slot the records into the DB. Is that how it is supposed to work or do I have to iterate through the rows of the DataTable and manually assign the parameters for each row before calling the update? I hope not becuase there are 46 fields! If it makes a difference there is a PK on the SQL table (UniqueID).

Any pointers as to where i'm going wrong would be much appreciated.

Thanks,

Tom
 

jmcilhinney

VB.NET Forum Moderator
Staff member
Joined
Aug 17, 2004
Messages
14,742
Location
Sydney, Australia
Programming Experience
10+
The problem is the fact that you're calling AcceptChanges. That sets the RowState of every DataRow to Unchanged, indicating that there are no changes to save. If you intend to insert the data then simply get rid of that call and leave all the RowStates as Added, meaning that they are new rows ready to insert.

By the way, if you don;t actually want any of the existing data from the database then call FillSchema instead of Fill.
 

tknight

Member
Joined
Sep 20, 2010
Messages
7
Programming Experience
Beginner
Thank you - that's sorted it. That line was in there from my semi-random exploration of datasets... I didn't even think it could be the problem after data had got into the table. (Note to self: Intellisense is just to help you code quickly not so much to teach you stuff!)

And thanks for the fill schema tip too...

Cheers,

Tom
 
Top Bottom