using store procedure to insert selected checkboxlist values into database

Mamphekgo Bahula

Hi I created store produce to insert selected values from checkboxlist into sql database and it gives me this error "Procedure or function SaveAdmin has too many arguments specified." here is my store procedure ALTER PROCEDURE [dbo].[SaveAdmin] -- Add the parameters for the stored procedure here @Perno int, @Client_id int, @Region_id int, @Service_id int AS INSERT INTO Admin(Perno, Client_id, Region_id, Service_id) VALUES(@Perno, @Client_id, @Region_id, @Service_id) and here is my vb.net code cmdStandby.CommandText = "SaveAdmin" cmdStandby.CommandType = CommandType.StoredProcedure cmdStandby.Connection = sqlstandby cmdStandby.Parameters.AddWithValue("@Perno", lblEmpNo.Text) cmdStandby.Parameters.AddWithValue("@Client_id", ddlClient1.SelectedValue) For Each li In ChkRegion.Items If li.Selected = True Then cmdStandby.Parameters.AddWithValue("@Region_id", li.Value) End If Next For Each li In ChkService.Items If li.Selected = True Then cmdStandby.Parameters.AddWithValue("@Service_id", li.Value) End If Next cmdStandby.ExecuteNonQuery() thanx in advance

Mamphekgo

TheComputerMan

Hi, I have never used add with value but I assume it adds the parameter to the collection as well as adding the value. Try using .value = Even if that worked you would still only get one 'set'passed through as your loops are replacing the value each iteration. Your executenonquery needs to be inside the loop, and the loop needs to add each type of value to the set. Looking at what you are trying to do I suspect that you should be normalising the database a bit more, unless you want each row to contain the employee and client data for each service ID and region ID Sort of like: (I don't think this is quite right as I an not sure what you controls are but you should get the idea)

cmdStandby.CommandText = "SaveAdmin"
cmdStandby.CommandType = CommandType.StoredProcedure
cmdStandby.Connection = sqlstandby

cmdStandby.Parameters.Add("@Perno", SqlDbType.Int)
cmdStandby.Parameters.Add("@Client_id", SqlDbType.Int)
cmdStandby.Parameters.Add("@Region_id", SqlDbType.Int)
cmdStandby.Parameters.Add("@Service_id", SqlDbType.Int)

For Each li In ChkRegion.CheckedItems

cmdStandby.Parameters("@Perno").Value = CINT(lblEmpNo.Text)  'or the proper conversion
cmdStandby.Parameters("@Client\_id").Value = ddlClient1.SelectedValue	'or should this be selected index? If value what is it ? Text, integer etc
cmdStandby.Parameters("@Region\_id").Value = li.Value '(?)
cmdStandby.Parameters("@Service\_id").Value = li.Value '(?)
cmdStandby.ExecuteNonQuery()

cmdStandby.Parameters("@Perno").Value = CINT(lblEmpNo.Text)  'or the proper conversion
cmdStandby.Parameters("@Client\_id").Value = ddlClient1.SelectedValue
cmdStandby.Parameters("@Region\_id").Value = li.Value '(?)
cmdStandby.Parameters("@Service\_id").Value = li.Value '(?)
cmdStandby.ExecuteNonQuery()

Dave Kreskowiak

Is suspect your problem is here:

For Each li In ChkService.Items
If li.Selected = True Then
cmdStandby.Parameters.AddWithValue("@Service_id", li.Value)
End If
Next

This loop is going through a list and checking if it's selected. For each value selected you're adding another parameter (with the same name) to the Parameters collection. If there is more than one item selected, your code is adding more than the 4 parameters to the SQL call.

A guide to posting questions on CodeProject[^]
Dave Kreskowiak Microsoft MVP Visual Developer - Visual Basic
2006, 2007, 2008