getting values from sql reader c# – This article will take you through the common SQL errors that you might encounter while working with c#, sql, sql. The wrong arrangement of keywords will certainly cause an error, but wrongly arranged commands may also be an issue. SQL keyword errors occur when one of the words that the SQL query language reserves for its commands and clauses is misspelled. If the user wants to resolve all these reported errors, without finding the original one, what started as a simple typo, becomes a much bigger problem.
SQL Problem :
I have the following code:
SqlConnection c = new SqlConnection("Data Source=localhost\sqlexpress;Initial Catalog=BookStoreDataBase1;Integrated Security=True;Pooling=False;");
c.Open();
string raf = string.Format("Select Id from Customer WHERE email='{0}'", DropDownList1.SelectedItem.Text);
SqlCommand comm2 = new SqlCommand(raf, c);
SqlDataReader r = comm2.ExecuteReader();
The object r now has the value of the query which is a row contains that the Id where email equals to random value from drop down list.
what I want is to get the exact value of that “Id” and assign it to label. please help me.
Solution :
First of all your query is open to SQL Injection attack so change it like this:-
string raf = "Select Id from Customer WHERE email= @Email";
SqlCommand comm2 = new SqlCommand(raf, c);
cmoo2.Parameters.Add("@Email",SqlDbType.NVarchar,20).Value =
DropDownList1.SelectedItem.Text;
You are fetching just one value so it can be done use ExecuteScalar like this:-
labelid.Text= cmoo2.ExecuteScalar.ToString();
But If you want to use SqlDataReader object then it will return the value when you call the Read method:-
using(SqlDataReader reader= cmoo2.ExecuteReader())
{
while (reader.Read())
{
labelid.Text= reader["Id"].ToString();
}
}
There are lot of examples already in stack overflow regarding this topic you can refer any of that answers or try this
using(SqlDataReader r= cmd.ExecuteReader())
{
while (r.Read())
{
var myString = r.GetString(0); //The 0 stands for "the 0'th column", so the first column of the result.
labelid.Text=myString;
}
}
Finding SQL syntax errors can be complicated, but there are some tips on how to make it a bit easier. Using the aforementioned Error List helps in a great way. It allows the user to check for errors while still writing the project, and avoid later searching through thousands lines of code.