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

Retrieving values from a subreport to Body of Parent Report

Is it possible to retrieve the value of a subreport's field or control from the parent report? I'm doing some grouping in the subreport and need to retrieve the group by's data value from the subreport.

Also, is there a way to repeat the main page's body when subreport has a page break? ie you page break on some thing in the subreport and need the body and head of the parent report to repeat on subsequent pages.

Thanks,
Garick

I want to do something similar.

I want the value of the amount of records retrieved in the sub report.

The table row that the sub report is in needs to be hidden if the value is not greater than 1.

Can it be done?

|||

Jabuka

I think that there's a simple way to do what you want with creating the same dataset that you have in your subreport in the parent one. Then you can evalute the field in your visibility expression.

Hope it helps you

|||

Another way is to create a simple assembly (any language in .Net) and have a static (or shared) variable in it. Set the value of this variable in your subreport and refer to that in your main report.

The only problem with this approach is concurrency as you are using a static variable.

Shyam

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.

retrieving data from sqldataprovider w/out presentation control

Is there a way to retrieve a returned value from a stored procedure w/out binding do a data presentationi control? I have the following sqldataprovider...

<asp:SqlDataSource ID="SqlDataSource1" runat="server" SelectCommand="f_GetUser" ConnectionString="<%$ ConnectionStrings:MyServer %>" SelectCommandType="StoredProcedure"> <SelectParameters> <asp:formparameter name="username" formfield="txtusername" /> <asp:formparameter name="password" formfield="txtpassword" /> </SelectParameters> </asp:SqlDataSource>

Do I have to make a gridview to get the value that is returned from this stored procedure? The SP only returns one value (a count of records) and I want to set it to a variable. Is there a way, similar to a recordset in ASP, that I can access that one value?

here is my stored procedure...

CREATE PROCEDURE f_GetUser @.usernamevarchar(50),@.passwordvarchar(50)ASSELECTCOUNT(*)AS numusersFROM f_UsersWHERE username=@.usernameAND password=@.password

I just want to retrieve "numusers" and use it in a conditional...

tia for your help

Here is a way to display data as a table using code to access the returned data

<%@.PageLanguage="C#" %>

<!

DOCTYPEhtmlPUBLIC"-//W3C//DTD XHTML 1.0 Transitional//EN""http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<

scriptrunat="server">

protectedvoid Page_Load(object sender,EventArgs e)

{

System.Data.DataView dv = (System.Data.DataView)SqlDataSource1.Select(DataSourceSelectArguments.Empty);

System.Data.DataTable dt = dv.Table;

foreach (System.Data.DataRow drin dt.Rows)

{

TableRow tr =newTableRow();

t.Rows.Add(tr);

foreach (System.Data.DataColumn dcin dt.Columns)

{

TableCell td =newTableCell();

tr.Cells.Add(td);

td.Text = dr[dc.ColumnName].ToString();

}

}

}

</

script>

<

htmlxmlns="http://www.w3.org/1999/xhtml">

<

headrunat="server">

<title>Untitled Page</title>

</head>

<body>

<formid="form1"runat="server">

<asp:Tablerunat="server"ID="t">

</asp:Table>

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

SelectCommand="SELECT [EmployeeID], [LastName], [FirstName] FROM [Employees]"></asp:SqlDataSource>

</form>

</body>

</html>

|||

thanks sb! You don't happen to have a vb version of that same code do you? >< I suck at C# :)

|||

Sorry, I just put this together off the peg. You shuld be able to easily write a VB version line by line as there are no special work rounds

I use C# most of the time, but if customers want VB, then fine, I don't mind using both - I can sit on the fence and see the sense of both!

Retrieve new values of a database

Hello, I have the view from a table that keeps incrementing with new
values, I have no control of this database, but I need to retrieve the
new values to insert it on my table (sql server) and send some
parameters of the database to another application, I'm using C# to get
access to both databases, so does anybody know of an efficient way to
retrieve only the new values of the database?

Thanks a lot

Luis Saavedra

Luis,
I don't think there is any general solution to your problem. I usually use triggers, but since you said you have no control on the db that won't work.

The only solution I can think of is to see it your table contains some identity column. If that is the case (and it often is), then you may just select data from the table with an identity column greater than the last you found. This will only work to get new additions to the db. It cannot detect deletions.

