How to get data from database to textbox on VB.net

Question

hi im new in Visual basic. I have a button that when its clicked, its gonna find the student via their ID inputted by the user and its gonna output the data to the textfields. I'm not pretty sure if im doing this right. because i'm getting this error [image] >> http://img812.imageshack.us/img812/7650/gq0z.png

btw here's my code so far. can someone help me please? thanks!

        cmd.CommandText = "Select * from Student where Student_id = '" & id.Text & "'"
        cmd.Connection = db

        dr = cmd.ExecuteReader

        Try
            dr.Read()
            id.Text = dr.GetValue(0)
            Lname.Text = dr.GetValue(1)
            Fname.Text = dr.GetValue(2)
            Mname.Text = dr.GetValue(3)
            datet.Text = dr.GetValue(4)
            age.Text = dr.GetValue(5)
            male.Text = dr.GetValue(6)
            female.Text = dr.GetValue(7)
            status.Text = dr.GetValue(8)
            staddress.Text = dr.GetValue(9)
            cityAdd.Text = dr.GetValue(10)
            dr.Close()

        Catch ex As Exception
            MsgBox("" + ex.Message)
            dr.Close()
        End Try

if id.Text is empty you will get an error; you will also want to convert the text it contains to a number before you build the SQL. In the future post the actual error message (they are important info) rather than a link to a picture — Ňɏssa Pøngjǣrdenlarp
– Ňɏssa Pøngjǣrdenlarp, Commented Oct 28, 2013 at 18:44
I would recommend a : If NOT isdbnull(dr.getvalue(0))... for each one obviously incrementing the getvalue number for each one. — logixologist
– logixologist, Commented Oct 28, 2013 at 18:46
Unsolicited Advice: go ahead and change dr.getvalue(n) to dr.getvalue("nameOfColumn"). Its a better habit. — logixologist
– logixologist, Commented Oct 28, 2013 at 18:47
or change that to dr.item("nameofcolumn") a bit shorter... see answer below — Justin E
– Justin E, Commented Oct 28, 2013 at 18:52

Justin E · Accepted Answer · 2013-10-28 18:49:27Z

4

cmd.CommandText = "Select * from Student where Student_id = '" & id.Text & "'"

change to:

if IsNumeric(id.text) Then
cmd.CommandText = "Select * from student where Student_id=@p1"
cmd.Prepare
cmd.Parameters.AddWithValue("@p1", id.text)
dr = cmd.ExecuteReader
....
Else
Exit Sub
End If

You can do it this way, or

 dr = cmd.ExecuteReader

    Try
       with dr
        .Read()
        id.Text = .GetValue(0)
        end with
        dr.Close()

or

with dr
    .read
    id.text = .item("id")
    .close

easier to read....

answered Oct 28, 2013 at 18:49

Justin E

1,25016 silver badges30 bronze badges

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

Comments

Evaldas Buinauskas · Accepted Answer · 2016-01-05 08:36:50Z

1

First add a reference, if you are using MySQL database put it bet. the class

Dim Connection As MySqlConnection
Dim command As MySqlCommand

Put this to your textbox

Connection = New MySqlConnection
Connection.ConnectionString = "Server=localhost;port=3306;userid=root;password=root;database=databasename"
Dim reader As MySqlDataReader

the root is default

Try
  Connection.Open()
  Dim query As String
  query= "Select * from Databasename.tablename where fieldname='" & textbox1.text & "'"
  Command = New MySqlCommand(query, Connection)
  reader = Command.ExecuteReader
  While reader.Read
    Dim sname As String
    sname = reader.GetString("Fieldname")
    textbox1.Items.Add(sname)
  End While
  Connection.Close()
Catch e MySqlException
  MsgBox (ex.Message)
Finally
  Connection.Dispose
End Try

edited Jan 5, 2016 at 8:36

Evaldas Buinauskas

14.2k11 gold badges63 silver badges119 bronze badges

answered Jan 5, 2016 at 8:32

user5746814

111 bronze badge

Comments

Stephen Rauch · Accepted Answer · 2018-02-27 02:43:38Z

0

If you are using Mysql Database, first add reference. To add reference of MySql Database

Go to your solution explorer and rightclick on your project name
Find add->references

a window will be opened
In that window,under Assemblies select framework.
On right side there will be a list find and select Microsoft.VisualBasic.Compatability.Data
In extensions, find and add MySql.Data and MSDATASRC
Click OK

edited Feb 27, 2018 at 2:43

Stephen Rauch♦

50.1k32 gold badges118 silver badges143 bronze badges

answered Feb 27, 2018 at 2:19

NARENDER REDDY

1111 silver badge4 bronze badges

Collectives™ on Stack Overflow

How to get data from database to textbox on VB.net

3 Answers 3

Comments

Comments

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Linked

Related