CODEWITHSUNDEEP
c#databasems-access
Jude Perera
Jude Perera

Reputation: 23

C# and Access Data Base Searching Via Name

Searching via the InqID is working properly but when I give the code to Search with InqName it gives me an error (All connections are given Properly I guess) Thanks.. ERROR DETAILS An unhandled exception of type 'System.Data.OleDb.OleDbException' occurred in System.Data.dll

Additional information: No value given for one or more required parameters.

private void btnSearch_Click(object sender, EventArgs e)
{
    DA.SelectCommand = new OleDbCommand("SELECT * FROM INQUIREt WHERE InqID=" +txtInqID.Text, CON);
    DS.Clear();
    DA.Fill(DS);
    dataGridView.DataSource = DS.Tables[0];

    CON.Open();
    DA.SelectCommand.ExecuteNonQuery();
    CON.Close();
}

private void btnNameSearch_Click(object sender, EventArgs e)
{
    DA.SelectCommand = new OleDbCommand("SELECT * FROM INQUIREt WHERE InqName=" + txtInqName.Text, CON);
    DS.Clear();
    DA.Fill(DS);
    dataGridView.DataSource = DS.Tables[0];

    CON.Open();
    DA.SelectCommand.ExecuteNonQuery();
    CON.Close();
}

Upvotes: 1

Views: 65

Answers (4)

Mairaj Ahmad
Mairaj Ahmad

Reputation: 14624

You need to surround the value of Name in quotes but it would be better to use Parameterized Sql.

DA.SelectCommand = new OleDbCommand("SELECT * FROM INQUIREt WHERE InqName=@Name " , CON);
DS.Clear();
DA.SelectCommand.Parameters.AddWithValue("@Name",txtInqName.Text);
DA.Fill(DS);
dataGridView.DataSource = DS.Tables[0];

Also you don't need below code as data is already selected using DataAdapter

CON.Open();
DA.SelectCommand.ExecuteNonQuery();
CON.Close();

Upvotes: 0

Cetin Basoz
Cetin Basoz

Reputation: 23827

Most of the answers tell you that you should use parameters and that is good. They also tell you that it would be correct if you included single quotes around it, which is incorrect. Adding single quotes is NOT a solution and would only work for some values (well many but not all) and is wide open to SQL injection attack.

There is only one way of doing it correct and that is to use parameters. With OleDb the parameters are NOT named but positional. With access however you can use named parameters by prefixing them with @.

private void btnSearch_Click(object sender, EventArgs e)
{
    DA.SelectCommand = new OleDbCommand("SELECT * FROM INQUIREt WHERE InqID=@ID", CON);
    DA.SelectCommand.Parameters.Add("@ID", OleDbType.VarChar).Value = txtInqID.Text;    
    DS.Clear();
    DA.Fill(DS);
    dataGridView.DataSource = null;
    dataGridView.DataSource = DS.Tables[0];
}

private void btnNameSearch_Click(object sender, EventArgs e)
{
    DA.SelectCommand = new OleDbCommand("SELECT * FROM INQUIREt WHERE InqName=@name", CON);
    DA.SelectCommand.Parameters.Add("@name", OleDbType.VarChar).Value = txtInqName.Text;
    DS.Clear();
    DA.Fill(DS);
    dataGridView.DataSource = null;
    dataGridView.DataSource = DS.Tables[0];
}

PS: You are using DataSet and DataAdapter in a weird way, but that is acceptable and works.

Upvotes: 2

Alex
Alex

Reputation: 1461

You are missing quotes that enclose your txtInqID.Text and InqName.Text values in your query:

DA.SelectCommand = new OleDbCommand("SELECT * FROM INQUIREt WHERE InqID = '" + txtInqID.Text + "'", CON);

DA.SelectCommand = new OleDbCommand("SELECT * FROM INQUIREt WHERE InqName = '" + txtInqName.Text + "'", CON);

Also building dynamically your query string like that is a very bad practice. Consider using command parameters instead:

DA.SelectCommand = new OleDbCommand("SELECT * FROM INQUIREt WHERE InqID = ?", CON);
DA.SelectCommand.Parameters.Add("InqID").Value = txtInqID.Text;

DA.SelectCommand = new OleDbCommand("SELECT * FROM INQUIREt WHERE InqName = ?" , CON);
DA.SelectCommand.Parameters.Add("InqName").Value = txtInqName.Text;

Upvotes: 0

Mikhail Tulubaev
Mikhail Tulubaev

Reputation: 4261

You are not specifying the search string parameter, you should add quotes as follows:

DA.SelectCommand = new OleDbCommand("SELECT * FROM INQUIREt WHERE InqName='" + txtInqName.Text + "'", CON);

However, constructing the query string as string is not an effective and readable way, the good practice is to use OleDbParameters as follows:

DA.SelectCommand = new OleDbCommand("SELECT * FROM INQUIREt WHERE InqName=?", CON);
DA.SelectCommand.Parameters.AddWithValue("?", txtInqName.Text);

Upvotes: 0

Related Questions