Multiple SQL record display

**stealth black** · Dec 29th, 2005, 11:53 AM

I have created a program that allows users to enter a sales order number, and obtain tracking info from a database... I am using SQL to perform this.... However, some sales order numbers have multiple boxes and/or shipments, and I am not sure how to first get ALL the records, and second display each record discretely....

Can anyone assist me?

Thanks in advance..........

**Hack** · Dec 29th, 2005, 11:55 AM

I don't know how you are displaying them now, but it would appear, at least on the surface, that a listbox or listview might be appropriate in displaying the information for those orders that have multiple whatevers.

How are you displaying the data now?

**stealth black** · Dec 29th, 2005, 12:11 PM

About 75% of the orders are single shipments, so I got 'em going to various text boxes and labels...I've attached the form

I've set a variable = to the record count, but it's only bringing back 1 record, when I know there are more....

**Hack** · Dec 29th, 2005, 12:13 PM

Originally Posted by stealth black

About 75% of the orders are single shipments, so I got 'em going to various text boxes and labels...

I've set a variable = to the record cound, but it's only bringing back 1 record, when I know there are more....

It sounds like you are going to have to take that variable count off, and switch from textboxs to a control that can display multiple entries. Granted, 75% of the returns will be single entries, but you need the mutiple entry controls for the other 25%.

Between a grid, or a listbox or a listview or a whatever is a decision you need to make based on how you want your screen to look and your application requirements.

You have some real estate on that screen, so changing controls shouldn't be a big deal.

Nice screen BTW!

**stealth black** · Dec 29th, 2005, 12:16 PM

Ok, cool... Do you know the SQL command to bring back the multiple records?

**Hack** · Dec 29th, 2005, 12:18 PM

Originally Posted by stealth black

Ok, cool... Do you know the SQL command to bring back the multiple records?

You would use your garden variety SELECT statement. It will bring back everything it finds that matches your WHERE clause and/or any joins you have regardless of whether it is one record or one hundred and one records.

**stealth black** · Dec 29th, 2005, 12:58 PM

Ok, I'm an intermediate SQL user.... This is what I have at present:

VB Code:

ConnectDatabase
        
        strSQL = "SELECT * " & _
        "FROM LAWSON_BSACLIPTRK " & _
        "Where LAWSON_BSACLIPTRK.ORDER_NBR = " & txtSOEntry
 
    Set rsLAWSON_BSACLIPTRK = dbResourceLocatorDatabase.OpenRecordset(strSQL, dbOpenDynaset)

How does the SELECT you metioned look?

**Hack** · Dec 29th, 2005, 01:02 PM

Looks fine to me. Does it work is the important question.

Are you going to use a ListBox or Listview? From what little I know of your app, I would think a Listbox would involve the least amount of coding.

**stealth black** · Dec 29th, 2005, 01:23 PM

Well, the SQL works, but I'm only getting 1 record... As for the data, I'll take your adivce and use the list box....

**Hack** · Dec 29th, 2005, 01:27 PM

Originally Posted by stealth black

Well, the SQL works, but I'm only getting 1 record... As for the data, I'll take your adivce and use the list box....

Is that because there is only one record associated with the transaction? You said that would be the case 75% of the time.

**stealth black** · Dec 29th, 2005, 01:32 PM

Well, I have access to the database, so I purposely selected a sales order number with multiple shipments...

**Hack** · Dec 29th, 2005, 01:34 PM

Originally Posted by stealth black

Well, I have access to the database, so I purposely selected a sales order number with multiple shipments...

And each shipment is in individual record which all have the exact same txtSOEntry number?

**stealth black** · Dec 29th, 2005, 01:45 PM

Yes...

**Hack** · Dec 29th, 2005, 01:48 PM

Originally Posted by stealth black

Yes...

Well, then stealth, I am a confused Hack

In your database, is your shipment number stored as a string or a number?

