Help Resolving Syntax Error

Hey All,

Need some help resolving a Syntax error as title says, been a long day pretty sure it's a simple fix but i can't find the error.

Code:

---

If bStudentNumberExists Then


            sSaveSql = "update CUSTOMER set " _
            & "Surname = '" & oThisFormA.txtCusSurname.Text.Trim & "'," _
            & "Given = '" & oThisFormA.txtCustGiven.Text.Trim & "'," _
            & "DateOfBirth = '" & oThisFormA.txtCustDOB.Text.Trim & "', " _
            & "Sex = '" & oThisFormA.txtCustSex.Text.Trim & "'," _
            & "Phone = '" & oThisFormA.txtCustPhone.Text.Trim & "'," _
            & "Address = '" & oThisFormA.txtCustAddress.Text.Trim & "'," _
            & "Suburb = '" & oThisFormA.txtCustSuburb.Text.Trim & "'," _
            & "State = '" & oThisFormA.txtCustState.Text.Trim & "'," _
            & "PostCode = '" & oThisFormA.txtCustPostCode.Text.Trim & "'," _
            & "where ID ='" & oThisFormA.txtCustID.Text.Trim & "';"


        End If

---

One of the best way to avoid syntax errors is to use a parameterized command. Below is an example doing an INSERT You can do the same for SELECT, UPDATE, DELETE etc.

Code:

---

Using cmd As OleDbCommand = New OleDbCommand With {.Connection = cn}
    cn.Open()
    Try
        cmd.CommandText = _
        <SQL>
            INSERT INTO Customers
                (
                    Identifier,
                    FirstName,
                    LastName
                )
            VALUES (@Identifier,@FirstName, @LastName)
        </SQL>.Value
       
        cmd.Parameters.AddWithValue("@Identifier", CurrentID)
        cmd.Parameters.AddWithValue("@FirstName", txtFirstName.Text)
        cmd.Parameters.AddWithValue("@LastName", txtLastName.Text)

        cmd.ExecuteNonQuery()

    Catch OleDbExceptionErr As OleDbException
        MessageBox.Show(OleDbExceptionErr.Message)
    End Try

End Using

---

Quote:

---

Originally Posted by kevininstructor

One of the best way to avoid syntax errors is to use a parameterized command. Below is an example doing an INSERT You can do the same for SELECT, UPDATE, DELETE etc.

Code:

---

Using cmd As OleDbCommand = New OleDbCommand With {.Connection = cn}
    cn.Open()
    Try
        cmd.CommandText = _
        <SQL>
            INSERT INTO Customers
                (
                    Identifier,
                    FirstName,
                    LastName
                )
            VALUES (@Identifier,@FirstName, @LastName)
        </SQL>.Value
       
        cmd.Parameters.AddWithValue("@Identifier", CurrentID)
        cmd.Parameters.AddWithValue("@FirstName", txtFirstName.Text)
        cmd.Parameters.AddWithValue("@LastName", txtLastName.Text)

        cmd.ExecuteNonQuery()

    Catch OleDbExceptionErr As OleDbException
        MessageBox.Show(OleDbExceptionErr.Message)
    End Try

End Using

---

---

I dont Understand how the cn Stuff works mate..

Currently i Have:

Code:

---

Dim oConn As OleDbConnection        'To reference a Connection obj.
        Dim oCmd_Select As OleDbCommand    'To Instantiate a Command obj used to execute the Select SQL.
        Dim oDataReader As OleDbDataReader  'To instantiate a DataReader obj.
        Dim oCmd_Update As OleDbCommand    'To Instantiate a Command obj used to execute the Insert/Update SQL.

        'sErrorMessage = ""
        oConn = Nothing
        oCmd_Select = Nothing
        oDataReader = Nothing
        oCmd_Update = Nothing

        ' --- create a database command object and set the SQL statement
        ' --- it will execute, and the database connection object associated to it
        sConnection = "Provider=Microsoft.Jet.OLEDB.4.0;"
        sConnection = sConnection & "User ID=Admin;"
        sConnection = sConnection & "Data Source="
        sConnection = sConnection & Application.StartupPath & "\" & sDBNameA
        oConn = New OleDbConnection(sConnection)
        oConn.Open()

        oCmd_Select = oConn.CreateCommand()
        sSQL = "select ID from CUSTOMER where ID= " & sCusID & ""
        oCmd_Select.CommandText = sSQL

        ' --- execute SQL command and place results into a datareader object
        oDataReader = oCmd_Select.ExecuteReader()

        bStudentNumberExists = (oDataReader.Read() = True)

        'MessageBox.Show("bStudentNumberExists=" & bStudentNumberExists)
        ' --- Build Insert or Update Sql depending if record retrieved with select query is found
        If bStudentNumberExists Then


                sSaveSql = "update CUSTOMER set " _
                            & "Surname = '" & oThisFormA.txtCusSurname.Text.Trim & "'," _
                            & "Given = '" & oThisFormA.txtCustGiven.Text.Trim & "'," _
                            & "DateOfBirth = '" & oThisFormA.txtCustDOB.Text.Trim & "', " _
                            & "Sex = '" & oThisFormA.txtCustSex.Text.Trim & "'," _
                            & "Phone = '" & oThisFormA.txtCustPhone.Text.Trim & "'," _
                            & "Address = '" & oThisFormA.txtCustAddress.Text.Trim & "'," _
                            & "Suburb = '" & oThisFormA.txtCustSuburb.Text.Trim & "'," _
                            & "State = '" & oThisFormA.txtCustState.Text.Trim & "'," _
                            & "PostCode = '" & oThisFormA.txtCustPostCode.Text.Trim & "'," _
                            & "where ID ='" & oThisFormA.txtCustID.Text.Trim & "';"

End If

---

cn is your oConn

This is known as cn is being set as the connection to the command

Code:

---

Using cmd As OleDbCommand = New OleDbCommand With {.Connection = cn}

---

only because I'm feeling generous today....

Code:

