Showing posts with label record. Show all posts
Showing posts with label record. Show all posts

Monday, February 13, 2012

[*-)] About Auto Increment Field

Hi,

I have one Auto increment field in the table.

I have a problem that suppose I have 10 Records in the table, Now If someone delete record no 3 and 5 then it has 8 records.

But The field will give 11 no to new record I want it first fill 3 and 5 then give 11 to the new record.

Is it possible with the auto increment field in sql server 2005.

No it's not possible with autoincrement fields. You would have to create your own primary key field, and query the table to get the lowest available number everytime you performed an insert. I would never recommend doing this. It's a potential nightmare, and I can't think of one single good reason for doing it. All you need is a unique identifier for each row. It doesn't matter if it's not nice and pretty (in terms of a sequential series), so long as it works.

Saturday, February 11, 2012

@@identity not working using SQL Express 2005 Sept CTP

I have just inherited some code from another company which uses SQL Server.
The logic is dependent on @.@.IDENTITY to retrieve the record just inserted -
so that more info can be added to the record. kind of a silly implementation
- but it uses a bunch of generic code to insert a new row and generate a
GUID, etc.. and it would be significant work to rewrite the entire
application.
The problem is the even though the table has an id column that is defined
with IDENTITY - and through queries I can easily see that each row added has
the proper values for the id column - @.@.IDENTITY inside the .asp vb scripting
app is returning NULL... I am using SQL Server Express 2005 Sept CTP set up
using a system DSN with the SQL Native Client (2005) driver.
here is the code:
Function NewRow(con,tab,col,val)
set nrconn = Server.CreateObject("ADODB.Connection")
nrconn.open SiteConnectionString
cmd = "INSERT INTO "&tab&" ("&col&") VALUES ("&val&") "
Set rs = nrconn.Execute(cmd)
cmd = "select @.@.IDENTITY as 'newid' from "&tab
Set rs = nrconn.Execute(cmd)
set NewRow = rs
End Function
After returning the code uses NewRow("newid") to fetch the record and update
it. of course, it returns NULL and no record is retrieved... Any help
would be much appreciated.
-Steve-o
Hi
You are submitting it as 2 batches, so it is not available to the 2nd one.
Any reason why you are not using stored procedures to do this? Dynamic SQL
is asking for security problems.
SCOPE_IDENTITY is the better way to retrieve is as a trigger will change the
@.@.identity's value if it does another insert.
cmd = "INSERT INTO "&tab&" ("&col&") VALUES ("&val&") "select
SCOPE_IDENTITY as 'newid' from "&tab "
Set rs = nrconn.Execute(cmd)
Regards
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"steve-o" <steveo@.discussions.microsoft.com> wrote in message
news:FE2815A4-B7CC-4D44-B14C-D4CDCD4A198C@.microsoft.com...
>I have just inherited some code from another company which uses SQL Server.
> The logic is dependent on @.@.IDENTITY to retrieve the record just
> inserted -
> so that more info can be added to the record. kind of a silly
> implementation
> - but it uses a bunch of generic code to insert a new row and generate a
> GUID, etc.. and it would be significant work to rewrite the entire
> application.
> The problem is the even though the table has an id column that is defined
> with IDENTITY - and through queries I can easily see that each row added
> has
> the proper values for the id column - @.@.IDENTITY inside the .asp vb
> scripting
> app is returning NULL... I am using SQL Server Express 2005 Sept CTP set
> up
> using a system DSN with the SQL Native Client (2005) driver.
> here is the code:
> Function NewRow(con,tab,col,val)
> set nrconn = Server.CreateObject("ADODB.Connection")
> nrconn.open SiteConnectionString
> cmd = "INSERT INTO "&tab&" ("&col&") VALUES ("&val&") "
> Set rs = nrconn.Execute(cmd)
> cmd = "select @.@.IDENTITY as 'newid' from "&tab
> Set rs = nrconn.Execute(cmd)
> set NewRow = rs
> End Function
> After returning the code uses NewRow("newid") to fetch the record and
> update
> it. of course, it returns NULL and no record is retrieved... Any help
> would be much appreciated.
> --
> -Steve-o
|||Hi
Strange ... the code works on older SQL server version. Nonetheless I
changed as you suggested and same result. I did a little more digging and
the execute line is return rs as type of 'Fields' instead of RecordSet AND
the Count of the Fields is 0... i.e. the execute is returning NOTHING ...
help !
-Steve-o
"Mike Epprecht (SQL MVP)" wrote:

> Hi
> You are submitting it as 2 batches, so it is not available to the 2nd one.
> Any reason why you are not using stored procedures to do this? Dynamic SQL
> is asking for security problems.
> SCOPE_IDENTITY is the better way to retrieve is as a trigger will change the
> @.@.identity's value if it does another insert.
> cmd = "INSERT INTO "&tab&" ("&col&") VALUES ("&val&") "select
> SCOPE_IDENTITY as 'newid' from "&tab "
> Set rs = nrconn.Execute(cmd)
> Regards
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> IM: mike@.epprecht.net
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
> "steve-o" <steveo@.discussions.microsoft.com> wrote in message
> news:FE2815A4-B7CC-4D44-B14C-D4CDCD4A198C@.microsoft.com...
>
>
|||solved the "Fields" problem - I had erroneously removed the "set" fro the rs
= stmt (WHOOPS !). So now the type is returned as Recordset, however,
rs.Fields.Count is 0 ... so still nothing is being returned...
-Steve-o
"steve-o" wrote:
[vbcol=seagreen]
> Hi
> Strange ... the code works on older SQL server version. Nonetheless I
> changed as you suggested and same result. I did a little more digging and
> the execute line is return rs as type of 'Fields' instead of RecordSet AND
> the Count of the Fields is 0... i.e. the execute is returning NOTHING ...
> help !
> --
> -Steve-o
>
> "Mike Epprecht (SQL MVP)" wrote:
|||more info ...
If I follow the original sequence of insert followed by select query I do
get a recordset back with a field count of 1. the name of the item is indeed
'newid' but the value is NULL.
strangely enough when using SQLCMD and doing this sequence of operations by
hand - SQLCMD outputs one line for each record in the table with the newid
field value for each being the "latest" value (i.e. value of 23 if the last
inserted record had and identity value of 23). so it seems like it is
working there, but not in the scripted app...
-Steve-o
"steve-o" wrote:
[vbcol=seagreen]
> solved the "Fields" problem - I had erroneously removed the "set" fro the rs
> = stmt (WHOOPS !). So now the type is returned as Recordset, however,
> rs.Fields.Count is 0 ... so still nothing is being returned...
> --
> -Steve-o
>
> "steve-o" wrote:
|||Try adding SET NOCOUNT ON before the INSERT statement.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"steve-o" <steveo@.discussions.microsoft.com> wrote in message
news:9F100702-8F1B-4A2B-807E-14E72416B956@.microsoft.com...[vbcol=seagreen]
> more info ...
> If I follow the original sequence of insert followed by select query I do
> get a recordset back with a field count of 1. the name of the item is indeed
> 'newid' but the value is NULL.
> strangely enough when using SQLCMD and doing this sequence of operations by
> hand - SQLCMD outputs one line for each record in the table with the newid
> field value for each being the "latest" value (i.e. value of 23 if the last
> inserted record had and identity value of 23). so it seems like it is
> working there, but not in the scripted app...
>
> --
> -Steve-o
>
> "steve-o" wrote:
|||YAY !!! Mike gave me the first half of the solution and Tibor the second
half. Thanks !!!
-Steve-o
"Tibor Karaszi" wrote:

