I'm a Noobie, so please be descriptive and provide sample code.

Situation: ASP.NET 2.0, VB.NET, OLE.DB

I've created a page in my ASP.NET Website which will allow the user to add a record. I made the access database "RecordID" a number so that I can manually create the ID through code. The reason I did this is because the user will also be able to update the record, making it RecordID = 1 & VersionID = 2, and so on. So I will eventually have multiple records with the same RecordID, but with different VersionID #'s, so that there are no duplicates. And as you know, using autonumber will not allow for inputting the same ID twice. This event will take place on a btnSubmit_Click event handler. 

The reason I must be able to duplicate the Record ID is because my client does not want any records to be replaced. So, when an employee updates the record, instead of replacing it, it will be added as a new record. But they need to have the same ID number so they are linked, and have different version number so that all records with the same ID number can be called and viewed, as well as tracked. 

I Have pasted the code I have below so you may better help my situation. Notice my problem is here: 

 ( ' Add Record ID parameter and set it to the value entered by user

cmd.Parameters.Add("@RecordID", Data.OleDb.OleDbType.VarChar, 50)

cmd.Parameters("@RecordID").Value = "1"

' Add Record Version parameter and set it to the value entered by user

cmd.Parameters.Add("@RecordVersion", Data.OleDb.OleDbType.VarChar, 50)

cmd.Parameters("@RecordVersion").Value = "1" )

I hope to be able to use this example for my Record Update page too. I'm sure it won't be much different.

Please provide your explanation and code sample in OLE.DB and VB.NET. Thank you again, Jim

Protected Sub btnCreateRecord_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnCreateRecord.Click

' User clicked Submit Record button - required fields have been filled in. create record

Dim conn As New OleDbConnection ' define connection string

Dim cmd As New OleDbCommand ' define data command

Dim cmdString As String ' select query

Dim blnSaved As Boolean = True ' indicates if save was successful, default to true  ' Create connection to access database

conn = New Data.OleDb.OleDbConnection("Provider=Microsoft.Jet.OleDb.4.0;" & _ "Data Source=|DataDirectory|\RecordDatabase.mdb")

' Create insert data command

cmdString = "INSERT INTO [tblRecord] ( [RecordID], [RecordVersion], [ProjectName], [Department], [OriginalSubmitDate], [RecordStatement], [Rank], [Identifiedby], [RecordDetail]) VALUES " & _ "(@RecordID, @RecordVersion, @ProjectName, @Department, @OriginalSubmitDate, @RecordStatement, @Rank, @Identifiedby, @RecordDetail)"

' Create data command using the select statement and connection string

cmd = New Data.OleDb.OleDbCommand(cmdString, conn)  

' Add Record ID parameter and set it to the value entered by user

cmd.Parameters.Add("@RecordID", Data.OleDb.OleDbType.VarChar, 50)

cmd.Parameters("@RecordID").Value = "1"

' Add Record Version parameter and set it to the value entered by user

cmd.Parameters.Add("@RecordVersion", Data.OleDb.OleDbType.VarChar, 50)

cmd.Parameters("@RecordVersion").Value = "1"

' Add Project Name and set it to the value entered by user

cmd.Parameters.Add("@ProjectName", Data.OleDb.OleDbType.VarChar, 50)

cmd.Parameters("@ProjectName").Value = Me.txtProjectName.Text

' Add Department parameter and set it to the value entered by user

cmd.Parameters.Add("@Department", Data.OleDb.OleDbType.VarChar, 50)

cmd.Parameters("@Department").Value = Me.txtDepartment.Text

' Add Date parameter and set it to the value entered by user

cmd.Parameters.Add("@OriginalSubmitDate", Data.OleDb.OleDbType.VarChar, 50)

cmd.Parameters("@OriginalSubmitDate").Value = Me.txtDate.Text

' Add Record Statement parameter and set it to the value entered by the user

cmd.Parameters.Add("@RecordStatement", Data.OleDb.OleDbType.VarChar, 50)

cmd.Parameters("@RecordStatement").Value = Me.txtRecordStatement.Text

' Add Rank parameter and set it to the value entered by user

