Return Code from Stored Proc

I am doing an insert with a stored proc using the ExecuteNonQuery in the
DataAccess Block from Microsoft. My parameters are inserted correctly into
the database but my return code is always a -1 instead of 0. Please review
this code and tell me if you see something I am doing wrong> Thanks in
Advance!
CREATE PROCEDURE insertTrans_sp
(@.batch_id numeric,
@.cpi numeric,
@.visit numeric,
@.qty numeric,
@.gl_proc varchar(8),
@.gl_desc varchar(50),
@.charge float,
@.processed_date datetime,
@.processed_by varchar(50))
AS
SET NOCOUNT ON
DECLARE
@.err int,
@.err_desc varchar(50)
Begin Transaction
INSERT INTO Trans
(batch_id,
cpi,
visit,
qty,
gl_proc,
gl_desc,
charge,
processed_date,
processed_by)
VALUES
(@.batch_id,
@.cpi,
@.visit,
@.qty,
@.gl_proc,
@.gl_desc,
@.charge,
@.processed_date,
@.processed_by)
SET @.err = @.@.error
if @.err <> 0 GOTO ErrorHandler
COMMIT Transaction
return 0
ErrorHandler:
SET @.err_desc = 'Error occurred in insertTrans_sp'
EXEC insertErrLog_sp @.err, @.err_desc
return -100
GO
Robert HillRobert,
Exactly how are you executing this? Is this ADO, ADO.net etc? A couple of
comments here. One is that it is not necessary to wrap the Insert in an
explicit transaction. Any single sql statement is ATOMIC by itself. The
insert will either succeed or it won't. By wrapping it in a tran you now
have to commit or roll it back yourself. In this case you don't even have
any code to issue a rollback. You should always test the trancount level
before issuing a commit or rollback.
IF @.@.TRANCOUNT > 0
COMMIT TRAN
You declare a series of variables as Numeric but do not specify their
precision or scale. Always specify the size or scale of all data types.
These all look like Integers anyway. If that is the case it is more
efficient to declare them as integers than numeric.
Andrew J. Kelly SQL MVP
"Robert" <rhill938@.hotmail.com> wrote in message
news:0B27280E-35B3-4548-B928-34A0E227453F@.microsoft.com...
>I am doing an insert with a stored proc using the ExecuteNonQuery in the
> DataAccess Block from Microsoft. My parameters are inserted correctly
> into
> the database but my return code is always a -1 instead of 0. Please
> review
> this code and tell me if you see something I am doing wrong> Thanks in
> Advance!
> CREATE PROCEDURE insertTrans_sp
> (@.batch_id numeric,
> @.cpi numeric,
> @.visit numeric,
> @.qty numeric,
> @.gl_proc varchar(8),
> @.gl_desc varchar(50),
> @.charge float,
> @.processed_date datetime,
> @.processed_by varchar(50))
> AS
> SET NOCOUNT ON
> DECLARE
> @.err int,
> @.err_desc varchar(50)
> Begin Transaction
> INSERT INTO Trans
> (batch_id,
> cpi,
> visit,
> qty,
> gl_proc,
> gl_desc,
> charge,
> processed_date,
> processed_by)
> VALUES
> (@.batch_id,
> @.cpi,
> @.visit,
> @.qty,
> @.gl_proc,
> @.gl_desc,
> @.charge,
> @.processed_date,
> @.processed_by)
> SET @.err = @.@.error
> if @.err <> 0 GOTO ErrorHandler
> COMMIT Transaction
> return 0
> ErrorHandler:
> SET @.err_desc = 'Error occurred in insertTrans_sp'
> EXEC insertErrLog_sp @.err, @.err_desc
> return -100
> GO
> --
> Robert Hill
>|||in order to get a return code, I believe you need to use execute Scalar
Greg Jackson
PDX, Oregon|||"pdxJaxon" <GregoryAJackson@.Hotmail.com> wrote in message
news:eAujW9fHFHA.1860@.TK2MSFTNGP15.phx.gbl...
> in order to get a return code, I believe you need to use execute Scalar
>
Yes, the return from ExecuteNonQuery is NOT the stored procedure return
code.
No ExecuteScalar won't help. To get the return code you will need to use
CommandType.Text and write a batch like:
exec @.rc=MyProc(@.p1,@.p2,@.p3)
then bind an output parameter to @.rc.
In your case it's not necessary to test the return code. From client code
if something goes wrong you will get a SqlException. A stored procedure
return code is really just for other stored procedures. When one procedure
calls another procedure the calling procedure cannot intercept the error
messages generated by the called procedure, so it must use the return code
to determine if something went wrong. From SqlClient the error message will
appear as a SqlException and you can examine it in your catch block.
David|||Thanks.
"David Browne" wrote:

