help with Insert SQl (newbie)

12 Mar 2006 8:50 PM

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

12 Mar 2006 9:07 PM

Adrienne Boswell

Show quote

Gazing into my crystal ball I observed "isaac2004" <isaac_2***@yahoo.com>
writing in news:1142196637.151702.214880@p10g2000cwp.googlegroups.com:

> 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.

--
Adrienne Boswell
http://www.cavalcade-of-coding.info
Please respond to the group so others can share

12 Mar 2006 9:43 PM

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