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

Thanks a lot Bregent,

I did as you suggested and stopped receiving the error,

i did recieve a few others which i fixed until no more errors are shown,

i go directly to the results page,

however, i still cant see the search results. Im not sure if its the query not working or the way im trying to show it, i created a dynamic table and selected the recordset i wanted to show in it, but all i see is a dot "."

this is how i have it:

<table border="1" cellpadding="5" cellspacing="5" class="result">   <tr></tr>   <% While ((Repeat1__numRows <> 0) AND (NOT rsBundleR.EOF)) %>     <tr></tr>     <%   Repeat1__index=Repeat1__index+1   Repeat1__numRows=Repeat1__numRows-1   rsBundleR.MoveNext() Wend %> </table>

thanks for your help,

ill be trying a few things in the meantime and let you know if i succeed

I dont know if this showed in the last post, i cant see it in the post so here it goes again:

I made a dynamic table and selected the recordset here:

<table border="1" cellpadding="5" cellspacing="5" class="result">

  <tr></tr>

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

    <tr></tr>

    <%

  Repeat1__index=Repeat1__index+1

  Repeat1__numRows=Repeat1__numRows-1

  rsBundleR.MoveNext()

Wend

%>

</table>

I saw a tutorial where it showed that by doing this the table would show all the information of every row, but all i see is the dot

What do you recomend is the appropiate way to show the information?

I was now building a table with all the rows i need and find a way to call the specific field information i need from the recordset on each field, i dont know how to call it yet tho, just a few ideas i was going to try then id start googling a way to do it.

am i on the right path?

allright i think thats what i did here:

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

