2. ASPRunnerPro General questions
  3. Correct syntax for strSQLUpdate
This topic is locked

Correct syntax for strSQLUpdate

 		11/7/2007 20:06:09
ASPRunnerPro General questions
P
 Philip author

Hello
I'm trying to update table test with values from an other table. I use the edit page with the Before record updated event. The code I use is as follows - who can help with syntax?
'** UPDATE a record in another table ****

Function BeforeEdit(dict, where)
strSQLUpdate = "UPDATE test SET H_Klasse_AOA =" & dict("H_Klasse_AOA") & " where H_Lizenz_AOA=" & dict("H_Lizenz_AOA")

dbConnection.Execute strSQLUpdate
BeforeEdit = True
End Function
' set BeforeAdd to True if you like to proceed with adding new record

' set it to False in other case
Sergey Kornilov admin 11/8/2007

Make sure you wrap text values with single quotes i.e.

"'" & dict("H_Klasse_AOA") & "'"
P
 		Philip author 11/8/2007

Sergey,
thanks, but the field H_Lizenz_AOA is number. When i use this code (where I hardcoded the value in where clause to 6405) it works.

Does it matter wether the field H_Lizenz_AOA is a key field? Do I have to change the where clause to something else?
'** UPDATE a record in another table ****
strSQLUpdate = "UPDATE masterdata SET H_Klasse_AOA =" & dict("H_Klasse_AOA") & " where H_Lizenz_AOA=6405"

dbConnection.Execute strSQLUpdate
BeforeEdit = True
' set BeforeAdd to True if you like to proceed with adding new record

' set it to False in other case
Sergey Kornilov admin 11/8/2007

Try this:

strSQLUpdate = "UPDATE masterdata SET H_Klasse_AOA =" & dict("H_Klasse_AOA") & " where " & where
P
 		Philip author 11/8/2007

Sorry - this leads to this error:
Technische Information

Error number -2147217904

Fehlerbeschreibung [Microsoft][ODBC Microsoft Access Driver] Too few parameters. Expected 1.

URL /_aoa3/aoa_v3_test/registrations_edit.asp

SQL Abfrage select * from [registrations] where ([registrations].[ID_Registration]=20989) and ([registrations].[H_Lizenz_AOA]=6405)

Solution This error means one of the following:

  1. your query contains parameters. Don't use query with parameters in ASPRunnerPro.
  2. One of field names in SQL query above is incorrect. Make sure all field names in this query are correct.
    Any other ideas?

    Philip
Sergey Kornilov admin 11/8/2007

Print your query on the web page to see what is wrong:
Response.Write strSQLUpdate
P
 		Philip author 11/8/2007

I found a solution :-)

Apparently there are different approaches for ADD or EDIT. Here are examples:
ADD page - before record added event
'** UPDATE a record in another table ****
strSQLUpdate = "UPDATE masterdata SET "

strSQLUpdate = strSQLUpdate & "H_Kategorie_AOA=" & "'" & dict("H_Kategorie_AOA") & "'" &", "

strSQLUpdate = strSQLUpdate & "H_Klasse_AOA=" & "'" & dict("H_Klasse_AOA") & "'" &", "

strSQLUpdate = strSQLUpdate & "HF_Sprache_AOA=" & "'" & dict("HF_Sprache_AOA") & "'"

strSQLUpdate = strSQLUpdate & " where H_Lizenz_AOA=" & dict("H_Lizenz_AOA")

dbConnection.Execute strSQLUpdate
BeforeAdd = True
EDIT page - before record updated event
'** UPDATE a record in another table ****
strSQLUpdate = "UPDATE masterdata SET "

strSQLUpdate = strSQLUpdate & "H_Kategorie_AOA=" & "'" & dict("H_Kategorie_AOA") & "'" &", "

strSQLUpdate = strSQLUpdate & "H_Klasse_AOA=" & "'" & dict("H_Klasse_AOA") & "'" &", "

strSQLUpdate = strSQLUpdate & "HF_Sprache_AOA=" & "'" & dict("HF_Sprache_AOA") & "'"

strSQLUpdate = strSQLUpdate & " where H_Lizenz_AOA=" & Session("UserID")

dbConnection.Execute strSQLUpdate
BeforeEdit = True
Cheers

Philip