[RESOLVED] AutoIncrement Problem

[ecabralrojas]

Good Morning:

I have a problem autoincrement a field "OrderNumber", I have a Listview with four column: "QTY", "Description", Price:

This is my code to save the Listview Item to the database Item:

Private Sub cmdSave_Click()

Dim rs As ADODB.RecordSet: Set rs = New ADODB.RecordSet
Dim Conn As ADODB.Command: Set Conn = New ADODB.Command

With Conn
.ActiveConnection = DataBaseServer
.CommandType = adCmdText

End With


Dim SQLQuery As String
Dim i As Integer




For i = 1 To ListView1.ListItems.count


SQLQuery = "INSERT INTO Item ([OrderNumber],[Qty],[Description],[Price] Values ("
SQLQuery = SQLQuery & Generate_No & ", "
SQLQuery = SQLQuery & (ListView1.ListItems(i).Text) & ", "
SQLQuery = SQLQuery & (ListView1.ListItems(i).SubItems("Description")) & ", "
SQLQuery = SQLQuery & (ListView1.ListItems(i).SubItems(("Price"))) & ", "

SQLQuery = SQLQuery & ")"


Conn.CommandText = SQLQuery
Conn.Execute

Next i


End Sub


this is the function that I call to autoincrement the "OrderNumber"


Private Function Generate_No() As Integer

Dim rst As ADODB.RecordSet: Set rst = New ADODB.RecordSet
Dim Conn As ADODB.Command: Set Conn = New ADODB.Command

With Conn
.ActiveConnection = DataBaseServer
.CommandType = adCmdText

End With


RecordSet.Open "Select Max(OrderNumber) as NextNum from Item", Conn.ActiveConnection, adOpenStatic, adLockOptimistic

Generate_No = RecordSet("NextNum") + 1

Rst.Close

Set Rst = Nothing


End Function


The Problem is that this generate for each number row an autoincrement number

I need One OrderNumber For all Items when i saved and I need to generate this secuence 00000-99999

I saw somes posted about this here but i found some that can fix with my

Please I'll Appreciate you help

Elvis Cabral

[nick2k3]

are your table normalized??? it seems that you're trying to save your header data and detail data into one table...
please correct me if im wrong...

[ecabralrojas]

Yes i'm going to save in one table.

what is the best way save the data and detail?, Please

Elvis Cabral

[si_the_geek]

I'd recommend following up on what nick2k3 hinted at - you should Normalise your tables. For information, see the article about it in the "Design" section of our Database Development FAQs/Tutorials (at the top of this forum)

I'd also recommend not doing your own auto-increment routine, but instead letting the database system do it (as it deals with several issues you don't seem to have thought of yet).

[nick2k3]

"as it deals with several issues you don't seem to have thought of yet"

yes mr. MODERATOR this is VERY VERY TRUE!!! some times value is there but not really...heheheh!!!

[ecabralrojas]

Thanks Alot Guys

Elvis Cabral