SQL statement question

[timothy2l]

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.

[Nerseus]

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?

 

Here's how you select the last 4 characters:

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:

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 "%":

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:

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 - that's 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

[timothy2l]

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