---

                'sSaveSql = "update CUSTOMER set " _
                '            & "Surname = '" & oThisFormA.txtCusSurname.Text.Trim & "'," _
                '            & "Given = '" & oThisFormA.txtCustGiven.Text.Trim & "'," _
                '            & "DateOfBirth = '" & oThisFormA.txtCustDOB.Text.Trim & "', " _
                '            & "Sex = '" & oThisFormA.txtCustSex.Text.Trim & "'," _
                '            & "Phone = '" & oThisFormA.txtCustPhone.Text.Trim & "'," _
                '            & "Address = '" & oThisFormA.txtCustAddress.Text.Trim & "'," _
                '            & "Suburb = '" & oThisFormA.txtCustSuburb.Text.Trim & "'," _
                '            & "State = '" & oThisFormA.txtCustState.Text.Trim & "'," _
                '            & "PostCode = '" & oThisFormA.txtCustPostCode.Text.Trim & "'," _
                '            & "where ID ='" & oThisFormA.txtCustID.Text.Trim & "';"


                sSaveSql = "update CUSTOMER set " _
                            & "Surname = ?," _
                            & "Given = ?," _
                            & "DateOfBirth = ?, " _
                            & "Sex = ?," _
                            & "Phone = ?," _
                            & "Address = ?," _
                            & "Suburb = ?," _
                            & "State = ?," _
                            & "PostCode = ?," _
                            & "where ID = ?;"

cmd.Parameters.AddWithValue("@Surname", oThisFormA.txtCusSurname.Text.Trim)
cmd.Parameters.AddWithValue("@Given", oThisFormA.txtCustGiven.Text.Trim)
cmd.Parameters.AddWithValue("@DateOfBirth", CDate(oThisFormA.txtCustDOB.Text.Trim))
cmd.Parameters.AddWithValue("@Sex", oThisFormA.txtCustSex.Text.Trim)
cmd.Parameters.AddWithValue("@Phone",oThisFormA.txtCustPhone.Text.Trim)
cmd.Parameters.AddWithValue("@Address", oThisFormA.txtCustAddress.Text.Trim)
cmd.Parameters.AddWithValue("@Suburb", oThisFormA.txtCustSuburb.Text.Trim)
cmd.Parameters.AddWithValue("@State", oThisFormA.txtCustState.Text.Trim)
cmd.Parameters.AddWithValue("@PostCode", oThisFormA.txtCustPostCode.Text.Trim)
cmd.Parameters.AddWithValue("@ID", CInt(oThisFormA.txtCustID.Text.Trim))

---

I made some assumptions: 1) Your DOB field is a date type. 2) Your ID field is an integer type.

-tg

You can clean up your connection as follows so not to worry about string concatenation. Note the use of IO.Path.Combine which ensures the blackslash will be added in this case.

Code:

---

Dim builder As New OleDbConnectionStringBuilder With _
    { _
        .DataSource = IO.Path.Combine(Application.StartupPath, sDBNameA), _
        .Provider = "Microsoft.Jet.OLEDB.4.0" _
    }
builder.Add("User ID", "Admin")

Dim oConn As New OleDbConnection With {.ConnectionString = builder.ConnectionString}

---

I'd also like to point out that I wouldn't use the Reader if all you are doing is checking to see if a record exists... use the ExecuteScalar method instead. Using the reader will lock the connection to the reader, preventing you from using it anywhere else until the reader is closed. By using ExecuteScalar you free up your connection, allowing you to use it for the update or what ever else command you want.

-tg

vb Code:

If bStudentNumberExists Then
            Using cmd As OleDbCommand = New OleDbCommand
                sSaveSql = "update CUSTOMER set " _
                               & "Surname = ?," _
                               & "Given = ?," _
                               & "DateOfBirth = ?, " _
                               & "Sex = ?," _
                               & "Phone = ?," _
                               & "Address = ?," _
                               & "Suburb = ?," _
                               & "State = ?," _
                               & "PostCode = ?," _
                               & "where ID = ?;"
 
                cmd.Parameters.AddWithValue("@Surname", oThisFormA.txtCusSurname.Text.Trim)
                cmd.Parameters.AddWithValue("@Given", oThisFormA.txtCustGiven.Text.Trim)
                cmd.Parameters.AddWithValue("@DateOfBirth", CDate(oThisFormA.txtCustDOB.Text.Trim))
                cmd.Parameters.AddWithValue("@Sex", oThisFormA.txtCustSex.Text.Trim)
                cmd.Parameters.AddWithValue("@Phone", oThisFormA.txtCustPhone.Text.Trim)
                cmd.Parameters.AddWithValue("@Address", oThisFormA.txtCustAddress.Text.Trim)
                cmd.Parameters.AddWithValue("@Suburb", oThisFormA.txtCustSuburb.Text.Trim)
                cmd.Parameters.AddWithValue("@State", oThisFormA.txtCustState.Text.Trim)
                cmd.Parameters.AddWithValue("@PostCode", oThisFormA.txtCustPostCode.Text.Trim)
                cmd.Parameters.AddWithValue("@ID", CInt(oThisFormA.txtCustID.Text.Trim))
 
            End Using
            'sSaveSql = "update CUSTOMER set " _
            '             & "Surname = '" & oThisFormA.txtCusSurname.Text.Trim & "'," _
            '             & "Given = '" & oThisFormA.txtCustGiven.Text.Trim & "'," _
            '             & "DateOfBirth = '" & oThisFormA.txtCustDOB.Text.Trim & "', " _
            '             & "Sex = '" & oThisFormA.txtCustSex.Text.Trim & "'," _
            '             & "Phone = '" & oThisFormA.txtCustPhone.Text.Trim & "'," _
            '             & "Address = '" & oThisFormA.txtCustAddress.Text.Trim & "'," _
            '             & "Suburb = '" & oThisFormA.txtCustSuburb.Text.Trim & "'," _
            '             & "State = '" & oThisFormA.txtCustState.Text.Trim & "'," _
            '             & "PostCode = '" & oThisFormA.txtCustPostCode.Text.Trim & "'," _
            '             & "where ID ='" & oThisFormA.txtCustID.Text.Trim & "';"
 
        Else
            sSaveSql = "insert into CUSTOMER(ID, Surname, Given, DateOfBirth,Sex,Phone,Address,Suburb,State,PostCode) " _
                     & " values('" & oThisFormA.txtCustID.Text & "'," _
                     & "'" & oThisFormA.txtCusSurname.Text.Trim & "'," _
                     & "'" & oThisFormA.txtCustGiven.Text.Trim & "'," _
                     & "'" & oThisFormA.txtCustDOB.Text.Trim & "'," _
                     & "'" & oThisFormA.txtCustSex.Text.Trim & "'," _
                     & "'" & oThisFormA.txtCustPhone.Text.Trim & "'," _
                     & "'" & oThisFormA.txtCustAddress.Text.Trim & "'," _
                     & "'" & oThisFormA.txtCustSuburb.Text.Trim & "'," _
                     & "'" & oThisFormA.txtCustState.Text.Trim & "'," _
                     & "'" & oThisFormA.txtCustPostCode.Text.Trim & "' )"
            MsgBox("ID:" & sCusID & " has been Added to the table")
 
 
            ' Be careful however with the commas and the closing bracket
        End If