<table align="center" border="1">

          <tr>

            <td align="left" width="50%">id</td>

            <td align="left" width="50%"><%=(rsBundleR.Fields.Item("id").Value)%></td>

          </tr>

          <tr>

            <td align="left" width="50%">seguro_social</td>

            <td align="left" width="50%"><%=(rsBundleR.Fields.Item("seguro_social").Value)%></td>

          </tr>

          <tr>

            <td align="left" width="50%">numero_estudiante</td>

            <td align="left" width="50%"><%=(rsBundleR.Fields.Item("numero_estudiante").Value)%></td>

          </tr>

          <tr>

            <td align="left" width="50%">nombre</td>

            <td align="left" width="50%"><%=(rsBundleR.Fields.Item("nombre").Value)%></td>

          </tr>

          <tr>

            <td align="left" width="50%">apellido</td>

            <td align="left" width="50%"><%=(rsBundleR.Fields.Item("apellido").Value)%></td>

          </tr>

          <tr>

            <td align="left" width="50%">telefono</td>

            <td align="left" width="50%"><%=(rsBundleR.Fields.Item("telefono").Value)%></td>

          </tr>

          <tr>

            <td align="left" width="50%">celular</td>

            <td align="left" width="50%"><%=(rsBundleR.Fields.Item("celular").Value)%></td>

          </tr>

          <tr>

            <td align="left" width="50%">direccion_postal</td>

            <td align="left" width="50%"><%=(rsBundleR.Fields.Item("direccion_postal").Value)%></td>

          </tr>

          <tr>

            <td align="left" width="50%">direccion_postal_2</td>

            <td align="left" width="50%"><%=(rsBundleR.Fields.Item("direccion_postal_2").Value)%></td>

          </tr>

          <tr>

            <td align="left" width="50%">email_pupr</td>

            <td align="left" width="50%"><%=(rsBundleR.Fields.Item("email_pupr").Value)%></td>

          </tr>

          <tr>

            <td align="left" width="50%">ciudad</td>

            <td align="left" width="50%"><%=(rsBundleR.Fields.Item("ciudad").Value)%></td>

          </tr>

          <tr>

            <td align="left" width="50%">estado</td>

            <td align="left" width="50%"><%=(rsBundleR.Fields.Item("estado").Value)%></td>

          </tr>

          <tr>

            <td align="left" width="50%">zona_postal</td>

            <td align="left" width="50%"><%=(rsBundleR.Fields.Item("zona_postal").Value)%></td>

          </tr>

          <tr>

            <td align="left" width="50%">direccion_fisica</td>

            <td align="left" width="50%"><%=(rsBundleR.Fields.Item("direccion_fisica").Value)%></td>

          </tr>

          <tr>

            <td align="left" width="50%">direccion_fisica_2</td>

            <td align="left" width="50%"><%=(rsBundleR.Fields.Item("direccion_fisica_2").Value)%></td>

          </tr>

          <tr>

            <td align="left" width="50%">email_personal</td>

            <td align="left" width="50%"><%=(rsBundleR.Fields.Item("email_personal").Value)%></td>

          </tr>

          <tr>

            <td align="left" width="50%">ciudad_fisica</td>

            <td align="left" width="50%"><%=(rsBundleR.Fields.Item("ciudad_fisica").Value)%></td>

          </tr>

          <tr>

            <td align="left" width="50%">estado_fisica</td>

            <td align="left" width="50%"><%=(rsBundleR.Fields.Item("estado_fisica").Value)%></td>

          </tr>

          <tr>

            <td align="left" width="50%">zona_postal_fisica</td>

            <td align="left" width="50%"><%=(rsBundleR.Fields.Item("zona_postal_fisica").Value)%></td>

          </tr>

          <tr>

            <td align="left" width="50%">tipo_estudiante</td>

            <td align="left" width="50%"><%=(rsBundleR.Fields.Item("tipo_estudiante").Value)%></td>

          </tr>

          <tr>

            <td align="left" width="50%">termino_prestamo</td>

            <td align="left" width="50%"><%=(rsBundleR.Fields.Item("termino_prestamo").Value)%></td>

          </tr>

          <tr>

            <td align="left" width="50%">nombre_referencia_1</td>

            <td align="left" width="50%"><%=(rsBundleR.Fields.Item("nombre_referencia_1").Value)%></td>

          </tr>

          <tr>

            <td align="left" width="50%">apellido_referencia_1</td>

            <td align="left" width="50%"><%=(rsBundleR.Fields.Item("apellido_referencia_1").Value)%></td>

          </tr>

          <tr>

            <td align="left" width="50%">telefono_referencia_1</td>

            <td align="left" width="50%"><%=(rsBundleR.Fields.Item("telefono_referencia_1").Value)%></td>

          </tr>

          <tr>

            <td align="left" width="50%">direccion_postal_1_referencia_1</td>

            <td align="left" width="50%"><%=(rsBundleR.Fields.Item("direccion_postal_1_referencia_1").Value)%></td>

          </tr>

          <tr>

            <td align="left" width="50%">direccion_postal_2_referencia_1</td>

            <td align="left" width="50%"><%=(rsBundleR.Fields.Item("direccion_postal_2_referencia_1").Value)%></td>

          </tr>

          <tr>

            <td align="left" width="50%">ciudad_referencia_1</td>

            <td align="left" width="50%"><%=(rsBundleR.Fields.Item("ciudad_referencia_1").Value)%></td>

          </tr>

          <tr>

            <td align="left" width="50%">estado_referencia_1</td>

            <td align="left" width="50%"><%=(rsBundleR.Fields.Item("estado_referencia_1").Value)%></td>

          </tr>

          <tr>

            <td align="left" width="50%">zona_postal_referencia_1</td>

            <td align="left" width="50%"><%=(rsBundleR.Fields.Item("zona_postal_referencia_1").Value)%></td>

          </tr>

          <tr>

            <td align="left" width="50%">nombre_referencia_2</td>

            <td align="left" width="50%"><%=(rsBundleR.Fields.Item("nombre_referencia_2").Value)%></td>

          </tr>

          <tr>

            <td align="left" width="50%">apllido_refencia_2</td>

            <td align="left" width="50%"><%=(rsBundleR.Fields.Item("apllido_refencia_2").Value)%></td>

          </tr>

          <tr>

            <td align="left" width="50%">telefono_referencia_2</td>

            <td align="left" width="50%"><%=(rsBundleR.Fields.Item("telefono_referencia_2").Value)%></td>

          </tr>

          <tr>

            <td align="left" width="50%">direccion_postal_1_referencia_2</td>

            <td align="left" width="50%"><%=(rsBundleR.Fields.Item("direccion_postal_1_referencia_2").Value)%></td>

          </tr>

          <tr>

            <td align="left" width="50%">direccion_postal_2_referencia_2</td>

            <td align="left" width="50%"><%=(rsBundleR.Fields.Item("direccion_postal_2_referencia_2").Value)%></td>

          </tr>

          <tr>

            <td align="left" width="50%">ciudad_referencia_2</td>

            <td align="left" width="50%"><%=(rsBundleR.Fields.Item("ciudad_referencia_2").Value)%></td>

          </tr>

          <tr>

            <td align="left" width="50%">estado_referencia_2</td>

            <td align="left" width="50%"><%=(rsBundleR.Fields.Item("estado_referencia_2").Value)%></td>

          </tr>

          <tr>

            <td align="left" width="50%">zona_postal_referencia_2</td>

            <td align="left" width="50%"><%=(rsBundleR.Fields.Item("zona_postal_referencia_2").Value)%></td>

          </tr>

          <tr>

            <td align="left" width="50%">tipo_de_prestamo</td>

            <td align="left" width="50%"><%=(rsBundleR.Fields.Item("tipo_de_prestamo").Value)%></td>

          </tr>

          <tr>

            <td align="left" width="50%">cantidad_prestamo</td>

            <td align="left" width="50%"><%=(rsBundleR.Fields.Item("cantidad_prestamo").Value)%></td>

          </tr>

          <tr>

            <td align="left" width="50%">subsidiado</td>

            <td align="left" width="50%"><%=(rsBundleR.Fields.Item("subsidiado").Value)%></td>

          </tr>

          <tr>

            <td align="left" width="50%">no_subsidiado</td>

            <td align="left" width="50%"><%=(rsBundleR.Fields.Item("no_subsidiado").Value)%></td>

          </tr>

          <tr>

            <td align="left" width="50%">autorizo_pupr</td>

            <td align="left" width="50%"><%=(rsBundleR.Fields.Item("autorizo_pupr").Value)%></td>

          </tr>

          <tr>

            <td align="left" width="50%">fecha_solicitado</td>

            <td align="left" width="50%"><%=(rsBundleR.Fields.Item("fecha_solicitado").Value)%></td>

          </tr>

          <tr>

            <td align="left" width="50%">estatus</td>

            <td align="left" width="50%"><%=(rsBundleR.Fields.Item("estatus").Value)%></td>

          </tr>

          <tr>

            <td align="left" width="50%">revisado_por</td>

            <td align="left" width="50%"><%=(rsBundleR.Fields.Item("revisado_por").Value)%></td>

          </tr>

          <tr>

            <td align="left" width="50%">nota_personal</td>

            <td align="left" width="50%"><%=(rsBundleR.Fields.Item("nota_personal").Value)%></td>

          </tr>

        </table>

  <br /><br />

    <%

  Repeat1__index=Repeat1__index+1

  Repeat1__numRows=Repeat1__numRows-1

  rsBundleR.MoveNext()

