Member Avatar for yasirfarid

I want to show record list in Datacombo through coding , using SQL Server 2000,VB6.

Following are the coding.

********************************
Private Sub Form_Load()

Dim CNN As New ADODB.Connection
Dim RST As New ADODB.Recordset

Set CNN = New ADODB.Connection
CNN.Provider = "SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=Maintenance;Data Source=YASIR"
CNN.Open

Set RST = New ADODB.Recordset

RST.Open "SELECT * FROM MAINT", CNN, adOpenKeyset, adLockOptimistic

Set DataCombo1.RowSource = RST
DataCombo1.ListField = Mill
Text1 = RST!Mill

End Sub

********************************
Text1(textbox) is showing record it means dataconnection is established. but no record showing in Datacombo. Please help .one thing more that if I use Ms Access Database then record is showing Datacombo,(just chaning on cnn.provider....)

Thanks
Yasir Farid

  • 4 Contributors
  • 9 Replies
  • 385 Views
  • 4 Years Discussion Span
  • Latest Post Latest Post by Rajeev3010

Dani AI

Generated

Good troubleshooting so far — the problem and fix in this thread are common, so here’s a short, focused explanation and a couple of practical alternatives to avoid the same trap later.

The root cause

  • Binding a DataCombo to an ADO Recordset coming from SQLOLEDB will often fail when the Recordset uses a server-side cursor. The DataCombo control expects a recordset it can page and enumerate locally; a client-side cursor satisfies that. Thanks to for the pointer that setting the cursor location to the client fixes the symptom, and to for confirming it solved the issue.

Quick checklist (things to verify before binding)

  • Make sure the column name (here, Mill) actually exists and is passed as a string when setting the ListField.
  • Set the ADO cursor location to client BEFORE opening the recordset; use a simple read-only/static cursor for lists.
  • Confirm your connection string and credentials (as suggested) and that the VB6 project references the Microsoft ADO library you expect.
  • If the drop-down shows items but the edit portion is blank at load, explicitly select an item (e.g., set the ListIndex) — some DataCombo implementations populate visible list only when the control is expanded.

Alternate approach (works around binding quirks)
If binding keeps acting up, populate a standard Combo/Datacombo manually from the recordset. That avoids binding semantics and is robust for simple lists:

' Execute a field-only query, walk the results and AddItem for each value
Dim rsLocal As ADODB.Recordset
Set rsLocal = conn.Execute("SELECT Mill FROM MAINT")
Combo1.Clear
Do Until rsLocal.EOF
    Dim sVal As String
    sVal = ""
    If Not IsNull(rsLocal.Fields("Mill").Value) Then sVal = CStr(rsLocal.Fields("Mill").Value)
    Combo1.AddItem sVal
    rsLocal.MoveNext
Loop
rsLocal.Close
Set rsLocal = Nothing

Final notes
For production, prefer a client cursor + static/read-only cursor type for lists (lower overhead), close/dispose ADO objects, and make sure the DataCombo control is registered on the target machine. If migrating away from VB6 is an option, modern UI frameworks avoid these legacy cursor-binding issues.

Recommended Answers

All 9 Replies

Member Avatar for AV Manoharan

Please check up whether after placing the period(dot) after DataCombo1 the methods RowSourse and ListFields are poping up.
If they are, please get back to me.

Yor connection strings are having some problem that I will tell you.

Member Avatar for QVeen72

Hi Yasir,

Check This :

Set DataCombo1.RowSource = RST DataCombo1.ListField= "Mill"

Wrap Mill with double Quotes ( " )

Regards
Veena

Member Avatar for yasirfarid

Please check up whether after placing the period(dot) after DataCombo1 the methods RowSourse and ListFields are poping up.
If they are, please get back to me.

Yor connection strings are having some problem that I will tell you.

yes after placing the period (dof) Rowsource and Listfields are popping up.

Member Avatar for yasirfarid

Veena
Yes I have done, but still not working...

Yasir

Hi Yasir,

Check This :

Set DataCombo1.RowSource = RST DataCombo1.ListField= "Mill"

Wrap Mill with double Quotes ( " )

Regards
Veena

Member Avatar for QVeen72

Hi Yasir,
Open Recordset this way :

Set RST = New ADODB.Recordset
RST.CursorLocation = adUseClient
RST.Open "SELECT * FROM MAINT", CNN, adOpenKeyset, adLockOptimistic


After filling, it dosent show on the Combo's Text, To Check Click on then DropDown Arrow of the Combo, It will be filled,

REgards
Veena

Member Avatar for QVeen72

Hi,

If still that did not work then
Open recordset this way :(Static / Read Only)


RST.Open "SELECT * FROM MAINT", CNN, adOpenStatic, adLockReadOnly

Regards
Veena

Member Avatar for AV Manoharan

I want to show record list in Datacombo through coding , using SQL Server 2000,VB6.

Following are the coding.

********************************
Private Sub Form_Load()

Dim CNN As New ADODB.Connection
Dim RST As New ADODB.Recordset

Set CNN = New ADODB.Connection

'Please avoid the above underlined One

CNN.Provider = "SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=Maintenance;Data Source=YASIR"

'Change all those underlined above as below

CNN.ConnectionString="Provider=SQLOLEDB.1;UID=sa;PWD=sa;Data Sourse=YASIR;Initial Catalog=Maintenance"

'If the UserID & Password are not 'sa' please change it accordingly.

CNN.Open

Set RST = New ADODB.Recordset

'Instead of above line please give as below

RST.ActiveConnection = CNN

If RST.State = adStateOpen then
RST.Close
End If

RST.Open "SELECT * FROM MAINT", CNN, adOpenKeyset, adLockOptimistic

'See that the table above underlined exist in the database Maintenance.

Set DataCombo1.RowSource = RST
DataCombo1.ListField = Mill
Text1 = RST!Mill

If RST.State = adStateOpen then
RST.Close
End If

End Sub

********************************
Text1(textbox) is showing record it means dataconnection is established. but no record showing in Datacombo. Please help .one thing more that if I use Ms Access Database then record is showing Datacombo,(just chaning on cnn.provider....)

Thanks
Yasir Farid

Yasir, I have underlined the code that you have to change or replace
then run it and get back to me.

AV Manoharan

Member Avatar for yasirfarid

Yes Veena Thank you,

After applying RST.CursorLocation = adUseClient, its shows records on datacombo..Thanks again.

meet you soon with other trouble.;)

Yasir

Member Avatar for Rajeev3010

Veena, you your aptitude is to be appreciated, even i had the same problem. from access, data coming in db combo, but not from sql...how can we guess, there is something wrong in the code, (in the way, data is picked up from sql...)

thanks a lot...

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.