Hi Haribo,
Let's take another look at the syntax:
Code: Select all
revExecuteSQL databaseID, SQLStatement [,{variablesList | arrayName}]
Obviously, you have the first part working, but the trouble is in the SQL statement and the variables list.
There are three ways of executing UPDATE statements in your database.
1. Concatenating everything yourself into a single query, foregoing the variables list or array name syntax:
Code: Select all
put "UPDATE Schueler SET" && \
"name = '" & tName & "'," && \
"vorname = '" & tVorName & "'," && \
"strasse = '" & tStrasse & "'," && \
/* ... add lines for each pair of columns and variables ... */
"email = '" & tEmail & "'" && \
"WHERE SchuelerID = " & tNr \
into tSQLstatement
revExecuteSQL gConID, tSQLstatement
put the result into tResult
if tResult is not a number then answer error tResult
Note the apostrophe characters around string columns.
2. Using the variables list, you insert the :1, :2, ..., :n placeholders into your query as follows:
Code: Select all
put "UPDATE Schueler SET" && \
"name = :1," && \
"vorname = :2," && \
"strasse = :3," && \
/* ... add lines for each pair of columns and variables ... */
"email = :99" && \
"WHERE SchuelerID = :100" \
into tSQLstatement
revExecuteSQL gConID, tSQLstatement, \
"tName", "tVorname", "tStrasse", \
/* ... add the other variable names, surrounded with quotes ... */
"tEmail", "tNr"
put the result into tResult
if tResult is not a number then answer error tResult
Note that there are no apostrophes needed here, as Revolution will add these automatically where needed.
3. Using the array approach, you collect the data into an array, using numeric keys, and then again insert the :1, :2, ..., :n placeholders into your query as follows:
Code: Select all
put field "name" into tArray[1]
put field "vorname" into tArray[2]
put field "strasse" into tArray[3]
/* ... add lines for each onscreen field ... */
put field "email" into tArray[99]
put field "nr" into tArray[100]
put "UPDATE Schueler SET" && \
"name = :1," && \
"vorname = :2," && \
"strasse = :3," && \
/* ... add lines for each pair of columns and variables ... */
"email = :99" && \
"WHERE SchuelerID = :100" \
into tSQLstatement
revExecuteSQL gConID, tSQLstatement, "tArray"
put the result into tResult
if tResult is not a number then answer error tResult
Note that there are no apostrophes needed here, as Revolution will add these automatically where needed.
Naturally, the :99 and :100 are there for you to replace with the correct sequential numbers
Of course, you won't be able to UPDATE a record if there's no Schueler record with the provided SchuelerID - at that point, the result will be the number zero, as no database records were affected.
Hope this helped,
Jan Schenkel.