With that as my Update code is still get SYNTAX ERROR in UPDATE clause

Thanks for your help so far, but still not working.

Quote:

---

Originally Posted by techgnome

I'd also like to point out that I wouldn't use the Reader if all you are doing is checking to see if a record exists... use the ExecuteScalar method instead. Using the reader will lock the connection to the reader, preventing you from using it anywhere else until the reader is closed. By using ExecuteScalar you free up your connection, allowing you to use it for the update or what ever else command you want.

-tg

---

Ok thanks but it's the way i have been asked to do it :)

Change

Code:

---

& "PostCode = ?," _

---

To

Code:

---

& "PostCode = ? " _

---

Quote:

---

Originally Posted by kevininstructor

Change

Code:

---

& "PostCode = ?," _

---

To

Code:

---

& "PostCode = ? " _

---

---

That fixed that error, thanks a lot! But now with this full code:

vb Code:

Public Sub g_sSaveCustomerRecord(ByVal oThisFormA As frmCaptureCustomer, _
                                   ByVal sCusID As String, _
                                   ByVal sDBNameA As String)
        ' ---------------------------------------------------------------------
        ' Incoming Parameters: 
        '       oThisFormA  - a reference to the current form
        '                     of type frmDataCapture
        '       sStudentNumberA - The student number
        '       sDBNameA    - The Database File Name 
        ' Return value :
        '       if save successfull (no validation issues) return empty string, ""
        '       otherwise return error message
        '------------------------------------------------------------
        Dim sSaveSql As String
        Dim sConnection As String
        Dim sErrorMessage As String
        Dim sSQL As String
        Dim bStudentNumberExists As Boolean
 
        Dim oConn As OleDbConnection        'To reference a Connection obj.
        Dim oCmd_Select As OleDbCommand     'To Instantiate a Command obj used to execute the Select SQL.
        Dim oDataReader As OleDbDataReader  'To instantiate a DataReader obj.
        Dim oCmd_Update As OleDbCommand     'To Instantiate a Command obj used to execute the Insert/Update SQL.
 
        'sErrorMessage = ""
        oConn = Nothing
        oCmd_Select = Nothing
        oDataReader = Nothing
        oCmd_Update = Nothing
 
        ' --- create a database command object and set the SQL statement 
        ' --- it will execute, and the database connection object associated to it
        sConnection = "Provider=Microsoft.Jet.OLEDB.4.0;"
        sConnection = sConnection & "User ID=Admin;"
        sConnection = sConnection & "Data Source="
        sConnection = sConnection & Application.StartupPath & "\" & sDBNameA
        oConn = New OleDbConnection(sConnection)
        oConn.Open()
 
        oCmd_Select = oConn.CreateCommand()
        sSQL = "select ID from CUSTOMER where ID= " & sCusID & ""
        oCmd_Select.CommandText = sSQL
 
        ' --- execute SQL command and place results into a datareader object
        oDataReader = oCmd_Select.ExecuteReader()
 
        bStudentNumberExists = (oDataReader.Read() = True)
 
        'MessageBox.Show("bStudentNumberExists=" & bStudentNumberExists)
        ' --- Build Insert or Update Sql depending if record retrieved with select query is found
        If bStudentNumberExists Then
            Using cmd As OleDbCommand = New OleDbCommand
                sSaveSql = "update CUSTOMER set " _
                               & "Surname = ?," _
                               & "Given = ?," _
                               & "DateOfBirth = ?, " _
                               & "Sex = ?," _
                               & "Phone = ?," _
                               & "Address = ?," _
                               & "Suburb = ?," _
                               & "State = ?," _
                               & "PostCode = ? " _
                               & "where ID = ?;"
 
                cmd.Parameters.AddWithValue("@Surname", oThisFormA.txtCusSurname.Text.Trim)
                cmd.Parameters.AddWithValue("@Given", oThisFormA.txtCustGiven.Text.Trim)
                cmd.Parameters.AddWithValue("@DateOfBirth", CDate(oThisFormA.txtCustDOB.Text.Trim))
                cmd.Parameters.AddWithValue("@Sex", oThisFormA.txtCustSex.Text.Trim)
                cmd.Parameters.AddWithValue("@Phone", oThisFormA.txtCustPhone.Text.Trim)
                cmd.Parameters.AddWithValue("@Address", oThisFormA.txtCustAddress.Text.Trim)
                cmd.Parameters.AddWithValue("@Suburb", oThisFormA.txtCustSuburb.Text.Trim)
                cmd.Parameters.AddWithValue("@State", oThisFormA.txtCustState.Text.Trim)
                cmd.Parameters.AddWithValue("@PostCode", oThisFormA.txtCustPostCode.Text.Trim)
                cmd.Parameters.AddWithValue("@ID", CInt(oThisFormA.txtCustID.Text.Trim))
 
            End Using
            'sSaveSql = "update CUSTOMER set " _
            '             & "Surname = '" & oThisFormA.txtCusSurname.Text.Trim & "'," _
            '             & "Given = '" & oThisFormA.txtCustGiven.Text.Trim & "'," _
            '             & "DateOfBirth = '" & oThisFormA.txtCustDOB.Text.Trim & "', " _
            '             & "Sex = '" & oThisFormA.txtCustSex.Text.Trim & "'," _
            '             & "Phone = '" & oThisFormA.txtCustPhone.Text.Trim & "'," _
            '             & "Address = '" & oThisFormA.txtCustAddress.Text.Trim & "'," _
            '             & "Suburb = '" & oThisFormA.txtCustSuburb.Text.Trim & "'," _
            '             & "State = '" & oThisFormA.txtCustState.Text.Trim & "'," _
            '             & "PostCode = '" & oThisFormA.txtCustPostCode.Text.Trim & "'," _
            '             & "where ID ='" & oThisFormA.txtCustID.Text.Trim & "';"
 
            'Else
            '    sSaveSql = "insert into CUSTOMER(ID, Surname, Given, DateOfBirth,Sex,Phone,Address,Suburb,State,PostCode) " _
            '             & " values('" & oThisFormA.txtCustID.Text & "'," _
            '             & "'" & oThisFormA.txtCusSurname.Text.Trim & "'," _
            '             & "'" & oThisFormA.txtCustGiven.Text.Trim & "'," _
            '             & "'" & oThisFormA.txtCustDOB.Text.Trim & "'," _
            '             & "'" & oThisFormA.txtCustSex.Text.Trim & "'," _
            '             & "'" & oThisFormA.txtCustPhone.Text.Trim & "'," _
            '             & "'" & oThisFormA.txtCustAddress.Text.Trim & "'," _
            '             & "'" & oThisFormA.txtCustSuburb.Text.Trim & "'," _
            '             & "'" & oThisFormA.txtCustState.Text.Trim & "'," _
            '             & "'" & oThisFormA.txtCustPostCode.Text.Trim & "' )"
            '    MsgBox("ID:" & sCusID & " has been Added to the table")
 
 
            '    ' Be careful however with the commas and the closing bracket
        End If
 
 
        oDataReader.Close()
        oCmd_Select.Dispose()
 
 
        ' Execute sql update or insert query:
        oCmd_Update = oConn.CreateCommand()
        oCmd_Update.CommandText = sSaveSql
        oCmd_Update.ExecuteNonQuery()
 
 
 
 
 
 
    End Sub

