How to populate a combo box with a SQL statement?

Hi,
I have a form with one Combo Box and a few TextBoxes.
How can I populate the combo with ChargeNumbers, and based on the ChargeNumber the user selects the corresponding values of that ChargeNumber should populate the TextBoxes (TxtCdType, TxtChgDrescr, TxtInsCd, TxtGlKey, TxtPriceDate, TxtPrice1, ect...)
I am using VB6 with ADO and SQL Server 2000.
Here is the Sql Statement:
SELECT CAST(CAST(ChgNum AS NUMERIC)AS VARCHAR)+' '+ ChgDescr AS ChargeNumber, CdType,ChgDescr,InsCd,GlKey,PriceDate,Price1,Price2,Price3 from MASTER_LIST_CDM_PRICING_DESCR_CODES
Your help is greatly appreciated.
Bookspan.
bookspanAsked:
Who is Participating?
 
Mikal613Commented:
Replace the Combo1_Click With this

Private Sub Combo1_Click()
Dim ArrID As Variant
Dim RS As adodb.Recordset
Set RS = New adodb.Recordset

ArrID = Split(Combo1.Text, ":")


RS.Open "select chgnum ,CdType,ChgDescr,InsCd,GlKey,PriceDate,Price1,Price2,Price3  from MASTER_LIST_CDM_PRICING_DESCR_CODES WHERE ChgNum = " & ArrID(0), cn, adOpenForwardOnly, adLockReadOnly, adCmdText

If Not RS.EOF Then
        txtCdType(0).Text =  RS("CdType")
        TxtPrice1(1).Text =  RS("Price1")
        txtPrice2(2).Text =  RS("Price2")
        txtDate(3).Text =  RS("PriceDate")
        txtInsCd(4).Text =  RS("InsCd")
        txtGlKey(5).Text =  RS("GlKey")

End If

RS.Close
Set RS = Nothing

End Sub
0
 
Mikal613Commented:
Do you have a connection to the database?
0
 
Mikal613Commented:
dim RS as Adodb.Recordset
Set rs = new Adodb.REcordset


SQL = "SELECT CAST(CAST(ChgNum AS NUMERIC)AS VARCHAR)+' '+ ChgDescr AS ChargeNumber, CdType,ChgDescr,InsCd,GlKey,PriceDate,Price1,Price2,Price3 from MASTER_LIST_CDM_PRICING_DESCR_CODES"

rs.open SQL,YourConn,Adopenforwardonly,Adopenlock,ADcmdtext

Do until rs.eof
    Select Case rs("ChargeNumber")
       CAse
       Case
                 combo1.additem rs("Wahtever")      
    End Select

     RS.Movenext
Loop

RS.close
Set res = nothing
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
bookspanAuthor Commented:
Yes I have a connection to the DB.
I do not understand the use of the case statement. Can you explain? I am new to VB.
The combo box should be only populate with the ChargeNumber.
Base on the ChargeNumber that the user selects, the other textboxes should be populated with their corresponding values.
0
 
Mikal613Commented:
you said theres a different case for the CargeNumbers so this evaluates each case of the charge number

 Select Case rs("ChargeNumber")
       CAse 12
       Case 13
                 combo1.additem rs("Wahtever")      
    End Select
0
 
bookspanAuthor Commented:
I did not say anything about the case. I don't even understand the concept.
Let's start from here.
Below is how I implement your code. I think we are have way to solve the puzzle.

Private Sub Form_Load()
       
    With cn
        .CursorLocation = adUseClient
        .ConnectionString = "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=Testing;Data Source=fhfinance"
        .Open
    End With
       
RS.Open "select cast(cast(chgnum as numeric)as varchar)+' '+ chgdescr AS ChargeNumber from MASTER_LIST_CDM_PRICING_DESCR_CODES", cn, adOpenStatic, adLockPessimistic
 Do Until RS.EOF
 Combo1.AddItem RS("chargeNumber")
  RS.MoveNext
 Loop
 Set RS = Nothing

Note that this code is not working properly. It returns a "Binding Collection Error" Binding not updatable, Bound Property Name: Text,FieldName: ChargeNumber

Please take a look at it and help me solve it. I am new to VB.
0
 
Mikal613Commented:
Private Sub Form_Load()
       
    With cn
        .CursorLocation = adUseClient
        .ConnectionString = "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=Testing;Data Source=fhfinance"
        .Open
    End With
       
