Update tables in a SQL Server database by using vb.net

Question

I have a SQL Server database with two tables:

tblEmp with columns:

EmpNum, EmpName, EmpAge, EmpAdress, EmpDegree, EmpJobTitle, EmpPhone

and the column UnitID that used to set the relationship between the two tables

tblUnits with columns:

UnitID, UnitName

In VB the code is:

Public Class Form1
    Dim con As New SqlConnection("server=.....etc")
    Dim adapter As SqlDataAdapter
    Dim dt As New DataTable
    Dim cmdb As New SqlCommandBuilder

    Private Sub btnUpdate_Click.......
        Dim row As DataRow = dt.Rows.Find(txtNum.Text)
        row(0) = txtNum.Text
        row(1) = txtName.Text
        row(2) = txtAge.Text
        row(3) = txtAdress.Text
        row(4) = txtDegree.Text
        row(5) = txtJob.Text
        row(6) = txtPhone.Text
        row(7) = cmbunits.Text
        cmdb = New SqlCommandBuilder(adapter)
        adapter.Update(dt)

I get this error:

Additional information: Dynamic SQL generation is not supported against multiple base tables.

jmcilhinney · Accepted Answer · 2020-03-30 14:13:32Z

1

A command builder uses the query in the SelectCommand of your data adapter to generate the other commands. That can only happen under certain circumstances. One of those circumstances is that the query does not involve multiple tables. The error message indicates that that is not true in your case. You need to create the UpdateCommand, etc, yourself.

answered Mar 30, 2020 at 14:13

jmcilhinney

54.9k5 gold badges30 silver badges52 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

adnan Over a year ago

I forgot to post the whole code: Dim strbring As String = "SELECT distinct EmpNum,EmpName,EmpAge,EmpAdress,EmpDegree,EmpJobTitle,EmpPhone, UnitName from tblEmp,tblUnit where tblUnit.UnitID =tblEmp.EmpUnitID " adapter = New SqlDataAdapter(strbring, con) adapter.Fill(dt) dgEmp.DataSource = dt

jmcilhinney Over a year ago

Don't post long code snippets in comments, especially unformatted. It's all but unreadable. Update your question so it contains all the information that it should have in the first place. That said, your comment simply reinforces what you've already been told.

Collectives™ on Stack Overflow

Update tables in a SQL Server database by using vb.net

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related