im trying to retrieve data from access database..... simple search but getting error help me please

Question

0.00/5 (No votes)

See more:

C#

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.OleDb;

namespace data_retrive
{
    public partial class Form1 : Form
    {
        private OleDbConnection connection = new OleDbConnection();
        public Form1()
        {
            InitializeComponent();
            connection.ConnectionString = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Users\SunilK\Desktop\ISES-V4\DB.accdb;
Persist Security Info=False;";
        }

        private void Form1_Load(object sender, EventArgs e)
        {

        }

        private void button1_Click(object sender, EventArgs e)
        {
            DataTable dt = new DataTable();
            connection.Open();
            OleDbDataReader dr = null;
            OleDbCommand cmd = new OleDbCommand("SELECT [sub1], [sub2], [sub3], [sub4], [sub5] FROM Marks WHERE ([ID] = "+ textBox1.Text +") ", connection);
        
            dr = cmd.ExecuteReader();
            while (dr.Read())
            {
                textBox2.Text = (dr["sub1"].ToString());
                textBox3.Text = (dr["sub2"].ToString());
                textBox4.Text = (dr["sub3"].ToString());
               
            }
            connection.Close();
        }
    }
}

Posted 3-Apr-15 5:02am

Member 10632254

Updated 3-Apr-15 5:18am

PIEBALDconsult

v2

Add a Solution

Comments

PIEBALDconsult 3-Apr-15 11:22am

Always always always always always always always always always always always always use a parameterized statement!

And don't put data access code in with your UI (Form) code -- write a Data Access Layer class to handle data.

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

OriginalGriff · Answer 1 · 2015-04-03T05:17:00

Solution 1

Without knowing the exact error message, it's difficult to say, but the first thing to do is to stop concatenating strings to form an SQL command. It's dangerous, because it leaves you wide open to SQL Injection attacks which can accidentally or deliberately damage or destroy your database. You should always use parameterized queries:

C#

OleDbCommand cmd = new OleDbCommand("SELECT [sub1], [sub2], [sub3], [sub4], [sub5] FROM Marks WHERE [ID] = @ID", connection);
cmd.Parameters.AddWithValue("@ID", textBox1.Text);
dr = cmd.ExecuteReader();

There is a very good chance that that will cure the problem you are having at the moment as well - but if it doesn't, we need to know the exact error message and which line it is occurring on.

Posted 3-Apr-15 5:17am

OriginalGriff

Comments

Member 10632254 3-Apr-15 11:25am

getting error "No value given for one or more required parameters." in dr = cmd.ExecuteReader(); statement

OriginalGriff 3-Apr-15 11:34am

Did you add the ...AddWithValue... line?

Member 10632254 3-Apr-15 11:36am

yup i add addwithvalue line

DataTable dt = new DataTable();
connection.Open();
OleDbDataReader dr = null;
OleDbCommand cmd = new OleDbCommand("SELECT [sub1], [sub2], [sub3], [sub4], [sub5] FROM Marks WHERE [ID] = @ID ", connection);
cmd.Parameters.AddWithValue("@ID", textBox1.Text);
dr = cmd.ExecuteReader();
while (dr.Read())
{
textBox2.Text = (dr["sub1"].ToString());
textBox3.Text = (dr["sub2"].ToString());
textBox4.Text = (dr["sub3"].ToString());

}
connection.Close();

Member 10632254 3-Apr-15 11:45am

thanks a lot ... i got the answer ...... thanks again

OriginalGriff 3-Apr-15 11:48am

You're welcome!

Sascha Lefèvre · Answer 2 · 2015-04-03T05:18:00

If your ID-column is a string you would have to quote textBox1.Text:

C#

"SELECT [sub1], [sub2], [sub3], [sub4], [sub5] FROM Marks WHERE ([ID] = '"+ textBox1.Text +"')"

But: You shouldn't build your query-strings by concatenating the values as literals anyway.

Instead you should use SQL-Parameters. Please see one of my previous answers where I wrote an exemplary "good practice" database-access. I also explain there the advantages of SQL-Parameters:

how to loop sql server table to create a datagridview - sql table field matches csv field[^]

The code-formatting is a bit messed up there, unfortunately, because of slight bugs with this website. There's a <pre>-Tag in the code that doesn't belong there and the last line isn't part of the code-block any more.

Edit: copy&paste syndrome fixed (first sentence)