Issues with an output param from a sproc using SQLDataSource

I have a stored proc that I'd like to return an output param from. I'm using a SQLDataSource and invoking the Update method which calls the sproc.

The proc looks like this currently:

ALTERproc [dbo].[k_sp_Load_IMIS_to_POP_x]

@.vcOutputMsgvarchar(255)OUTPUT

AS

SETNOCOUNTON;

select @.vcOutputMsg='asdf'

The code behind looks like this:

protectedvoid SqlDataSource1_Updated(object sender,SqlDataSourceStatusEventArgs e)

{

//handle error on return

string returnmessage = (string)e.Command.Parameters["@.vcOutputMsg"].Value;

}

On the page source side, the params are defined declaratively:

<UpdateParameters>

<asp:ParameterDirection="ReturnValue"Name="RETURN_VALUE"Type="Int32"/>

<asp:ParameterDirection="InputOutput"Name="vcOutputMsg"Type="String"/>

</UpdateParameters>

When I run it, the code behind throws the following exception - "Unable to cast object of type 'System.DBNull' to type 'System.String'"

PLEASE HELP! What am I doing wrong? Is there a better way to get output from a stored proc?

I got mine to work. Here's my example.

ASPX

<asp:gridview id="GridView1" runat="server" autogeneratecolumns="false" autogenerateeditbutton="true"datasourceid="SqlDataSource1"><columns><asp:boundfield datafield="ShipperID" headertext="ShipperID" readonly="True" /><asp:boundfield datafield="CompanyName" headertext="CompanyName" readonly="True" /><asp:boundfield datafield="Phone" headertext="Phone" readonly="True" /></columns></asp:gridview><asp:sqldatasource id="SqlDataSource1" runat="server" connectionstring="<%$ ConnectionStrings:NorthwindConnectionString%>"onupdated="SqlDataSource1_Updated" selectcommand="SELECT * FROM [Shippers]" updatecommand="sp_GetMessage"updatecommandtype="StoredProcedure"><updateparameters><asp:parameter direction="InputOutput" name="Message" size="50" type="String" /></updateparameters></asp:sqldatasource><asp:label id="Label1" runat="server" />

CODE-BEHIND

protected void SqlDataSource1_Updated(object sender, SqlDataSourceStatusEventArgs e){Label1.Text = e.Command.Parameters["@.Message"].Value.ToString();}

STORED PROCEDURE

ALTER PROCEDURE dbo.sp_GetMessage(@.MessageAS VARCHAR(50)OUTPUT)ASBEGINSELECT @.Message ='Hello World!'END

|||

Mine looked identical to yours except for the size on the output param. Once I added it in, it worked beautifully!! Not sure why it wasn't automatically declared when I configged the SQLDatasource to use the update method. But hey, it works now!!!

Thanks for the help!!!!!

Bill

|||

Mine didn't get created either, except I got another error actually referencing the absence of a size. I'm not too sure why you didn't receive the same. I also don't know why the Size attribute is needed.

Issue with SQLDataSource and FilterExpression

I'm not sure exactly how the FilterExpression works. I have a sqldatasource, stored procedure, and GridView. My stored procedure basicly a select statement to populate my gridview, it included the fields I want to filter on. In my codebehind file I build a WHERE Clause based on the entries a user makes. Then I add the my FilterExpr variable to the SqlDataSource1.FilterExpression = FilterExpr. My SqlDataSource has a number of control parameters that match the textboxes a users enters into.

My question, I guess is does my stored procedure need the variables matching my controlparameters for my sqldatasource? Or how does this work? My GridView is returning all rows no matter what I enter into the filter textboxes (first, last, etc...)

MY SQLSDATASOURCE

 <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:ConnectionString%>" SelectCommand="ClientSearch" SelectCommandType="StoredProcedure"> <FilterParameters> <asp:ControlParameter ControlID="SearchLastName" Name="LastName" PropertyName="Text" ConvertEmptyStringToNull="true" /> <asp:ControlParameter ControlID="SearchFirstName" Name="FirstName" PropertyName="Text" ConvertEmptyStringToNull="true" /> <asp:ControlParameter ControlID="SearchEmail" Name="Email" PropertyName="Text" ConvertEmptyStringToNull="true" /> <asp:ControlParameter ControlID="SearchAddress" Name="Address" PropertyName="Text" ConvertEmptyStringToNull="true" /> <asp:ControlParameter ControlID="SearchComment" Name="Comment" PropertyName="Text" ConvertEmptyStringToNull="true" /> </FilterParameters> </asp:SqlDataSource>