> Try adding SET NOCOUNT ON before the INSERT statement.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "steve-o" <steveo@.discussions.microsoft.com> wrote in message
> news:9F100702-8F1B-4A2B-807E-14E72416B956@.microsoft.com...
>

Thursday, February 9, 2012

@@Rowcount or output parameters error

I have the following stored procedure working with an Access 2000 front end. The output parameters returned to Access are both Null when the record is successfully updated (ie when @.@.Rowcount = 1), but the correct parameters are returned when the update fails. I'm a bit new to using output parameters, but I have them working perfectly with an insert sproc, and they look basically the same. What bonehead error have I made here? The fact that the record is updated indicates to me that the Commit Trans line is being executed, so why aren't the 2 output parameters set?

TIA

EDIT: Solved, sort of. I found that dropping the "@.ResNum +" from "@.ResNum + ' Updated'" resolved the problem (@.ResNum is an input parameter). This implies that the variable lost its value between the SQL statement and the If/Then, since the SQL correctly updates only the appropriate record from the WHERE clause. Is this supposed to happen? I looked in BOL, and if it's addressed there I missed it.

CREATE PROCEDURE [procResUpdate]

Various input parameters here,

@.RetCode as int Output, @.RetResNum as nvarchar(15) Output

AS

Declare @.RowCounter int

Begin Tran

UPDATE tblReservations
SET Various set statements here, LastModified = @.LastModified + 1
WHERE ResNum = @.ResNum AND LastModified = @.LastModified

SELECT @.RowCounter = @.@.ROWCOUNT

If @.RowCounter = 1
Begin
Commit Tran
Select @.RetCode = 1
Select @.RetResNum = @.ResNum + ' Updated'
End
Else
Begin
Rollback Tran
Select @.RetCode = 0
Select @.RetResNum = 'Update Failed'
End
GOPost the complete DDL for the proc (including @.ResNum) and a sample of calling it that produces the problem you describe.

Regards,

hmscott|||Here's the complete sproc (I added back the offending part in red):

CREATE PROCEDURE [procResUpdate]

@.ReqDate as datetime, @.PassName as nvarchar(25), @.DispDate as datetime, @.PassPhone as nvarchar(25),
@.PassQuant as smallint, @.AuthBy as nvarchar(25), @.AcctID as smallint, @.PuLandmark as smallint, @.PuStreet as nvarchar(50),
@.PuCity as smallint, @.PuXStreet as nvarchar(50), @.PuPoint as nvarchar(50), @.DestLandmark as smallint, @.DestStreet as nvarchar(50),
@.DestCity as smallint, @.DestXStreet as nvarchar(50), @.DestPoint as nvarchar(50), @.Operator as smallint, @.Comments as nvarchar(250),
@.DriverReq as nvarchar(25), @.CarType as smallint, @.EstHours as money, @.EstPrice as money, @.CCType as nvarchar(25), @.CCnum as nvarchar(25),
@.CCAuthNum as nvarchar(25), @.CarQuant as tinyint, @.ResNum as int, @.Status as smallint, @.LastModified as tinyint,

@.RetCode as int = Null Output, @.RetResNum as nvarchar(15) = Null Output

AS

Declare @.RowCounter int

Begin Tran

UPDATE tblReservations
SET ReqDateTime = @.ReqDate, PassName = @.PassName, DispDateTime = @.DispDate, PassPhone = @.PassPhone, PassQuant = @.PassQuant, AuthBy = @.AuthBy, AcctID = @.AcctID,
PuLandmarkID = @.PuLandmark, PuStreet = @.PuStreet, PuCityID = @.PuCity, PuXStreet = @.PuXStreet, PuPoint = @.PuPoint, DestLandmarkID = @.DestLandmark, DestStreet = @.DestStreet,
DestCityID = @.DestCity, DestXStreet = @.DestXStreet, DestPoint = @.DestPoint, OperatorID = @.Operator, Comments = @.Comments, DriverReq = @.DriverReq, CarType = @.CarType,
EstHours = @.EstHours, EstPrice = @.EstPrice, CCType = @.CCType, CCNum = @.CCnum, CCAuthNum = @.CCAuthNum, CarQuant = @.CarQuant, Status = @.Status, LastModified = @.LastModified + 1
WHERE ResNum = @.ResNum AND LastModified = @.LastModified

SELECT @.RowCounter = @.@.ROWCOUNT

If @.RowCounter = 1
Begin
Commit Tran
Select @.RetCode = 1
Select @.RetResNum = ' Updated'
End
Else
Begin
Rollback Tran
Select @.RetCode = 0
Select @.RetResNum = @.ResNum + ' Update Failed'
End
GO


and the VBA that calls it. The 2 highlighted lines will be null if the "@.ResNum + " is included. Otherwise, it runs fine.

Public Sub cmdUpdate_Click()
Dim strSQL As String
Dim strField As String
Dim fOK As Integer
Dim intCount As Integer
Dim x As Integer
Dim strMsg As String
Dim cmd As ADODB.Command
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim tdf As DAO.TableDef
Dim dteReqDate As Date
Dim dteDispDate As Date

On Error GoTo ErrorHandler

dteReqDate = Me.txtReqDate & " " & Me.txtReqTime
dteDispDate = DateAdd("n", -Nz(Me.txtMinutesAhead, 0), dteReqDate)

Call EstablishConnection
Set db = CurrentDb()
Set cmd = New ADODB.Command
Set rst = New ADODB.Recordset
Set tdf = db.TableDefs("tblReservations_Local")
rst.ActiveConnection = objConn

With cmd
.ActiveConnection = objConn
.CommandText = "procResUpdate"
.CommandType = adCmdStoredProc

