Vb net record counter

[no1filmfan]

Hello,
I am trying to make a record counter for a textbox i have on my form. Basically i have an Access database called Member and wish too count how many records there are in the database and insert the figure into the textbox called MemberID. I am doing this to stop duplication in my form. I am doing it on the MemberID which is a Primary Key and not an autonumber.

I have tried this code so far but i have had no luck

Public Class frmMember
    Dim Include As Integer
    Dim Connect As New OleDb.OleDbConnection
    Dim DataSet As New DataSet
    Dim DataAdapter As OleDb.OleDbDataAdapter
    Dim StructuredQueryLanguage As String
    Dim LastRow As Integer
    Dim Maxrows As Integer
 
Private Sub btnNewRecord_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnNewRecord.Click
 txtMemberID.Text = DataSet.Tables("Members").Rows.Count
        'Maxrows = DataSet.Tables("Members").Rows.Count
        Include = +1

I have got alot of code on my database. But i have tried using count so it goes up in order and the user wont have to enter anything into the textbox but i am really struggling to get it working.

Any suggestion on fixing my problem would be grateful
Thank You

[Shaggy Hiker]

Are you trying to find the current highest value so that you get one above that, or just the count of the number of records? What you have shown would give you the number of records, as long as the dataset has the complete set of records, but apparently you are having some issue with that, so I expect that is not quite what you want.

[no1filmfan]

oooo i see yes the one above it i think :s I am having a problem with it though because i have five records saved and stored in my database however, every time i add a new record it still remains as five records when really it should be 6. What i am trying to do is to get numbers entered automatically in the textbox to stop duplication. And so i thought the easiest way to do this was by doing a record count

[Shaggy Hiker]

Wouldn't that number have to be unique, though? If so, you don't so much want the count as you want the Max value. After all, if you are using the count N, and you end up deleting a record from the middle of the table, the count will be N-1, but N will already be used for a record, so the count would no longer do you any good. Using Max+1 would be ideal.

There may be an easier way to get the max, but this should work:

Dim max = (from m in Dataset.Tables("Members") Select DirectCast(m,Datarow).Item("MemberID")).Max

Alternatively, you could do something like this:

If Dataset.Tables("Members").Select("MemberID = '" & txtMemberID.Text & "'").FirstOrDefault IsNothing Then
 'Whatever was entered is not in the dataset.
Else
 'Whatever was entered IS in the dataset
End If

Still, one thing concerns me about what you said: You stated that the count didn't change when you added a row. That's not right. I'd investigate that further. It suggests that you are not adding the new row to the dataset, which would mean that either of the previous methods will fail.

[no1filmfan]

Hi, Thank you for your help it is much appreciated i just carnt seem to get my head around it!
The two codes above do not work in my form. See ideally i would like text to be entered straight away when the user inputs so i can just disable the txtMemberID box so they wont have to enter into the textbox. I dont know if i am missing something really obvious i dont know. Here is abit more of my code

Public Class frmMember
    Dim Include As Integer
    Dim Connect As New OleDb.OleDbConnection
    Dim DataSet As New DataSet
    Dim DataAdapter As OleDb.OleDbDataAdapter
    Dim StructuredQueryLanguage As String
    Dim Maxrows As Integer

I am presuming the code above i would enter into the New record button (btnNewRecord)?

I have tried that many codes now i am just confused about the whole thing i have been searching for hours trying to find the right thing. I have saved multiple copies of my database and form. I just tried the code in a new back up of it that works fine but the code you have given me still dosent work.

On my form i have a update,new record, edit, delete and cancel button.
The user clicks new record so the fields become blank
The user clicks edit to edit the form so the read only is disabled
The user clicks update and thats when it officially updates the database.

Thank You for your help its doing my head in!

[Shaggy Hiker]

I suppose I should ask what version of the framework you are targeting, as that first example used LINQ, which would require framework 3.5 or higher.

Other than that, what errors are you getting? The fact that you have a dataset called Dataset is a bit troubling, but it should work.

[no1filmfan]

Hi I am using VB Net 86x. I am not getting any error messages on the code just 5 keeps appear when i do it this way.

Private Sub btnNewRecord_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnNewRecord.Click
txtMemberID.Text = DataSet.Tables("Members").Rows.Count
DataSet.Tables("Members").Rows(Include).Item(0) = txtMemberID.Text
Include = -1
DataSet.AcceptChanges()


I am presuming that is the kind of text i need but im not sure if i have written it right?

[jasonwucinski]

what about using the type AutoNumber for your pk? The DBMS will automatically increment the value for you

[Shaggy Hiker]

VB Net 86x is not a version. It was already clear that you were using VB, and it was also clear that you were using .NET. As for 86x, that isn't anything at all. I expect that you meant x86, which still isn't a version, it is just a processor architecture target. The question is the framework you are targeting, which is found near where you found that x86.

