Trying to insert a row using stored procedured with a parameter binded to an expression.

Question

Environment: asp.net 3.5 (C# and VB) , Ms-sql server 2005 express

Tables

Table:tableUser ID (primary key) username

Table:userSchedule ID (primary key) thecreator (foreign key = tableUser.ID) other fields

I have created a procedure that accepts a parameter username and gets the userid and inserts a row in Table:userSchedule

Problem: Using stored procedure with datalist control to only fetch data from the database by passing the current username using statement below works fine

protected void SqlDataSourceGetUserID_Selecting(object sender, SqlDataSourceSelectingEventArgs e)
    {
        e.Command.Parameters["@CurrentUserName"].Value = Context.User.Identity.Name; 
    }

But while inserting using DetailsView it shows error

Procedure or function OASNewSchedule has too many arguments specified. 

I did use

protected void SqlDataSourceCreateNewSchedule_Selecting(object sender, SqlDataSourceSelectingEventArgs e)
    {
        e.Command.Parameters["@CreatedBy"].Value = Context.User.Identity.Name;
    }

DetailsView properties: autogen fields: off, default mode: insert, it shows all the fields that may not be expected by the procedure like ID (primary key) not required in procedure and CreatedBy (user id ) field .

So I tried removing the 2 fields from detailsview and shows error

Cannot insert the value NULL into column 'CreatedBy', table 'D:\OAS\OAS\APP_DATA\ASPNETDB.MDF.dbo.OASTest'; column does not allow nulls. INSERT fails. The statement has been terminated.

For some reason parameters value is not being set.

Can anybody bother to understand this and help?

Answer 1

Ok my mistake, I didn't know how delegation works and what it was. Instead of SqlDataSourceCreateNewSchedule_Selecting(....) I should have used SqlDataSourceCreateNewSchedule_Inserting to modify the parameter after all the need was to set parameter while inserting not selecting.