CORRECR SQL STATEMENT FOR UPDATE

Hi
I have the following mysql statement for INSERT INTO which works fine.

Private Sub cmd_SaveDataS_Click()
frmSave.Show

mysql = "UPDATE ActualOrders  Values('" & _
                txtDate.Text & "' ," & txtATHact.Text & "," & txtCHEact.Text & "," & txtDARact.Text & "," & txtNESact.Text & "," & txtSWIact.Text & "," & txtACTtotal.Text & ")"
         Debug.Print mysql

        MsgBox "Mysql = " & mysql, vbOKOnly
        Call Get_My_Connection
        myConnection.Execute (mysql)

End Sub

What I now want is   to be able to UPDATE/EDIT the records in my database, I have tried this code but get an error message saying SYNTAX ERROR IN UPDATE STATEMENT

Function Database()
Dim ad As ADODB.Connection
Set ad = New ADODB.Connection
Let ad.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Documents and Settings\andy\Desktop\Production\Planner.accdb;Persist Security Info=False;"
ad.Open

Set rs = New ADODB.Recordset
mysql = ("SELECT * FROM Actualorders WHERE [Date] = txtDate.Text")

With rs
           .LockType = 2
           .CursorType = 3
           .Open mysql, Get_My_Connection

Do While Not rs.EOF

txtDate.Text = rs!Date
txtATHact.Text = rs!Atherstone
txtCHEact.Text = rs!Chelmsford
txtDARact.Text = rs!Darlington
txtNESact.Text = rs!Neston
txtSWIact.Text = rs!Swindon
ar.MoveNext
Loop

End With
End Function

Many Thanks
Andy
samandrewAsked:
Who is Participating?
 
ee_rleeConnect With a Mentor Commented:
sorry, should be like this
Private Sub cmdSaveData_Click(Index As Integer)
Dim response As Integer
Dim mysql As String
   
mysql = "SELECT Count(*) AS RecExists FROM Actualorders WHERE Date = #" & txtDate.Text & "#"
 
 rs.Open mysql, Get_My_Connection
 
If rs!RecExists > 0 Then
      mysql = "UPDATE Actualorders Set Atherstone = " & txtATHact.Text & ", Chelmsford = " & txtCHEact.Text & ", Darlington = " & txtDARact.Text & ", Neston = " & txtNESact.Text & ", Swindon = " & txtSWIact.Text & ", DailyTotal = " & txtACTtotal.Text & " Where [Date] = #" & txtDate.Text & "#"
      'insert your insert query'
Else
    mysql = "INSERT INTO AcutalOrders (Atherstone, Chelmsford, Darlington, Neston, Swindon, DailyTotal, [Date]) Values (" &  txtATHact.Text & "," & txtCHEact.Text & "," & txtDARact.Text & "," & txtNESact.Text & "," & txtSWIact.Text & "," & txtACTtotal.Text & ", #" & txtDate.Text & "#)"
End If
 
rs.Close
Get_My_Connection.Execute mysql
 
End Sub

Open in new window

0
 
Ashish PatelCommented:
Use this, if all your columns are of char/varchar/string type

mysql = "UPDATE ActualOrders  Values('" & _
                txtDate.Text & "' ,'" & txtATHact.Text & "','" & txtCHEact.Text & "','" & txtDARact.Text & "','" & txtNESact.Text & "','" & txtSWIact.Text & "','" & txtACTtotal.Text & "')"
0
 
Ashish PatelCommented:
By the way what is the database you are using?
0
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
samandrewAuthor Commented:
Hi I am using Microsoft Access Office 2007

Thanks
Andy
0
 
samandrewAuthor Commented:
Hi have tried that code and still get the same error message, all fields except Date are numeric.

Thanks
Andy
0
 
Ashish PatelCommented:
Try this then

mysql = "UPDATE ActualOrders  Values(#" &  txtDate.Text & "# , " & txtATHact.Text & ", " & txtCHEact.Text & ", " & txtDARact.Text & ", " & txtNESact.Text & ", " & txtSWIact.Text & ", " & txtACTtotal.Text & ")"
0
 
samandrewAuthor Commented:
Hi
have the following code

Private Sub cmdSaveData_Click(Index As Integer)
Dim response As Integer
Dim mysql As String
   
response = MsgBox("You Are About To Enter Aldi Just Crumpets Orders Into The Database - DO YOU WANT TO CONTINUE?", vbQuestion + vbYesNo)

