How to Increment datarow by value 10

Question

Public Class frmVisitor

    Private dbConnection As New OleDb.OleDbConnection()
    'To add a DataAdapter
    Private daVisitor As OleDb.OleDbDataAdapter
    'To add a CommandBuilder
    Private cmdbVisitor As OleDb.OleDbCommandBuilder Private dtVisitor As New DataTable
    Private rpVisitor As Integer = 0
    Dim intNewVisitorID As Integer

    Private Sub frmVisitor_FormClosed(ByVal sender As Object,  ByVal e As System.Windows.Forms.FormClosedEventArgs Handles Me.FormClosed

        'Closing a Connection to a Data Source ta the time when Main Form Closed
        'this is accomplished by calling the Close() method of the connection object
        dbConnection.Close()
        dbConnection.Dispose()
    End Sub

    Private Sub frmVisitor_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

        dbConnection.ConnectionString = _
        "Provider=Microsoft.Jet.OLEDB.4.0;Data Source =SNGPLDbase.mdb"

         daVisitor = New OleDb.OleDbDataAdapter("Select * From Visitor", dbConnection)
        'To initialize the CommandBuilder object
        cmdbVisitor = New OleDb.OleDbCommandBuilder(daVisitor)

        daVisitor.Fill(dtVisitor) 'Because the DataTable doesn't hold a connection to the data source, you don't need to close it when you're finished.
        'Used to display the current record in the data table
        'Me.ShowVisitorRecord()
    End Sub

    Private Sub ShowVisitorRecord()

        If dtVisitor.Rows.Count = 0 Then 'When there is no record

            Me.cboVisitorCode.Text = ""
            Me.txtVisitorName.Text = ""
            Me.txtVehicleNo.Text = ""
            Exit Sub

        End If  'When there is record

        cboVisitorCode.Text = dtVisitor.Rows(rpVisitor)("VisitorCode")
        txtVisitorName.Text = dtVisitor.Rows(rpVisitor)("VisitorName")
        txtVehicleNo.Text = dtVisitor.Rows(rpVisitor)("VehicleNo")

    End Sub

   Private Sub btnAdd_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnAdd.Click

        If dtVisitor.Rows.Count = 0 Then 'When there is no record

            Dim intNewVisitorID = 10 'Initial value of VisitorCode
            Dim drNewRow As DataRow = dtVisitor.NewRow()
            drNewRow.Item("VisitorCode") = intNewVisitorID

            Me.cboVisitorCode.Items.Add(drNewRow.Item("VisitorCode"))
            Me.cboVisitorCode.Text = drNewRow.Item("VisitorCode")

            '  cboVisitorCode.Text = dtVisitor.Rows(rpVisitor)("VisitorCode")
            dtVisitor.Rows.Add(drNewRow)

            Exit Sub
        Else

            ' If there are any rows in the data table,
            ' move to the last and show the record.

            If dtVisitor.Rows.Count > 0 Then
                rpVisitor = dtVisitor.Rows.Count - 1
                ' Me.ShowVisitorRecord()
            End If

            Dim drNewRow As DataRow = dtVisitor.NewRow()
            intNewVisitorID = drNewRow.Item("VisitorCode") + 10 'VisitorCode increase by 10
            drNewRow.Item("VisitorCode") = intNewVisitorID

            'To Insert new VisitorCode to Combobox In case of New Entry.
            Me.cboVisitorCode.Items.Add(drNewRow.Item("VisitorCode"))
            Me.cboVisitorCode.Text = drNewRow.Item("VisitorCode")
            dtVisitor.Rows.Add(drNewRow)

        End If
    End Sub
End Class

Answer 1

can you re-post your code, please, in the correct format? Click the </> button on the toolbar above where you type the question. Select VB.NET from the language box.

second why not have your ID column as AutoIncrement? Then you can set the seed and increment. Try the below in a test app with 1 button and 1 datagridview. You can see the AutoIncrement code.

    Dim dtTest As New DataTable
    Dim dsTest As New DataSet

    Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        dtTest = dsTest.Tables.Add("dtTest")
        dtTest.Columns.Add("myID", GetType(Integer))
        For i As Integer = 1 To 2
            dtTest.Columns.Add("Column" & i.ToString)
        Next
        dtTest.Columns(2).DataType = GetType(Integer)

        dtTest.Columns(0).AutoIncrement = True
        dtTest.Columns(0).AutoIncrementSeed = 10
        dtTest.Columns(0).AutoIncrementStep = 10

        Dim rnd As New Random
        For r As Integer = 1 To 50
            Dim value1 As String = "Row" & r.ToString
            Dim value2 As Integer = rnd.Next(0, 101)
            dtTest.Rows.Add(Nothing, value1, value2)
        Next

        DataGridView1.DataSource = dtTest.DefaultView
        DataGridView1.SelectionMode = DataGridViewSelectionMode.FullRowSelect

        
    End Sub

Answer 2

Thx for ur cute & quick reply & also 4 ur suggestion of the forum use actually I was new to forum and to VB.Net 2008 
that is my first project that why many error has occur from me. A cute fact that younger will learn from their elder. 

Now again come to the topic, I have three fields on the front end. Of which two are type text and one combobox. 

   VisitorCode 	typefield	comboBox
   VisitorName 	typefield	Textbox
   VehicleNo 	typefield	Textbox

When I click the combobox , it will display all VisitorCode record present. Then of which I take a selection of VisitorCode
as a result it show its specific two record i.e. VisitorName & VehicleNo.

Further when I click the btnAdd button it generate a new VisitorCode on comboBox field and the data for my other two field
of nature textfield are manually enterd. For example.

VisitorCode 	50
VisitorName 	abc
VehicleNo		xyz250

Help..Visitor Code=10,20,30,40 are present on combobox suppose. Means when user click the Add button then it generate 
the value 50, and for next time it generate 60. etc.

When I click the save button all the three field are stored in the database upto now I had not write a code for that bc when
my first problem of Add button will handle then I move forward.

Answer 3

Hi,

In my idea you simply cannot set a content of an autoincrement ID yourself.

If you want to do it like you even if it is not an autoincrement ID, then you become in big problems in multi user environments, and that is where all managed program languages are build for currently.

---

Success
Cor

Answer 4

@Cor,
but isn't that the beauty of an AutoIncrement column in a datatable? you don't have to set the content yourself. You have more experience with working with databases than I do - do you know of any problems having an autoincrement column? I know when you set up a datatable in the database in VS2008, you can set the column to autoincrement.


@Irfan
try this one in a new app. Need 1 DataGridView, 1 button, 1 combobox and 2 textboxes

you'll see that when you click the button to add the data from textbox1 and textbox2, the new row is incremented correctly. You are only adding data to columns 2 and 3 (look in the button_click event). Your first value is Nothing, since that number is added automatically. The new number also shows in the combobox because you are adding the new data to the datatable, so it shows on both the datagridview and combobox.

As far as saving it to the database, I don't have enough experience to help with that. Cor or someone else should be able to help you.

If you don't want an autoincrement column, then you can set a golbal variable right below Public Class frmVisitor. Something like Dim counter As Integer.
Then somewhere after you pull the data from the database, you'll have to get the value of the last row and add 10 to it. Then everythime you Add data to the table (in the btnAdd event), you can add 10 to it there.

    Dim dtTest As New DataTable
    Dim dsTest As New DataSet
   
    Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        dtTest = dsTest.Tables.Add("dtTest")
        dtTest.Columns.Add("myID", GetType(Integer))
        For i As Integer = 1 To 2
            dtTest.Columns.Add("Column" & i.ToString)
        Next
        dtTest.Columns(2).DataType = GetType(Integer)

        dtTest.Columns(0).AutoIncrement = True
        dtTest.Columns(0).AutoIncrementSeed = 10
        dtTest.Columns(0).AutoIncrementStep = 10

        Dim rnd As New Random
        For r As Integer = 1 To 10
            Dim value1 As String = "Row" & r.ToString
            Dim value2 As Integer = rnd.Next(0, 101)
            dtTest.Rows.Add(Nothing, value1, value2)
        Next

        DataGridView1.DataSource = dtTest.DefaultView
        DataGridView1.SelectionMode = DataGridViewSelectionMode.FullRowSelect
        ComboBox1.DataSource = dtTest.DefaultView
        ComboBox1.DisplayMember = "myID"
        ComboBox1.ValueMember = "Column1"
        AddHandler ComboBox1.SelectedIndexChanged, AddressOf ComboBox1_SelectedIndexChanged
    End Sub

    Private Sub ComboBox1_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) 
        MessageBox.Show(ComboBox1.SelectedValue.ToString)
    End Sub

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        If TextBox1.Text <> "" AndAlso TextBox2.Text <> "" Then
            dtTest.Rows.Add(Nothing, TextBox1.Text, TextBox2.Text)
        End If
    End Sub

