-
Jun 18th, 2018, 01:57 PM
#1
Thread Starter
Hyperactive Member
[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
-
Jun 19th, 2018, 12:12 AM
#2
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
-
Jun 22nd, 2018, 11:12 AM
#3
Thread Starter
Hyperactive Member
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.
-
Jun 22nd, 2018, 11:13 AM
#4
Thread Starter
Hyperactive Member
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
-
Jun 22nd, 2018, 11:46 AM
#5
Re: Try to learn from web Videos having problem with listbox and query
Originally Posted by Alfarata
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.
-
Jun 22nd, 2018, 12:17 PM
#6
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
-
Jun 22nd, 2018, 12:56 PM
#7
Thread Starter
Hyperactive Member
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|