How to debug SQLDataSource update command?

Question

In Visual Studio 2010 is there a way to step through the Update Command of a SQLDataSource?

I have the SQLDataSource and the parameters specified on the *.aspx It's using a Stored Procedure.

I'm getting a "has too many arguments specified" error. The number of parameters I've specified exactly match the stored proc. So, I'd love to be able to debug and see what arguments the SQLDataSource is trying to send.

My stored procedure:

ALTER PROCEDURE [dbo].[UPDATE_TABLEA]
    --UPDATE
    @ID int,
    @COL1 varchar(200),
    @COL2 varchar(200),
    @COL3 varchar(20),
    @COL4 varchar(100),
    @COL5 varchar(100),
    @COL6 varchar(10),
    @COL7 varchar(200),
    @COL8 datetime,
    @COL9 datetime,
    @COL10 varchar(20),
    @COL11 datetime,
    @COL12 varchar(20),
    @COL13 datetime,    
    --INSERT TRANSACTION
    @COL14 varchar(100)

AS

BEGIN
    --UPDATE
    UPDATE TABLEA SET 
        COL1 = @COL1,
        COL2 = @COL2,
        COL3 = @COL3,
        COL4 = @COL4,
        COL5 = @COL5,
        COL6 = @COL6,
        COL7 = @COL7,
        COL8 = @COL8,
        COL9 = @COL9,
        COL10 = @COL10,
        COL11 = @COL11,
        COL12 = @COL12,
        COL13 = @COL13,
        COL15 = (SELECT COLX FROM TABLE WHERE [COLY] = 'ASDF')
    WHERE ID = @ID

    --ADD TRANSACTION
    INSERT INTO TABLEB ([COL1], [COL2], id, [datetime]) VALUES (@COL14, (SELECT COLX FROM TABLE WHERE [COLY] = 'ASDF'), @ID, GETDATE())
END

My SQLDataSource:

<asp:SqlDataSource ID="ds1" runat="server" 
            ...
            UpdateCommand="UPDATE_TABLEA" UpdateCommandType="StoredProcedure"             
            ...
        >
            <UpdateParameters>    
                <asp:Parameter Type="Int32" Name="@ID" Direction="Input" />
                <asp:Parameter Type="String" Size="200" Name="@COL1" Direction="Input" />
                <asp:Parameter Type="String" Size="200" Name="@COL2" Direction="Input" />
                <asp:Parameter Type="String" Size="20" Name="@COL3" Direction="Input" />
                <asp:Parameter Type="String" Size="100" Name="@COL4" Direction="Input" />
                <asp:Parameter Type="String" Size="100" Name="@COL5" Direction="Input" />
                <asp:Parameter Type="String" Size="10" Name="@COL6" Direction="Input" />
                <asp:Parameter Type="String" Size="200" Name="@COL7" Direction="Input" />
                <asp:Parameter Type="DateTime" Name="@COL8" Direction="Input" />
                <asp:Parameter Type="DateTime" Name="@COL9" Direction="Input" />
                <asp:Parameter Type="String" Size="20" Name="@COL10" Direction="Input" />
                <asp:Parameter Type="DateTime" Name="@COL11" Direction="Input" />
                <asp:Parameter Type="String" Size="20" Name="@COL12" Direction="Input" />
                <asp:Parameter Type="DateTime" Name="@COL13" Direction="Input" />
                <asp:Parameter Type="String" Size="100" Name="@COL14" DefaultValue="<%=user_id%>" Direction="Input" />
            </UpdateParameters>

Here's the SQL Profiler Output:

exec UPDATE_TABLEA @@ID=NULL,@@COL1=NULL,@@COL2=NULL,@@COL3=NULL,@@COL4=NULL,@@COL5=NULL,@@COL6=NULL,@@COL7=NULL,@@COL8=NULL,@@COL9=NULL,@@COL10=NULL,@@COL11=NULL,@@COL12=NULL,@@COL13=NULL,@@COL14=N'<%=user_id%>',@ID=1

The last 2 params look odd. Not sure why though...

Any ideas...why it's telling me too many arguments?

Do you have access to the SQL Server? If so, you could try SQL Profiler. — Bridge
– Bridge, Commented May 11, 2012 at 14:34

Romil Kumar Jain · Accepted Answer · 2012-05-11 14:38:06Z

1

The SqlDataSource is in an EditItemTemplate. That means there'll be a SqlDataSource for each row of this GridView, Repeater—whatever this is.

If you want to get the SqlDataSource for, say, the third row in your GridView, you'd want something like:

Code behind:

SqlDataSource sqlSource = gv.Rows[2].FindControl("sqlId") as SqlDataSource;
Response.Write(sqlSource.ConnectionString);

Now check the parameters for this SqlDataSource.

answered May 11, 2012 at 14:38

Romil Kumar Jain

20.8k9 gold badges67 silver badges95 bronze badges

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

Comments

s15199d · Accepted Answer · 2012-05-11 16:30:49Z

0

The problem was the @ in my parameter definitions.

Doesn't work:

<asp:Parameter Type="DateTime" Name="@COL11" Direction="Input" />

Works:

<asp:Parameter Type="DateTime" Name="COL11" Direction="Input" />

answered May 11, 2012 at 16:30

s15199d

7,82913 gold badges45 silver badges75 bronze badges

Collectives™ on Stack Overflow

How to debug SQLDataSource update command?

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related