Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2000 Forums
 SQL Server Development (2000)
 Begin Tran -- Return recordset

Author  Topic 

MeTitus
Starting Member

7 Posts
Posted - 2006-08-21 : 08:24:44
Hi you all,

What I am trying to do is to insert a record into a table and then retrive the id of that same table.

<code>

BEGIN TRAN
INSERT INTO SIE_ActionsHistory WITH (TABLOCK) (status,description,dateStarted,idEnvironment_FK,idReleaseGlobal_FK,technicalNo_FK,defectsNo_FK,idReleasePartial_FK)
VALUES(0,'for future use',getdate(),1,'REL01','00','00','01')
SELECT MAX(idActionsHistory_PK) as idActionsHistory_PK FROM SIE_ActionsHistory
COMMIT TRAN;

</code>

When I run the query in the query analyser it runs just fine and I can see the returned column idActionsHistory_PK, but if for eg. I run the query in an asp page the recordset doensn't have the comumn

<code>

strSQL = "DECLARE @UsedIndex CHAR(5) BEGIN TRAN "
strSQL = strSQL + "INSERT INTO SIE_ActionsHistory WITH (TABLOCK) (status,description,dateStarted,idEnvironment_FK,idReleaseGlobal_FK,technicalNo_FK,defectsNo_FK,idReleasePartial_FK) "
strSQL = strSQL + "VALUES(0,'for future use',getdate(),1,'REL01','00','00','01') "
strSQL = strSQL + "SELECT @UsedIndex = MAX(idActionsHistory_PK) "
strSQL = strSQL + "FROM SIE_ActionsHistory "
strSQL = strSQL + "COMMIT TRAN;"

Set conn = CreateObject("ADODB.Connection")
Set oRs = CreateObject("ADODB.Recordset")

conn.Open "Provider=SQLOLEDB.1;Data Source=vfpt-siebarb01;Initial Catalog=CM_MDW_BAK;user id ='cm_mdw';password='cm_mdw'"
oRs.Open strSQL, conn
'oRs.Close
'Set oRs = Nothing

manageDatabase = oRs("@UsedIndex").Value ' Can't find the row
</code>

Any idea why this is hapenning?

Any help is welcome,
Thanks,

MeTitus

MeTitus
Starting Member

7 Posts
Posted - 2006-08-21 : 08:26:59
Sorry the code bellow is the right code

<code>
strSQL = "BEGIN TRAN "
strSQL = strSQL + "INSERT INTO SIE_ActionsHistory WITH (TABLOCK) (status,description,dateStarted,idEnvironment_FK,idReleaseGlobal_FK,technicalNo_FK,defectsNo_FK,idReleasePartial_FK) "
strSQL = strSQL + "VALUES(0,'for future use',getdate(),1,'REL01','00','00','01') "
strSQL = strSQL + "SELECT MAX(idActionsHistory_PK) AS idActionsHistory_PK "
strSQL = strSQL + "FROM SIE_ActionsHistory "
strSQL = strSQL + "COMMIT TRAN;"
</code>
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts
Posted - 2006-08-21 : 08:34:23
You are trying to exeucte two statements. Try to add a ";" to the end of VALUES statement, but before SELECT MAX.

Peter Larsson
Helsingborg, Sweden
Go to Top of Page

MeTitus
Starting Member

7 Posts
Posted - 2006-08-21 : 09:49:03
quote:
Originally posted by Peso

You are trying to exeucte two statements. Try to add a ";" to the end of VALUES statement, but before SELECT MAX.

Peter Larsson
Helsingborg, Sweden


Hi Peter,

I did as you said, but it still not working.

<code>

strSQL = "BEGIN TRAN "
strSQL = strSQL + "INSERT INTO SIE_ActionsHistory WITH (TABLOCK) (status,description,dateStarted,idEnvironment_FK,idReleaseGlobal_FK,technicalNo_FK,defectsNo_FK,idReleasePartial_FK) "
strSQL = strSQL + "VALUES(0,'for future use',getdate(),1,'REL01','00','00','01'); "
strSQL = strSQL + "SELECT MAX(idActionsHistory_PK) AS idActionsHistory_PK "
strSQL = strSQL + "FROM SIE_ActionsHistory; "
strSQL = strSQL + "COMMIT TRAN;"

