-
1. Re: Declare @p1 variable error when creating multiple search form to show database info
bregent Jul 9, 2012 1:59 PM (in response to krizcortes)1 person found this helpfulAre 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.
-
2. Re: Declare @p1 variable error when creating multiple search form to show database info
krizcortes Jul 10, 2012 12:37 PM (in response to bregent)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
-
3. Re: Declare @p1 variable error when creating multiple search form to show database info
bregent Jul 10, 2012 1:11 PM (in response 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?
-
4. Re: Declare @p1 variable error when creating multiple search form to show database info
krizcortes Jul 10, 2012 1:18 PM (in response to bregent)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?
-
5. Re: Declare @p1 variable error when creating multiple search form to show database info
bregent Jul 10, 2012 1:47 PM (in response 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>
-
6. Re: Declare @p1 variable error when creating multiple search form to show database info
krizcortes Jul 10, 2012 1:56 PM (in response to bregent)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 )
-
7. Re: Declare @p1 variable error when creating multiple search form to show database info
bregent Jul 10, 2012 3:22 PM (in response 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.
-
8. Re: Declare @p1 variable error when creating multiple search form to show database info
krizcortes Jul 11, 2012 7:47 AM (in response to bregent)"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 ?
-
9. Re: Declare @p1 variable error when creating multiple search form to show database info
krizcortes Jul 11, 2012 9:02 AM (in response to bregent)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
-
10. Re: Declare @p1 variable error when creating multiple search form to show database info
krizcortes Jul 11, 2012 10:21 AM (in response to bregent)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?
-
11. Re: Declare @p1 variable error when creating multiple search form to show database info
bregent Jul 11, 2012 10:55 AM (in response 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.
-
12. Re: Declare @p1 variable error when creating multiple search form to show database info
krizcortes Jul 11, 2012 11:09 AM (in response to bregent)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 ?
-
13. Re: Declare @p1 variable error when creating multiple search form to show database info
krizcortes Jul 11, 2012 11:23 AM (in response to krizcortes)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
%>
-
14. Re: Declare @p1 variable error when creating multiple search form to show database info
bregent Jul 11, 2012 11:26 AM (in response 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) %>
-
15. Re: Declare @p1 variable error when creating multiple search form to show database info
krizcortes Jul 11, 2012 11:42 AM (in response to bregent)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.
-
16. Re: Declare @p1 variable error when creating multiple search form to show database info
bregent Jul 11, 2012 1:38 PM (in response to krizcortes)1 person found this helpful>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.
-
17. Re: Declare @p1 variable error when creating multiple search form to show database info
krizcortes Jul 19, 2012 12:53 PM (in response to bregent)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!
-
18. Re: Declare @p1 variable error when creating multiple search form to show database info
bregent Jul 20, 2012 9:16 AM (in response 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.
-
19. Re: Declare @p1 variable error when creating multiple search form to show database info
krizcortes Jul 20, 2012 10:46 AM (in response to bregent)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?
-
20. Re: Declare @p1 variable error when creating multiple search form to show database info
bregent Jul 20, 2012 11:25 AM (in response 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?