MySQL coding help

I used the following code to Query a small database I wrote in MySQL 4.1.10. It works fine now but I am asking if anyone has code or can help me alter this code so that the customer can enter their order number which is the primary key and view a row or fields in a row rather than entire columns?




<%


Dim cnnSimple ' ADO connection
Dim rstSimple ' ADO recordset
Set cnnSimple = Server.CreateObject("ADODB.Connection")


' DSN
cnnSimple.Open "DSN=<username>_mysqlConn;"


Set rstSimple = cnnSimple.Execute("SELECT * FROM tblTest")



%>
<P> Connecting to mySQL DB with ODBC DSN </P>


<table border="1">
<%
Do While Not rstSimple.EOF
%>
<tr>
<td><%= rstSimple.Fields("id").Value %></td>
<td><%= rstSimple.Fields("name").Value %></td>
</tr>
<%
rstSimple.MoveNext
Loop
%>
</table>
<%
' Close our recordset and connection and dispose of the objects rstSimple.Close Set rstSimple = Nothing cnnSimple.Close Set cnnSimple = Nothing


cnnSimple.close
%>





The Doc

 

You could get the code from the QueryString like 'page.asp?id=123'

And in your page turn this..



Set rstSimple = cnnSimple.Execute("SELECT * FROM tblTest")


into this...


Dim whereClause
If (Request.QueryString("id") <> "") Then
whereClause = "WHERE id = " &amp; Replace(Request.QueryString("id"), "'", "''")
End If
Set rstSimple = cnnSimple.Execute("SELECT * FROM tblTest" &amp; whereClause)


note: the Replace method will help prevent sql injection exploit.


Joel Thoms
DiscountASP.NET
http://www.DiscountASP.NET

 

Joel:

I tried this:
I xx out the DSN just for this display...
Sorry if I do not understand fully your code from the query string like sql1.asp?id=3920
I have the other example working at: latenitepros.com/sql.asp
I named this page sql1.asp
ordernum is my primary key
Remember 3 weeks ago I knew nothing about MySQL...

The (Old) Doc

<title>Order Information</title>
<%
Dim cnnSimple ' ADO connection
Dim rstSimple ' ADO recordset
Set cnnSimple = Server.CreateObject("ADODB.Connection")
' DSN
cnnSimple.Open "DSN=xxxxxxxxxxx_mysqlConn;"
Dim whereClause
If (Request.QueryString("ordernum") <> "") Then whereClause = "Where id = " &amp; Replace(Request.QueryString("ordernum"),"''", "''")
End IF
Set rstSimple = cnnSimple.Execute("Select * From info" &amp; whereClause)
%>
<table border="1">
<%
Do While Not rstSimple.EOF
%>
<tr>
<td><%= rstSimple.Fields("ordernum").Value %></td>
<td><%= rstSimple.Fields("name").Value %></td>
<td><%= rstSimple.Fields("ordrecvd").Value %></td>
</tr>
<%
rstSimple.Movenext
Loop
%>
</table>
<%
rstSimple.Close Set rstSimple = Nothing cnnSimple.Close Set cnnSimple = Nothing
cnnSimple.close %>

 

Joel:


I tried this:
I xx out the DSN just for this display...
Sample at: latenitepros.com/tester.htm
I named this page sql1.asp and then used sql1.asp?id=10507
ordernum is my primary key... (There are (3) orders:10507,3920,6314)
I still am getting a server error...
The (Old) Doc

<title>Order Information</title>
<%
Dim cnnSimple ' ADO connection
Dim rstSimple ' ADO recordset
Set cnnSimple = Server.CreateObject("ADODB.Connection")
' DSN
cnnSimple.Open "DSN=xxxxxxxxxxx_mysqlConn;"
Dim whereClause
If (Request.QueryString("id") <> "") Then whereClause = "Where id = " &amp; Replace(Request.QueryString("id"),"''", "''")
End IF
Set rstSimple = cnnSimple.Execute("Select * From info" &amp; whereClause)
%>
<table border="1">
<%
Do While Not rstSimple.EOF
%>
<tr>
<td><%= rstSimple.Fields("ordernum").Value %></td>
<td><%= rstSimple.Fields("name").Value %></td>
<td><%= rstSimple.Fields("zip").Value %></td>
<td><%= rstSimple.Fields("price").Value %></td>
<td><%= rstSimple.Fields("ordrecvd").Value %></td>
<td><%= rstSimple.Fields("shipped").Value %></td>
</tr>
<%
rstSimple.Movenext
Loop
%>
</table>
<%
rstSimple.Close Set rstSimple = Nothing cnnSimple.Close Set cnnSimple = Nothing
cnnSimple.close %>

 

What is the server error you are getting? Can you post a URL to an example of the problem?


Joel Thoms
DiscountASP.NET
http://www.DiscountASP.NET

 

The page cannot be displayed

