Need some SQL help please

[GoingUp]

Workin on a project for school.

I have a database called customers. A user logs into the system by entering their customer id and name. An SQL database on an SQL server should get checked to verify that the user is registered. I cant seem to find any information on how to write a statement that checks to see if the information is in the database at all.

Sql = "SELECT * FROM CUSTOMER " '& _
Cmd = New OleDb.OleDbCommand(Sql, Con)

Con.Open()


Reader = Cmd.ExecuteReader(CommandBehavior.CloseConnection)
If Reader.Read() Then

labeltext = Reader("First_name").ToString
labeltext &= Reader("Last_name").ToString
labeltext &= Reader("Company").ToString
Label2.Text = labeltext

End If

Ive been using the label2 to print out whats getting queried and right now when I run the script as is, I get First_name as a return. First_name is the headings of one of my columns. How the heck is that coming up? I would appreciate any help that I anyone could provide with an SQL statement to check the database for an existing name and customer number.

 

[StageLeft]

In regards to the basic SQL statement, I'd imagine that you'd want something like:

SELECT * FROM CUSTOMERS WHERE Customer_Name = 'Gobadgrs' AND Customer_Password = 'ILoveSkoorbie'

 

[KLin]

I would assume that you would take the username that was inputted and set it to a variable, then concatenate the variable to the SQL statement. If no records are returned then you open a msgbox saying that the user needs to register. At least that's how I would do it in VBA.

 

[GoingUp]

Originally posted by: Skoorb
In regards to the basic SQL statement, I'd imagine that you'd want something like:

SELECT * FROM CUSTOMERS WHERE Customer_Name = 'Gobadgrs' AND Customer_Password = 'ILoveSkoorbie'

Of course I love you

Will the SQL statement automatically search through all the records in the table? The user id and name will also be text boxes, so how do I get the values from that text box into an SQL statement?

Edit: It should look something like this right? Sql = "SELECT " & inputtext & "FROM CUSTOMER "

How do you let the user know that the record wasnt found, as in if the search comes up empty?

Anyone know the command for that?

 

[KLin]

Originally posted by: Gobadgrs

Originally posted by: Skoorb
In regards to the basic SQL statement, I'd imagine that you'd want something like:

SELECT * FROM CUSTOMERS WHERE Customer_Name = 'Gobadgrs' AND Customer_Password = 'ILoveSkoorbie'

Of course I love you

Will the SQL statement automatically search through all the records in the table? The user id and name will also be text boxes, so how do I get the values from that text box into an SQL statement?

Edit: It should look something like this right? Sql = "SELECT " & inputtext & "FROM CUSTOMER "

How do you let the user know that the record wasnt found, as in if the search comes up empty?

Anyone know the command for that?

since this is obviously homework, shouldn't you be doing the research yourself to solve the problem?

 

[GoingUp]

Originally posted by: KLin

Originally posted by: Gobadgrs

Originally posted by: Skoorb
In regards to the basic SQL statement, I'd imagine that you'd want something like:

SELECT * FROM CUSTOMERS WHERE Customer_Name = 'Gobadgrs' AND Customer_Password = 'ILoveSkoorbie'

Of course I love you

Will the SQL statement automatically search through all the records in the table? The user id and name will also be text boxes, so how do I get the values from that text box into an SQL statement?

Edit: It should look something like this right? Sql = "SELECT " & inputtext & "FROM CUSTOMER "

How do you let the user know that the record wasnt found, as in if the search comes up empty?

Anyone know the command for that?

since this is obviously homework, shouldn't you be doing the research yourself to solve the problem?

Ive been at it for a while and im trying to get some help. Here is what I currently have.

SELECT Cust_no, Last_name FROM CUSTOMER WHERE Cust_no =" & idtext & " AND Last_name=" & nametext

idtext is from the cust id text box and nametext is from the last name text box. for whatever reason when i run the code and type in the last name, it says that "smith" is not a valid column name

smith is the last name that I am typing in. i cant seem to figure out why the database thinks that smith is the name of a column

 

[GoingUp]

for whatever reason when I search the userID column it always works, but whenever I include or search any of the columns with text the search always fails as the database thinks that the name of the value I want to search for is the name of the column.

EG if I type in Gobadgrs for the first name it will tell me that Gobadgrs is not a valid column.

Im still using the select statement in the above post and I can't see whats wrong with it at all.

 

[replicator]

"SELECT Cust_no, Last_name FROM CUSTOMER WHERE Cust_no ='" & idtext & "' AND Last_name='" & nametext & "'"

make sure that you surround the name of the comparison string with a single quotation lik

' Gobadgrs '

Instead of Gobadgrs

Instead of using a dynamic query, you should look into using one that is parameterized

Less error prone and if you don't carefully create dynamic queries, it can be big security hole.

 

[GoingUp]

Originally posted by: replicator
"SELECT Cust_no, Last_name FROM CUSTOMER WHERE Cust_no ='" & idtext & "' AND Last_name='" & nametext & "'"

make sure that you surround the name of the comparison string with a single quotation lik

' Gobadgrs '

Instead of Gobadgrs

Instead of using a dynamic query, you should look into using one that is parameterized

Less error prone and if you don't carefully create dynamic queries, it can be big security hole.

Bloody brilliant! Thanks for your help! It looks like I was close, but I forgot the single quotes. Can you explain to me quick why I need single quotes?

 

[replicator]

You need single quotes so that SQL will know it is a string it should be comparing, not a reference.

In the VB code, it may appear as though you don't need the quotes , but this query string is again passed to the database and thats where it is needed.

If passed without the quotes around your input variable, it would appear to the database as:

SELECT Cust_no, Last_name FROM CUSTOMER WHERE Cust_no = Gobadgrs

but really it should appear as

SELECT Cust_no, Last_name FROM CUSTOMER WHERE Cust_no = 'Gobadrs'

Otherwise the database won't know that it is a string constant.

 

[GoingUp]

Originally posted by: replicator
You need single quotes so that SQL will know it is a string it should be comparing, not a reference.

In the VB code, it may appear as though you don't need the quotes , but this query string is again passed to the database and thats where it is needed.

If passed without the quotes around your input variable, it would appear to the database as:

SELECT Cust_no, Last_name FROM CUSTOMER WHERE Cust_no = Gobadgrs

but really it should appear as

SELECT Cust_no, Last_name FROM CUSTOMER WHERE Cust_no = 'Gobadrs'

Otherwise the database won't know that it is a string constant.

Ahhhh Much obliged!

 

[Lint21]

Also, in cases like this, some simple text output can be very helpful when debugging. For example, you could have your app display the SQL being passed into the database and check it to make sure nothing screwy is happening in the concatenation.