RS.Open "select cast(cast(chgnum as numeric)as varchar)+' '+ chgdescr AS ChargeNumber from MASTER_LIST_CDM_PRICING_DESCR_CODES", cn, adOpenForwardOnly, adLockReadOnly, adCmdText
 Do Until RS.EOF
 Combo1.AddItem RS("chargeNumber") & ""
  RS.MoveNext
 Loop
RS.Close
 Set RS = Nothing
0
 
Mikal613Commented:
and based on the ChargeNumber the user selects the corresponding values of that ChargeNumber should populate the TextBoxes (TxtCdType, TxtChgDrescr, TxtInsCd, TxtGlKey, TxtPriceDate, TxtPrice1, ect...)

Acording to this

Select  Case rs("ChargeNumber")
   Case 23
          txtcdtype.text = txtcdtype.text & rs("ChargeNumber")
    Case 45
        TxtPrice1.text = TxtPrice1.text & rs("ChargeNumber")
   'And So Onnnn
End Select
0
 
bookspanAuthor Commented:
It is not working.
It returns a "Method or Data member not found" error.
the error occurs at the RS("chargeNumber") that immediatly followed TxtCdType(0).Text &

Here is a portion of the code

Do Until RS.EOF
 Combo1.AddItem RS("ChargeNumber") & " "
  RS.MoveNext
 Loop
 Select Case RS("ChargeNumber")
   Case 23
          txtCdType.Text = txtCdType(0).Text & RS("ChargeNumber")
    Case 45
        TxtPrice1.Text = TxtPrice1(1).Text & RS("ChargeNumber")
   Case 46
          txtPrice2.Text = txtPrice(2).Text & RS("ChargeNumber")
    Case 47
        txtDate.Text = txtDate(3).Text & RS("ChargeNumber")
    Case 48
          txtInsCd.Text = txtInsCd(4).Text & RS("ChargeNumber")
    Case 49
        txtGlKey.Text = txtGlKey(5).Text & RS("ChargeNumber")

End Select
0
 
Mikal613Commented:
Select Case RS("ChargeNumber")
   Case 23
          txtCdType.Text = txtCdType(0).Text & RS("ChargeNumber") &""
    Case 45
        TxtPrice1.Text = TxtPrice1(1).Text & RS("ChargeNumber") &""
   Case 46
          txtPrice2.Text = txtPrice(2).Text & RS("ChargeNumber") &""
    Case 47
        txtDate.Text = txtDate(3).Text & RS("ChargeNumber") &""
    Case 48
          txtInsCd.Text = txtInsCd(4).Text & RS("ChargeNumber") &""
    Case 49
        txtGlKey.Text = txtGlKey(5).Text & RS("ChargeNumber") &""

End Select
What Line exactly does it crash??
0
 
bookspanAuthor Commented:
Line 3 at RS just after Text &
0
 
Mikal613Commented:
Case 45
        TxtPrice(1).Text = TxtPrice1(1).Text & RS("ChargeNumber") &""
   Case 46
          txtPrice(2).Text = txtPrice(2).Text & RS("ChargeNumber") &""
0
 
bookspanAuthor Commented:
It still not working.
First I got Binding Collection Error.
 Field not  updatable, Bound PropertyName:Text,FieldName:ChargeNumber
Then after you click on OK you have a VB run-time error'3021' Either BOF or EOF is true, or the current record has been deleted. Requested Operation requires a current record
and when you click on the debug button the Select Case RS("ChargeNumber") is highlited
0
 
Mikal613Commented:
Post your Exact code for this piece ill fix it up
0
 
bookspanAuthor Commented:
Bellow is the Code:

Option Explicit
Dim cn As New Adodb.Connection
Dim RS As New Adodb.Recordset

Private Sub Form_Load()
       
    With cn
        .CursorLocation = adUseClient
        .ConnectionString = "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=Testing;Data Source=fhfinance"
        .Open
    End With
       
