Solved

How to populate a combo box with a SQL statement?

Posted on 2003-11-04
42
590 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
 
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
What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

 
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 125 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

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Have you ever wanted to restrict the users input in a textbox to numbers, and while doing that make sure that they can't 'cheat' by pasting in non-numeric text? Of course you can do that with code you write yourself but it's tedious and error-prone …
I was working on a PowerPoint add-in the other day and a client asked me "can you implement a feature which processes a chart when it's pasted into a slide from another deck?". It got me wondering how to hook into built-in ribbon events in Office.
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

707 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now