Answer 5

jwavila,

In my expirience (I did not try it again) is the autoincrement property only for the datatable.

If you set that to -1 and the seed also to -1 then you wont become in trouble with existing keys.

After updating it is replaces by the the real given key (if you use the with the strongly typed dataset cq datatables generated update code or do that yourself in that code). I never tested this behaviour with the commandbuilder.

Because it has to be set to 10 does the GUID as well not help.

Altouhg in this case I would use a Guid for the database an and beside that a logical key, which has no function as database key.

---

Success
Cor

Answer 6

intNewVisitorID = drNewRow.Item("VisitorCode") + 10

when i run the program the process will stop at the above line and display the following error

Operator '+' is not defined for type 'DBNull' and type 'Integer'.

but already there is a data in the database but such error. i had done it in Vb6 that works fine with the following code.

 

Private Sub cboArea_Click()

Set rscboArea = New ADODB.Recordset
With rscboArea
.CursorLocation = adUseClient
.CursorType = adOpenDynamic
.LockType = adLockOptimistic
.ActiveConnection = db
.Source = ("SELECT * FROM Area WHERE AreaCode= " & Me.cboArea.Text &"")
.Open

If rscboArea.BOF And rscboArea.EOF Then
Me.lblRecNbr.Caption = " 0 of 0 "
MsgBox "No Record Was Found", vbOKOnly + vbInformation
Else

 

