• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 370
  • Last Modified:

Looking in Multiple tables (VB6 / SQL2K)

Here is the code I'm currently using:

  Dim m_Cost As String
  Dim m_QtyM As String
  Dim db As Connection
  Set db = New Connection
  db.CursorLocation = adUseClient
  db.Open "PROVIDER=MSDASQL;driver={SQL Server};server=USSRM-FP04;uid=;pwd=;database=PhysicalInventory;"
  Set adoPrimaryRS5 = New Recordset
  adoPrimaryRS5.Open "Select imPiawipof.Cost, imPiawipof.Quantity from imPiawipof where ('" & m_Part & "' = imPiawipof.PartNumber)", db, adOpenStatic, adLockOptimistic
  GetCost = adoPrimaryRS5("Cost")
  GetQtyM = adoPrimaryRS5("Quantity")
  db.Close
  Set db = Nothing
  Set adoPrimaryRS5 = Nothing
  m_Cost = GetCost
  m_QtyM = GetQtyM

What I want to do is to check the table above. If it finds that m_Part does in fact match imPiawipof.PartNumber, then go on to the next part of my application. If it does not, then I want to check another table (imPiawiproic). I want it to keep doing this until it finds a match.
There will be three other available tables in addition to the ones I just mentioned. They are imPifin, imPirrb, and imPiwip. All of the tables contain the same column names and are in the same DB as the one above. How do I do this? TIA
0
Mike Miller
Asked:
Mike Miller
  • 4
  • 3
  • 2
  • +1
2 Solutions
 
Johnny NewbeeCommented:
Try this with ado --

 Dim strSQL as string
 Dim rs as ADODB.recordset

 Dim m_Cost As String
 Dim m_QtyM As String
 Dim db As Connection

  Set db = New Connection
  db.Open "PROVIDER=MSDASQL;driver={SQL Server};server=USSRM-FP04;uid=;pwd=;database=PhysicalInventory;"
  db.CommandTimeout=300

rs.CursorLocation = adUseClient
rs.CursorType=adOpenStatic
rs.activeconnection=db
 
strSQL="Select imPiawipof.Cost, imPiawipof.Quantity from imPiawipof where ('" & m_Part & "' = imPiawipof.PartNumber)"

rs.source=strsql
rs.open

if rs.recordcount<=0 then
     rs.close
    strSQL="Select imPifin.Cost, imPifin.Quantity from imPifin where ('" & m_Part & "' = imPifin.PartNumber)"
     rs.source=strsql
     rs.open
     if rs.recordcount<=0 then
             rs.close
             strSQL="Select table3.Cost, table3.Quantity from table3 where ('" & m_Part & "' = table3.PartNumber)"
             rs.source=strsql
             rs.open
             if rs.recordcount<=0 then
                    'YOU SHOULD GET THE IDEA FROM HERE NOW...  
             else
                    m_Cost = rs.fields(0)
                    m_QtyM = rs.fields(1)                  
             end if
      else
             m_Cost = rs.fields(0)
             m_QtyM = rs.fields(1)
      end if
else
  m_Cost = rs.fields(0)
  m_QtyM = rs.fields(1)
end if

-------------------------------------------
If you don't like the nest way, you could put 4 table names into an array for strSQL and loop through them do get the queries going. I just do it quick to get your points.




0
 
Mike MillerSoftware EngineerAuthor Commented:
what do i have wrong here??



Dim strSQL as string
 Dim rs as ADODB.recordset

 Dim m_Cost As String
 Dim m_QtyM As String
 Dim db As Connection

  Set db = New Connection
  db.Open "PROVIDER=MSDASQL;driver={SQL Server};server=USSRM-FP04;uid=;pwd=;database=PhysicalInventory;"
  db.CommandTimeout=300

rs.CursorLocation = adUseClient
rs.CursorType=adOpenStatic
rs.activeconnection=db
 
strSQL="Select imPiawipof.Cost, imPiawipof.Quantity from imPiawipof where ('" & m_Part & "' = imPiawipof.PartNumber)"

rs.source=strsql
rs.open

if rs.recordcount<=0 then
     rs.close
    strSQL="Select imPifin.Cost, imPifin.Quantity from imPifin where ('" & m_Part & "' = imPifin.PartNumber)"
     rs.source=strsql
     rs.open
     if rs.recordcount<=0 then
             rs.close
             strSQL="Select imPiawiproic.Cost, imPiawiproic.Quantity from imPiawiproic where ('" & m_Part & "' = imPiawiproic.PartNumber)"
             rs.source=strsql
             rs.open
             if rs.recordcount<=0 then        
             rs.close
             strSQL="Select imPirrb.Cost, imPirrb.Quantity from imPirrb where ('" & m_Part & "' = imPirrb.PartNumber)"
             rs.source=strsql
             rs.open
             if rs.recordcount<=0 then
             rs.close
             strSQL="Select imPiwip.Cost, imPiwip.Quantity from imPiwip where ('" & m_Part & "' = imPiwip.PartNumber)"
             rs.source=strsql
             rs.open
                   
             else
                    m_Cost = rs.fields(0)
                    m_QtyM = rs.fields(1)                  
             end if
      else
             m_Cost = rs.fields(0)
             m_QtyM = rs.fields(1)
      end if
else
  m_Cost = rs.fields(0)
  m_QtyM = rs.fields(1)
end if
0
 
Johnny NewbeeCommented:
First thought --

You have 5 sql quering, but only 3 else ...
0
Cloud Class® Course: Certified Penetration Testing

