Results 1 to 6 of 6
  1. #1
    Join Date
    Apr 2009
    Posts
    2

    Unanswered: How to give an SQL statement in combobox(Visual basic 6.0)

    Hi everyone..

    I want to insert a combobox with the list of test names of a database, but the testname contains 15000+ data, and I cant type all the data. so can any one help me in writing an SQL statement, so that if I give that statement it will automatically swaps the table names to the combobox.

    P.S I know the SQL statement but I dono where to give that SQL statement in combobox

    I have done some program to extract the data...in the program if you look at the SQL statement(underlined) I want to insert all the values of fullname into the combo box, so that if I select one of the fullname then the data regarding that fullname should be extracted and should be displayed after pressing the Ok button
    Private Sub Okbutton_Click()
    On Error Resume Next

    Dim sConnect As String
    Dim db As Database
    Dim rs As Recordset
    Dim Ws As Workspace
    'Dim ID As Double
    'Dim doubleID1 As Double
    Dim intstring As String

    Set Ws = DBEngine.Workspaces(0)

    sConnect = "ODBC;DSN=WinUTMS_RT_DB;UID=Localhost;PWD=;Dat abas e=WinUTMS_RT_DB;"

    Set db = Ws.OpenDatabase("", 0, 0, sConnect)

    Set rs = db.OpenRecordset("SELECT Fullname FROM abssubtest")
    Do While Not rs.EOF
    'ID = rs!Messwert
    'doubleID1 = rs!UPLimWert
    intstring = rs.Fields("Fullname")
    rs.MoveNext
    Loop
    End Sub
    explanation with program will be verymuch helpfull for me

    Thanks in advance

    Regards
    Siva

  2. #2
    Join Date
    Apr 2009
    Posts
    6
    Hi There,
    First you need to set the recordsource property for your combo box to "SELECT Fullname FROM abssubtest;".

    Then in your OK button code, you want to read the value that is in the combo box, and either filter your form, or update the recordsource for your form to display the other fields associated with that FullName.

    So in your ok button code you will have something like this:

    Code:
    Private Sub Okbutton_Click()
    On Error Resume Next
    
    me.form.recordsource = "SELECT * FROM abssubtest WHERE Fullname = ' " & ComboBox.Value & " ';"
    
    me.requery
    
    End Sub
    Note i used singles quotes around the FullName value that will be read from the combo box.

    It is critical that you set your combo box Recordsource to "SELECT Fullname FROM abssubtest;" in your properties box.

    Good Luck!
    Nate

  3. #3
    Join Date
    Apr 2009
    Posts
    2
    Private Sub Combo1_Click()

    Label1.Caption = "find" & Combo1.Text
    Label1.Visible = True

    End Sub

    Private Sub Form_Load()
    On Error Resume Next

    Label1.Visible = False
    DataGrid1.Visisble = False

    Dim cn As ADODB.Connection
    Dim rs As ADODB.Recordset

    Set cn = New ADODB.Connection
    cn.ConnectionString = "ODBC;DSN=WinUTMS_RT_DB;UID=Localhost;PWD=;Databas e=WinUTMS_RT_DB;"
    cn.Open

    Set rs = New ADODB.Recordset
    rs.Open "absprfg", cn, adOpenKeyset, adLockPessimistic, adCmdTable

    Do While Not rs.EOF
    Combo1.AddItem rs.Fields("Modus")
    rs.MoveNext
    Loop
    If Not (rs.EOF And rs.BOF) Then
    rs.MoveFirst
    End If
    End Sub

    Private Sub Command1_Click()
    On Error Resume Next
    Me.Form.RecordSource = "SELECT Fullname FROM abssubtest & ComboBox.Value & " ';"

    Me.Requery
    End Sub

    Private Sub Label1_Click()
    DataGrid1.Visible = True

    Adodc1.CommandType = adCmdText
    Adodc1.RecordSource = "select absprfg.Modus From absprfg WHERE absprfg.Modus='" & Combo1.Text & "' "
    Adodc1.Refresh

    End Sub
    I have done the above program with your suggestion...but after pressing the run button..I am getting the blank screen and after some time the vb is not responding and I have to close the program...even I have seen it with break point but of no use....can you please tell me where I have done the mistake...

    Regards
    Siva

  4. #4
    Join Date
    Feb 2009
    Posts
    23
    Set cn = New ADODB.Connection
    cn.ConnectionString = "ODBC;DSN=WinUTMS_RT_DB;UID=Localhost;PWD=;Dat abas e=WinUTMS_RT_DB;"
    cn.Open

    Set rs = New ADODB.Recordset
    rs.Open "absprfg", cn, adOpenKeyset, adLockPessimistic, adCmdTable
    i did not see query before the above quote.
    Code:
    absprfg = "SELECT FieldName1,FieldName2 From YourTable"
    Set rs = New ADODB.Recordset
    rs.Open "absprfg", cn, adOpenKeyset, adLockPessimistic, adCmdTable

  5. #5
    Join Date
    Apr 2009
    Posts
    6
    also try setting your breakpoint to your form load event, then press F8 to step through each line.
    Good luck!

  6. #6
    Join Date
    Jun 2004
    Location
    Arizona, USA
    Posts
    1,848
    You want 15000 names on that combobox???

    IMO, that will NOT be a good user interface... the slightest movement of the mouse will cause the names to jump up or down by hundreds of names. At the least, add 27 buttons, each with a single letter (A-Z, or *) as the caption. Then, use the button to filter the data first. The user selects the 'E' button, and you then issue a query, to fetch all names beginning with 'E.' (* returns all the names)
    Lou
    使大吃一惊
    "Lisa, in this house, we obey the laws of thermodynamics!" - Homer Simpson
    "I have my standards. They may be low, but I have them!" - Bette Middler
    "It's a book about a Spanish guy named Manual. You should read it." - Dilbert


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •