How do I get values from a SQL database into textboxes using C#?

Question

I'm creating a booking management system and I am having problems trying to get data from a SQL database and insert into a group of textboxes of my application.

I want to show the customer details, when a button is clicked, in a DataGridView, but when I click the button, the application throws an exception with the following error message;

Invalid attempt to read when no data is present.

I have attached a screenshot of the screen where I want to view customer details, and the code for the button, which will eventually show customer details in the respective textboxes. Any help would be greatly appreciated!

    SqlConnection sc = new SqlConnection("Data Source=localhost;Initial Catalog=LoginScreen;Integrated Security=True");
        SqlCommand com = new SqlCommand();
        com.Connection = sc;
        sc.Open();
        SqlDataReader read = (null);
        com.CommandText = ("select * from Pending_Tasks");
        read = com.ExecuteReader();
        CustID.Text = (read["Customer_ID"].ToString());
        CustName.Text = (read["Customer_Name"].ToString());
        Add1.Text = (read["Address_1"].ToString());
        Add2.Text = (read["Address_2"].ToString());
        PostBox.Text = (read["Postcode"].ToString());
        PassBox.Text = (read["Password"].ToString());
        DatBox.Text = (read["Data_Important"].ToString());
        LanNumb.Text = (read["Landline"].ToString());
        MobNumber.Text = (read["Mobile"].ToString());
        FaultRep.Text = (read["Fault_Report"].ToString());
        sc.Close();

You're not reading anything! You execute it, but you don't iterate through the reader! Use while(read.Read()) - please note, if there are more than 1 set of results, only the last set will be saved. — Dave
– Dave, Commented May 15, 2013 at 12:33
Check out this link over here codeproject.com/Questions/155444/… — Mingebag
– Mingebag, Commented May 15, 2013 at 12:33
do you have any date in your table Pending_tasks? if yes then you should first do read.Read() and then assign values — Ehsan
– Ehsan, Commented May 15, 2013 at 12:34
You can also wrap the connection in a using block, to ensure it gets closed. Some folks recommend still doing the close in finally. — DOK
– DOK, Commented May 15, 2013 at 12:36

Alberto Solano · Accepted Answer · 2013-12-11 11:37:31Z

9

The line reader.Read() is missing in your code. You should add it. It is the function which actually reads data from the database:

string conString = "Data Source=localhost;Initial Catalog=LoginScreen;Integrated Security=True";
SqlConnection con = new SqlConnection(conString);

string selectSql = "select * from Pending_Tasks";
SqlCommand com = new SqlCommand(selectSql, con);

try
{
    con.Open();

    using (SqlDataReader read = cmd.ExecuteReader())
    {
        while(reader.Read())
        {
            CustID.Text = (read["Customer_ID"].ToString());
            CustName.Text = (read["Customer_Name"].ToString());
            Add1.Text = (read["Address_1"].ToString());
            Add2.Text = (read["Address_2"].ToString());
            PostBox.Text = (read["Postcode"].ToString());
            PassBox.Text = (read["Password"].ToString());
            DatBox.Text = (read["Data_Important"].ToString());
            LanNumb.Text = (read["Landline"].ToString());
            MobNumber.Text = (read["Mobile"].ToString());
            FaultRep.Text = (read["Fault_Report"].ToString());
        }
    }
}
finally
{
    con.Close();
}

EDIT : This code works supposing you want to write the last record to your textboxes. If you want to apply a different scenario, like for example to read all the records from database and to change data in the texboxes when you click the Next button, you should create and use your own Model, or you can store data in the DataTable and refer to them later if you wish.

edited Dec 11, 2013 at 11:37

Alberto Solano

8,2553 gold badges40 silver badges62 bronze badges

answered May 15, 2013 at 12:34

Adil Mammadov

8,7164 gold badges35 silver badges65 bronze badges

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

3 Comments

David S. Over a year ago

If there are multiple rows, this will overwrite the textboxes until the last row is read.

Adil Mammadov Over a year ago

yes I know this. he can either write first info to textboxes or last one.

Adil Mammadov Over a year ago

I am glad that it worked for you. But keep in mind that it will overwrite records in textboxes if there are more than one row. Thanks @DavidS. for commenting

