using the SELECT command help [Archive]

psyc635

01-26-2003, 10:04 PM

I used the select command to display certain parts of info for my page from a database. Up until now, i've used an autonumber column, but now, i would like to use an autonumber column or another so there are two ways to select it. Im not sure how to do this, i got the first one to work with the 2nd one in there, but only the first one does anything, here is the code i have, please help

<% 'specify the provider
strProvider="Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & Server.MapPath("data.mdb")

'create and open a connection
set dbConn = Server.CreateObject("ADODB.Connection")
dbConn.Open strProvider
set dbConn2 = Server.CreateObject("ADODB.Connection")
dbConn2.Open strProvider %>
</head>

<body>

<div class="header"><h3></span></h3></div>

<hr size=1 width=90%>

<div align="center">
<form method="post">
<input type="text" size="20" name="idform"></input>
<input type="submit" value="Submit"></input>
</form>
<% rowCount=0
'define the options query
strSQL="SELECT DISTINCT app_name_version FROM tbl1"

'create and open the options recordset
set rsType=Server.CreateObject("ADODB.Recordset")
rsType.Open strSQL, dbConn, , , adCmdText %>

<form method="post">
Choose Control Number

<select name="prodType">
<% do until rsType.EOF %>
<option><%= rsType("app_name_version")%>
<% rsType.MoveNext
loop
'close options recset and flush from memory
rsType.Close
set rsType = Nothing %>
</select> <input type="submit" value="Submit">
</form>

<% if not isEmpty(Request("prodType")) OR not isEmpty(Request("idform")) then
strType=Request("prodType")
strType2=Request("idform")
%>
<h5>. . . retrieved <%= strType %> <%= strType2 %>records from the tbl1 Table</h5>

<% 'define the tbl1 query
strQuery2 = "SELECT * FROM tbl1 WHERE app_name_version = '" & strType & "'"
strQuery = "SELECT * FROM tbl1 WHERE id = " & strType2 &""
'create and open the selected recordset
set rsProds=Server.CreateObject("ADODB.Recordset")
set rsProds2=Server.CreateObject("ADODB.Recordset")
rsProds.Open strQuery, dbConn, , , adCmdText
rsProds2.Open strQuery2, dbConn2, , , adCmdText %>

When i select the data from the ID column it works fine, but when i try to use the app_name_version column, it fails, i get this error message, what am i doing wrong?

Error Message:

Microsoft JET Database Engine (0x80040E14)
Syntax error (missing operator) in query expression 'id ='.

head8k

01-26-2003, 11:09 PM

It's very difficult to diagnose this without seeing the whole SQL statement that is causing the error. If I were you, I'd comment out the line which executes the SQL statement and replace it with:

response.write "<h3>" & strQuery2 & "</h3>"

or similar and then post the whole SQL statement back here.

psyc635

01-26-2003, 11:57 PM

what do you mean by the whole sql statement?

head8k

01-27-2003, 10:55 AM

I mean the whole string as passed to the connection object. In this case, strQuery2 in it's final form with the variable strType included in full.

whammy

01-28-2003, 02:10 AM

I already suggested this to psyc635 with regards to SQL Statements... if you recall I said something like:

"When you're having problems with a SQL Statement, the first step in debugging is to do this:"

MYSQLSTATEMENT = "WHATEVER"
Response.Write(MYSQLSTATEMENT) : Response.End

That way, you can see EXACTLY what the database is trying to execute. Usually it's much easier to spot syntax/datatype errors this way, which is always the first debugging step.