הערה
הגישה לדף זה מחייבת הרשאה. באפשרותך לנסות להיכנס או לשנות מדריכי כתובות.
הגישה לדף זה מחייבת הרשאה. באפשרותך לנסות לשנות מדריכי כתובות.
Question
Tuesday, February 26, 2013 6:29 PM
Hey everyone, first post here. I am not the best programmer in the world and I am being asked to develop a program. Now, I am very rusty in my VB.NET... I already created one of the programs asked of me, but this one has me in a bind (get it?). I already added the Server Database to the project (via the GUI basically Tools > Connect to Database). Now, what I need is this... and I am having trouble figuring it out.
I have a textbox where people will input only numeric data. I already set up a "Regex" code for this, which works perfectly. Now I basically want this numeric info to be searched on the database when a "Search" button is clicked. This will display the name and last name of the client below. The program needs to do more than this but I am sure I can figure out the rest... its just that line of code. The searches I've done all lead to codes that take into assumption I am adding the table adapter or the connection manually into the code, when in fact I already added this server connection through the VB UI itself. Any help?
All replies (7)
Tuesday, February 26, 2013 9:23 PM ✅Answered | 2 votes
Imports System.Data.SqlClient
Public Class Form1
Class UserData
Property ClientNumber As Integer
Property Name As String
Property LastName As String
End Class
Function GetData(clientNo As Integer) As List(Of UserData)
Dim theResults = New List(Of UserData)
Dim connStr = "your connection string"
' using "Using" will dispose of the connection object when it is finished
Using conn = New SqlConnection(connStr)
' the text for the SQL; use a parameter for the value sought
Dim sql = "SELECT ClientNumber, Name, LastName FROM ClientTable Where [columnName] = @whatever"
Dim sqlCmd = New SqlCommand(sql, conn)
' add the parameter with its value
sqlCmd.Parameters.AddWithValue("@whatever", clientNo)
conn.Open()
' execute the SQL command
Dim rdr = sqlCmd.ExecuteReader
' read back the results
While rdr.Read
theResults.Add(New UserData With {
.ClientNumber = rdr.GetInt32(0),
.Name = rdr.GetString(1),
.LastName = rdr.GetString(2)
})
End While
conn.Close()
End Using
Return theResults
End Function
Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
Dim clientNo As Integer = 0
If Integer.TryParse(TextBox1.Text, clientNo) Then
Dim myResults = GetData(clientNo)
If myResults.Count = 1 Then
' we have a unique result, show information
Label1.Text = String.Format("Welcome, {0} {1}! Your turn is ##", myResults(0).Name, myResults(0).LastName)
ElseIf myResults.Count > 1 Then
MsgBox(String.Format("Duplicate data found for client number {0}.", clientNo))
Else
MsgBox(String.Format("Client number {0} not found.", clientNo))
End If
Else
MsgBox("Client number must be a whole number.")
End If
End Sub
End Class
(N.B. Not tested.)
HTH,
Andrew
Tuesday, February 26, 2013 6:50 PM
So did you use the TableAdapter or Data Source Configuration Wizard to create your query?
http://msdn.microsoft.com/en-us/library/6sb6kb28(v=vs.110).aspx
Paul ~~~~ Microsoft MVP (Visual Basic)
Tuesday, February 26, 2013 8:38 PM
I created a TableAdapter but deleted all that and decided to code the SqlConnection into the program itself. I tested the connection and it worked. Now I am building the query, this is basically my code.
Dim con as New SqlClient.SqlConnection
Dim strCommand as String = "
ANd there I stopped hahaha. I know I have to build the query there, but I want to pass the value from the textbox and search for it in the Database, and when it does, fill back a Label or Textbox below with the full name of the client. Ex.
I input 124. Click Button. "Welcome NAME LASTNAME, your turn is ##"
And that's it. I know how the query should work...
Select ClientNumber, Name, LastName FROM ClientTable Where....
Not sure if this is how VB directly works with the query, if it has a difference from how a query is executed and how will I basically input the textbox information into the query. Thanks for any help :), its greatly appreciated.
Wednesday, February 27, 2013 7:49 PM
Wow Andrew, thanks a lot. Lots of code I had never seen in my life even when I was a student. Managed to fully understand how the code worked, but I can't seem to pinpoint an error. The program loads, but it seemingly never finds anyone. I am trying out different account numbers and it just shoots back the "Client number not found" error. I am seeing if there's a problem somewhere but since I am barely intermdiate level I can't find the exact source of the problem. I believe its just not querying well? (I of course adapted your table names to the one I am using).
Once again, thanks a million for your response.
Wednesday, February 27, 2013 8:04 PM
Nevermind I made an error in the logic I added. Thanks a million really appreciated.
Wednesday, February 27, 2013 9:06 PM
Wow Andrew, thanks a lot.
You're welcome.
Managed to fully understand how the code worked..
That's what we like to hear :)
--
Andrew
Friday, March 1, 2013 1:16 PM
Ok, program is working perfectly and I've added some details. Now I am dealing with one very important detail, inserting certain data to a table in the server. I added the following logic inside the button press and the success If.... Basically opens a connection to the server and adds this
Dim InsertData = "INSERT INTO [tblTurns] (info1, info2, info3, info4) Values (@parameter, myresults(0).Name...etc)
Now... the program isn't crashing. I am deciding if I should add this to the GetData function posted above or to the button... but as I said the program doesn't crash, yet data isn't added to the DB. Pointers? Is the query bad (i don't know if I can use the results I got from the function and insert them into a table, and no... I can't relate both tables). Any help? Greatly appreciated :)