> "pdxJaxon" <GregoryAJackson@.Hotmail.com> wrote in message
> news:eAujW9fHFHA.1860@.TK2MSFTNGP15.phx.gbl...
> Yes, the return from ExecuteNonQuery is NOT the stored procedure return
> code.
> No ExecuteScalar won't help. To get the return code you will need to use
> CommandType.Text and write a batch like:
> exec @.rc=MyProc(@.p1,@.p2,@.p3)
> then bind an output parameter to @.rc.
> In your case it's not necessary to test the return code. From client code
> if something goes wrong you will get a SqlException. A stored procedure
> return code is really just for other stored procedures. When one procedur
e
> calls another procedure the calling procedure cannot intercept the error
> messages generated by the called procedure, so it must use the return code
> to determine if something went wrong. From SqlClient the error message wi
ll
> appear as a SqlException and you can examine it in your catch block.
> David
>
>|||Robert,
you dont have to set the command type to text.
you can (And in my opinion, should) leave the command type to "stored
procedure"
you can create a parameter object with direction of "Output" and get the
return value.
GAJ|||Thanks.
My solution was to use ExecuteScalar in the Application Block with the
command type "stored procedure" with an output parameter.
Robert
"pdxJaxon" wrote:

> Robert,
> you dont have to set the command type to text.
> you can (And in my opinion, should) leave the command type to "stored
> procedure"
> you can create a parameter object with direction of "Output" and get the
> return value.
> GAJ
>
>|||pdxJaxon wrote:
> Robert,
> you dont have to set the command type to text.
> you can (And in my opinion, should) leave the command type to "stored
> procedure"
> you can create a parameter object with direction of "Output" and get
> the return value.
>
You can also use a parameter with direction of "ReturnValue" to get the
return value ...
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"|||Thanks!
I now need to return 2 values from a table. I tested my stored proc in
query analyzer and it seemed to work fine, returning the values I need. The
foloowing is the code I use in my app to call the stored proc using a
SqlParameter array. All I get back is a -1. What am I doing wrong?
SqlParameter[] oParms = new SqlParameter[3];
try
{
oParms[0] = new SqlParameter("@.cpi", sCPI);
oParms[1] = new SqlParameter("@.visit", ParameterDirection.Output);
oParms[2] = new SqlParameter("@.batch_id", ParameterDirection.Output);
ConnectString oCn = new ConnectString();
cn = oCn.GetConnection();
object oRes = new object();
oRes = SqlHelper.ExecuteNonQuery(cn,
CommandType.StoredProcedure,
"verifyCPIandBatchId_sp",
oParms);
Robert
"Bob Barrows [MVP]" wrote:

> pdxJaxon wrote:
> You can also use a parameter with direction of "ReturnValue" to get the
> return value ...
> --
> Microsoft MVP - ASP/ASP.NET
> Please reply to the newsgroup. This email account is my spam trap so I
> don't check it very often. If you must reply off-line, then remove the
> "NO SPAM"
>
>|||close the connection before attempting to read the output parms
GAJ

return an id while doing an insert\update to a table

Hi people,

i Have a small issue. I need to be able to retrive an id number of a new row to a table using the the insert into command. I was able to do this in sql 2000 but the same sql does not work now in 2005. here is the code

"Set NoCount On; select user_id from users insert into users (username) values('" & CurrentUser & "')"

This used to work in sql2000,

I am woundering if anyone could help me or point me in the right direction for doing this with SQL 2005

Best regards

RBowden

Did you try putting a semincolumn between the statements (before the insert) ?

HTH, jens Suessmeyer.

http://www.sqlserver2005.de
|||

I tried putting the ; before the insert function it is still returns 0

any other ideas?

|||

Ah, ok now I know what you mean. You are refering to the OUTPUT clause in SQL Server 2005.

"Set NoCount On; DECLARE @.Somevar VARCHAR(10);insert into users (username) OUTPUT user_id INTO @.SomeVar values('" & CurrentUser & "')"; SELECT @.SomeVar"

Look in the BOL, there should me some examples around that. If you are using an IDENTITY Column for the userid cou can also query the SCOPE_IDENTITY() function for the new identity value.

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

|||

Cheers,

thank you very much for your help, that worked a treat.

keep up the good advice

all the best

Return a value after insert the query

Hi!

create table testReturn
(
id int identity(100,1),
name varchar(10)
)

How can I return the value of identity column after inserting the value.

Dim objConn As SqlConnection
Dim SQLCmd As SqlClient.SqlCommand
Dim ds As New DataSet
Dim strsql As String

Try

objConn = New SqlConnection
objConn.ConnectionString = _
"Network Library=DBMSSOCN;" & _
"Data Source=localhost;" & _
"Initial Catalog=mydb;" & _
"User ID=userid;" & _
"Password=pass"

objConn.Open()

strsql = "insert into testReturn values ('a')"
SQLCmd = New SqlClient.SqlCommand(strsql, objConn)
Dim rowsAffected As Integer = 0
rowsAffected = SQLCmd.ExecuteNonQuery

Dim rv As String
rv = SQLCmd.Parameters(0).Value.ToString()
Response.Write(rv)

Catch ex As Exception
Response.Write(ex.ToString)
End Try

SeeHow do I get the IDENTITY / AUTONUMBER value for the row I inserted?

|||

strsql = "insert into testReturn values ('a'); select @.@.Identity"
SQLCmd = New SqlClient.SqlCommand(strsql, objConn)

dim Identiy as Object

Identity = SQLCmd.ExecuteScalar

|||

You need to put "Select @.@.identity" statement immediately after your insert query so it would return the ID value of the record inserted, using the preceding Insert statement.

Cheers
Ritesh

|||

Using @.@.IDENTITY is an vulnerable approach, since it returns the last inserted id of any table. If you are using SQL 2005 then you can use the OUTPUT clause.

SeeHow to get an Identity value with SQL Server 2005

|||

You can either use @.@.identity or scope_identity().

@.@.identity gives you the last generated identity value.

scope_identity() gives you the last generated identity value for current scope.

Just have a quick look at BOL for further understanding.

Hope this will help.

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

return @@identity for another function

What I'm trying to do is provide a solution where users can upload an image and a description, to a database, so I'm trying to insert the title and description then return the @.@.identity for the image upload function which will name the image like this
image_23.jpg (23 being the @.@.identity) resize it and save it to specified directory

I cant seem to get the identity to return to my script.
This is my SP

CREATE PROCEDURE SP_Insertad
(
@.catid int,
@.subcatid int,
@.areaid int,
@.uid int,
@.adtitle varchar(255),
@.addescription varchar(1000)
)

AS
Insert Into Tbl_ad
(ad_title, ad_description,ad_area,ad_ui_id,ad_active,ad_date,ad_ct_id,ad_sc_id,ad_location)
VALUES
(@.adtitle,@.addescription,@.areaid, @.uid, 0,convert(varchar, GETUTCDATE(), 101), @.catid, @.subcatid, 1)

select @.@.identity
return
GO

I tested in query analyser, and it works fine, so It must be my code. this is my function

Sub Insert_pic(sender as object, e as eventargs)

Dim catid = Request.form("ddcats")
Dim subcatid = Request.form("subcatrad")
Dim adtitle = Request.Form("txttitle")
Dim AdDescription = Request.form("txtdescription")
Dim uid = getUID(Context.User.Identity.Name)
Dim areaid = Request.form("ddarea")
SQLConnect = new SqlConnection(ConfigurationSettings.Appsettings("mydB"))

SQLCommander = New SQLCommand("SP_INSERTad", SQLConnect)

SQLCommander.Commandtype = Commandtype.StoredProcedure

SQLCommander.Parameters.add("@.adtitle", adtitle)
SQLCommander.Parameters.add("@.addescription", addescription)
SQLCommander.Parameters.add("@.catid", catid)
SQLCommander.Parameters.add("@.subcatid", subcatid)
SQLCommander.Parameters.add("@.uid", uid)
SQLCommander.Parameters.add("@.areaid", areaid)

'// this section not working right, it wont write return id

Dim paramreturn as SQLParameter
paramreturn = SQLCommander.Parameters.Add("ReturnValue", SQLDBType.Int)
ParamReturn.Direction = ParameterDirection.ReturnValue

response.write(SQLCommander.Parameters("ReturnValue").Value)

SQLConnect.open()
SQLCommander.ExecuteNonQuery()
SQLConnect.close()

End sub

Can anybody see anything I missing? I appreciate any imputYour existing code will work by making the following changes:

1) Take out the following lines:

