Selecting data from sql database and importing into combobox

[CasperG]

Help!!!

I cannot get my combo box to populate using a select statement from mysql database.

Please see below for coding

CN = New ADODB.Connection

        'Connecion to database
        CN.Open("driver={mysql odbc 5.1 driver};server=localhost;database=v_control;user=root;password=;option=3")

        RS = New ADODB.Recordset

        RS.Open(SQLdata, CN, ADODB.CursorTypeEnum.adOpenUnspecified, ADODB.LockTypeEnum.adLockPessimistic)

If DBconnect() = True Then
            SQLSearch("select * from engineer (EngineerNumber")

            RS.MoveFirst()
            While RS.EOF = False
                Do
                    cmbEngNum.Items.Add("EngineerNumber")
                    RS.MoveNext()
                Loop
            End While
        End If

but this reports the following error

[MySQL][ODBC 5.1 Driver][mysqld-5.1.36-community-log]You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '(EngineerNumber' at line 1

Any ideas would be most appreciated, im quite new to this as you can probly tell.

thanks

[ndeniche]

Actually, your error is literally what the compiler displays. You _do_ have an error in your SQL syntax. It should be SELECT * FROM engineer .

I don't really know what the ADODB class for row storage is, so i'll just call it RowSet , so, supposing you have a Name column for the engineer's name, when you add the combobox items, it should be something like:

Dim queryStr As String = "SELECT * FROM engineer"
RowSet = SQLSEARCH(queryStr)
Dim count As Integer = 0
While Not RS.EOF Loop
   cmbEngNum.Add(RowSet.Rows[count].Columns["Name"].Value.ToString())
End While

Remember the results of a query are returned in a table format, so you must have an object receiving that value, and you must read through this object to assign the desired value to the combobox items.

[CasperG]

Hi thanks for your help, managed to get this working, used the following code

If DBconnect() = True Then
            
            SQLSearch("Select * FROM engineer")
            RS.MoveFirst()
            While RS.EOF = False
                cmbEngNum.Items.Add(RS.Fields.Item("EngineerNumber").Value)
                RS.MoveNext()
            End While
        End If

[ndeniche]

Great! Always a pleasure... (Mark it as solved so it can be useful to others)

[bvvvb]

thank yuo
http://www.7ozn.com/vb

[dhiane_1112]

how about in VB6.0, how can i populate items in my combobox using records from my mssql table? this is my code: but it says eithe BOF nd EOF is true; or the currentrecord has been deleted.

RS.Open "select Payment_Desc from tblPaymentdetails where Stud_No='" & txtSearchStudent.Text & "'", cn, 3, 2

RS.MoveLast
With Me.cboPaymentDetails
.Clear
Do
.AddItem RS!Payment_Desc
RS.MoveNext
Loop Until RS.EOF
End With
RS.Close
cn.Close
Set RS = Nothing
Set cn = Nothing

pls help me.. thanks!

[ylsehl00]

. hi need your help regarding my vb project . using combobox in my vb and transfering its data from my database. I really need some help !! :icon_sad:

[JJCollins]

What exactly are you trying to achieve ylsehl00?

[andhie.dhie.1]

Comments on using his connections to SQL, how to code themselves when using the Database Access