Where statement

[richh]

This time, I spent a good while going through documentations before making this post

ok, so I am working with SQL 2005 and I am running queries against it just fine. the problem I am running into now is that I want to query the database based upon data in which I have in a text field.

in the below statement:

Code: Select all

  put revDataFromQuery(comma,return, dbConnectionID, "select user_name, user_login, user_password, role_id from T_USERS where user_name =" & numToChar(39) & tUser & numToChar(39) & " and user_password = " & numToChar(39) & tPassword & numToChar(39)) into tData

I get an error. when I check SQL Profiler, I see that the statement is being generated as ' ' username ' ' and ' ' password ' ' which is an incorrect sql statement for MS SQL. It should be 'username' and 'password'

I searched the dictionary and came across quote which gave the example of:

Code: Select all

put quote field 2 quote into responseToPost

when I modify it to run in my example, it gives me an error saying that its missing a comma so I have to run it like

Code: Select all

put quote, field 2, quote into responseToPost

but this gives me ",sometext,"

anywho... best way to customize the where statement?

[mroam]

Hi Richh;

your simple example

Code: Select all

put quote, field 2, quote into responseToPost

which produces ",sometext," should be written as

Code: Select all

put quote & field 2 & quote into responseToPost

which uses the ampersand to combine text, and would produce
"sometext"
which I think is what you want.

Note: in some sample code you might see people using double ampersand (&&), but that supplies a blank between the text snippets, so

Code: Select all

put quote && field 2 && quote into responseToPost

would result in " sometext "

--Mike
ps: I don't know enough about SQL yet to attempt to revise your revDataFromQuery statement.
Briefly looking at it I wonder why you're using numToChar(39), which should produce single quote ' (apostrophe).

[richh]

thank Mike for your input.

I gave the below a try

Code: Select all

put quote & field 2 & quote into responseToPost

and you are correct, I needed & instead of commas. The dictionary should probably be updated.

the reason why I am using numToChar(39) or single quote is because when you write a MS SQL statement and are referring to string, you need to use single quotes.

for example:

Code: Select all

select user_id, user_firstname, user_lastname, user_dob from T_USER where user_firstname = 'john' and user_lastname = 'smith'

this would return an entry with john in the user_firstname column and smith in the user_lastname column.

I was playing around with the code some more and broke it out as such

Code: Select all

   -- Grab Username / Password
   
   put numToChar(39) & field "userNameField" & numToChar(39) into tUser
   put numToChar(39) & field "passwordField" & numToChar(39) into tPassword
   put "select user_name, user_login, user_password, role_id from T_USER where user_name =" & tUser & " and user_password = " & tPassword & "" into tStatement
   
   -- Perform query on database.
   
   put revDataFromQuery(comma,return, dbConnectionID, tStatement) into tData

I noticed that tStatement string is correctly formatted; however, when tStatement is applied to the query, I get double single quotes before and after the user name and password.

I am not sure if revDataFromQuery modifies the select statement or not once a string is submitted to it.

[richh]

I think I am getting closer

Code: Select all

   -- Grab Username / Password
   put field "userNameField" into tUser
   put field "passwordField" into tPassword
   
   -- Perform query on database.
   put revDataFromQuery(comma,return, dbConnectionID, "select user_name, user_login, user_password, role_id from T_USER where user_name =:1 and user_password = :2", "tUser","tPassword") into tData

This time no double single quotes; however, not seeing any data in tData

[bangkok]

No need to make your life complicated.

Code: Select all

put "select user_name, user_login, user_password, role_id from T_USER where user_name='"&tUser&"' and user_password='"&tPassword&"'" into dbSQL
put revDataFromQuery(comma,return, dbConnectionID, dbSQL) into tData

(note we have a single quote followed by a double quote, before and after each variable)

And just to be sure that your query is fine, make a :

Code: Select all

answer dbSQL

before the revDataFromQuery

[Klaus]

Hi Rich,

here some little timesaving functions that I always have in my stackscript(s):

Code: Select all

function Q tText
  return QUOTE & tText & QUOTE
end Q

And this one for single QUOTES:

Code: Select all

function Q2 tText
  return "'" & tText & "'"
end Q2

Especially very handy for building SQL statements!


Best

Klaus

[richh]

thank you Klaus & bangkok for your replies...

I tried various combinations and still ending up with the same result

using this code:

Code: Select all

   -- Grab Username / Password
   put field "userNameField" into tUser
   put field "passwordField" into tPassword
   
 
   -- Perform query on database.
   
   put "select user_name, user_login, user_password, role_id from T_USER where user_name='"&tUser&"' and user_password='"&tPassword&"'" into dbSQL
   put revDataFromQuery(comma,return, dbConnectionID, dbSQL) into tData

SQL Profiler shows:

Code: Select all

declare @p1 int
set @p1=1
exec sp_prepare @p1 output,NULL,N'select user_name, user_login, user_password, role_id from T_USER where user_name=''jsmith'' and user_password=''pwd''',1
select @p1

However, dbSQL does show single quotes which tells me that when revDataFromQuery runs, it modifies the single quotes to two single quotes.

Klaus,

that is a great tidbit to know about the use of functions.

[richh]

OMG... the problem was my sql query the whole time. I was porting over one of my applications and I should have used user_login and not user_name

everything works as intended.

once again... thank you everyone for your input. I promise not to post any new items for a few days as to give you guys a break from my questions