Results 1 to 7 of 7

Thread: [RESOLVED] Try to learn from web Videos having problem with listbox and query

  1. #1

    Thread Starter
    Hyperactive Member
    Join Date
    Dec 2006
    Posts
    320

    Resolved [RESOLVED] Try to learn from web Videos having problem with listbox and query

    I am trying hard to learn VB2017 and SQL 2017 as some of you have seen from posts. I am following some training on the web. VB.NET Tutorial - UPDATE a SQL Server Database - Part 3
    I have completed with some help to have parts 1 and 2 working.
    When I get to the part where I select the data in the list box to populate the text and checkbox field .my query returns no records
    any help on this would be great I've been at it most of the morning to no avail. I have it working up 23minutes into the video I just can't move the selected item in the listbox to the textboxes on the form.

    Not sur if this is ok to ask but it's kicking my tail.
    Code:
    Public Class Change_Employee
        Private SQL As New SQLControl
        Private Sub fetchusers()
            'REFRESH EMPLOYEE LIST
            lbxEmployees.Items.Clear()
    
    
            'ADD PARAMS & RUN QUERY
            SQL.AddParam("@users", "%" & txtFilter.Text & "%")
    
            SQL.ExecQuery("SELECT FName " &
                          "FROM employee " &
                          "WHERE fname LIKE @users " &
                          "ORDER BY fname ASC;")
    
    
            'REPORT & ABORT ON ERRORS
            If SQL.hasexception(True) Then Exit Sub
    
            'LOOP ROWS AND RETURN USERS TO LIST
            For Each r As DataRow In SQL.DBDT.Rows
                lbxEmployees.Items.Add(r("fname"))
    
            Next
        End Sub
    
        Private Sub GetUserDetails(username As String)
            SQL.AddParam("@user", username)
            SQL.ExecQuery("SELECT TOP 1 * " &
                          "FROM employee" &
                          "WHERE username = @user;")
            If SQL.RecordCount < 1 Then
                MsgBox("record count zip")
                Exit Sub
            End If
    
            For Each r As DataRow In SQL.DBDT.Rows
                txtID.Text = r("empID")
                TxtFname.Text = r("fname")
                txtLName.Text = r("lname")
                cbxEmployeed.Checked = r("employeed")
    
            Next
    
    
        End Sub
    
        Private Sub EditEmployee_Load(sender As Object, e As EventArgs) Handles MyBase.Load
            MdiParent = frmMain
            fetchusers()
    
        End Sub
    
        Private Sub txtFilter_KeyDown(sender As Object, e As KeyEventArgs) Handles txtFilter.KeyDown
    
    
            If e.KeyCode = Keys.Enter Then
                fetchusers()
                ' Stop the beeping when doing the keydown and enter
                e.Handled = True
                e.SuppressKeyPress = True
            End If
    
        End Sub
    
        Private Sub lbxEmployees_SelectedIndexChanged(sender As Object, e As EventArgs) Handles lbxEmployees.SelectedIndexChanged
            GetUserDetails(lbxEmployees.Text
                           )
        End Sub
    End Class
    thanks

  2. #2
    PowerPoster Zvoni's Avatar
    Join Date
    Sep 2012
    Location
    To the moon and then left
    Posts
    4,418

    Re: Try to learn from web Videos having problem with listbox and query

    1) Is there a blank missing between e and "?
    2) Shouldn't that be FName instead of username?
    Code:
    Private Sub GetUserDetails(username As String)
            SQL.AddParam("@user", username)
            SQL.ExecQuery("SELECT TOP 1 * " &
                          "FROM employee" &
                          "WHERE username = @user;")
    EDIT: As an advise: To avoid mistakes like the missing blank: Always try to write your SQL-Statements in one line.
    Because that way it's easy just to copy/paste it during debug-mode to test it in a SQL-Client
    Last edited by Zvoni; Jun 19th, 2018 at 01:06 AM.
    Last edited by Zvoni; Tomorrow at 31:69 PM.
    ----------------------------------------------------------------------------------------

    One System to rule them all, One Code to find them,
    One IDE to bring them all, and to the Framework bind them,
    in the Land of Redmond, where the Windows lie
    ---------------------------------------------------------------------------------
    People call me crazy because i'm jumping out of perfectly fine airplanes.
    ---------------------------------------------------------------------------------
    Code is like a joke: If you have to explain it, it's bad

  3. #3

    Thread Starter
    Hyperactive Member
    Join Date
    Dec 2006
    Posts
    320

    Re: Try to learn from web Videos having problem with listbox and query

    OK I have fought this and I finally found the problem I fixed it but I worry that this could bite me again later.

    When I did the query it looked like this'
    Code:
    SQL.ExecQuery("SELECT TOP 1 * " &
                                                                                              "FROM employee" &
                                                                                               "WHERE FName = @fnames;")

    In version 2008 when I concatenated a line I would leave space and put an underline at the end EX. " & _"
    When I do this in 2017 the underline goes away and so does the space so to fix it I placed a space in front of the FROM and the WHERE statements so the query would work.

    I take it that this removal of the space and the underline is new to this version?

    I know I can put everything in one line but while I'm learning this SQL code it helps me keep things straight in my mind.

    Thanks for the help
    I want to show the fix for this for me. I'll mark it finished later tonight.

  4. #4

    Thread Starter
    Hyperactive Member
    Join Date
    Dec 2006
    Posts
    320

    Re: Try to learn from web Videos having problem with listbox and query

    Code:
    SQL.ExecQuery("SELECT TOP 1 * " &
                          " FROM employee" &
                          " WHERE FName = @fnames;")
    This didn't show correctly on the last post

  5. #5
    Super Moderator si_the_geek's Avatar
    Join Date
    Jul 2002
    Location
    Bristol, UK
    Posts
    41,929

    Re: Try to learn from web Videos having problem with listbox and query

    Quote Originally Posted by Alfarata View Post
    In version 2008 when I concatenated a line I would leave space and put an underline at the end EX. " & _"
    When I do this in 2017 the underline goes away and so does the space ...
    I take it that this removal of the space and the underline is new to this version?
    In order to spread a line of code you used to need to indicate that by putting a space and an underscore at the end of the line, it changed a few versions ago (maybe VS 2010?), and now it just need to be obvious that the line of code isn't finished (eg: ending a line with & or + isn't valid) so VS knows it should assume the code continues onto the next line.

    so to fix it I placed a space in front of the FROM and the WHERE statements so the query would work.
    It doesn't really matter whether the space goes at the end of one line or the start of the next, my preference is the end but it is your choice.

    I know I can put everything in one line but while I'm learning this SQL code it helps me keep things straight in my mind.
    For code like that it is better to use multiple lines (no matter how well you understand what it is doing), because it makes the code easier to read.

  6. #6
    Smooth Moperator techgnome's Avatar
    Join Date
    May 2002
    Posts
    34,532

    Re: Try to learn from web Videos having problem with listbox and query

    Yeah, with VS2015, or 2017, I forget which one... the line continuation character (the _ ) became obsolete. The IDE/compiler became smart enough that it can determine when a line has been split over multiple lines.

    -tg
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  7. #7

    Thread Starter
    Hyperactive Member
    Join Date
    Dec 2006
    Posts
    320

    Re: Try to learn from web Videos having problem with listbox and query

    Thanks for the answers on this I thought I was doing it wrong when it kept disappearing

    thanks

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