I get Erro:

Code:

---

No value given for one or more required parameters.

---

Any Idea why?

I have done a debug and every txt box had a vale assigned to it.

Good to hear that fixed the first error, using parameters along with you statement w/o string concatenation allows you to examine said statement easier to spot issues.

For example the following returns a string which is easy to locate issues.

Code:

---

Dim UpdateStatement = _
<SQL>
    UPDATE CUSTOMER set
        Surname = ?,
        Given = ?,
        DateOfBirth = ?,
        Sex = ?,
        Phone = ?,
        Address = ?,
        Suburb = ?,
        State = ?,
        PostCode = ?
    WHERE ID = ?
</SQL>.Value

---

Quote:

---

Originally Posted by kevininstructor

Good to hear that fixed the first error, using parameters along with you statement w/o string concatenation allows you to examine said statement easier to spot issues.

For example the following returns a string which is easy to locate issues.

Code:

---

Dim UpdateStatement = _
<SQL>
    UPDATE CUSTOMER set
        Surname = ?,
        Given = ?,
        DateOfBirth = ?,
        Sex = ?,
        Phone = ?,
        Address = ?,
        Suburb = ?,
        State = ?,
        PostCode = ?
    WHERE ID = ?
</SQL>.Value

---

---

Sorry to bug ya mate, im new to vb haha so not to sure what you want me to do with that piece of code

Quote:

---

Originally Posted by coccoster

Sorry to bug ya mate, im new to vb haha so not to sure what you want me to do with that piece of code

---

Lets simplify it by taking the variable out, this is your SQL statement assigned directly to the commmand's command text.

Code:

---

Using cmd As OleDbCommand = New OleDbCommand
    cmd.CommandText = _
    <SQL>
    UPDATE CUSTOMER set
        Surname = ?,
        Given = ?,
        DateOfBirth = ?,
        Sex = ?,
        Phone = ?,
        Address = ?,
        Suburb = ?,
        State = ?,
        PostCode = ?
    WHERE ID = ?
    </SQL>.Value

End Using

---

Quote:

---

Originally Posted by kevininstructor

Lets simplify it by taking the variable out, this is your SQL statement assigned directly to the commmand's command text.

Code:

---

Using cmd As OleDbCommand = New OleDbCommand
    cmd.CommandText = _
    <SQL>
    UPDATE CUSTOMER set
        Surname = ?,
        Given = ?,
        DateOfBirth = ?,
        Sex = ?,
        Phone = ?,
        Address = ?,
        Suburb = ?,
        State = ?,
        PostCode = ?
    WHERE ID = ?
    </SQL>.Value

End Using

---

---

I commented out the other code you gave me and pasted that code instead.. The only error i got was

Code:

---

"Command text was not set for the command object."

---

Not sure im doing the right thing though.

Here is another example

Original

Code:

---

sSQL = "select ID from CUSTOMER where ID= " & sCusID & ""

---

Alternate

Code:

---

Dim sSQL = <SQL>select ID from CUSTOMER where ID= <%= sCusID %></SQL>.Value

---

Both do the same thing but the second one w/o concatenation

Quote:

---

Originally Posted by kevininstructor

Here is another example

Original

Code:

---

sSQL = "select ID from CUSTOMER where ID= " & sCusID & ""

---

Alternate

Code:

---

Dim sSQL = <SQL>select ID from CUSTOMER where ID= <%= sCusID %></SQL>.Value

