[RESOLVED] Syntax Error in UPDATE statement

[RichardKnox]

HI, I am unable to figure out why I am getting this error. Its probably simple, but I haven't found out how to fix it yet.


Here's the piece of code thats giving me the error.

Public Class DrugList
Private m_cnADONetConnection As New OleDb.OleDbConnection() 
Private m_daDataAdapter as OleDb.OleDbDataAdapter
Private m_cbCommandBuilder as OleDb.OleDbCommandBuilder
Private m_dtDrugs as New DataTable
Private m_rowPosition as Integer = 0

----------------------------------------------------------------------------------------------------------
Private Sub btnSave ()

If m_dtDrugs.Rows.Count <> 0 then
m_dtDrugs.Rows(m_rowPosition) ("YesNo") = txtYorN.Text
m_daDataAdapter.Update(m_dtDrugs)  <----------------------This is the line that throws the error.
End if
End Sub
End Class

I am able to access the database, work my way through the data, add data, but when I attempt to update the data, I get the error.

So, I know I am accessing the database, but Im not sure why it keeps giving me the error in the Subject line.

Any help would be greatfully received.

Thanks,
Richard

[Campion]

What line throws the error? What type is the field in question? Why are you using a textbox to update a yes/no field?

[RichardKnox]

This is the line of code throwing the error:

m_daDataAdapter.Update(m_dtDrugs)

The field type is a Text field.

I am self-teaching myself how to program in Visual Basic 2008. So, Im certain that I am not doing this correctly all the time, but I do take suggestions and pointers very well.

If there is a better approach to handling a Y/N field then please instruct me.

Thanks,
Richard

[Campion]

For a yes/no, or True/False as it is properly known, field in your database, there are special types dedicated for that job, such as Bit(1) for SQL Server, and its derivatives, Tinyint(1) for MySQL, as well as several others. Inside of your VB program, that type is Boolean. It can only hold two values: 0 (false), and not zero (true).

When you are using True/False fields in your program, it is highly recommended you use a checkbox. While not required, it is the preferred way. The way you current have it would require many lines of code to authenticate the "yes" or "no" before you use it anywhere in your program.

What database are you using anyhow?

[RichardKnox]

Access is the database.

[jmcilhinney]

You say that the error message tells you that there's a syntax error in your UPDATE statement yet you haven't shown us the UPDATE statement. Are you, by any chance, using an OleDbCommandBuilder that you haven't shown us?

[RichardKnox]

I posted the UPDATE statement already and indicated it within the code I posted.

Yes, I am using OleDbCommandBuilder. I just attempted to be a little brief in the amount I present, so as to not so all the code and have to much getting in the way. I will update my thread with the other code.

[jmcilhinney]

I posted the UPDATE statement already and indicated it within the code I posted.

I'm talking about the SQL code, not the VB code. It's your SQL UPDATE statement that contains the syntax error.

If you're using a command builder then you're not creating that UPDATE statement yourself. It's one of the commands that the command builder is building. You can get a look at it by calling the GetUpdateCommand method of your command builder and then the actual SQL code is contained in the CommandText of that OleDbCommand.

The most common reason for this issue is that you are using a SELECT statement with a wildcard for the column list, e.g.

SELECT * FROM MyTable

and one of your column names is a reserved word. This is one of the reasons to not use reserved words as identifiers and it's also one of the gotchas when using command builders. You'll need to either change the offending column name, create your own action queries or else write out the column list in full in your SELECT statement. An example of that last option would be:

SELECT UserID, UserName, [Password] FROM Users

You'd need to escape the "Password" column name because it's a reserved word. Your command builder should then do the same in the action queries.

[RichardKnox]

I'm talking about the SQL code, not the VB code. It's your SQL UPDATE statement that contains the syntax error.

If you're using a command builder then you're not creating that UPDATE statement yourself. It's one of the commands that the command builder is building. You can get a look at it by calling the GetUpdateCommand method of your command builder and then the actual SQL code is contained in the CommandText of that OleDbCommand.

The most common reason for this issue is that you are using a SELECT statement with a wildcard for the column list, e.g.

SELECT * FROM MyTable

and one of your column names is a reserved word. This is one of the reasons to not use reserved words as identifiers and it's also one of the gotchas when using command builders. You'll need to either change the offending column name, create your own action queries or else write out the column list in full in your SELECT statement. An example of that last option would be:

SELECT UserID, UserName, [Password] FROM Users

You'd need to escape the "Password" column name because it's a reserved word. Your command builder should then do the same in the action queries.

I dont have any of that. I will post the whole code for the form in the next post.

[RichardKnox]

Here is the complete code for the form.

