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.
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> |
|
|
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 LarssonHelsingborg, Sweden |
|
|
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 LarssonHelsingborg, 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> |
|
|
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.BeginTransactionoRs.Open strSQL, connstrSQL = "SELECT MAX(idActionsHistory_PK) UsedIndex"strSQL = strSQL + "FROM SIE_ActionsHistory"oRs.Open strSQL, connmanageDatabase = oRs("UsedIndex").ValueoRs.CloseSet oRs = Nothingif conn.errors.count > 0 conn.rollbacktransactionelse conn.committransactionPeter LarssonHelsingborg, Sweden |
|
|
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 |
|
|
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 |
|
|
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 LarssonHelsingborg, Sweden |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2006-08-21 : 10:28:22
|
And the stored procedure like thisCREATE PROCEDURE uspDimensionSave( @DimensionID INT OUT, @DimensionName VARCHAR(32), @AcceptRules BIT, @HasOwners BIT, @HasOtherNames BIT)ASSET NOCOUNT ONINSERT INTO Dimensions ( DimensionName, AcceptRules, HasOwners, HasOtherNames )VALUES ( @DimensionName, @AcceptRules, @HasOwners, @HasOtherNames )SELECT @DimensionID = SCOPE_IDENTITY() Peter LarssonHelsingborg, Sweden |
|
|
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 PesoMadhivananFailing to plan is Planning to fail |
|
|
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 |
|
|
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 LarssonHelsingborg, Sweden
So why does it work with ADO.net?MeTitus |
|
|
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 |
|
|
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 |
|
|
|
|
|
|
|