How do I do a SQL from a textbox and display in a datagridview?
Here is the code snippet
string search = textBox1.Text;
int s = Convert.ToInt32(search);
string conn="Provider=Microsoft.ACE.OLEDB.12.0;Data Source=E:\\Data.accdb";
string query="SELECT playerBatStyle FROM Player where playerID='" + s +开发者_如何学运维 ";
OleDbDataAdapter dAdapter=new OleDbDataAdapter (query ,conn );
OleDbCommandBuilder cBuilder=new OleDbCommandBuilder (dAdapter );
DataTable dTable=new DataTable ();
dAdapter .Fill (dTable );
dataGridView1.DataSource = dTable;
You had an unclosed single quote in your where clause. Try this instead:
string query = String.Format("SELECT playerBatStyle FROM Player where playerID={0}", s);
As the others have mentioned, s is of type int so quotes are not needed in the query and you do need the databind line.
Also, if you're not already, you'll want to check that a value actually exists in the text box before attempting to convert it to an integer. You don't need the OleDbCommandBuilder as the DataAdapter is handling the command internally as the SelectCommand property. Definitely consider using a parameterized query, which will reduce sql injection vulnerabilities.
The below combines my suggestions:
if (textBox1.Text != "")
{
string search = textBox1.Text;
int s = Convert.ToInt32(search);
string conn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\\Local Docs\\Temp\\Data.accdb";
string query = "SELECT playerBatStyle FROM Player where playerID=@playerID";
OleDbDataAdapter dAdapter = new OleDbDataAdapter(query, conn);
dAdapter.SelectCommand.Parameters.AddWithValue("@playerID", s);
DataTable dTable = new DataTable();
dAdapter.Fill(dTable);
dataGridView1.DataSource = dTable;
dataGridView1.DataBind();
}
The code you posted looks okay. A few corrections though:
...
//fix lots of missing quotation marks
string query="SELECT playerBatStyle FROM Player where playerID='" + s + "' ";
...
dataGridView1.DataBind(); //yes, we should call DataBind
PlayerID
looks int
type to me which means you don't need to put single quotes around it
string query = "SELECT playerBatStyle FROM Player where playerID=" + s + ";
At the end you would have to do dataGridView1.DataBind();
if you want to show results in DataGridView
On the side note it is always recommended to user parametrized query instead of concatinating values in the query, it's not safe
Use this query to retrieve data related to user search
"Select playerBatStyle from Player where Player like %'"+s+"'";
see if this example can help you,
Regards.
精彩评论