VB UPDATE access database

I notice that you do not have a where clause, so this command will effectively Update EVERY record in your table.  Is that the intention?

AW

Syntax Error at     Dim countFound As Integer = CType(sqlCommand.ExecuteScalar, Integer)

You are correct it should update what ever is in that specific text box.

"You are correct it should update what ever is in that specific text box" what does that mean?  

You need to specify which RECORD in the table to update, using a WHERE clause in the SQL.  Yes, you will use the Textboxes for the source of the values to update the fileds TO, but they will not specify WHICH single record is to be updated.

and where does your syntax error come in?  you have only shown me the SQL that you are trying to use to update the table.  You use an

       sqlCommand.ExecuteNONScalar()

to execute an Update query.

AW

First thing is that this is a Access Query String. There is not a ExecuteNonScalar(). I added what you suggested being a where [id] = '"& txtid.text & "'. I am still getting Syntax Errors. I hope you or someone can fix it!


Dim connectionString As String = _
     "Provider=Microsoft.Jet.OLEDB.4.0;" & _
     "Data Source=" & Application.StartupPath & "\Inventory.mdb;" & _
     "User ID=Admin;" & _
     "Password="

        Dim updatequery As New OleDbConnection(connectionString)

        ' Create a ADO.NET OleDbCommand.

******* Access Query String ******
        Dim sqlInventoryString As String = "UPDATE Inventory Set [First Name] = '" & txtfirstname.Text & "' , [Last Name] = '" & txtlastname.Text & "', [Phone] = '" & txtphone.Text & "',  [Description] = '" & txtdescription.Text & "',  [Order Placed] = '" & checkboxorderplaced.Checked & "', [Order Shipped] = '" & checkboxordershipped.Checked & "', [Order Received] = '" & checkboxorderreceived.Checked & "', [Shipping] = '" & txtshipping.Text & "', [Updated By]='" & LogOnForm.UserNameTextBox.Text & "', where [ID]='" & txtid.Text & "';"
        Dim sqlCommand As New OleDb.OleDbCommand(sqlInventoryString, updatequery)
        ' Open the database connection.

        updatequery.Open()

        ' Run the sqlCommand.

        Dim countFound As Integer = CType(sqlCommand.ExecuteNonQuery, Integer) <-- this is where it keeps saying syntax error

        ' Close the database connection.

        updatequery.Close()

        ' If countFound = 1 a record with a matching UserName and Password
        ' was found..

        If countFound = 1 Then
            tmlabel.Text = "Update Unsuccessful please try again"
        Else

            tmlabel.Text = "Update Successful"

            Me.tmfirstname.Clear()
            Me.tmlastname.Clear()
            Me.tmdescripition.Clear()
            Me.tmphone.Clear()
            Me.tmshipping.Clear()
            Me.tmorderplaced.Checked = False
            Me.tmorderreceived.Checked = False
            Me.tmordershipped.Checked = False
            Me.tmovernight.Checked = False
            Me.tmground.Checked = False
            Me.tm2day.Checked = False
            Me.tm3day.Checked = False
            Me.tmother.Checked = False

            buttonload.Visible = True
            tmlistbox.Visible = False
            buttonupdate.Visible = False
            buttonedit.Visible = False

I must be slow ha ha. I can not find the executenonscalar method for the life of me. I imported system.data.oledb and I still cant find it. Thank you so much for your help as i believe we are right around the corner from solving this.

Sorry, I was sort of right the first time.

replace:

Dim countFound As Integer = CType(sqlCommand.ExecuteNonQuery, Integer)

with

Dim countFound As Integer = sqlCommand.ExecuteNonQuery()

The return value should be the number of rows updated by the SQL, and returns an Integer value by definition, so no need to use cType to convert the resulting value to an Integer - it already is one.

AW

I am still getting UPDATE syntax erros?? whats wrong with me update statment? I am sooo sorry this is taking so long I am kinda new to coding

Dim sqlInventoryString As String = "UPDATE Inventory Set ([First Name] = '" & txtfirstname.Text & "' , [Last Name] = '" & txtlastname.Text & "', [Phone] = '" & txtphone.Text & "',  [Description] = '" & txtdescription.Text & "',  [Order Placed] = '" & checkboxorderplaced.Checked & "', [Order Shipped] = '" & checkboxordershipped.Checked & "', [Order Received] = '" & checkboxorderreceived.Checked & "', [Shipping] = '" & txtshipping.Text & "', [Updated By]='" & LogOnForm.UserNameTextBox.Text & "') where [ID]='" & txtid.Text & "';"
       

I also for got to say for your troubles I will give you 500 points even if you  are not the accepted answer. Thanks for your help

Is your ID field a NUMBER or TEXT?  
If it is a Number, then drop the '...' around txtID.Text.

also, drop the  (...) around the Set part:

Dim sqlInventoryString As String = "UPDATE Inventory Set [First Name] = '" & txtfirstname.Text & "' , [Last Name] = '" & txtlastname.Text & "', [Phone] = '" & txtphone.Text & "',  [Description] = '" & txtdescription.Text & "',  [Order Placed] = '" & checkboxorderplaced.Checked & "', [Order Shipped] = '" & checkboxordershipped.Checked & "', [Order Received] = '" & checkboxorderreceived.Checked & "', [Shipping] = '" & txtshipping.Text & "', [Updated By]='" & LogOnForm.UserNameTextBox.Text & "' where [ID]='" & txtid.Text & "';"

The proper syntax is

Update <TableName> SET Field1 = Value1, Field2 = Value2, Field3 = Value3 Where Field4 = Value4

NOT

Update <TableName> SET (Field1 = Value1, Field2 = Value2, Field3 = Value3) Where Field4 = Value4
THIS STATEMENT IS IN ERROR

AW        

This thing is being a pain in the butt.

txt ID is a text box that has the ID number from the table in it. So you make that call ha ha

new error:

Data type mismatch in criteria expression. at the Dim countFound As Integer = sqlCommand.ExecuteNonQuery() portion of the code

You really dont know how much help you have been! Thanks

Can you set a breakpoint on that line, then in immediate mode (CTRL-G), print the value being returned by the sqlCommand.ExecuteNonQuery()  call?

AW

also, I understand the the txtID.Text is in fact text.  What I was asking was about the field (in the Table) ID.  Is that defined as a Number value, or it is a Text value?

AW