How do I access the value of a stored proc return param in c# using executeNonQuery?

I've got a stored proc to insert a record and return the id of the record inserted in an output param.
How do I access this value in my code after the proc is executed?

param = comm.CreateParameter();
param.ParameterName ="@.MemberID";
param.Direction =ParameterDirection.Output;
param.DbType =DbType.Int32;
comm.Parameters.Add(param);

try
{
rowsAffected =GenericDataAccess.ExecuteNonQuery(comm);
}
catch
{
rowsAffected = -1;
}

you should simply be able to access the parameters Value property.

param.Value

How could I return the next row(Or 8th row for example)

Hi,
I want to return the next row in a select ... order by ... cursor.
I don't want to loop,just SQL,Do you know any solution?
For example: in Oracle we use rownum,is there any equivalent in SQL Server?
-ThanksWhy don't you explain why you think you need a cursor? In Oracle cursors are required, but with SQL Server they are considered inefficent and are rarely used.|||Thanks blindman ,
Typical scenario may be looks like this: I return the result of a heavy select(think about many joins and ...) to the client application: I want just one row at this stage. The application processes that row and then it needs the next row in that select to process.How should I say: "The next row in that select"?
One solution: Use a table (maybe temporary table) to hold the result of that select with an identity column as the rownum of each row.
Another solution: Hold the result of select in application layer and go through a loop in application to fetch the next row.
I am just curious about how one could do this in an "on the fly" manner? You are right maybe it is not efficient in SQL Server but is there any way at all?
-Regards|||Well, I think you should hold the data in the application layer if you want to ensure a static dataset for processing. Problem is, if you go back to SQL Server for your "next" record, the underlying data may have changed. Is your application modifying the data and sending it back to SQL server to be updated?|||Is your application modifying the data and sending it back to SQL server to be updated?
No,it is not.So there is no phantom read problem.I think this is so inefficient to pull all data to client-side only because you may want the next row.Are you in agreement? SQL Server should think about this carefuly,IMHO!
-Thanks|||Well you may think that, but it doesn't, not in SQL 2K.

2k5 will have rownumber if I'm not mistaken...

But this is the best server side paging articles I've seen

http://weblogs.sqlteam.com/jeffs/archive/2004/03/22/1085.aspx

And

http://weblogs.sqlteam.com/jeffs/archive/2003/12/22/672.aspx|||No,it is not.So there is no phantom read problem.I think this is so inefficient to pull all data to client-side only because you may want the next row.Are you in agreement? SQL Server should think about this carefuly,IMHO!
-Thanks
No, I'm not in agreement. How the data is displayed is up to the presentation layer and/or middle tier, and is not the database's responsibility. If the application demands paging, then the database needs to be DESIGNED to facilitate paging. Even ROWNUMBER is meaningless for repeated calls on dynamic datasets.|||How the data is displayed is up to the presentation layer and/or middle tier, and is not the database's responsibility.
OK,I am in agreement.Maybe this is about "Presentation of Data" but we know there is not a clear line between those layers: Many things can be done in Database layer but is implemented in "Application server or middle layer" and vice versa.
Thanks to your posts but I still think it is inefficient to pull whole data between layers and I became so happy when I heard that Yukon will have rownum from Brett.Thanks!|||I'd say use the system the way it was designed. Most of your time is not going to be pulling back an entire dataset, but cycling through it row by row. Databases are designed to work on results sets (i.e., not row by row) and thus aren't very efficient when you want to do that. However, procedural languages are designed to do that very thing. By way of example, I had a cursor running on the database when I first began posting here that took approximately 15 minutes to run. Through the help of those more knowledgable here, I was able to remove the cursor and get the same results in under 30 seconds. I guess the best way to test it is do it both ways, but I'm willing to bet you'll find that pulling the entire result set back and cycling in the app will be faster than coding a cursor in the database. Just my thoughts.

How come I can connect to a database through a datasource control, but not code?

Hi I am trying to open a database connection to a Northwind database. I can open it with a datasource control and return data to a gridview, but can't programically. I get a sqlexception error. this is ust for learning.

Protected Sub Button1_Click(ByVal senderAs Object,ByVal eAs System.EventArgs)Handles Button1.Click
Dim SAs New SqlConnection
Dim builderAs New SqlConnectionStringBuilder

builder("Data Source") =".\SQLEXPRESS;AttachDbFilename=C:\SQL\NORTHWND.MDF"
builder("Integrated Security") =True
builder("Connect Timeout") = 30
builder("User Instance") =True