---

Both do the same thing but the second one w/o concatenation

---

Ohh ok..

I un commented out everything, and did what you just posted..

Still get the same error as above:

Code:

---

"Command text was not set for the command object."

---

Sp full code looks like:

vb Code:

Public Sub g_sSaveCustomerRecord(ByVal oThisFormA As frmCaptureCustomer, _
                                   ByVal sCusID As String, _
                                   ByVal sDBNameA As String)
        ' ---------------------------------------------------------------------
        ' Incoming Parameters: 
        '       oThisFormA  - a reference to the current form
        '                     of type frmDataCapture
        '       sStudentNumberA - The student number
        '       sDBNameA    - The Database File Name 
        ' Return value :
        '       if save successfull (no validation issues) return empty string, ""
        '       otherwise return error message
        '------------------------------------------------------------
        Dim sSaveSql As String
        Dim sConnection As String
        Dim sErrorMessage As String
        Dim sSQL As String
        Dim bStudentNumberExists As Boolean
 
        Dim oConn As OleDbConnection        'To reference a Connection obj.
        Dim oCmd_Select As OleDbCommand     'To Instantiate a Command obj used to execute the Select SQL.
        Dim oDataReader As OleDbDataReader  'To instantiate a DataReader obj.
        Dim oCmd_Update As OleDbCommand     'To Instantiate a Command obj used to execute the Insert/Update SQL.
 
        'sErrorMessage = ""
        oConn = Nothing
        oCmd_Select = Nothing
        oDataReader = Nothing
        oCmd_Update = Nothing
 
        ' --- create a database command object and set the SQL statement 
        ' --- it will execute, and the database connection object associated to it
        sConnection = "Provider=Microsoft.Jet.OLEDB.4.0;"
        sConnection = sConnection & "User ID=Admin;"
        sConnection = sConnection & "Data Source="
        sConnection = sConnection & Application.StartupPath & "\" & sDBNameA
        oConn = New OleDbConnection(sConnection)
        oConn.Open()
 
        oCmd_Select = oConn.CreateCommand()
        sSQL = <SQL>select ID from CUSTOMER where ID= <%= sCusID %></SQL>.Value
        'sSQL = "select ID from CUSTOMER where ID= " & sCusID & ""
        oCmd_Select.CommandText = sSQL
 
        ' --- execute SQL command and place results into a datareader object
        oDataReader = oCmd_Select.ExecuteReader()
 
        bStudentNumberExists = (oDataReader.Read() = True)
 
        'MessageBox.Show("bStudentNumberExists=" & bStudentNumberExists)
        ' --- Build Insert or Update Sql depending if record retrieved with select query is found
        If bStudentNumberExists Then
 
            Using cmd As OleDbCommand = New OleDbCommand
                sSaveSql = "update CUSTOMER set " _
                               & "Surname = ?," _
                               & "Given = ?," _
                               & "DateOfBirth = ?, " _
                               & "Sex = ?," _
                               & "Phone = ?," _
                               & "Address = ?," _
                               & "Suburb = ?," _
                               & "State = ?," _
                               & "PostCode = ? " _
                               & "where ID = ?;"
 
                cmd.Parameters.AddWithValue("@Surname", oThisFormA.txtCusSurname.Text.Trim)
                cmd.Parameters.AddWithValue("@Given", oThisFormA.txtCustGiven.Text.Trim)
                cmd.Parameters.AddWithValue("@DateOfBirth", CDate(oThisFormA.txtCustDOB.Text.Trim))
                cmd.Parameters.AddWithValue("@Sex", oThisFormA.txtCustSex.Text.Trim)
                cmd.Parameters.AddWithValue("@Phone", oThisFormA.txtCustPhone.Text.Trim)
                cmd.Parameters.AddWithValue("@Address", oThisFormA.txtCustAddress.Text.Trim)
                cmd.Parameters.AddWithValue("@Suburb", oThisFormA.txtCustSuburb.Text.Trim)
                cmd.Parameters.AddWithValue("@State", oThisFormA.txtCustState.Text.Trim)
                cmd.Parameters.AddWithValue("@PostCode", oThisFormA.txtCustPostCode.Text.Trim)
                cmd.Parameters.AddWithValue("@ID", CInt(oThisFormA.txtCustID.Text.Trim))
 
 
            End Using
            'sSaveSql = "update CUSTOMER set " _
            '             & "Surname = '" & oThisFormA.txtCusSurname.Text.Trim & "'," _
            '             & "Given = '" & oThisFormA.txtCustGiven.Text.Trim & "'," _
            '             & "DateOfBirth = '" & oThisFormA.txtCustDOB.Text.Trim & "', " _
            '             & "Sex = '" & oThisFormA.txtCustSex.Text.Trim & "'," _
            '             & "Phone = '" & oThisFormA.txtCustPhone.Text.Trim & "'," _
            '             & "Address = '" & oThisFormA.txtCustAddress.Text.Trim & "'," _
            '             & "Suburb = '" & oThisFormA.txtCustSuburb.Text.Trim & "'," _
            '             & "State = '" & oThisFormA.txtCustState.Text.Trim & "'," _
            '             & "PostCode = '" & oThisFormA.txtCustPostCode.Text.Trim & "'," _
            '             & "where ID ='" & oThisFormA.txtCustID.Text.Trim & "';"
 
            'Else
            '    sSaveSql = "insert into CUSTOMER(ID, Surname, Given, DateOfBirth,Sex,Phone,Address,Suburb,State,PostCode) " _
            '             & " values('" & oThisFormA.txtCustID.Text & "'," _
            '             & "'" & oThisFormA.txtCusSurname.Text.Trim & "'," _
            '             & "'" & oThisFormA.txtCustGiven.Text.Trim & "'," _
            '             & "'" & oThisFormA.txtCustDOB.Text.Trim & "'," _
            '             & "'" & oThisFormA.txtCustSex.Text.Trim & "'," _
            '             & "'" & oThisFormA.txtCustPhone.Text.Trim & "'," _
            '             & "'" & oThisFormA.txtCustAddress.Text.Trim & "'," _
            '             & "'" & oThisFormA.txtCustSuburb.Text.Trim & "'," _
            '             & "'" & oThisFormA.txtCustState.Text.Trim & "'," _
            '             & "'" & oThisFormA.txtCustPostCode.Text.Trim & "' )"
            '    MsgBox("ID:" & sCusID & " has been Added to the table")
 
 
            '    ' Be careful however with the commas and the closing bracket
        End If
 
 
        oDataReader.Close()
        oCmd_Select.Dispose()
 
 
        ' Execute sql update or insert query:
        oCmd_Update = oConn.CreateCommand()
        oCmd_Update.CommandText = sSaveSql
        oCmd_Update.ExecuteNonQuery()
 
 
 
 
 
 
    End Sub

