CODEWITHSUNDEEP
c#asp.netstored-procedures
Sue
Sue

Reputation: 47

how to update database table with listbox values using c#

I'm trying to update a database table with values from listbox as well as textbox values however and exception was thrown Exception.

Procedure or function expects parameter, which was not supplied.

thrown even after supplying the parameter. I wanted the courses selected to be shown in the same column of database table separated by comma. This is what I have so far.

 using (SqlCommand com = new SqlCommand("updateStudent", connection))
 {
        com.CommandType = CommandType.StoredProcedure;

        com.Parameters.AddWithValue("@studentNum", studentNum.Text);

        com.Parameters.Add("@studentName", SqlDbType.NVarChar).Value = studentName.Text;

        foreach (ListItem item in lstCourse.Items)
        {
             if (item.Selected)
             {
                   com.Parameters.Add("@courses", SqlDbType.NVarChar).Value = " " + item.Text + " ";
              }
         }
         com.ExecuteNonQuery();
         com.Parameters.Clear();
       }
   }

Upvotes: 1

Views: 280

Answers (3)

Vinit
Vinit

Reputation: 2607

Instead of foreach loop, you should do

// create comma separated list of selected courses
var courses = string.Join(",", lstCourse.Items
                                        .Where(item=>item.Selected)
                                        .Select(s=>s.Text)
                                        .ToList()); 
// add courses as parameter
com.Parameters.Add("@courses", SqlDbType.NVarChar).Value = courses;

UPDATE After reading through your one of the comments where you have shared the procedure code below.

update Student 
set studentNum=@studentNum,studentName=@studentName,courses=@courses 
where studentId=@studentId`

it seems you are not passing the @studentId as parameter. that might be the reason for error message

Procedure or function expects parameter, which was not supplied.

So you should add this parameter to sql command 

com.Parameters.Add("@studentId", SqlDbType.Int).Value = studentId; -- update the dbtype and variable as per your system.

Upvotes: 1

Mike
Mike

Reputation: 818

I think @Vinit had the right answer to do what you want to do. In place of your foreach (ListItem item in lstCourse.Items) you want this instead.

// create comma separated list of selected courses
var courses = string.Join(",", lstCourse.Items
                                    .Where(item=>item.Selected)
                                    .Select(s=>s.Text)
                                    .ToList()); 
// add courses as parameter
com.Parameters.Add("@courses", SqlDbType.NVarChar).Value = courses;

It builds the list of comma separated course names and then puts that into the command as one argument @courses.

However!! After having done this for around 20 years I can tell you you're not really going to like it this way. You're going to want the courses that the student selected to be in separate records. Each time you have to deal with these courses you're going to deal with this collection of course names. What I have done in this case is create a separate table to join Student to Course in a "Many to Many" relationship. BTW - I don't see the place in your code where you tell the command what SP to execute.

Mike

Upvotes: 1

Averis Ruiz
Averis Ruiz

Reputation: 26

The Error is normal

You can not send more than once a parameter with the same name for the invocation of a stored procedure.

If you need to save a list of courses to the student entity, you must change the structure of the database, and use a new table with a foreign key to the student table.

Check this documentation Foreing Key , Store Procedure

Upvotes: 1

Related Questions