As for what you have written, the first line makes sense, and would show the count of the number of rows in the table. The next line is likely to be problematic in some situations. I would guess that Item(0) is your primary key, so you are setting the primary key to the current count. That will work as long as the first item is 0 (unusual for numeric primary keys, but possible since you are not using AutoIncrement), and as long as no record is ever deleted from the table.

The bigger problem is the final line. Calling AcceptChanges is generally not done, as it will change the state of all the rows and prevent you from ever being able to push the data back to the database (or at least make it horribly inefficient and difficult).

[no1filmfan]

Hi thank you for your reply however, this is university work and my lecturer only wants code to be used as i would of used auto number in my MS database

[jasonwucinski]

i cant believe that you professor would want you to do it this way, as using an autonumber surrogate key is the generally accepted way to do this. consider, right now it looks like you are creating a PK by counting the number of records already found in the table. This is no guaranteed way to do it. what if your table has 5 records (with PK 1-5). If you count the records, then add 1, your next would be 6. Unless one of the records (2-4) were deleted. Then you would have 4 records + 1 = 5, which already exists and is now in violation of PK. Or, what if the PK doesn't start at 1? There are to many problems with creating your own surrogate key. Are you sure you understood the question? Of course, this wouldn't be the first professor that didn't know what they were doing

[Hack]

Duplicate Threads Merged

Please do not create multiple threads for the same topic.

Thank you.

[no1filmfan]

Hi, thank you for both replies.
Shaggy Hiker: - Sorry i misunderstood your question. Ok i understand i have tryedd and tryed still found no real answer to what i need to do.
jasonwucinski : - The problem is we started off using auto number and then he said your not aloud to use auto number now but keep it as a primary key because the table needs a primary key. Yes my lecture did say that not to use it! So now i am trying to figure out a way around my program to stop it crashing or replacing one of my records.
For instance if i already have a member id with 1 in and a user tries to input 1 then basically it replaces it which is a major problem.
We are making a form which does multiple things so now i am just basically trying to find away around the user entering a number that already exists in the database! I would like maybe a message box to appear saying this already exists but i have searched and searched and i am still stuck!
I would really appreciate some help on code or something just to make it work i am literally pulling my hair out now over this problem.
Thank You

[jasonwucinski]

Well, you should always use error catching. ie:

try
 '... your code here

Catch sqlExc As SqlClient.SqlException
     MsgBox(sqlExc.ErrorCode)
 Catch genExc As Exception
      MsgBox(genExc.Message)
 End Try

This way, when an error is generated, you can catch it, find out what the error is, and deal with it. Errors produce specific error number and messages. The example above will catch general error and SQLClient errors.

[Evil_Giraffe]

The user should not be generating the Id, normally. You would generate the Id yourself in code (assuming you're in a strange parallel universe where you're not allowed to use the features of your database - by the way, has your lecturer banned the use of the 'INSERT' SQL statement yet?) and submit it along with the other fields that are taken from user input.

In your case, you want the next highest value after the current highest. As SH says, you can use the MAX() function in a database query to return the current maximum, then add 1 to the value to get your key.

Of course, this doesn't stop something else inserting a field with that value as the ID between getting the max and calling INSERT, so your program had better be prepared to try again if it gets a Primary Key Violation when it calls INSERT.

Alternatively, use a GUID as a primary key. You can be fairly sure that you aren't going to generate a GUID that's in use separately, but it is still a very remote possibility so you still need the retry code. It saves having to query for the current maximum, however.

[Evil_Giraffe]

For instance if i already have a member id with 1 in and a user tries to input 1 then basically it replaces it which is a major problem.

Are you using some kind of UPSERT function? (UPdate or inSERT)

A straight INSERT should return an exception if a record with that primary key already exists. Use standard exception handling to deal with this situation to prevent the app crashing.

[no1filmfan]

Hi,
I am using OleDb version.
Jasonwucinski: - I have tried the code you have given but all that comes up now is Error: -
The changes you requested to the table were not successful because they would create duplicate values in the index, primary key, or relationship. Change the data in the field or fields that contain duplicate data, remove the index, or redefine the index to permit duplicate entries and try again.

[no1filmfan]

I use an update code in my form. I have tried the MAX() function but still having no luck its really getting on my nerves now I am a new to VB and by the sounds of it like you guys have said if its there we should be able to use it.

[Shaggy Hiker]

Show us how you used the MAX function. Also, are you trying to get the max value from the datatable or the database itself? You can get the value from the datatable if you can be sure that your datatable holds all the records from the database (or at least holds the one with the highest current value). If you can't be absolutely certain of that, then you would want to get the MAX value from the database directly.

[Evil_Giraffe]

Ah, didn't notice the DataTable sitting infront of the database. That probably negates most of the things I mentioned.