Incrementing a field

[Chloe828]

How can I increment a field without using Autonumber,
like reading from its last value and then increment it?
I'm new to vb.NET and really need a detailed explanation.

[Crusader2010]

I believe you are talking about autonumbers for fields in a database, right? In vb.net simply use a command (and a dataReader if you need more stuff) on a query like

INSERT INTO yourTable SELECT ((select count(*) from yourTable) + 1), ...

But be careful, when you delete a row from that table, the COUNT(*) might return an already existing index and cause an error.

EDIT: you can also do (if this is what you wanted)

INSERT INTO yourtable SELECT ((select MAX(yourTable_index) from yourTable)+1),...

[Chloe828]

Hi, thank you for your kind help.
I have tried the code you gave me but it doesn't seem to work.
Ican't store the ID nor increment the Code.
Here are my codes below,


Column "Code" is the one that suppose to increment as I store new "ID".

         Private Sub addbtn3_Click(ByVal sender As System.Object, ByVal e As      System.EventArgs) Handles addbtn3.Click
 
 Dim intaffected As Integer
        Dim intaffected2 As Integer
        Dim dsClassification As New DataSet
        dsClassification = New DataSet
        Dim strID As String
        Dim Code As Integer
 
        'Prompt the user to enter a ID
        If txtID.Text = "" Then
 
            MessageBox.Show("Please Enter ID!")
 
        End If
 
        If txtID.Text <> "" Then
            'Retrieve Column from database to check if there is any duplicate
            'Check duplicate2 is in the Service Object and whatever is entered in the text box
            'will be compared to the ID Column'
            dsClassification = CheckDuplicate2(txtID.Text)
            strID= txtID.Text
            'If its true/if a duplicate ID is found,
            If dsClassification.Tables(0).Rows.Count = 1 Then
                'Show Duplicate is found and ask the user to enter new ID
                MessageBox.Show("Duplicate Found. Please Enter New ID")
                txtID.Text = ""
 
 
            Else
 
 
                'new record
                intaffected = AddID(txtID.Text)
 
 
                MessageBox.Show("No Duplicate Found! Adding Successful! ")
 
                txtID.Text = ""
            End If
 
 
            If intaffected = 0 Then
 
 
 
            End If
        End If
 
 
    End Sub
 

Public Function AddID(ByVal ID As String)
        Dim strSql As String
        Dim dstemp As New DataSet
        Dim intaffected As Integer
 
        Try
            build()
            'Using a SQL statement INSERT INTO to include data to access table
            strSql = "INSERT INTO tblClassification SELECT ((select MAX(Code)from tblClassification)+1 ,[ID]) VALUES ('" & ID & "')"
 
 
 
            myCommand = New OdbcCommand(strSql, myConn)
            myCommand.CommandTimeout = 50
 
            connect()
 
            intaffected = myCommand.ExecuteNonQuery()
 
            Return intaffected
 
        Catch ex As Exception
            Try
 
            Catch odbcEx As OdbcException
                MessageBox.Show(odbcEx.Message & odbcEx.StackTrace)
            End Try
 
            Return intaffected
        Finally
            disconnect()
        End Try
 
    End Function
 
 

[Crusader2010]

strSql = "INSERT INTO tblClassification SELECT ((select 
MAX(Code)from tblClassification)+1 ,[ID]) VALUES ('" & ID & "')"
 

The problem is here, in your AddID function. One tip i can give you is to research the INSERT INTO SELECT combination on google to get a better understanding, and the INSERT INTO VALUES also. What i mean is this(just an example):

INSERT INTO table(ID,table_string,table_number) VALUES (1,'abc',3)
(or something similar)

is equivalent to:

INSERT INTO table(ID,table_string,table_number) SELECT 1,'abc',3

Now if instead of the ID you want to put something that increments itself (basically something that you don't need to or cannot know the value of), you put it like:

INSERT INTO table(ID,table_string,table_number) SELECT ((SELECT MAX(ID) FROM TABLE)+1) , 'abc' , 3

Make sure your ID field in the database is of type INT or Number or something similar, and not varchar or other types of strings.

Adjust this query to what your table has and, in order to make it easier to test everything, try running it directly on your database (not from code) and see what happens and if it works.

I don't know what kind of database you have and there could be some syntax differences (i'm working with transact-sql, but i believe it should also work on what you have).

Also, it's not very clear from your code, but are you trying to insert ONLY an ID into your table and still asking the user for its value? IDs (unique indexes) shouldn't be input from the keyboard usually. They are meant to keep track of the rows in the table, and this table should usually contain other data (i.e. more columns). Try reading about designing database tables to get a better understanding of these.

Now some other small issues:

Dim dsClassification As New DataSet
dsClassification = New DataSet

I believe you can leave the first line only, or Dim dsClassification As DataSet = New DataSet