.Parameters.Append .CreateParameter("@.ReqDate", adDBTimeStamp, adParamInput, , dteReqDate)
.Parameters.Append .CreateParameter("@.PassName", adVarChar, adParamInput, 25, Me.txtPassName)
.Parameters.Append .CreateParameter("@.DispDate", adDBTimeStamp, adParamInput, , dteDispDate)
.Parameters.Append .CreateParameter("@.PassPhone", adVarChar, adParamInput, 25, Me.txtPassPhone)
.Parameters.Append .CreateParameter("@.PassQuant", adInteger, adParamInput, , Me.txtPassQuant)
.Parameters.Append .CreateParameter("@.AuthBy", adVarChar, adParamInput, 25, Me.txtAuthBy)
.Parameters.Append .CreateParameter("@.AcctID", adInteger, adParamInput, , Me.cboAcctNum)
.Parameters.Append .CreateParameter("@.PuLandmark", adInteger, adParamInput, , Me.cboLandmark)
.Parameters.Append .CreateParameter("@.PuStreet", adVarChar, adParamInput, 50, Me.txtPuAddress)
.Parameters.Append .CreateParameter("@.PuCity", adInteger, adParamInput, , Me.cboPuCity)
.Parameters.Append .CreateParameter("@.PuXStreet", adVarChar, adParamInput, 50, Me.txtPuXStreet)
.Parameters.Append .CreateParameter("@.PuPoint", adVarChar, adParamInput, 50, Me.txtPuPoint)
.Parameters.Append .CreateParameter("@.DestLandmark", adInteger, adParamInput, , Me.cboDestLandmark)
.Parameters.Append .CreateParameter("@.DestStreet", adVarChar, adParamInput, 50, Me.txtDestAddress)
.Parameters.Append .CreateParameter("@.DestCity", adInteger, adParamInput, , Me.cboDestCity)
.Parameters.Append .CreateParameter("@.DestXStreet", adVarChar, adParamInput, 50, Me.txtDestXStreet)
.Parameters.Append .CreateParameter("@.DestPoint", adVarChar, adParamInput, 50, Me.txtDestPoint)
.Parameters.Append .CreateParameter("@.Operator", adInteger, adParamInput, , Me.txtOperator)
.Parameters.Append .CreateParameter("@.Comments", adVarChar, adParamInput, 250, Me.txtComments)
.Parameters.Append .CreateParameter("@.DriverReq", adVarChar, adParamInput, 25, Me.txtDriverReq)
.Parameters.Append .CreateParameter("@.CarType", adInteger, adParamInput, , Me.cboCarType)
.Parameters.Append .CreateParameter("@.EstHours", adCurrency, adParamInput, , Me.txtEstHours)
.Parameters.Append .CreateParameter("@.EstPrice", adCurrency, adParamInput, , Me.txtEstPrice)
.Parameters.Append .CreateParameter("@.CCType", adVarChar, adParamInput, 25, Me.cboCCType)
.Parameters.Append .CreateParameter("@.CCnum", adVarChar, adParamInput, 25, Me.txtCCNum)
.Parameters.Append .CreateParameter("@.CCAuthNum", adVarChar, adParamInput, 25, Me.txtCCAuthNum)
.Parameters.Append .CreateParameter("@.CarQuant", adInteger, adParamInput, , Me.txtCarQuant)
.Parameters.Append .CreateParameter("@.ResNum", adInteger, adParamInput, , Me.txtResNum)
.Parameters.Append .CreateParameter("@.Status", adInteger, adParamInput, , Me.cboStatus)
.Parameters.Append .CreateParameter("@.LastModified", adInteger, adParamInput, , Me.txtLastModified)

.Parameters.Append .CreateParameter("@.RetCode", adInteger, adParamOutput)
.Parameters.Append .CreateParameter("@.RetResNum", adVarChar, adParamOutput, 15)

.Execute

End With

fOK = cmd.Parameters("@.RetCode")
strMsg = cmd.Parameters("@.RetResNum")|||Update for anyone interested. It seems that there was a type mismatch occurring. @.ResNum is a numeric value (data type int), and apparently in this line that caused a problem:

Select @.RetResNum = @.ResNum + ' Update Failed'

I assumed it would concatenate the two values. If I create a new varchar variable and populate it with the value of @.ResNum (converted to varchar) and use that, everything works fine. What threw me was that not only was that return parameter null, but the other one was too.

@@RowCount

I want to run one of two SELECT statements, one with a Join the other
without, depending on wheteher the Join fails because of an unrelated record
.
The second statement should always return one row as the @.RequestID value
being passed comes from a Tree selection. But no row is returned. Where am I
going wrong? TIA
I am using:
CREATE PROCEDURE tdGetRequestor
@.RequestID BigInt
AS
SELECT tblRequests.RequestID, tblRequests.RequestedBy,
tblRequests.RequestDate, tblRequests.RequestorSectionID,
tblSections.SectionName AS RequestorSection
FROM tblRequests
INNER JOIN tblSections ON tblRequests.RequestorSectionID =
tblSections.SectionID
WHERE tblRequests.RequestID = @.RequestID
IF @.@.ROWCOUNT=0
BEGIN
SELECT RequestID, RequestedBy, RequestDate,RequestorSectionID
FROM tblRequests
WHERE tblRequests.RequestID = @.RequestID
END
GOHave you tried the second query in outside of the SP by providing @.RequestID
manually?
"td" <td@.discussions.microsoft.com> wrote in message
news:6DC4AB4F-DEF2-478E-B873-30422C1207AF@.microsoft.com...
>I want to run one of two SELECT statements, one with a Join the other
> without, depending on wheteher the Join fails because of an unrelated
> record.
> The second statement should always return one row as the @.RequestID value
> being passed comes from a Tree selection. But no row is returned. Where am
> I
> going wrong? TIA
> I am using:
> CREATE PROCEDURE tdGetRequestor
> @.RequestID BigInt
> AS
> SELECT tblRequests.RequestID, tblRequests.RequestedBy,
> tblRequests.RequestDate, tblRequests.RequestorSectionID,
> tblSections.SectionName AS RequestorSection
> FROM tblRequests
> INNER JOIN tblSections ON tblRequests.RequestorSectionID =
> tblSections.SectionID
> WHERE tblRequests.RequestID = @.RequestID
> IF @.@.ROWCOUNT=0
> BEGIN
> SELECT RequestID, RequestedBy, RequestDate,RequestorSectionID
> FROM tblRequests
> WHERE tblRequests.RequestID = @.RequestID
> END
> GO|||I'm not sure there is an use for RowCount here. Is there a reason why you
can't use an outer join? This should return the same results, with NULL for
tblSections.SectionName if a related record does not exist.
CREATE PROCEDURE tdGetRequestor
@.RequestID BigInt
AS
SELECT
tblRequests.RequestID
, tblRequests.RequestedBy
, tblRequests.RequestDate
, tblRequests.RequestorSectionID
, tblSections.SectionName AS RequestorSection
FROM tblRequests
LEFT OUTER JOIN tblSections
ON tblRequests.RequestorSectionID = tblSections.SectionID
WHERE tblRequests.RequestID = @.RequestID
GO
"td" <td@.discussions.microsoft.com> wrote in message
news:6DC4AB4F-DEF2-478E-B873-30422C1207AF@.microsoft.com...
> I want to run one of two SELECT statements, one with a Join the other
> without, depending on wheteher the Join fails because of an unrelated
record.
> The second statement should always return one row as the @.RequestID value
> being passed comes from a Tree selection. But no row is returned. Where am
I
> going wrong? TIA
> I am using:
> CREATE PROCEDURE tdGetRequestor
> @.RequestID BigInt
> AS
> SELECT tblRequests.RequestID, tblRequests.RequestedBy,
> tblRequests.RequestDate, tblRequests.RequestorSectionID,
> tblSections.SectionName AS RequestorSection
> FROM tblRequests
> INNER JOIN tblSections ON tblRequests.RequestorSectionID =
> tblSections.SectionID
> WHERE tblRequests.RequestID = @.RequestID
> IF @.@.ROWCOUNT=0
> BEGIN
> SELECT RequestID, RequestedBy, RequestDate,RequestorSectionID
> FROM tblRequests
> WHERE tblRequests.RequestID = @.RequestID
> END
> GO|||While I agree with Jim that you proably could use an outer join for this
(almost assuredly) have you tried to run this in Query Analyzer? See what
happens there. If you get two result sets, then it is probably something in
how you are trying to deal with 2 resultsets (which would be a question for
someone in the newsgroup that corresponds to your language (programming
language, of course :))
----
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP
"Arguments are to be avoided: they are always vulgar and often convincing."
(Oscar Wilde)
"td" <td@.discussions.microsoft.com> wrote in message
news:6DC4AB4F-DEF2-478E-B873-30422C1207AF@.microsoft.com...
>I want to run one of two SELECT statements, one with a Join the other
> without, depending on wheteher the Join fails because of an unrelated
> record.
> The second statement should always return one row as the @.RequestID value
> being passed comes from a Tree selection. But no row is returned. Where am
> I
> going wrong? TIA
> I am using:
> CREATE PROCEDURE tdGetRequestor
> @.RequestID BigInt
> AS
> SELECT tblRequests.RequestID, tblRequests.RequestedBy,
> tblRequests.RequestDate, tblRequests.RequestorSectionID,
> tblSections.SectionName AS RequestorSection
> FROM tblRequests
> INNER JOIN tblSections ON tblRequests.RequestorSectionID =
> tblSections.SectionID
> WHERE tblRequests.RequestID = @.RequestID
> IF @.@.ROWCOUNT=0
> BEGIN
> SELECT RequestID, RequestedBy, RequestDate,RequestorSectionID
> FROM tblRequests
> WHERE tblRequests.RequestID = @.RequestID
> END
> GO|||Save @.@.rowcount in an integer variable immediately after the Transact-SQL
statement completes. The value of the variable can be used later.
declare @.rowcount int
set @.rowcount = @.@.rowcount
HTH
Rajesh Peddireddy
"td" wrote:

