Using SqlDataSource In Visual Studio

Question

I am having an interesting problem in visual studio (likely due to my lack of experience). I have a SqlDataSource object and a GridView object. The Gridview object is setup to use the SqlDataSource as a source.

The SqlDataSource is using a parameter (Source = Control, its a textbox), to pass as an input into a SQL stored procedure to return a recordset. When I step through the code, on change to the textbox I execute SqlDataSource.Insert(), then GridView.DataBind().

Since I was having issues with not getting the results I was expecting I ran a trance in SQL Server Profiler.

It seems that when SqlDataSource.Insert() runs, a null value is passed through the parameter. When GridView.DataBind() runs on the next line, the parameter value is passed properly.

Any ideas what could be causing this? I believe whatever I am doing wrong here is also affecting other areas of my code.

protected void ItemBox_TextChanged(object sender, EventArgs e)
{
    if(setFire)
    {
        SqlDataSource1.Insert();
        GridView1.DataBind();
        Label1.Text = GridView1.Rows.Count.ToString() + " Results Found";
        Label2.Text = ItemBox.Text;

        setFire = false;
        ItemBox.Text = "";
        ItemBox.Focus();
        setFire = true;
    }
}

ProfilerSnapshot

DataSourceParam

GridView

Why we have SqlDataSource.Insert here , i am not seeing any database insertion things. ? — Dreamweaver
– Dreamweaver, Commented May 26, 2015 at 19:53
To be perfectly honest I am just trying to execute the stored procedure. I thought I needed to use .Insert() since I am trying to push values of parameters to the stored procedure. — user2061929
– user2061929, Commented May 26, 2015 at 20:13
To add a little to this. I have a second SqlDataSource that returns an output parameter. I am trying to execute that as well, but I have the same issue as here where NULL is getting passed. — user2061929
– user2061929, Commented May 26, 2015 at 20:17

alainlompo · Accepted Answer · 2015-05-26 21:24:33Z

1

Before you can call the Insert method on your SqlDataSource you need to correctly define its InsertCommand. Here is how you can do it:

protected void ItemBox_TextChanged(object sender, EventArgs e)
{
    if(setFire)
    {
        SqlDataSource1.InsertCommandType = SqlDataSourceCommandType.StoredProcedure;
        string storedProcedureName = .... // The name of your stored procedure

        SqlDataSource1.InsertCommand= storedProcedureName;

        // Then you should add a SqlParameter object into the collection
        // of Parameters for the Insert Command
        // Its name correspond to the variable name expected by your
        // stored procedure and its value comes from your textbox
        string paramName = .... // The parameter name in your stored procedure

        SqlDataSource1.InsertParameters.Add(paramName, yourTextBoxName.Text);

        // Now you can call Insert()

        SqlDataSource1.Insert();
        GridView1.DataBind();
        Label1.Text = GridView1.Rows.Count.ToString() + " Results Found";
        Label2.Text = ItemBox.Text;

        setFire = false;
        ItemBox.Text = "";
        ItemBox.Focus();
        setFire = true;
    }
}

edited May 26, 2015 at 21:24

answered May 26, 2015 at 21:19

alainlompo

4,4344 gold badges36 silver badges43 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

user2061929 Over a year ago

I tried your solution but I got the following error: "Procedure or function has too many arguments specified.". Do you think this is because I already did some setup via the Configure Data Source GUI?

user2061929 Over a year ago

Also, how would I adjust this to work with an output parameter? I have another SqlDataSource that is using a second stored procedure with two input and one output parameter. Thank you for your help so far!

alainlompo Over a year ago

@user2061929, yes it is possible to have a conflict with initial setups. I will investigate further an be back with a more detailed example. Best wishes

user2061929 · Accepted Answer · 2015-05-28 18:59:30Z

0

I was able to resolve my issues. I had two stored procedures I needed to execute. One was tied to a GridView and the other was just to return an output parameter that I would display on screen.

With SqlDataSource1 (the object tied to my GridView) I didn't need to call SqlDataSource1.Insert(). Just doing the GridView1.DataBind() command worked fine.

As for the other I couldn't get the SqlDataSource object to behave like I wanted, so I ended up using the method outlined in the youtube video below:

https://www.youtube.com/watch?v=stIWeAcO45Y

It ended up working perfectly.

Thank you so much @alainlompo for your help above. Although I didn't end up using that technique I did find your example very useful.

answered May 28, 2015 at 18:59

user2061929

4113 gold badges5 silver badges13 bronze badges

Collectives™ on Stack Overflow

Using SqlDataSource In Visual Studio

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related