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

x
?
Solved

How to populate a combo box with a SQL statement?

Posted on 2003-11-04
42
Medium Priority
?
602 Views
Last Modified: 2013-12-25
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.
0
Comment
Question by:bookspan
  • 24
  • 18
42 Comments
 
LVL 48

Expert Comment

by:Mikal613
ID: 9679767
Do you have a connection to the database?
0
 
LVL 48

Expert Comment

by:Mikal613
ID: 9679783
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
 

Author Comment

by:bookspan
ID: 9680026
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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 48

Expert Comment

by:Mikal613
ID: 9680038
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
 

Author Comment

by:bookspan
ID: 9680229
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
 
LVL 48

Expert Comment

by:Mikal613
ID: 9680297
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
 
LVL 48

Expert Comment

by:Mikal613
ID: 9680321
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
 

Author Comment

by:bookspan
ID: 9680626
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
 
LVL 48

Expert Comment

by:Mikal613
ID: 9680670
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
 

Author Comment

by:bookspan
ID: 9680774
Line 3 at RS just after Text &
0
 
LVL 48

Expert Comment

by:Mikal613
ID: 9680824
Case 45
        TxtPrice(1).Text = TxtPrice1(1).Text & RS("ChargeNumber") &""
   Case 46
          txtPrice(2).Text = txtPrice(2).Text & RS("ChargeNumber") &""
0
 

Author Comment

by:bookspan
ID: 9681095
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
 
LVL 48

Expert Comment

by:Mikal613
ID: 9681132
Post your Exact code for this piece ill fix it up
0
 

Author Comment

by:bookspan
ID: 9681325
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
 
LVL 48

Expert Comment

by:Mikal613
ID: 9681376
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
 

Author Comment

by:bookspan
ID: 9681837
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
 
LVL 48

Expert Comment

by:Mikal613
ID: 9681884
What do you mean when you select another item??
0
 
LVL 48

Expert Comment

by:Mikal613
ID: 9681897
ohh i see what you want its alot more than what you wanted ill do it hold on
0
 
LVL 48

Expert Comment

by:Mikal613
ID: 9681909
You wanna Up the Points its alot more code?
0
 

Author Comment

by:bookspan
ID: 9681969
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
 
LVL 48

Expert Comment

by:Mikal613
ID: 9681970
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
 
LVL 48

Expert Comment

by:Mikal613
ID: 9682018
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
 

Author Comment

by:bookspan
ID: 9682125
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
 
LVL 48

Expert Comment

by:Mikal613
ID: 9682141
Stop the program and Start it  again
0
 

Author Comment

by:bookspan
ID: 9682221
Same error after changing the if...then
stop and restart the program.
Is something needs to be setup in the properties???
0
 
LVL 48

Expert Comment

by:Mikal613
ID: 9682239
take out the line   .CursorLocation = adUseClient
0
 

Author Comment

by:bookspan
ID: 9682419
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
 

Author Comment

by:bookspan
ID: 9682496
Mikal613 Let's continue this tomorrow. I know we will fix it.
Thanks
Bookspan
0
 
LVL 48

Expert Comment

by:Mikal613
ID: 9682610
try Val(rs("ChargeNumber"))
0
 

Author Comment

by:bookspan
ID: 9687430
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
 
LVL 48

Expert Comment

by:Mikal613
ID: 9687467
"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
 

Author Comment

by:bookspan
ID: 9688102
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
 
LVL 48

Expert Comment

by:Mikal613
ID: 9688158
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
 

Author Comment

by:bookspan
ID: 9688464
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
 
LVL 48

Expert Comment

by:Mikal613
ID: 9688510
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
 

Author Comment

by:bookspan
ID: 9688685
We are getting there.
What about the spliting part in the where clause
& Arrid(0) etc....
0
 
LVL 48

Expert Comment

by:Mikal613
ID: 9688736
is only for the Click Event for the combo its 2 different queries
0
 
LVL 48

Expert Comment

by:Mikal613
ID: 9688742
spliting part in the where clause
& Arrid(0) etc....

is only for the Click Event for the combo

0
 

Author Comment

by:bookspan
ID: 9689001
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
 
LVL 48

Accepted Solution

by:
Mikal613 earned 500 total points
ID: 9689068
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
 

Author Comment

by:bookspan
ID: 9689209
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
 
LVL 48

Expert Comment

by:Mikal613
ID: 9689249
ny,US
0

Featured Post

Vote for the Most Valuable Expert

It’s time to recognize experts that go above and beyond with helpful solutions and engagement on site. Choose from the top experts in the Hall of Fame or on the right rail of your favorite topic page. Look for the blue “Nominate” button on their profile to vote.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Background What I'm presenting in this article is the result of 2 conditions in my work area: We have a SQL Server production environment but no development or test environment; andWe have an MS Access front end using tables in SQL Server but we a…
You can of course define an array to hold data that is of a particular type like an array of Strings to hold customer names or an array of Doubles to hold customer sales, but what do you do if you want to coordinate that data? This article describes…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
Suggested Courses
Course of the Month19 days, 1 hour left to enroll

834 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question