Fill a combobox from SQL Server

egovernment
egovernment used Ask the Experts™
on
I use this code is working only with Acceass database not SQL Server database what the reason ?

    vr_RdSet.Open "SELECT [XXX] FROM [YYY] WHERE [XXX] IS NOT NULL " & _
    "ORDER BY [XXX]", vr_Conn, adOpenStatic, adLockReadOnly
    If Not vr_RdSet.EOF Then
        With Combobox278
            Set .Recordset = vr_RdSet
            .Requery
        End With
    End If
    vr_RdSet.Close
    Set vr_RdSet = Nothing

Open in new window


I'm using unbound form + Vba + Ado + Sql server
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
I am not sure you have posted all your code, but below an example on how to do this. You might change the ConnectionString accordingly.

Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim sql As String
 
  Set cn = New ADODB.Connection
  cn.ConnectionString = "Provider=SQLNCLI;Server=USJILLMA\SQLEXPRESS;" & _
    "Database=faa;Trusted_Connection=yes;"
  cn.Open
 
  sql ="SELECT [XXX] FROM [YYY] WHERE [XXX] IS NOT NULL " & _
    "ORDER BY [XXX]"


  Set rs = New ADODB.Recordset
  Set rs.ActiveConnection = cn
  rs.Open sql2
 
  Set Combobox278.Recordsource = rs

HTH,
Daniel

Author

Commented:
Set Combobox278.Recordsource = rs

This is give me an error because the recordsource is not an property to combobox

Author

Commented:
This is urgent
Success in ‘20 With a Profitable Pricing Strategy

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Oops that must be:

Set Combobox278.Rowsource = rs

HTH,
Daniel

With Combobox278
        .Clear
        Do
            .AddItem vr_RdSet![XXX]
            vr_RdSet.MoveNext
        Loop Until vr_RdSet.EOF
    End With



refer
http://www.fontstuff.com/vba/vbatut10.htm
hi

try this code

Code Snippet
    Private Sub ReFillCombo()
        Me.ComboBox1.Items.Clear() ' Clear the items of the combobox
        Dim constr As String = "Your Connection String"
        Dim conn As New SqlConnection(constr)
        Dim da As New SqlDataAdapter("Your select command", conn)
        Dim dt As New DataTable
        da.Fill(dt)
        Me.ComboBox1.DisplayMember = "The field which  you want to display"
        Me.ComboBox1.ValueMember = "The value member (mostly used for ID fields)"
        Me.ComboBox1.DataSource = dt
    End Sub

by
kumaresan
hi

try this codes also

Dim MyConnObj As New ADODB.Connection 'ADODB Connection Object
Dim myRecSet As New ADODB.Recordset 'Recordset Object
Dim sqlStr As String ' String variable to store sql command
MyConnObj.Open _
"Provider = sqloledb;" & _
"Data Source="";" & _
"Initial Catalog=tebrauteguh;" & _
"User ID=sa;" & _
"Password=sa;"
sqlStr = "select * from tblMLeave "
myRecSet.Open sqlStr, MyConnObj, adOpenKeyset
MsgBox "Total Number of records = " & myRecSet.RecordCount
'Dim i As Integer 'variable to keep count
'i = 1
'Print "#"; Tab; "LCode"; Tab; "LName";
'Print ""
With myRecSet
While Not myRecSet.EOF ' Loop until endd fo file is reached
ReaApp.Items.Add (tblMLeave.Item("LName"))
.MoveNext
Wend
End With
MyConnObj.Close

by
kumaresan

Author

Commented:
Still my question is not answer that why the my above code only working with access not sql server ?
Which piece of code are you trying to get working?

Is your code getting you a recordset when querying SQL?

    vr_RdSet.Open "SELECT [XXX] FROM [YYY] WHERE [XXX] IS NOT NULL " & _
    "ORDER BY [XXX]", vr_Conn, adOpenStatic, adLockReadOnly

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial