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

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

SQL Server and VB 6.0

I have a function which I wrote to validate it against SQL Server Db and this value which should be validated is a scanned barcode value ...  this works fine when I scan a partnumber it checks this partnumber against the db and works correctly but not for the other partnumber ... Is there any specific reason for this as I am a newbie to Sql Server as well to VB . Anyone's help would be greatly appreciated and below is the function code
Function validatepartNo(strpartnumber As String) As Boolean
 Dim cnn As New ADODB.Connection
 Dim rst As New ADODB.Recordset
 Dim strSQL As String
  cnn = SQLDB_CONN_STRING
 cnn.CommandTimeout = 300    ' added
 cnn.Open
    ' MsgBox strpartnumber
 strSQL = "Select count(*) from tblParts Where UPN = '" & Trim(strpartnumber) & "'"
 rst.Open strSQL, cnn
  If Trim(rst(0).Value = 0) Then
    validatepartNo = False
    MsgBox "Partno not existing in tblParts table", vbOKOnly, " Invalid Partno"
  Else
    validatepartNo = True
  End If
 rst.Close
 cnn.Close
End Function

Open in new window

0
Radhs74
Asked:
Radhs74
  • 16
  • 9
  • 6
  • +1
1 Solution
 
BrandonGalderisiCommented:
Try POPPING strSQL and see if the SQL looks valid.
Function validatepartNo(strpartnumber As String) As Boolean
 Dim cnn As New ADODB.Connection
 Dim rst As New ADODB.Recordset
 Dim strSQL As String
  cnn = SQLDB_CONN_STRING
 cnn.CommandTimeout = 300    ' added
 cnn.Open
    ' MsgBox strpartnumber
 strSQL = "Select count(*) from tblParts Where UPN = '" & Trim(strpartnumber) & "'"
msgbox strSQL
 rst.Open strSQL, cnn
  If Trim(rst(0).Value = 0) Then
    validatepartNo = False
    MsgBox "Partno not existing in tblParts table", vbOKOnly, " Invalid Partno"
  Else
    validatepartNo = True
  End If
 rst.Close
 cnn.Close
End Function

Open in new window

0
 
Radhs74Author Commented:
I did actually it does show the value of the barcode value in the sql stmt soemthing like this
"select count(*) from tblparts where upn = 613991234 "
this value exists in the table but it displays the message as
MsgBox "Partno not existing in tblParts table", vbOKOnly, " Invalid Partno"
which it should not it should normally continue processing after it validates against db

0
 
BrandonGalderisiCommented:
is UPN an INT or VARCHAR field.  your strSQL = puts it in quotes.

you should also be checking the status of your recordset between lines 11 and 12.

if not rst.eof and not rst.bof then
  If Trim(rst(0).Value = 0) Then
    validatepartNo = False
    MsgBox "Partno not existing in tblParts table", vbOKOnly, " Invalid Partno"
  Else
    validatepartNo = True
  End If
end if
0
Fill in the form and get your FREE NFR key NOW!

Veeam is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

 
mdouganCommented:
Based on your sample code, it appears that the UPN field of your tblParts table is defined as either a varchar or char field (since you are wrapping single quotes around it).  If this is true, then you might try running a query in SQL Server, something like:

select distinct UPN, LEN(UPN) as PLength from tblParts

Then, compare the lengths with the number of characters you see... it is possible that some part number got entered into your table with a space at the beginning, or at the end, or it might even be a garbage non-visible character... but it should still show up in the Length.

If your UPN field is defined as a number  (decimal, integer etc), then you should remove the single quotes in the WHERE clause.

In my experience, if that column in the table appears to have a value equal to what you are passing in the query, and yet the query can't find it, the fault lies with an extra space or character before or after the number part.
0
 
Radhs74Author Commented:
It is nvarchar(25) datatype in the table
0
 
Radhs74Author Commented:
Hi Brandon:
   I inserted that EOF and BOF statement but it is still the same ... any other suggestions please!!
How can I check the status of this
you should also be checking the status of your recordset between lines 11 and 12.
by using a msgbox or debugging and if so what I need to check there?

Thanks for your time
Radhs


0
 
BrandonGalderisiCommented:
"you should also be checking the status of your recordset between lines 11 and 12."

That's what this does:

if not rst.eof and not rst.bof then

This:  If Trim(rst(0).Value = 0) Then

Also doesn't need trimmed since it's an int.  and .Value shouldn't be necessary.

If rst(0) = 0 Then


Try this:

Function validatepartNo(strpartnumber As String) As Boolean
 Dim cnn As New ADODB.Connection
 Dim rst As New ADODB.Recordset
 Dim strSQL As String
  cnn = SQLDB_CONN_STRING
 cnn.CommandTimeout = 300    ' added
 cnn.Open
    ' MsgBox strpartnumber
 strSQL = "Select count(*) from tblParts Where UPN = '" & Trim(strpartnumber) & "'"
msgbox strSQL
 rst.Open strSQL, cnn
  If rst(0) = 0 Then
    validatepartNo = False
    MsgBox "Partno not existing in tblParts table", vbOKOnly, " Invalid Partno"
  Else
    validatepartNo = True
  End If
 rst.Close
 cnn.Close
End Function

Open in new window

0
 
Radhs74Author Commented:
Hi Dougan:

Can you tell me in detail the steps I got to do

Thanks for your time
Radhs
0
 
Radhs74Author Commented:

Brandon:
   I tried something like this but not working
rst.Open strSQL, cnn
       If rst(0) = 0 Then
         validatepartNo = False
         MsgBox "Partno not existing in tblPartlookup table", vbOKOnly, " Invalid Partno"
      Else
         validatepartNo = True
      End If
 
thanks
Radhs
0
 
BrandonGalderisiCommented:
Try:
Function validatepartNo(strpartnumber As String) As Boolean
 Dim cnn As New ADODB.Connection
 Dim rst As New ADODB.Recordset
 Dim strSQL As String
  cnn = SQLDB_CONN_STRING
 cnn.CommandTimeout = 300    ' added
 cnn.Open
    ' MsgBox strpartnumber
 strSQL = "Select count(*) cnt from tblParts Where UPN = '" & Trim(strpartnumber) & "'"
msgbox strSQL
 rst.Open strSQL, cnn
  If rst("cnt") = 0 Then
    validatepartNo = False
    MsgBox "Partno not existing in tblParts table", vbOKOnly, " Invalid Partno"
  Else
    validatepartNo = True
  End If
 rst.Close
 cnn.Close
End Function

Open in new window

0
 
Radhs74Author Commented:

Brandon:
I am getting this error
3265 : Item cannot be found in the collection corresponding to the requested name or ordinal

Thanks
Radhs
0
 
Radhs74Author Commented:
Brandon:
This is the record in the table tblparts and the value which we are validating against the scanned value  is 20613994516873
2430      ZK4003M      13      20613994516873      TJM 100PK      NULL      5      NULL      NULL      NULL      NULL      2009-02-17 09:28:46.273

Thanks
Radhs

0
 
BrandonGalderisiCommented:
Hmm... That's saying that cnt (the alias I put on count(*)) isn't in rst (your recordset).
0
 
BrandonGalderisiCommented:
Let's try this...
Function validatepartNo(strpartnumber As String) As Boolean
 Dim cnn As New ADODB.Connection
 Dim rst As New ADODB.Recordset
 Dim strSQL As String
  cnn = SQLDB_CONN_STRING
 cnn.CommandTimeout = 300    ' added
 cnn.Open
    ' MsgBox strpartnumber
 strSQL = "Select 1 cnt from tblParts Where UPN = '" & Trim(strpartnumber) & "'"
msgbox strSQL
 rst.Open strSQL, cnn
  If rst.eof and rst.bof Then
    validatepartNo = False
    MsgBox "Partno not existing in tblParts table", vbOKOnly, " Invalid Partno"
  Else
    validatepartNo = True
  End If
 rst.Close
 cnn.Close
End Function

Open in new window

0
 
