Update SQL Statement

[Kielo]

Should this work, I can't see a problem with it... but it's not working

Private Sub myAddNew(ByVal CusRef As Integer)
        Dim ConnectionString As String
        Dim SQLString As String
        Dim whichButtonDialogResult As DialogResult
        Dim dbCommand As System.Data.OleDb.OleDbCommand
        Dim Connection As System.Data.OleDb.OleDbConnection
 
        ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data "
        ConnectionString += "Source=" & "Opticians.accdb "
        Connection = New System.Data.OleDb.OleDbConnection(ConnectionString)
        'SQLString = "INSERT INTO SpecSalesTable (DateReceived) "
        SQLString = "Update SpecSalesTable "
        SQLString += "SET DateReceived = #" & Date.Today & "#"
        SQLString += "WHERE SpecSalesID = SpecIDTextBox.Text "
        whichButtonDialogResult = MessageBox.Show("Are you sure these details are correct?", "Update!", MessageBoxButtons.YesNo, MessageBoxIcon.Question)
        If whichButtonDialogResult = DialogResult.Yes Then
            Connection.Open()
            If CBool(ConnectionState.Open) Then
                dbCommand = New System.Data.OleDb.OleDbCommand(SQLString, Connection)
                Try
                    dbCommand.ExecuteNonQuery()
                    MessageBox.Show("Spectacles Returned, SpecSalesTable Updated! ")
                Catch ex As Exception
                    MessageBox.Show(" Error updating... ")    'IF ERROR DISPLAYS MESSAGE
                End Try
 
            End If
 
        End If
 
        Connection.Close()
    End Sub

[Hack]

Moved To Database Development

What is the error message?

[Kielo]

Well from that code I'm only getting my own error message, it's connecting to the database, but goes to the catch...

[GaryMazzone]

change this
MessageBox.Show(" Error updating... ") 'IF ERROR DISPLAYS MESSAGE
to this
MessageBox.Show(" Error updating... " & System.Enviornment.NewLine & ex.Message) 'IF ERROR DISPLAYS MESSAGE

and post the real error

[techgnome]

This is your problem"
SQLString += "WHERE SpecSalesID = SpecIDTextBox.Text "


Your text box is inside your strng... so it is LITTERALY USING SpecIDTextBox.Text..... not the contents...

1) In the future, try displaying your SQL in a messagebox before executring it so that you are sure you know what you are executing
2) SQL doesn't know jack about your textboxes, so you can't use the name inside it like that
3) Read the DB FAQ section on parameters, that's the best way to solve the problem.

-tg

[Kielo]

Data type mismatch in criteria expression...

SQLString = "Update SpecSalesTable "
        SQLString += "SET DateReceived = #" & Date.Today & "#"
        SQLString += "WHERE SpecSalesID = '" & SpecIDTextBox.Text & "' "

[Kielo]

"Update SpecSalesTable SET DateReceived = #25/03/2010#"

There's the SQL String

[GaryMazzone]

Is SpecSalesId a Number or Text?

If a number then

SQLString = "Update SpecSalesTable "        
SQLString &= "SET DateReceived = #" & Date.Today & "#"        
SQLString &= "WHERE SpecSalesID = " & SpecIDTextBox.Text

if text then

SQLString = "Update SpecSalesTable "        
SQLString &= "SET DateReceived = #" & Date.Today & "#"        
SQLString &= "WHERE SpecSalesID = '" & SpecIDTextBox.Text & "'"

[Kielo]

Once again Gary your my saviour

number....

Thanks to everyone!

[si_the_geek]

"Update SpecSalesTable SET DateReceived = #25/03/2010#"

There's the SQL String

SQLString += "SET DateReceived = #" & Date.Today & "#"

You have got a bug there, and you are likely to notice it next week.

DateReceived is only being set to the 25th of March because there aren't 25 months... on April 1st your SQL statement will include #01/04/2010# , which will mean that January 4th will be stored in the database.

Either append the value to the SQL statement correctly (as explained in the FAQ article How do I use values (numbers, strings, dates) in SQL statements? ), or save yourself lots of hassle for that and lots of other things by following TG's advice.

For an explanation of why you should be using parameters (and links to code examples), see the article Why should I use Parameters instead of putting values into my SQL string? from our Database Development FAQs/Tutorials (at the top of this forum).