20 Replies Latest reply: Jul 20, 2012 11:25 AM by bregent RSS

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

    krizcortes

      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!

        • 1. Re: Declare @p1 variable error when creating multiple search form to show database info
          bregent MVP

          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.


          • 2. Re: Declare @p1 variable error when creating multiple search form to show database info
            krizcortes Community Member

            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 MVP

              >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 Community Member

                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 MVP

                  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 Community Member

                    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 MVP

                      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 Community Member

                        "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 Community Member

                          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 Community Member

                            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 MVP

                              >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 Community Member

                                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 Community Member

                                  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 MVP

                                    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 Community Member

                                      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 MVP

                                        >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

                                        • 17. Re: Declare @p1 variable error when creating multiple search form to show database info
                                          krizcortes Community Member

                                          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 MVP

                                            >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 Community Member

                                              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 MVP

                                                >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