This CPTE Certified Penetration Testing Engineer course covers everything you need to know about becoming a Certified Penetration Testing Engineer. Career Path: Professional roles include Ethical Hackers, Security Consultants, System Administrators, and Chief Security Officers.

 
Johnny NewbeeCommented:
Let me know exact error message you got. It should stop at the line which has problem.
0
 
rdwillettCommented:
   Dim m_Cost As String
    Dim m_QtyM As String
    Dim db As ADODB.Connection
    Dim adoPrimaryRS5 As ADODB.Recordset
    Dim TableName As String
    Dim x As Integer
   
    Set db = New ADODB.Connection
    db.CursorLocation = adUseClient
    db.Open "PROVIDER=MSDASQL;driver={SQL Server};server=USSRM-FP04;uid=;pwd=;database=PhysicalInventory;"
   
    Set adoPrimaryRS5 = New Recordset
    m_Cost = ""
    m_QtyM = ""
    For x = 0 To 3
        'imPiawipof, imPifin, imPirrb, and imPiwip
        If x = 0 Then TableName = imPiawipof
        If x = 1 Then TableName = imPifin
        If x = 2 Then TableName = imPirrb
        If x = 3 Then TableName = imPiwip
        If adoPrimaryRS5.State = adStateOpen Then adoPrimaryRS5.Close
        adoPrimaryRS5.Open "Select Cost, Quantity from " & TableName & " where PartNumber ='" & m_part & "'", db, adOpenStatic, adLockOptimistic
        If adoPrimaryRS5.RecordCount > 0 Then
            GetCost = adoPrimaryRS5("Cost")
            GetQtyM = adoPrimaryRS5("Quantity")
            x = 16
        End If
    Next x
   
    If db.State = adStateOpen Then db.Close
    Set db = Nothing
    Set adoPrimaryRS5 = Nothing
   
    'Do whatever you want to here
0
 
Mike MillerSoftware EngineerAuthor Commented:
on this line...

rs.CursorLocation = adUseClient


I'm getting...(RTE 91)

Object variable or With block variable not set
0
 
gary_jCommented:
whenever you get "object varialbe or with block variable not set" try using set:

set rs.cursorlocation = adUseClient

I don't know if that's the answer or not, but the error tells me it's worth a try.

0
 
Mike MillerSoftware EngineerAuthor Commented:
gary_j

i got "Invalid use of property" on

.cursorlocation


0
 
gary_jCommented:
sorry, i didn't look closely

you need a

set rs = new adodb.recordset

in there:

 Dim rs as ADODB.recordset

 Dim m_Cost As String
 Dim m_QtyM As String
 Dim db As Connection

  Set db = New Connection
  db.Open "PROVIDER=MSDASQL;driver={SQL Server};server=USSRM-FP04;uid=;pwd=;database=PhysicalInventory;"
  db.CommandTimeout=300
                      <--------------------------   set rs = new adodb.recordset
rs.CursorLocation = adUseClient
rs.CursorType=adOpenStatic
rs.activeconnection=db
 
0
 
Johnny NewbeeCommented:
'gary_j was right about " set rs=new adodb.recordset "
'Here is the complete long nest one --

 
 Dim strSQL as string
 Dim rs as ADODB.recordset
 Dim m_Cost As String
 Dim m_QtyM As String
 Dim db As Connection

  Set db = New Connection
  db.Open "PROVIDER=MSDASQL;driver={SQL Server};server=USSRM-FP04;uid=;pwd=;database=PhysicalInventory;"
  db.CommandTimeout=300

  set rs = new adodb.recordset
 
rs.CursorLocation = adUseClient
rs.CursorType=adOpenStatic
rs.activeconnection=db

strSQL="Select imPiawipof.Cost, imPiawipof.Quantity from imPiawipof where ('" & m_Part & "' = imPiawipof.PartNumber)"

rs.source=strsql
rs.open

if rs.recordcount<=0 then
  rs.close
  strSQL="Select imPifin.Cost, imPifin.Quantity from imPifin where ('" & m_Part & "' = imPifin.PartNumber)"
  rs.source=strsql
  rs.open
   if rs.recordcount<=0 then
       rs.close
       strSQL="Select imPiawiproic.Cost, imPiawiproic.Quantity from imPiawiproic where ('" & m_Part & "' = imPiawiproic.PartNumber)"
       rs.source=strsql
       rs.open
           if rs.recordcount<=0 then
                rs.close
                strSQL="Select imPirrb.Cost, imPirrb.Quantity from imPirrb where ('" & m_Part & "' = imPirrb.PartNumber)"
                rs.source=strsql
                rs.open
                    if rs.recordcount<=0 then
                              rs.close
                              strSQL="Select imPiwip.Cost, imPiwip.Quantity from imPiwip where ('" & m_Part & "' = imPiwip.PartNumber)"
                              rs.source=strsql
                              rs.open
                    else
                              m_Cost = rs.fields(0)
                              m_QtyM = rs.fields(1)                  
                    end if
           else
                m_Cost = rs.fields(0)
                m_QtyM = rs.fields(1)                  
           end if
   else
       m_Cost = rs.fields(0)
       m_QtyM = rs.fields(1)                  
   end if
else
  m_Cost = rs.fields(0)
  m_QtyM = rs.fields(1)                  
end if                  

set rs=nothing
set db=nothing

'-------------------------------------------------------------------------------------
'use the m_Cost and m_QtyM for whatever you need to do with
'or like I said before, you could loop thru the table names and query that way, like rdwillett gave you a sample.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

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