insert using stored procedure and sql datasource control

[mahajanvit]

Hi one and all
I got this problem during my project. So in order to solve this I made
a very small application. I am trying to insert using SP and
sqldatasource control. I know that while using sqldatasource control,
there is no need of opening and closing a connection. Also there is no
need to write connection string. When i am selecting table from
sqldatasource and writing insert statement in C# code, its working
fine. When I am selecting SP from sqldatasource control, its not
working. While using SP i have to remove insert statement from C# Code
because same thing is there in SP. Error: SP is having too many
arguments specified. The thing is that i have only three fields in
front end and three in SP. SP is executing successfully. For ur kind
reference i am pasting the code.

protected void Btnsubmit_Click(object sender, EventArgs e)
{
string Profession = string.Empty;
string Address = string.Empty;
string City = string.Empty;

Profession = txtprofession.Text.ToString();
Address = txtaddress.Text.ToString();
City = txtcity.Text.ToString();


ParameterCollection parameters = new ParameterCollection();
SqlDataSource1.InsertParameters.Add("@Profession",
txtprofession.Text);
SqlDataSource1.InsertParameters.Add("@Address",
txtaddress.Text);
SqlDataSource1.InsertParameters.Add("@City", txtaddress.Text);
//SqlDataSource1.InsertCommand = "insert into
Name(Profession,Address,City) values ('" + Profession + "','" + Address
+ "','" + City + "')";

SqlDataSource1.Insert();
}
---------------------------------------------------------------------------------------------------------------------------------------
Stored Procedure
--------------------------
ALTER PROCEDURE Name1
(
@Profession varchar(20),
@Address varchar(100),
@City varchar(10)
)
/*
(
@parameter1 int = 5,
@parameter2 datatype OUTPUT
)
*/
AS
insert into Name
(Profession,Address,City)values(@Profession,@Address,@City)
/* SET NOCOUNT ON */
RETURN
-------------------------------------------------------------------------------------------------------------------------
I dont know where i am making mistake. Waiting for favourable reply
from u all .NET lovers

Regards
Raghav Mahajan
Gurgaon, INDIA
(e-mail address removed)

 

[Bruno Alexandre]

do the

SqlDataSource1.InsertParameters.Clear()

before adding the insertParameters

change your code to this:

protected void Btnsubmit_Click(object sender, EventArgs e) {
string Profession = txtprofession.Text.ToString();
string Address = txtaddress.Text.ToString();
string City = txtcity.Text.ToString();

SqlDataSource1.InsertParameters.Clear()
SqlDataSource1.InsertParameters.Add("@Profession", Profession);
SqlDataSource1.InsertParameters.Add("@Address", Address);
SqlDataSource1.InsertParameters.Add("@City", City);
SqlDataSource1.InsertCommand = "insert into Name(Profession,Address,City)
values (@Profession, @Address, @City)";

SqlDataSource1.Insert();
}

check the errors
hope it helps

 

[raghav]

Hi Mr Bruno Alexandre
I appreciate the efforts done by u. I did as suggested by u. Now i am
getting error: SP expects parameter '@Profession', which was not
supplied. @Profession is very well mentioned in SP. I tried to shuffle
values also, i mean in SP i wrote @City first then @Profession, then it
started giving same error message for @City....So whatever field is
first---error is coming for that...There should not be any prob in SP,
its very small SP, just one insert statement. I dont know, where is
mistake.....can u figure out something else??? Ur INDIAN friend will
highly appreciate that....
Regards
Raghav

 

[raghav]

I would like to inform that I have fixed this issue. Error, SP has too
many arguments specified was coming because these parametes were
supplied twice, once from C# code and secondly while configuring
sqldatasource control. I made one more application because that was
fully messed up by doing so many experiments . Here 3 field are
city, state and country. Now my C# code is like this:

protected void Button1_Click(object sender, EventArgs e)
{

string city = txtcity.Text.ToString();
string state = txtstate.Text.ToString();
string country = txtcountry.Text.ToString();

SqlDataSource1.InsertParameters[0].DefaultValue = city;
SqlDataSource1.InsertParameters[1].DefaultValue = state;
SqlDataSource1.InsertParameters[2].DefaultValue = country;
SqlDataSource1.Insert();
}

Its working very well. I saw in MSDN lib also but they have told that
sqldatadource is used to bind other controls like gridview, formview
etc... No where they have explained simple insert using sqldatasource
control. My sincere thanks to everyone for their kind support..
Regards
Raghav Mahajan