cmd.Parameters.Add("@Rank", Data.OleDb.OleDbType.VarChar, 50)

cmd.Parameters("@Rank").Value = Me.ddlRank.Text

' Add Identified by parameter and set it to the value entered by the user

cmd.Parameters.Add("@Identifiedby", Data.OleDb.OleDbType.VarChar, 50)

cmd.Parameters("@Identifiedby").Value = Me.txtIdentifiedby.Text

' Add Requirement Detail parameter and set it to the value entered by the user

cmd.Parameters.Add("@RecordDetail", Data.OleDb.OleDbType.VarChar, 50)

cmd.Parameters("@RecordDetail").Value = Me.txtRecordDetail.Text

' Open the connection to database

conn.Open()

Try

cmd.ExecuteNonQuery()

Catch ex As Exception

' If error ocurred during save, display error and set focus to User ID and set blnSaved to false

blnSaved = False

lblError.Visible = True

Me.txtRecordStatement.Focus()

End Try

' Close connection

conn.Close()

' If save was successful, Notify User and redirect to default.aspx

If blnSaved = True

ThenMsgBox("Your Record was successfully saved! You will now be directed to the Main Page.")

Response.Redirect("~/Default.aspx")

End If

End Sub

Exactly what is the problem, I only see lots of VB code?

I'll give it a try anyway. You say that RecordID 'is a number' do you mean that the type is numeric (Integer, Long Integer) in Access? If so, the first parameter is wrong,m you assign it the string "1" instead of the numeric value 1 (and off course the parameter type is wrong). The sam applies to RecordVersion

Hi Hans_V. Thank you for your response.

My problem is, the way I have my code written only works if the RecordID is set as an "autonumber" in access. Because I would just eliminate the RecordID syntax and access would assign it it's own autonumber. To answer your question, yes, I set the RecordID as "number" in access so that I could manually create the RecordID. I did this so that when a user updates a record, I will assign it the same RecordID # and give it a new VersionID #. It needs to be done this way because the cient does not want to replace or delete previous records. They want the original version still there for history purposes, and add the new version as an update to that record. They will have the same RecordID so that all "same" records can be searched and viewed easily, as many different Records will be produced.

So, to recap: I need help determining the Max ID # of the RecordID, so I can create the next availible RecordID in access, upon New Record input. The same technique will be used in the "Update Record" page when finding the Max ID # for the VersionID, and adding the next available "VersionID" to that Record which is being updated, and adding the record to the database..

I'm not real sure I understand what you were trying to tell me in your explanation of how to fix my problem. Would you, or anyone reading this explain how to do this and provide an example using Ole.Db and VB.NET, along with my code. As I mentioned, I'm a noobie. Thank you for any assistance.

OK, now I think understand what you;re trying to do.

First of all, I would recommend to use a autonumber field anyway. Just add an extra field, and make this field the primary key.

So then the next part. What I understand is that you when you update a record, you're not want to update the record, but insert a new record instead, with the same RecordID and Recordversion + 1?

This can be done using the following query:

INSERT INTO [tblRecord] ( [RecordID], [RecordVersion], [ProjectName], [Department], [OriginalSubmitDate], [RecordStatement], [Rank], [Identifiedby], [RecordDetail])
SELECT @RecordID, MAX([Recordversion]) + 1, @RecordVersion, @ProjectName, @Department, @OriginalSubmitDate, @RecordStatement, @Rank, @Identifiedby, @RecordDetail FROM [tblRecord] WHERE [RecordID] = @RecordID

In the same way, if you want to insert a new record (with the next recordID, and recordversion 1), you could use:

INSERT INTO [tblRecord] ( [RecordID], [RecordVersion], [ProjectName], [Department], [OriginalSubmitDate], [RecordStatement], [Rank], [Identifiedby], [RecordDetail])
SELECT MAX([RecordID]) + 1, 1, @ProjectName, @Department, @OriginalSubmitDate, @RecordStatement, @Rank, @Identifiedby, @RecordDetail FROM [tblRecord] WHERE [RecordID] = @RecordID

(This last query only works if the table isn;t empty, so you'll have to insert the first record (recordID 1, RecordVersion 1) manually, or change the query a little bit