Me.cboArea.Text = rscboArea.Fields("AreaCode")
Me.txtAreaName.Text = rscboArea.Fields("AreaName")
Me.txtSalesMan.Text = rscboArea.Fields("SalesMan")
Me.lblRecNbr.Caption = CStr(cboArea.ListIndex + 1) & " of " & rsArea.RecordCount

End If
End With
rscboArea.Close
Set rscboArea = Nothing
End Sub

 

Private Sub cmdAddNew_Click()

Set rsAdd = New ADODB.Recordset

With rsAdd
.CursorLocation = adUseClient
.CursorType = adOpenDynamic
.LockType = adLockOptimistic
.ActiveConnection = db
.Source = "SELECT * FROM AREA"
.Open

If rsAdd.BOF And rsAdd.EOF Then
intIDForNewRecord = 10 'Initial value of AreaCode
rsAdd.AddNew 'Add a new AreaCode in our record
rsAdd("AreaCode") = intIDForNewRecord
Me.cboArea.AddItem rsAdd("AreaCode")

Else

rsAdd.MoveLast
intIDForNewRecord = rsAdd(

"AreaCode") + 10 'AreaCode increase by 10
rsAdd.AddNew 'Add a new AreaCode in our record
rsAdd("AreaCode") = intIDForNewRecord
Me.cboArea.AddItem rsAdd("AreaCode") 'To Insert new AreaCode to Combobox In case of New Entry.

End If
End With
Me.cboArea.Text = intIDForNewRecord
Me.txtAreaName.SetFocus

Me.txtAreaName.Text = "" ' Clear all the previous fields to accept further data
Me.txtSalesMan.Text = ""
Me.lblRecNbr.Caption = "Add operation"
' rsAdd.Close 'Now Close and Clean up
Set rsAdd = Nothing
AddNewCmd Me
cmdDisable Me
Me.cmdExit.Enabled = False

End Sub

 

I want to done it perfectly in Vb.Net2008 but sometime a little error would not detect by a mind

Answer 7

 A little more help of ur example. i seted it for himself as, (and at the end see help section)

Public Class frmVisitor

 

    Private dbConnection As New OleDb.OleDbConnection()

    Private daVisitor As OleDb.OleDbDataAdapter

    Private cmdbVisitor As OleDb.OleDbCommandBuilder

    Private dtVisitor As New DataTable

    Private rpVisitor As Integer = 0

    Dim intNewVisitorID As Integer

Private Sub frmVisitor_FormClosed(ByVal sender As Object, ByVal e As System.Windows.Forms.FormClosedEventArgs) Handles Me.FormClosed

 

        dbConnection.Close()

        dbConnection.Dispose()

