help with Insert SQl (newbie)

[isaac2004]

hi i am having a problem inserting a new record into my database it
throws this error

[Microsoft][ODBC Microsoft Access Driver] Data type mismatch in
criteria expression.

i looked up the info for this and it says im trying to add an empty
string to the Date/Time field in my db

here is my code watch for line breaks

<% dim intStars, strTitle, strReview, strName, strCity, strISBN,
strSQL, objRS

strISBN = Request.Cookies("ISBN")

'function to clean querystrings
function CleanIn(strIn)
strIn = replace(strIn, "'", "''")
CleanIn = replace(strIn, "<", " ")
end function

'declare local variables
intStars = CleanIn(request.querystring("dlStars"))
strTitle = CleanIn(request.querystring("tbTitle"))
strReview = CleanIn(request.querystring("tbReview"))
strName = CleanIn(request.querystring("tbName"))
strCity = CleanIn(request.querystring("tbCity"))


If len(intStars) < 1 or _
len(strTitle) < 3 or _
len(strReview) < 9 or _
len(strName) < 3 or _
len(strCity) < 2 then
'invalid field
response.write("<h2>Please Complete all
Fields.</h2><br><br><INPUT TYPE='button' VALUE='< Go
Back'onClick='history.back()'><br>")
response.end
end if
'create SQL statement and open the database
strSQL = "INSERT INTO tblReviews (revTitle, strStars, strName,
strLocation, ISBN, dtDate, strReview) " & _
"VALUES ('" & strTitle & "', '" & intStars & "', '" &
strName & "', '" & strCity & "', '" & Now() & "', '" & strISBN & "', '"
& strReview & "')"

Set objRS = Server.CreateObject("ADODB.Recordset")
objRS.Open strSQL, objConn, adOpenForwardOnly, adLockOptimistic


'response.write "strSQL:" & strSQL & "<br>"



Response.Cookies("ISBN").Expires = Date() - 1

response.redirect
"http://yorktown.cbe.wwu.edu/06Winter/levini/A07/ProductPage.asp?isbn="
& strISBN
%>

is there something wrong with my SQL, thanks for the help

 

[Adrienne Boswell]

hi i am having a problem inserting a new record into my database it
throws this error

[Microsoft][ODBC Microsoft Access Driver] Data type mismatch in
criteria expression.

i looked up the info for this and it says im trying to add an empty
string to the Date/Time field in my db

here is my code watch for line breaks

<% dim intStars, strTitle, strReview, strName, strCity, strISBN, strSQL,
objRS

strISBN = Request.Cookies("ISBN")

'function to clean querystrings
function CleanIn(strIn)
strIn = replace(strIn, "'", "''")
CleanIn = replace(strIn, "<", " ")
end function

'declare local variables
intStars = CleanIn(request.querystring("dlStars"))
strTitle = CleanIn(request.querystring("tbTitle"))
strReview = CleanIn(request.querystring("tbReview"))
strName = CleanIn(request.querystring("tbName"))
strCity = CleanIn(request.querystring("tbCity"))


If len(intStars) < 1 or _
len(strTitle) < 3 or _
len(strReview) < 9 or _
len(strName) < 3 or _
len(strCity) < 2 then
'invalid field
response.write("<h2>Please Complete all
Fields.</h2><br><br><INPUT TYPE='button' VALUE='< Go
Back'onClick='history.back()'><br>")
response.end
end if
'create SQL statement and open the database
strSQL = "INSERT INTO tblReviews (revTitle, strStars, strName,
strLocation, ISBN, dtDate, strReview) " & _
"VALUES ('" & strTitle & "', '" & intStars & "', '" &
strName & "', '" & strCity & "', '" & Now() & "', '" & strISBN & "', '"
& strReview & "')"

Set objRS = Server.CreateObject("ADODB.Recordset")
objRS.Open strSQL, objConn, adOpenForwardOnly, adLockOptimistic


'response.write "strSQL:" & strSQL & "<br>"



Response.Cookies("ISBN").Expires = Date() - 1

response.redirect
"http://yorktown.cbe.wwu.edu/06Winter/levini/A07/ProductPage.asp?isbn="
& strISBN
%>

is there something wrong with my SQL, thanks for the help

1. What does the response.write of strSQL say?
2. Since you are using Access, you probably need to delimit the date field
with #
3. Why are you opening a recordset to do an insert?

I'm sure someone will come along shortly and say something about dynamic
SQL.

 

[isaac2004]

1. What does the response.write of strSQL say?

strSQL:INSERT INTO tblReviews (revTitle, strStars, strName,
strLocation, ISBN, dtDate, strReview) VALUES ('sdafdsafsadas', '5',
'fdsfsdfsdf', 'dsgfsdfd', '3/12/2006 1:42:00 PM', '0789723107',
'dfsdfdsfdsadsadsadasdsa')

2. Since you are using Access, you probably need to delimit the date field

with #

sorry dont unerstand that

3. Why are you opening a recordset to do an insert?

dont you have to when you reference an object to the record set

thanks