Wend

%>

, no error is given BUT still, no information is showing.

However, i dont see the "." either :/

I made it create a table for each record,

is it the way i called the information?

or is the query not finding anything?

( there is information in the database )

"SELECT * FROM jcollazo.sol_prestamo_estudiantil WHERE id >= ? AND id <= ?  AND fecha_solicitado >= ? AND fecha_solicitado <= ?  AND seguro_social >= ? AND seguro_social <= ? ORDER BY id ASC" 

tried this instead, didnt work,

tried removing the 3rd filter completely, same empty table,

removed the date, same empty table,

ill try hardcoding the values and see where it takes me

is the new way im implementing the BETWEEN works? Or should i keep using between ?

my friend, for some reason i was missing this part for the repeat region:

<%

Dim Repeat1__numRows

Dim Repeat1__index

Repeat1__numRows = 100

Repeat1__index = 0

rsBundleR_numRows = rsBundleR_numRows + Repeat1__numRows

%>

it now Works!!!

However, how can i change the Repeat1__numRows = 100

to repeat untill the end of file ? i wrote EOF and it just disappears again

thanks a lot you were of great guidance

i dont understand why it is showing information, yet now in the server behaviors panels the recordset doesnt appear, what is it that makes it recognize its a recordset?

The department that requested the job actually need to print each selected record in a table all at once,

all i can think of is having no paging, making each row fill a table and give the table the dimensions of a 8x10 paper,

Im hoping if they print that page with all the tables one under the other each paper will only fit one table and the other will be printed in another paper, think that would work?

I  haven't reached that part yet, ha.

So by end of file i mean end of recordset rows, or last row ?

GOT IT!

sharing the code for all of you who need the none-paging recordset

<!-- RECORD COUNT CODE -->

<%

'  *** Recordset Stats, Move To Record, and Go To Record: declare stats variables

Dim rsBundleR_total

Dim rsBundleR_first

Dim rsBundleR_last

' set the record count

rsBundleR_total = rsBundleR.RecordCount

' set the number of rows displayed on this page

If (rsBundleR_numRows < 0) Then

  rsBundleR_numRows = rsBundleR_total

Elseif (rsBundleR_numRows = 0) Then

  rsBundleR_numRows = 1

End If

' set the first and last displayed record

rsBundleR_first = 1

rsBundleR_last  = rsBundleR_first + rsBundleR_numRows - 1

' if we have the correct record count, check the other stats

If (rsBundleR_total <> -1) Then

  If (rsBundleR_first > rsBundleR_total) Then

    rsBundleR_first = rsBundleR_total

  End If

  If (rsBundleR_last > rsBundleR_total) Then

    rsBundleR_last = rsBundleR_total

  End If

  If (rsBundleR_numRows > rsBundleR_total) Then

    rsBundleR_numRows = rsBundleR_total

  End If