Secondly, research the InputBox function for asking the user to input a value and returning it, i believe it will ease up your work (http://msdn.microsoft.com/en-us/libr...=VS.90%29.aspx).

Hope this helped. Good luck

[Chloe828]

I've changed the ID to description because since you've said its
unique indexes.. so here what I actually suppose to do,


Access data:

Column 1: CODE (primary key)
Column 2: DESCRIPTION


CODE DESCRIPTION

1 MAMMAL
2 REPTILE
3 AMPHIBIAN

Its like this, as I enter a new DESCRIPTION,
and when I click the add button, it suppose to be stored into the data table.
And the first column (CODE) should increment, so when I entered a new data,BIRDS, table should be like:

CODE DESCRIPTION

1 MAMMALS
2 REPTILES
3 AMPHIBIANS
4 BIRDS



ps. sorry for confusing you, I'm confused myself..

[Crusader2010]

Don't worry about it. So you should have something like this(in vb.net):

 
Dim description As String
' ...
' Here you determine the value of description
' by asking the user for it or something (see Inputbox function).
' So you should end up having description = "Mammals" and so on.
' ...
strSql = "INSERT INTO yourTable(Code,Description) SELECT (SELECT isnull(MAX(Code),0) FROM yourTable)+1, '" + description + "'"
 
' now you run your sqlCommand for every value of description in order to insert it in the table.
 

NOTE: isnull(MAX(Code),0) returns MAX(Code) if Code is not null (i.e. if the table entry is empty), and 0 otherwise. This helps you insert the first value in the table, since MAX(Code) with Code being NULL gives an error, so you need this test before. Also the syntax of isnull might vary according to your database, so search for a function that does exactly the same

isnull(x, value) => returns value if x=null.

[Chloe828]

It didn't increment my Code column nor store the new description
:(
How so?

Public Function AddClassification(ByVal Description As String)
        Dim strSql As String
        Dim dstemp As New DataSet
        Dim intaffected As Integer
 
        Try
            build()
            'Using a SQL statement INSERT INTO to include data to access table
            strSql = "INSERT INTO tblClassification(Code, Description) SELECT (SELECT isnull(MAX(Code),0) FROM tblClassification)+1, '" & Description & "'"
 
 
 
            myCommand = New OdbcCommand(strSql, myConn)
            myCommand.CommandTimeout = 50
 
            connect()
 
            intaffected = myCommand.ExecuteNonQuery()
 
            Return intaffected
 
        Catch ex As Exception
            Try
 
            Catch odbcEx As OdbcException
                MessageBox.Show(odbcEx.Message & odbcEx.StackTrace)
            End Try
 
            Return intaffected
        Finally
            disconnect()
        End Try
 
    End Function
 
 

Does it have to do with my click button?

Private Sub addbtn3_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles addbtn3.Click
        Dim intaffected As Integer
 
        Dim dsClassification As DataSet = New DataSet
 
        Dim strdescription As String
 
 
        'Prompt the user to enter a Classification Description
        If txtDescription.Text = "" Then
 
            MessageBox.Show("Please Enter Description!")
 
        End If
 
        If txtDescription.Text <> "" Then
            'Retrieve Column from database to check if there is any duplicate
            'Check duplicate2 is in the Service Object and whatever is entered in the text box
            'will be compared to the 'Classification Description Column'
            dsClassification = CheckDuplicate2(txtDescription.Text)
            strdescription = txtDescription.Text
            'If its true/if a duplicate Description is found,
            If dsClassification.Tables(0).Rows.Count = 1 Then
                'Show Duplicate is found and ask the user to enter new description
                MessageBox.Show("Duplicate Found. Please Enter New Description")
                txtDescription.Text = ""
 
 
            Else
 
 
                'new record
                intaffected = AddClassification(Description.Text)
 
 
                MessageBox.Show("No Duplicate Found! Adding Successful! ")
 
                txtDescription.Text = ""
            End If
 
 
            If intaffected = 0 Then
 
 
 
            End If
        End If
 
 
    End Sub
 

[Crusader2010]

Did it give you any error? Try running that insert query directly on the database (NOT from code). Something like:

INSERT INTO tblClassification(Code, Description) SELECT (SELECT isnull(MAX(Code),0) FROM tblClassification)+1, 'Mammals'

and see what happens. It might be a syntax problem like i said before. It depends on what kind of database you are using and if it connects to it properly, and if you have enough rights to insert data into it.

I've already tested it in sql server 2008 on an empty table with 2 fields(an int and a varchar) and it works just fine. If the query inserts the data properly in your table too then it's something wrong in the code. Please try this and let me know what happens, as well as what database you have (i mean with what did you create it, MS Access, MS Sql server 2008 etc).

PS: also in your AddClassification function, put the disconnect() BEFORE each Return statement. I believe it might not close the connection thus making it unable to see any modification on your database.

[Chloe828]

I'm using MsAccess 2007 for the database..
And I've tried the above and still it doesn't store.
As in it has no error or syntax, it just can't store :(

Is there any other way like using loops?

[Crusader2010]

Not the loops are the problem. It's strange there is no error. Did you do the following? (i've made the text bold and inclined)

Try
            build()
            'Using a SQL statement INSERT INTO to include data to access table
            strSql = "INSERT INTO tblClassification(Code, Description) SELECT (SELECT isnull(MAX(Code),0) FROM tblClassification)+1, '" & Description & "'"
 
 
            connect()
            myCommand = New OdbcCommand(strSql, myConn)
            myCommand.CommandTimeout = 50
 
            intaffected = myCommand.ExecuteNonQuery()
 
             disconnect() 
            Return intaffected
 
        Catch ex As Exception
             'Try
 
            'Catch odbcEx As OdbcException
            MessageBox.Show(Ex.Message & Ex.StackTrace)
            'End Try
 
            disconnect()
            Return intaffected
         
 
        End Try
 
 

You dont need the Finally statement in this case anymore. Also i don't know what connect() and disconnect() are actually doing. Do you have a global variable called myConn ? Are your ODBC data sources set properly in Windows?

If all of this doesn't do the trick, try replacing

(SELECT isnull(MAX(Code),0) FROM tblClassification)+1

with

(SELECT NZ(MAX(Code),0) FROM tblClassification)+1

in the query. Please try all of these things and tell me the results. It should work or at least give an error or something.

Did you run the query directly from MS Access, as i wrote in my previous post's example? (replace isnull with NZ if it gives an error). Please do this and say what happens.

[adi12345]

what is ( CheckDuplicate2 ) is it function or inbuilt etc!!!!