• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 312
  • Last Modified:

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

0
888_ryan_888
Asked:
888_ryan_888
1 Solution
 
Solar_FlareCommented:
what you need to do is to set the command of the dataadapter


 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.InsertCommand = new sqlcommandbuilder
                    *** daTest.Update(dsTest, "Test")
0
 
888_ryan_888Author Commented:
                   
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")
0
 
newyuppieCommented:
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
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: CompTIA Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now