S.ConnectionString = builder.ConnectionString

Me.Label1.Text = S.ConnectionString

S.Open()
Me.Label2.Text = S.State.ToString
S.Close()
Me.Label3.Text =Me.SqlDataSource1.ConnectionString.ToString

End Sub

The text in label2 and Label three are identical except there are "" around the datasource.

How come I can connect through the datasource control but not through code?

TallMike:

I get a sqlexception error.

What's the exception?|||

I pasted at the bottom of the reply error below. I tried removing the " " but get a syntax error at

builder("Data Source") =".\SQLEXPRESS;AttachDbFilename=C:\SQL\NORTHWND.MDF"

Label1 reads

Data Source=".\SQLEXPRESS;AttachDbFilename=C:\SQL\NORTHWND.MDF";Integrated Security=True;Connect Timeout=30;User Instance=True

Label2 reads "Closed"

Label3 reads

Data Source=.\SQLEXPRESS;AttachDbFilename=C:\SQL\NORTHWND.MDF;Integrated Security=True;Connect Timeout=30;User Instance=True

I tried running this code but using the ASPNETDB database in my app_code folder, cause I thought it might be a permissions thing and I didn't set any permission after I downloaded Northwind from MS, but I got the same results

An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: SQL Network Interfaces, error: 25 - Connection string is not valid)

|||

Seems the connection string is not valid? Check the connectionString refering to 'SqlConnection.ConnectionString Property' topic in VS2005 Documentation.

FYI, here is a typical connection string:

"Persist Security Info=False;Integrated Security=SSPI;Initial Catalog=Northwind;server=(local)"

|||

The connection string you mentioned is for a database on a server. I am trying to connect to a SQL Express database file. The first string in my example works the second doesn't. The only difference is the " "., but you can't use the connection builder without them.

Mike

|||These two ways work

S.ConnectionString ="Data Source=.\SQLEXPRESS;AttachDbFilename=C:\SQL\NORTHWND.MDF;Integrated Security=True;Connect Timeout=30;User Instance=True"S.ConnectionString = ConfigurationManager.ConnectionStrings(1).ToString

This way doesn't

builder(

"Data Source") =".\SQLEXPRESS;AttachDbFilename=C:\SQL\NORTHWND.MDF"

builder(

"Integrated Security") =True

builder(

"Connect Timeout") = 30

builder(

"User Instance") =True

I give up

|||I've not used theSqlConnectionStringBuilder, but it seems to me that this one line:

builder("Data Source") =".\SQLEXPRESS;AttachDbFilename=C:\SQL\NORTHWND.MDF"

should be 2:

builder("Data Source") =".\SQLEXPRESS"
builder("AttachDbFilename")="C:\SQL\NORTHWND.MDF"

sql

How can show list of database

Salam to All

Plz give me Sql query which return list of Database By providing these information(Server name, Uerid ,Pasword)

Thanks

select * from master..sysdatabases or you can useexec sp_databases.

For more information on this visithttp://forums.asp.net/t/1190279.aspx.

|||

Thank u

How can I use the last recordset in a SP that return 3 recordsets ?

Hello,

I have a SP1 that is calling another SP2,
SP2 is using select and returning records

So in SP1 I get 3 different record sets
(as I can see it on Query Analyzer - one after the other)

When I'm trying using the ASP page I get this error:
"Item cannot be found in the collection corresponding to the requested name or ordinal."

How can I access the last recordset ?If you really mean ASP.NET, the DataReader has a NextResult method that can get you from result set 1 to 2, and then to three.|||Thanks, but actually I need to do it on ASP,

what is the eqvivalent for ASP ?

H - E - L - P|||ADO has a NextRecordset method:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/ado270/htm/mdmthnextrec.asp

This is really an ASP.NET forum, so other places may be better for info on ADO classic...

How can I update the col value using extended stored procedure