Quote:

---

Originally Posted by coccoster

I commented out the other code you gave me and pasted that code instead.. The only error i got was

Code:

---

"Command text was not set for the command object."

---

Not sure im doing the right thing though.

---

Not sure either as the code is clearly setting the CommandText ie

Code:

---

        Using cmd As OleDbCommand = New OleDbCommand
            cmd.CommandText = _
            <SQL>
            UPDATE CUSTOMER set
                Surname = ?,
                Given = ?,
                DateOfBirth = ?,
                Sex = ?,
                Phone = ?,
                Address = ?,
                Suburb = ?,
                State = ?,
                PostCode = ?
            WHERE ID = ?
            </SQL>.Value

            MsgBox(cmd.CommandText)
        End Using

---

Or

Code:

---

        Using cmd As OleDbCommand = New OleDbCommand
            cmd.CommandText = _
            <SQL>
            UPDATE CUSTOMER set
                Surname = ?,
                Given = ?,
                DateOfBirth = ?,
                Sex = ?,
                Phone = ?,
                Address = ?,
                Suburb = ?,
                State = ?,
                PostCode = ?
            WHERE ID = ?
            </SQL>.Value

            Console.WriteLine(cmd.CommandText)
        End Using

---

Result from console write line

Code:

---

            UPDATE CUSTOMER set
                Surname = ?,
                Given = ?,
                DateOfBirth = ?,
                Sex = ?,
                Phone = ?,
                Address = ?,
                Suburb = ?,
                State = ?,
                PostCode = ?
            WHERE ID = ?

---

Quote:

---

Originally Posted by kevininstructor

Not sure either as the code is clearly setting the CommandText ie

Code:

---

        Using cmd As OleDbCommand = New OleDbCommand
            cmd.CommandText = _
            <SQL>
            UPDATE CUSTOMER set
                Surname = ?,
                Given = ?,
                DateOfBirth = ?,
                Sex = ?,
                Phone = ?,
                Address = ?,
                Suburb = ?,
                State = ?,
                PostCode = ?
            WHERE ID = ?
            </SQL>.Value

            MsgBox(cmd.CommandText)
        End Using

---

Or

Code:

---

        Using cmd As OleDbCommand = New OleDbCommand
            cmd.CommandText = _
            <SQL>
            UPDATE CUSTOMER set
                Surname = ?,
                Given = ?,
                DateOfBirth = ?,
                Sex = ?,
                Phone = ?,
                Address = ?,
                Suburb = ?,
                State = ?,
                PostCode = ?
            WHERE ID = ?
            </SQL>.Value

            Console.WriteLine(cmd.CommandText)
        End Using

---

Result from console write line

Code:

---

            UPDATE CUSTOMER set
                Surname = ?,
                Given = ?,
                DateOfBirth = ?,
                Sex = ?,
                Phone = ?,
                Address = ?,
                Suburb = ?,
                State = ?,
                PostCode = ?
            WHERE ID = ?

---

---

Does the code i posted before look right to you?

The following will work if you have Option Infer On and using Framework 3.5 or higher (VS2008 or VS2010)

I highly suggest turning Option Strict On

Code:

---

        If bStudentNumberExists Then

            Using cmd As OleDbCommand = New OleDbCommand
                                cmd.CommandText = _
                                <SQL>
                                UPDATE CUSTOMER set
                                        Surname = ?,
                                        Given = ?,
                                        DateOfBirth = ?,
                                        Sex = ?,
                                        Phone = ?,
                                        Address = ?,
                                        Suburb = ?,
                                        State = ?,
                                        PostCode = ?
                                WHERE ID = ?
                                </SQL>.Value


                cmd.Parameters.AddWithValue("@Surname", oThisFormA.txtCusSurname.Text.Trim)
                cmd.Parameters.AddWithValue("@Given", oThisFormA.txtCustGiven.Text.Trim)
                cmd.Parameters.AddWithValue("@DateOfBirth", CDate(oThisFormA.txtCustDOB.Text.Trim))
                cmd.Parameters.AddWithValue("@Sex", oThisFormA.txtCustSex.Text.Trim)
                cmd.Parameters.AddWithValue("@Phone", oThisFormA.txtCustPhone.Text.Trim)
                cmd.Parameters.AddWithValue("@Address", oThisFormA.txtCustAddress.Text.Trim)
                cmd.Parameters.AddWithValue("@Suburb", oThisFormA.txtCustSuburb.Text.Trim)
                cmd.Parameters.AddWithValue("@State", oThisFormA.txtCustState.Text.Trim)
                cmd.Parameters.AddWithValue("@PostCode", oThisFormA.txtCustPostCode.Text.Trim)
                cmd.Parameters.AddWithValue("@ID", CInt(oThisFormA.txtCustID.Text.Trim))


            End Using

        End If

---

Yep ive had Option Strict on the whole time.
I'm currently using vs2010.

Well with my code looking like:

vb Code:

