The best way to retrieve a single record and update with a new value

C

Coderz

Hi,

I have a table called key_master. This table looks like:

TableName NextNo <== This is a column
Customers 22
Users 9
Payments 11

The NextNo column saves a value that auto increments each time a user
clicks the Add New Record in my application.

Since I came from VB 6.0 and would like to port all my code to VB.NET
2008 I just want to know what is the best way of incrementing a value
using .NET?

This is the code I am using in VB 6

'Call a procedure called getIndex to get the current value and
increment by 1:

PK = getIndex("Customers")

Public Function getIndex(ByVal srcTable As String) As Long
On Error GoTo err
Dim RS As New Recordset
Dim RI As Long

RS.CursorLocation = adUseClient
RS.Open "SELECT * FROM [KEY_MASTER] WHERE TableName = '" &
srcTable & "'", CN, adOpenStatic, adLockOptimistic

RI = RS.Fields("NextNo")
CN.BeginTrans
RS.Fields("NextNo") = RI + 1
RS.Update
CN.CommitTrans
getIndex = RI

srcTable = ""
RI = 0
Set RS = Nothing
Exit Function
err:
''Error when incounter a null value
If err.Number = 94 Then
getIndex = 1
Resume Next
Else
MsgBox err.Description
End If
CN.RollbackTrans
End Function

Thanks for any help.

http://www.sourcecodester.com - Download free source code
 
M

Mr. Arnold

Coderz said:
Hi,

I have a table called key_master. This table looks like:

TableName NextNo <== This is a column
Customers 22
Users 9
Payments 11

The NextNo column saves a value that auto increments each time a user
clicks the Add New Record in my application.

Since I came from VB 6.0 and would like to port all my code to VB.NET
2008 I just want to know what is the best way of incrementing a value
using .NET?

This is the code I am using in VB 6

'Call a procedure called getIndex to get the current value and
increment by 1:

PK = getIndex("Customers")

Public Function getIndex(ByVal srcTable As String) As Long
On Error GoTo err
Dim RS As New Recordset
Dim RI As Long

RS.CursorLocation = adUseClient
RS.Open "SELECT * FROM [KEY_MASTER] WHERE TableName = '" &
srcTable & "'", CN, adOpenStatic, adLockOptimistic

RI = RS.Fields("NextNo")
CN.BeginTrans
RS.Fields("NextNo") = RI + 1
RS.Update
CN.CommitTrans
getIndex = RI

srcTable = ""
RI = 0
Set RS = Nothing
Exit Function
err:
''Error when incounter a null value
If err.Number = 94 Then
getIndex = 1
Resume Next
Else
MsgBox err.Description
End If
CN.RollbackTrans
End Function

Thanks for any help.
Click to expand...

I am sure you can find other examples that deal with using the
technology, by using Google.

http://www.vbdotnetheaven.com/UploadFile/mahesh/ADORecSet04232005043740AM/ADORecSet.aspx

What you really need is a good book on ADO.NET.
 
C

Coderz

The link you give is not related to my question. It is does not return
a single record.

I am looking for the best way to retrieve a single record and not a
BOOK.

Thank you

Coderz said:
Hi,

I have a table called key_master. This table looks like:

TableName NextNo <== This is a column
Customers 22
Users 9
Payments 11

The NextNo column saves a value that auto increments each time a user
clicks the Add New Record in my application.

Since I came from VB 6.0 and would like to port all my code to VB.NET
2008 I just want to know what is the best way of incrementing a value
using .NET?

This is the code I am using in VB 6

'Call a procedure called getIndex to get the current value and
increment by 1:

PK = getIndex("Customers")

Public Function getIndex(ByVal srcTable As String) As Long
On Error GoTo err
Dim RS As New Recordset
Dim RI As Long

RS.CursorLocation = adUseClient
RS.Open "SELECT * FROM [KEY_MASTER] WHERE TableName = '" &
srcTable & "'", CN, adOpenStatic, adLockOptimistic

RI = RS.Fields("NextNo")
CN.BeginTrans
RS.Fields("NextNo") = RI + 1
RS.Update
CN.CommitTrans
getIndex = RI

