return @@rowcount from stored proc

Hi

I'm using an sqldatasource control in my aspx page, and then executing it from my code behind page (SqlDataSource1.Insert()), how do i retrieve the number of rows (@.@.rowcount) which have been inserted into the database and display it in my aspx page. I am using a stored procedure.

thanks

Hello Mattock,

Have a look at the following article about using stored procedure to update data:http://msdn2.microsoft.com/en-us/library/59x02y99(VS.80).aspx

Jeroen Molenaar.

sql

retriving data from 2 views by the value in a textbox

i have a textbox which a user enters a numeric value

i want it to use SqlDataSource and check if the value exists in any of the tables.

in my text box the users would enter starting from '100000' or '200000'

i want it to check the view that starts the # with '100000' and 2ed view starts '200000'

With this i can check in one of the tables and make the selection.

<asp:SqlDataSourceID="SqlDataSource1"runat="server"ConnectionString="<%$ ConnectionStrings:imacstestConnectionString %>"

SelectCommand="SELECT [ReportNumber] FROM [AppraisalSummaryBlue] WHERE ([ReportNumber] = @.ReportNumber)">

<SelectParameters>

<asp:ControlParameterControlID="txtReport"Name="ReportNumber"PropertyName="Text"

Type="String"/>

</SelectParameters>

</asp:SqlDataSource>

How can i make this possible ?

i was thinking putting a second sqldatasource and have that check the second view but how can i make the textbox goto the correct selectcommand ?

You'll need to do a conditional if in your select command:

IF @.ReportNumber > 10000 BEGINSELECT [ReportNumber] FROM [AppraisalSummaryBlue] WHERE ([ReportNumber] = @.ReportNumber) END ELSE BEGINSELECT [ReportNumber] FROM [AppraisalSummaryTHEOTHERTABLE] WHERE ([ReportNumber] = @.ReportNumber) END

Retrieving selected gridview column values for SQLDatasource asp:controlparameters

Not sure if this is the correct forum, but I 'm having problems retrieving a sqldatasource's asp:control parameter values from a selected row (during edit) in a gridview to update a record thru a stored procedure. The stored procedure is pretty intense, so I'd like to keep it in SQL if possible instead of creating the generic "update table set ..." that I see in most examples. It seems as if I can't get the propertyname right or something because it keeps giving me a "Procedure or function XX has too many arguments specified error". Maybe the DataKeyNames is not right?? I've tried just passing one parameter (ProductID-same as DataKeyNames) using "SelectedValue" as propertyname and still get the same. It's got to be something very simple, but I'm at a loss. All parameters are spelled the same in the sp (with an added "@." at start) as in the asp:controlparameters. Here's the gridview (asp.net 2.0 connecting to SQL Server 2005):

<

asp:GridViewID="gvLoadEditProductPrices"runat="server"AutoGenerateColumns="False"AllowSorting="True"DataSourceID="SqlDataSource1"DataKeyNames="ProductID"><Columns><asp:CommandFieldShowEditButton="True"/><asp:BoundFieldDataField="ProductID"HeaderText="ProductID"HeaderStyle-BackColor="white"InsertVisible="False"ReadOnly="True"SortExpression="ProductID"/><asp:BoundFieldDataField="Product"HeaderText="Product"SortExpression="Product"ReadOnly="True"/><asp:BoundFieldDataField="ProductCat"HeaderText="ProductCat"SortExpression="ProductCat"ReadOnly="True"/><asp:BoundFieldDataField="VarRate"HeaderText="VarRate"SortExpression="VarRate"/><asp:BoundFieldDataField="loadid"HeaderText="loadid"InsertVisible="False"ReadOnly="True"SortExpression="loadid"/><asp:BoundFieldDataField="loadamount"HeaderText="loadamount"SortExpression="loadamount"ReadOnly="True"/><asp:BoundFieldDataField="ProductCol"HeaderText="ProductCol"SortExpression="ProductCol"ReadOnly="True"/><asp:BoundFieldDataField="PageID"HeaderText="PageID"SortExpression="PageID"ReadOnly="True"/></Columns></asp:GridView>

and the sqldatasource's info:

<

asp:SqlDataSourceID="SqlDataSource1"runat="server"ConnectionString="<%$ ConnectionStrings:MARSProductEditor %>"ProviderName="System.Data.SqlClient"SelectCommand="spGetLoadEditProductPrices"SelectCommandType="StoredProcedure"UpdateCommand="spUpdateProductPrices"UpdateCommandType="StoredProcedure"><UpdateParameters><asp:ControlParameterName="ProductID"Type="Int32"ControlID="gvLoadEditProductPrices"PropertyName=SelectedDataKey.Values("ProductID")></asp:ControlParameter><asp:ControlParameterName="LoadID"Type="Int32"ControlID="gvLoadEditProductPrices"PropertyName=SelectedDataKey.Values("LoadID")></asp:ControlParameter><asp:ControlParameterName="PageID"Type="Int32"ControlID="gvLoadEditProductPrices"PropertyName=SelectedDataKey.Values("PageID")></asp:ControlParameter><asp:ControlParameterName="ProductCol"Type="Int32"ControlID="gvLoadEditProductPrices"PropertyName=SelectedDataKey.Values("ProductCol")></asp:ControlParameter><asp:ControlParameterName="NewRate"Type="Double"ControlID="gvLoadEditProductPrices"PropertyName=SelectedDataKey.Values("NewRate")></asp:ControlParameter></UpdateParameters><SelectParameters><asp:ControlParameterControlID="ddlEstLoadsPerAcre"Name="LoadID"PropertyName="SelectedValue"Type="Int32"/><asp:ControlParameterControlID="txtEditType"Name="PageName"PropertyName="Text"Type="String"/></SelectParameters></asp:SqlDataSource>

