Skip navigation
Currently Being Moderated

Declare @p1 variable error when creating multiple search form to show database info

Jul 9, 2012 11:22 AM

Tags: #problem #help #data #search #database #table #multiple #query #filter #variable #asp #recordset #selected #declare #odbc #vbscript #@

Hi, all help is incredibly appreciated,

i have been trying to make a multiple filtered search form for a database in asp / vbscript. I want to be able to select from THIS database table, all info on the rows which id is BETWEEN x and x AND date is BETWEEN x and x AND securitynumber is BETWEEN x and x

 

what would be the best way to do this?

 

this is what ive got so far, which gives me the error:

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

[Microsoft][ODBC SQL Server Driver][SQL Server]Must declare the variable '@P1'.

 

<%

Dim rsBundleR__p_selectedDB

rsBundleR__p_selectedDB = request.form("selectedDataBase")

If (request.form("selectedDataBase") <> "") Then

  rsBundleR__p_selectedDB = request.form("selectedDataBase")

End If

%>

<%

Dim rsBundleR__p_idFrom

rsBundleR__p_idFrom = request.form("bundleIdFrom")

If (request.form("bundleIdFrom")  <> "") Then

  rsBundleR__p_idFrom = request.form("bundleIdFrom")

End If

%>

<%

Dim rsBundleR__p_idTo

rsBundleR__p_idTo = request.form("bundleIdTo")

If (request.form("bundleIdTo") <> "") Then

  rsBundleR__p_idTo = request.form("bundleIdTo")

End If

%>

<%

Dim rsBundleR__p_dateFrom

rsBundleR__p_dateFrom = request.form("fromDate")

If (request.form("fromDate") <> "") Then

  rsBundleR__p_dateFrom = request.form("fromDate")

End If

%>

<%

Dim rsBundleR__p_dateTo

rsBundleR__p_dateTo = request.form("toDate")

If (request.form("toDate") <> "") Then

  rsBundleR__p_dateTo = request.form("toDate")

End If

%>

<%

Dim rsBundleR__p_ssFrom

rsBundleR__p_ssFrom = request.form("fromSS")

If (request.form("fromSS") <> "") Then

  rsBundleR__p_ssFrom = request.form("fromSS")

End If

%>

<%

Dim rsBundleR__p_ssTo

rsBundleR__p_ssTo = request.form("toSS")

If (request.form("toSS") <> "") Then

  rsBundleR__p_ssTo = request.form("toSS")

End If

%>

 

 

 

<%

Dim rsBundleR

Dim rsBundleR_cmd

Dim rsBundleR_numRows

 

Set rsBundleR_cmd = Server.CreateObject ("ADODB.Command")

rsBundleR_cmd.ActiveConnection = MM_PHPSQL_STRING

rsBundleR_cmd.CommandText = "SELECT * FROM ? WHERE id BETWEEN ? AND ?  AND fecha_solicitado BETWEEN ? AND ?  AND seguro_social BETWEEN ? AND ? ORDER BY id ASC"

rsBundleR_cmd.Prepared = true

rsBundleR_cmd.Parameters.Append rsBundleR_cmd.CreateParameter("param1", 200, 1, 255, rsBundleR__p_selectedDB) ' adVarChar

rsBundleR_cmd.Parameters.Append rsBundleR_cmd.CreateParameter("param2", 5, 1, -1, rsBundleR__p_idFrom) ' adDouble

rsBundleR_cmd.Parameters.Append rsBundleR_cmd.CreateParameter("param3", 5, 1, -1, rsBundleR__p_idTo) ' adDouble

rsBundleR_cmd.Parameters.Append rsBundleR_cmd.CreateParameter("param4", 135, 1, -1, rsBundleR__p_dateFrom) ' adDBTimeStamp

rsBundleR_cmd.Parameters.Append rsBundleR_cmd.CreateParameter("param5", 135, 1, -1, rsBundleR__p_dateTo) ' adDBTimeStamp

rsBundleR_cmd.Parameters.Append rsBundleR_cmd.CreateParameter("param6", 200, 1, 255, rsBundleR__p_ssFrom) ' adVarChar

rsBundleR_cmd.Parameters.Append rsBundleR_cmd.CreateParameter("param7", 200, 1, 255, rsBundleR__p_ssTo) ' adVarChar

 

