Prob:Distinct Increment Manual AutoNumber Field

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

Other Forums

MS Access

Prob:Distinct Increment Manual AutoNumber Field

Author

Topic

btp111
Starting Member

6 Posts

Posted - 2008-05-06 : 17:00:11

Hi. Thanks in advance to anyone who can help with this.

I have a simple app that I am trying to create that will run an Insert statement into my SQL Database from an Access front end. It all works well except one of the fields must be a next number field and it must be unique. I have tried setting up a new SQL table on the svr and I was planning on doing an insert statement into that table that would add a row upon the form opening to this table. Then I would use the MAX value of that column as my next number. Does this seem feasible to you?

Stored Procedure:
CREATE PROCEDURE InsNN @nn float(8) AS
Insert Into ManualNextNumber (ManualAutoNumber)
Values
(@NN)
GO

My code in the Form Load section looks like this:

Dim number As ADODB.Parameter
Dim cn1 As New ADODB.Connection
Dim cmd1 As New ADODB.Command
Dim rs As New ADODB.Recordset
Dim line As Variant
Dim strsql As String
Dim cmd2 As ADODB.Command
Dim newnum As Variant

cn1.Provider = "sqloledb"
cn1.Properties("Data Source").Value = "HLAERP1"
cn1.Properties("Initial Catalog").Value = "PS_CRP"
cn1.Properties("Integrated Security").Value = "SSPI"
cn1.Open

Set cmd1.ActiveConnection = cn1
cmd1.CommandText = "insNN"
cmd1.CommandType = adCmdStoredProc
cmd1.CommandTimeout = 15

'Set cmd object to find the max value.
Set cmd2.ActiveConnection = cn1
cmd2.CommandText = "Select Max(ManualAutoNumber) from ManualNextNumber"
cmd2.CommandType = adCmdText
cmd2.CommandTimeout = 15

'Pass parameter into the command that calls the Store Procedure.
Set number = cmd1.CreateParameter("InputLineNumber", adInteger,adParamInput)
cmd1.Parameters.Append number
newnumber = cmd2.CommandText
'number.Value = number

Set rs = cmd1.Execute
My biggest issue is I cannot seem to get it to run 2 SQL statements on one connection to the DB. Also, I need to be able to pass the results from the sQL statement (cmd2.commandtext) to a variable so I can then determine what value to pass to the InsNN stored procedure. This is my first attempt at creating something like this.

Any ideas on this or how I can do this better or more efficiently is greatly appreciated.
Have a good day.

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2008-05-07 : 01:30:37

You need to do it Max+1, but you will have issues with concurency with multiple users. Why not just use an autonumber (Identity in SQL)?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-05-07 : 04:17:17

Also, when you run this for the first time, MAX(ManualAutoNumber) will return NULL.

E 12°55'05.25"
N 56°04'39.16"

btp111
Starting Member

6 Posts

Posted - 2008-05-07 : 08:04:08

Thanks guys for the help. When I run this code as is I get the following error: object variable or block variable not set.
Debug says it is referring to the following code (line 1).
Set cmd2.ActiveConnection = cn1
cmd2.CommandText = "Select Max(ManualAutoNumber) from ManualNextNumber"
cmd2.CommandType = adCmdText
cmd2.CommandTimeout = 15

Rick, I would rather use the Identity value in SQL but I could not think of a way to increment that in the code. Any ideas would be appreciated.

Thanks.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-05-07 : 08:13:16

No need for.
IDENTITY columns are AUTOINCREMENTED for every insert.

E 12°55'05.25"
N 56°04'39.16"

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2008-05-07 : 09:39:02

quote:
Originally posted by Peso

No need for.
IDENTITY columns are AUTOINCREMENTED for every insert.

E 12°55'05.25"
N 56°04'39.16"

You also have the added bonus of not having to worry about concurrency as this is hadled at the Database Engine level.

btp111
Starting Member

6 Posts

Posted - 2008-05-07 : 12:27:55

Wouldn't I need to do an insert into the table and then pull the last AutoNumber identity field into my form as a variable? I am having trouble connecting to 2 tables in the DB at the same time. As you can see from my last post it gives me an error. I am guessing I would need to follow the below flow as I need to use the unique next number in my Insert statement to the table that I am calling when the user clicks a Button:
1)On Form Load Do a Select Max of Identity column.
2)Capture that Max # + 1 to a variable. I would use this as the next number for my insert statement.
3)Immediately do an Insert statement to that table so that the next number stays unique. The next time the form is loaded it would grab the next number.

Sorry, but as you can tell I am new to this. How do I assign the return value from my select statement (select Max(AutoNumber) from Table1) to a global variable to be used later when the user clicks the Button. Then do I just do the insert statement and insert anything into that column to generate the next number. I think I can get the Insert statement to work but I am unable to use 2 connections to the DB with the 1 connection object.

Thanks again and sorry if these are easy questions.

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2008-05-08 : 02:09:45

As you can see fromt he following, you can capture the last identity inserted into a table fairly easily.

Why do you need the number before anything else is written?


declare @id int
declare @a table (a int identity(1,1), b char(1))
Insert Into @a
select 'a'
union select 'b'

set @id = scope_identity()

select * from @a

select @id

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources