SQL statement question

T

timothy2l

Well-known member
Joined
Jul 3, 2003
Messages
61
I want to SELECT the last four characters from a Social Security Number (SSN) record in a DB field. Here are two things I have tried so far:

Sql1 = "SELECT SSN, DateOfBirth FROM Validation WHERE SSN = " + txtSSN.Text = Right("SSN", 4) + " AND DateOfBirth = " + txtDOB.Text + ""


Sql1 = "SELECT Right("SSN", 4), DateOfBirth FROM Validation WHERE Right("SSN", 4)= " + txtSSN.Text + " AND DateOfBirth = " + txtDOB.Text + ""

I get errors with both of these. Can someone help me out?
Thanks in advance.
 
First, do you want to return the last 4 digits of SSN, or use the last 4 as part of the WHERE clause to filter by?

Heres how you select the last 4 characters:
Code: 
SELECT Right(SSN, 4) AS [ShortSSN], DateOfBirth FROM Validation WHERE...

Now I assume you have two textboxes, txtSSN and txtDOB that you want to filter by. If the SSN textbox contains the full SSN, then - in other words you want to filter on an exact match - then do this:
Code: 
Sql1 = "SELECT SSN, DateOfBirth FROM Validation WHERE SSN = " + txtSSN.Text.Replace("", "") + " "
Sql1 = Sql1 + " AND DatOfBirth = " + txtDOB.Text.Replace("", "") + ""

If the txtSSN might contain the first few characters of the SSN (most common), use LIKE. The big difference below is the word "LIKE" and the use of "%":
Code: 
Sql1 = "SELECT SSN, DateOfBirth FROM Validation WHERE SSN LIKE " + txtSSN.Text.Replace("", "") + "% "
Sql1 = Sql1 + " AND DatOfBirth = " + txtDOB.Text.Replace("", "") + ""

If the txtSSN might contain the last few (or 4) characters of SSN, be prepared for a SLOW search as all databases will have to do a table scan to find matching rows:
Code: 
Sql1 = "SELECT SSN, DateOfBirth FROM Validation WHERE SSN LIKE %" + txtSSN.Text.Replace("", "") + " "
Sql1 = Sql1 + " AND DatOfBirth = " + txtDOB.Text.Replace("", "") + ""
In the above, the "%" went on the front of the string - thats the only difference.

For the record, always use Replace("", "") when passing strings to a database SQL statement. Badly formed strings (with single quotes) could cause errors, or worse with malicious users.

-Ner
 
The database field has the full SSN but the user only enters the last 4 digits in the textbox. The SQL statement should select the record that has the matching last 4 digits and DOB. It will be a small table with probably fewer than 100 records. Is the last example you posted assuming the user only enters the last 4 digits in the txtbox?

Also, what is the SQL statement to insert a checkbox value into a T/F field? I keep getting syntax errors.
Thanks for your help
 
Back
Top