SQL error message

[j william]

I am trying to query a database and I continue to get the same error message - [Microsoft][ODBC Microsoft Access 97 Driver] Data type mismatch in
criteria expression.

example - MyDb.dbSQL = "SELECT * from Userdb WHERE ID='" & Request ("ID&#34 & "' AND UserID='" & Request ("UserID&#34 & "' AND Password = '" & Request ("Password&#34 & "'"

Is there an easier way to allow each student to access their work to edit, one record at a time.

Your help is greatly appreciated.

[John]

Hi J,

You're on the right track. I suspect that one of your fields is a
numeric field and you're surrounding it with quotes - thereby causing
the problem. Easy way to debug is to put your SELECT string into a
variable (i.e. mySQL), and then write that out, and then finally set
MyDb.dbSQL = mySQL. This way you can see exactly what you're statement
looks like after the SQL statement is "built up" from your variables.

If it still doesn't work, reply back here with the exact output of that
string (Response.write(mySQL)).

Thanks,
John

On 2/24/99 3:32:03 PM, j william wrote:
> I am trying to query a database and I continue to get the same error
> message - [Microsoft][ODBC Microsoft Access 97 Driver] Data type mismatch
> in
criteria expression.

example - MyDb.dbSQL = "SELECT * from
> Userdb WHERE ID='" & Request ("ID&#34 & "' AND
> UserID='" & Request ("UserID&#34 & "' AND Password =
> '" & Request ("Password&#34 & "'"

Is there
> an easier way to allow each student to access their work to edit, one
> record at a time.

Your help is greatly appreciated.

[J Williams]

I put the select string into a variable and set the response.write and the error message did not appear on the screen, nor anything from the response.write ("mysql&#34



On 2/24/99 8:15:14 PM, John wrote:
> Hi J,

You're on the right track. I suspect that one of your fields
> is a
numeric field and you're surrounding it with quotes - thereby
> causing
the problem. Easy way to debug is to put your SELECT string into
> a
variable (i.e. mySQL), and then write that out, and then finally
> set
MyDb.dbSQL = mySQL. This way you can see exactly what you're
> statement
looks like after the SQL statement is "built up" from
> your variables.

If it still doesn't work, reply back here with the
> exact output of that
string
> (Response.write(mySQL)).

Thanks,
John

On 2/24/99 3:32:03 PM, j
> william wrote:
> I am trying to query a database and I continue to get
> the same error
> message - [Microsoft][ODBC Microsoft Access 97 Driver]
> Data type mismatch
> in
criteria expression.

example - MyDb.dbSQL
> = "SELECT * from
> Userdb WHERE ID='" & Request
> ("ID&#34 & "' AND
> UserID='" & Request
> ("UserID&#34 & "' AND Password =
> '" & Request
> ("Password&#34 & "'"

Is there
> an easier way to
> allow each student to access their work to edit, one
> record at a
> time.

Your help is greatly appreciated.

[John]

Try using code like this:

<%
Set MyDb = Server.CreateObject(&#34;AspDB.Pro&#34
MyDb.dbDSN=&#34;nwind&#34;
MyDb.dbMode=&#34;grid&#34;
myID = 20
myName = &#34;%ch%&#34;
mySQL = &#34;SELECT * FROM Products WHERE ProductID > &#34; & myID & _
&#34; AND ProductName LIKE &#39;&#34; & myName & &#34;&#39;&#34;
Response.Write(&#34;mySQL = [&#34; & mySQL & &#34;]<P>&#34
MyDb.dbSQL= mySQL
MyDb.dbUnit=99
MyDb.ASPdbPro
%>

This code works perfectly on the MS Access Northwind database.
Let us know what happens when you try something like this.

John


On 2/24/99 8:26:51 PM, J Williams wrote:
> I put the select string into a variable and set the response.write and the
> error message did not appear on the screen, nor anything from the
> response.write (&#34;mysql&#34



On 2/24/99 8:15:14 PM, John wrote:
>
> Hi J,

You&#39;re on the right track. I suspect that one of your
> fields
> is a
numeric field and you&#39;re surrounding it with quotes -
> thereby
> causing
the problem. Easy way to debug is to put your SELECT
> string into
> a
variable (i.e. mySQL), and then write that out, and then
> finally
> set
MyDb.dbSQL = mySQL. This way you can see exactly what
> you&#39;re
> statement
looks like after the SQL statement is &#34;built
> up&#34; from
> your variables.

If it still doesn&#39;t work, reply
> back here with the
> exact output of that
string
>
> (Response.write(mySQL)).

Thanks,
John

On 2/24/99 3:32:03 PM, j
>
> william wrote:
> I am trying to query a database and I continue to get
>
> the same error
> message - [Microsoft][ODBC Microsoft Access 97
> Driver]
> Data type mismatch
> in
criteria expression.

example -
> MyDb.dbSQL
> = &#34;SELECT * from
> Userdb WHERE ID=&#39;&#34; &
> Request
> (&#34;ID&#34 & &#34;&#39; AND
> UserID=&#39;&#34; & Request
>
> (&#34;UserID&#34 & &#34;&#39; AND Password =
> &#39;&#34; &
> Request
> (&#34;Password&#34 & &#34;&#39;&#34;

Is there
> an
> easier way to
> allow each student to access their work to edit, one
>
> record at a
> time.

Your help is greatly appreciated.