Set rsBundleR = rsBundleR_cmd.Execute

rsBundleR_numRows = 0

%>

 

 

someone, please help, this is very important!

 
Replies
  • Currently Being Moderated
    Jul 9, 2012 1:59 PM   in reply to krizcortes

    Are you sure that the command object supports the table name as a SQL parameter? Try hardcoding the table name in the SQL and see if you get the same error.


     
    |
    Mark as:
  • Currently Being Moderated
    Jul 10, 2012 1:11 PM   in reply to krizcortes

    >however, i still cant see the search results.

     

    You are looping through the recordset, but I don't see anywhere in your code where you are outputting values from it. Is there more code you aren't showing?

     
    |
    Mark as:
  • Currently Being Moderated
    Jul 10, 2012 1:47 PM   in reply to krizcortes

    You are not outputting any data in your loop. For each iteration, you are creating a table row. You would need to put the recordset data within that row by referencing the recordset and column within the <tr></tr>

     
    |
    Mark as:
  • Currently Being Moderated
    Jul 10, 2012 3:22 PM   in reply to krizcortes

    That looks better. I'd suspect that your recordset is empty. I'd be suspicious of using the BETWEEN operator on character data for the SSN. Try removing that and see if you get any records. If that doesn't work, try hardcoding values into the other placeholders. Remember to comment out the corresponding append parameter statement so you don't get out of synch.

     
    |
    Mark as:
  • Currently Being Moderated
    Jul 11, 2012 10:55 AM   in reply to krizcortes

    >to repeat untill the end of file ?

     

    Not sure what you mean by 'end of file'. You don't want any paging?

     

    >what is it that makes it recognize its a recordset?

     

    Once you've modified code by hand, DW will no longer recognize it.

     
    |
    Mark as:
  • Currently Being Moderated
    Jul 11, 2012 11:26 AM   in reply to krizcortes

    If you don't want paging, then try changing this:

     

      <% While ((Repeat1__numRows <> 0) AND (NOT rsBundleR.EOF)) %>

     

    to this:

     

      <% While (NOT rsBundleR.EOF) %>

     
    |
    Mark as:
  • Currently Being Moderated
    Jul 11, 2012 1:38 PM   in reply to krizcortes

    >m having trouble searching with the date only,

    >for some reason its not working, could it be the datatype?

     

    Certainly. Use the type and format that meets your specific needs.

     

    http://www.w3schools.com/ADO/met_comm_createparameter.asp

     
    |
    Mark as:
  • Currently Being Moderated
    Jul 20, 2012 9:16 AM   in reply to krizcortes

    >its an invisible filed that creates it using: <% =date %> </br> <% =time %> .

     

    I have no idea what you are saying here. Can you try to explain it better?

     

    >What type would it be? i thought the most correct one was: adLongVarChar (201)

     

    If you are storing values that include date and time, then use either the DATETIME or TIMESTAMP datatype. Never store date/time as character types.

     

    >do i need to add the linebreak in the query ?

     

    No. you should not be storing linebreaks in the datetime column.

     
    |
    Mark as:
  • Currently Being Moderated
    Jul 20, 2012 11:25 AM   in reply to krizcortes

    >This field's value is generated with the asp code: <% =date %> </br> <% =time %>

     

    You need to first combine the date and time into a single field, and then store that in a DATETIME field in the database.

     

    >I have no idea what type that would be

     

    Forget about trying to search for the data that is stored the way you currently have it. Whatever you do will be a kludge and will forever be a nightmare to work with. Rule #1 in database design is to use the right datatype for the job. Date/time data needs to be stored in DATETIME columns, PERIOD!  Fix the datatype, fix the data and change the script to combine the date and time data, and your problems will disappear.

     

    To fix the current data you will need to add a new DATETIME column, and then run an update statement that will convert the current garbage data into real data you can work with.

     

    >i couldnt find the DATETIME type you mention, maybe thats the missing link here, whats the value for that type?

     

    http://msdn.microsoft.com/en-us/library/ms187819

     
    |
    Mark as:

More Like This

  • Retrieving data ...

Bookmarked By (0)

Answers + Points = Status

  • 10 points awarded for Correct Answers
  • 5 points awarded for Helpful Answers
  • 10,000+ points
  • 1,001-10,000 points
  • 501-1,000 points
  • 5-500 points