MY CODEBEHIND

       Dim FilterExprAs String
        If SearchLastName.Text =""And _
            SearchFirstName.Text =""And _
            SearchEmail.Text =""And _
            SearchComment.Text =""And _
            SearchAddress.Text =""Then
            lblMessage.Text ="You didn't enter any search parameters. Please try Again."
            Me.GridViewSearch.DataSourceID = ""
            Exit Sub
        Else
            Me.GridViewSearch.DataSourceID = "SqlDataSource1"
        End If
        FilterExpr = ""
        If SearchLastName.Text <> "" Then
            FilterExpr = FilterExpr & "LastNameLike'" & _
            SearchLastName.Text &"%" &"' AND "
        End If
        If SearchFirstName.Text <> "" Then
            FilterExpr = FilterExpr & "FirstNameLike'" & _
            SearchFirstName.Text &"%" &"' AND "
        End If
        If SearchEmail.Text <> "" Then
            FilterExpr = FilterExpr & "Like'" & _
             SearchEmail.Text &"%" &"' AND "
        End If
        If SearchComment.Text <> "" Then
            FilterExpr = FilterExpr & "[Comments]Like'" & "%" & _
             SearchComment.Text &"%" &"' AND "
        End If
        If SearchAddress.Text <> "" Then
            FilterExpr = FilterExpr & "[Address]Like'" & "%" & _
             SearchAddress.Text &"%" &"' AND "
        End If
        If Right(FilterExpr, 4) = "AND "Then
            FilterExpr = Left(FilterExpr, Len(FilterExpr) - 4)
       End If
        Try
            Me.SqlDataSource1.FilterExpression = FilterExpr
           Me.SqlDataSource1.DataBind()
           Me.GridViewSearch.DataBind()
           Me.lblMessage.Text =Me.SqlDataSource1.FilterExpression
       Catch objExceptionAs SqlException
           Dim objErrorAs SqlError
           For Each objErrorIn objException.Errors
                Response.Write(objError.Message)
           Next
        End Try
    End Sub 

MY SPROC

GOALTER PROCEDURE [dbo].[ClientSearch]ASSELECT C.ClientID, C.FirstName, C.LastName, A.Address, C.Comments, C.EMailFROM tblClient CINNERJOIN tblClientAddresses AON C.ClientID = A.ClientID

Remove all the <FilterParameters> and it should work fine.

Issue with getting values from child controls in a gridview, to use for the update using a

Hi all,

I have a gridview bound with a SQLDataSource. I am using the Update feature of the SQLDataSource to update a SQL Server database with values entered into the gridview. However I am not getting it to work. I believe this is due to the controls that contain the user entries are not the gridview itself, but rather child controls within the gridview. I have been using the names of the actual controls but nothing happens. Upon submit, the screen returns blank, and the database is not updated. Here is some code:

<

asp:GridViewID="GridEditSettlement"runat="server"AutoGenerateColumns="False"BackColor="Navy"BorderColor="IndianRed"BorderStyle="Solid"Font-Names="Verdana"Font-Size="X-Small"DataSourceID="SqlDataSource_grid"AllowPaging="True"AllowSorting="True"ForeColor="White"DataKeyNames="legid"><Columns><asp:CommandFieldShowEditButton="True"CancelImageUrl="~/App_Graphics/quit.gif"CancelText=""EditImageUrl="~/App_Graphics/EditGrid.GIF"EditText=""UpdateImageUrl="~/App_Graphics/save.gif"UpdateText=""ButtonType="Image"/><asp:BoundFieldDataField="StartDate"HeaderText="Start Date"ReadOnly="True"/><asp:BoundFieldDataField="EndDate"HeaderText="End Date"ReadOnly="True"/><asp:BoundFieldDataField="CounterpartDealRef"HeaderText="CP Deal Ref"ReadOnly="True"/>

<asp:TemplateFieldHeaderText="Preliminary Settlement Price"><ItemTemplate><asp:LabelID=lblPreliminaryrunat=serverText='<%# Bind("PrimarySettlementPrice") %>'/></ItemTemplate><EditItemTemplate><asp:TextBoxrunat="server"ID=txtPrimaryPriceText='<%# Bind("PrimarySettlementPrice") %>'></asp:TextBox>

</EditItemTemplate></asp:TemplateField>

<asp:TemplateFieldHeaderText="Agreed Settlement Price"><ItemTemplate><asp:LabelID=lblAgreedrunat=serverText='<%# Bind("AgreedSettlementPrice") %>'/></ItemTemplate><EditItemTemplate><asp:TextBoxrunat="server"ID=txtAgreedPriceText='<%# Bind("AgreedSettlementPrice") %>'></asp:TextBox>