End Sub

 

Private Sub frmVisitor_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

 

        dbConnection.ConnectionString = _

        "Provider=Microsoft.Jet.OLEDB.4.0;Data Source =SNGPLDbase.mdb"

        dbConnection.Open()

daVisitor = New OleDb.OleDbDataAdapter("Select * From Visitor",     dbConnection)

        cmdbVisitor = New OleDb.OleDbCommandBuilder(daVisitor)

 

        daVisitor.Fill(dtVisitor)

        Dim ds As New DataSet

        Dim drNewRow As DataRow = dtVisitor.NewRow()

 

 

        dtVisitor.Columns(2).DataType = GetType(String)

        dtVisitor.Columns(0).AutoIncrement = True

        dtVisitor.Columns(0).AutoIncrementSeed = 10

        dtVisitor.Columns(0).AutoIncrementStep = 10

 

        dtVisitor.Rows.Add()

       

        DataGridView1.Refresh()

        DataGridView1.DataSource = dtVisitor.DefaultView

DataGridView1.SelectionMode = DataGridViewSelectionMode.FullRowSelect

 

        cboVisitorCode.DataSource = dtVisitor.DefaultView

        cboVisitorCode.DisplayMember = "VisitorCode"

        AddHandler cboVisitorCode.SelectedIndexChanged, AddressOf cboVisitorCode_SelectedIndexChanged

 

    End Sub

Private Sub btnAdd_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnAdd.Click

 

        Me.txtVisitorName.Text = ""

        Me.txtVehicleNo.Text = ""

End Sub

Private Sub btnSave_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSave.Click

       

If txtVisitorName.Text <> "" AndAlso txtVehicleNo.Text <> "" Then

   dtVisitor.Rows.Add(Nothing, txtVisitorName.Text, txtVehicleNo.Text)

      End If

End Sub

End Class

‘Help this section

‘ Code for

‘ put the selected row fields values in textbox from datagridview ‘curent slected row

‘ such as 10 --à visitorcode

‘         abc--à visitor 10 name in Me.txtVisitorName.Text

‘         r123--àvisitor 10 vehicle no in Me.txtVehicleNo.Text

 

‘     Me.cboVisitorCode.text= 10 from datagridview

‘     Me.txtVisitorName.Text= abc from datadridview

‘     Me.txtVehicleNo.Text= r123 from datadridview

‘And the same thing in case from combo box selected visitor code

 

Answer 8

jwavila

Thanks for your help. The example you gave above was precisely what I wanted to do and with your help I finally manageed to get it to work excatly the way I wanted it to work via the use of dtagridview. Thanks a lot for your help. One other thing I may wish to ask you, is that how can I now

‘ Code for

‘ put the selected row fields values in textbox from datagridview ‘curent slected row

‘ such as 10 --à visitorcode

‘         abc--à visitor 10 name in Me.txtVisitorName.Text

‘         r123--àvisitor 10 vehicle no in Me.txtVehicleNo.Text

 

‘     Me.cboVisitorCode.text= 10 from datagridview

‘     Me.txtVisitorName.Text= abc from datadridview

‘     Me.txtVehicleNo.Text= r123 from datadridview

‘And the same thing in case from combo box selected visitor code

Irfan

Answer 9

IrfanKhan
  glad to hear you've gotten that part figured out. Sounds like you are moving along. Sorry it took a while to get back to you.

one way is to add a DataBinding to the TextBoxes. You could put this right below where you set the DataSource for the cboVisitorCode in the Form Load event (below the AddHandler line).

because you have the ComboBox and DataGridView set to the same DataSource (dtTest), selecting either a row on the DGV or an item in the ComboBox, it will show the appropriate values in the TextBoxes. Using my example above, where the last 2 columns are named "Column1" and "Column2", this will display the values from those in the corresponding TextBoxes.

        TextBox1.DataBindings.Add("Text", dtTest.DefaultView, "Column1")
        TextBox2.DataBindings.Add("Text", dtTest.DefaultView, "Column2")

Hope this helps with that problem

Please remember to mark any (can be more than 1) posts as Answer if they provided the answer to a problem, and mark as Helpful any posts that provided helpful information or explained something to help you solve the problem.