Having trouble with SQL statement with ASP 2

[jazzz]

objCmd.CommandText = strSQL & " WHERE ItemID = " & rsItems("ItemID") & ";"

That code works just fine. Now I want to add another criteria to the WHERE CLAUSE and it is itemdelete = False then I would like to sort the results ascending.

Before I go nuts can someone guide me in utilizing the proper syntax to add the criteria to the WHERE CLause. I wish somewhere there was a diagram that explained the syntax so I could print it out for future reference.

Thanks in advance.

Life's a journey enjoy the ride...

jazzz

 

[tsdragon]

objCmd.CommandText = strSQL & " WHERE ItemID = " & rsItems("ItemID") & " AND NOT itemdelete ORDER BY ItemID;"

It might be easier for you if you wrote out the whole SQL statement as you would if typing it into an interpreter, then changed it to include variables, etc.

The basic sql syntax is:

SELECT [i]cols[/i] FROM[i]table[/i] WHERE[i]conds[/i] ORDER BY [i]sort-cols[/i]

where:

   [i]cols[/i] is either * (all cols) or col,col,col

   [i]conds[/i] is cond AND|OR cond AND|OR cond

   [i]sort-cols[/i] is col,col,col

Tracy Dryden

Meddle not in the affairs of dragons,
For you are crunchy, and good with mustard.

 

[jazzz]

Thank you Tracy, what I don't fully understand is when and how to use the double quotes or single quotes. I just get so confused.

Life's a journey enjoy the ride...

jazzz

 

[DotNetGnat]

if your field is numeric, use double quotes..."&yourfield&"
if it is string, use with single quotes...'"&yourfield&"'


-DNG

 

[jazzz]

Thanks DNG, it is appreciated.

Life's a journey enjoy the ride...

jazzz

 

[tsdragon]

I think what he means is double quotes are delimiters for the parts of the VB string - you don't ever use them in the SQL itself. If your field is numeric, use NO quotes around the field VALUE, but you have to use double quotes to close the previous string and open a new string (with your variable concatenated between them. If your field is a string, you need to enclose it in single quotes WITHIN THE SQL STRING, so you have to code single quotes before and after the value within the VB string too. Maybe this will help

' id is a NUMERIC field in the db
' myid is a variable containing 2
sql = "select id from tbl where id = " & myid
      |                              | |   |
      +---------vb string  ----------+ |   |
                       concatenation --+   |
            variable containing a NUMBER --+
'results:
'select id from tbl where id = 2

' name is a STRING field in the db
' myname is a variable containing "tracy"
sql = "select name from tbl where name = '" & myname & "'"
      |                                  || |    |   | |||
      +-- start of vb string             || |    |   | |||
  single quote to enclose string value --+| |    |   | |||
                       end of vb string --+ |    |   | |||
                            concatenation --+    |   | |||
                  variable containing a STRING --+   | |||
                                     concatenation --+ |||
                          start of another vb string --+||
             single quote to enclose the string value --+|
                              end of another vb string --+
'results:
'select name from tbl where name = 'tracy'

That's why I suggested you write it out in just plain SQL first, so you can see what you need. Then you can start replacing values with variables. Close the SQL string, concat the variable, concat the rest of the SQL string, repeat.

Tracy Dryden

Meddle not in the affairs of dragons,
For you are crunchy, and good with mustard.

 

[jazzz]

Tracy, thank you for your excellent diagram. I printed it out and it is now sitting on my desk for future reference. I work with SQL in Access databases but utilizing it in ADO and ASP is just so daunting for a beginner like me.

Here is my problem maybe you can help me out? I have a page to view items that a user enters via a form. I use a session variable to identify them once they log in so I can display their information. Maybe I should be using a cookie instead I don't know at this point.

Anyway the user has the option to delete any item that they entered previously. I really don't allow them to delete it I just hide it from their screen. Therefore if they click on the item they entered then select edit I display the item so they can place a check in a checkbox called delete. I then update the database so the field itemdelete=True. Now the item isn't displayed on the main page called BrowseItems but on the users page it is. Here is why.

Dim rsItems

Set rsItems = Server.CreateObject("ADODB.Recordset")
rsItems.Filter = "SellerID = " & Session("PersonID")
rsItems.Open "tblItem", objConn, 0, 2, 2

What I am doing is just filtering my recordset to display the results of the person logged in I would like to add to the filter itemdelete=False or NOT rsItems("itemdelete") so it doesn't display the deleted item on their personal page. Also I want to sort their personal page by ExpirationDate DESC. I guess I need to rethink my project and maybe do this as a SQL rather than a filter.

Tracy, I really appreciate the diagram it was what I wanted. I am just cutting my teeth on ADO and ASP I am from the DAO and Access school. I sincerely thank you for the help.

Life's a journey enjoy the ride...

jazzz

 

[tsdragon]

I've never used a Filter before, so I can't say for sure, but try this:

' SellerID = xx AND NOT itemDelete
rsItems.Filter = "SellerID = " & Session("PersonID") & " AND NOT itemDelete"

All I did was add the string " AND NOT itemDelete" to the end of the filter string.

To change the sequence, try this:

rsItems.Sort = "ExpirationDate DESCENDING"

Tracy Dryden

Meddle not in the affairs of dragons,
For you are crunchy, and good with mustard.

 

[jazzz]

Thank you Tracy, I tried using the same filter as you described above and I receive the following ASP Error Message: Arguments are of the wrong type, are out of acceptable range, or are in conflict with one another.

The table does include both the sellerid and itemdelete. Looks like another long day with the books.

Life's a journey enjoy the ride...

jazzz

 

[tsdragon]

Make sure SellerID is numeric and itemDelete is boolean (or bit).


Tracy Dryden

Meddle not in the affairs of dragons,
For you are crunchy, and good with mustard.

 

[jazzz]

Yes, they are. SellID is autonumber and ItemDelete is Boolean.

Life's a journey enjoy the ride...

jazzz

 

[DotNetGnat]

The change this line (as suggested by Tracy)

rsItems.Filter = "SellerID = " & Session("PersonID") & " AND NOT itemDelete"


to

rsItems.Filter = "SellID = " & Session("PersonID") & " AND NOT ItemDelete"

-DNG

 

[jazzz]

I typed to fast it is SellerID not SellID, sorry.

Life's a journey enjoy the ride...

jazzz

 

[tsdragon]

Try doing:

response.write Session("PersonID")

and make sure that variable has an actual value and that it is numeric.


Tracy Dryden

Meddle not in the affairs of dragons,
For you are crunchy, and good with mustard.

 

[jazzz]

Here is what I came concerning my filter delima. First, I should have mentioned to all of you I am using Access as the db on the site.

I decided to use an SQL statment and it gives me the desired results. Here it is:

strSQL = "SELECT * FROM tblItem WHERE SellerID = " & strSellerCookie & _
"AND NOT itemdelete Order By ExpirationDate DESC;"

I don't know if I should use a cookie or session variable in my WHERE clause? I am covering myself if the user deletes their cookies so I don't forsee a problem.

Thank you everyone for your help and suggestions. Tracy the diagram was a big help.

Life's a journey enjoy the ride...

jazzz