Results 1 to 25 of 25

Thread: SQL query question [Resolved]

  1. #1

    Thread Starter
    Member
    Join Date
    Jun 2005
    Location
    Melbourne, Australia
    Posts
    39

    Resolved SQL query question [Resolved]

    I have a db with a table called Customers. There are two columns in Customers: CustomerCode and CustomerName.

    My vb form has a combo box with a list of the customer code, a text box, and a button.

    What I want to do is: If a user chooses a customer code from the combo box I want the customer name to show in the text box.

    This is the SQL query that I have been using:

    strSQL = "SELECT CustomerName FROM Customers WHERE CustomerCode = '" & cboCustomerCode.Text & "'"

    The problem i'm having is that the result of the customer name will not show on the text box.

    This is what I used to put the result in the text box:

    txtCustomerName = strSQL

    Is this correct?
    Last edited by Kalilian; Jul 10th, 2005 at 06:42 PM.

  2. #2
    PowerPoster lintz's Avatar
    Join Date
    Mar 2003
    Location
    The 19th Hole
    Posts
    2,697

    Re: SQL query question

    The query syntax looks correct however you need to execute the query before the result will show in the text box.

  3. #3

    Thread Starter
    Member
    Join Date
    Jun 2005
    Location
    Melbourne, Australia
    Posts
    39

    Re: SQL query question

    Yes, I think that's where I'm having the problem. How would you execute the SQL?

  4. #4
    Software Carpenter dee-u's Avatar
    Join Date
    Feb 2005
    Location
    Pinas
    Posts
    11,118

    Re: SQL query question

    What did you use to populate the combo box in the first place?
    Regards,


    As a gesture of gratitude please consider rating helpful posts. c",)

    Some stuffs: Mouse Hotkey | Compress file using SQL Server! | WPF - Rounded Combobox | WPF - Notify Icon and Balloon | NetVerser - a WPF chatting system

  5. #5

    Thread Starter
    Member
    Join Date
    Jun 2005
    Location
    Melbourne, Australia
    Posts
    39

    Re: SQL query question

    I used the values from the db. Therefore I did all the connection, recordsets and all that.

    I was wondering if I should do:

    recordset.Open strSQL

  6. #6
    PowerPoster lintz's Avatar
    Join Date
    Mar 2003
    Location
    The 19th Hole
    Posts
    2,697

    Re: SQL query question

    Yes

  7. #7
    Software Carpenter dee-u's Avatar
    Join Date
    Feb 2005
    Location
    Pinas
    Posts
    11,118

    Re: SQL query question

    Then create a recordset too based on what is selected in the combo box....

    VB Code:
    1. Dim adoRecordset As ADODB.Recordset
    2. Set adoRecordset = New ADODB.Recordset
    3. With adoRecordset
    4.     .Open "SELECT CustomerName FROM Customers WHERE CustomerCode = '" & cboCustomerCode.Text & "'",'your connection'
    5.     If Not (.EOF And .BOF) Then
    6.         txtCustomerName = .Fields("CustomerName")
    7.     End If
    8.     .Close
    9. End With
    10. Set adoRecordset = Nothing
    Regards,


    As a gesture of gratitude please consider rating helpful posts. c",)

    Some stuffs: Mouse Hotkey | Compress file using SQL Server! | WPF - Rounded Combobox | WPF - Notify Icon and Balloon | NetVerser - a WPF chatting system

  8. #8

    Thread Starter
    Member
    Join Date
    Jun 2005
    Location
    Melbourne, Australia
    Posts
    39

    Re: SQL query question

    Ok.

    A different scenario now.

    What if I have multiple SQL queries in different Subs? Would you still do the same thing?

  9. #9
    Addicted Member
    Join Date
    Mar 2005
    Posts
    174

    Re: SQL query question

    try this code:
    In the General Declaration:
    Dim cnn As ADODB.Connection
    Dim rs As ADODB.Recordset

    Form_Load()
    Set cnn = New ADODB.Connection
    Set rs = New ADODB.Recordset
    cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & app.path _ & "\db.mdb;Persist Security Info=False"

    In the event You want:

    strSQL = "SELECT CustomerName FROM Customers WHERE CustomerCode = '" & trim(cboCustomerCode.Text) & "'"
    cnn.close
    cnn.open
    rs.open strsql,cnn
    rs.requery
    text1.text=rs.fields("Customername")

    Yes it is possible to have multiple Sql queries in different subs. Write the exact thing which is written in the event You want. Simply change the Sql query

  10. #10

    Thread Starter
    Member
    Join Date
    Jun 2005
    Location
    Melbourne, Australia
    Posts
    39

    Re: SQL query question

    What if I use the 'command' property? How would you go about doing that?

  11. #11
    Giants World Champs!!!! Mark Gambo's Avatar
    Join Date
    Sep 2003
    Location
    Colorado
    Posts
    2,965

    Re: SQL query question

    Why not populate your combobox with the CustomerCode and CustomerName.
    and then parse what you need and put it into the textbox

    "1234-Jones"
    "2345-Roberts"
    "234-Davis"

    VB Code:
    1. Private Sub Combo1_KeyPress()
    2. Dim strTex as String
    3.      strText = Me.Combo1.Text
    4.      strText = Mid(strText,Instr(strText,"-")+1)
    5.      Me.Text1.Text = strText
    6. End Sub
    Regards,

    Mark

    Please remember to rate posts! Rate any post you find helpful. Use the link to the left - "Rate this Post". Please use [highlight='vb'] your code goes in here [/highlight] tags when posting code. When a question you asked has been resolved, please go to the top of the original post and click "Thread Tools" then select "Mark Thread Resolved."


  12. #12

    Thread Starter
    Member
    Join Date
    Jun 2005
    Location
    Melbourne, Australia
    Posts
    39

    Re: SQL query question

    No, I can not do that. The customer code can only be a combo box and the customer name has to be a text box.

  13. #13
    Giants World Champs!!!! Mark Gambo's Avatar
    Join Date
    Sep 2003
    Location
    Colorado
    Posts
    2,965

    Re: SQL query question

    Quote Originally Posted by Kalilian
    No, I can not do that. The customer code can only be a combo box and the customer name has to be a text box.
    No I understand that but I don't think you understand what I am posting take a look at the attached project:
    Attached Images Attached Images  
    Attached Files Attached Files
    Regards,

    Mark

    Please remember to rate posts! Rate any post you find helpful. Use the link to the left - "Rate this Post". Please use [highlight='vb'] your code goes in here [/highlight] tags when posting code. When a question you asked has been resolved, please go to the top of the original post and click "Thread Tools" then select "Mark Thread Resolved."


  14. #14

    Thread Starter
    Member
    Join Date
    Jun 2005
    Location
    Melbourne, Australia
    Posts
    39

    Re: SQL query question

    Ok, I understand you Mark Gambo. But it's all right. I got it working. However, there is another thing that I need to fix.

    I am now able to get the customer name and place that in the text box. My combo box does the auto-fill thingy where the user types in a letter and tries to find it in the list. How can I get the method to work (customer name into text box) only when the user has finished typing in the combo box?

    Because every time I type in the combo box it gives me the customer name but if the customer name doesn't exist in the list then it gives me an error.

  15. #15
    Giants World Champs!!!! Mark Gambo's Avatar
    Join Date
    Sep 2003
    Location
    Colorado
    Posts
    2,965

    Re: SQL query question

    Quote Originally Posted by Kalilian
    Ok, I understand you Mark Gambo. But it's all right. I got it working. However, there is another thing that I need to fix.

    I am now able to get the customer name and place that in the text box. My combo box does the auto-fill thingy where the user types in a letter and tries to find it in the list. How can I get the method to work (customer name into text box) only when the user has finished typing in the combo box?

    Because every time I type in the combo box it gives me the customer name but if the customer name doesn't exist in the list then it gives me an error.
    You can either trap for the error or fire the code that gets the Customer's name on the comboboxes Lost Focus Event.
    Regards,

    Mark

    Please remember to rate posts! Rate any post you find helpful. Use the link to the left - "Rate this Post". Please use [highlight='vb'] your code goes in here [/highlight] tags when posting code. When a question you asked has been resolved, please go to the top of the original post and click "Thread Tools" then select "Mark Thread Resolved."


  16. #16
    Banned dglienna's Avatar
    Join Date
    Jun 2004
    Location
    Center of it all
    Posts
    17,901

    Re: SQL query question

    Sounds like you are using the Changed event, when you would want to use the lost focus, or even a button. Post your code that you are using.

  17. #17

    Thread Starter
    Member
    Join Date
    Jun 2005
    Location
    Melbourne, Australia
    Posts
    39

    Re: SQL query question

    How would you use the Lost Focus Event?

  18. #18
    Giants World Champs!!!! Mark Gambo's Avatar
    Join Date
    Sep 2003
    Location
    Colorado
    Posts
    2,965

    Re: SQL query question

    Quote Originally Posted by Mark Gambo
    You can either trap for the error or fire the code that gets the Customer's name on the comboboxes Lost Focus Event.

    or better yet add code to the GetData Function that checks that there is a valid value in the combobox before running the query. Post your code for the Auto-Fill.
    Regards,

    Mark

    Please remember to rate posts! Rate any post you find helpful. Use the link to the left - "Rate this Post". Please use [highlight='vb'] your code goes in here [/highlight] tags when posting code. When a question you asked has been resolved, please go to the top of the original post and click "Thread Tools" then select "Mark Thread Resolved."


  19. #19

    Thread Starter
    Member
    Join Date
    Jun 2005
    Location
    Melbourne, Australia
    Posts
    39

    Re: SQL query question

    Here's my auto-fill code:

    Public Sub cboCustomerCode_Change()

    Dim i As Long
    Dim MyText As Long

    'Doesn't run the autofill when deleting or using the backspace
    If Backspaced = True Or cboCustomerCode.Text = "" Then
    Backspaced = False
    Exit Sub
    End If

    'Run through the available items and grab the first matching one.
    For i = 0 To cboClustomerCode.ListCount - 1

    If InStr(1, cboCustomerCode.List(i), cboCustomerCode.Text, vbTextCompare) = 1 Then

    MyText = cboCustomerCode.SelStart
    cboCustomerCode.Text = cboCustomerCode.List(i)
    cboCustomerCode.SelStart = MyText
    cboCustomerCode.SelLength = Len(cboCustomerCode.Text) - MyText

    Exit For
    End If

    Next
    End Sub

  20. #20

    Thread Starter
    Member
    Join Date
    Jun 2005
    Location
    Melbourne, Australia
    Posts
    39

    Re: SQL query question

    And this is what I used to fill the text box:

    Public Sub getName()
    comm.CommandText = "SELECT CustomerName FROM Customers WHERE CustomerCode = '" & cboCustomerCode.Text & "'"

    Set recordSet = comm.Execute

    txtCustomerName.Text = recdSet.Fields("CustomerName")
    End Sub

  21. #21
    Giants World Champs!!!! Mark Gambo's Avatar
    Join Date
    Sep 2003
    Location
    Colorado
    Posts
    2,965

    AutoFill ComboBox Function

    This is what I use for a Combo AutoFill:

    In A Module:
    VB Code:
    1. Private Declare Function SendMessage Lib "user32" Alias "SendMessageA" _
    2. (ByVal hwnd As Long, ByVal wMsg As Long, ByVal wParam As Long, _
    3. lParam As Any) As Long
    4.  
    5. Public Function AUTOFIND(ByRef cboCurrent As ComboBox, _
    6.     ByVal KeyAscii As Integer, Optional ByVal LimitToList As Boolean = False)
    7.    
    8.     Dim lCB As Long
    9.     Dim sFindString As String
    10.    
    11.     If KeyAscii = 8 Then
    12.         If cboCurrent.SelStart <= 1 Then
    13.             cboCurrent = ""
    14.             AUTOFIND = 0
    15.             Exit Function
    16.         End If
    17.             If cboCurrent.SelLength = 0 Then
    18.                 sFindString = UCase(Left(cboCurrent, Len(cboCurrent) - 1))
    19.             Else
    20.                 sFindString = Left$(cboCurrent.Text, cboCurrent.SelStart - 1)
    21.             End If
    22.    
    23.     ElseIf KeyAscii < 32 Or KeyAscii > 127 Then
    24.         Exit Function
    25.     Else
    26.         If cboCurrent.SelLength = 0 Then
    27.             sFindString = UCase(cboCurrent.Text & Chr$(KeyAscii))
    28.         Else
    29.             sFindString = Left$(cboCurrent.Text, cboCurrent.SelStart) & Chr$(KeyAscii)
    30.         End If
    31.     End If
    32.     lCB = SendMessage(cboCurrent.hWnd, CB_FINDSTRING, -1, ByVal sFindString)
    33.     If lCB <> CB_ERR Then
    34.         cboCurrent.ListIndex = lCB
    35.         cboCurrent.SelStart = Len(sFindString)
    36.         cboCurrent.SelLength = Len(cboCurrent.Text) - cboCurrent.SelStart
    37.         AUTOFIND = 0
    38.     Else
    39.         If LimitToList = True Then
    40.             AUTOFIND = 0
    41.         Else
    42.             AUTOFIND = KeyAscii
    43.         End If
    44.     End If
    45. End Function

    On the Form:
    VB Code:
    1. Private Sub Combo1_KeyPress(KeyAscii As Integer)
    2. On Error GoTo Combo1_KeyPress_Error
    3.  
    4.      KeyAscii = AUTOFIND(Me.Combo1, KeyAscii, True)
    5.  
    6. On Error GoTo 0
    7. Exit Sub
    8.  
    9. Combo1_KeyPress_Error:
    10.  
    11. MsgBox "Error " & Err.Number & " (" & Err.Description & ") "  & _
    12. "in procedure Combo1_KeyPress of Form " & Me.Name
    13. End Sub
    Last edited by Mark Gambo; Sep 14th, 2005 at 07:19 PM.
    Regards,

    Mark

    Please remember to rate posts! Rate any post you find helpful. Use the link to the left - "Rate this Post". Please use [highlight='vb'] your code goes in here [/highlight] tags when posting code. When a question you asked has been resolved, please go to the top of the original post and click "Thread Tools" then select "Mark Thread Resolved."


  22. #22
    Giants World Champs!!!! Mark Gambo's Avatar
    Join Date
    Sep 2003
    Location
    Colorado
    Posts
    2,965

    Re: SQL query question

    and Then run your code to fill the TextBox:
    VB Code:
    1. Public Sub getName()
    2. comm.CommandText = "SELECT CustomerName FROM Customers " & _
    3. "WHERE CustomerCode = '" & cboCustomerCode.Text & "'"
    4.  
    5. Set recordSet = comm.Execute
    6.  
    7. txtCustomerName.Text = recdSet.Fields("CustomerName")
    8. End Sub

    I put the AutoFind function into a module so that I can use it throughout my application. I still think that you should combine the Customer Number and the Customer Name in the Combobox and then after that proper name has been selected then you strip away the customer number from the combobox and take the customer name and put it into the textbox, just my two cents.
    Last edited by Mark Gambo; Jul 7th, 2005 at 05:20 AM.
    Regards,

    Mark

    Please remember to rate posts! Rate any post you find helpful. Use the link to the left - "Rate this Post". Please use [highlight='vb'] your code goes in here [/highlight] tags when posting code. When a question you asked has been resolved, please go to the top of the original post and click "Thread Tools" then select "Mark Thread Resolved."


  23. #23

    Thread Starter
    Member
    Join Date
    Jun 2005
    Location
    Melbourne, Australia
    Posts
    39

    Re: SQL query question

    How would you write the syntax for:

    If 'text in combo box not in the list' then
    'do nothing'
    Else
    'get the name'

  24. #24

    Thread Starter
    Member
    Join Date
    Jun 2005
    Location
    Melbourne, Australia
    Posts
    39

    Re: SQL query question

    Quote Originally Posted by Kalilian
    How would you write the syntax for:

    If 'text in combo box not in the list' then
    'do nothing'
    Else
    'get the name'
    Figured it out

  25. #25

    Thread Starter
    Member
    Join Date
    Jun 2005
    Location
    Melbourne, Australia
    Posts
    39

    Re: SQL query question

    I have resolved my problems. Thanks for all your help.

Posting Permissions

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



Click Here to Expand Forum to Full Width