Dim paramreturn as SQLParameter
paramreturn = SQLCommander.Parameters.Add("ReturnValue", SQLDBType.Int)
ParamReturn.Direction = ParameterDirection.ReturnValue

2) Make the following change to the SQLCommander.ExecuteNonQuery() line:

Dim returnVal As Integer = SQLCommander.ExecuteScalar()

See the following link about the ExecuteScalar() method:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpref/html/frlrfsystemdatasqlclientsqlcommandclassexecutescalartopic.asp|||you should say "return @.@.ID..." rather than "select"|||i changed it to
...
AS
Insert Into Tbl_Ads
(ad_title, ad_description,ad_area,ad_ui_id,ad_active,ad_date,ad_ct_id,ad_sc_id,ad_location)
VALUES
(@.adtitle,@.addescription,@.areaid, @.uid, 0,convert(varchar, GETUTCDATE(), 101), @.catid, @.subcatid, 1)

return @.@.identity
GO

I think the break down is here -
Dim paramreturn as SQLParameter
paramreturn = SQLCommander.Parameters.Add("ReturnValue", SQLDBType.Int)
ParamReturn.Direction = ParameterDirection.ReturnValue

response.write(SQLCommander.Parameters("ReturnValue").Value)

it's not writing to page. Stumped|||ghost opz ,
that worked, I dont know why or how but That did the trick.

thanks a million!

striker

retrieving user's permissions for each table

Hi ,
Is it possible to get the user's permissions to each table i.e user can
select , delete , insert , update , execute , DRI
what does DRI means and what is it used for ?
and also it it possible to get the permissions up till the column-level ?
what are the tables that these info are kept ?
appreciate ur advise
tks & rdgs
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200606/1To get the permissions for each user, I suggest an inner join between
the sysprotects and syspermissions tables on uid = grantee
DRI stands for Declarative Referential Integrity...see books online
Column level permissions: See the [Columns] field of the sysprotects
table
HTH
SQLPoet
maxzsim via SQLMonster.com wrote:
> Hi ,
> Is it possible to get the user's permissions to each table i.e user can
> select , delete , insert , update , execute , DRI
> what does DRI means and what is it used for ?
> and also it it possible to get the permissions up till the column-level ?
> what are the tables that these info are kept ?
> appreciate ur advise
> tks & rdgs
> --
> Message posted via SQLMonster.com
> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200606/1|||Hi
You could look at the syspermissions table, but you would also need to
enumerate group membership and which permissions they have indirectly.
John
"maxzsim via SQLMonster.com" wrote:
> Hi ,
> Is it possible to get the user's permissions to each table i.e user can
> select , delete , insert , update , execute , DRI
> what does DRI means and what is it used for ?
> and also it it possible to get the permissions up till the column-level ?
> what are the tables that these info are kept ?
> appreciate ur advise
> tks & rdgs
> --
> Message posted via SQLMonster.com
> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200606/1
>|||tk you ppl for ur advice
rdgs
SQLPoet wrote:
>To get the permissions for each user, I suggest an inner join between
>the sysprotects and syspermissions tables on uid = grantee
>DRI stands for Declarative Referential Integrity...see books online
>Column level permissions: See the [Columns] field of the sysprotects
>table
>HTH
>SQLPoet
>> Hi ,
>[quoted text clipped - 10 lines]
>> tks & rdgs
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200607/1

retrieving user's permissions for each table

Hi ,
Is it possible to get the user's permissions to each table i.e user can
select , delete , insert , update , execute , DRI
what does DRI means and what is it used for ?
and also it it possible to get the permissions up till the column-level ?
what are the tables that these info are kept ?
appreciate ur advise
tks & rdgs
Message posted via droptable.com
http://www.droptable.com/Uwe/Forum...server/200606/1To get the permissions for each user, I suggest an inner join between
the sysprotects and syspermissions tables on uid = grantee
DRI stands for Declarative Referential Integrity...see books online
Column level permissions: See the [Columns] field of the sysprotects
table
HTH
SQLPoet
maxzsim via droptable.com wrote:
> Hi ,
> Is it possible to get the user's permissions to each table i.e user can
> select , delete , insert , update , execute , DRI
> what does DRI means and what is it used for ?
> and also it it possible to get the permissions up till the column-level ?
> what are the tables that these info are kept ?
> appreciate ur advise
> tks & rdgs
> --
> Message posted via droptable.com
> http://www.droptable.com/Uwe/Forum...server/200606/1|||Hi
You could look at the syspermissions table, but you would also need to
enumerate group membership and which permissions they have indirectly.
John
"maxzsim via droptable.com" wrote:

> Hi ,
> Is it possible to get the user's permissions to each table i.e user can
> select , delete , insert , update , execute , DRI
> what does DRI means and what is it used for ?
> and also it it possible to get the permissions up till the column-level ?
> what are the tables that these info are kept ?
> appreciate ur advise
> tks & rdgs
> --
> Message posted via droptable.com
> http://www.droptable.com/Uwe/Forum...server/200606/1
>|||To get the permissions for each user, I suggest an inner join between
the sysprotects and syspermissions tables on uid = grantee
DRI stands for Declarative Referential Integrity...see books online
Column level permissions: See the [Columns] field of the sysprotects
table
HTH
SQLPoet
maxzsim via droptable.com wrote:
> Hi ,
> Is it possible to get the user's permissions to each table i.e user can
> select , delete , insert , update , execute , DRI
> what does DRI means and what is it used for ?
> and also it it possible to get the permissions up till the column-level ?
> what are the tables that these info are kept ?
> appreciate ur advise
> tks & rdgs
> --
> Message posted via droptable.com
> http://www.droptable.com/Uwe/Forum...server/200606/1|||Hi
You could look at the syspermissions table, but you would also need to
enumerate group membership and which permissions they have indirectly.
John
"maxzsim via droptable.com" wrote:

