asked on

Insert Row into sql dbo from Dataset using Data Adapter

Hello,

I'm attempting to insert a row into an sql dbo.

I receive the following error at the asterik point below. "Update requires a valid InsertCommand when passed DataRow collection with new rows."

Can you show me the nature of the insertCommand or whatever code is necessary to add the row to the original database?

' Test Insert
Dim sTestSelect As String = "Select * From Test"
Dim daTest As New SqlDataAdapter(sTestSelect, conRyan)
Dim dsTest As New DataSet()
daTest.Fill(dsTest, "Test")

Dim wsPol As String = "CWO8888888"
Dim wsLob As String = "0001"
Dim wsAgent As String = "0001"

Dim drTest As DataRow = dsTest.Tables("Test").NewRow
drTest("Policy") = wsPol
drTest("Lob") = wsLob
drTest("Agent") = wsAgent
dsTest.Tables("Test").Rows.Add(drTest)
*** daTest.Update(dsTest, "Test")

Ryan

ASKER CERTIFIED SOLUTION

Solar_Flare

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

888_ryan_888

ASKER

Nice thanks, That worked fine.

I'm brain fried today.

Here is the code that I ended up with.

Dim wsPol As String = "CWO8888888"
Dim wsLob As String = "0001"
Dim wsAgent As String = "0001"

Dim sTestSelect As String = "Select * From Test"
Dim sTestUpdate As String = "Insert Into Test (policy, lob, agent) Values ('" + wsPol + "','" + wsLob + "','" + wsAgent + "')"
Dim cmdTestUpdate As New SqlCommand(sTestUpdate, conRyan)
Dim daTest As New SqlDataAdapter(sTestSelect, conRyan)
Dim dsTest As New DataSet()
daTest.Fill(dsTest, "Test")

Dim drTest As DataRow = dsTest.Tables("Test").NewRow
drTest("Policy") = wsPol
drTest("Lob") = wsLob
drTest("Agent") = wsAgent
dsTest.Tables("Test").Rows.Add(drTest)
Dim cbTest As New SqlCommandBuilder(daTest)
daTest.InsertCommand = cmdTestUpdate
daTest.Update(dsTest, "Test")

newyuppie

solarflare,
i think per recommendation of MSDN, (http://msdn2.microsoft.com/en-us/library/system.data.sqlclient.sqlcommandbuilder.aspx)

this line you put in his code
daTest.InsertCommand = new sqlcommandbuilder
should read
Dim builder As SqlCommandBuilder = New SqlCommandBuilder(daTest)
builder.GetUpdateCommand()
daTest.Update(dsTest, "Test")

NY