Error in data set. Update requires a valid InsertCommand when passed DataRow.....

[ishrar]

I have written such a simple code in vb.net
I have used data adapter command and dataset to pass data in database
this is my code

 Private Sub cmdSave_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdSave.Click
        If Mode = "New" Then
            sql = "select * from donorentry"
            cmd = New SqlClient.SqlCommand(sql, cn)
            Dim dt As DataTable

            dset.Clear()
            dadap.SelectCommand = cmd
            'dadap.Fill(dset)
            dadap.Fill(dset, "donorentry")
            dt = dset.Tables("donorentry")
            ro = dt.NewRow
            'dset.Clear()
            SetRows()

            dt.Rows.Add(ro)
            ' ==================== Error on  this statement ================
           'error is 
'Update requires a valid InsertCommand when passed DataRow collection with new rows.
            dadap.Update(dset, "donorentry")


        ElseIf Mode = "Edit" Then
            ro.BeginEdit()
            SetRows()
            ro.EndEdit()
        End If

    End Sub

 Private Sub SetRows()
'initialization of record into ro (a data row)
' ro is declared globally
        ro.Item("did") = txtDID.Text
        ro.Item("entrydate") = dtEntryDate.Value
        ro.Item("fname") = txtFirstName.Text
        ro.Item("lname") = txtLastName.Text
        ro.Item("sname") = txtSurName.Text

' first time tried this method of initializing it was also not working then changed into above format.
        'ro!hadd1 = txtHadd1.Text
        'ro!hadd2 = txtHadd2.Text
        'ro!hcity = cmbCity.Text
        'ro!hstate = cmbHstate.Text
        'ro!hcountry = cmbHCountry.Text
        'ro!hpin = txtHPincode.Text

    End Sub

I am trying from three days but I couldn't touch the solution.
Every where on net I found this method to insert record. I cant understand where there is silly mistake in this code.
Please help me

[jmcilhinney]

You have written the SQL code to retrieve data from the database:

sql = "select * from donorentry"

The SQL code to insert data into the database will not appear by magic. Either you have to write it or you have to tell the system to generate it for you. Follow the Data Access link in my signature for examples of each way.

[ishrar]

Ohhh, it becomes very lengthy because i have so many fields in my each table, what can we do for it.
Is it the one only solution?


I have referred this links and I have also referred which do not say to write insert statement.
http://metrix.fcny.org/wiki/display/...ment+in+VB.NET

[jmcilhinney]

The code in that link uses a command builder, as does one of the examples in the Data Access link of mine. I alluded to that in my previous post:

Either you have to write it or you have to tell the system to generate it for you. Follow the Data Access link in my signature for examples of each way.

If you want to insert data then you need an SQL INSERT statement. You already have the two options for creating it in front of you.

If writing a bit of SQL code with a lot of column names in it is a problem for you I suggest you give up programming now.

[ishrar]

yes i did it without insert query.......
i just changed two lines......

If Mode = "New" Then
            sql = "insert into donorentry ()"
            sql = "select * from donorentry"
            cmd = New SqlClient.SqlCommand(sql, cn)
            Dim dt As DataTable
            
            dset.Clear()
            dadap.SelectCommand = cmd
            'dadap.Fill(dset)
            dadap.Fill(dset, "donorentry")
'==========
'                  it automatically creates insert command
            Dim cmdbldr As New SqlCommandBuilder(dadap)
            cmdbldr.GetInsertCommand()
'==========
            dt = dset.Tables("donorentry")
            ro = dt.NewRow
            'dset.Clear()
            SetRows()

            dt.Rows.Add(ro)
           
            dadap.Update(dset, "donorentry")

I got this thing from
http://metrix.fcny.org/wiki/display/...ment+in+VB.NET

and MSDN
http://msdn.microsoft.com/en-us/libr...ndbuilder.aspx

I was trying from three days now its done

Thank v.much you for your co-operation.

[ishrar]

I don't give up anything I just find solution hidden inside problem.

[jmcilhinney]

yes i did it without insert query

No, you didn't. What you did was tell the system to generate the INSERT statement for you, as I have already said twice. Data cannot be inserted into a database without an INSERT statement. My Data Access thread provides a code example of doing just that.

Note that if your query involves only one table and it has a primary key then you can take the easy option and use a CommandBuilder instead of creating the non-query commands yourself:

Dim connection As New SqlConnection("connection string here")
Dim adapter As New SqlDataAdapter("SELECT ID, Name, Quantity, Unit FROM StockItem", connection)
Dim builder As New SqlCommandBuilder(adapter)
 
adapter.MissingSchemaAction = MissingSchemaAction.AddWithKey
 
Dim table As New DataTable
 
'Retrieve the data.
adapter.Fill(table)
 
'The table can be used here to display and edit the data.
'That will most likely involve data-binding but that is not a data access issue.
 
'Save the changes.
adapter.Update(table)