auto increment of number - SQL Server Forums

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

Development Tools

Other Development Tools

auto increment of number

Author

Topic

mswantek
Starting Member

3 Posts

Posted - 2008-09-24 : 07:22:16

Hi. this is my first post so please be gentle...

I am writing an application that will allow a user to submit a work request. This request gets a number.... This number is recorded in table A. Under each request number, there can be several requests.
Example.... Tracking number 2000 can have information coming in.... sequence 01, 02, 03, 04. This is in Table B.

The Code to generate a new Request number is easy, its the sequence under each that is confusing me.
In the Query analyzer I can get it to work.

DECLARE @newseq int
SET @newseq=(select max (suffixSeq) from incomingdata where (tagID) = '2004' AND (suffix)= 'ff')
SET @newseq =@newseq + 1
select @newseq

this is the table of what I would LIKE

ID suffix TagId suffixseq
1 en 2000 01
2 ff 2000 01
3 en 2001 01
4 en 2000 02
5 en 2000 03

<sql:query var="queryName" dataSource="jdbc/PPOefab" >
SET NOCOUNT ON
INSERT into incomingdata (suffix,tagID,tcaeitemnumb,tcaeitemrev,descriptofdata,custID)
Values ('${param.suffix}', '${param.projnum}', '${param.tcaeNum}', '${param.tcaeRevLevel}', '${param.dataDescription}','${sessionScope.svID}')
SELECT NewIdNumber = @@IDENTITY
SET NOCOUNT OFF
</sql:query>
<c:out value="${queryName.rows[0].NewIdNumber}"/>
<c:set var="idnumb" value="${queryName.rows[0].NewIdNumber}"/>
<sql:query var="new" dataSource="jdbc/PPOefab">
SELECT MAX(suffixSeq) as maxseq 
FROM incomingdata WHERE tagID = ? AND suffix= ?
<sql:param value="${param.projnum}"/>
<sql:param value="${param.suffix}"/>
</sql:query>
<c:out value="${new.rows[0].maxseq}"/>
<c:set var="newseq" value="${new.rows[0].maxseq + 1}"/>
<sql:update dataSource="jdbc/PPOefab">
UPDATE incomingdata
SET suffixSeq = '${newseq}'
WHERE ID = ? 
<sql:param value="${idnumb}"/>
</sql:update>

Why is it the query Analyzer makes it work but my JSTL code doesnt?

Please help.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-24 : 07:26:37

I think what you want is to have an identity column in your table.Make use of output parameter to get the generated id value each time and return it to your application.

mswantek
Starting Member

3 Posts

Posted - 2008-09-24 : 07:43:09

Which is what the @@IDENTITY is for.... Where it gets hung up right now is on the

<sql:query var="new" dataSource="jdbc/PPOefab">
SELECT MAX(suffixSeq) as maxseq 
FROM incomingdata WHERE tagID = ? AND suffix= ?
<sql:param value="${param.projnum}"/>
<sql:param value="${param.suffix}"/>
</sql:query>
<c:out value="${new.rows[0].maxseq}"/>
<c:set var="newseq" value="${new.rows[0].maxseq + 1}"/>

This is the error code I get
javax.servlet.ServletException:
SELECT MAX(suffixSeq) as maxseq
FROM incomingdata WHERE tagID = ? AND suffix= ?

: [sunm][SQLServer JDBC Driver]Unable to determine the type of the specified object.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-24 : 07:56:30

quote:
Originally posted by mswantek

Which is what the @@IDENTITY is for.... Where it gets hung up right now is on the
<sql:query var="new" dataSource="jdbc/PPOefab">
SELECT MAX(suffixSeq) as maxseq
FROM incomingdata WHERE tagID = ? AND suffix= ?
<sql:param value="${param.projnum}"/>
<sql:param value="${param.suffix}"/>
</sql:query>
<c:out value="${new.rows[0].maxseq}"/>
<c:set var="newseq" value="${new.rows[0].maxseq + 1}"/>

This is the error code I get
javax.servlet.ServletException:
SELECT MAX(suffixSeq) as maxseq
FROM incomingdata WHERE tagID = ? AND suffix= ?

: [sunm][SQLServer JDBC Driver]Unable to determine the type of the specified object.

its better to use SCOPE_IDENTITY() which will always return id generated in current scope. Didnt understand the cause of error though.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources