Error mgs: No value given for one or more required parameters: rgds to multiple parameters

Question

Access 2003 VS 2010 C#

I am having problems with my WHERE clause. Initially I used the OR but now realized it must be something to do with the WHERE that I am not doing something right or is it something else? Please can someone help me here. Thanks in advance. ps c sharp student helped me previously.

StudentTable

Field Name Data Type

 ID         Number 
 FirstName  Text
 LastName   Text
 StudentDOB Date/Time
 Course     Text

connection string:

myCon = new OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\...\StudentDB.mdb");

btnFind method...

private void btnFind_Click(object sender, EventArgs e)
    {
        string title = txtSearch.Text.ToString();
        string queryString = "SELECT * FROM StudentTable WHERE ID = ?"; //I have also tried WHERE ID = @ID without success

        OleDbCommand command = new OleDbCommand();
        command.CommandText = queryString;
        command.Connection = myCon;
        myCon.Open();
        OleDbDataReader dr = command.ExecuteReader();
        while (dr.Read())
        {
            txtStudID.Text += String.Format("ID: {0}\n", dr["ID"].ToString());
            txtFirstName.Text += String.Format("FirstName: {0}\n", dr["FirstName"].ToString());
            txtLastName.Text += String.Format("LastName: {0}\n", dr["LastName"].ToString());
            txtStudDOB.Text += String.Format("StudentDOB: {0}\n", dr["StudentDOB"].ToString());
            txtCourse.Text += String.Format("Course: {0}\n", dr["Course"].ToString());

        }
        myCon.Close();

    }

My delete method is this and there are no problems(I thought you may like to know)...

 private void btnDelete_Click(object sender, EventArgs e)
    {
        OleDbCommand cmd = new OleDbCommand();
        cmd.CommandType = CommandType.Text;

        cmd.CommandText = "DELETE FROM StudentTable WHERE txtStudID = ?";
        cmd.Parameters.AddWithValue("@ID", txtStudID.Text);
        cmd.Parameters.AddWithValue("@FirstName", txtFirstName.Text);
        cmd.Parameters.AddWithValue("@LastName", txtLastName.Text);
        cmd.Parameters.AddWithValue("@StudDOB", txtStudDOB.Text);
        cmd.Parameters.AddWithValue("@Course", txtCourse.Text);

        cmd.Connection = myCon;
        myCon.Open();
        cmd.ExecuteNonQuery();
        myCon.Close();

    }

I tested sql query in Access without problems...

SELECT StudentTable.ID
FROM StudentTable
WHERE (((StudentTable.ID)=1));

Solution Found

I found a solution from this link, from stackoverflow of course....

here

Thank you everyone for their valuable time especially Brad.

Brad - here is the code

private void btnFind_Click(object sender, EventArgs e)
    {

        using (var command = myCon.CreateCommand())
        {
            command.CommandText = "select * from StudentTable where ID = @ID";
            command.Parameters.AddWithValue("ID", int.Parse(txtSearch.Text));

            myCon.Open();
            var reader = command.ExecuteReader();

            while (reader.Read())
            {
                txtStudID.Text = reader["ID"].ToString();
                txtFirstName.Text = reader["FirstName"].ToString();
                txtLastName.Text = reader["LastName"].ToString();
                txtStudDOB.Text = reader["StudentDOB"].ToString();
                txtCourse.Text = reader["Course"].ToString();

            }
        }       myCon.Close();
                myCon.Close();

      }

I may not use OR clause actually - this will do.

@Melaine - problem is am receving error message "No value given for one or more required parameters2 — bucketblast, Mar 12 '13 at 20:08
atID - worked. I used atID with your code and had no effect. For whatever reason I am unable to use at symbol in comment area. I will update at the top — bucketblast, Mar 13 '13 at 19:38

Brad · Answer 1 · 2013-03-12T20:37:54.317

0

You don't add the parameters in your Select

command.Parameters.AddWithValue("@ID", SqlDbType.Int);
command.Parameters["@ID"].Value = int.Parse(txtStudID.Text);

Also, you have a lot of parameters in your Delete but only use one of them. Was that a transcription error or do @FirstName, @LastName, @StudDOB and @Course actually go unused? @ID only happens to be used correctly because it is the first one.

edited Mar 12 '13 at 20:37

answered Mar 12 '13 at 20:06

Brad

11,934
4
45
73

Are you referring to btnFind as well? – bucketblast Mar 12 '13 at 20:14
Yeah, this code should go after `command.CommandText = queryString;` in `btnFind_Click` – Brad Mar 12 '13 at 20:16
No. Output is for example on textbox named txtStudID I enter student ID 2 the out is showing...1ID: 1 – bucketblast Mar 12 '13 at 20:31
@bucketblast Try again with that edit. I bet that is a data conversion issue. Make sure you are passing in the right datatype to your ID – Brad Mar 12 '13 at 20:38
same output. My datatype is Number for ID? I am not sure how to pass the data type unless you have provided above – bucketblast Mar 12 '13 at 20:40
let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/26058/discussion-between-brad-and-bucketblast) – Brad Mar 12 '13 at 20:42

score 0 · Answer 2 · answered Mar 12 '13 at 20:06

0

Your SQL query contains a parameter marker (the ?) but you never specified a value for it. Your delete method does it correctly (calling AddWithValue on the Parameters collection) but even there, you're adding way too many parameters to your query.

You need to become more familiar with OLEDBparameterized queries. You should start here and check out the examples.

answered Mar 12 '13 at 20:06

Michael Edenfield

28,070
4
86
117

I should also mention that the OleDbCommand object doesn't pay any attention to the names of your parameters, it only cares about the order you add them. – Michael Edenfield Mar 12 '13 at 20:08
User enters a value of either Student ID, FirstName or LastName. – bucketblast Mar 12 '13 at 20:10
That's fine, but you need to tell *Access* what value they entered so it can find the correct records. – Michael Edenfield Mar 12 '13 at 20:11
you mean this...cmd.Parameters.AddWithValue etc – bucketblast Mar 12 '13 at 20:13
@bucketblast how do you deal with the logic of deciding to use `ID`, `FirstName` or `LastName`? – Brad Mar 12 '13 at 20:17
@Brad - I wanted the user to enter either ID, FirstName or LastName and then edit the record. ID is PK – bucketblast Mar 12 '13 at 20:21

score 0 · Answer 3 · answered Mar 13 '13 at 18:24

0

Solution found as updated in bold heading solution found. I am receiving a msg saying I can close this thread tomorrow.

answered Mar 13 '13 at 18:24

bucketblast

437
4
14
37

Error mgs: No value given for one or more required parameters: rgds to multiple parameters

3 Answers3