N/APosted on - 07/28/2011
The issue I am facing is: I want to save multiple records with unique ID’s at the same time when I am using SQL in back end and VB 6.0 at front end.
For example: I want to enter 15 records in the database from only one VB form.
But the issue is that I want to create unique ID’s in the database table and when I am saving only one ID is generated and other data is saved on the base of this ID.
Any help would be appreciated!
Want to add multiple records with multiple IDs
The way you are following for this purpose is wrong, because the scenario you have mentioned created when you are making one filed of your record or form as a unique ID and saving rest of the records on the basis of that record. All you need to consider is as follows:
1: Make the table with the key ID field, set that field as primary key.
2: Set “Identity” in the description area as “yes”.
3: Set “Identity Seed” in the description area as “1”.
4: Set “Identity Increment” in the description area as “1”.
5: Enter the remaining required fields in the table and save the table.
6: Then open your coding project in VB.
7: In VB, use grid for adding multiple records in a single form.
8: In the coding of “Save” button, use the query I am sending here as an idea.
*Here sql is a variable of string type and I am sending query to this variable.
Dim sql As String
For Counter = 1 To .Rows – 1
sql = "INSERT INTO TableName ("
sql = sql & ", ProductID "
sql = sql & ", Quantity "
sql = sql & ", UnitRate "
sql = sql & ", Amount "
sql = sql & ") VALUES ("
sql = sql & "," & Val(.TextMatrix(Counter, grid_ProductID)) & ""
sql = sql & "," & Val(.TextMatrix(Counter, grid_Quantity)) & ""
sql = sql & "," & Val(.TextMatrix(Counter, grid_UnitRate)) & ""
sql = sql & "," & Val(.TextMatrix(Counter, grid_Amount)) & ""
sql = sql & ")"
As you can see that here I am not saving the unique ID field through query because whenever you will run this query, new record will be inserted into the database with automatically increment in ID by 1.
Enjoy the Solution.