Public Sub g_sSaveCustomerRecord(ByVal oThisFormA As frmCaptureCustomer, _
                                   ByVal sCusID As String, _
                                   ByVal sDBNameA As String)
        ' ---------------------------------------------------------------------
        ' Incoming Parameters: 
        '       oThisFormA  - a reference to the current form
        '                     of type frmDataCapture
        '       sStudentNumberA - The student number
        '       sDBNameA    - The Database File Name 
        ' Return value :
        '       if save successfull (no validation issues) return empty string, ""
        '       otherwise return error message
        '------------------------------------------------------------
        Dim sSaveSql As String
        Dim sConnection As String
        Dim sErrorMessage As String
        Dim sSQL As String
        Dim bStudentNumberExists As Boolean
 
        Dim oConn As OleDbConnection        'To reference a Connection obj.
        Dim oCmd_Select As OleDbCommand     'To Instantiate a Command obj used to execute the Select SQL.
        Dim oDataReader As OleDbDataReader  'To instantiate a DataReader obj.
        Dim oCmd_Update As OleDbCommand     'To Instantiate a Command obj used to execute the Insert/Update SQL.
 
        'sErrorMessage = ""
        oConn = Nothing
        oCmd_Select = Nothing
        oDataReader = Nothing
        oCmd_Update = Nothing
 
        ' --- create a database command object and set the SQL statement 
        ' --- it will execute, and the database connection object associated to it
        sConnection = "Provider=Microsoft.Jet.OLEDB.4.0;"
        sConnection = sConnection & "User ID=Admin;"
        sConnection = sConnection & "Data Source="
        sConnection = sConnection & Application.StartupPath & "\" & sDBNameA
        oConn = New OleDbConnection(sConnection)
        oConn.Open()
 
        oCmd_Select = oConn.CreateCommand()
        sSQL = <SQL>select ID from CUSTOMER where ID= <%= sCusID %></SQL>.Value
        'sSQL = "select ID from CUSTOMER where ID= " & sCusID & ""
        oCmd_Select.CommandText = sSQL
 
        ' --- execute SQL command and place results into a datareader object
        oDataReader = oCmd_Select.ExecuteReader()
 
        bStudentNumberExists = (oDataReader.Read() = True)
 
  
        ' --- Build Insert or Update Sql depending if record retrieved with select query is found
        If bStudentNumberExists Then
 
            If bStudentNumberExists Then
 
                Using cmd As OleDbCommand = New OleDbCommand
                    cmd.CommandText = _
                    <SQL>
                UPDATE CUSTOMER set 
                    Surname = ?,
                    Given = ?,
                    DateOfBirth = ?, 
                    Sex = ?,
                    Phone = ?,
                    Address = ?,
                    Suburb = ?,
                    State = ?,
                    PostCode = ?
                WHERE ID = ?
                </SQL>.Value
 
 
                    cmd.Parameters.AddWithValue("@Surname", oThisFormA.txtCusSurname.Text.Trim)
                    cmd.Parameters.AddWithValue("@Given", oThisFormA.txtCustGiven.Text.Trim)
                    cmd.Parameters.AddWithValue("@DateOfBirth", CDate(oThisFormA.txtCustDOB.Text.Trim))
                    cmd.Parameters.AddWithValue("@Sex", oThisFormA.txtCustSex.Text.Trim)
                    cmd.Parameters.AddWithValue("@Phone", oThisFormA.txtCustPhone.Text.Trim)
                    cmd.Parameters.AddWithValue("@Address", oThisFormA.txtCustAddress.Text.Trim)
                    cmd.Parameters.AddWithValue("@Suburb", oThisFormA.txtCustSuburb.Text.Trim)
                    cmd.Parameters.AddWithValue("@State", oThisFormA.txtCustState.Text.Trim)
                    cmd.Parameters.AddWithValue("@PostCode", oThisFormA.txtCustPostCode.Text.Trim)
                    cmd.Parameters.AddWithValue("@ID", CInt(oThisFormA.txtCustID.Text.Trim))
 
 
                End Using
 
            End If
 
 
            '    ' Be careful however with the commas and the closing bracket
        End If
 
 
        oDataReader.Close()
        oCmd_Select.Dispose()
 
 
        ' Execute sql update or insert query:
        oCmd_Update = oConn.CreateCommand()
        oCmd_Update.CommandText = sSaveSql
        oCmd_Update.ExecuteNonQuery()
 
    End Sub

I am getting the same

Code:

---

"Command text was not set for the command object."

---