srcTable = ""
RI = 0
Set RS = Nothing
Exit Function
err:
''Error when incounter a null value
If err.Number = 94 Then
getIndex = 1
Resume Next
Else
MsgBox err.Description
End If
CN.RollbackTrans
End Function

Thanks for any help.
Click to expand...

I am sure you can find other examples that deal with using the
technology, by using Google.

http://www.vbdotnetheaven.com/UploadFile/mahesh/ADORecSet04232005043740AM/ADORecSet.aspx

What you really need is a good book on ADO.NET.
Click to expand...
 
A

Armin Zingler

Coderz said:
The link you give is not related to my question. It is does not return
a single record.
Click to expand...

Misunderstanding probably. Answer to the question on how to do the same in
..net (by setting a reference to the ADO library)
I am looking for the best way to retrieve a single record and not a
BOOK.
Click to expand...

Well, recommending a book is not a very detailled answer :) but it is not
wrong if more code has to be upgraded, and your code looks as if.




Two days ago I wrote a reply to your question but I didn't send it because
there may be a lot of space for criticism. ;-) However, here it is:



In a multiuser environment, concurrent access must also be kept in mind. I
assume you use an Access mdb.

Example (untested!!!): (didn't set connection string etc)


Private Function GetNextID(ByVal Table As String) As Integer

Dim cmdSelect, cmdUpdate, cmdInsert As OleDbCommand
Dim Result As Integer

cmdSelect = New OleDbCommand( _
"select NextNo from key_master where TableName=?")
cmdSelect.Parameters.AddWithValue("Table", Table)

cmdUpdate = New OleDbCommand( _
"update key_master set NextNo = ? where TableName=? and NextNo=?")
cmdUpdate.Parameters.Add("NewValue", OleDbType.Integer)
cmdUpdate.Parameters.AddWithValue("Table", Table)
cmdUpdate.Parameters.Add("OldValue", OleDbType.Integer)

cmdInsert = New OleDbCommand( _
"insert into key_master (Tablename, NextNo) values (?, 2)")
cmdInsert.Parameters.AddWithValue("Table", Table)

Do
Dim value As Object
value = cmdSelect.ExecuteScalar 'Get next ID

If value Is Nothing Then 'not found: Start with ID 1
Try
cmdInsert.ExecuteNonQuery() 'Insert new record for table
Return 1
Catch ex As OleDbException _
When ex.Errors.Count > 0 _
AndAlso ex.Errors(0).SQLState = "3022"
'Record has been inserted meanwhile.
'Try to update next time. Loop is not left.
End Try
Else 'update record
Result = DirectCast(value, Integer)
cmdUpdate.Parameters("NewValue").Value = Result + 1
cmdUpdate.Parameters("OldValue").Value = Result

If cmdUpdate.ExecuteNonQuery = 1 Then
Return Result
End If
End If
Loop

End Function


In addition, a timeout should be implemented to avoid an infinite loop (even
though very improbable), and you can insert a waiting period between
attempts.



Armin
 
C

Coderz

This is exactly what I am looking for. I am also planning to use data
reader but have ask here before I do it. But your code seems to work
fine.

Just used version 2003 before and found out that there's a lot of
modification to 2008. Ex: from data adapter to table adapter. from
datagrid to datagridview. And may be a lot more.

Thanks for the code. At least I can test a different aspect.
 
A

Armin Zingler

Coderz said:
Just used version 2003 before and found out that there's a lot of
modification to 2008. Ex: from data adapter to table adapter. from
datagrid to datagridview. And may be a lot more.
Click to expand...

Right. I haven't upgraded my mind to the new objects yet. Just got used to
the old ones. And ASA I master to use the new ones they become the old
ones. I seem to become the old one....


Armin
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Repost - Best way to get data from Access 2003 to Excel 2003 2
Passing a Recordset from VB6 to VB.Net (QueryInterface failed.) 1
connect to Access 2007 3
bound control to find a record 2
A problem with getting ID of a just inserted record 9
Using VB to look up and insert a value. 9
Best way to retrieve a value from a table 4
need to update recordset for a function to calculate correctly - clarified and continued 5

Top