mysql = "UPDATE Actualorders  SET Values(#" & txtDate.Text & "# , " & txtATHact.Text & ", " & txtCHEact.Text & ", " & txtDARact.Text & ", " & txtNESact.Text & ", " & txtSWIact.Text & ", " & txtACTtotal.Text & ")"


           
   
        With rs
           .LockType = 2
           .CursorType = 3
           .Open mysql, Get_My_Connection
           Debug.Print mysql
     
           If .RecordCount > 0 Then
                txtDate.Text = .Fields("Date")
                txtATHact.Text = .Fields("Atherstone")
                txtCHEact.Text = .Fields("Chelmsford")
                txtDARact.Text = .Fields("Darlington")
                txtNESact.Text = .Fields("Neston")
                txtSWIact.Text = .Fields("Swindon")
                txtACTtotal.Text = .Fields("DailyTotal")
            .Update
            Set rs = Nothing
            End If
.Close
End With
       


MsgBox "Mysql = " & mysql, vbOKOnly
   
If response = vbYes Then
    MsgBox "Orders Succesfully Updated"
ElseIf response = vbNo Then
    MsgBox "Operation Has Been Cancelled - NO DATA HAS BEEN CHANGED"
End If
End Sub

and still get an error message saying SYNTAX ERROR IN UPDATE STATEMENT
but when I hover the pointer over the error line it shows for example #27-Jan-08#, 22,0,0,0,12,34
which is what I am trying to update.

Thanks
Andy
0
 
Ashish PatelCommented:
You cannot do this, this is update statement, so just after the line of update statement you have to execute the mysql like in code snippet.
Private Sub cmdSaveData_Click(Index As Integer)
Dim response As Integer
Dim mysql As String
   
response = MsgBox("You Are About To Enter Aldi Just Crumpets Orders Into The Database - DO YOU WANT TO CONTINUE?", vbQuestion + vbYesNo)
 
If response = vbYes Then
    mysql = "UPDATE Actualorders  SET Values(#" & txtDate.Text & "# , " & txtATHact.Text & ", " & txtCHEact.Text & ", " & txtDARact.Text & ", " & txtNESact.Text & ", " & txtSWIact.Text & ", " &         txtACTtotal.Text & ")"
 
    Get_My_Connection.Execute mysql
 
    MsgBox "Orders Succesfully Updated"
ElseIf response = vbNo Then
    MsgBox "Operation Has Been Cancelled - NO DATA HAS BEEN CHANGED"
End If
End Sub

Open in new window

0
 
3_SCommented:
change
mysql = ("SELECT * FROM Actualorders WHERE [Date] = txtDate.Text")
to
mysql = ("SELECT * FROM Actualorders WHERE [Date] =" & txtDate.Text)
0
 
samandrewAuthor Commented:
Hi Both

Have tried your code asvforce and get the following error,
Run-time error '-2147217904(80040e10)':
No value given for one or more required parameters.

Also tried 3 S: code and got the following error message.
Run-time error '-2147217900(80040e14)':
syntax error in UPDATE statement

Many Thanks
Andy
0
 
3_SCommented:
mysql = "UPDATE Actualorders  SET Values(#" & txtDate.Text & "# , " & txtATHact.Text & ", " & txtCHEact.Text & ", " & txtDARact.Text & ", " & txtNESact.Text & ", " & txtSWIact.Text & ", " & txtACTtotal.Text & ")"
this command will try to do an update on all rows of your table, you should add a where condition (to specify the which record(s) must be updated)

Your command is wrong (i guess your have an insert and update statement mixed up with each other)
it should look like:
"update Actualorders  set field1= #" & txtDate.Text & "#,field2=" &txtATHact.Text
update syntax
update tablename set field1=value, field2=value where recordid=id
insert syntax
insert into tablename (field1,field2) values(value,value)
0
 
samandrewAuthor Commented:
Hi
do I need to use the name of the field or just field1, field2 etc

update Actualorders  set field1= #" & txtDate.Text & "#,field2=" &txtATHact.Text

Many Thanks
Andy
0
 
3_SCommented:
you have to use the name of the field
I used field1, field2,... because I don't know the name of the fields of the table Actualorders.

don't forget to specify the where condition
0
 
samandrewAuthor Commented:
Hi
I have tried the following but I must have something wrong could you please look at my code and see what you think.

Dim response As Integer
Dim mysql As String
   
response = MsgBox("You Are About To Enter Aldi Just Crumpets Orders Into The Database - DO YOU WANT TO CONTINUE?", vbQuestion + vbYesNo)
 