> I want to run one of two SELECT statements, one with a Join the other
> without, depending on wheteher the Join fails because of an unrelated reco
rd.
> The second statement should always return one row as the @.RequestID value
> being passed comes from a Tree selection. But no row is returned. Where am
I
> going wrong? TIA
> I am using:
> CREATE PROCEDURE tdGetRequestor
> @.RequestID BigInt
> AS
> SELECT tblRequests.RequestID, tblRequests.RequestedBy,
> tblRequests.RequestDate, tblRequests.RequestorSectionID,
> tblSections.SectionName AS RequestorSection
> FROM tblRequests
> INNER JOIN tblSections ON tblRequests.RequestorSectionID =
> tblSections.SectionID
> WHERE tblRequests.RequestID = @.RequestID
> IF @.@.ROWCOUNT=0
> BEGIN
> SELECT RequestID, RequestedBy, RequestDate,RequestorSectionID
> FROM tblRequests
> WHERE tblRequests.RequestID = @.RequestID
> END
> GO|||Good thinking! I'm glad I thought of that (=: (or should have thought of tha
t)
"Jim Underwood" wrote:

> I'm not sure there is an use for RowCount here. Is there a reason why you
> can't use an outer join? This should return the same results, with NULL f
or
> tblSections.SectionName if a related record does not exist.
> CREATE PROCEDURE tdGetRequestor
> @.RequestID BigInt
> AS
> SELECT
> tblRequests.RequestID
> , tblRequests.RequestedBy
> , tblRequests.RequestDate
> , tblRequests.RequestorSectionID
> , tblSections.SectionName AS RequestorSection
> FROM tblRequests
> LEFT OUTER JOIN tblSections
> ON tblRequests.RequestorSectionID = tblSections.SectionID
> WHERE tblRequests.RequestID = @.RequestID
> GO
> "td" <td@.discussions.microsoft.com> wrote in message
> news:6DC4AB4F-DEF2-478E-B873-30422C1207AF@.microsoft.com...
> record.
> I
>
>|||Yes. It works fine. As I mentioned, the second statement will always return
a
row.
"Leila" wrote:

> Have you tried the second query in outside of the SP by providing @.Request
ID
> manually?
>
> "td" <td@.discussions.microsoft.com> wrote in message
> news:6DC4AB4F-DEF2-478E-B873-30422C1207AF@.microsoft.com...
>
>|||Hello Louis, Thanks for your input.
I did take Jim's advise & it works fine.
On your suggestion I have run the query in Query Analyser and it does run
correctly. In VB .Net the Reader is reporting that .HasRows is false.
Thanks to all for your suggestions
What gets returned by SQL Server in a situation such as this? It does seem
that it is reporting the zero rows back from the first query and, perhaps,
returning the result of the second as well?
In Query Analyser two distinct results ARE displayed. I will write some more
code and check what is happening in VB .Net.
td
"Louis Davidson" wrote:

> While I agree with Jim that you proably could use an outer join for this
> (almost assuredly) have you tried to run this in Query Analyzer? See what
> happens there. If you get two result sets, then it is probably something
in
> how you are trying to deal with 2 resultsets (which would be a question fo
r
> someone in the newsgroup that corresponds to your language (programming
> language, of course :))
> --
> ----
--
> Louis Davidson - http://spaces.msn.com/members/drsql/
> SQL Server MVP
> "Arguments are to be avoided: they are always vulgar and often convincing.
"
> (Oscar Wilde)
> "td" <td@.discussions.microsoft.com> wrote in message
> news:6DC4AB4F-DEF2-478E-B873-30422C1207AF@.microsoft.com...
>
>

@@identity not working using SQL Express 2005 Sept CTP