RS.Open "select cast(cast(chgnum as numeric)as varchar)+' '+ chgdescr AS ChargeNumber from MASTER_LIST_CDM_PRICING_DESCR_CODES", cn, adOpenStatic, adLockPessimistic
 Do Until RS.EOF
 Combo1.AddItem RS("ChargeNumber") & " "
  RS.MoveNext
 Loop
 Select Case RS("ChargeNumber")
   Case 23
          txtCdType(0).Text = txtCdType(0).Text & RS("ChargeNumber")
    Case 45
        TxtPrice1(1).Text = TxtPrice1(1).Text & RS("ChargeNumber")
   Case 46
          txtPrice2(2).Text = txtPrice2(2).Text & RS("ChargeNumber")
    Case 47
        txtDate(3).Text = txtDate(3).Text & RS("ChargeNumber")
    Case 48
          txtInsCd(4).Text = txtInsCd(4).Text & RS("ChargeNumber")
    Case 49
        txtGlKey(5).Text = txtGlKey(5).Text & RS("ChargeNumber")

End Select

 Set RS = Nothing
     
End Sub
0
 
Mikal613Commented:
Option Explicit
Dim cn As New Adodb.Connection
Dim RS As New Adodb.Recordset

Private Sub Form_Load()
       
    With cn
        .CursorLocation = adUseClient
        .ConnectionString = "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=Testing;Data Source=fhfinance"
        .Open
    End With
       
RS.Open "select cast(cast(chgnum as numeric)as varchar)+' '+ chgdescr AS ChargeNumber from MASTER_LIST_CDM_PRICING_DESCR_CODES", cn, adOpenStatic, adLockPessimistic

 Do Until RS.EOF

       Select Case RS("ChargeNumber")
           Case 23
               txtCdType(0).Text = txtCdType(0).Text & RS("ChargeNumber")
          Case 45
              TxtPrice1(1).Text = TxtPrice1(1).Text & RS("ChargeNumber")
           Case 46
              txtPrice2(2).Text = txtPrice2(2).Text & RS("ChargeNumber")
           Case 47
               txtDate(3).Text = txtDate(3).Text & RS("ChargeNumber")
           Case 48
                 txtInsCd(4).Text = txtInsCd(4).Text & RS("ChargeNumber")
          Case 49
              txtGlKey(5).Text = txtGlKey(5).Text & RS("ChargeNumber")
     End Select

  Combo1.AddItem RS("ChargeNumber") & " "

  RS.MoveNext

 Loop

RS.Close
 Set RS = Nothing
     
End Sub
0
 
bookspanAuthor Commented:
The Binding Collection error still happening.
The combo box is populated with it values, but when you select another value the textboxes are not updating.
0
 
Mikal613Commented:
What do you mean when you select another item??
0
 
Mikal613Commented:
ohh i see what you want its alot more than what you wanted ill do it hold on
0
 
Mikal613Commented:
You wanna Up the Points its alot more code?
0
 
bookspanAuthor Commented:
I meant the chargeNumber.
The combo box got populated with hundreds of ChargeNumbers. Based on the one selected the textboxes should update. for example the combo box has chargenumber from 1 to 100
when you select 1, the cdtype box, price1 box ect should updated and display the corresponding value of chargenumber 1.
when you select 2, you should have the values for chargenumber 2 and so on.
0
 
Mikal613Commented:
Private Sub Form_Load()
       
    With cn
        .CursorLocation = adUseClient
        .ConnectionString = "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=Testing;Data Source=fhfinance"
        .Open
    End With
       
RS.Open "select cast(cast(chgnum as numeric)as varchar)+' '+ chgdescr AS ChargeNumber from MASTER_LIST_CDM_PRICING_DESCR_CODES", cn, adOpenStatic, adLockPessimistic

 Do Until RS.EOF
  Combo1.AddItem RS("ChargeNumber") & " "

  RS.MoveNext

 Loop

RS.Close
 Set RS = Nothing
     
End Sub
Private Sub Combo1_Click()

Dim RS as Adodb.Recordset
Set rs = new adodb.RecordSet

RS.open RS.Open "select cast(cast(chgnum as numeric)as varchar)+' '+ chgdescr AS ChargeNumber,CdType,ChgDescr,InsCd,GlKey,PriceDate,Price1,Price2,Price3  from MASTER_LIST_CDM_PRICING_DESCR_CODES WHERE ChargeNumber = " & Combo1.text , cn, adOpenStatic, adLockPessimistic

