#1
  1. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Feb 2005
    Posts
    53
    Rep Power
    10

    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.
  2. #2
  3. Contributing User
    Devshed Novice (500 - 999 posts)

    Join Date
    Feb 2005
    Posts
    724
    Rep Power
    291
    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.
  4. #3
  5. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,027
    Rep Power
    4210
    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
  6. #4
  7. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Feb 2005
    Posts
    53
    Rep Power
    10
    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?
  8. #5
  9. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,027
    Rep Power
    4210
    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'
    r937.com | rudy.ca
    please visit Simply SQL and buy my book
  10. #6
  11. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Feb 2005
    Posts
    53
    Rep Power
    10
    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 =?
  12. #7
  13. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,027
    Rep Power
    4210
    Originally Posted by grissom
    I am looking for equality, it should match the number exactly, maybe i should use =?
    yep, i think so
    r937.com | rudy.ca
    please visit Simply SQL and buy my book

IMN logo majestic logo threadwatch logo seochat tools logo