I have just inherited some code from another company which uses SQL Server.
The logic is dependent on @.@.IDENTITY to retrieve the record just inserted -
so that more info can be added to the record. kind of a silly implementatio
n
- but it uses a bunch of generic code to insert a new row and generate a
GUID, etc.. and it would be significant work to rewrite the entire
application.
The problem is the even though the table has an id column that is defined
with IDENTITY - and through queries I can easily see that each row added has
the proper values for the id column - @.@.IDENTITY inside the .asp vb scriptin
g
app is returning NULL... I am using SQL Server Express 2005 Sept CTP set up
using a system DSN with the SQL Native Client (2005) driver.
here is the code:
Function NewRow(con,tab,col,val)
set nrconn = Server.CreateObject("ADODB.Connection")
nrconn.open SiteConnectionString
cmd = "INSERT INTO "&tab&" ("&col&") VALUES ("&val&") "
Set rs = nrconn.Execute(cmd)
cmd = "select @.@.IDENTITY as 'newid' from "&tab
Set rs = nrconn.Execute(cmd)
set NewRow = rs
End Function
After returning the code uses NewRow("newid") to fetch the record and update
it. of course, it returns NULL and no record is retrieved... Any help
would be much appreciated.
-Steve-oHi
You are submitting it as 2 batches, so it is not available to the 2nd one.
Any reason why you are not using stored procedures to do this? Dynamic SQL
is asking for security problems.
SCOPE_IDENTITY is the better way to retrieve is as a trigger will change the
@.@.identity's value if it does another insert.
cmd = "INSERT INTO "&tab&" ("&col&") VALUES ("&val&") "select
SCOPE_IDENTITY as 'newid' from "&tab "
Set rs = nrconn.Execute(cmd)
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"steve-o" <steveo@.discussions.microsoft.com> wrote in message
news:FE2815A4-B7CC-4D44-B14C-D4CDCD4A198C@.microsoft.com...
>I have just inherited some code from another company which uses SQL Server.
> The logic is dependent on @.@.IDENTITY to retrieve the record just
> inserted -
> so that more info can be added to the record. kind of a silly
> implementation
> - but it uses a bunch of generic code to insert a new row and generate a
> GUID, etc.. and it would be significant work to rewrite the entire
> application.
> The problem is the even though the table has an id column that is defined
> with IDENTITY - and through queries I can easily see that each row added
> has
> the proper values for the id column - @.@.IDENTITY inside the .asp vb
> scripting
> app is returning NULL... I am using SQL Server Express 2005 Sept CTP set
> up
> using a system DSN with the SQL Native Client (2005) driver.
> here is the code:
> Function NewRow(con,tab,col,val)
> set nrconn = Server.CreateObject("ADODB.Connection")
> nrconn.open SiteConnectionString
> cmd = "INSERT INTO "&tab&" ("&col&") VALUES ("&val&") "
> Set rs = nrconn.Execute(cmd)
> cmd = "select @.@.IDENTITY as 'newid' from "&tab
> Set rs = nrconn.Execute(cmd)
> set NewRow = rs
> End Function
> After returning the code uses NewRow("newid") to fetch the record and
> update
> it. of course, it returns NULL and no record is retrieved... Any help
> would be much appreciated.
> --
> -Steve-o|||Hi
Strange ... the code works on older SQL server version. Nonetheless I
changed as you suggested and same result. I did a little more digging and
the execute line is return rs as type of 'Fields' instead of RecordSet AND
the Count of the Fields is 0... i.e. the execute is returning NOTHING ...
help !
-Steve-o
"Mike Epprecht (SQL MVP)" wrote:

> Hi
> You are submitting it as 2 batches, so it is not available to the 2nd one.
> Any reason why you are not using stored procedures to do this? Dynamic SQL
> is asking for security problems.
> SCOPE_IDENTITY is the better way to retrieve is as a trigger will change t
he
> @.@.identity's value if it does another insert.
> cmd = "INSERT INTO "&tab&" ("&col&") VALUES ("&val&") "select
> SCOPE_IDENTITY as 'newid' from "&tab "
> Set rs = nrconn.Execute(cmd)
> Regards
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> IM: mike@.epprecht.net
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
> "steve-o" <steveo@.discussions.microsoft.com> wrote in message
> news:FE2815A4-B7CC-4D44-B14C-D4CDCD4A198C@.microsoft.com...
>
>|||solved the "Fields" problem - I had erroneously removed the "set" fro the rs
= stmt (WHOOPS !). So now the type is returned as Recordset, however,
rs.Fields.Count is 0 ... so still nothing is being returned...
--
-Steve-o
"steve-o" wrote:
[vbcol=seagreen]
> Hi
> Strange ... the code works on older SQL server version. Nonetheless I
> changed as you suggested and same result. I did a little more digging and
> the execute line is return rs as type of 'Fields' instead of RecordSet AND
> the Count of the Fields is 0... i.e. the execute is returning NOTHING ...
> help !
> --
> -Steve-o
>
> "Mike Epprecht (SQL MVP)" wrote:
>|||more info ...
If I follow the original sequence of insert followed by select query I do
get a recordset back with a field count of 1. the name of the item is indee
d
'newid' but the value is NULL.
strangely enough when using SQLCMD and doing this sequence of operations by
hand - SQLCMD outputs one line for each record in the table with the newid
field value for each being the "latest" value (i.e. value of 23 if the last
inserted record had and identity value of 23). so it seems like it is
working there, but not in the scripted app...
-Steve-o
"steve-o" wrote:
[vbcol=seagreen]
> solved the "Fields" problem - I had erroneously removed the "set" fro the
rs
> = stmt (WHOOPS !). So now the type is returned as Recordset, however,
> rs.Fields.Count is 0 ... so still nothing is being returned...
> --
> -Steve-o
>
> "steve-o" wrote:
>|||Try adding SET NOCOUNT ON before the INSERT statement.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"steve-o" <steveo@.discussions.microsoft.com> wrote in message
news:9F100702-8F1B-4A2B-807E-14E72416B956@.microsoft.com...[vbcol=seagreen]
> more info ...
> If I follow the original sequence of insert followed by select query I do
> get a recordset back with a field count of 1. the name of the item is ind
eed
> 'newid' but the value is NULL.
> strangely enough when using SQLCMD and doing this sequence of operations b
y
> hand - SQLCMD outputs one line for each record in the table with the newid
> field value for each being the "latest" value (i.e. value of 23 if the las
t
> inserted record had and identity value of 23). so it seems like it is
> working there, but not in the scripted app...
>
> --
> -Steve-o
>
> "steve-o" wrote:
>|||YAY !!! Mike gave me the first half of the solution and Tibor the second
half. Thanks !!!
--
-Steve-o
"Tibor Karaszi" wrote:

> Try adding SET NOCOUNT ON before the INSERT statement.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "steve-o" <steveo@.discussions.microsoft.com> wrote in message
> news:9F100702-8F1B-4A2B-807E-14E72416B956@.microsoft.com...
>

@@identity not working using SQL Express 2005 Sept CTP