</code>
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts
Posted - 2006-08-21 : 09:54:23
Do something like this if you insist having the transaction handling on client side.

strSQL = "INSERT INTO SIE_ActionsHistory WITH (TABLOCK) (status,description,dateStarted,idEnvironment_FK,idReleaseGlobal_FK,technicalNo_FK,defectsNo_FK,idReleasePartial_FK) "
strSQL = strSQL + "VALUES(0,'for future use',getdate(),1,'REL01','00','00','01') "

Set conn = CreateObject("ADODB.Connection")
Set oRs = CreateObject("ADODB.Recordset")

conn.Open "Provider=SQLOLEDB.1;Data Source=vfpt-siebarb01;Initial Catalog=CM_MDW_BAK;user id ='cm_mdw';password='cm_mdw'"
conn.BeginTransaction

oRs.Open strSQL, conn

strSQL = "SELECT MAX(idActionsHistory_PK) UsedIndex"
strSQL = strSQL + "FROM SIE_ActionsHistory"

oRs.Open strSQL, conn

manageDatabase = oRs("UsedIndex").Value
oRs.Close
Set oRs = Nothing

if conn.errors.count > 0
conn.rollbacktransaction
else
conn.committransaction



Peter Larsson
Helsingborg, Sweden
Go to Top of Page

MeTitus
Starting Member

7 Posts
Posted - 2006-08-21 : 10:12:19
Once again thanks for your reply Peter.

I havent tryied your eg to see that it works, because anyone clearly knows it will ;). The odd thing here is that the Ado recordset is not capable of "understaning" that one row is being returned.

I have tried this is Ado.net with c# and it works just fine. Maybe its a weekeness of the Ado object.

Many thanks,

MeTitus
Go to Top of Page

jsmith8858
Dr. Cross Join

7423 Posts
Posted - 2006-08-21 : 10:25:00
Two things:

1) add SET NOCOUNT ON before your SQL Code. The INSERT returns a result, and that first result is probably what is being sent back to your client.

2) Use Scope_Identity() to return the just inserted ID; that is the purpose of the function and it is more efficient and more accurate than requerying the table to return the MAX().


- Jeff
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts
Posted - 2006-08-21 : 10:27:08
No it is not. Local variables cannot be returned to the client, except as a resultset, because that is what rs variable is.
You would be better of with code like this
   Dim cn As ADODB.Connection, cmd As ADODB.Command

   On Error Resume Next
   Set cn = modDatabase.Connection(adModeRead)

   Set cmd = modDatabase.Command(cn, "uspDimensionSave")
   With cmd
      If DimensionID < 1 Then
         .Parameters.Append .CreateParameter("@DimensionID", adInteger, adParamInputOutput, 4, Null)
      Else
         .Parameters.Append .CreateParameter("@DimensionID", adInteger, adParamInputOutput, 4, DimensionID)
      End If
      .Parameters.Append .CreateParameter("@DimensionName", adVarChar, adParamInput, 32, DimensionName)
      .Parameters.Append .CreateParameter("@AcceptRules", adBoolean, adParamInput, 1, AcceptRules)
      .Parameters.Append .CreateParameter("@HasOwners", adBoolean, adParamInput, 1, HasOwners)
      .Parameters.Append .CreateParameter("@HasOtherNames", adBoolean, adParamInput, 1, HasOtherNames)
      .Execute , , adExecuteNoRecords
   End With

   If cn.Errors.Count > 0 Then
      modAlsisTM.ShowOutput olsFailure, cn.Errors(0).Description
   Else
      DimensionID = cmd.Parameters("@DimensionID")
      Me.Caption = Trim$(txtName.Text) & " dimension"
      DoSave = True
   End If

   Set cmd = Nothing

   cn.Close
   Set cn = Nothing


Peter Larsson
Helsingborg, Sweden
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts
Posted - 2006-08-21 : 10:28:22
And the stored procedure like this
CREATE PROCEDURE uspDimensionSave
(
	@DimensionID INT OUT,
	@DimensionName VARCHAR(32),
	@AcceptRules BIT,
	@HasOwners BIT,
	@HasOtherNames BIT
)
AS

SET NOCOUNT ON

INSERT INTO	Dimensions
		(
			DimensionName,
			AcceptRules,
			HasOwners,
			HasOtherNames
		)
VALUES	(
			@DimensionName,
			@AcceptRules,
			@HasOwners,
			@HasOtherNames
		)

