Hi,





I am having trouble with updating records in a database.  Basically the 

problem is that i have created a 'concatenated' date code variable:





        DateCode = Edit_Month & Edit_Year & User_ID

      



I now want to update all records in the data base with that code.  So the 

beginning of the update should look something like:

	

        Set xyzRS = Server.CreateObject("ADODB.Recordset")

        xyzRSRS.Open "SELECT * FROM people where DateCode='"&DateCode,  

        Connect,adOpenDynamic,adLockOptimistic



When i run the completed code, i get a syntax error

[Microsoft][ODBC Microsoft Access Driver] Syntax error in string in query 

expression 'DateCode='January2002299'.  



what is wrong with the code.  The Datebase field DateCode is Text!  Have i 

got the '" &datecode, part correct.





Your Help is Valued!!!! :)



Cheers,



Andrew

<%

strSQL = _

    "SELECT * FROM People WHERE DateCode = '" & DateCode & "'"



Response.Write(strSQL)

%>



and you will see the difference between the above and what you are doing

currently.



Secondly, when you want to update a set of records, don't extract them all

then update them one at a time - that's the worst way to use a database -

avoid it if at all possible. Databases work on sets, so if you can update a

whole set at a time you're going to get much better performance:



<%

strSQL = _

    "UPDATE People " & _

    "SET field1 = '" & strSomevalue & "' " & _

    "WHERE DateCode = '" & strDateCode & "'"



objConn.Execute strSQL,,adCmdText+adExecuteNoRecords

%>



Cheers

Ken



~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

From: "Andrew Matheson" <amatheso@y...>

Subject: [access_asp] Syntax Error!!





: Hi,

:

:

: I am having trouble with updating records in a database.  Basically the

: problem is that i have created a 'concatenated' date code variable:

:

:

:         DateCode = Edit_Month & Edit_Year & User_ID

:

:

: I now want to update all records in the data base with that code.  So the

: beginning of the update should look something like:

:

:         Set xyzRS = Server.CreateObject("ADODB.Recordset")

:         xyzRSRS.Open "SELECT * FROM people where DateCode='"&DateCode,

:         Connect,adOpenDynamic,adLockOptimistic

:

: When i run the completed code, i get a syntax error

: [Microsoft][ODBC Microsoft Access Driver] Syntax error in string in query

: expression 'DateCode='January2002299'.

:

: what is wrong with the code.  The Datebase field DateCode is Text!  Have i

: got the '" &datecode, part correct.

:

:

: Your Help is Valued!!!! :)



~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

You need to add a closing single quote to the SQL string after DateCode.

Unfortunately, your error message makes it only seem like there is already a

closing single quote, but that is part of the error message. Try this:



    ' Instantiate the recordset object

    Set xyzRS = Server.CreateObject("ADODB.Recordset")



    ' Build the SQL string

    ' Put in final closing single quote after DateCode

    SqlString = "SELECT * FROM people " _

                    & " WHERE DateCode = '" & DateCode & "'"



Also, shouldn't this be xyzRS.open, not xyzRSRS.open:



    xyzRSRS.Open SqlString, Connect, adOpenDynamic, adLockOptimistic



May I also suggest commenting your code to make it more understandable to

yourself and others?



-- Z









----- Original Message -----

From: "Andrew Matheson" <amatheso@y...>

To: "Access ASP" <access_asp@p...>

Sent: Sunday, February 03, 2002 7:46 PM

Subject: [access_asp] Syntax Error!!





> Hi,

>

>

> I am having trouble with updating records in a database.  Basically the

> problem is that i have created a 'concatenated' date code variable:

>

>

>         DateCode = Edit_Month & Edit_Year & User_ID

>

>

> I now want to update all records in the data base with that code.  So the

> beginning of the update should look something like:

>

>         Set xyzRS = Server.CreateObject("ADODB.Recordset")

>         xyzRSRS.Open "SELECT * FROM people where DateCode='"&DateCode,

>         Connect,adOpenDynamic,adLockOptimistic

>

> When i run the completed code, i get a syntax error

> [Microsoft][ODBC Microsoft Access Driver] Syntax error in string in query

> expression 'DateCode='January2002299'.

>

> what is wrong with the code.  The Datebase field DateCode is Text!  Have i

> got the '" &datecode, part correct.

>

>

> Your Help is Valued!!!! :)

>

> Cheers,

>

> Andrew

>




$subst('Email.Unsub').

>