I have just inherited some code from another company which uses SQL Server.
The logic is dependent on @.@.IDENTITY to retrieve the record just inserted -
so that more info can be added to the record. kind of a silly implementation
- but it uses a bunch of generic code to insert a new row and generate a
GUID, etc.. and it would be significant work to rewrite the entire
application.
The problem is the even though the table has an id column that is defined
with IDENTITY - and through queries I can easily see that each row added has
the proper values for the id column - @.@.IDENTITY inside the .asp vb scripting
app is returning NULL... I am using SQL Server Express 2005 Sept CTP set up
using a system DSN with the SQL Native Client (2005) driver.
here is the code:
Function NewRow(con,tab,col,val)
set nrconn = Server.CreateObject("ADODB.Connection")
nrconn.open SiteConnectionString
cmd = "INSERT INTO "&tab&" ("&col&") VALUES ("&val&") "
Set rs = nrconn.Execute(cmd)
cmd = "select @.@.IDENTITY as 'newid' from "&tab
Set rs = nrconn.Execute(cmd)
set NewRow = rs
End Function
After returning the code uses NewRow("newid") to fetch the record and update
it. of course, it returns NULL and no record is retrieved... Any help
would be much appreciated.
--
-Steve-oHi
You are submitting it as 2 batches, so it is not available to the 2nd one.
Any reason why you are not using stored procedures to do this? Dynamic SQL
is asking for security problems.
SCOPE_IDENTITY is the better way to retrieve is as a trigger will change the
@.@.identity's value if it does another insert.
cmd = "INSERT INTO "&tab&" ("&col&") VALUES ("&val&") "select
SCOPE_IDENTITY as 'newid' from "&tab "
Set rs = nrconn.Execute(cmd)
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"steve-o" <steveo@.discussions.microsoft.com> wrote in message
news:FE2815A4-B7CC-4D44-B14C-D4CDCD4A198C@.microsoft.com...
>I have just inherited some code from another company which uses SQL Server.
> The logic is dependent on @.@.IDENTITY to retrieve the record just
> inserted -
> so that more info can be added to the record. kind of a silly
> implementation
> - but it uses a bunch of generic code to insert a new row and generate a
> GUID, etc.. and it would be significant work to rewrite the entire
> application.
> The problem is the even though the table has an id column that is defined
> with IDENTITY - and through queries I can easily see that each row added
> has
> the proper values for the id column - @.@.IDENTITY inside the .asp vb
> scripting
> app is returning NULL... I am using SQL Server Express 2005 Sept CTP set
> up
> using a system DSN with the SQL Native Client (2005) driver.
> here is the code:
> Function NewRow(con,tab,col,val)
> set nrconn = Server.CreateObject("ADODB.Connection")
> nrconn.open SiteConnectionString
> cmd = "INSERT INTO "&tab&" ("&col&") VALUES ("&val&") "
> Set rs = nrconn.Execute(cmd)
> cmd = "select @.@.IDENTITY as 'newid' from "&tab
> Set rs = nrconn.Execute(cmd)
> set NewRow = rs
> End Function
> After returning the code uses NewRow("newid") to fetch the record and
> update
> it. of course, it returns NULL and no record is retrieved... Any help
> would be much appreciated.
> --
> -Steve-o|||Hi
Strange ... the code works on older SQL server version. Nonetheless I
changed as you suggested and same result. I did a little more digging and
the execute line is return rs as type of 'Fields' instead of RecordSet AND
the Count of the Fields is 0... i.e. the execute is returning NOTHING ...
help !
--
-Steve-o
"Mike Epprecht (SQL MVP)" wrote:
> Hi
> You are submitting it as 2 batches, so it is not available to the 2nd one.
> Any reason why you are not using stored procedures to do this? Dynamic SQL
> is asking for security problems.
> SCOPE_IDENTITY is the better way to retrieve is as a trigger will change the
> @.@.identity's value if it does another insert.
> cmd = "INSERT INTO "&tab&" ("&col&") VALUES ("&val&") "select
> SCOPE_IDENTITY as 'newid' from "&tab "
> Set rs = nrconn.Execute(cmd)
> Regards
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> IM: mike@.epprecht.net
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
> "steve-o" <steveo@.discussions.microsoft.com> wrote in message
> news:FE2815A4-B7CC-4D44-B14C-D4CDCD4A198C@.microsoft.com...
> >I have just inherited some code from another company which uses SQL Server.
> > The logic is dependent on @.@.IDENTITY to retrieve the record just
> > inserted -
> > so that more info can be added to the record. kind of a silly
> > implementation
> > - but it uses a bunch of generic code to insert a new row and generate a
> > GUID, etc.. and it would be significant work to rewrite the entire
> > application.
> >
> > The problem is the even though the table has an id column that is defined
> > with IDENTITY - and through queries I can easily see that each row added
> > has
> > the proper values for the id column - @.@.IDENTITY inside the .asp vb
> > scripting
> > app is returning NULL... I am using SQL Server Express 2005 Sept CTP set
> > up
> > using a system DSN with the SQL Native Client (2005) driver.
> >
> > here is the code:
> >
> > Function NewRow(con,tab,col,val)
> > set nrconn = Server.CreateObject("ADODB.Connection")
> > nrconn.open SiteConnectionString
> > cmd = "INSERT INTO "&tab&" ("&col&") VALUES ("&val&") "
> > Set rs = nrconn.Execute(cmd)
> > cmd = "select @.@.IDENTITY as 'newid' from "&tab
> > Set rs = nrconn.Execute(cmd)
> > set NewRow = rs
> >
> > End Function
> >
> > After returning the code uses NewRow("newid") to fetch the record and
> > update
> > it. of course, it returns NULL and no record is retrieved... Any help
> > would be much appreciated.
> >
> > --
> > -Steve-o
>
>|||solved the "Fields" problem - I had erroneously removed the "set" fro the rs
= stmt (WHOOPS !). So now the type is returned as Recordset, however,
rs.Fields.Count is 0 ... so still nothing is being returned...
--
-Steve-o
"steve-o" wrote:
> Hi
> Strange ... the code works on older SQL server version. Nonetheless I
> changed as you suggested and same result. I did a little more digging and
> the execute line is return rs as type of 'Fields' instead of RecordSet AND
> the Count of the Fields is 0... i.e. the execute is returning NOTHING ...
> help !
> --
> -Steve-o
>
> "Mike Epprecht (SQL MVP)" wrote:
> > Hi
> >
> > You are submitting it as 2 batches, so it is not available to the 2nd one.
> >
> > Any reason why you are not using stored procedures to do this? Dynamic SQL
> > is asking for security problems.
> >
> > SCOPE_IDENTITY is the better way to retrieve is as a trigger will change the
> > @.@.identity's value if it does another insert.
> >
> > cmd = "INSERT INTO "&tab&" ("&col&") VALUES ("&val&") "select
> > SCOPE_IDENTITY as 'newid' from "&tab "
> > Set rs = nrconn.Execute(cmd)
> >
> > Regards
> > --
> > Mike Epprecht, Microsoft SQL Server MVP
> > Zurich, Switzerland
> >
> > IM: mike@.epprecht.net
> >
> > MVP Program: http://www.microsoft.com/mvp
> >
> > Blog: http://www.msmvps.com/epprecht/
> >
> > "steve-o" <steveo@.discussions.microsoft.com> wrote in message
> > news:FE2815A4-B7CC-4D44-B14C-D4CDCD4A198C@.microsoft.com...
> > >I have just inherited some code from another company which uses SQL Server.
> > > The logic is dependent on @.@.IDENTITY to retrieve the record just
> > > inserted -
> > > so that more info can be added to the record. kind of a silly
> > > implementation
> > > - but it uses a bunch of generic code to insert a new row and generate a
> > > GUID, etc.. and it would be significant work to rewrite the entire
> > > application.
> > >
> > > The problem is the even though the table has an id column that is defined
> > > with IDENTITY - and through queries I can easily see that each row added
> > > has
> > > the proper values for the id column - @.@.IDENTITY inside the .asp vb
> > > scripting
> > > app is returning NULL... I am using SQL Server Express 2005 Sept CTP set
> > > up
> > > using a system DSN with the SQL Native Client (2005) driver.
> > >
> > > here is the code:
> > >
> > > Function NewRow(con,tab,col,val)
> > > set nrconn = Server.CreateObject("ADODB.Connection")
> > > nrconn.open SiteConnectionString
> > > cmd = "INSERT INTO "&tab&" ("&col&") VALUES ("&val&") "
> > > Set rs = nrconn.Execute(cmd)
> > > cmd = "select @.@.IDENTITY as 'newid' from "&tab
> > > Set rs = nrconn.Execute(cmd)
> > > set NewRow = rs
> > >
> > > End Function
> > >
> > > After returning the code uses NewRow("newid") to fetch the record and
> > > update
> > > it. of course, it returns NULL and no record is retrieved... Any help
> > > would be much appreciated.
> > >
> > > --
> > > -Steve-o
> >
> >
> >|||more info ...
If I follow the original sequence of insert followed by select query I do
get a recordset back with a field count of 1. the name of the item is indeed
'newid' but the value is NULL.
strangely enough when using SQLCMD and doing this sequence of operations by
hand - SQLCMD outputs one line for each record in the table with the newid
field value for each being the "latest" value (i.e. value of 23 if the last
inserted record had and identity value of 23). so it seems like it is
working there, but not in the scripted app...
-Steve-o
"steve-o" wrote:
> solved the "Fields" problem - I had erroneously removed the "set" fro the rs
> = stmt (WHOOPS !). So now the type is returned as Recordset, however,
> rs.Fields.Count is 0 ... so still nothing is being returned...
> --
> -Steve-o
>
> "steve-o" wrote:
> > Hi
> >
> > Strange ... the code works on older SQL server version. Nonetheless I
> > changed as you suggested and same result. I did a little more digging and
> > the execute line is return rs as type of 'Fields' instead of RecordSet AND
> > the Count of the Fields is 0... i.e. the execute is returning NOTHING ...
> > help !
> >
> > --
> > -Steve-o
> >
> >
> > "Mike Epprecht (SQL MVP)" wrote:
> >
> > > Hi
> > >
> > > You are submitting it as 2 batches, so it is not available to the 2nd one.
> > >
> > > Any reason why you are not using stored procedures to do this? Dynamic SQL
> > > is asking for security problems.
> > >
> > > SCOPE_IDENTITY is the better way to retrieve is as a trigger will change the
> > > @.@.identity's value if it does another insert.
> > >
> > > cmd = "INSERT INTO "&tab&" ("&col&") VALUES ("&val&") "select
> > > SCOPE_IDENTITY as 'newid' from "&tab "
> > > Set rs = nrconn.Execute(cmd)
> > >
> > > Regards
> > > --
> > > Mike Epprecht, Microsoft SQL Server MVP
> > > Zurich, Switzerland
> > >
> > > IM: mike@.epprecht.net
> > >
> > > MVP Program: http://www.microsoft.com/mvp
> > >
> > > Blog: http://www.msmvps.com/epprecht/
> > >
> > > "steve-o" <steveo@.discussions.microsoft.com> wrote in message
> > > news:FE2815A4-B7CC-4D44-B14C-D4CDCD4A198C@.microsoft.com...
> > > >I have just inherited some code from another company which uses SQL Server.
> > > > The logic is dependent on @.@.IDENTITY to retrieve the record just
> > > > inserted -
> > > > so that more info can be added to the record. kind of a silly
> > > > implementation
> > > > - but it uses a bunch of generic code to insert a new row and generate a
> > > > GUID, etc.. and it would be significant work to rewrite the entire
> > > > application.
> > > >
> > > > The problem is the even though the table has an id column that is defined
> > > > with IDENTITY - and through queries I can easily see that each row added
> > > > has
> > > > the proper values for the id column - @.@.IDENTITY inside the .asp vb
> > > > scripting
> > > > app is returning NULL... I am using SQL Server Express 2005 Sept CTP set
> > > > up
> > > > using a system DSN with the SQL Native Client (2005) driver.
> > > >
> > > > here is the code:
> > > >
> > > > Function NewRow(con,tab,col,val)
> > > > set nrconn = Server.CreateObject("ADODB.Connection")
> > > > nrconn.open SiteConnectionString
> > > > cmd = "INSERT INTO "&tab&" ("&col&") VALUES ("&val&") "
> > > > Set rs = nrconn.Execute(cmd)
> > > > cmd = "select @.@.IDENTITY as 'newid' from "&tab
> > > > Set rs = nrconn.Execute(cmd)
> > > > set NewRow = rs
> > > >
> > > > End Function
> > > >
> > > > After returning the code uses NewRow("newid") to fetch the record and
> > > > update
> > > > it. of course, it returns NULL and no record is retrieved... Any help
> > > > would be much appreciated.
> > > >
> > > > --
> > > > -Steve-o
> > >
> > >
> > >|||Try adding SET NOCOUNT ON before the INSERT statement.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"steve-o" <steveo@.discussions.microsoft.com> wrote in message
news:9F100702-8F1B-4A2B-807E-14E72416B956@.microsoft.com...
> more info ...
> If I follow the original sequence of insert followed by select query I do
> get a recordset back with a field count of 1. the name of the item is indeed
> 'newid' but the value is NULL.
> strangely enough when using SQLCMD and doing this sequence of operations by
> hand - SQLCMD outputs one line for each record in the table with the newid
> field value for each being the "latest" value (i.e. value of 23 if the last
> inserted record had and identity value of 23). so it seems like it is
> working there, but not in the scripted app...
>
> --
> -Steve-o
>
> "steve-o" wrote:
>> solved the "Fields" problem - I had erroneously removed the "set" fro the rs
>> = stmt (WHOOPS !). So now the type is returned as Recordset, however,
>> rs.Fields.Count is 0 ... so still nothing is being returned...
>> --
>> -Steve-o
>>
>> "steve-o" wrote:
>> > Hi
>> >
>> > Strange ... the code works on older SQL server version. Nonetheless I
>> > changed as you suggested and same result. I did a little more digging and
>> > the execute line is return rs as type of 'Fields' instead of RecordSet AND
>> > the Count of the Fields is 0... i.e. the execute is returning NOTHING ...
>> > help !
>> >
>> > --
>> > -Steve-o
>> >
>> >
>> > "Mike Epprecht (SQL MVP)" wrote:
>> >
>> > > Hi
>> > >
>> > > You are submitting it as 2 batches, so it is not available to the 2nd one.
>> > >
>> > > Any reason why you are not using stored procedures to do this? Dynamic SQL
>> > > is asking for security problems.
>> > >
>> > > SCOPE_IDENTITY is the better way to retrieve is as a trigger will change the
>> > > @.@.identity's value if it does another insert.
>> > >
>> > > cmd = "INSERT INTO "&tab&" ("&col&") VALUES ("&val&") "select
>> > > SCOPE_IDENTITY as 'newid' from "&tab "
>> > > Set rs = nrconn.Execute(cmd)
>> > >
>> > > Regards
>> > > --
>> > > Mike Epprecht, Microsoft SQL Server MVP
>> > > Zurich, Switzerland
>> > >
>> > > IM: mike@.epprecht.net
>> > >
>> > > MVP Program: http://www.microsoft.com/mvp
>> > >
>> > > Blog: http://www.msmvps.com/epprecht/
>> > >
>> > > "steve-o" <steveo@.discussions.microsoft.com> wrote in message
>> > > news:FE2815A4-B7CC-4D44-B14C-D4CDCD4A198C@.microsoft.com...
>> > > >I have just inherited some code from another company which uses SQL Server.
>> > > > The logic is dependent on @.@.IDENTITY to retrieve the record just
>> > > > inserted -
>> > > > so that more info can be added to the record. kind of a silly
>> > > > implementation
>> > > > - but it uses a bunch of generic code to insert a new row and generate a
>> > > > GUID, etc.. and it would be significant work to rewrite the entire
>> > > > application.
>> > > >
>> > > > The problem is the even though the table has an id column that is defined
>> > > > with IDENTITY - and through queries I can easily see that each row added
>> > > > has
>> > > > the proper values for the id column - @.@.IDENTITY inside the .asp vb
>> > > > scripting
>> > > > app is returning NULL... I am using SQL Server Express 2005 Sept CTP set
>> > > > up
>> > > > using a system DSN with the SQL Native Client (2005) driver.
>> > > >
>> > > > here is the code:
>> > > >
>> > > > Function NewRow(con,tab,col,val)
>> > > > set nrconn = Server.CreateObject("ADODB.Connection")
>> > > > nrconn.open SiteConnectionString
>> > > > cmd = "INSERT INTO "&tab&" ("&col&") VALUES ("&val&") "
>> > > > Set rs = nrconn.Execute(cmd)
>> > > > cmd = "select @.@.IDENTITY as 'newid' from "&tab
>> > > > Set rs = nrconn.Execute(cmd)
>> > > > set NewRow = rs
>> > > >
>> > > > End Function
>> > > >
>> > > > After returning the code uses NewRow("newid") to fetch the record and
>> > > > update
>> > > > it. of course, it returns NULL and no record is retrieved... Any help
>> > > > would be much appreciated.
>> > > >
>> > > > --
>> > > > -Steve-o
>> > >
>> > >
>> > >|||YAY !!! Mike gave me the first half of the solution and Tibor the second
half. Thanks !!!
--
-Steve-o
"Tibor Karaszi" wrote:
> Try adding SET NOCOUNT ON before the INSERT statement.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "steve-o" <steveo@.discussions.microsoft.com> wrote in message
> news:9F100702-8F1B-4A2B-807E-14E72416B956@.microsoft.com...
> > more info ...
> > If I follow the original sequence of insert followed by select query I do
> > get a recordset back with a field count of 1. the name of the item is indeed
> > 'newid' but the value is NULL.
> >
> > strangely enough when using SQLCMD and doing this sequence of operations by
> > hand - SQLCMD outputs one line for each record in the table with the newid
> > field value for each being the "latest" value (i.e. value of 23 if the last
> > inserted record had and identity value of 23). so it seems like it is
> > working there, but not in the scripted app...
> >
> >
> >
> > --
> > -Steve-o
> >
> >
> > "steve-o" wrote:
> >
> >> solved the "Fields" problem - I had erroneously removed the "set" fro the rs
> >> = stmt (WHOOPS !). So now the type is returned as Recordset, however,
> >> rs.Fields.Count is 0 ... so still nothing is being returned...
> >> --
> >> -Steve-o
> >>
> >>
> >> "steve-o" wrote:
> >>
> >> > Hi
> >> >
> >> > Strange ... the code works on older SQL server version. Nonetheless I
> >> > changed as you suggested and same result. I did a little more digging and
> >> > the execute line is return rs as type of 'Fields' instead of RecordSet AND
> >> > the Count of the Fields is 0... i.e. the execute is returning NOTHING ...
> >> > help !
> >> >
> >> > --
> >> > -Steve-o
> >> >
> >> >
> >> > "Mike Epprecht (SQL MVP)" wrote:
> >> >
> >> > > Hi
> >> > >
> >> > > You are submitting it as 2 batches, so it is not available to the 2nd one.
> >> > >
> >> > > Any reason why you are not using stored procedures to do this? Dynamic SQL
> >> > > is asking for security problems.
> >> > >
> >> > > SCOPE_IDENTITY is the better way to retrieve is as a trigger will change the
> >> > > @.@.identity's value if it does another insert.
> >> > >
> >> > > cmd = "INSERT INTO "&tab&" ("&col&") VALUES ("&val&") "select
> >> > > SCOPE_IDENTITY as 'newid' from "&tab "
> >> > > Set rs = nrconn.Execute(cmd)
> >> > >
> >> > > Regards
> >> > > --
> >> > > Mike Epprecht, Microsoft SQL Server MVP
> >> > > Zurich, Switzerland
> >> > >
> >> > > IM: mike@.epprecht.net
> >> > >
> >> > > MVP Program: http://www.microsoft.com/mvp
> >> > >
> >> > > Blog: http://www.msmvps.com/epprecht/
> >> > >
> >> > > "steve-o" <steveo@.discussions.microsoft.com> wrote in message
> >> > > news:FE2815A4-B7CC-4D44-B14C-D4CDCD4A198C@.microsoft.com...
> >> > > >I have just inherited some code from another company which uses SQL Server.
> >> > > > The logic is dependent on @.@.IDENTITY to retrieve the record just
> >> > > > inserted -
> >> > > > so that more info can be added to the record. kind of a silly
> >> > > > implementation
> >> > > > - but it uses a bunch of generic code to insert a new row and generate a
> >> > > > GUID, etc.. and it would be significant work to rewrite the entire
> >> > > > application.
> >> > > >
> >> > > > The problem is the even though the table has an id column that is defined
> >> > > > with IDENTITY - and through queries I can easily see that each row added
> >> > > > has
> >> > > > the proper values for the id column - @.@.IDENTITY inside the .asp vb
> >> > > > scripting
> >> > > > app is returning NULL... I am using SQL Server Express 2005 Sept CTP set
> >> > > > up
> >> > > > using a system DSN with the SQL Native Client (2005) driver.
> >> > > >
> >> > > > here is the code:
> >> > > >
> >> > > > Function NewRow(con,tab,col,val)
> >> > > > set nrconn = Server.CreateObject("ADODB.Connection")
> >> > > > nrconn.open SiteConnectionString
> >> > > > cmd = "INSERT INTO "&tab&" ("&col&") VALUES ("&val&") "
> >> > > > Set rs = nrconn.Execute(cmd)
> >> > > > cmd = "select @.@.IDENTITY as 'newid' from "&tab
> >> > > > Set rs = nrconn.Execute(cmd)
> >> > > > set NewRow = rs
> >> > > >
> >> > > > End Function
> >> > > >
> >> > > > After returning the code uses NewRow("newid") to fetch the record and
> >> > > > update
> >> > > > it. of course, it returns NULL and no record is retrieved... Any help
> >> > > > would be much appreciated.
> >> > > >
> >> > > > --
> >> > > > -Steve-o
> >> > >
> >> > >
> >> > >
>