CODEWITHSUNDEEP
c#asp.netoledb
Tariq Mehmood
Tariq Mehmood

Reputation: 3

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;

Upvotes: 0

Views: 2780

Answers (6)

Carmelo La Monica
Carmelo La Monica

Reputation: 765

see if this example can help you,

Regards.

Upvotes: 0

Prabu
Prabu

Reputation: 11

Use this query to retrieve data related to user search

"Select playerBatStyle from Player where Player like %'"+s+"'";

Upvotes: 0

ShellyFM
ShellyFM

Reputation: 561

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(); 
}

Upvotes: 1

James Johnson
James Johnson

Reputation: 46077

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);

Upvotes: 1

Haris Hasan
Haris Hasan

Reputation: 30127

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

Upvotes: 0

rlb.usa
rlb.usa

Reputation: 15041

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

Upvotes: 0

Related Questions