TIA,

John

Nevermind...after hours of testing many different combinations and scenarios, I found that I had to add each field that the control parameter needs to reference in the gridview to the "DataKeyNames" property in the gridview. But, any field I wanted to be updated thru the gridview edit had to use the PropertyName="SelectedValue" as opposed to the PropertyName="SelectedDataKey.Values('fieldname')". I also had to use single quotes for the field name.

John

Retrieving Output paramater after insert

Can some one offer me some assistance?

I'm using a SQLDataSource control to call a stored proc to insert a record into a control. The stored proc I'm calling has an output paramater that returns the new rows identity field to be used later. I'm having trouble getting to this return value via the SQLDataSource . Below is my code (C#):

SqlDataSource1.InsertParameters["USR_AUTH_NAME"].DefaultValue = storeNumber;

SqlDataSource1.InsertParameters["usr_auth_pwd"].DefaultValue =string.Empty;

SqlDataSource1.InsertParameters["mod_usr_name"].DefaultValue ="SYSTEM";

SqlDataSource1.InsertParameters["usr_auth_id"].Direction =ParameterDirection.ReturnValue;

SqlDataSource1.Insert();

int id =int.Parse(SqlDataSource1.InsertParameters["usr_auth_id"].DefaultValue);

below is the error I'm getting:

System.Data.SqlClient.SqlException: Procedure 'csi_USR_AUTH' expects parameter '@.usr_auth_id', which was not supplied.

Has anyone done this before and if so how did you do it?

How did you define the stored procedure 'csi_USR_AUTH' ? Since you add the Direction of usr_auth_id toParameterDirection.ReturnValue, you don't need to declare a @.usr_auth_id parameter in the sp; instead, it will be filled by using a RETURN command. Check this article to see how to use ReturnValue parameter:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpguide/html/cpconinputoutputparametersreturnvalues.asp

Retrieving data from SqlDataSource (old school)

Hey,

I need to retrieve info from a database and display it using a repeater control. No problems there! But, I need to add data before displaying it, and I don't mean add data to the database but rather to the repeater control. For eaxample:

I have a simple database containing two fields: [date] and [event]. The repeater will display these events in a monthly view. That is: the repeater will have 31 rows and the events will be displayed next to the day it happens. Now if there's nothing happening a certain day then I need to add that day manually because it will not be bound, right! See my problem?

In other words, how do i loop through a records when using the SqlDataSource?

Thanks,
Bj?rn Andersson

You can use the split function (Described in this forum somewhere on one implementation of it), or create a table with 31 rows in it, and left join it to your query. Then you'll get back 31 rows (or more) every time.

RETRIEVE VALUE AFTER ADD FROM SQLDATASOURCE

I am using an SQLDataSource to add a product, this works fine, but I would like to know what syntax is used to retrieve the product ID in this case which is return by the SPROC

Thanks

Steve

protected void SqlDataSource1_Inserted(object sender, SqlDataSourceStatusEventArgs e){if (e.Exception ==null){int id = Convert.ToInt32(e.Command.Parameters["@.ProductId"].Value);}}

Sure, you just need to add a Parameter of the type "Return" as in this example:

ASPX

<asp:formview id="FormView1" runat="server" datasourceid="SqlDataSource1" defaultmode="Insert"><insertitemtemplate>Name:<asp:textbox id="NameTextBox" runat="server" text='<%# Bind("Name")%>' /><br /><asp:linkbutton id="InsertButton" runat="server" causesvalidation="True" commandname="Insert"text="Insert" /><asp:linkbutton id="InsertCancelButton" runat="server" causesvalidation="False" commandname="Cancel"text="Cancel" /></insertitemtemplate><itemtemplate>Id:<asp:label id="IdLabel" runat="server" text='<%# Eval("Id")%>' /><br />Name:<asp:label id="NameLabel" runat="server" text='<%# Bind("Name")%>' /><br /><asp:linkbutton id="NewButton" runat="server" causesvalidation="False" commandname="New"text="New" /></itemtemplate></asp:formview>

CODE-BEHIND

protected void SqlDataSource1_Inserted(object sender, SqlDataSourceStatusEventArgs e){if (e.Exception ==null){int id = Convert.ToInt32(e.Command.Parameters["@.ProductId"].Value);}} 

Retrieve data from a SQLDatasource object.

I'm an "old" programmer but new to ASP.NET.

I want to get a value from the SQL Dataset.

What I would normally do in other environments is iterate through the dataset to get the value I would be intrested in, but I can't figure out how to do this without using a visual data display object like a Grid view.

Typically I want to get a value from the database that I then after manipulating it, like multply by 5, use to format something on the page.

thanks in advance,

Thommie

if you already have created the dataset then its easy to retrieve and iterate through rows.

for each row as datarow in dataset.tables(table index or name, 0 if its only table).rows

dim str as string=row(column name or index).tostring

Next

or

for i as integer=0 to dataset.tables(0).rows.count

var=dataset.tables(0).rows(i)(columnname or index)

next

hope this helps.

Eric

|||

Do you have a compelling reason to use a DataSet? If you are needing to return a single value, I'd look into using ExecuteScalar() off of the SqlCommand object or using an output parameter and getting your value like that. If you need some more explicit examples, let me know.

|||

Hi,

You can go through the DataSet using foreach, like

foreach(DataRow dr in DataSet.Table[0].Rows)
{
//use dr[0] to get the first column data.
}

HTH. If this does not answer your question, please feel free to mark the post as Not Answered and reply. Thank you!