Deepu SreedharBI Software EngineerCommented:
Why not to try this?
Function validatepartNo(strpartnumber As String) As Boolean
 Dim cnn As New ADODB.Connection
 Dim rst As New ADODB.Recordset
 Dim strSQL As String
  cnn = SQLDB_CONN_STRING
 cnn.CommandTimeout = 300    ' added
 cnn.Open
    ' MsgBox strpartnumber
 strSQL = "Select count(*) from tblParts Where UPN = '" & Trim(strpartnumber) & "'"
 rst.Open strSQL, cnn
  If Trim(rst(0).Value) = 0 Then
    validatepartNo = False
    MsgBox "Partno not existing in tblParts table", vbOKOnly, " Invalid Partno"
  Else
    validatepartNo = True
  End If
 rst.Close
 cnn.Close
End Function

Open in new window

0
 
Radhs74Author Commented:
Hy Brandon:
Same error it is displaying this in the msgbox something like this
Select 1 cnt from tblPartLookup Where fsUPN = 613994516873

and then this message
MsgBox "Partno not existing in tblPartlookup table", vbOKOnly, " Invalid Partno"

Thanks
Radhs
0
 
Radhs74Author Commented:
Hi DeepuSreedhar:

I tried that in the beginning itself but not working!

Thanks
Radhs
0
 
BrandonGalderisiCommented:
is it upn or fsupn?  Is it with or without quotes?

Your data example above shows:

20613994516873

But your query shows:

613994516873

That's not a match.  It's working correctly.
0
 
Radhs74Author Commented:
Hi Brandon:

It is fsupn and this what i did below and this works when I hardcode the value and the value which I am scanning has actually 3 values which I divided into like this
Private Sub ParseHeader(strBarcode As String)
   strpartnumber = Mid(strBarcode, 5, 12)
   strdate = Mid(strBarcode, 19, 6)
   strlnum = Mid(strBarcode, 27, 9)
End Sub

so all through the application it takes the strpartnumber from position 5 to about 12 chars actually it is about 14 chars ( I am not sure why they have taken 12 instead of 14) but as this application was already developed by some one and they asked me to make changes which I am doing now and below is the function which I modified now or hardcoded

Function validatepartNo(strpartnumber As String) As Boolean
 Dim cnn As New ADODB.Connection
 Dim rst As New ADODB.Recordset
 Dim strSQL As String
 Dim strfsupn
  cnn = new_cnn
 cnn.CommandTimeout = 300    ' added
 cnn.Open
 
 strfsupn =20613994516873#
 MsgBox strfsupn
 
  strSQL = "Select count(*) from tblPartLookup Where fsUPN like '" & Trim(strfsupn) & "'"
   MsgBox strSQL

   rst.Open strSQL, new_cnn
      If (rst(0).Value) = 0 Then
           validatepartNo = False
         MsgBox "Partno not existing in tblParts table", vbOKOnly, " Invalid Partno"
      Else
         validatepartNo = True
      End If
  rst.Close
 cnn.Close
End Function

this works when i hardcode but not when I try to scan .... so I am not sure what I got to do now

Thanks
Radhs
0
 
Radhs74Author Commented:

Brandon:
Even though I changed the parseheader function it is not working .... I am not supposed to change anything other then adding the code  as it should not reflect on the other part of the application ...

Thanks
Radhs
0
 
mdouganCommented:
Hi Radhs74,

In a few of the queries listed above, the table name is variously called tblParts and tblPartLookup, and the field name is variously called fsUPN and UPN... so, you have to decide which is the correct table name and field name... assuming that they are what you had in your original query - tblParts and UPN then use the queries below... if the actual table name and/or field name are different, then substitute as necessary.

To tell if you might have a bad part number in your database, for this particular record, you should go into SQL Server, either Query Analyser or Enterprise Manager, select your database, open a new Query Pane and try the following three queries:

select count(*) from tblParts where UPN = '20613994516873'

select count(*) from tblParts where UPN LIKE '%20613994516873'

select count(*) from tblParts where UPN LIKE '20613994516873%'

All three of these queries should return the count of 1.  If the first query returns a count of zero, then see if either of the other two queries return a count of 1.  If the first query is zero and the second is 1 then you have a UPN with a space or special character in front of the number.  If the first query is zero, the second query is zero and the third query is 1 then that means that you have a UPN with a space or special character at the end of the number.

0
 
Radhs74Author Commented:
Sorry Brandon I had to change the column name and table name for personal reasons

select count(*) from tblParts where  UPN = '20613994516873'
this returns count as 1

select count(*) from tblParts where UPN LIKE '%20613994516873'
also returns count as 1