**szlamany** · Dec 29th, 2005, 01:57 PM

Originally Posted by stealth black

Well, the SQL works, but I'm only getting 1 record... As for the data, I'll take your adivce and use the list box....

And what code are you using to determine that you are getting one row returned?

Only one row will be "presented" by ADO at a time...

**stealth black** · Dec 29th, 2005, 01:58 PM

String... I think what I need is a fetch command, I just keep getting a syntax error...

**szlamany** · Dec 29th, 2005, 02:01 PM

How are the controls being filled from that RS?

**stealth black** · Dec 29th, 2005, 02:06 PM

VB Code:

ConnectDatabase
        
        strSQL = "SELECT * FROM LAWSON_BSACLIPTRK " & _
        "Where LAWSON_BSACLIPTRK.ORDER_NBR = " & txtSOEntry
         
    Set rsLAWSON_BSACLIPTRK = dbResourceLocatorDatabase.OpenRecordset(strSQL, dbOpenDynaset)
    
    intShipmentCount = rsLAWSON_BSACLIPTRK.RecordCount
    Debug.Print intShipmentCount
    
        txtTrackingResult = Trim(rsLAWSON_BSACLIPTRK!TRACKING_NBR)
        lstTrackingResult.AddItem (Trim(rsLAWSON_BSACLIPTRK!TRACKING_NBR))
        lblCarrierResult = rsLAWSON_BSACLIPTRK!CAR_NAME
        txtPONumber = Trim(rsLAWSON_BSACLIPTRK!PUR_NBR)
  
'-------------------------------------------------------------------------------------------------
        'Date Conversion
        strYear = Mid(rsLAWSON_BSACLIPTRK!DATE_IN, 1, 4)
        strMonth = Mid(rsLAWSON_BSACLIPTRK!DATE_IN, 5, 2)
        strDay = Mid(rsLAWSON_BSACLIPTRK!DATE_IN, 7, 2)
        strDate = strMonth & "/" & strDay & "/" & strYear
        lblShipDateResult = strDate
'-------------------------------------------------------------------------------------------------
        'Time Conversion
        strHour = Mid(rsLAWSON_BSACLIPTRK!R_TIME, 1, 2)
        strMinute = Mid(rsLAWSON_BSACLIPTRK!R_TIME, 2, 2)
        
        Select Case strHour
        
        Case Is > 12
            strHour = strHour - 12
            strTimeOfDay = " p.m."
            
        Case Is < 12
        
            strTimeOfDay = " a.m."
         
        Case Is = 12
        
            strTimeOfDay = " p.m."
            
        End Select
        
        strTime = strHour & ":" & strMinute & strTimeOfDay
        lblTime = strTime
        
'-------------------------------------------------------------------------------------------------
 
        lblFreightChargeResult = rsLAWSON_BSACLIPTRK!FRT_CHG
        lblFreightChargeResult = "$" & lblFreightChargeResult / 100
        lblFreightChargeResult = Format(lblFreightChargeResult, "Currency")

**szlamany** · Dec 29th, 2005, 02:16 PM

Here's an example of moving through a recordset:

Code:

    objCmd.CommandText = "GetAppEle_P"
    objCmd.CommandType = adCmdStoredProc
    
    objCmd.ActiveConnection = gCn
    Set rsTemp = objCmd.Execute
    
    ReDim gEleRoleBits(rsTemp(0), 1 To 2)
    
    Do While rsTemp.EOF = False
        Select Case rsTemp(1)
            Case Is < 33
                gEleRoleBits(rsTemp(0), 1) = gEleRoleBits(rsTemp(0), 1) Or (2 ^ (rsTemp(1) - 1))
            Case Is < 65
                gEleRoleBits(rsTemp(0), 2) = gEleRoleBits(rsTemp(0), 2) Or (2 ^ (rsTemp(1) - 33))
        End Select
        rsTemp.MoveNext
    Loop