Public Class DrugList
    Private m_cnADONetConnection As New OleDb.OleDbConnection()
    Private m_daDataAdapter As OleDb.OleDbDataAdapter
    Private m_cbCommandBuilder As OleDb.OleDbCommandBuilder
    Private m_dtDrugs As New DataTable
    Private m_rowPosition As Integer = 0
    Public m_numDrugs As Integer

    Private Sub DrugList_FormClosed(ByVal sender As Object, ByVal e As System.Windows.Forms.FormClosedEventArgs) Handles Me.FormClosed
        CloseData()
    End Sub

    Private Sub DrugList_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        'Open access to the Drugs database
        m_cnADONetConnection.ConnectionString = _
        "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Application.StartupPath & "\Drugs.mdb"

        m_cnADONetConnection.Open()

        m_daDataAdapter = New OleDb.OleDbDataAdapter("Select * From drugs", m_cnADONetConnection)

        m_cbCommandBuilder = New OleDb.OleDbCommandBuilder(m_daDataAdapter)

        Dim DrugsDataSet As New DataSet

        m_daDataAdapter.Fill(m_dtDrugs)

        Me.ShowCurrentRecord()
    End Sub
    Private Sub ShowCurrentRecord()
        If m_dtDrugs.Rows.Count = 0 Then
            txtDrugName.Text = ""
            txtYorN.Text = ""
            Exit Sub

        End If
        txtDrugName.Text = _
        m_dtDrugs.Rows(m_rowPosition)("TradeName").ToString()
        txtYorN.Text = _
        m_dtDrugs.Rows(m_rowPosition)("YesNo").ToString()

    End Sub

    Private Sub btnMoveFirst_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnMoveFirst.Click
        m_rowPosition = 0
        Me.ShowCurrentRecord()
    End Sub

    Private Sub btnMovePrevious_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnMovePrevious.Click
        'If not at the first row, go back one row and show the record.
        If m_rowPosition > 0 Then
            m_rowPosition = m_rowPosition - 1
            Me.ShowCurrentRecord()
        End If
    End Sub

    Private Sub btnMoveNext_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnMoveNext.Click
        'If not on the last row, advance one row and show the record.
        If m_rowPosition < (m_dtDrugs.Rows.Count - 1) Then
            m_rowPosition = m_rowPosition + 1
            Me.ShowCurrentRecord()
        End If
    End Sub

    Private Sub btnMoveLast_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnMoveLast.Click
        'If there are any rows in the data table, move to the last one and show the record.
        If m_dtDrugs.Rows.Count > 0 Then
            m_rowPosition = m_dtDrugs.Rows.Count - 1
            Me.ShowCurrentRecord()

        End If
    End Sub

    Private Sub btnSave_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSave.Click
        'If there is existing data, update it.
        If m_dtDrugs.Rows.Count <> 0 Then
            m_dtDrugs.Rows(m_rowPosition)("YesNo") = txtYorN.Text
            m_daDataAdapter.Update(m_dtDrugs)  '<---------- This is the one thats highlighted.

        End If
    End Sub
End Class

[jmcilhinney]

The complete code for the form is of no use to us. We know what the problem is and the solution has already been explained.

[stanav]

What are the field names in the drugs table?

[RichardKnox]

Field Name DataType
TradeName Text
GenericName Text
Classification Text
PrimaryIndication Text
PrimaryContraindication Text
DosageAdult Text
DosagePeds Text
SideEffects Text
Testable Yes/No <- Just altered this one, trying the Checkedbox approach.

[heeroyu16]

Meaning you need an update sql statement in order to excute an update.
(eg UPDATE tablename SET columnname=somevalue)

[stanav]

I notice that in your database table, there's no "YesNo" field, but you use that field name in your code

m_dtDrugs.Rows(m_rowPosition)("YesNo") = txtYorN.Text

And one more question, does the drugs table has a primary field? If it does, which one is it?

[Campion]

I notice that in your database table, there's no "YesNo" field, but you use that field name in your code

He stated that he changed it to "Testable", a Boolean field, in response to what I pointed out in in reply #3.

[RichardKnox]

He stated that he changed it to "Testable", a Boolean field, in response to what I pointed out in in reply #3.

As Champion has stated, I did change that to a Yes/No field. I found out from what he had stated and other readings, thats the way I should have gone to begin with.

[Campion]

What DB server are you using anyhow? How is the conversion going? Is there anything you are having trouble with in the conversion?

[RichardKnox]

Im creating a free-standing program that has the database in the program folder. There is no server or anything.

Conversion?

[jmcilhinney]

Maybe you could do what I suggested back in post #8 and look at the actual SQL code that's being executed. Call GetUpdateCommand on the CommandBuilder to get an OleDbCommand and then get the CommandText property of that. If the error message is telling us that there's a syntax error in the UPDATE statement then seeing that UPDATE statement would be helpful.

[RichardKnox]

I thank everyone for their help. I was finally able to get the code working. I'm uncertain as to what the issue was, but I re-wrote the code again and this time it worked just fine. No major re-thinks, just starting from scratch and hitting each area again and it works.

Again,
Thanks
Richard