system.data.sqlclientexception:incorrect syntax near '@p2'

Question

I am trying to insert, update the data on Grid in VB.net windows application which has to update the data in database. I am not able to achieve this functionality using new schema name(Customer) but when I try to create table with schema "dbo" I am able insert, update the data on grid and able to see the data in SQL Server as well.

Please help me what I need to change in code perform insert and update options.

code:

Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load

    Dim test1 As String
    test1 = "Select * from Customer.CustomerID"

    connection = New OleDbConnection
    connection.ConnectionString = "Provider=MSOLEDBSQL.1;Integrated Security=SSPI;Initial Catalog=prod;Data Source=IN-TESTVM;Use Procedure for Prepare=1;Auto Translate=True;Packet Size=4096;Workstation ID=IN-TESTVM;Use Encryption for Data=False;Tag with column collation when possible=False;MARS Connection=False;DataTypeCompatibility=0;Trust Server Certificate=False;Application Intent=READWRITE;MultisubnetFailover=False;Use FMTONLY=False;"
    connection.Open()
    myDA = New OleDbDataAdapter(test1, connection)
    dsDataGrid = New DataSet
    myDA.Fill(dsDataGrid)
    grid.DataSource = dsDataGrid.Tables(0)
    bindsrc2.DataSource = dsDataGrid
    connection.Close()

End Sub

Button click event code is like as below

Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
    app = New OleDbCommandBuilder(myDA)
    bindsrc2.EndEdit()

    myDA.Update(bindsrc2.DataSource)  'Hitting the error while updating the data at this line
End Sub

I am able to load the data into grid using the schema name other than dbo as well.

myDA.Fill(dsDataGrid)
grid.DataSource = dsDataGrid.Tables(0)
bindsrc2.DataSource = dsDataGrid

Error message after clicking save button

Answer 1

Try setting the QuotePrefix and QuoteSuffix of your command builder to "[" and "]" respectively. As you are using a wildcard in your query, the command builder will not escape column names automatically and that means that keywords or spaces or other special characters will cause syntax errors.

Note that there are two alternatives. One is to not use a command builder at all and create your own action commands. In that case, you write the SQL so you escape the column names that need it. The other is to not use a wildcard in the query, in which case you will escape the column names that need it and the command builder will follow suit.