-----Original Message-----

From: Dan_Singleton@a... [mailto:Dan_Singleton@a...]

Sent: 14 June 2000 19:04

To: philp@w...

Subject: Question from Beginning Active Server Pages 3.0 





Hi Philip,



I have a question that I could not find in the subject book that deals 

with

creating an sql  select statement that will select only items in an access

database as follows:



My current sql statement produces all of the words in a colum that

"CONTAIN"  the letter I type in an input box.

What I want is just the words that "BEGIN" with the letter I type.



In access the sql would look like this to pick all of the names beginning

with the letter G:



SELECT EMP.Name

FROM EMP

WHERE (((EMP.Name) Like "g*"));



Here is what I have in ASP

ssearch=  session.Contents ("varemployeeid")

  ssql="select * from emp where name like '%" &  ssearch & "%'





Can you please tell me how to create the access equivalent in ASP.

I am going to buy the 2nd level boox from WROX today.



Thanks

Dan Singleton

Dan,

I have some great code I've been using for an example that filters the SQL

from some drop down boxes to create the recordset you want. This passes the

recordset to a Visual Interdev record design time control, but you should be

able to look at the code and see how this works. If you happen to be using

Interdev, I'll send you the whole thing -otherwise, hope this helps:



'

' btnShowReport_onclick will build and execute a SQL statement based

' off the user's input. The ProductList recordset will be opened once

' the SQL text is set, resulting in the ReportGrid to be displayed.

'

Sub btnShowReport_onclick()

    dim strSQL              ' the SQL query

    dim multipleArgs        ' flag used if multiple arguments needed for 

                            ' the query

    

    multipleArgs = false    ' initialize multiple arguments to false

     

    ' base query string

    strSQL = "SELECT ProductID, ProductName, SupplierID," & _

             " CategoryID, UnitPrice FROM Products"

        

    ' check the category list box value

    if (CatID.getValue() <> 0) then        ' CategoryID filter specified

        strSQL = strSQL & " WHERE CategoryID = " & CatID.getValue() 

        multipleArgs = true

    end if

    

    ' check the supplier list box value

    if (SupplierID.getValue() <> 0) then    ' SupplierID filter specified

        if multipleArgs = true then

            strSQL = strSQL & " AND "

        else

            strSQL = strSQL & " WHERE "

            multipleArgs = true

        end if 

                       

        strSQL = strSQL & " SupplierID = " & SupplierID.getValue()

    end if



    ' check the product description and list box value

    if (txtboxProdDesc.value <> "") then    ' ProdDescID filter specified

        if multipleArgs = true then

          strSQL = strSQL & " AND "

        else

          strSQL = strSQL & " WHERE "

          multipleArgs = true

        end if 

        

        strSQL = strSQL & " ProductName "

        

        select case ProdDescID.getValue()

            case 1  strSQL = strSQL & " LIKE "

                    strSQL = strSQL & "'" & txtboxProdDesc.value & "'"

                    

            case 2  strSQL = strSQL & " LIKE "

                    strSQL = strSQL & "'" & txtboxProdDesc.value & "%'" 

            

            case 3  strSQL = strSQL & " LIKE "

                    strSQL = strSQL & "'%" & txtboxProdDesc.value & "%'" 

       end select

    end if

    

    ' check the unit price and list box value

    if (txtboxUnitPrice.value <> "") then   ' UnitPrice filter specified  

        if multipleArgs = true then

          strSQL = strSQL & " AND "

        else

          strSQL = strSQL & " WHERE "

          multipleArgs = true

        end if 

        

        strSQL = strSQL & " UnitPrice "

        

        select case UnitPriceID.getValue()

            case 1  strSQL = strSQL & " = "    ' equal

                    

            case 2  strSQL = strSQL & " != "   ' not equal

                    

            case 3  strSQL = strSQL & " > "    ' greater than

                    

            case 4  strSQL = strSQL & " < "    ' less than

                    

            case 5  strSQL = strSQL & " >= "   ' greater than or equal to

                    

            case 6  strSQL = strSQL & " <= "   ' less than or equal to

        end select    

        

        strSQL = strSQL & txtboxUnitPrice.value

    end if



    ' show the complete SQL query string

    Response.Write "<font face=Tahoma size=1 color=red> Debug: " & _

                   strSQL & "</font>"

    

    ' execute the SQL query

    ProductList.setSQLText(strSQL)

    

    ProductList.open      ' open Recordset to load and display the Grid

End Sub



-----Original Message-----

From: Phil Perks [mailto:philp@w...]

Sent: Wednesday, June 14, 2000 4:15 PM

To: ASP Databases

Subject: [asp_databases] creating an sql select statement 



-----Original Message-----

From: Dan_Singleton@a... [mailto:Dan_Singleton@a...]

Sent: 14 June 2000 19:04

To: philp@w...

Subject: Question from Beginning Active Server Pages 3.0 





Hi Philip,



I have a question that I could not find in the subject book that deals 

with

creating an sql  select statement that will select only items in an access

database as follows:



My current sql statement produces all of the words in a colum that

"CONTAIN"  the letter I type in an input box.

What I want is just the words that "BEGIN" with the letter I type.



In access the sql would look like this to pick all of the names beginning

with the letter G:



SELECT EMP.Name

FROM EMP

WHERE (((EMP.Name) Like "g*"));



Here is what I have in ASP

ssearch=  session.Contents ("varemployeeid")

  ssql="select * from emp where name like '%" &  ssearch & "%'





Can you please tell me how to create the access equivalent in ASP.

I am going to buy the 2nd level boox from WROX today.



Thanks

Dan Singleton