If response = vbYes Then
    mysql = "UPDATE Actualorders Set Date = #" & txtDate.Text & "#,Atherstone = txtATHact.Text, Chelmsford = txtCHEact.Text, Darlington = txtDARact.Text, Neston = txtNESact.Text, Sindon = txtSWIact.Text, DailyTotal = txtACTtotal.Text"
    Where txtDate.Text = Date
   
   
     mysql _
            = " SELECT Atherstone, Chelmsford, Darlington, Neston, Swindon, DailyTotal" _
            & " FROM Actualorders " _
            & " WHERE Actualorders.[Date] Between" & mysql & " " & -7 & " And " & mysql

    Get_My_Connection.Execute mysql
 
    MsgBox "Orders Succesfully Updated"
ElseIf response = vbNo Then
    MsgBox "Operation Has Been Cancelled - NO DATA HAS BEEN CHANGED"
End If

Many Thanks
Andy
0
 
samandrewAuthor Commented:
Sorry shouldnt have the other mysql in was just using this as reference

code below which i am using

Private Sub cmdSaveData_Click(Index As Integer)
Dim response As Integer
Dim mysql As String
   
response = MsgBox("You Are About To Enter Aldi Just Crumpets Orders Into The Database - DO YOU WANT TO CONTINUE?", vbQuestion + vbYesNo)
 
If response = vbYes Then
    mysql = "UPDATE Actualorders Set Date = #" & txtDate.Text & "#,Atherstone = txtATHact.Text, Chelmsford = txtCHEact.Text, Darlington = txtDARact.Text, Neston = txtNESact.Text, Sindon = txtSWIact.Text, DailyTotal = txtACTtotal.Text"
    Where txtDate.Text = Date
   
   
     Get_My_Connection.Execute mysql
 
    MsgBox "Orders Succesfully Updated"
ElseIf response = vbNo Then
    MsgBox "Operation Has Been Cancelled - NO DATA HAS BEEN CHANGED"
End If


End Sub

Thanks

Andy
0
 
ee_rleeCommented:
try this one,

it doesn't makes sense thought. you are updating [Date]=txtDate.text when [Date]=txtDate.text.

is that the condition you really want. if it is, then you could just remove
Set [Date] = #" & txtDate.Text & "#,
from the statement below
mysql = "UPDATE Actualorders Set [Date] = #" & txtDate.Text & "#, Atherstone = " & txtATHact.Text & ", Chelmsford = " & txtCHEact.Text & ", Darlington = " & txtDARact.Text & ", Neston = " & txtNESact.Text & ", Sindon = " & txtSWIact.Text & ", DailyTotal = & " & txtACTtotal.Text & " Where [Date] = #" & txtDate.Text & "#"

Open in new window

0
 
samandrewAuthor Commented:
Hi
This now works great thanks but befor I award points would you be able to offer any adice please.

I want to have a SAVE button which saves a number of txtboxs(7 in total)on each day of the week to my database. Im sure I can do this with an INSERT INTO without any help, the problem is I will not have all the values for each txtbox at the same time hence why I want a UPDATE STATEMENT. If i am right? by using the INSERT INTO each time i save this will then add additional records(I do not want this) Do you know how i can first save the record and not allow any duplicates and then edit the record when I have the values for the remaning txtboxs.

Many Thanks
Andy
0
 
ee_rleeCommented:
You could first check if a record already exists for the current date:

SELECT Count(*) AS Exists FROM ActualOrders WHERE A=[Date] = #" & txtDate.Text & "#"

if Exists>0 then, use the update query, else use the insert query.
0
 
samandrewAuthor Commented:
Hi
could you possibly give me sample code so i get the correct syntax

Many Thanks
Andy
0
 
ee_rleeCommented:
try this

mysql = "SELECT Count(*) AS Exists FROM ActualOrders WHERE A=[Date] = #" & txtDate.Text & "#"

rs.open mysql, Get_My_Connection

if rs!ActualOrders>0 then
      insert your update query
else
      insert your insert query
end if
0
 
samandrewAuthor Commented:
Hi
Have tried the code like this

Private Sub cmdSaveData_Click(Index As Integer)
Dim response As Integer
Dim mysql As String
   
mysql = "SELECT Count(*) AS Exists FROM Actualorders WHERE A=[Date] = #" & txtDate.Text & "#"

rs.Open mysql, Get_My_Connection

