DB Access search textbox

Question

StudentID > Number

StudentName > Text

StudentCNIC > Text

StudentDOB > Date/Time

I am trying to create a search textbox where the results are show in of the text boxes. I have a named a button calling it FindBtn where user enters of either StudentID, Student Name or Student CNCI (just pain numbers). The results are then shown in the textboxes...StudIDTb (displaying Student ID), StudNameTb (displaying Student Name), StudCNCITb (displaying Student CNCI) and StudDOBTb (displaying Students DOB).

A lot of examples I have seen on the internet uses gridview but I am using textboxes to show results. The ones I have found of interest are...

here here here and here

    public Form1()
    {
        InitializeComponent();
        //Connection String for Access 2003. 
        myCon = new OleDbConnection(@" Provider=Microsoft.Jet.OLEDB.4.0;Data 
        Source=C....\StudDB.mdb ");
    }

    private void Form1_Load(object sender, EventArgs e)
    {

        this.studentsTableAdapter.Fill(this.studDBDataSet.Students);
    }

In the FindBtn function have done the following....

    private void FindBtn_Click(object sender, EventArgs e)
    {
        string title = textBox1.Text.ToString();
        if (title != "")
        {
            string queryString = "SELECT * FROM Students + title;
            //I need help here
            StudIDTb.Text = queryString;
            StudNameTb.Text = queryString;
            StudCNCITb.Text = queryString;
            StudDOBTb.Text = queryString;
        }
        else
            MessageBox.Show("Please try again\nError: ");
    }

The results shows ....

SELECT *
FROM Students 103

in each of those textboxes and exe freezes so obviously it is wrong. Please can someone help me here, thanks in advance.

UPDATE 1 I have included OleDbCommand, CommandType and opening connection just before string variable which prevents the screen from freezing but it does not solve the problem

UPDATE 2 Found something I like but it is not working Here

Update 3

As C Sharp Corner, kindly provided a piece of code....

 private void btnFind_Click(object sender, EventArgs e)
    {
        string title = textBox1.Text.ToString();
        string queryString = "SELECT * FROM Students" + title;
        OleDbCommand command = new OleDbCommand();
        command.CommandText = queryString;

        command.Connection = myCon;
        myCon.Open();

        OleDbDataReader dr = command.ExecuteReader();
        while (dr.Read())
        {
             StudIDTb.Text += String.Format("Student ID:                
             {0}\n",dr["StudID"].ToString());
            StudNameTb.Text += String.Format("StudentName ID: {0}\n", 
            dr["StudentName"].ToString());
            StudCNCITb.Text += String.Format("StudentCNIC: {0}\n", 
            dr["StudentCNIC"].ToString());
            StudDOBTb.Text += String.Format("StudentCNIC: {0}\n", 
            dr["StudentCNIC"].ToString());
        }
        myCon.Close();

I am receiving a error after amending formatting....

"The Microsoft Jet database engine cannot find the input table or query 'Students101'. Make sure it exists and that its name is spelled correctly".

I am looking at it now to find out what it means

The error is because your query is wrong, you should include your parameters in the query something like: queryString = "SELECT * From Students where StudentName = " + txtStudentNameTb.Text + " or StudentCNIC = " + txtStudentCNIC.Text But this isn't the "safe" way, I don't know much about querys etc, but you should google for something about parameterized querys, there should be plenty of explanations — Random IT Guy, Mar 09 '13 at 20:04

score 2 · Accepted Answer · answered Mar 09 '13 at 18:52

2

Here's an example that should work (also if you included oledbcommand, commandtype etc update your code too so we can see what you changed):

OleDbCommand command = new OleDbCommand();
command.CommandText = queryString;
command.Connection = myCon;
myCon.Open();
OleDbDataReader dr = command.ExecuteReader();
while(dr.Read())
{
   StudIDTb.Text += dr["StudID"].ToString();
   StudNameTb.Text += dr["StudName"].ToString();
   StudCNCITb.Text += dr["StudCNCI"].ToString();
   StudDOBTb.Text += dr["StudDOB"].ToString();
}
myCon.Close();

answered Mar 09 '13 at 18:52

Random IT Guy

625
1
8
16

I didn't include formatting so you could add something like: StudIDTb.Text += String.Format("Student ID: {0}\n",dr["StudID"].ToString()); – Random IT Guy Mar 09 '13 at 19:36
@bucketblast refer to this thread for parameterized queries: http://stackoverflow.com/a/11910114/2019288 and this thread for how to use basic queries http://msdn.microsoft.com/en-us/library/bb264565(v=sql.90).aspx and this one for and/or since you have multiple parameters http://www.sql-tutorial.net/SQL-AND-OR.asp – Random IT Guy Mar 09 '13 at 20:07
Thank you for your valuable time. BTW did I code correctly in update 3? – bucketblast Mar 09 '13 at 20:08
@bucketblast As far as I can see yes, except the query. And I'm glad to help since I was just testing with a similar example, althought I don't see wy you don't use a gridview which is easier and more organised.. – Random IT Guy Mar 09 '13 at 20:16

DB Access search textbox

1 Answers1