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:
The code doesn't error but it doesn't create the record in the SQL DB either. If I use
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
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