If rs!ActualOrders > 0 Then
      mysql = "UPDATE Actualorders Set Atherstone = " & txtATHact.Text & ", Chelmsford = " & txtCHEact.Text & ", Darlington = " & txtDARact.Text & ", Neston = " & txtNESact.Text & ", Swindon = " & txtSWIact.Text & ", DailyTotal = " & txtACTtotal.Text & " Where [Date] = #" & txtDate.Text & "#"
      'insert your insert query'
Else

      mysql = "INSERT INTO Actualorders Set Atherstone = " & txtATHact.Text & ", Chelmsford = " & txtCHEact.Text & ", Darlington = " & txtDARact.Text & ", Neston = " & txtNESact.Text & ", Swindon = " & txtSWIact.Text & ", DailyTotal = " & txtACTtotal.Text & " Where [Date] = #" & txtDate.Text & "#"
   
End If
End Sub

And get an error message as below

The SELECT statement includes a reserved word or an argument name that is misspelled or missing
or punctuation is incorrect

Many Thanks
Andy

0
 
ee_rleeCommented:
sorry, change this

mysql = "SELECT Count(*) AS Exists FROM Actualorders WHERE A=[Date] = #" & txtDate.Text & "#"
to
mysql = "SELECT Count(*) AS RecExists FROM Actualorders WHERE A=[Date] = #" & txtDate.Text & "#"


and

If rs!ActualOrders > 0 Then
to
If rs!RecExists > 0 Then
0
 
samandrewAuthor Commented:
Hi
have changed the code and still does not seem to work, furthermore when I click on another command button to show a different days figures I get an error message saying

Operation is not allowed when the object is open.

Private Sub cmdSaveData_Click(Index As Integer)
Dim response As Integer
Dim mysql As String
   
mysql = "SELECT Count(*) AS RecExists FROM Actualorders WHERE Date = #" & txtDate.Text & "#"

 rs.Open mysql, Get_My_Connection

If rs!RecExists > 0 Then
      mysql = "UPDATE Actualorders Set Atherstone = " & txtATHact.Text & ", Chelmsford = " & txtCHEact.Text & ", Darlington = " & txtDARact.Text & ", Neston = " & txtNESact.Text & ", Swindon = " & txtSWIact.Text & ", DailyTotal = " & txtACTtotal.Text & " Where [Date] = #" & txtDate.Text & "#"
      'insert your insert query'
Else
    mysql = "INSERT INTO Actualorders Set Atherstone = " & txtATHact.Text & ", Chelmsford = " & txtCHEact.Text & ", Darlington = " & txtDARact.Text & ", Neston = " & txtNESact.Text & ", Swindon = " & txtSWIact.Text & ", DailyTotal = " & txtACTtotal.Text & " Where [Date] = #" & txtDate.Text & "#"

End If

End Sub

Thanks
Andy
0
 
3_SCommented:
add rs.close to avoid error
Operation is not allowed when the object is open.

If rs!RecExists > 0 Then
      rs.close
      mysql = "UPDATE Actualorders Set Atherstone = " & txtATHact.Text & ", Chelmsford = " & txtCHEact.Text & ", Darlington = " & txtDARact.Text & ", Neston = " & txtNESact.Text & ", Swindon = " & txtSWIact.Text & ", DailyTotal = " & txtACTtotal.Text & " Where [Date] = #" & txtDate.Text & "#"
      'insert your insert query'
Else
    rs.close
    mysql = "INSERT INTO Actualorders Set Atherstone = " & txtATHact.Text & ", Chelmsford = " & txtCHEact.Text & ", Darlington = " & txtDARact.Text & ", Neston = " & txtNESact.Text & ", Swindon = " & txtSWIact.Text & ", DailyTotal = " & txtACTtotal.Text & " Where [Date] = #" & txtDate.Text & "#"
End If

0
 
samandrewAuthor Commented:
Hi have tried the new code and do not get an error but it does not update a record or insert a record

Thanks
Andy
0
 
3_SCommented:
do not use rs!RecExists > 0  to check if there are values or not
the count(*) always returns on record (with on field containing the number of records that fullfill the condition)

use
if not rs.eof then
    if rs.fields.item(0) > 0 then
       ' update
    else
       ' insert
    endif
else
    'this will never occur with count(*)
endif
0
 
ee_rleeCommented:
try this
Private Sub cmdSaveData_Click(Index As Integer)
Dim response As Integer
Dim mysql As String
   
mysql = "SELECT Count(*) AS RecExists FROM Actualorders WHERE Date = #" & txtDate.Text & "#"
 
 rs.Open mysql, Get_My_Connection
 
