Show SQL Column Item in VB.NET TextBox

Question

I want a specific value of an SQL Database column to appear in a textbox, but there is an error with my code which appears to be at this line:

Dim lrd As MySqlDataReader = cmd.ExecuteReader()

Imports MySql.Data.MySqlClient

Public Class Main

Dim conn As MySqlConnection

Private Sub Main_Load(sender As Object, e As EventArgs) Handles Me.Load
    conn = New MySqlConnection()
    conn.ConnectionString = "server='127.0.0.1';user id='root';Password='test';database='snipper'"
    Try
        conn.Open()
    Catch myerror As MySqlException
        MsgBox("Error Connecting to Database. Please Try again !")
    End Try
    Dim strSQL As String = "SELECT * FROM snippets"
    Dim da As New MySqlDataAdapter(strSQL, conn)
    Dim ds As New DataSet
    da.Fill(ds, "snippets")
    With ComboBox1
        .DataSource = ds.Tables("snippets")
        .DisplayMember = "title"
        .SelectedIndex = 0
    End With
    Dim cmd = New MySqlCommand("SELECT snippet FROM snippets where title=" & cbSnippets.Text)
    cmd.Connection = conn
    Dim lrd As MySqlDataReader = cmd.ExecuteReader()
    While lrd.Read()
        txtCode.Text = lrd("snippet").ToString()
    End While
End Sub

What may be wrong?

what error are you getting, and where does it occur?

BWS
– BWS

2013-12-23 14:07:01 +00:00
Commented Dec 23, 2013 at 14:07 — BWS
– BWS, Commented Dec 23, 2013 at 14:07

GarethD · Accepted Answer · 2013-12-23 20:15:59Z

3

PLEASE USE PARAMETERISED QUERIES

Your actual problem originates from this line:

Dim cmd = New MySqlCommand("SELECT snippet FROM snippets where title=" & cbSnippets.Text)

Supposing I enter "This is a test" into the text box, the SQL becomes

SELECT snippet 
FROM snippets 
WHERE title=This is a test

With no quotes around the text, it should be:

SELECT snippet 
FROM snippets 
WHERE title='This is a test'

However, if I were to write "''; DROP TABLE Snippets; -- " In your text box you may find yourself without a snippets table!.

You should always use parameterised queries, this is safer and more efficient (it means query plans can be cached and reused so don't need to be compiled each time);

Dim cmd = New MySqlCommand("SELECT snippet FROM snippets where title = @Title")
cmd.Parameters.AddWithValue("@Title", cbSnippets.Text)
Dim lrd As MySqlDataReader = cmd.ExecuteReader()

edited Dec 23, 2013 at 20:15

answered Dec 23, 2013 at 14:22

GarethD

70k11 gold badges90 silver badges130 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

suchanoob Over a year ago

Thanks a lot for the suggestions! Worked!

Laurent S. · Accepted Answer · 2013-12-23 14:07:05Z

2

Try changing this line :

Dim cmd = New MySqlCommand("SELECT snippet FROM snippets where title=" & cbSnippets.Text)

to :

Dim cmd = New MySqlCommand("SELECT snippet FROM snippets where title='" & cbSnippets.Text & "'")

Note the quotes around the string you'l be searching for. You could aswell use the like comparison too :

Dim cmd = New MySqlCommand("SELECT snippet FROM snippets where title like '%" & cbSnippets.Text & "%'")

The % symbol acts as a wildcard. In this case, that would look for any string containing the searched text instead of string being exactly the same as the searched text.

answered Dec 23, 2013 at 14:07

Laurent S.

6,9373 gold badges32 silver badges42 bronze badges

3 Comments

suchanoob Over a year ago

Thanks a tone! Worked!

Laurent S. Over a year ago

You're welcome. But indeed although this will be working, the use of parameterised queries as suggested by GarethD is the best practice.

suchanoob Over a year ago

I will use parameterised queries for sure. Thank you both!

Collectives™ on Stack Overflow

Show SQL Column Item in VB.NET TextBox

2 Answers 2

1 Comment

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related