Using SQL datareader to set variables to a label

Question

I have spent so long on figuring this out however cant seem to resolve the problem. I am using MySql and Visual Basic.

I am using the select statement to get the variables i need, then using data reader to loop through, get the variables (to string) and finally set these to text labels which I am using in a form. I am sure I am following the correct procedure for this, but this does not seem to bring back results.

If anybody could help me i would be very much appreciated. The code I have is below;

    lblName.Text = Form1.topicName

    MyConnString = "Database=case management system;Data Source=localhost;User Id=root;Password="abc"
    Dim MySqlConn As New MySqlConnection(MyConnString)
    Dim MySelectQuery As String = "SELECT topic_id, topic_name, description, useful_links, employee_id, date "             
       & "FROM knowledge_base "
       & "WHERE topic_name = '" & lblName.Text & "'"
    Dim myCommand As New MySqlCommand(MySelectQuery)
    myCommand.Connection = MySqlConn
    MySqlConn.Open()
    myCommand.ExecuteNonQuery()
    mydata = myCommand.ExecuteReader
    While mydata.Read()
        labelDescription.Text = mydata.Item("description").ToString
        labelLinks.Text = mydata.Item("useful_links").ToString
    End While
    mydata.Close()
    myCommand.Connection.Close()
    MySqlConn.Close()

why are you using ExecuteNonQuery() here?

Kiran1016
– Kiran1016

2013-01-22 19:21:12 +00:00
Commented Jan 22, 2013 at 19:21 — Kiran1016
– Kiran1016, Commented Jan 22, 2013 at 19:21

Richard Schneider · Accepted Answer · 2013-01-22 19:21:02Z

1

Remove the line containing myCommand.ExecuteNonQuery(). You are doing query!

Also, the labels (descriptions and links) will only contain the last data from the last row returned.

Adding text input directly to a SQL command allows a SQL injection attack. You should use sql parameters. See http://www.dotnetperls.com/sqlparameter

edited Jan 22, 2013 at 19:21

answered Jan 22, 2013 at 19:09

Richard Schneider

35.5k9 gold badges59 silver badges74 bronze badges

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

10 Comments

Danielle Neil Over a year ago

I have removed this, and still no luck?

Richard Schneider Over a year ago

Did the query return anything? Check mydata.HasRows.

Danielle Neil Over a year ago

What do you mean will only contain the last data from the last row. The statement I am using should return only one row so how can i get it to return just this data? Thanks.

Danielle Neil Over a year ago

Now im even more confused.. My msgbox wont show up during mydata.read() While mydata.Read() If mydata.HasRows = True Then MsgBox("Yes", MsgBoxStyle.OkCancel) Else MsgBox("No", MsgBoxStyle.OkCancel) End If labelDescription.Text = mydata.Item("description").ToString labelLinks.Text = mydata.Item("useful_links").ToString End While

Kiran1016 Over a year ago

yes,check the Query once again and see what it returns in the database

|

Collectives™ on Stack Overflow

Using SQL datareader to set variables to a label

1 Answer 1

10 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

10 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related