Cannot add a New Record but can update OK

[jazzz]

Hello and thanks in advance.

Here is my dilema. I have a form where I collect information and on submit I send the data to a processor page and add or edit it to an Access database. If I edit the item all works just fine but when I attempt to add a New record I receive an error in my SQL string stating I need an operator

Syntax error (missing operator) in query expression 'ReviewID ='.

ReviewID is my PK therefore it should be blank this is a new record. Here is my code maybe someone could see what I am missing.

'##########################################################
'# the admin is logged in so update or add a new record.
Dim rs_reviews, strReviewID, strReviewSQL

strReviewID = Trim(Request.Form("ReviewId"))

Set rs_reviews = Server.CreateObject("ADODB.Recordset")
strReviewSQL = "SELECT * FROM tblReview WHERE ReviewID = " & strReviewID
rs_reviews.Open strReviewSQL, objConn, adOpenKeyset, adLockOptimistic

'##################################################
'# check if we have a valid record
If NOT rs_reviews.EOF Then
'do nothing
Else
If Request.Form("txtReviewID") = "" Then
rs_reviews.AddNew
Else
Response.Redirect("admin_models.asp")
End If
End If

'##########################################################
'# update or add a model to our db

rs_reviews("ModID") = Trim(Request.Form("txtmodID"))

If LEN(Request.Form("txtreview"))=0 Then
rs_reviews("review")= null
Else
rs_reviews("review") = Trim(Request.Form("txtreview"))
End If


rs_reviews.Update

response.redirect "admin_models.asp"

'##########################################################
'# Close and clean up

If Not (rs_reviews Is Nothing) Then
rs_reviews.Close
Set rs_reviews = Nothing
End If

Life's a journey enjoy the ride...

jazzz

 

[DotNetGnat]

when you update your sql string looks like this:

update mytable set myfield='blah' where ReviewID =20

but on the otherhand when you try to add a new record...
you have this:

insert into mytable(myfield) values('blah')

you dont need ReviewID here..i guess its an auto number and it is created once you insert the record...so you dont need to mention ReviewID in your sql string...

can you show your queries...

-DNG

 

[jazzz]

DNG,

Thanks for the reply but I got it figured out after spending an afternoon on it. The database has a relationship set on this table with another. Therefore I needed to check on the FK not the PK and it works just fine now. Therefore looking at the SQL above it now reads

strModID = Trim(Request.Form("ModId")) 'FK

Set rs_reviews = Server.CreateObject("ADODB.Recordset")
strReviewSQL = "SELECT * FROM tblReview WHERE ModIDID = " & strModID

It now works regardless if I am adding a 'New record or editing an existing one.

Thanks for taking the time to take a look at this.


Life's a journey enjoy the ride...

jazzz