> Hi ,
> Is it possible to get the user's permissions to each table i.e user can
> select , delete , insert , update , execute , DRI
> what does DRI means and what is it used for ?
> and also it it possible to get the permissions up till the column-level ?
> what are the tables that these info are kept ?
> appreciate ur advise
> tks & rdgs
> --
> Message posted via droptable.com
> http://www.droptable.com/Uwe/Forum...server/200606/1
>|||tk you ppl for ur advice
rdgs
SQLPoet wrote:[vbcol=seagreen]
>To get the permissions for each user, I suggest an inner join between
>the sysprotects and syspermissions tables on uid = grantee
>DRI stands for Declarative Referential Integrity...see books online
>Column level permissions: See the [Columns] field of the sysprotects
>table
>HTH
>SQLPoet
>
>[quoted text clipped - 10 lines]
Message posted via droptable.com
http://www.droptable.com/Uwe/Forum...server/200607/1|||tk you ppl for ur advice
rdgs
SQLPoet wrote:[vbcol=seagreen]
>To get the permissions for each user, I suggest an inner join between
>the sysprotects and syspermissions tables on uid = grantee
>DRI stands for Declarative Referential Integrity...see books online
>Column level permissions: See the [Columns] field of the sysprotects
>table
>HTH
>SQLPoet
>
>[quoted text clipped - 10 lines]
Message posted via droptable.com
http://www.droptable.com/Uwe/Forum...server/200607/1

Retrieving Scope_Entity or Identity from an SQL Insert

The following code inserts a record into a table. I now wish to retrieve the IDENTITY of that entry into a variable so that I can use it again as input for other inserts. Can someone offer assistance in handling this... I tried several alternatives that I found on the internet but none seem to work...

Thanks!

Dim objConn3As SqlConnection
Dim mySettings3AsNew NameValueCollection
mySettings3 = AppSettings
Dim strConn3AsString
strConn3 = mySettings3("connString")
objConn3 =New SqlConnection(strConn3)
Dim strInsertPatientAsString
Dim cmdInsertAs SqlCommand
Dim strddlSexAsString
Dim strddlPatientStateAsString
Dim rowsAffectedAsInteger

strddlSex = ddlSex.SelectedItem.Text
strddlPatientState = ddlPatientState.SelectedItem.Text

strInsertPatient ="Insert ClinicalPatient ( UserID, Accession, FirstName, MI, " & _
"LastName, MedRecord, ddlSex, DOB, Address1, Address2, City, Suite, strddlPatientState, " & _
"ZIP, HomeTelephone, OutsideNYC, ClinicalImpression, Today_Date_Month, Today_Date_Day, " & _
"Today_Date_Year) Values (@.UserID, @.Accession, @.FirstName, @.MI, @.LastName, @.MedRecord, " & _
"'" & strddlSex &"', @.DOB, @.Address1, @.Address2, @.City, @.Suite , '" & strddlPatientState &"', " & _
"@.ZIP, @.HomeTelephone, @.OutsideNYC, @.ClinicalImpression, @.Today_Date_Month, @.Today_Date_Day, " & _
"@.Today_Date_Year)SELECT @.@.IDENTITY AS NewID SET NOCOUNT OFF"

cmdInsert =New SqlCommand(strInsertPatient, objConn3)

cmdInsert.Parameters.Add("@.UserID","Joe For Now")
cmdInsert.Parameters.Add("@.Accession", Accession.Text)
cmdInsert.Parameters.Add("@.LastName", LastName.Text)
cmdInsert.Parameters.Add("@.MI", MI.Text)
cmdInsert.Parameters.Add("@.FirstName", FirstName.Text)
cmdInsert.Parameters.Add("@.MedRecord", MedRecord.Text)
cmdInsert.Parameters.Add("@.ddlSex", strddlSex)
cmdInsert.Parameters.Add("@.DOB", DOB.Text)
cmdInsert.Parameters.Add("@.Address1", Address1.Text)
cmdInsert.Parameters.Add("@.Address2", Address2.Text)
cmdInsert.Parameters.Add("@.City", City.Text)
cmdInsert.Parameters.Add("@.Suite", Suite.Text)
cmdInsert.Parameters.Add("@.strddlPatientState", strddlPatientState)
cmdInsert.Parameters.Add("@.ZIP", zip.Text)
cmdInsert.Parameters.Add("@.HomeTelephone", Phone.Text)
cmdInsert.Parameters.Add("@.OutsideNYC", OutsideNYC.Text)
cmdInsert.Parameters.Add("@.ClinicalImpression", ClinicalImpression.Text)
cmdInsert.Parameters.Add("@.Today_Date_Month", Today_Date_Month.Text)
cmdInsert.Parameters.Add("@.Today_Date_Day", Today_Date_Day.Text)
cmdInsert.Parameters.Add("@.Today_Date_Year", Today_Date_Year.Text)

