Problem saving New Row's to multiple tables VB/SQLServer

[cwnonthehill]

Hey guys, I'm having a little trouble making multiple row inserts to different tables. Below is my code. The idea is basically to create new rows in the dataset, then Insert those new rows to my SQL database. The first set (tblComplaint) gets inserted fine. Then when it attempts to Insert tblReporter, it says ...

"Missing the DataColumn 'Date' in the DataTable 'tblReporter' for the SourceColumn 'Date'."

So I'm assuming the problem is that it's still trying to insert the tblComplaint data into tblReporter, as tblReporter does not have a Date Column, and tblComplaint does. I have tried having multiple SqlCommand statements for each select, that made no difference. I also tried having multiple DataRow (ie. dsnr, dsnr2, etc) and that made no difference. I also tried adding .Dispose to my data adapter and data set before moving to the second table, that also made no difference. Can anyone suggest what I might be doing wrong?

   Dim dcb As New SqlCommandBuilder(da)
            Dim cmd As New SqlCommand
            Dim dsnr As DataRow
            Dim con As New SqlConnection("my server info")

            dsnr = ds.Tables("tblComplaint").NewRow
            cmd = New SqlCommand("SELECT * FROM tblComplaint", con)

            dsnr.Item(0) = DBNull.Value
            dsnr.Item(1) = txtDate.Text
            dsnr.Item(2) = cmbReason.SelectedValue
            dsnr.Item(3) = txtRecBy.Text
            dsnr.Item(4) = txtRepBy.Text
            dsnr.Item(5) = txtRepPh.Text
            dsnr.Item(6) = txtRelated.Text
            dsnr.Item(7) = txtComNotes.Text
            dsnr.Item(8) = cmbMaterial.SelectedValue
            dsnr.Item(9) = txtAdjNotes.Text
            dsnr.Item(10) = txtCleanNotes.Text

            inc = rowcount

            ds.Tables("tblComplaint").Rows.Add(dsnr)
            da.SelectCommand = cmd
            da.Update(ds, "tblComplaint")

            dsnr = ds.Tables("tblReporter").NewRow
            cmd = New SqlCommand("SELECT * FROM tblReporter", con)

            dsnr.Item(0) = txtRepPh.Text
            dsnr.Item(1) = txtRepBy.Text
            dsnr.Item(2) = txtRepAdd.Text

            ds.Tables("tblReporter").Rows.Add(dsnr)
            da.SelectCommand = cmd
            da.Update(ds, "tblReporter")

            dsnr = ds.Tables("tblOwner").NewRow
            cmd = New SqlCommand("SELECT * FROM tblOwner", con)

            dsnr.Item(0) = txtOPhone.Text
            dsnr.Item(1) = txtOwner.Text
            dsnr.Item(2) = txtOAdd.Text
            dsnr.Item(3) = ds.Tables("tblComplaint").Rows(inc).Item(0)
            ds.Tables("tblOwner").Rows.Add(dsnr)
            da.SelectCommand = cmd
            da.Update(ds, "tblOwner")

 

[IanRyder]

Hi,

Firstly, when you add code to your comment please use the code tags in the "Go Advanced" button when posting. It makes it a whole lot easier to read.

When looking at what you have done there are many declared variables that you are using which are not declared in this context. What is obvious however is that you are using the variable "da", presumably declared as a DataAdapter elsewhere in your code, to do the update of each and every one of your tables. See the cut down elements of your code below:-

da.Update(ds, "tblComplaint")
da.Update(ds, "tblReporter")
da.Update(ds, "tblOwner")

What you have to remember is that each DataTable has to have it's own declared DataAdapter. This is because the DataAdapter contains the specific SQL elements which interface with a specific data source in your database. You cannot therefore use the same DataAdapter to update different DataTables.

To sort this you need to create the additional DataAdapters and CommandBuilders for each of the tables that you want to manage and then call the update method on the additional adapters.

Hope that helps.

Cheers,

Ian

 

[cwnonthehill]

Ian, thanks for your help. I am unsure if we are on the same page regarding the DataAdapter, I have used in this same application, and in applications before, the same DataAdapter to Fill multiple tables in the DataSet. So, the idea that you say I need a seperate DataAdapter for each table seems foreign to me?

 

[cwnonthehill]

For instance, the following works with no problems, to fill my form...

        conString = "server=vmsqltest; database=eadb-complaints; trusted_connection=true;"
        con = New SqlConnection(conString)

        con.Open()
        dc = New SqlCommand("SELECT * FROM tblReason ORDER BY ReasonID", con)
        da.SelectCommand = dc
        da.Fill(ds, "tblReason")
        con.Close()
        cmbReason.DisplayMember = "Reason"
        cmbReason.ValueMember = "ReasonID"
        cmbReason.DataSource = ds.Tables("tblReason")

        con.Open()
        dc = New SqlCommand("SELECT * FROM tblMaterial ORDER BY MaterialID", con)
        da.SelectCommand = dc
        da.Fill(ds, "tblMaterial")
        con.Close()
        cmbMaterial.DisplayMember = "Material"
        cmbMaterial.ValueMember = "MaterialID"
        cmbMaterial.DataSource = ds.Tables("tblMaterial")

        cmd1 = "SELECT * FROM tblReporter"
        cmd2 = "SELECT * FROM tblOwner"
        cmd3 = "SELECT * FROM tblComplaint"

        con.Open()
        dc = New SqlCommand(cmd3, con)
        da.SelectCommand = dc
        da.Fill(ds, "tblComplaint")
        da.SelectCommand.CommandText = cmd2
        da.Fill(ds, "tblOwner")
        da.SelectCommand.CommandText = cmd1
        da.Fill(ds, "tblReporter")
        dc.Dispose()
        da.Dispose()
        con.Close()

 

[cwnonthehill]

Actually, I think I understand what your saying now, but it goes against what I thought was correct. I guess when it comes to SELECT commands, it doesn't generate a problem using the same DataAdapter, but when UPDATING/INSERTING, is when the problem comes in to play. I am going to create new DA's and CB's and see how it goes. Thanks!

 

[IanRyder]

Hi,

Look at what you are doing. See below another cut down version of your code:-

dc = New SqlCommand("SELECT * FROM tblReason ORDER BY ReasonID", con)
da.SelectCommand = dc
da.Fill(ds, "tblReason")
 
dc = New SqlCommand("SELECT * FROM tblMaterial ORDER BY MaterialID", con)
da.SelectCommand = dc
da.Fill(ds, "tblMaterial")
 
dc = New SqlCommand(cmd3, con)
da.SelectCommand = dc
da.Fill(ds, "tblComplaint")
da.SelectCommand.CommandText = cmd2
da.Fill(ds, "tblOwner")
da.SelectCommand.CommandText = cmd1
da.Fill(ds, "tblReporter")

When you change the DataAdapter select command and then fill your controls all will be fine since you have changed the DataAdapter. However, you also use a CommandBuilder to create the SQL modification statements for the DataAdapter and therefore only the modification statements will be created based on the Select command you had at the point you created the CommandBuilder.

The CommandBuilder will not "auto-add/recreate" additional modification commands all because you have changed the Select Command.

Hope that helps to explain better.

Cheers,

Ian

N.B - You got it.