</EditItemTemplate></asp:TemplateField>

<asp:BoundFieldDataField="Volume"HeaderText="Volume"ReadOnly="True"/><asp:BoundFieldDataField="Price"HeaderText="Price"ReadOnly="True"/><asp:BoundFieldDataField="TotalVolume"HeaderText="Total Volume"ReadOnly="True"/><asp:BoundFieldDataField="InstrumentName"HeaderText="Instrument"ReadOnly="True"/><asp:BoundFieldDataField="NominalValue"HeaderText="Nominal Value"ReadOnly="True"/><asp:BoundFieldDataField="Strike"HeaderText="Strike"ReadOnly="True"/><asp:BoundFieldDataField="DeliveryDate"HeaderText="Delivery Date"ReadOnly="True"/><asp:TemplateFieldHeaderText="LegId"SortExpression="LegId"><ItemTemplate><asp:LabelID="lblLegID"runat="server"Text='<%# Bind("LegId") %>'></asp:Label></ItemTemplate><EditItemTemplate><asp:TextBoxrunat="server"ID=txtLegIDText='<%# Bind("LegId") %>'></asp:TextBox>

</EditItemTemplate></asp:TemplateField>

</Columns><RowStyleBackColor="#FFFF66"ForeColor="#333333"/><EditRowStyleBackColor="#FFFF66"Font-Names="Verdana"Font-Size="X-Small"ForeColor="#333333"/><PagerStyleForeColor="White"/><AlternatingRowStyleBackColor="White"ForeColor="#333333"/></asp:GridView> <br/>

<asp:SqlDataSourceID="SqlDataSource_grid"runat="server"ConnectionString="<%$ ConnectionStrings:DealCaptureDev %>"SelectCommand="sp_get_single_deal"SelectCommandType="StoredProcedure"UpdateCommand="Update trDealLeg Set PrimarySettlementPrice=@.primarysettlement, AgreedSettlementprice=@.agreedsettlement, LastUpdate=GetDate(), LastUpdateBy=Session('userid') Where LegID=@.legid"EnableCaching="True"ConflictDetection="CompareAllValues"ProviderName="System.Data.SqlClient"><SelectParameters><asp:QueryStringParameterDefaultValue=""Name="dealnum"QueryStringField="deal"Type="String"/></SelectParameters><UpdateParameters><asp:ControlParameterControlID="txtLegId"PropertyName="Text"Name="legId"/><asp:ControlParameterControlID="txtPrimarySettlement"Name="primarysettlement"PropertyName="Text"/><asp:ControlParameterControlID="txtAgreedSettlement"Name="agreedsettlement"PropertyName="Text"/><asp:SessionParameterDefaultValue=""Name="userid"SessionField="userid"/></UpdateParameters></asp:SqlDataSource>

As seen above, controls such as txtPrimarySettlement are referenced but the update is not successful. The text boxes are within the GridEditSettlement gridview. In the .aspx code I cannot use FindControl (at least I don't think it will work).

So the questions are: Is it possible to reference the child controls, if so - how? Is there another way to do this, such as in the vb code behind - in the either the gridview's RowUpdating event or the SQLDataSource's Updating event.

What is the best approach? Anyone come up against this issue before?

Thanks,

KB

You could try this way:

code in gridview: <asp:TemplateField HeaderText="company name" SortExpression="camcompany"> <EditItemTemplate> <asp:TextBox ID="TextBox2" runat="server" Text='<%# Bind("camcompany")%>'></asp:TextBox> </EditItemTemplate> <InsertItemTemplate> <asp:TextBox ID="TextBox1" runat="server" Text='<%# Bind("camcompany")%>'></asp:TextBox> </InsertItemTemplate> <ItemTemplate> <asp:Label ID="Label2" runat="server" Text='<%# Bind("camcompany")%>'></asp:Label> </ItemTemplate> </asp:TemplateField>code in sqlDataSource: <UpdateParameters> <asp:Parameter Name="camcompany" Type="String" /> ......... </UpdateParameters>

You can specify a row and access the controls within that row:

Dim lastNameAsString = selectRow.Cells(1).Text

You can access the individual cells of theGridViewRow object by using theCells property. If a cell contains other controls, you can retrieve a control from the cell by using theControls collection of the cell. You can also use theFindControl method of the cell to find the control, if the control has anID specified.

seehttp://msdn2.microsoft.com/en-us/library/system.web.ui.webcontrols.gridviewrow.aspx for details.

Hope it helps.