How can I get a value from a table in SQL server to text box in visual stidio using C#

Question

1.00/5 (2 votes)

See more:

I am trying to get data from database tables that have several rows and columns, in SQL server to text boxes in Visual Studio form using C#. When I write the code:

C#

        con.Open();
SqlCommand cmd1 = new SqlCommand("Select * from Table1", con);
        SqlDataReader dr1 = cmd1.ExecuteReader();

        while (dr1.Read())
        {
           textBox1.Text = dr1[0].ToString();
           textBox2.Text = dr1[2].ToString();
        }
        con.Close();

I can get the data from the last row of first column and second column into textBox1 and textBox2. If I need to get a value that is in the 3rd row and 5th column, how can I write the code?

What I have tried:

I am trying something like:
textBox1.Text = dr1[4].Rows[2].ToString();
I get build error - error CS1061: 'object' does not contain a definition for 'Rows' and no extension method 'Rows' accepting a first argument of type 'object' could be found (are you missing a using directive or an assembly reference?)
Can anyone please help me to write correctly? Thanks.

Posted 3-Jun-16 5:24am

Savi kolla

Updated 3-Jun-16 6:11am

Add a Solution

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Michael_Davies · Accepted Answer · 2016-06-03T05:36:00

The reader positions at the next row when you dr1.read() so the first dr1.read presents you with the first row, you cannot access subsequent rows without moving the read on to those rows.

It might be better to get you select to return just the information you need, how do you know it is the third row?

To make a simple example to meet your question the following will do it:

C#

dr1.Read();
           textBox1.Text = dr1[0].ToString();
           textBox2.Text = dr1[2].ToString();
dr1.read();
dr1.read();
           textBox3.Text = dr1[4].ToString();

The flaw in the example is obvious, you must know that you returned at least three rows or test each read for success.

George Jonsson · Accepted Answer · 2016-06-03T06:11:00

You can also use the SqlDataAdapter class and fill a DataTable with the returned values.
For more information see MSDN SqlDataAdapter Class (System.Data.SqlClient)[^]

The code in your case would be something like this:

C#

con.Open();
SqlCommand cmd1 = new SqlCommand("Select * from Table1", con);
SqlDataAdapter adapter = new SqlDataAdapter();
adapter.SelectCommand = cmd1;
DataTable dataTable = new DataTable();
adapter.Fill(dataTable);
con.Close();

if ((dataTable.Rows.Count > 0) && (dataTable.Columns.Count > 2))
{
    textBox1.Text = dataTable.Rows[0][0].ToString();
    textBox2.Text = dataTable.Rows[0][2].ToString();
}
if ((dataTable.Rows.Count > 2) && (dataTable.Columns.Count > 4))
{
    textBox3.Text = dataTable.Rows[2][4].ToString();
}

A better way would be to used named columns in the query.

C#

con.Open();
SqlCommand cmd1 = new SqlCommand("Select Column1, Column3, Columns5 from Table1", con);
SqlDataAdapter adapter = new SqlDataAdapter();
adapter.SelectCommand = cmd1;
DataTable dataTable = new DataTable();
adapter.Fill(dataTable);
con.Close();

if (dataTable.Rows.Count > 0)
{
    textBox1.Text = dataTable.Rows[0]["Column1"].ToString();
    textBox2.Text = dataTable.Rows[0]["Column3"].ToString();
}
if (dataTable.Rows.Count > 2)
{
    textBox3.Text = dataTable.Rows[2]["Column5"].ToString();
}

The column names are of course only examples, it should be names like Id, Name, CurrentDate or similar.

How can I get a value from a table in SQL server to text box in visual stidio using C#

2 solutions

Solution 1

Solution 2

Add your solution here

Preview 0