Data Adapter Update command causing exception

Question

JUST A HEADS UP ----- This no longer applies for me. I fixed it by adding the following two lines to my form.load.

    cb.QuotePrefix = "["
    cb.QuoteSuffix = "]"

It has fixed the issue for me and I hope it sorts it out for you too.

Okay, so, I have an application in VB.net that serves as a scheduler for the trainers at the company I work at. In the edit mode (so updates can be made to the database the information is stored on) there are a number of buttons, adding and removing trainers, or adding weeks to the table.

The button causing me problems is the save/update button. It has three sets of commands within it, one for updating added columns, one for removed columns, and then a third one which simply updates other modified data. I know it probably could be more efficient but oh well, I'll get to that later.

The problem is, the last chunk of code includes "da.update(ds)" which is the data adapter updating the datasource. While this command works perfectly fine in our other app that connects to our SQL server, its causing problems here.

Any column where the first cell's value is null causes an exception saying

"Incorrect syntax near 'the column header's first two characters'."

Now, I thought this issue stemmed from - due to the exception - me using an incorrect set of names for the columns, which were the dates of the mondays of each week (so for example 01/02/2016) so it'd show Incorrect syntax near '01'. in this instance.

However, changing the naming convention did not fix this like how the exception would suggest, and it only occurs on columns where the FIRST value is a null - implying that the first trainer has nothing planned for this week right now.

Anyone have any ideas as to a way around this that doesnt involve basically filling in every null on this rather large table? I know that would work but It'd be pretty time consuming, and I am willing to do this if no other solution rears its head.

I've looked around on the internet and haven't found a solution that appeared relevant to our exact issue, so help would be very appreciated.

In case it is important - here is the function causing the issue.

Dim da As OleDbDataAdapter 'The datasets and adapters variables.
Dim da2 As OleDbDataAdapter
Public ds As DataSet = New DataSet
Public ds2 As DataSet = New DataSet


    'Connection String. Connects to the server and finds the database and table listed.
    cs = "Provider=SQLOLEDB;"
    cs &= "Server=SOFWAREDEVSQLSE\SQLEXPRESS;"
    cs &= "Database=MTS2;"
    cs &= "User Id=;" 'You don't need to see that to be fair.
    cs &= "Password=;" 'You don't need to see that to be fair.
    sql = "Select * FROM MTS2;"

    'Runs the string. Flares up a timed out error if connection could not be established.
    Try
        da = New OleDbDataAdapter(sql, cs)
        da.Fill(ds)
        da2 = New OleDbDataAdapter(sql, cs)
        da2.Fill(ds2)
    Catch ex As Exception
        MsgBox("Connection failed. Please ensure you have a suitable connection to the Training network. Otherwise, refer to helpdesk support for further assistance.")
        Me.Close()
    End Try
    dgvSchedule.DataSource = ds.Tables(0)


Private Function save()

    'Try
    ''This section reads the SQL server for column names, and adds any that are listed in the DGV, but not the database. I know its a little messy but itll do.
    Dim columnnum As Integer = -1
    Dim columname As String
    For Each column In ds.Tables(0).Columns
        columnnum = columnnum + 1
        columname = dgvSchedule.Columns(columnnum).HeaderText
        If Not ds2.Tables(0).Columns.Contains(columname) Then
            Dim SqlAddCol As String = "ALTER TABLE MTS2 ADD [" & columname.Trim() & "] nvarchar(255)"
            Using con As New OleDbConnection(cs)
                Using cmd As New OleDbCommand(SqlAddCol, con)
                    con.Open()
                    cmd.ExecuteNonQuery()
                End Using
            End Using

        End If
    Next

    columnnum = -1
    For Each column In ds2.Tables(0).Columns
        columnnum = columnnum + 1
        columname = ds2.Tables(0).Columns(columnnum).ColumnName
        If Not ds.Tables(0).Columns.Contains(columname) Then
            Dim SqlDelCol As String = "ALTER TABLE MTS2 DROP COLUMN [" & columname.Trim() & "]"
            Using con As New OleDbConnection(cs)
                Using cmd As New OleDbCommand(SqlDelCol, con)
                    con.Open()
                    cmd.ExecuteNonQuery()
                End Using
            End Using
        End If
    Next

    ds2.Tables.Clear()
    da2 = New OleDbDataAdapter(sql, cs)
    da2.Fill(ds2)

    da.Update(ds) ''''' The exception is thrown here. " Incorrect syntax near '01'."
    DataTableColours()
    MessageBox.Show("Data saved successfully. New weeks and trainers added and deleted. Changed values updated.")
    'Catch
    '   MessageBox.Show("Data failed to update properly. Please ensure you are connected to the Baltic network and try again. If the problem persists, seek IT support.")
    'End Try

End Function

The function saves the values in the data grid view (DGVSchedule) to the server by taking the current columns in the DS with their original columns (which are in DS2) A Sql query is then ran to add or remove any column mismatches. DS2 is then updated to use the same values as DS. Finally, DA.update(DS) is called, which updates all other modified values into the SQL server - theoretically. It is instead causing our peculiar exception.

Any help would be greatly appreciated, thanks.

Data Adapter Update command causing exception

Answers (1)

Related Questions