[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 180
  • Last Modified:

Function in SQL String

I am creating an application that is being used by police departments.  This will scan you driver license and search their database for individuals.  I am using Access as the backend, with VB.Net as the frontend.  

In their database, the master name file has a field for driver license number, there was no 'format' specified for this field so the number could be entered in as 12345678 or 12 345 678 or a combination in between.

When the license is scanned from the barcode it comes in as 12345678.

My situation is that I need to do a select query on the driver license number including any varation of spacing on that field.

Could I run a select statement, formatting the database field (which is mastname.olnno) to replace all spaces with no characters.  Not sure how I can do this though.  Can I run a replace function on the field itself in a select statement?  If not, how can I go about getting this resolved?
0
K-9
Asked:
K-9
  • 5
  • 5
  • 4
  • +2
1 Solution
 
Éric MoreauSenior .Net ConsultantCommented:
why don't you just remove all the spaces from driver license field before comparing?
0
 
K-9Author Commented:
I don't want to change the way their data shows in the database.  The driver license number on your license shows as 123 45 678, however the barcode shows 12345678.  I would like to do a search without having to change any of their data.
0
 
Dale FyeCommented:
Yes, you can use the Replace function in an Access query.

SELECT Replace([olnno], " ", "") as NewOlnno  FROM yourTable

Can the [olnno] field contain any characters besides spaces, like "-", or "_"?

If so, you could nest those Replace commands

SELECT Replace(Replace([olnno], " ", ""), "-", "") as NewOlnno  FROM yourTable



0
Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

 
Éric MoreauSenior .Net ConsultantCommented:
>>I don't want to change the way their data shows in the database.

I don't mean to change, just in the query, use the replace statement to do the comparison
0
 
K-9Author Commented:
fyed, I tried your suggestion, getting the error: 'Object reference not set to an instance of an object' on dr.read

Here is the code..
 
cmd = New OleDbCommand("SELECT MASTNAME.NameID, MASTNAME.FULLNAME, MASTNAME.SSN, MASTNAME.DOB, MASTNAME.ADDRESS1, MASTNAME.CITY, MASTNAME.STATE, MASTNAME.ZIP, replace(MASTNAME.OLNNO,' ','') As NewOLNNO FROM MASTNAME WHERE replace(MASTNAME.OLNNO,' ','') = '" & tmpDriverLicenseNumber & "'", cn)
  
        Do While dr.Read()

Open in new window

0
 
Dale FyeCommented:
Don't think you can do it this way from .Net.

Can you actually create a saved query in the Access database, which contains the Replace function?  If you can, then you would use the query as the source of your query above, instead of the table.

0
 
HainKurtSr. System AnalystCommented:
create a query in your access as

select m.*, replace(OLNNO," ", "") as Fixed_OLNNO from MASTNAME m

save as qryMASTNAME and use this query

cmd = New OleDbCommand("SELECT NameID, FULLNAME, SSN, DOB, ADDRESS1, CITY, STATE, ZIP, fixed_OLNNO As NewOLNNO FROM qryMASTNAME WHERE fixed_OLNNO = '" & tmpDriverLicenseNumber & "'", cn)
Do While dr.Read()
0
 
HainKurtSr. System AnalystCommented:
replace is not available outside access...
0
 
Éric MoreauSenior .Net ConsultantCommented:
apparently, replace is not available when used from an OleDbCommand. Can you create a query that returns the results of the Replace and run your OleDbCommand against this query?
0
 
K-9Author Commented:
Thanks for the replies so far.. HainKurt I tried your suggestion and running from access it does what it says, however on blank olnno entries, it shows #error on the fixed_olnno field, which is ok..

However, running my vb.net project, I get the following error:


error.jpg
0
 
Éric MoreauSenior .Net ConsultantCommented:
aren't you missing a line? it looks like your datareader is never opened/assigned?
0
 
K-9Author Commented:
You are right.. my mistake!  sorry..
I added that line in, and get another error now:

error2.jpg
0
 
Éric MoreauSenior .Net ConsultantCommented:
looks like there is no way around it. it is a limitation of Access.

What I would suggest is to create a computed column in your database that would be updated everytime the license number is updated and that contains the value minus the spaces
0
 
Dale FyeCommented:
What is the full text of your current cmd line:

cmd =
0
 
mbizupCommented:
You can't use Replace() in a SQL statement from .Net.

Go back to your original query, and use your .Net code to restrict processing of your data to the License values you need.

Something like this:


Do While dr.Read()
        If Replace(dr("olnno"), " ", "")  = tmpDriverLicenseNumber  then
                        ' Continue with the rest of your If-Then blocks here
         end if
Loop
0
 
HainKurtSr. System AnalystCommented:
what about writing a udf in access and use it in your sql via oledb?

function myRpelace(str, c,w)
  myReplace= replace(str,c,w)
end function

and use this function in your query... will you get the same error?

cmd = New OleDbCommand("SELECT NameID, FULLNAME, SSN, DOB, ADDRESS1, CITY, STATE, ZIP, myReplace(OLNNO) As NewOLNNO FROM MASTNAME WHERE myReplace(OLNNO) = '" & tmpDriverLicenseNumber & "'", cn)
Do While dr.Read()
0
 
HainKurtSr. System AnalystCommented:
above post probably will give the same error...

have another idea... write a function in asp/vb say LikeStr(s) which does:

LikeStr("12345678") --> "1%2%3%4%5%6%7%8"
or
LikeStr("12345678") --> "1_2_3_4_5_6_7_8"

and use this in your query like:

cmd = New OleDbCommand("SELECT NameID, FULLNAME, SSN, DOB, ADDRESS1, CITY, STATE, ZIP FROM MASTNAME WHERE OLNNO like '" & LikeStr(tmpDriverLicenseNumber) & "'", cn)
Do While dr.Read()

it should work! try first with a hardcoded value, if it works, write the function LikeStr(s)
0
 
HainKurtSr. System AnalystCommented:
or this

LikeStr("12345678") --> "1*2*3*4*5*6*7*8"

for example this query gives me


SELECT * from cities where name like 'N*e*w*C*i*t*y*C*A'

City      Name
1      New City CA      

note the spaces in city name...
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 5
  • 5
  • 4
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now