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();
This question is related to
c#
sql
ado.net
sqldatareader
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.
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;"
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();
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;
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.
Source: Stackoverflow.com