insert using stored procedure & sqldatasource ctrl

mahajanvit · August 30th, 2006, 05:41 AM

Hi Everyone
I am new member of this group, joined today only. I am working on .NET 2005. I faced this problem during my project. so in order to solve this, i made a small application. I am trying to insert into a table using SP and sqldatasource control. For ur king reference i will paste SP as well as code also.

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("@Address", Address);
        SqlDataSource1.InsertParameters.Add("@Profession1" , Profession);
        SqlDataSource1.InsertParameters.Add("@City", City);
         SqlDataSource1.Insert();
---------------------------------------------------------------------
Stored Procedure

ALTER PROCEDURE dbo.StoredProcedure1

    (
        @City varchar(10),
        @Address varchar(100),
        @Profession1 varchar(20)
    )

AS
begin
insert into Name (Address,Profession,City)values(@Address,@Professi on1,@City)
end
---------------------------------------------------------------------
Error: SP has too many specified arguments. But i have only three controls in Front end and three in SP. SP is executing well. I dont know where i am going wrong. While configuring sqldatasource control, if i am choosing Table instead of SP and writing insert statement in C# code, its working well. But not working if SP is choosed from sqldatasource control.

Regards
Raghav Mahajan

Quick209 · August 30th, 2006, 07:06 PM

I am still i nthe middle of installing my VS2K5 here thanks to a HD crash so I cannot test this out buuuttt, what does your ASP.NET markup look like for the sqldatasource? I ask this only because I know that you have to configure the InsertCommandType to StoredProcedure and the InsertCommand is your stored procedure name. if those are in your markup (aspx file) or being configured in your code behind then that is your issue. I don't really see an issue with your sp though. You did test it from a query analyzer or Enterprise Manager query tool to make sure it works right? You probably already did this but just narrowing down possibilities. Imar may come in and solve it in one big swoop hehe :)

I have been having issues with atlas, MasterPages and sqldatasource markup finding my connection string in my webconfig (I encrypt it on final release anyway so I eventually do all the setup on code behind). So, I add the sqldatasource as markup but configure the datasource on page load via code behind.

thenoseknows · August 30th, 2006, 10:02 PM

The sp is fine.

You're probably not giving the sqlDataSource the values. There has to be something that tells the data source what values to put into those parameters that you defined. You can do this in the tags on the front side or you can do it in the code behind.

mahajanvit · August 31st, 2006, 03:50 AM

Hi
I am simply selecting SP while configuring sqldatasource.I am not writing any insert, select or update statement inside sqldatsource control. I already mentioned that when i am selecting Table from sqldatsource control,application is working well. please tell me where I am going wrong? I think C# code under button click is correct.....

thenoseknows · September 1st, 2006, 12:02 AM

I don't see anywhere where you tell the SqlDataSource what stored procedure to use to perform that Insert command.

Try telling it that, something like this:

Code:

SqlDataSource1.InsertCommand = "StoredProcedure1"
SqlDataSource1.InsertCommandType = SqlDataSourceCommandType.StoredProcedure

I also don't see that you have given the SqlDataSource a connection string, which it will need. Perhaps you have done that elsewhere in your code.

mahajanvit · September 1st, 2006, 08:31 AM

Hi Neil Timmerman
I tried as suggested by u. I appreciate the efforts done by u, But its giving error Exact Error message: Function 'StoredProcedure1' expects parameter '@City', which was not supplied.. I tried reshuffling also. If I am making @Address on top in SP, its giving same error for that. I am writing code inside submit button click, Please let me know whether I have to write anything inside on inserting or on inserted. Now I am using Try and Catch block also, its throwing exception...
Regards
Raghav Mahajan

thenoseknows · September 1st, 2006, 09:45 AM

Ok, I went and tried it too and it doesn't work. What does work is this:

Code:

Protected Sub Btnsubmit_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Btnsubmit.Click

        Dim Profession As String
        Dim Address As String
        Dim City As String

        Profession = txtprofession.Text
        Address = txtaddress.Text
        City = txtcity.Text

        Dim conString As String = "your connection string here"

        Dim cmd As SqlCommand = New SqlCommand("StoredProcedure1", con)
        cmd.CommandType = Data.CommandType.StoredProcedure
        cmd.Parameters.Add(New SqlParameter("@Address", SqlDbType.VarChar, 100))
        cmd.Parameters.Item("@Address").Value = Address
        cmd.Parameters.Add(New SqlParameter("@Profession1", SqlDbType.VarChar, 10))
        cmd.Parameters.Item("@Profession1").Value = Profession
        cmd.Parameters.Add(New SqlParameter("@City", SqlDbType.VarChar, 20))
        cmd.Parameters.Item("@City").Value = City

        con.Open()

        cmd.ExecuteNonQuery()

        cmd.Connection.Close()

    End Sub

The SqlDataSource is one of those drag and drop things they throw in for the designer view so that secretary's can in theory make simple web pages...I don't know that it was even meant to be used in an event handler like you are using it. Perhaps someone else knows. It probably has something to do with a SqlDataSources life cycle and that its not like a data access class for you to use on the fly. But I am not sure. Anyway I tested the above code and it works.

mahajanvit · September 1st, 2006, 11:41 AM

Hi Neil
Thanks dude. U are really very helpful. I am doing extensive research. I have found some articles in MSDN lib also. But as I am in India, its friday night and weekend ahead. So I will try these things on Monday morning. But I am thinking that if I can insert data using Table in Sqldatasource ctrl why not with SP? Lets see...Any how thanks for all this and I will be in touch with u regarding this topic and others in future. Thanks for spending ur precious time for me.
Regards
Raghav Mahajan
Gurgaon, INDIA

thenoseknows · September 1st, 2006, 03:16 PM

No problem. Helping other people with things helps me get better myself so it's beneficial to me as well.

I really don't understand why the SqlDataSource does not work. I agree with you, that it should work. I might mess with it this weekend. Typically if I am doing this programatically in the code behind, I just make the ADO.NET objects and method calls myself.

My coworker just told me that it might be because we didnt put in a provider to the sqlDataSource. Theres three method signatures on the constructor of the SqlDataSource, the third of which allows you to specify a provider. He does that in one of his pages and it works. So I think you should try that. If you dont know what I mean I'll post it here later. It's labor day weekend here in the USA though (holiday Monday which we don't work) so I am heading out of here early before the bad traffic hits! Have a good weekend.

mahajanvit · September 2nd, 2006, 06:49 AM

Hi Neil
I would like to inform that I have fixed this issue. Error, SP has too many arguments specified was coming because I was supplying these parametes 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. Now I can work on my main project..Once again thank you very much for ur kind support and interest in solving issues and helping other people. I hope you will be here in future to solve my other queries. Hope u are enjoying ur three consecutive holidays!!!

Take Care
Regards
Raghav..