Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 366
  • 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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
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

Featured Post

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

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