select count(*) from tblParts where UPN LIKE '20613994516873%'
also returns count as 1

Thanks
Radhs






0
 
mdouganCommented:
Hi Radhs74,  

Your problem is with this:

Private Sub ParseHeader(strBarcode As String)
   strpartnumber = Mid(strBarcode, 5, 12)
   strdate = Mid(strBarcode, 19, 6)
   strlnum = Mid(strBarcode, 27, 9)
End Sub

"so all through the application it takes the strpartnumber from position 5 to about 12 chars actually it is about 14 chars ( I am not sure why they have taken 12 instead of 14) but as this application was already developed by some one and they asked me to make changes which I am doing now and below is the function which I modified now or hardcoded "

Your parseheader routine is starting at character 5 and taking the next 12 characters as the part number.  If you see that the part number passed to the query is   '613994516873' then the first two characters are getting truncated...  so, your parseheader routine might need to change to:

   strpartnumber = Mid(strBarcode, 3, 14)

But, if that messes up the part number for other records, then you've got a bigger problem on your hand... why is this one part number 14 characters and the others only 12 characters?

You could change your query like this and it will probably work... but it is a kludgy way to do it.  You should do an exact compare on the part number, not a LIKE.

strSQL = "Select count(*) from tblPartLookup Where fsUPN like '%" & Trim(strfsupn) & "'"
   MsgBox strSQL

   rst.Open strSQL, new_cnn

0
 
BrandonGalderisiCommented:
Same error it is displaying this in the msgbox something like this
Select 1 cnt from tblPartLookup Where fsUPN = 613994516873
Same error it is displaying this in the msgbox something like this
Select 1 cnt from tblPartLookup Where fsUPN = 613994516873
Same error it is displaying this in the msgbox something like this
Select 1 cnt from tblPartLookup Where fsUPN = 613994516873


Every time you sayd that fsupn/upn is 613994516873.  But your DATABASE is 20613994516873.  Like I said in http:#a23662188 it is working.  The value you are searching for is NOT what is in the database.
0
 
mdouganCommented:
If you say that all through the application, they are using a 12 character part number, but the actual part number in the table can have up to 14 characters, then it sounds like at one point in time, the part number grew from 12 characters to 14 (or more) characters and the programmer back then failed to make all the necessary changes.  And, you will need to bring that to the attention of the people who say that you are not supposed to change anything else.
0
 
Radhs74Author Commented:
Dougan:
  This works perfectly ... will there be any other issues if I go ahead and implement it
strSQL = "Select count(*) from tblParts Where UPN like '%" & Trim(strfsupn) & "'"

Thanks a lot,
Radhs
0
 
Radhs74Author Commented:
Ok will do that!!

Thanks a lot
Radhs
0
 
mdouganCommented:
Well, the reason I said that it was "kludgy" (that means 'not really the best way to do it') is that the query will return a count of 1 or more if you submit the value  LIKE '%613994516873'  and, you have the following parts in your database:

2430      ZK4003M      13      20613994516873
2431      ZK4003N      14      30613994516873
2432      ZK4003O      15      40613994516873

If you just do the    LIKE '%613994516873'   then you will get a count of 3.  If you were looking for part number    50613994516873 and you truncate the first two characters in your ParseHeader routine, then pass the LIKE '%613994516873'   to the query, it will return 3 (because of the other existing part numbers) when it really should have returned 0 because, let's say, part number 50613994516873 doesn't exist in the table.

So, I really think you need to discuss this problem with your supervisors.  You should be looking for exact matches on part numbers.  If some part numbers are 14 characters and some are 12 characters, then you have to adjust your ParseHeader routine to account for the differences.  If they have changed the Barcode standard, then your current ParseHeader routine is incorrect.
0
 
Radhs74Author Commented:


Dougan:

Thanks for the detailed explanation and your time

Radhs
0
 
mdouganCommented:
My pleasure!
0
 
BrandonGalderisiCommented:
Let's not forgot that I pointed this problem out LONG ago in http:#a23662188
0
 
Radhs74Author Commented:
Thanks to you Brandon
For your time and cooperation

Thanks again
Radhs
0

Featured Post

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

  • 16
  • 9
  • 6
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now