Michael Yaworski · Accepted Answer · 2013-12-11 00:22:09Z

using (SqlConnection connection =  new SqlConnection("Data Source=localhost;Initial Catalog=LoginScreen;Integrated Security=True"))
{
    SqlCommand command =
    new SqlCommand("select * from Pending_Tasks WHERE CustomerId=...", connection);
    connection.Open();

    SqlDataReader read= command.ExecuteReader();

    while (read.Read())
    {
        CustID.Text = (read["Customer_ID"].ToString());
        CustName.Text = (read["Customer_Name"].ToString());
        Add1.Text = (read["Address_1"].ToString());
        Add2.Text = (read["Address_2"].ToString());
        PostBox.Text = (read["Postcode"].ToString());
        PassBox.Text = (read["Password"].ToString());
        DatBox.Text = (read["Data_Important"].ToString());
        LanNumb.Text = (read["Landline"].ToString());
        MobNumber.Text = (read["Mobile"].ToString());
        FaultRep.Text = (read["Fault_Report"].ToString());
    }
    read.Close();
}

Make sure you have data in the query : select * from Pending_Tasks and you are using "using System.Data.SqlClient;"

David S. · Accepted Answer · 2013-05-15 12:39:55Z

2

read = com.ExecuteReader()

SqlDataReader has a function Read() that reads the next row from your query's results and returns a bool whether it found a next row to read or not. So you need to check that before you actually get the columns from your reader (which always just gets the current row that Read() got). Or preferably make a loop while(read.Read()) if your query returns multiple rows.

edited May 15, 2013 at 12:39

answered May 15, 2013 at 12:33

David S.

6,1332 gold badges45 silver badges82 bronze badges

1 Comment

David S. Over a year ago

@EhsanUllah yes OP should restrict the query on primary key or store results from each Read() in another fashion

Pang · Accepted Answer · 2016-10-26 06:32:22Z

0

If you want to display single value access from database into textbox, please refer to the code below:

SqlConnection con=new SqlConnection("connection string");
SqlCommand cmd=new SqlConnection(SqlQuery,Con);
Con.Open();
TextBox1.Text=cmd.ExecuteScalar();
Con.Close();

or

SqlConnection con=new SqlConnection("connection string");
SqlCommand cmd=new SqlConnection(SqlQuery,Con);
Con.Open();
SqlDataReader dr=new SqlDataReadr();
dr=cmd.Executereader();
if(dr.read())
{
    TextBox1.Text=dr.GetValue(0).Tostring();
}
Con.Close();

edited Oct 26, 2016 at 6:32

Pang

10.2k146 gold badges87 silver badges126 bronze badges

answered Oct 26, 2016 at 6:20

Manoj Niakm

1

Comments

beachgreatsquaremoon · Accepted Answer · 2017-02-28 07:13:01Z

Make a connection and open it.

con = new OracleConnection("Data Source=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=localhost)(PORT=1521)))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=<database_name>)));User Id =<userid>; Password =<password>");
con.Open();

Write the select query:

string sql = "select * from Pending_Tasks";

Create a command object:

OracleCommand cmd = new OracleCommand(sql, con);

Execute the command and put the result in a object to read it.

OracleDataReader r = cmd.ExecuteReader();

now start reading from it.

while (read.Read())
{
 CustID.Text = (read["Customer_ID"].ToString());
 CustName.Text = (read["Customer_Name"].ToString());
 Add1.Text = (read["Address_1"].ToString());
 Add2.Text = (read["Address_2"].ToString());
 PostBox.Text = (read["Postcode"].ToString());
 PassBox.Text = (read["Password"].ToString());
 DatBox.Text = (read["Data_Important"].ToString());
 LanNumb.Text = (read["Landline"].ToString());
 MobNumber.Text = (read["Mobile"].ToString());
 FaultRep.Text = (read["Fault_Report"].ToString());
}
read.Close();

Add this too using Oracle.ManagedDataAccess.Client;

Collectives™ on Stack Overflow

How do I get values from a SQL database into textboxes using C#?

5 Answers 5

3 Comments

Comments

1 Comment

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

3 Comments

Comments

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related