Simple sql query in ASP

R

Rams_

Board Regular
Joined
Oct 31, 2007
Messages
63
I cannot seem to get a simple query in ASP to work... I get a numerical value from a form, but when I try to process it I get a data mismatch error.

My code is the following with error line in bold

Rich (BB code): 
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1" />
<title>Welcome to the Kennels Database System - Animals Section</title>
<link href="external.css" rel="stylesheet" type="text/css" />
<style type="text/css">
<!--
.style1 {
	font-size: 14px;
	color: #000000;
}
-->
</style>
</head>

<body>
<center>

<!..					EXTRACTING RECORDSET AND CREATING QUERY	..>

<%
set conn=Server.CreateObject("ADODB.Connection")
conn.Provider="Microsoft.Jet.OLEDB.4.0"
conn.Open(Server.Mappath("kennels.mdb"))

Session("customerid")=request("customerid")
intcustomersearch=session("customerid")

set rs = Server.CreateObject("ADODB.recordset")

rs.Open "SELECT * FROM tblCustomers where customerid='intcustomersearch', conn 

response.write intcustomersearch

%>

<!..					EXTRACTING VALUES FROM THE FIELDS	..>

<%
CustomerID=rs("CustomerID")
Title=rs("Title")
CustomerSurname=rs("CustomerSurname")
CustomerForename=rs("CustomerForename")
Address=rs("Address")
Postcode=rs("Postcode")
TelNumber=rs("TelNumber")
MobNumber=rs("MobNumber")
%>

<!..					TABLE OF RESULTS	..>

<% If rs.EOF <> True AND rs.BOF <> True Then
	While NOT rs.EOF

	
		%>
		<table border="1" cellspacing=10>
		<tr>
		<th>Customer ID</th><td><%=rs("CustomerID")%></td></tr>
		<tr><th>Title</th><td><%=rs("Title")%></td></tr>
		<tr><th>Customer Surname</th><td><%=rs("CustomerSurname")%></td></tr>
		<tr><th>Customer Forename</th><td><%=rs("CustomerForename")%></td></tr>
		<tr><th>Address</th><td><%=rs("Address")%></td></tr>
		<tr><th>Postcode</th><td><%=rs("Postcode")%></td></tr>
		<tr><th>Tel Number</th><td><%=rs("TelNumber")%></td></tr>
		<tr><th>Mob Number</th><td><%=rs("MobNumber")%></td></tr>
		</table><br>
		<%
		rs.MoveNext
	Wend
end if
 
rs.close
conn.close
%>

</body>

</html>

I know that intcustomersearch is coming to the right number, because i have tested it with response.write

Any ideas?
 

Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.
Sort by date Sort by votes
In the above, you don't appear to be correctly closing the SQl string ie:

"SELECT * FROM tblCustomers where customerid='intcustomersearch'

should be:

Code: 
"SELECT * FROM tblCustomers where customerid='intcustomersearch'"
Closing double-quote difference
 
Upvote 0
Silly me, you need to include the variable value, not the string value "intcustomersearch":

Code: 
"SELECT * FROM tblCustomers where customerid='" & intcustomersearch & "'"
 
Upvote 0
Does this field hold numbers (ie numeric data type) or text? Possibly, if it is numbers, you may have to do without the single quotes around the number.
 
Upvote 0
Excellent, that worked!

Coming from a VBA background, I find all this work with single and double quotes very frustrating. So easy to make a mistake.

Thanks anyway though!
 
Upvote 0
Okay, got another problem now :(

Firstly I have a relationship where 1 customer owns many animals.

I want to have a page in ASP where I list the customers and have a link for each one where I can view their animals.

What's the best way to achieve this?

I have tried to add a command button onto the end of each line linking to their customer ID, but it didn't work.


Code: 
<title>Welcome to the Kennels Database System - Animals Section</title>
<link href="external.css" rel="stylesheet" type="text/css" />
<style type="text/css">
<!--
.style1 {
font-size: 14px;
color: #000000;
}
-->
</style>
</head>

<body>
<center>

<!.. EXTRACTING RECORDSET AND CREATING QUERY ..>

<%
set conn=Server.CreateObject("ADODB.Connection")
conn.Provider="Microsoft.Jet.OLEDB.4.0"
conn.Open(Server.Mappath("kennels.mdb"))

set rs = Server.CreateObject("ADODB.recordset")

rs.Open "SELECT * FROM tblCustomers", conn

%>



<!.. TABLE OF RESULTS ..>

<table border="1" cellspacing=10>
<tr>
<th>Customer ID</th>
<th>Title</th>
<th>Customer Surname</th>
<th>Customer Forename</th>
<th>Customer Address</th>
<th>Customer Postcode</th>
<th>Customer Tel Number</th>
<th>Customer Mob Number</th>

<%do until rs.EOF%>
<tr>
<%for each x in rs.Fields%>
<td><%Response.Write(x.value)%></td>
<%next
CustID=rs("CustomerID")%>
<td><input type="button" name="<%custID%>" value="View Animals" onClick="customersanimals.asp"></td>
<%rs.movenext %>
</tr>
<%loop

rs.close
conn.close
%>
</table>
<br>
<a href="customers.asp">Back to Customer main page</a>

</body>

</html>
 
Upvote 0
You must log in or register to reply here.

Similar threads

V
  • Question Question
Issue with scraping with VBA
Replies
1
Views
706
John_w
J
C
  • Solved
Excel 2016 32bit VBA Pulling info from Website to Sheet
Replies
2
Views
2K
CrashOD
C
L
  • Question Question
How to declare contentWindow.document for an iFrame inside another iFrame ?
Replies
1
Views
4K
LouieH
L
Z
  • Question Question
How do i log into this web site and pull data?
Replies
0
Views
2K
Zino_596
Z
E
  • Question Question
VBA HTML Mailing
Replies
6
Views
5K
excelman888
E

Forum statistics

Threads
1,225,363
Messages
6,184,519
Members
453,238
Latest member
visuvisu

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top