objConn3.Open()
cmdInsert.ExecuteNonQuery()
objConn3.Close()

Try this - a zillion ways to get Scope_Identity back:http://www.mikesdotnetting.com/Article.aspx?ArticleID=54

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 Login Name?

Hi,
I need to know how to retrieve the login name of the user into a local
variable so that I can insert it into a table.
I want to do something like this:
DECLARE @.USERNAME varchar(50)
DECLARE @.OTHERFIELD varchar(50)
SET @.USERNAME = EXEC sp_who ?or something like this?
INSERT INTO MYTABLE(Username, OtherField)
VALUES(@.USERNAME, @.OTHERFIELD)
Can anyone show me the correct way to do this?
JDJoe Delphi wrote:
> I need to know how to retrieve the login name of the user into a local
> variable so that I can insert it into a table.
> SET @.USERNAME = EXEC sp_who ?or something like this?
select @.USERNAME = SYSTEM_USER
SYSTEM_USER is a built-in system function. You can look up "system
functions" in the SQL help for some other values as well.

Retrieving Identity after insert

Hey,

I've been having problems - when trying to insert a new row i've been trying to get back the unique ID for that row. I've added "SELECT @.MY_ID = SCOPE_IDENTITY();" to my query but I am unable get the data. If anyone has a better approach to this let me know because I am having lots of problems.

Thanks,
Lang

hi,

can you try using @.@.Identity please.

morever please put some code what exactly you've done.

regards,

satish.

|||

Scope Identity is safer than @.@.Identity. @.@.Identity could possibly give you the wrong ID back if your table has triggers that also insert records.

Is @.MY_ID being returned as an output parameter or is this something you are simply doing in a stored procedure with no object/class interaction ?

Retrieving ID after INSERT Behind the scenes - Not using a grid view

Hi there. I looked through many other posts describing scope_identity but I am trying to achieve the same thing from the code behind. i.e. I need to some how call a method to execute the insert command and then return the ID so I can update other tables with this value.

I was going down the road of something like:

addnew as sqldatasource = new sqldatasource

addnew.insertcommand = "Insert into...; def @.NewID as scope_identity"

addnew.insert()

The problem is I don't know how to add a output parameter using VB or how to retrieve it.

Any help would be much appreciated, this is doing my head in...

Doug.

Hi, you can just new a SqlParameter and set the Direction to output like this:

Dim parameterdat2AsNew SqlParameter("@.accountnum", SqlDbType.NVarChar, 20, ParameterDirection.Output)

Retrieving ID after insert

Hi, I have a Stored procedure doind an INSERT which then returns @.@.Identity.

I have set this parameters direction to output, however, when i run it I get an error saying procedure is expecting this output parameter..

Not sure where I am going wrong...

Can someone please help with retrieving the ID after an insert. What is the correct code in .NET?

Many ThanksShow your SP, and how you call it. You can get info like htis back as an output parameter, or as a return code. Absent knowing what you are doing, it is impossible to help.|||This is just a suggestion.

First define the input parameters and assign its values.

'Define the output parameter
With cmd.Parameters.Add("@.outputField", SqlDbType.Int)
.Direction = ParameterDirection.Output
End With

'Read the result
Dim dr As SqlDataReader = cmd.ExecuteReader
Dim returnId As Integer
If dr.Read Then
returnId = dr("OutputField")
End If
dr.Close()

My cmd is the reference to the SqlCommand.
OutputField – Field which you want to return.

Hope this might give you some help.|||Use Scope_Identity() instead of @.@.Identity.

Also..

cmd.ExecuteNonReader()

returnID = cmd.Parameters("@.outputField").Value
cmd.Dipose()

Retrieving GUID from INSERT query -- HELP

I have tried many code sample but I am very stuck

The primary key of my database (SQL server 2005) table is a uniqueidentifier.

I am using the following code to insert a row into my table:

myCommand.CommandText = sqlEvent.ToString(); //add the sql query to the command
myCommand.Connection = this.dbConnection; //add the database connection to the command
myCommand.ExecuteNonQuery(); //execute the insert query

I need to retrieve the GUID that is automatically generated when the insert command is executed.

Can someone help me? How do I get the GUID that is automatically generated? I have tried lots of things like using

string _id = (string)myCommand.ExecuteScalar();

and I am still stuck. I will really appreciate it if someone can refer me to some code sample.

