none
auto increment number RRS feed

  • Question

  • dear all

    i have a textbox name serial number. what i need is when user press enter to add textboxes data to datagrid,that textbox must keep increment the number and when user save it and reopen or again open it, it must start from last saved number. this textbox i linked to sql database . 

    example 

    1

    2

    data saved

    new entry

    5

    6

    data saved

    i need something like this example

    Thursday, May 30, 2019 6:30 AM

Answers

All replies

  • Hi,

    You can create a .ini file,

    Imports System.Runtime.InteropServices
    Imports System.Text
    
    Public Class Form1
        <DllImport("kernel32")>
        Private Shared Function WritePrivateProfileString(ByVal section As String, ByVal key As String, ByVal val As String, ByVal filePath As String) As Long
        End Function
        <DllImport("kernel32")>
        Private Shared Function GetPrivateProfileString(ByVal section As String, ByVal key As String, ByVal defVal As String, ByVal retVal As StringBuilder, ByVal size As Integer, ByVal filePath As String) As Integer
        End Function
    
        Private Sub TextBox1_KeyPress(sender As Object, e As KeyPressEventArgs) Handles TextBox1.KeyPress
            If e.KeyChar = ChrW(13) Then
                TextBox1.Text = (CInt(TextBox1.Text) + 1).ToString
            End If
        End Sub
    
        Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
            Dim temp As StringBuilder = New StringBuilder(500)
            Dim i As Integer = GetPrivateProfileString("Serial Number", "Id", "", temp, 500, "D:\test.ini")
            TextBox1.Text = temp.ToString()
        End Sub
    
        Private Sub Form1_FormClosed(sender As Object, e As FormClosedEventArgs) Handles MyBase.FormClosed
            WritePrivateProfileString("Serial Number", "Id", TextBox1.Text, "D:\test.ini")
        End Sub
    End Class
    

    ini file:

    [Serial Number]
    Id=1
    

    Best Regards,

    Alex


    MSDN Community Support Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Thursday, May 30, 2019 7:28 AM

  • i have a textbox name serial number. what i need is when user press enter to add textboxes data to datagrid,that textbox must keep increment the number and when user save it and reopen or again open it, it must start from last saved number. this textbox 

    You can use the project Settings to persist (save/restore) the value automatically.
    It can be set up with very little additional programming needed.

    See the following references:

    Walkthrough: Persisting an Object in Visual Studio (Visual Basic)
    https://docs.microsoft.com/en-us/dotnet/visual-basic/programming-guide/concepts/serialization/walkthrough-persisting-an-object-in-visual-studio

    How to: Persist User Settings in Visual Basic
    https://docs.microsoft.com/en-us/dotnet/visual-basic/developing-apps/programming/app-settings/how-to-persist-user-settings

    My.Settings Object
    https://docs.microsoft.com/en-us/dotnet/visual-basic/language-reference/objects/my-settings-object

    Manage application settings (.NET)
    https://docs.microsoft.com/en-us/visualstudio/ide/managing-application-settings-dotnet?view=vs-2019

    - Wayne

    Thursday, May 30, 2019 8:50 AM
  • Thank you for your reply

    Alex can you please check below code. i am getting error 

    error

    The parameterized query '(@d1 nvarchar(4000),@d2 nvarchar(4000),@d3 nvarchar(4000),@d4 nv' expects the parameter '@d1', which was not supplied.

            

      con = New SqlConnection(appx.myconnection)
            con.Open()
            Dim cb As String = "insert into cashpayment(cashpaymentid,date,coa_id2,paymentfrom,coa_id,paymentto,amountdr,amountcr,remarks) Values (@d12,@d13,@d14,@d15,@d16,@d17,@d18,@d19,@d20)"
            cmd = New SqlCommand(cb)
            cmd.Parameters.AddWithValue("@d12", CashPaymentidTextBox.Text)
            cmd.Parameters.AddWithValue("@d13", DateDateTimePicker.Value)
            cmd.Parameters.AddWithValue("@d14", Coa_id2TextBox.Text)
            cmd.Parameters.AddWithValue("@d15", PaymentfromTextBox.Text)
            cmd.Parameters.AddWithValue("@d16", Coa_idTextBox.Text)
            cmd.Parameters.AddWithValue("@d17", PaymenttoTextBox.Text)
            cmd.Parameters.AddWithValue("@d18", AmountdrTextBox.Text)
            cmd.Parameters.AddWithValue("@d19", AmountcrTextBox.Text)
            cmd.Parameters.AddWithValue("@d20", RemarksTextBox.Text)
            cmd.Connection = con
            cmd.ExecuteReader()
            con.Close()

            con = New SqlConnection(appx.myconnection)
            con.Open()
            Dim cb1 As String = "insert into cashpayment_join(cpvidjoin,cashpaymentid,date,paymentto,paymentfrom,dr,cr,description,total,userid) VALUES (@d1,@d2,@d3,@d4,@d5,@d6,@d7,@d8,@d9,@d10)"
            cmd = New SqlCommand(cb1)
            cmd.Connection = con
            ' Prepare command for repeated execution
            cmd.Prepare()
            ' Data to be inserted
            For Each row As DataGridViewRow In CashpaymentDataGridView.Rows



                If Not row.IsNewRow Then

                    cmd.Parameters.AddWithValue("@d1", row.Cells(1).Value)  'cpvidjoin
                    cmd.Parameters.AddWithValue("@d2", row.Cells(2).Value) 'cashpaymentid
                    cmd.Parameters.AddWithValue("@d3", row.Cells(3).Value)  'date
                    cmd.Parameters.AddWithValue("@d4", row.Cells(4).Value)  'paymentto
                    cmd.Parameters.AddWithValue("@d5", row.Cells(5).Value)  'paymentfrom
                    cmd.Parameters.AddWithValue("@d6", row.Cells(6).Value)  'dr
                    cmd.Parameters.AddWithValue("@d7", row.Cells(7).Value)  'cr
                    cmd.Parameters.AddWithValue("@d8", row.Cells(8).Value)  'description
                    cmd.Parameters.AddWithValue("@d9", row.Cells(9).Value)  'total
                    cmd.Parameters.AddWithValue("@d10", row.Cells(10).Value) 'userid
                    cmd.ExecuteNonQuery()
                    cmd.Parameters.Clear()
                End If
            Next
            con.Close()


            Using conn = New SqlConnection(appx.myconnection)
                conn.Open()
                cmd = New SqlCommand("UPDATE COA SET opening_balance = opening_balance + @1  WHERE AccountID = '" & Coa_idTextBox.Text & "'", conn)
                cmd = New SqlCommand("UPDATE COA SET opening_balance = opening_balance - @2  WHERE AccountID = '" & Coa_id2TextBox.Text & "'", conn)

                cmd.Parameters.AddWithValue("@1", CInt(TotalamountTextBox.Text))
                cmd.Parameters.AddWithValue("@2", CInt(TotalamountTextBox.Text))
                cmd.ExecuteNonQuery()
            End Using






            '   updateQuery = "UPDATE COA SET opening_balance = opening_balance + '" & TotalamountTextBox.Text & "'  WHERE AccountID = '" & Coa_idTextBox.Text & "'"

            '  updateQuery1 = "UPDATE COA SET opening_balance = opening_balance - " & TotalamountTextBox.Text & "  WHERE accountID = '" & Coa_id2TextBox.Text & "'"


            '  cudfunctionNOmsg(updateQuery)
            '  cudfunctionNOmsg(updateQuery1)

            'END
            'SUCCESS MSH

            '   LBLMSG.Text = "The " & TXTPRODUCT.Text & " has been added into the inventory."
            '  LBLMSG.BackColor = Color.Aquamarine
            ' LBLMSG.ForeColor = Color.Black
            MsgBox("The " & PaymenttoTextBox.Text & " has been added into the inventory.")
            'update autonumber
            '   updateAutoNumber(1)
            '    Call AddNewToolStripMenuItem_Click(sender, e)

            '    MsgBox("Fill up the correct product in the empty fields inorder to save.")
            '  LBLMSG.BackColor = Color.Red
            '  LBLMSG.ForeColor = Color.White
            Beep()




            CashPaymentidTextBox.Text = ""

            CashpaymentDataGridView.DataSource = Nothing

    Thursday, May 30, 2019 9:12 AM
  • Thank you for the code but i need it to be done in sql server 
    Thursday, May 30, 2019 9:21 AM
  • Thank you for the code but i need it to be done in sql server 

    Are you sure you're in the right forum? This one is for issues related to
    Visual Basic (VB.Net). Perhaps one of the many forums for SQL Server would be
    a better choice.

    - Wayne

    Thursday, May 30, 2019 9:36 AM
  • wayne this is vb.net script and i normally post sql and vb.net scripts in vb.net 
    Thursday, May 30, 2019 9:40 AM


  •                 cmd.Parameters.AddWithValue("@d1", row.Cells(1).Value)  'cpvidjoin
                    cmd.Parameters.AddWithValue("@d2", row.Cells(2).Value) 'cashpaymentid
                    cmd.Parameters.AddWithValue("@d3", row.Cells(3).Value)  'date
                    cmd.Parameters.AddWithValue("@d4", row.Cells(4).Value)  'paymentto
                    cmd.Parameters.AddWithValue("@d5", row.Cells(5).Value)  'paymentfrom
                    cmd.Parameters.AddWithValue("@d6", row.Cells(6).Value)  'dr
                    cmd.Parameters.AddWithValue("@d7", row.Cells(7).Value)  'cr
                    cmd.Parameters.AddWithValue("@d8", row.Cells(8).Value)  'description
                    cmd.Parameters.AddWithValue("@d9", row.Cells(9).Value)  'total
                    cmd.Parameters.AddWithValue("@d10", row.Cells(10).Value) 'userid
                     

    Hi,

    see the following link,then fix your code:

    https://stackoverflow.com/questions/23448403/the-parameterized-query-expects-the-parameter-units-which-was-not-supp


    Best Regards,

    Alex


    MSDN Community Support Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Thursday, May 30, 2019 9:46 AM
  • i fix it already alex but i have one more issue 

    i am working on "cash payment" module 

    and here i am entering 5 accounts which will be debit after i save it. now i am using below code that will update all accounts at ones but can you check what mistake im doing becz this code is not working it saving the record but not hitting the accounts 

            For Each row As DataGridViewRow In CashpaymentDataGridView.Rows
                    If Not row.IsNewRow Then
                        con = New SqlConnection(appx.myconnection)
                        con.Open()
                        Dim ctx As String = "select Account_Title from COA where Account_Title=@d1 and Account_Title=@d2"
                        cmd = New SqlCommand(ctx)
                        cmd.Connection = con
                        cmd.Parameters.AddWithValue("@d1", row.Cells(3).Value)
                        cmd.Parameters.AddWithValue("@d2", row.Cells(5).Value)
                        rdr = cmd.ExecuteReader()
                        If (rdr.Read()) Then
    
                            con = New SqlConnection(appx.myconnection)
                            con.Open()
                            Dim cb2 As String = "Update COA set opening_balance = opening_balance + " & row.Cells(3).Value & " where AccountID=@d1"
                            Dim cb3 As String = "Update COA set opening_balance = opening_balance - " & row.Cells(5).Value & " where AccountID=@d2"
                            cmd = New SqlCommand(cb2 And cb3)
                            cmd.Connection = con
                            cmd.Parameters.AddWithValue("@d1", (row.Cells(3).Value))
                            cmd.Parameters.AddWithValue("@d2", row.Cells(5).Value)
                            cmd.ExecuteReader()
                            con.Close()
                        End If
                    End If

    Thursday, May 30, 2019 10:49 AM
  • Omi,

    You do this in a very strange way. 

    Normally if this is a Microsoft database it is simply done in the database table. 

    You select the column, make the column integer, click right and set Primary key

    Then in the properties you go to the Ident properties, there you can specify how the icrement is done. 


    Success
    Cor

    Thursday, May 30, 2019 11:39 AM
  • Actually i know that method but i want it to be increment when user add data to datagridview i am using ms sql 

    Thursday, May 30, 2019 11:42 AM
  • i fix it already alex but i have one more issue 

    i am working on "cash payment" module 

    and here i am entering 5 accounts which will be debit after i save it. now i am using below code that will update all accounts at ones but can you check what mistake im doing becz this code is not working it saving the record but not hitting the accounts 

        For Each row As DataGridViewRow In CashpaymentDataGridView.Rows
                    If Not row.IsNewRow Then
                        con = New SqlConnection(appx.myconnection)
                        con.Open()
                        Dim ctx As String = "select Account_Title from COA where Account_Title=@d1 and Account_Title=@d2"
                        cmd = New SqlCommand(ctx)
                        cmd.Connection = con
                        cmd.Parameters.AddWithValue("@d1", row.Cells(3).Value)
                        cmd.Parameters.AddWithValue("@d2", row.Cells(5).Value)
                        rdr = cmd.ExecuteReader()
                        If (rdr.Read()) Then
    
                            con = New SqlConnection(appx.myconnection)
                            con.Open()
                            Dim cb2 As String = "Update COA set opening_balance = opening_balance + " & row.Cells(3).Value & " where AccountID=@d1"
                            Dim cb3 As String = "Update COA set opening_balance = opening_balance - " & row.Cells(5).Value & " where AccountID=@d2"
                            cmd = New SqlCommand(cb2 And cb3)
                            cmd.Connection = con
                            cmd.Parameters.AddWithValue("@d1", (row.Cells(3).Value))
                            cmd.Parameters.AddWithValue("@d2", row.Cells(5).Value)
                            cmd.ExecuteReader()
                            con.Close()
                        End If
                    End If

    Thursday, May 30, 2019 12:36 PM
  • waiting for reply of above question (update record)... please im stuck at that point
    Friday, May 31, 2019 7:16 AM
  • Hi,

    If your issue is solved(auto increment number),please mark the useful replies as answers.If you have other problems, please open another thread to ask .

    Best Regards,

    Alex


    MSDN Community Support Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Friday, May 31, 2019 7:33 AM
  • ok alex done but my question still not answered regarding auto number

     i need that to be done in sql server . whenever user press enter the number must go to maximum number and when user add new record, it must show the next number of last saved number. 

    Friday, May 31, 2019 8:00 AM
  • waiting for answerr

    auto increment number

    Saturday, June 1, 2019 8:12 AM
  • i solve the problem 

    • Marked as answer by Omi4u Saturday, June 1, 2019 10:47 AM
    Saturday, June 1, 2019 10:47 AM