**stealth black** · Dec 29th, 2005, 02:31 PM

Ok... I am trying not to loop though.... There are 400,000+ records
I was looking for the SQL commands....

**szlamany** · Dec 29th, 2005, 02:38 PM

Originally Posted by stealth black

Ok... I am trying not to loop though.... There are 400,000+ records
I was looking for the SQL commands....

Either you or I are missing the point here.

The SQL command you execute will return two rows when you specify an ORDER NUMBER that has two records associated with it in the database.

Then on the VB side you need to move from one row to the next - that's the code I'm showing.

I would never return 400000 rows in a QUERY and then loop through them

**stealth black** · Dec 29th, 2005, 03:32 PM

Oh, I get it

I would only be looping through the records the SQL statement brings back.... What a dork...

Thanks!

Sorry for the confusion Hack

**Hack** · Dec 30th, 2005, 08:06 AM

Originally Posted by stealth black

Sorry for the confusion Hack

Actually, I was confused for a while.

I was happy to see szlamany step in. So, you got this all settled now?

**stealth black** · Dec 30th, 2005, 10:45 AM

For the most part... I'm returning the records now, but I want to allow the user to manually select the next record by pressing Next, and it's not doing anything...I get the first record, press next, and it says "No record"...

VB Code:

Private Sub cmdSOTrack_Click()
 
    'For Testing
    'txtSOEntry = 1666458 '<------ FedEx Test
    txtSOEntry = 1078290 '<------ UPS Test
    'txtSOEntry = 1680136 '<------ USPS Test
    'txtSOEntry = 1666439 '<------ ESTES Test
    
    If txtSOEntry = "" Or txtSOEntry = " " Then
        
        MsgBox "Please enter a PO or Sale Order Number to track", vbOKOnly, "Need A Number"
        txtSOEntry.BackColor = &HC0FFFF
        
    End If
    
    ConnectDatabase
        
        strSQL = "SELECT * FROM LAWSON_BSACLIPTRK " & _
        "Where LAWSON_BSACLIPTRK.ORDER_NBR = " & txtSOEntry
 
    Set rsLAWSON_BSACLIPTRK = dbResourceLocatorDatabase.OpenRecordset(strSQL, dbOpenDynaset)
    
        rsLAWSON_BSACLIPTRK.MoveLast
    
        intShipmentCount = rsLAWSON_BSACLIPTRK.RecordCount
        
    If intShipmentCount > 1 Then
        
        MsgBox "There are " & intShipmentCount &" shipments... Please use the 'Next Record' button to view other shipments", vbOKOnly, "Multiple Shipments"
            cmdNext.Enabled = True
    
    End If
            
        txtTrackingResult = Trim(rsLAWSON_BSACLIPTRK!TRACKING_NBR)
        lblCarrierResult = rsLAWSON_BSACLIPTRK!CAR_NAME
        txtPONumber = Trim(rsLAWSON_BSACLIPTRK!PUR_NBR)
  
'-------------------------------------------------------------------------------------------------
        'Date Conversion
        strYear = Mid(rsLAWSON_BSACLIPTRK!DATE_IN, 1, 4)
        strMonth = Mid(rsLAWSON_BSACLIPTRK!DATE_IN, 5, 2)
        strDay = Mid(rsLAWSON_BSACLIPTRK!DATE_IN, 7, 2)
        strDate = strMonth & "/" & strDay & "/" & strYear
        lblShipDateResult = strDate
'-------------------------------------------------------------------------------------------------
        'Time Conversion
        strHour = Mid(rsLAWSON_BSACLIPTRK!R_TIME, 1, 2)
        strMinute = Mid(rsLAWSON_BSACLIPTRK!R_TIME, 2, 2)
        
        Select Case strHour
        
        Case Is > 12
            strHour = strHour - 12
            strTimeOfDay = " p.m."
            
        Case Is < 12
        
            strTimeOfDay = " a.m."
         
        Case Is = 12
        
            strTimeOfDay = " p.m."
            
        End Select
        
        strTime = strHour & ":" & strMinute & strTimeOfDay
        lblTime = strTime
        