error :(

I put Option Infer On right at the top aswell.

Quote:

---

Originally Posted by coccoster

Yep ive had Option Strict on the whole time.
I'm currently using vs2010.

Well with my code looking like:

vb Code:

Public Sub g_sSaveCustomerRecord(ByVal oThisFormA As frmCaptureCustomer, _
                                   ByVal sCusID As String, _
                                   ByVal sDBNameA As String)
        ' ---------------------------------------------------------------------
        ' Incoming Parameters: 
        '       oThisFormA  - a reference to the current form
        '                     of type frmDataCapture
        '       sStudentNumberA - The student number
        '       sDBNameA    - The Database File Name 
        ' Return value :
        '       if save successfull (no validation issues) return empty string, ""
        '       otherwise return error message
        '------------------------------------------------------------
        Dim sSaveSql As String
        Dim sConnection As String
        Dim sErrorMessage As String
        Dim sSQL As String
        Dim bStudentNumberExists As Boolean
 
        Dim oConn As OleDbConnection        'To reference a Connection obj.
        Dim oCmd_Select As OleDbCommand     'To Instantiate a Command obj used to execute the Select SQL.
        Dim oDataReader As OleDbDataReader  'To instantiate a DataReader obj.
        Dim oCmd_Update As OleDbCommand     'To Instantiate a Command obj used to execute the Insert/Update SQL.
 
        'sErrorMessage = ""
        oConn = Nothing
        oCmd_Select = Nothing
        oDataReader = Nothing
        oCmd_Update = Nothing
 
        ' --- create a database command object and set the SQL statement 
        ' --- it will execute, and the database connection object associated to it
        sConnection = "Provider=Microsoft.Jet.OLEDB.4.0;"
        sConnection = sConnection & "User ID=Admin;"
        sConnection = sConnection & "Data Source="
        sConnection = sConnection & Application.StartupPath & "\" & sDBNameA
        oConn = New OleDbConnection(sConnection)
        oConn.Open()
 
        oCmd_Select = oConn.CreateCommand()
        sSQL = <SQL>select ID from CUSTOMER where ID= <%= sCusID %></SQL>.Value
        'sSQL = "select ID from CUSTOMER where ID= " & sCusID & ""
        oCmd_Select.CommandText = sSQL
 
        ' --- execute SQL command and place results into a datareader object
        oDataReader = oCmd_Select.ExecuteReader()
 
        bStudentNumberExists = (oDataReader.Read() = True)
 
  
        ' --- Build Insert or Update Sql depending if record retrieved with select query is found
        If bStudentNumberExists Then
 
            If bStudentNumberExists Then
 
                Using cmd As OleDbCommand = New OleDbCommand
                    cmd.CommandText = _
                    <SQL>
                UPDATE CUSTOMER set 
                    Surname = ?,
                    Given = ?,
                    DateOfBirth = ?, 
                    Sex = ?,
                    Phone = ?,
                    Address = ?,
                    Suburb = ?,
                    State = ?,
                    PostCode = ?
                WHERE ID = ?
                </SQL>.Value
 
 
                    cmd.Parameters.AddWithValue("@Surname", oThisFormA.txtCusSurname.Text.Trim)
                    cmd.Parameters.AddWithValue("@Given", oThisFormA.txtCustGiven.Text.Trim)
                    cmd.Parameters.AddWithValue("@DateOfBirth", CDate(oThisFormA.txtCustDOB.Text.Trim))
                    cmd.Parameters.AddWithValue("@Sex", oThisFormA.txtCustSex.Text.Trim)
                    cmd.Parameters.AddWithValue("@Phone", oThisFormA.txtCustPhone.Text.Trim)
                    cmd.Parameters.AddWithValue("@Address", oThisFormA.txtCustAddress.Text.Trim)
                    cmd.Parameters.AddWithValue("@Suburb", oThisFormA.txtCustSuburb.Text.Trim)
                    cmd.Parameters.AddWithValue("@State", oThisFormA.txtCustState.Text.Trim)
                    cmd.Parameters.AddWithValue("@PostCode", oThisFormA.txtCustPostCode.Text.Trim)
                    cmd.Parameters.AddWithValue("@ID", CInt(oThisFormA.txtCustID.Text.Trim))
 
 
                End Using
 
            End If
 
 
            '    ' Be careful however with the commas and the closing bracket
        End If
 
 
        oDataReader.Close()
        oCmd_Select.Dispose()
 
 
        ' Execute sql update or insert query:
        oCmd_Update = oConn.CreateCommand()
        oCmd_Update.CommandText = sSaveSql
        oCmd_Update.ExecuteNonQuery()
 
    End Sub

I am getting the same

Code:

---

"Command text was not set for the command object."

---

error :(

I put Option Infer On right at the top aswell.

---

Which line number in regards to the code above is giving you this error message.

Quote:

---

Originally Posted by kevininstructor

Which line number in regards to the code above is giving you this error message.

---

101. oCmd_Update.ExecuteNonQuery()

Quote:

---

Originally Posted by coccoster

101. oCmd_Update.ExecuteNonQuery()

---

That is because you have not set the CommandText for oCmd_Update

You have set the following only

Code:

---

oCmd_Select.CommandText = sSQL

---

and

Code:

---

Using cmd As OleDbCommand = New OleDbCommand
    cmd.CommandText = _
    <SQL>
    UPDATE CUSTOMER set
        Surname = ?,
        Given = ?,
        DateOfBirth = ?,
        Sex = ?,
        Phone = ?,
        Address = ?,
        Suburb = ?,
        State = ?,
        PostCode = ?
    WHERE ID = ?
    </SQL>.Value

---

Quote:

---

Originally Posted by kevininstructor

That is because you have not set the CommandText for oCmd_Update

You have set the following only

Code:

---

oCmd_Select.CommandText = sSQL

---

and

Code:

---

Using cmd As OleDbCommand = New OleDbCommand
    cmd.CommandText = _
    <SQL>
    UPDATE CUSTOMER set
        Surname = ?,
        Given = ?,
        DateOfBirth = ?,
        Sex = ?,
        Phone = ?,
        Address = ?,
        Suburb = ?,
        State = ?,
        PostCode = ?
    WHERE ID = ?
    </SQL>.Value

---

---

:confused::confused:
I'm not sure what i have to do now, sorry mate.

Quote:

---

Originally Posted by coccoster

:confused::confused:
I'm not sure what i have to do now, sorry mate.

---

You need to set the CommandText, simple as that.

Quote:

---

Originally Posted by kevininstructor

You need to set the CommandText, simple as that.

---

Im sorry but how do i do that??

Im not quite sure i understand entirely what i need to edit/add

Quote:

---

Originally Posted by coccoster

Im sorry but how do i do that??

Im not quite sure i understand entirely what i need to edit/add

---

You set the CommandText
i.e.

MyCommand.CommandText = "Your command text"

or as I have shown using

Code:

---

<SQL>
UPDATE CUSTOMER set
    Surname = ?,
    Given = ?,
    DateOfBirth = ?,
    Sex = ?,
    Phone = ?,
    Address = ?,
    Suburb = ?,
    State = ?,
    PostCode = ?
WHERE ID = ?
</SQL>.Value

---

It is that simple.

Code:

---

<SQL>
UPDATE CUSTOMER set
    Surname = ?,
    Given = ?,
    DateOfBirth = ?,
    Sex = ?,
    Phone = ?,
    Address = ?,
    Suburb = ?,
    State = ?,
    PostCode = ?
WHERE ID = ?
</SQL>.Value

---

So i have to remove the "?" and add txtHardGiven.Text etc??

no... you assign theabove as t he command text... then you need to add the parameters in the manner in which I showed using command.parameters.addwithvalue ...

-tg