CODEWITHSUNDEEP
sql-servervb.net
user1988492
user1988492

Reputation: 3

Generating a ListBox with SQL Results in VB

I am building a small VB Application that queries a MSSQL Database and then displays those results in a Listbox. There is a TextBox (TextBox1) where the user will input a CharName that will be used to select the CharName in the database to return results for that user only. I currently have the sql query coded into Button1.

So what I am needing help with is taking the input from TextBox1 and replacing Chars.CharName in the sql query with the user supplied input so that when the button is clicked the query executes and populates ListBox1 with the results.

Btw I am a total noob with VB (clearly).

The code I have thus far is:

Public Class Form1

    Private Sub Form1_Load(sender As System.Object, e As System.EventArgs) Handles MyBase.Load

    End Sub

    Private Sub TextBox1_Text(sender As System.Object, e As System.EventArgs) Handles TextBox1.TextChanged

    End Sub

    Private Sub Button1_Click(sender As System.Object, e As System.EventArgs) Handles Button1.Click
        Dim sqlquery As String
        sqlquery = ("SELECT Chars.CharName, CharItems.Type, CharItems.TypeID,  CharItems.ItemName, CharItems.Bag, CharItems.Slot, CharItems.ItemUID, CharItems.Craftname, CharItems.Gem1, CharItems.Gem2, CharItems.Gem3, CharItems.Gem4, CharItems.Gem5, CharItems.Gem6 FROM Chars INNER JOIN CharItems ON Chars.CharID = CharItems.CharID WHERE ([CharName] = '" + TextBox1.Text + "'")

    End Sub

    Private Sub ListBox1_SelectedIndexChanged(sender As System.Object, e As System.EventArgs) Handles ListBox1.SelectedIndexChanged

    End Sub
End Class

Upvotes: 0

Views: 5205

Answers (3)

Stephen Byrne
Stephen Byrne

Reputation: 7475

Not a VB guy so this is off the top of my head. Pay attention to the comments, though

'put these at the top of your code file:
imports System.Data.SqlClient
imports System.Data

'and this in the Button1_Click:
dim sql as string
dim cname as string
cname=TextBox1.Text


'never just directly accept the input from a textbox, etc, into an SQL query. Always use a parameter so prevent SQL Injection attacks.
  sqlquery="SELECT Chars.CharName, CharItems.Type, CharItems.TypeID,CharItems.ItemName,CharItems.Bag, CharItems.Slot, CharItems.ItemUID, CharItems.Craftname, CharItems.Gem1, CharItems.Gem2, CharItems.Gem3, CharItems.Gem4, CharItems.Gem5, CharItems.Gem6 FROM Chars INNER JOIN CharItems ON Chars.CharID = CharItems.CharID WHERE Chars.CharName = @CharName" 


using connection as SqlConnection = new SqlConnection(ConnectionString) 'substitute ConnectionString for the connection string to your database, where you get this from is beyond the scope of this code but it should be somewhere secure i.e app.config ConnectionStrings section
connection.Open()
using comm as SqlCommand = new SqlCommand(query,conn)
dim p as SqlParameter = new SqlParameter("CharName",cname)
comm.Parameters.Add(p)
dim rs as SqlDataReader = comm.ExecuteReader
dim dt as DataTable = new DataTable
dt.Fill(rs)
end using 'comm
end using 'conn

ListBox1.DisplayMember="CharName"
ListBox1.DataSource=dt
ListBox1.DataBind()

Upvotes: 0

DeanOC
DeanOC

Reputation: 7262

You don't need brackets around the string literals when setting the value of sqlquery and there is a typo in there as well.

It should just be

Dim sqlquery As String
sqlquery = "SELECT Chars.CharName, CharItems.Type, CharItems.TypeID, CharItems.ItemName, CharItems.Bag, CharItems.Slot, CharItems.ItemUID, CharItems.Craftname, CharItems.Gem1, CharItems.Gem2, CharItems.Gem3, CharItems.Gem4, CharItems.Gem5, CharItems.Gem6 FROM Chars INNER JOIN CharItems ON Chars.CharID = CharItems.CharID WHERE [CharName] = '" + TextBox1.Text + "'"

There is the whole issue of SQL Injection with this approach that I don't want to get into here, but you really should read up on executing Stored Procedures instead of raw SQL so that you can parameterise your inputs.

Upvotes: 0

Abdusalam Ben Haj
Abdusalam Ben Haj

Reputation: 5423

you need to do your connection and fetch the results from your database and store them in a Datatable, then you can bind your Datatable to the ListBox

Upvotes: 1

Related Questions