End If

%>

<%

' *** Recordset Stats: if we don't know the record count, manually count them

If (rsBundleR_total = -1) Then

  ' count the total records by iterating through the recordset

  rsBundleR_total=0

  While (Not rsBundleR.EOF)

    rsBundleR_total = rsBundleR_total + 1

    rsBundleR.MoveNext

  Wend

  ' reset the cursor to the beginning

  If (rsBundleR.CursorType > 0) Then

    rsBundleR.MoveFirst

  Else

    rsBundleR.Requery

  End If

  ' set the number of rows displayed on this page

  If (rsBundleR_numRows < 0 Or rsBundleR_numRows > rsBundleR_total) Then

    rsBundleR_numRows = rsBundleR_total

  End If

  ' set the first and last displayed record

  rsBundleR_first = 1

  rsBundleR_last = rsBundleR_first + rsBundleR_numRows - 1

  If (rsBundleR_first > rsBundleR_total) Then

    rsBundleR_first = rsBundleR_total

  End If

  If (rsBundleR_last > rsBundleR_total) Then

    rsBundleR_last = rsBundleR_total

  End If

End If

%>

<!-- REPEAT CODE 1 -->

<%

Dim Repeat1__numRows

Dim Repeat1__index

Repeat1__numRows = rsBundleR_total

Repeat1__index = 0

rsBundleR_numRows = rsBundleR_numRows + Repeat1__numRows

%>

hmm, i like your solution better !

Im having trouble searching with the date only, for some reason its not working, could it be the datatype?

I dont understand how they are set in dreamweaver:

rsBundleR_cmd.Prepared = true

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

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

rsBundleR_cmd.Parameters.Append rsBundleR_cmd.CreateParameter("param3", 200, 1, 255, rsBundleR__p_dateFrom) ' adVarChar

rsBundleR_cmd.Parameters.Append rsBundleR_cmd.CreateParameter("param4", 200, 1, 255, rsBundleR__p_dateTo) ' adVarChar

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

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

what do each of the numbers in the parenthesis after "paramX" means ? How can i change datatype there? Ive looked for this in the web but cant seem to find the "number" for the datatypes, just names.

My friend, i found the problem,

the form who's information is stored in the database i am targetting with MY form to make the search,

in the date_created field ( i am tring to search from date x to date x in mine ) its an invisible filed that creates it using: <% =date %> </br> <% =time %> .

Hence it never showed anything searching with a date because thats not the onlything that is stored in that field.

So, how could i make a search from date x to date x from that list of saved dates with the linebreak and the time?

I tried searching putting a time in the field aswell and it never worked, call i can think of is using LIKE in the query instead of BETWEEN

( however, i actually need some kind of BETWEEN date x and date x )

How would i be able to do this? I have this so far and im getting errors:

"SELECT * FROM database WHERE fecha_solicitado LIKE ALL ('%" & "?" & "%','%" & "?" & "%' ) ORDER BY id ASC"

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

do i need to add the linebreak in the query ?

thanks again!

The form that stores the values in the database has an invisible field that is also saved into the database with each form submition.

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

so whatever is stored in the "time of submission" column, is what <% =date %> </br> <% =time %> generates.

I can see that column's info when i make the search by the id's only

some examples of what is generated by the code in each submission:

4/30/2012

12:05:01 PM

4/30/2012

12:22:03 PM

5/1/2012

4:04:38 PM

I have no idea what type that would be, i cant seem to find any way to search for it so that it recognizes it

i tried the following formats: adDate 7, adDBDate 133, adDBTimeStamp 135

the one with best results was the 135 which gave me the following error:

[Microsoft][ODBC SQL Server Driver][SQL Server]Syntax error converting datetime from character string.

so is tarted looking for string types that could work, and thought the most fitting was adVarChar

AND IT GIVES NO ERRORS BUT IT WONT SHOW ANYTHING IN THE RESULTS.

i tried searching like:

1/1/2012   to   4/1/2012

1/1/2012  00:00:00 AM   to   4/1/2012  00:00:00 AM

1/1/2012  </br> 00:00:00 AM   to   4/1/2012 </br>  00:00:00 AM

and still no results, no errors either, just empty table

so then i tried adLongVarChar

which then gives me this error:

[Microsoft][ODBC SQL Server Driver][SQL Server]The text, ntext, and image data types cannot be compared or sorted, except when using IS NULL or LIKE operator.

i didnt make the form, its been like that for years, im just making the page where our employees can search for the specific records they need.

i could remove the linebreak and make it store the info some different way, but i still have to search in the previously submitted records with the linebreak

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