Hi,
I have created extended stored procedure. In that I need to update the value
of the perticular column of the table and return the same value. Is that
possible using extended stored procedure.
Please help me to solve this problem. If you have any sample code plz send
me, it would be more helpful to me.
Regards
VeeruI can think of 2 strategies:
1) If you only have to update a single value at the end of your processing
inside the XP code, you are best of returning this as a output parameter or
set of output parameters and use this inside a regular SP to do an INSERT,
UPDATE or DELETE
2) If you need to do this within the XP, you need to create a loop back
connection, using ODBC, OLE-DB or ADO. And issue the INSERT, UPDATE or
DELETE on the loopback connection. There is a ODBC sample for setting up a
loop back connection using ODBC that ships with SQL Server. The rest is
normal ODBC programming of which there are many samples flying around.
GertD@.SQLDev.Net
"Veeru" <Veeru@.discussions.microsoft.com> wrote in message
news:5920B696-2415-4429-AEE9-D26086D3B208@.microsoft.com...
> Hi,
> I have created extended stored procedure. In that I need to update the
> value
> of the perticular column of the table and return the same value. Is that
> possible using extended stored procedure.
> Please help me to solve this problem. If you have any sample code plz send
> me, it would be more helpful to me.
>
> Regards
> Veeru|||SQL 2000 XP? There's a sample called xp_dblib that demonstrates how to
connect back to SQL Server from an XP and execute SQL commands.
http://msdn.microsoft.com/library/d...
o1f.asp
"Veeru" <Veeru@.discussions.microsoft.com> wrote in message
news:5920B696-2415-4429-AEE9-D26086D3B208@.microsoft.com...
> Hi,
> I have created extended stored procedure. In that I need to update the
> value
> of the perticular column of the table and return the same value. Is that
> possible using extended stored procedure.
> Please help me to solve this problem. If you have any sample code plz send
> me, it would be more helpful to me.
>
> Regards
> Veeru|||Hi Thanks for your responce.
The documentation in the below path you have sent saying that the sample is
available at x:\Program Files\Microsoft SQL
Server\80\Tools\Devtools\Samples\ODS\Xp_
dblib. but i do not have it. plz sen
d
if you have that.
"Mike C#" wrote:

> SQL 2000 XP? There's a sample called xp_dblib that demonstrates how to
> connect back to SQL Server from an XP and execute SQL commands.
> http://msdn.microsoft.com/library/d...r />
_2o1f.asp
>
> "Veeru" <Veeru@.discussions.microsoft.com> wrote in message
> news:5920B696-2415-4429-AEE9-D26086D3B208@.microsoft.com...
>
>|||Hi Gert E.R. Drapers,
Thanks for your response.
I need to implement first point what you mentioned in your post below in XP
code. Can you please guide me with sample. I just started learning and
working on SQL Server 2005. We are using OLEDB not ODBC.
Regards
Veeru
"Gert E.R. Drapers" wrote:

> I can think of 2 strategies:
> 1) If you only have to update a single value at the end of your processing
> inside the XP code, you are best of returning this as a output parameter o
r
> set of output parameters and use this inside a regular SP to do an INSERT,
> UPDATE or DELETE
> 2) If you need to do this within the XP, you need to create a loop back
> connection, using ODBC, OLE-DB or ADO. And issue the INSERT, UPDATE or
> DELETE on the loopback connection. There is a ODBC sample for setting up a
> loop back connection using ODBC that ships with SQL Server. The rest is
> normal ODBC programming of which there are many samples flying around.
> GertD@.SQLDev.Net
>
> "Veeru" <Veeru@.discussions.microsoft.com> wrote in message
> news:5920B696-2415-4429-AEE9-D26086D3B208@.microsoft.com...
>
>|||BTW if you're using SQL 2005, then don't use extended stored proc's, as they
are depracated. Use the SQLCLR to create assemblies.
"Veeru" <Veeru@.discussions.microsoft.com> wrote in message
news:79390102-F24A-40AC-A19E-A9D44491AF40@.microsoft.com...
> Hi Thanks for your responce.
> The documentation in the below path you have sent saying that the sample
> is
> available at x:\Program Files\Microsoft SQL
> Server\80\Tools\Devtools\Samples\ODS\Xp_
dblib. but i do not have it. plz
> send
> if you have that.
> "Mike C#" wrote:
>|||It should be in C:\Program Files\Microsoft SQL
Server\80\Tools\Devtools\Samples\ODS\Xp_
dblib, assuming you installed on the
C: drive. They are installed by the SQL 2000 installer.
"Veeru" <Veeru@.discussions.microsoft.com> wrote in message
news:79390102-F24A-40AC-A19E-A9D44491AF40@.microsoft.com...
> Hi Thanks for your responce.
> The documentation in the below path you have sent saying that the sample
> is
> available at x:\Program Files\Microsoft SQL
> Server\80\Tools\Devtools\Samples\ODS\Xp_
dblib. but i do not have it. plz
> send
> if you have that.
> "Mike C#" wrote:
>