HELP

Here is a sample:

CREATE TABLE T1(
id uniqueidentifier PRIMARY KEY DEFAULT(NEWID()),
name nvarchar(100)
);

INSERT INTO T1 (name) OUTPUT inserted.id VALUES('name 1');

SELECT * from T1;

So you just need to call T-SQL similar to "INSERT INTO T1 (name) OUTPUT inserted.id VALUES('name 1');" with ExecuteScalar();

Thanks,

Zuomin

Retrieving generated keys

I have the following scenario:
MyTable
id number entity(1, 1)
name nvarchar
sniplet of Java code:
String sql = "Insert Into MyTable (name) Values( 'MyName' )";
int updateCnt = stmt.executeUpdate(sql, Statement.RETURN_GENERATED_KEYS);
ResultSet rs = stmt.getGeneratedKeys();
The executeUpdate() method throws an java.lang.AbstractMethodError exception.
How do I get the value of "id" after running my insert statement?
The microsoft example goes through a lot of object instantiation with
prepared calls and all that I don't necessarily want to complicate the
previous code by doing all that. But if I do, could someone please help me
with the conversion? I always use the above approach.
Thanks.
The MS driver is a JDBC 2.0 implementation.
Statement.RETURN_GENERATED_KEYS is a JDBC 3.0 feature. Either use a
JDBC 3.0 driver or append SELECT SCOPE_IDENTITY() to all your INSERT
statements.
Alin,
The jTDS Project.
|||If youre using SQL Server why not do the insert through a stored
procedure?...this way you can use the Scope_Identity function to return
back the value of the newly inserted identity to Java..let me know if
you need some sample code...
|||Jimbo wrote:
> If youre using SQL Server why not do the insert through a stored
> procedure?...this way you can use the Scope_Identity function to
return
> back the value of the newly inserted identity to Java..let me know if
> you need some sample code...
You can use scope_identity() without a stored procedure. Just execute
something like "INSERT ... SELECT scope_identity()". The problem is
that the JDBC code won't work across databases.
Alin.

Retrieve windows user account

Hi, All
I'm use SQL Server 2005 and want to know is any way can retrieve windows
user account?
I want to retrieve windows user account and insert into the table.
I don't want to use any program lanaguage just want to know SQL 2005 has any
kind of function
or store procedure can support this.
Please give me function or store procedure name and reference.
Thanks for any advice!
Angiangi
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/565984cd-60c6-4df7-83ea-
2349b838ccb2.htm
"angi" <angi@.news.microsoft.com> wrote in message
news:OgEkdW0xGHA.2400@.TK2MSFTNGP03.phx.gbl...
> Hi, All
> I'm use SQL Server 2005 and want to know is any way can retrieve windows
> user account?
> I want to retrieve windows user account and insert into the table.
> I don't want to use any program lanaguage just want to know SQL 2005 has
> any kind of function
> or store procedure can support this.
> Please give me function or store procedure name and reference.
> Thanks for any advice!
> Angi
>|||Thanks for Uri.
But it my fault that didn't describe it clearly.
SYSTEM_USER retrive the windows user account who login the SQL Server.
But what I want is not who login to SQL Server and retrive it.
I want is retrive all of windows user account or user name on local server,
on windows server.
Example:
There are 10 windows user accounts on Local Server, such as John, Mary, May
and so on.
They are window server's user and maybe just John has the right to access
SQL Server.
And I want to use some kinds of SP or Function to get John, Mary, May's user
name
(not just login SQL Server's user, not SYSTEM_USER just show
"LOCALHOST\John"),
or some function like LDAP can get Windows user account or user name through
the T-SQL to retrive all of the windows user account or user name.
Such AS:
SELECT xxFn ... AS Username
EXEC xxSp ... AS Username
Username
----
John
Mary
May <-- user's account on windows server (not who login sql server)
...
uh... hope someone know what is my requirement!
Thanks for help!
Angi
"Uri Dimant" <urid@.iscar.co.il> glsD:%236RU9B1xGHA.480@.TK2MSFTNGP06.phx.gbl...[vbc
ol=seagreen]
> angi
> ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/565984cd-60c6-4df7-83e
a-2349b838ccb2.htm
>
> "angi" <angi@.news.microsoft.com> wrote in message
> news:OgEkdW0xGHA.2400@.TK2MSFTNGP03.phx.gbl...
>[/vbcol]

Retrieve Values from SQL Server into Winforms

Hi all,

I want to insert information like SQL Server Version, current sql server user etc into a form, How will I achieve this? I have followed this question at http://www.vbforums.com/showthread.php?t=357605 ,but I havent had an answer to my question yet. Please help, I am stuck and can't go on with my application until I have figured this out.

Thanks alot in advance

Rudi Groenewald

