SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 SQL Server Development (2000)
 Begin Tran -- Return recordset
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

MeTitus
Starting Member

7 Posts

Posted - 08/21/2006 :  08:24:44  Show Profile  Reply with Quote
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 - 08/21/2006 :  08:26:59  Show Profile  Reply with Quote
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

Sweden
30281 Posts

Posted - 08/21/2006 :  08:34:23  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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 - 08/21/2006 :  09:49:03  Show Profile  Reply with Quote
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

Sweden
30281 Posts

Posted - 08/21/2006 :  09:54:23  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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 - 08/21/2006 :  10:12:19  Show Profile  Reply with Quote
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

Edited by - MeTitus on 08/21/2006 10:14:26
Go to Top of Page

jsmith8858
Dr. Cross Join

USA
7423 Posts

Posted - 08/21/2006 :  10:25:00  Show Profile  Visit jsmith8858's Homepage  Reply with Quote
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

Sweden
30281 Posts

Posted - 08/21/2006 :  10:27:08  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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

Sweden
30281 Posts

Posted - 08/21/2006 :  10:28:22  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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

India
22772 Posts

Posted - 08/21/2006 :  11:19:12  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
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 - 09/07/2006 :  10:14:56  Show Profile  Reply with Quote
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

Edited by - MeTitus on 09/07/2006 10:15:36
Go to Top of Page

MeTitus
Starting Member

7 Posts

Posted - 09/07/2006 :  10:17:34  Show Profile  Reply with Quote
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

USA
7423 Posts

Posted - 09/07/2006 :  11:01:30  Show Profile  Visit jsmith8858's Homepage  Reply with Quote
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

Edited by - jsmith8858 on 09/07/2006 11:03:30
Go to Top of Page

MeTitus
Starting Member

7 Posts

Posted - 09/08/2006 :  07:02:33  Show Profile  Reply with Quote
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
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000