if not RS.EOF then
        txtCdType(0).Text = txtCdType(0).Text & RS("CdType")
        TxtPrice1(1).Text = TxtPrice1(1).Text & RS("Price1")
        txtPrice2(2).Text = txtPrice2(2).Text & RS("Price2")
        txtDate(3).Text = txtDate(3).Text & RS("PriceDate")
        txtInsCd(4).Text = txtInsCd(4).Text & RS("InsCd")
        txtGlKey(5).Text = txtGlKey(5).Text & RS("GlKey")

End if

RS.Close
Set rs = Nothing

End Sub
0
 
Mikal613Commented:
Sorry Replace with this

if not RS.EOF then
        txtCdType(0).Text = RS("CdType")
        TxtPrice1(1).Text =  RS("Price1")
        txtPrice2(2).Text =  RS("Price2")
        txtDate(3).Text =  RS("PriceDate")
        txtInsCd(4).Text =  RS("InsCd")
        txtGlKey(5).Text =  RS("GlKey")

End if
0
 
bookspanAuthor Commented:
Now I am having a VB runtime error '424' Object required.
When you click on the Debug Button, line 3 of the code
.CursorLocation = adUseClient is highlited.
0
 
Mikal613Commented:
Stop the program and Start it  again
0
 
bookspanAuthor Commented:
Same error after changing the if...then
stop and restart the program.
Is something needs to be setup in the properties???
0
 
Mikal613Commented:
take out the line   .CursorLocation = adUseClient
0
 
bookspanAuthor Commented:
The General declaration was missing in your last code.

Option Explicit
Dim cn As New Adodb.Connection
Dim RS As New Adodb.Recordset

I add it to the code.
but still having the binding error.
I think I know why but I don't know how to fix it.
Here is why. Reminber the ChargeNumber is concatenated with ChgNum + ChgDescr, just to let the user know what he is selecting from the combo. Once selected VB is confused because at the Database level the other column CdType. Price1, ect.... get update with the value of ChgNum.
How can we trim the ChargeNumber before update???
0
 
bookspanAuthor Commented:
Mikal613 Let's continue this tomorrow. I know we will fix it.
Thanks
Bookspan
0
 
Mikal613Commented:
try Val(rs("ChargeNumber"))
0
 
bookspanAuthor Commented:
Hello Mikal613,
Val(rs("ChargeNumber")) did not solve it.
In the where clause of this statement,
RS.Open "select cast(cast(chgnum as numeric)as varchar)+' '+ chgdescr AS ChargeNumber,CdType,ChgDescr,InsCd,GlKey,PriceDate,Price1,Price2,Price3  from MASTER_LIST_CDM_PRICING_DESCR_CODES WHERE ChargeNumber = " & Combo1.text , cn, adOpenStatic, adLockPessimistic
the value " & Combo1.text is passing to ChargeNumber is a concatenated value of chgnum and chgdescr
How can we cleanup chargenumber so that it hold only the chgnum ?
0
 
Mikal613Commented:
"select cast(cast(chgnum as numeric)as varchar) as chgdescr , ChargeNumber,CdType,ChgDescr,InsCd,GlKey,PriceDate,Price1,Price2,Price3  from MASTER_LIST_CDM_PRICING_DESCR_CODES WHERE ChargeNumber = " & Combo1.text , cn, adOpenStatic, adLockPessimistic
0
 
bookspanAuthor Commented:
I got your point but it's not going to solve the problem because the user need to see both the chgnum and chgdescr in the combo drop down.
Instead of concatenate chgnum and chgdescr, is it possible in VB to display two values in the combo so that at run time you can just pass one??
0
 
Mikal613Commented:
yes

Use the Previous Sql Statement and do this
 Combo1.AddItem RS("ChargeNumber") & ":" & rs("chgdescr")

and use this Sub

Private Sub Combo1_Click()
dim ArrID as Variant
Dim RS as Adodb.Recordset
Set rs = new adodb.RecordSet

arrid = Split(Combo1.text,":")


RS.open RS.Open "select cast(cast(chgnum as numeric)as varchar)+' '+ chgdescr AS ChargeNumber,CdType,ChgDescr,InsCd,GlKey,PriceDate,Price1,Price2,Price3  from MASTER_LIST_CDM_PRICING_DESCR_CODES WHERE ChargeNumber = " & arrid(0), cn, adOpenStatic, adLockPessimistic

