What is wrong with my sql statment

Dev Shed Forums Sponsor:

May 17th, 2012, 06:34 AM

grissom

Contributing User

Join Date: Feb 2005

Posts: 53

Time spent in forums: 19 h 35 m 11 sec
Reputation Power: 9

What is wrong with my sql statment

I am trying to create a search using sql statement and I want the result to show up in the same page just below the searchform. But I keep getting the following error everytime I try to search for a number.

Microsoft JET Database Engine error '80040e10'
No value given for one or more required parameters.
/search-form2.asp, line 41

Here is the link to the page. http://www.studio36.no/search-form2.asp

If you try to search for the following number. (all searches will be for numbers in this format) 979 498 071 -you will see the error I am getting.

I will also provide the code for the page.

Code:

<%@LANGUAGE="VBSCRIPT" CODEPAGE="1252"%>
<!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>Untitled Document</title>


</head>

<body>

<%

Dim strURL, cnnSearch, rstSearch, strDBPath, strSQL, strSearch

strURL = Request.ServerVariables("URL")

strSearch = Request.QueryString("search")


%>
<p>Søk etter organisasjonsnummer</p>
<form action="<%= strURL %>" method="get">
<input name="search" value="<%= strSearch %>" />
<input type="submit" value="Hent" />
</form>

<%
If strSearch <> "" Then

	strDBPath = Server.MapPath("kunder.mdb")
	Set cnnSearch = Server.CreateObject("ADODB.Connection")

	'cnnSearch.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\inetpub\wwwroot\nina-saether\www\Databaser\kunder.mdb;Persist Security Info=False"
		cnnSearch.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:\home\studio36\www\Databaser\kunder.mdb;Persist Security Info=False"


	strSQL = "SELECT * FROM kunder WHERE Org-nr LIKE '" & strSearch & "'"

	Set rstSearch = cnnSearch.Execute(strSQL)

	%>
	<table border="1">
	<tr>
	<th>Kundeinformasjon</th>

	</tr>
	<%
	Do While Not rstSearch.EOF
		%>
		<tr>
		<td><%= rstSearch.Fields("Org-nr").Value %> <br />
			 <%= rstSearch.Fields("Navn").Value %> <br />
			 <%= rstSearch.Fields("Tlf").Value %> <br />
             <%= rstSearch.Fields("Mobil").Value %> <br />
             <%= rstSearch.Fields("E-post").Value %> <br /> 
        </td>
		</tr>
		<%
		rstSearch.MoveNext
	Loop
	%>
	</table>
	<%

	rstSearch.Close
	Set rstSearch = Nothing
	cnnSearch.Close
	Set cnnSearch = Nothing
End If

%>

</body>
</html>

Any help on resolving this would be greatly appreciated.

May 17th, 2012, 10:44 AM

Vomster

Contributing User

Join Date: Feb 2005

Posts: 724

Time spent in forums: 1 Week 3 Days 2 h 26 m 31 sec
Reputation Power: 290

Have you verified that strSearch is being populated when you do the Request.QueryString?

Have you verified that the query works if you hard code something into the LIKE ''?

EDIT

Is kunder the name of the table? I ask because that is the name of the database.

May 17th, 2012, 02:22 PM

r937

SQL Consultant

Join Date: Feb 2003

Location: Toronto Canada

Posts: 26,376

Time spent in forums: 3 Months 1 Week 2 Days 7 h 35 m 43 sec
Reputation Power: 4140

if you had tested the query by itself, i think you would have discovered that it will fail because it doesn't recognize the "Org" and "nr" columns that you appear to be trying to subtract there

... which is a roundabout way of saying that [Org-nr] needs brackets because the column name contains a special character

__________________
r937.com | rudy.ca
please visit Simply SQL and buy my book

May 17th, 2012, 03:20 PM

grissom

Contributing User

Join Date: Feb 2005

Posts: 53

Time spent in forums: 19 h 35 m 11 sec
Reputation Power: 9

Sorry, I somtimes post in multiple forums hoping to widen the search for an answer.

I have tried to hardcode a number into the sql statement ("SELECT * FROM tblkunder WHERE OrgNr LIKE 990 362 084")which gives me the following error: Syntax error (missing operator) in query expression 'OrgNr LIKE 990 362 084'.

I can see in the URI that ?search=990+362+084 so I guess it is populating.

It's been several years since I worked with asp and sql so I am very, very rusty and I really need som help.

I thought that Org-nr could give me problems so I renamed it to OrgNr. I also renamed the database to dbKunder and the table to tblKunder but no luck.

Any ideas on where I go wrong?

May 17th, 2012, 08:34 PM

r937

SQL Consultant

Join Date: Feb 2003

Location: Toronto Canada

Posts: 26,376

Time spent in forums: 3 Months 1 Week 2 Days 7 h 35 m 43 sec
Reputation Power: 4140

do you want LIKE or do you want equality?

because LIKE without wildcard characters is the same as an equality test

Code:

SELECT something
     , anything
     , just_not_the_dreaded_evil_select_star
  FROM tblkunder 
 WHERE OrgNr = '990 362 084'

May 18th, 2012, 04:53 AM

grissom

Contributing User

Join Date: Feb 2005

Posts: 53

Time spent in forums: 19 h 35 m 11 sec
Reputation Power: 9

Thanks for your reply.

I solved the problem late last night. When removing the space between the numbers in the database it now works.

I am looking for equality, it should match the number exactly, maybe i should use =?

May 18th, 2012, 04:59 AM

r937

SQL Consultant

Join Date: Feb 2003

Location: Toronto Canada

Posts: 26,376

Time spent in forums: 3 Months 1 Week 2 Days 7 h 35 m 43 sec
Reputation Power: 4140

Quote:

Originally Posted by grissom

I am looking for equality, it should match the number exactly, maybe i should use =?

yep, i think so

What is wrong with my sql statment

Developer Shed Advertisers and Affiliates