HTH
--mc

|||

the problem is that the identity column appears to be autoincremental, but is not in all of the cases, for example I have something like

1001

1002

1003

1005

1007

1006

1004

what I have considered is using a counter (of the inserted registers) in a table of my database, then use a count of all the registers of the informix table and get a top (of the difference between these two tables), the problem is that if in the lapse between the count and the selection of the registers they insert something in the table of informix, I will be missing some values, any ideas to solve this or use another solution...

Thanks

Luis Saavedra

|||

I think you're on the right track here. You might want to store the same values in that field from the source system in your own column in a table on your system. You can then compare based on a join. That might not work for you if there are a lot of values or it is updated a lot. In that case, is there a "Row Number" in your source database you can get at pprogramatically? If so, you could store the last value of that column, which will increment, in one row of your table and update based on it. Does that make sense? Something like this:

Source DB:

(Hidden RowNumber feature) IdentityColumn

(1) 1001

(2) 1002

(3) 1003

(4) 1005

(5) 1007

(6) 1006

Your DB:

Tracking Number IdentityColumn

(1) 1001

(2) 1002

(3) 1003

(4) 1005

Buck Woody

http://www.buckwoody.com

Retrieve Images from SQL db: Code Problem.

Hi,
I want to get an image from a sql server database and display it with an asp:image control. I use C# in MS Visual Studio .Net 2005 and Sql server 2005.
All I've done is:

// and Page Display_image.aspx.cs
protected void Page_Load(object sender, EventArgs e)
{
try
{

SqlConnection Con = new SqlConnection(
"server=localhost;" +
"database=;" +
"uid=;" +
"password=;");

System.String SqlCmd = "SELECT img_data FROM Image WHERE business_id = 2";

System.Data.SqlClient.SqlCommand SqlCmdObj = new System.Data.SqlClient.SqlCommand(SqlCmd, Con);

Con.Open();

System.Data.SqlClient.SqlDataReader SqlReader = SqlCmdObj.ExecuteReader(CommandBehavior.CloseConnection);

SqlReader.Read();

System.Web.HttpContext.Current.Response.ContentType = "image/jpeg";

// I write this:
System.Web.HttpContext.Current.Response.BinaryWrite((byte[])SqlReader["img_data"]);

// Or this:
//System.Drawing.Image _image = System.Drawing.Image.FromStream(new System.IO.MemoryStream((byte[])SqlReader["img_data"]));

//System.Drawing.Image _newimage = _image.GetThumbnailImage(100, 100, null, new System.IntPtr());

//_newimage.Save(System.Web.HttpContext.Current.Response.OutputStream, System.Drawing.Imaging.ImageFormat.Jpeg);

Con.Close();

}
catch (System.Exception Ex)
{

System.Web.HttpContext.Current.Trace.Write(Ex.Message.ToString());
}
}

Both work the same way. I mean the image is displayed well but when I view code of the web page I see this:

...
GIF89aå o ÷ó å݉97)HS ¢ ?x L£0¬B´ç¬D(áâK? ?ô² –I€8`È® û l1ã?#K?L( ?*\X±‰ Î"«Mè± ??
...

TOO MUCH characters before the html tag. And any code of the master page used for this page does not work as well!

Can anyone help me with this? I've tried for 2 days but I still fail.

Thanks.

This should help:

Dim

drAs SqlDataReader

dr = cmd.ExecuteReader

dr.Read()

Response.Clear()

Response.AddHeader(

"Content-type", dr("MimeType"))

Response.AddHeader(

"Content-Disposition","inline; filename=""" & dr("Filename") &"""")Dim buffer()AsByte = dr("Data")Dim blenAsInteger =CType(dr("Data"),Byte()).Length

Response.OutputStream.Write(buffer, 0, blen)

Response.End()

|||Hello Motley,

I've tried your comment, but it stays the same. The image is displayed, but plenty of charaters in the page source still appears.

I guess it's because of the 'response.outputstream.write()' command, so all the image's byte data has been writen to the page's code.

Can you find any way to replace 'outputstream.write()' or some changes to stop those disgusting characters?

Thank you for your help,
maivangho.