if not RS.EOF then
        txtCdType(0).Text = txtCdType(0).Text & RS("CdType")
        TxtPrice1(1).Text = TxtPrice1(1).Text & RS("Price1")
        txtPrice2(2).Text = txtPrice2(2).Text & RS("Price2")
        txtDate(3).Text = txtDate(3).Text & RS("PriceDate")
        txtInsCd(4).Text = txtInsCd(4).Text & RS("InsCd")
        txtGlKey(5).Text = txtGlKey(5).Text & RS("GlKey")

End if

RS.Close
Set rs = Nothing

End Sub




0
 
bookspanAuthor Commented:
It returns a VB run-time error '3265'
Item cannot be found in the collection corresponding to the requested name or ordinal.
when click on debug
Combo1.AddItem RS("ChargeNumber") & ":" & RS("chgdescr") is highlited

Does this sql statement need to be change??
RS.Open "select cast(cast(chgnum as numeric)as varchar)+' '+ chgdescr AS ChargeNumber from MASTER_LIST_CDM_PRICING_DESCR_CODES", cn, adOpenStatic, adLockPessimistic
0
 
Mikal613Commented:
yes

select cast(cast(chgnum as numeric)as varchar) as chgdescr , ChargeNumber,CdType,ChgDescr,InsCd,GlKey,PriceDate,Price1,Price2,Price3  from MASTER_LIST_CDM_PRICING_DESCR_CODES WHERE ChargeNumber = " & Combo1.text , cn, adOpenForwardOnly, adLockReadOnly, adCmdText

0
 
bookspanAuthor Commented:
We are getting there.
What about the spliting part in the where clause
& Arrid(0) etc....
0
 
Mikal613Commented:
is only for the Click Event for the combo its 2 different queries
0
 
Mikal613Commented:
spliting part in the where clause
& Arrid(0) etc....

is only for the Click Event for the combo

0
 
bookspanAuthor Commented:
Here is the clean code after the Tornedo. This one does not return any error message, but what it does to the form is this:
At run time when the first values of the textboxes are loaded and you select another item from the combo drop down, the new value is append to the first one. this happening to every textbox. For example
when 1st item in the combo is 101 and txtCdType is 24, then txtCdType is 24
when you select the 2nd item in the combo e.g. 102 and txtCdType is 36, it appends the 36 to
the first value in such a way that you have 2436 and so on.
Like whenever you select a new item the previous result does not refresh. Something like that.

Here is the Code. Take a look at it.

Option Explicit
Dim cn As New adodb.Connection
Dim RS As New adodb.Recordset

Private Sub Command2_Click()
End
End Sub

Private Sub Form_Load()
       
    With cn
        .CursorLocation = adUseClient
        .ConnectionString = "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=Testing;Data Source=fhfinance"
        .Open
    End With
       
RS.Open "select chgnum ,chgdescr from MASTER_LIST_CDM_PRICING_DESCR_CODES", cn, adOpenStatic, adLockPessimistic

 Do Until RS.EOF
  Combo1.AddItem RS("ChgNum") & ":" & RS("chgdescr")

  RS.MoveNext

 Loop

RS.Close
 Set RS = Nothing
     
End Sub
Private Sub Combo1_Click()
Dim ArrID As Variant
Dim RS As adodb.Recordset
Set RS = New adodb.Recordset

ArrID = Split(Combo1.Text, ":")


RS.Open "select chgnum ,CdType,ChgDescr,InsCd,GlKey,PriceDate,Price1,Price2,Price3  from MASTER_LIST_CDM_PRICING_DESCR_CODES WHERE ChgNum = " & ArrID(0), cn, adOpenForwardOnly, adLockReadOnly, adCmdText

If Not RS.EOF Then
        txtCdType(0).Text = txtCdType(0).Text & RS("CdType")
        TxtPrice1(1).Text = TxtPrice1(1).Text & RS("Price1")
        txtPrice2(2).Text = txtPrice2(2).Text & RS("Price2")
        txtDate(3).Text = txtDate(3).Text & RS("PriceDate")
        txtInsCd(4).Text = txtInsCd(4).Text & RS("InsCd")
        txtGlKey(5).Text = txtGlKey(5).Text & RS("GlKey")

End If

RS.Close
Set RS = Nothing

End Sub

0
 
bookspanAuthor Commented:
Very Good, You deserve a raise. I am going to increase the point to 125.
Thanks Mikal613.
P.S. Which State/Country are you in?
0
 
Mikal613Commented:
ny,US
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.

All Courses

From novice to tech pro — start learning today.