There is a problem with the page you are trying to reach and it cannot be displayed.




Please try the following:

• 
  <LI id=instructionsText1>Open the www.latenitepros.comhome page, and then look for links to the information you want.
  <LI id=instructionsText2>Click the Refresh button, or try again later.
  
  <LI id=instructionsText3>Click Search to look for information on the Internet.
  <LI id=instructionsText4>You can also see a list of related sites.

HTTP 500 - Internal server error Internet Explorer
Joel: If you go to latenitepros.com/tester.htm I set up links to all three ordernum but still get an error above...
Doc

 

The error messages from those pages are from too much code being on one line. in vb you will either need to put in a linefeed or use a colon ':' to delimit the commands.


Joel Thoms
DiscountASP.NET
http://www.DiscountASP.NET

 

Joel:
From page latenitepros.com/tester.htm...

I used latenitepros.com/sql1.asp?id=3920

Can you indicate by example linefeed or a colon : to delimit the commands?

Thnak you...

Doc

 

Joel/Bruce:

I eliminated the errors butwith the URL Querystring
latenitepros.com/sql1.asp?id=3920
I get the entire info table from the database not my order number 3920 and it's row of data. I have tried some other combos but not the right one...

Doc

<title>Order Information</title>
<%
Dim cnnSimple ' ADO connection
Dim rstSimple ' ADO recordset
Set cnnSimple = Server.CreateObject("ADODB.Connection")
' DSN
cnnSimple.Open "DSN=xxxxxxxx_mysqlConn;"
Dim whereClause
If (Request.QueryString("id") <> "") Then
whereclause = "WHERE id = " &amp; Replace(Request.QueryString("id"),"'", "''")
End IF
Set rstSimple = cnnSimple.Execute("SELECT * FROM info" &amp; whereClause)
%>
<P>Customer Order Information</P>
<table border="9">
<%
Do While Not rstSimple.EOF
%>
<tr>
<td><%= rstSimple.Fields("ordernum").Value %></td>
<td><%= rstSimple.Fields("name").Value %></td>
<td><%= rstSimple.Fields("zip").Value %></td>
<td><%= rstSimple.Fields("price").Value %></td>
<td><%= rstSimple.Fields("ordrecvd").Value %></td>
<td><%= rstSimple.Fields("shipped").Value %></td>
</tr>
<%
rstSimple.MoveNext
Loop
%>
</table>
<%
' Close our recordset and connection and dispose of the objects rstSimple.Close Set rstSimple = Nothing cnnSimple.Close Set cnnSimple = Nothing
cnnSimple.close
%>

 

Add a space between SELECT * FROM info and the last quota ". Not sure if this will fix this, but it will prevent it from returning proper results.



Joel Thoms
DiscountASP.NET
http://www.DiscountASP.NET

 

Joel:

I gave your suggestion a try and now have this error. I have tried asking 3 young guys who program for a living but still no luck...

The Doc



Microsoft OLE DB Provider for ODBC Drivers error '80040e14'


[MySQL][ODBC 3.51 Driver][mysqld-4.1.18-standard]Unknown column 'id' in 'where clause'


/sql1.asp, line 15





<title>Order Information</title>
<%


Dim cnnSimple ' ADO connection
Dim rstSimple ' ADO recordset
Set cnnSimple = Server.CreateObject("ADODB.Connection")


' DSN
cnnSimple.Open "DSN=xxxxxxxxxxx_mysqlConn;"


Dim whereClause
If (Request.QueryString("id") <> "") Then
whereclause = "WHERE id = " &amp; Replace(Request.QueryString("id"),"'", "''")
End IF
Set rstSimple = cnnSimple.Execute("SELECT * FROM info " &amp; whereclause)
%>
<P>Customer Order Information</P>
<table border="9">
<%
Do While Not rstSimple.EOF
%>
<tr>
<td><%= rstSimple.Fields("ordernum").Value %></td>
<td><%= rstSimple.Fields("name").Value %></td>
<td><%= rstSimple.Fields("zip").Value %></td>
<td><%= rstSimple.Fields("price").Value %></td>
<td><%= rstSimple.Fields("ordrecvd").Value %></td>
<td><%= rstSimple.Fields("shipped").Value %></td>
</tr>
<%
rstSimple.MoveNext
Loop
%>
</table>
<%
' Close our recordset and connection and dispose of the objects rstSimple.Close Set rstSimple = Nothing cnnSimple.Close Set cnnSimple = Nothing


cnnSimple.close
%>

 

Joel said:

Add a space between SELECT * FROM info and the last quota ". Not sure if this will fix this, but it will prevent it from returning proper results.

This was the "final solution" since I tried using ordernum to replace "id" in the past it never worked because there should have been one more space after the last quota.

Thanks your help.

Your friend Doc...

 

It sounds like the column "id" doesn't exist in your database table.


Joel Thoms
DiscountASP.NET
http://www.DiscountASP.NET