SELECT	@DimensionID = SCOPE_IDENTITY()


Peter Larsson
Helsingborg, Sweden
Go to Top of Page

madhivanan
Premature Yak Congratulator

22864 Posts
Posted - 2006-08-21 : 11:19:12
Avoid having Concatenated Strings in Client Application. Use Stored Procedure with parameters as suggested by Peso

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

MeTitus
Starting Member

7 Posts
Posted - 2006-09-07 : 10:14:56
I don't agree when someone says that is it a much more wise choice to use stored procedures rather then having it all coded. Imagine that for eg the company I am working for decides to switch its database to another vendor. What happens to all the stored procedures, views and so one??? We have to change them all, at least when doing the way I do, I wont have this sort of problems.

MeTitus
Go to Top of Page

MeTitus
Starting Member

7 Posts
Posted - 2006-09-07 : 10:17:34
quote:
Originally posted by Peso

No it is not. Local variables cannot be returned to the client, except as a resultset, because that is what rs variable is.
You would be better of with code like this
   Dim cn As ADODB.Connection, cmd As ADODB.Command

   On Error Resume Next
   Set cn = modDatabase.Connection(adModeRead)

   Set cmd = modDatabase.Command(cn, "uspDimensionSave")
   With cmd
      If DimensionID < 1 Then
         .Parameters.Append .CreateParameter("@DimensionID", adInteger, adParamInputOutput, 4, Null)
      Else
         .Parameters.Append .CreateParameter("@DimensionID", adInteger, adParamInputOutput, 4, DimensionID)
      End If
      .Parameters.Append .CreateParameter("@DimensionName", adVarChar, adParamInput, 32, DimensionName)
      .Parameters.Append .CreateParameter("@AcceptRules", adBoolean, adParamInput, 1, AcceptRules)
      .Parameters.Append .CreateParameter("@HasOwners", adBoolean, adParamInput, 1, HasOwners)
      .Parameters.Append .CreateParameter("@HasOtherNames", adBoolean, adParamInput, 1, HasOtherNames)
      .Execute , , adExecuteNoRecords
   End With

   If cn.Errors.Count > 0 Then
      modAlsisTM.ShowOutput olsFailure, cn.Errors(0).Description
   Else
      DimensionID = cmd.Parameters("@DimensionID")
      Me.Caption = Trim$(txtName.Text) & " dimension"
      DoSave = True
   End If

   Set cmd = Nothing

   cn.Close
   Set cn = Nothing


Peter Larsson
Helsingborg, Sweden


So why does it work with ADO.net?

MeTitus
Go to Top of Page

jsmith8858
Dr. Cross Join

7423 Posts
Posted - 2006-09-07 : 11:01:30
quote:
Originally posted by MeTitus

I don't agree when someone says that is it a much more wise choice to use stored procedures rather then having it all coded. Imagine that for eg the company I am working for decides to switch its database to another vendor. What happens to all the stored procedures, views and so one??? We have to change them all, at least when doing the way I do, I wont have this sort of problems.

MeTitus


If you use stored procedures and switch vendors (or if your schema changes), you rewrite your *database* code and your app isn't touched.

If you don't use stored procedures and switch vendors (or change your schema), you have to rewrite your *application* code ... and re-release, re-compile, etc.

Which makes more sense to you when the *database* changes: Changing database code or application code?

It is also much, much easier to troubleshoot stored procedures. You can test them individually w/o your app. YOu can tweak them for performance or fix bugs without changing your app. You don't have to worry about delimiters or spacing or if your string variables have errors in them which is undetectable at runtime until the proc is actually executed. You get feedback when you create procs about missing columns or syntax errors and so on. Using parameters avoids sql injection, conversion issues, delimiter issues, and otehr problems with concatenating and executing dynamic SQL strings. You also can grant permissions to only the stored procs for your app and limit access to the direct tables, further increasing security and abstracting your true schema from your code which allows you to change it without touching a single line of your app code.


- Jeff
Go to Top of Page

MeTitus
Starting Member

7 Posts
Posted - 2006-09-08 : 07:02:33
Hi Jeff,

First of all, thanks for your reply. Secondly and after reading your reply I have to say that all the thigs you said make sense, and above all, are very correct. I will take some of your advices in consideration for my future projects

MeTitus
Go to Top of Page
   

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site