开发者

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.

0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