You can use various SQL Server Functions for this (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_fa-fz_7oqb.asp).

Here's an example:

select serverproperty('ProductVersion') as ProductVersion
serverproperty('Edition') as Edition;

On my computer this returns:

Product Version Edition
-
9.00.1116 Express Edition

Hope this helps,
Josh Lindenmuth

Retrieve value within a data flow

hi,

I am doing a data flow to insert data into a dimension table(A) and master surrogate key table(B). Master surrogate key table(B) will be inserted prior to dimension table(A). My dimension table(A)'s key is depend on the last key in master surrogate key table(B). The data flow started from a flat file and checking if the key found in dimension table. If not found, insert a record into B. Then generate the new record to A with max id from B.

My question is how to retrieve the max id in master surrogate key table(B) in a data flow and use it as an input to my dimension table?

Have you tried the LOOKUP component? That's the sort of thing it is designed to do.

-Jamie

Retrieve truncated data from BULK INSERT?

Hi everyone. Does anyone know if you can retrieve truncated data from a BULK INSERT operation?

We have a file that needs to be inserted into a SQL Server Database. There is a field that has a maximum of 8000 characters, but some times users submit files that have more than that. We need to be able to capture the truncated data. The BULK INSERT operation does not throw an error. The only way I can think of to get the data is if I bulk insert the data into a temporary table with a memo field and then copy it over, but that may really slow down the SP.

Has anyone encountered this situation before? I also have the option of parsing the file in .NET.

Thanks and take care,

Angelwhat is a memo field? do you mean text or ntext?

also, if you are on 2005 you can use varchar(max) for the column type and you won't have this problem.

EDIT: you can get force the bulk insert to terminate on the first error if you use the MAXERRORS option and set it to 1.
http://msdn2.microsoft.com/en-us/library/ms188365.aspx|||The bulk insert does not throw a warning/error? Is the setting ANSI_WARNINGS set to OFF?|||The bulk insert does not throw a warning/error? Is the setting ANSI_WARNINGS set to OFF?

the default value of MAXERRORS is 10, so if there are less than 10 truncations, it won't fail.

However, if you use the ERRORFILE option, you can discover if there were any rows that failed to make it in even when MAXERRORS is not reached.|||Hi again everyone. Wow, thank you for all of the replies.

First, yes, memo = text. Sorry, I learned the name first as memo somewhere else ...

Second, should BULK INSERT throw an error during a truncation? I really do not want the insert to fail - I just want to capture the truncated data. If I changed it to do a BULK INSERT with the ERRORFILE option, will the ERRORFILE file be filled with the rows that were truncated?

Also, we are using 2005 for some DBs, but not this one. The limitation on the field is a requirement. The goal is to capture the truncated data and report it back to the user.|||I would load it to a staging table and change the datatype to text. Then I would find al the rows that exceed 8000 using DATALENGTH(Col)>8000 in the predicate|||Yeah, the staging table is the best option. There's no way to capture the truncated data during load.|||Has anyone used the ERRORFILE option? Is it available in SQL Server (actually MSDE)? If it captures truncated rows and stores them in a file, where does it store them? Is that file something I can reach with .NET? Thank you everyone for helping me!|||FYI: The ERRORFILE option doesn't seem to be available in 2000. Thanks again everyone!|||The bulk insert does not throw a warning/error? Is the setting ANSI_WARNINGS set to OFF?

Actually I don't know if ANSI_WARNINGS is set to off or on since it's not explicitly assigned. I would assume it's turned on because the warnings are thrown back to .NET. Also, when I run the stored proc in Sql server Management studio, I receive the warning error messages.

retrieve the primary keys with SQL DMO and vb.net

Hello,
I am using SQL DMO with VB6, my tool has to generate TSQL Statement INSERT
and UPDATE, INSERT is ok but for an update statement, i have to retreive
the list of primary keys on a table.
Do you know which method to implement to do so ?
Thanks for your help
Olivier
Each Table object has a Keys collection. Each Key has a Type property.
Failing that, you can use T-SQL:
select
*
from
INFORMATION_SCHEMA.KEY_COLUMN_USAGE
where
1 in (
objectproperty (object_id (CONSTRAINT_NAME), 'CnstIsClustKey')
, objectproperty (object_id (CONSTRAINT_NAME), 'CnstIsNonclustKey')
)
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
..
"oLiVieR" <ocheneson@.hotmail.com> wrote in message
news:OMUoOMCyFHA.3556@.TK2MSFTNGP12.phx.gbl...
Hello,
I am using SQL DMO with VB6, my tool has to generate TSQL Statement INSERT
and UPDATE, INSERT is ok but for an update statement, i have to retreive
the list of primary keys on a table.
Do you know which method to implement to do so ?
Thanks for your help
Olivier