Question Update requires a valid InsertCommand when passed DataRow collection with new Rows?

ms960

New member
Joined
Oct 18, 2011
Messages
1
Programming Experience
Beginner
I'm still pretty new at .Net and database coding, and am working on a small program that is generating an error and I'm not sure how to resolve it.

My code:

VB.NET:
Public Class MainForm
    Private m_cn As New SqlConnection()
    Private m_DA As SqlDataAdapter
    Private m_CB As SqlCommandBuilder
    Private m_DataTable As New DataTable
    Private m_rowPosition As Integer = 0
 

    Private Sub MainForm_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        m_cn.ConnectionString = "Data Source=.\SQLEXPRESS; AttachDbFilename = C:\Temp\Test.mdf;Integrated Security=True;Connect Timeout=30;User Instance=True"
        m_cn.Open()
        m_DA = New SqlDataAdapter("Select * from Contacts", m_cn)
        m_DA.Fill(m_DataTable)
        Me.ShowCurrentRecord()
    End Sub
 
    Private Sub btnAddNew_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnAddNew.Click
        Dim drNewRow As DataRow = m_DataTable.NewRow()
        drNewRow("ContactName") = txtNewContactName.Text
        drNewRow("State") = txtNewState.Text
        m_DataTable.Rows.Add(drNewRow)

        m_DA.Update(m_DataTable)
        m_rowPosition = m_DataTable.Rows.Count - 1
        Me.ShowCurrentRecord()
    End Sub
End Class

The following statement is giving me the error below: m_DA.Update(m_DataTable)
"Update requires a valid InsertCommand when passed DataRow collection with new Rows"


I can't for the life of me figure this out. I know the answer is going to be very simple, but I'm at a loss. I would greatly appreciate it if someone could point out the flaw in my code and how to correct it. And yes, I have done fairly extensive searching on this error, but none of them give any idea how to fix it, they just hint around at what's wrong.

Thanks!
 

s1ckOh

Well-known member
Joined
Aug 1, 2011
Messages
68
Location
San Diego, Ca
Programming Experience
Beginner
The error is saying something like... if you want to insert the changes into your database use a valid insert command eg...

VB.NET:
Dim sc As New SqlCommand("INSERT INTO [Table] VALUES " & "[whatever]", m_cn)
m_DA = New SqlDataAdapter
m_DA.InsertCommand = sc
m_DA.Update(m_DataTable)

I dont think I fully answered your question but this is a start also take a look at,

SqlDataAdapter.InsertCommand Property (System.Data.SqlClient)

and

SqlDataAdapter Class (System.Data.SqlClient)

info on sqlDataAdapter

you may also want to do m_DataTable.AcceptChanges() after adding a row
 
Last edited:

Estuardo

Well-known member
Joined
Nov 27, 2004
Messages
45
Location
Portugal
Programming Experience
5-10
G'd Morning!
I think that if you use your command builder (the one you have declared but not used) it may solve the problem. try this way:
VB.NET:
Private m_cn As New SqlConnection()
    Private m_DA As SqlDataAdapter
    Private m_CB As SqlCommandBuilder
    Private m_DataTable As New DataTable
    Private m_rowPosition As Integer = 0
 

    Private Sub MainForm_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        m_cn.ConnectionString = "Data Source=.\SQLEXPRESS; AttachDbFilename = C:\Temp\Test.mdf;Integrated Security=True;Connect Timeout=30;User Instance=True"
        m_cn.Open()
        m_DA = New SqlDataAdapter("Select * from Contacts", m_cn) 
        m_DA.Fill(m_DataTable)
        Me.ShowCurrentRecord()
    End Sub
 
    Private Sub btnAddNew_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnAddNew.Click
        Dim drNewRow As DataRow = m_DataTable.NewRow()
        drNewRow("ContactName") = txtNewContactName.Text
        drNewRow("State") = txtNewState.Text
        m_DataTable.Rows.Add(drNewRow)

        [B]m_CB = New SqlCommandBuilder(m_DA)[/B]
       

        m_DA.Update(m_DataTable)
        m_rowPosition = m_DataTable.Rows.Count - 1
        Me.ShowCurrentRecord()
    End Sub
 
Top Bottom