If rs!RecExists > 0 Then
      mysql = "UPDATE Actualorders Set Atherstone = " & txtATHact.Text & ", Chelmsford = " & txtCHEact.Text & ", Darlington = " & txtDARact.Text & ", Neston = " & txtNESact.Text & ", Swindon = " & txtSWIact.Text & ", DailyTotal = " & txtACTtotal.Text & " Where [Date] = #" & txtDate.Text & "#"
      'insert your insert query'
Else
    mysql = "INSERT INTO Actualorders Set Atherstone = " & txtATHact.Text & ", Chelmsford = " & txtCHEact.Text & ", Darlington = " & txtDARact.Text & ", Neston = " & txtNESact.Text & ", Swindon = " & txtSWIact.Text & ", DailyTotal = " & txtACTtotal.Text & " Where [Date] = #" & txtDate.Text & "#"
End If
 
rs.close
Get_My_Connection.Execute mysql
 
End Sub

Open in new window

0
 
samandrewAuthor Commented:
Hi eerlee
Get error message saying incorrect syntax on INSERT INTO statement, could this be because my Date field is the first in the database and not Atherstone?

Hi 3 S
As im confused could you give me an example with my code please?.

Private Sub cmdSaveData_Click(Index As Integer)
Dim response As Integer
Dim mysql As String
   
mysql = "SELECT Count(*) AS RecExists FROM Actualorders WHERE Date = #" & txtDate.Text & "#"
 
 rs.Open mysql, Get_My_Connection
 
If rs!RecExists > 0 Then
      mysql = "UPDATE Actualorders Set Atherstone = " & txtATHact.Text & ", Chelmsford = " & txtCHEact.Text & ", Darlington = " & txtDARact.Text & ", Neston = " & txtNESact.Text & ", Swindon = " & txtSWIact.Text & ", DailyTotal = " & txtACTtotal.Text & " Where [Date] = #" & txtDate.Text & "#"
      'insert your insert query'
Else
    mysql = "INSERT INTO Actualorders Set Atherstone = " & txtATHact.Text & ", Chelmsford = " & txtCHEact.Text & ", Darlington = " & txtDARact.Text & ", Neston = " & txtNESact.Text & ", Swindon = " & txtSWIact.Text & ", DailyTotal = " & txtACTtotal.Text & " Where [Date] = #" & txtDate.Text & "#"
End If
 
rs.Close
Get_My_Connection.Execute mysql
 
End Sub

Many Thanks
Andy
0
 
ee_rleeCommented:
try this

you must not set the date as a condition since you are inserting a new record
Private Sub cmdSaveData_Click(Index As Integer)
Dim response As Integer
Dim mysql As String
   
mysql = "SELECT Count(*) AS RecExists FROM Actualorders WHERE Date = #" & txtDate.Text & "#"
 
 rs.Open mysql, Get_My_Connection
 
If rs!RecExists > 0 Then
      mysql = "UPDATE Actualorders Set Atherstone = " & txtATHact.Text & ", Chelmsford = " & txtCHEact.Text & ", Darlington = " & txtDARact.Text & ", Neston = " & txtNESact.Text & ", Swindon = " & txtSWIact.Text & ", DailyTotal = " & txtACTtotal.Text & " Where [Date] = #" & txtDate.Text & "#"
      'insert your insert query'
Else
    mysql = "INSERT INTO Actualorders Set Atherstone = " & txtATHact.Text & ", Chelmsford = " & txtCHEact.Text & ", Darlington = " & txtDARact.Text & ", Neston = " & txtNESact.Text & ", Swindon = " & txtSWIact.Text & ", DailyTotal = " & txtACTtotal.Text & ", [Date] = #" & txtDate.Text & "#"
End If
 
rs.Close
Get_My_Connection.Execute mysql
 
End Sub

Open in new window

0
 
samandrewAuthor Commented:
Hi
Sorry for the confusion seems I had a typo error as embarissingly found by my wife as she was trying to pry me away from the computer, many thanks for your help.

mysql = "INSERT INTO AcutalOrders (Atherstone, Chelmsford, Darlington, Neston, Swindon, DailyTotal, [Date]) Values (" &  txtATHact.Text & "," & txtCHEact.Text & "," & txtDARact.Text & "," & txtNESact.Text & "," & txtSWIact.Text & "," & txtACTtotal.Text & ", #" & txtDate.Text & "#)"

Should have been Actualorders

Andy
0
All Courses

From novice to tech pro — start learning today.