'-------------------------------------------------------------------------------------------------
 
        lblFreightChargeResult = rsLAWSON_BSACLIPTRK!FRT_CHG
        lblFreightChargeResult = "$" & lblFreightChargeResult / 100
        lblFreightChargeResult = Format(lblFreightChargeResult, "Currency")
 
    If InStr(lblCarrierResult, "UPS") <> 0 Then
        
        imgTrackingViewer.Picture = LoadPicture("C:\Documents and Settings\gstallin\My Documents\Visual Basic Programs\Resource Locator\UPS Package 1" & ".jpg")
        imgESTESPage.Enabled = False
        imgESTESPage.Visible = False And imgESTESPage.Visible = False
        imgUSPSPage.Enabled = False
        imgUSPSPage.Visible = False And imgUSPSPage.Visible = False
        imgFedExPage.Visible = False And imgFedExPage.Visible = False
        
    End If
    
    If InStr(lblCarrierResult, "USPS") <> 0 Then
    
        imgTrackingViewer.Picture = LoadPicture("C:\Documents and Settings\gstallin\My Documents\Visual Basic Programs\Resource Locator\USPS Truck" & ".gif")
        imgUPSPage.Enabled = False
        imgUPSPage.Visible = False And imgUSPSPage.Visible = False
        imgESTESPage.Enabled = False
        imgESTESPage.Visible = False And imgESTESPage.Visible = False
        imgFedExPage.Visible = False And imgFedExPage.Visible = False
        
    End If
    
    If InStr(lblCarrierResult, "ESTES") <> 0 Then
    
        imgTrackingViewer.Picture = LoadPicture("C:\Documents and Settings\gstallin\My Documents\Visual Basic Programs\Resource Locator\Estes" & ".gif")
        imgUPSPage.Enabled = False
        imgUPSPage.Visible = False And imgUPSPage.Visible = False
        imgUSPSPage.Enabled = False
        imgUSPSPage.Visible = False And imgUSPSPage.Visible = False
        imgFedExPage.Visible = False And imgFedExPage.Visible = False
        
    End If
    
    If InStr(lblCarrierResult, "FedEx") <> 0 Then
    
        imgTrackingViewer.Picture = LoadPicture("C:\Documents and Settings\gstallin\My Documents\Visual Basic Programs\Resource Locator\FedEx" & ".jpg")
        imgUPSPage.Enabled = False
        imgUPSPage.Visible = False And imgUPSPage.Visible = False
        imgUSPSPage.Enabled = False
        imgUSPSPage.Visible = False And imgUSPSPage.Visible = False
        imgESTESPage.Visible = False And imgESTESPage.Visible = False
        
    End If
    
EndSub:
 
    'CloseDatabase
    
End Sub
 
Private Sub cmdNext_Click()
    rsLAWSON_BSACLIPTRK.MoveNext
    
End Sub

Thread: Multiple SQL record display

Thread Tools

Display

Multiple SQL record display

Re: Multiple SQL record display

Re: Multiple SQL record display

Re: Multiple SQL record display

Re: Multiple SQL record display

Re: Multiple SQL record display

Re: Multiple SQL record display

Re: Multiple SQL record display

Re: Multiple SQL record display

Re: Multiple SQL record display

Re: Multiple SQL record display

Re: Multiple SQL record display

Re: Multiple SQL record display

Re: Multiple SQL record display

Re: Multiple SQL record display

Re: Multiple SQL record display

Re: Multiple SQL record display

Re: Multiple SQL record display

Re: Multiple SQL record display

Re: Multiple SQL record display

Re: Multiple SQL record display

Re: Multiple SQL record display

Re: Multiple SQL record display

Re: Multiple SQL record display

Posting Permissions