locked
Auto increment

    Question

  • Here is my little code.

    Dim refcode, Code As String

    Dim num As Integer = 0

    refcode = "sometexthere"

    Code = refcode +

    Date.Today.ToString("yyyyMMdd") + "-" + num.ToString

    Ok my concern is each time a row is added in the database, Check if the same "Code" exists. If so, then the "num" value increments. For the new day, the first raw added starts with "num = 0". Is anyone can help solve this issue. Any help will be very appreciated.


    • Edited by Rhyck Thursday, September 15, 2011 3:57 PM
    Thursday, September 15, 2011 3:48 PM

Answers

  • A better way to do this is to set your Database Column to auto increment.

    Which data base are you using (SQL\Access) ?

     

    Try counting the number of rows and increment in it.

    cmd.CommandText = "SELECT * FROM TABELNAME"
    da.SelectCommand = cmd
    da.Fill(ds, "TABELNAME")
    dt = ds.Tables("TABELNAME")
     
    MsgBox(dt.Rows.Count)
    


    I hope you know ho to work with SQL Query to connect to database.

     

    OR

     

    If you are using Visual studio IDE (ADO.NET) to connect to database then

    Datatable.Rows.Count()
    



    Please mark those posts as answer which answers your question. Faraz
    • Marked as answer by Rhyck Thursday, September 15, 2011 6:33 PM
    Thursday, September 15, 2011 4:05 PM
  • Leave the code try this

    In your SQL management studio right click table and select "Design" , Select your column and from its properties (Properties panel)

    Set identity to YES

    Identity increment and seed to 1 to have an increment of 1.

     


    Please mark those posts as answer which answers your question. Faraz
    • Marked as answer by Rhyck Friday, September 16, 2011 10:38 PM
    Friday, September 16, 2011 4:48 AM
  • make sure that you are not updating or changing any thing in the column which you set to auto increment.

    When you perform the auto increment steps above , you do not need to use "num" to make an increment.


    Please mark those posts as answer which answers your question. Faraz
    • Marked as answer by Rhyck Saturday, September 17, 2011 4:05 PM
    Saturday, September 17, 2011 4:05 AM
  • paste the complete code , what is the name and data type of your auto incremented column ?
    Please mark those posts as answer which answers your question. Faraz
    • Marked as answer by Rhyck Saturday, September 17, 2011 7:02 PM
    Saturday, September 17, 2011 4:34 PM
  • The insert query is not sutiable in your situation, we want to insert data in all columns except for one which is auto increment.

    Use the code below to insert data in your sql data base ( the code below is called sql parametrized query )

        Dim sql As String = "INSERT INTO [result] (column1, column2, column3) VALUES (@q1, @q2, @q3)" 'do not put the auto incremented column here
     
        Using cn As New SqlConnection("Your connection string here"), _
              cmd As New SqlCommand(sql, cn)
     
            cmd.Parameters.Add("@q1", SqlDbType.VarChar, 50).Value = q1.Text
            cmd.Parameters.Add("@q2", SqlDbType.VarChar, 50).Value = q2.Text 
            cmd.Parameters.Add("@q3", SqlDbType.VarChar, 50).Value = q3.Text
     
            cn.Open()
            cmd.ExecuteNonQuery()
        End Using  
    



    Please mark those posts as answer which answers your question. Faraz
    • Marked as answer by Rhyck Sunday, September 18, 2011 1:30 PM
    Sunday, September 18, 2011 7:27 AM
  • You should have said it before, i was thinking that you want to increment a column like (1,2,3,..)

     

    Search your data base to check if the same record exists

    dim intva as integer = 0
    Dim str as string = "wwwww/20110901-" & intva 
    
    Dim cmd As SqlCommand = new SqlCommand("SELECT * FROM TableName WHERE IdColums =" & str, connection)
    Dim reader As SqlDataReader = cmd.ExecuteReader()
    IF Not reader.HasRows Then
    'the data does not exist. increment in integer variable declared above
    Else
    'The record exists
    End IF
    



    Please mark those posts as answer which answers your question. Faraz
    • Marked as answer by Rhyck Monday, September 19, 2011 2:09 AM
    Sunday, September 18, 2011 2:44 PM
  • Yes, use do while loop

    dim intva as integer = 0
    Dim str as string = nothing
    dim check as boolean = false
    
    do while check =false
    
    str = "wwwww/20110901-" & intva 
    Dim cmd As SqlCommand = new SqlCommand("SELECT * FROM TableName WHERE IdColums =" & str, connection)
    Dim reader As SqlDataReader = cmd.ExecuteReader()
    IF Not reader.HasRows Then
    check = true
    Else
    'The record exists , do nothing
    End IF
    
    intva  += 1
    
    loop
    
    msgbox ("This record does not exists in data base = " & str)
    
    
    



    Please mark those posts as answer which answers your question. Faraz
    • Marked as answer by Rhyck Monday, September 19, 2011 2:34 PM
    Monday, September 19, 2011 4:43 AM
  • that is because you wrote the code to do so , follow the below pattern

     

    Check if the record with the same code exists

    if the record exists increment in the integer

    if the record does not exists insert a row

     

    Dim code As String
    Dim num As Integer = 0
    Dim check As Boolean = False
    
    do while check = false
    
    code = "wwf-capg/" + Date.Today.ToString("yyyyMMdd") + "-" + num.ToString
    
     Sqlquery1 = ("SELECT * FROM table code WHERE code = '" + code + "'")
    
    
           Cmd1 = New OleDbCommand(Sqlquery1, Myconn)
            Cmd1.ExecuteNonQuery()
            Dim adapter As New OleDbDataAdapter(Cmd1)
            adapter.Fill(table)
    
    
            If table.Rows.Count > 0 Then
            'increment in the integer because the same code exists
            num += 1
    
            Else
            'write the code here to insert in to the data base.
    
    Exit Do 'exit the do while loop after inserting the record
            End IF
    
    loop
    
    


    Please mark those posts as answer which answers your question. Faraz
    • Marked as answer by Rhyck Monday, September 19, 2011 9:26 PM
    Monday, September 19, 2011 5:11 PM
  • put the code in a try catch block if some error occurs it will capture it.

    'only this part of the code can produce an error.
    try
      Cmd1 = New OleDbCommand(Sqlquery1, Myconn)
            Cmd1.ExecuteNonQuery()
            Dim adapter As New OleDbDataAdapter(Cmd1)
            adapter.Fill(table)
    catch ex as exception
    msgbox (ex.message)
    end try
    


    If you have a lot of data in your database , the do while loop could take some time to finish (for testing purpose delete all the rows in your database and start your program again).


    Please mark those posts as answer which answers your question. Faraz
    • Marked as answer by Rhyck Tuesday, September 20, 2011 9:25 PM
    • Unmarked as answer by Rhyck Tuesday, September 20, 2011 9:25 PM
    • Marked as answer by Rhyck Tuesday, September 20, 2011 9:25 PM
    Tuesday, September 20, 2011 3:40 AM
  • please paste the complete code of this form
    Please mark those posts as answer which answers your question. Faraz
    • Marked as answer by Rhyck Wednesday, September 21, 2011 3:56 PM
    Wednesday, September 21, 2011 7:06 AM
  • The error in these two lines

     code = "wwf-capg/" + Date.Today.ToString("yyyyMMdd") + "-"
            code = code + "" & num

    Do it like this

     

      code = "wwf-capg/" & Date.Today.ToString("yyyyMMdd") & "-" & num
            
    

     


    The code which you used will give you out put like (on second increment)

    wwf-capg/Date.Today.ToString20111231-1wwf-capg/Date.Today.ToString20111231-2

     

    Below is only for your information do not use it in your code

    Here you violated very basic law of programing

     code = code + "" & num
    

    You can not add a integer value in to a string , if you need to combine them use "&" operator instead of "+"


    Please mark those posts as answer which answers your question. Faraz
    • Marked as answer by Rhyck Wednesday, September 21, 2011 6:10 PM
    Wednesday, September 21, 2011 4:33 PM
  • Sqlquery2 = ("INSERT INTO gorhyck VALUES('" + code + "')")
                    Cmd2 = New OleDbCommand(Sqlquery2, Myconn)
                    Cmd2.ExecuteNonQuery()
                    chk = true ' make chk true to stop the do while counter
    '                Exit Do
                End If
            Loop
    


    Please mark those posts as answer which answers your question. Faraz
    • Marked as answer by Rhyck Thursday, September 22, 2011 4:02 PM
    Thursday, September 22, 2011 7:35 AM
  • Can you upload your form with this data base file.

    Use skydrive or any other uploading facility.


    Please mark those posts as answer which answers your question. Faraz
    • Marked as answer by Rhyck Monday, September 26, 2011 4:48 AM
    Thursday, September 22, 2011 4:40 PM
  • sorry for being so late,

    If you have a database in *.MDF format, please upload it i do not have a SQL management studio installed on my current system and i do not want to install it due to some testing purpose.


    Please mark those posts as answer which answers your question. Faraz
    • Marked as answer by Rhyck Wednesday, September 28, 2011 2:44 AM
    Tuesday, September 27, 2011 3:07 PM
  • If table.rows.count is >0 at the start of the loop, it will always be >0. The condition for the loop to exit is that the number of rows in the tables is zero, but as you don't remove any rows, this never happens. Hence you get an overflow after 2 Giga iterations. That's the maximum Integer value.
    Armin
    • Marked as answer by Rhyck Wednesday, September 28, 2011 5:12 PM
    Wednesday, September 28, 2011 4:29 AM
  • Ok, Then what to do? what is your proposition?

    I was only looking at the small code part from the screenshot. I'm afraid, I don't understand what's the final goal.

    Armin
    • Marked as answer by Rhyck Wednesday, September 28, 2011 6:07 PM
    Wednesday, September 28, 2011 5:35 PM
  • I think I see now. You just want to get the maximum running number from the same day in the database, then increment it by one to write a new record, right?

       Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
    
          Dim Myconn As OleDbConnection
          Dim Cmd1, Cmd2 As OleDbCommand
          Dim Sqlquery1, Sqlquery2 As String
          Dim NewNum As Integer
          Dim codeBase, NewCode As String
          Dim maxValue As Object
    
          Myconn = New OleDbConnection("Provider=SQLOLEDB;Data Source=RHYCK-PC\SQLEXPRESS;Integrated Security=SSPI;Initial Catalog=rhyckincrement")
          Myconn.Open()
    
          'Retrieve last value
          codeBase = "wwf-capg/" & Date.Today.ToString("yyyyMMdd") & "-"
          Sqlquery1 = "SELECT max(code) FROM gorhyck WHERE code LIKE ?"
    
          Cmd1 = New OleDbCommand(Sqlquery1, Myconn)
          Cmd1.Parameters.AddWithValue("code", codeBase & "%")
    
          maxValue = Cmd1.ExecuteScalar
    
          'Calc new value
          If maxValue Is Nothing Then
             NewNum = 0
          Else
             Dim LastCode = maxValue.ToString
             Dim LastNum = Integer.Parse(LastCode.Substring(codeBase.Length))
    
             NewNum = LastNum + 1
          End If
    
          NewCode = codeBase & NewNum
    
          'Write new record
          Sqlquery2 = "INSERT INTO gorhyck (code) VALUES(?)"
    
          Cmd2 = New OleDbCommand(Sqlquery2, Myconn)
          Cmd2.Parameters.AddWithValue("code", NewCode)
          Cmd2.ExecuteNonQuery()
    
          Myconn.Close()
    
          Label1.Text = "Data Sent !"
    
       End Sub
    

    Is the database in a multi-user environment? Then we'd have to modify it.

    I don't know what you want to do with the DataTable and DataAdapter.

    I was not able to test it, but I hope this is what you are looking for.


    Armin
    • Marked as answer by Rhyck Wednesday, September 28, 2011 7:31 PM
    Wednesday, September 28, 2011 7:08 PM
  • Thank you Andrew for your help and time.

    I exactly repeated your code and got the same results but the only difference is that the field CodeSequence does not increment!!!,

    Also, you've probably already done it, but if you do want the sequence number to be for the date regardless of the CodeText, you can change the SQL from

    "INSERT INTO EtcStore (CodeText, CodeDate, CodeSequence, etc) values (@CodeText, @CodeDate, (SELECT COALESCE(MAX(CodeSequence),-1)+1 FROM EtcStore WHERE CodeDate=@CodeDate AND CodeText=@CodeText), @etc)"

    to

    "INSERT INTO EtcStore (CodeText, CodeDate, CodeSequence, etc) values (@CodeText, @CodeDate, (SELECT COALESCE(MAX(CodeSequence),-1)+1 FROM EtcStore WHERE CodeDate=@CodeDate), @etc)"

    HTH,

    Andrew

    • Marked as answer by Rhyck Saturday, October 15, 2011 12:42 AM
    Thursday, October 13, 2011 6:05 PM

All replies

  • A better way to do this is to set your Database Column to auto increment.

    Which data base are you using (SQL\Access) ?

     

    Try counting the number of rows and increment in it.

    cmd.CommandText = "SELECT * FROM TABELNAME"
    da.SelectCommand = cmd
    da.Fill(ds, "TABELNAME")
    dt = ds.Tables("TABELNAME")
     
    MsgBox(dt.Rows.Count)
    


    I hope you know ho to work with SQL Query to connect to database.

     

    OR

     

    If you are using Visual studio IDE (ADO.NET) to connect to database then

    Datatable.Rows.Count()
    



    Please mark those posts as answer which answers your question. Faraz
    • Marked as answer by Rhyck Thursday, September 15, 2011 6:33 PM
    Thursday, September 15, 2011 4:05 PM
  • I am using SQL Server 2008

    And anytime I use Rows.Count() I have this error message: "Rows is not a member of String".

    here is what I did.

    Dim Sqlquery As String = ("SELECT * FROM Table Code WHERE Code = '"+ Code +"'")

    MyConn.Open()

    Cmd.ExecuteNonQuery()

    If Sqlquery.Rows.Count() > 0 Then        ---------> This doesn't work !!!!

    num++

    etc.

     


    Using the code you provided above, giving me some undeclared variables errors (da, dt, ts) Are these in the system or what?

    Also, is it possible to set to increment a variable containing String and Integer in Sql Table column ?

    Anyway, I would prefer the second option (counting the number of rows in Sql query). But how to do that in VB?

     

    • Edited by Rhyck Thursday, September 15, 2011 6:56 PM
    Thursday, September 15, 2011 6:34 PM
  • Leave the code try this

    In your SQL management studio right click table and select "Design" , Select your column and from its properties (Properties panel)

    Set identity to YES

    Identity increment and seed to 1 to have an increment of 1.

     


    Please mark those posts as answer which answers your question. Faraz
    • Marked as answer by Rhyck Friday, September 16, 2011 10:38 PM
    Friday, September 16, 2011 4:48 AM
  • I tried that and here is the error message:

    I could not find the "IDENTITY_INSERT" in Column properties.

    I believe it because of the type of Data which is an integer and String. Here is the Data to be incremented:

    Dim code As String
    Dim num As Integer = 0
    
    code = "sometext/"+ Date.Today.ToString("yyyyMMdd") + "-" + num.ToString 
    

     

    I only need the number "num" to be incremented.

    And here is my Sql Table:

    In order to be automatically incremented the code column needs to be set to Integer as type of Data. I am not sure about that but I tried other type didn't work.

    I am really stuck. Please Help!!!!

     

     


    • Edited by Rhyck Friday, September 16, 2011 11:09 PM
    Friday, September 16, 2011 11:07 PM
  • make sure that you are not updating or changing any thing in the column which you set to auto increment.

    When you perform the auto increment steps above , you do not need to use "num" to make an increment.


    Please mark those posts as answer which answers your question. Faraz
    • Marked as answer by Rhyck Saturday, September 17, 2011 4:05 PM
    Saturday, September 17, 2011 4:05 AM
  • Tried still getting the same error without "num"

    Dim code As String
    
    code = "sometext/" + Date.Today.ToString("yyyyMMdd") + "-"
    

    Error message:

    Where can I Set the "IDENTITY_INSERT" to ON ?

    Saturday, September 17, 2011 4:14 PM
  • paste the complete code , what is the name and data type of your auto incremented column ?
    Please mark those posts as answer which answers your question. Faraz
    • Marked as answer by Rhyck Saturday, September 17, 2011 7:02 PM
    Saturday, September 17, 2011 4:34 PM
  • The name is "code" and data type = Int As shown above

    here is the complete code:

    Dim code, Sqlquery As String
    Dim num As Integer = 0
    Dim Myconn As OleDbConnection
    Dim Cmd As OleDbCommand
    
    Myconn = New OleDbConnection("....")
    Myconn.Open()
    
    code = "sometext/" + Date.Today.ToString("yyyyMMdd") + "-" + num.ToString
    
    Sqlquery = ("INSERT INTO table VALUES('"+ code +"' , '"+ etc +"') ")
    
    Cmd = New OleDbCommand(Sqlquery, Myconn)
    
    Cmd.ExecuteNonQuery()
    
    


    • Edited by Rhyck Saturday, September 17, 2011 10:08 PM
    Saturday, September 17, 2011 7:00 PM
  • The insert query is not sutiable in your situation, we want to insert data in all columns except for one which is auto increment.

    Use the code below to insert data in your sql data base ( the code below is called sql parametrized query )

        Dim sql As String = "INSERT INTO [result] (column1, column2, column3) VALUES (@q1, @q2, @q3)" 'do not put the auto incremented column here
     
        Using cn As New SqlConnection("Your connection string here"), _
              cmd As New SqlCommand(sql, cn)
     
            cmd.Parameters.Add("@q1", SqlDbType.VarChar, 50).Value = q1.Text
            cmd.Parameters.Add("@q2", SqlDbType.VarChar, 50).Value = q2.Text 
            cmd.Parameters.Add("@q3", SqlDbType.VarChar, 50).Value = q3.Text
     
            cn.Open()
            cmd.ExecuteNonQuery()
        End Using  
    



    Please mark those posts as answer which answers your question. Faraz
    • Marked as answer by Rhyck Sunday, September 18, 2011 1:30 PM
    Sunday, September 18, 2011 7:27 AM
  • I can insert data in my table that is not the problem.

    what I want is to autoincrement the fisrt column with the variable inside.

    This is the data I am sending in that column: wwwww/20110901-0 

    So each time a row is added, the new row just added will have as code:  wwwww/20110901-1  and so on...

    So rather then having an autoincrement column, I would like the code to autoincrement the last number.

    This is what I exactly want:

    when a button had been clicked, check if this code:  wwwww/20110901-0  exists. if so, Then Increment the last number.

    Sunday, September 18, 2011 1:47 PM
  • You should have said it before, i was thinking that you want to increment a column like (1,2,3,..)

     

    Search your data base to check if the same record exists

    dim intva as integer = 0
    Dim str as string = "wwwww/20110901-" & intva 
    
    Dim cmd As SqlCommand = new SqlCommand("SELECT * FROM TableName WHERE IdColums =" & str, connection)
    Dim reader As SqlDataReader = cmd.ExecuteReader()
    IF Not reader.HasRows Then
    'the data does not exist. increment in integer variable declared above
    Else
    'The record exists
    End IF
    



    Please mark those posts as answer which answers your question. Faraz
    • Marked as answer by Rhyck Monday, September 19, 2011 2:09 AM
    Sunday, September 18, 2011 2:44 PM
  • Thank you Faraz, it worked.

    but there is a hic. it just incremented one time. the initial code  "wwwww/20110901-" & intva  chenged from 0 to 1.

    So, when you send an other record, the intva doesn't increment anymore.

    Is there a way we can do a while loop, something like

    For intva = 0, intva < str, intva ++

    While intva < str, intva ++   ?

     

    Monday, September 19, 2011 2:25 AM
  • Yes, use do while loop

    dim intva as integer = 0
    Dim str as string = nothing
    dim check as boolean = false
    
    do while check =false
    
    str = "wwwww/20110901-" & intva 
    Dim cmd As SqlCommand = new SqlCommand("SELECT * FROM TableName WHERE IdColums =" & str, connection)
    Dim reader As SqlDataReader = cmd.ExecuteReader()
    IF Not reader.HasRows Then
    check = true
    Else
    'The record exists , do nothing
    End IF
    
    intva  += 1
    
    loop
    
    msgbox ("This record does not exists in data base = " & str)
    
    
    



    Please mark those posts as answer which answers your question. Faraz
    • Marked as answer by Rhyck Monday, September 19, 2011 2:34 PM
    Monday, September 19, 2011 4:43 AM
  • Thank you Faraz for your assistance, I really appreciated.

    It works but again with a little hic.

    Now it can't stop. Just filling out the table at just one click!

    here is what I mean:

    So, it does increment the last value which is good and what I need. But now its just doing that over and over with just one click!!!

    I just want it to do that just each time we click.

    Here is my entire script:

    Dim code As String
    Dim num As Integer = 0
    
    code = "wwf-capg/" + Date.Today.ToString("yyyyMMdd") + "-" + num.ToString 
    
    Sqlquery1 = ("SELECT * FROM table code WHERE code = '" + code + "'")
    
    
           Cmd1 = New OleDbCommand(Sqlquery1, Myconn)
            Cmd1.ExecuteNonQuery()
            Dim adapter As New OleDbDataAdapter(Cmd1)
            adapter.Fill(table)
            Dim check As Boolean = False
    
            If table.Rows.Count > 0 Then
                check = True
                Do While check = True
    
                    num += 1
                    code = "wwf-capg/" + Date.Today.ToString("yyyyMMdd") + "-" + num.ToString
    
                    Sqlquery2 = ("INSERT INTO table VALUES('" + code + "','" + LabelTitre + "','" + LabelAuteur + "')")
                    Cmd2 = New OleDbCommand(Sqlquery2, Myconn)
                    Cmd2.ExecuteNonQuery()
    
                Loop
    
            Else
                Sqlquery2 = ("INSERT INTO table VALUES('" + code + "','" + LabelTitre + "','" + LabelAuteur + "')")
                Cmd2 = New OleDbCommand(Sqlquery2, Myconn)
                Cmd2.ExecuteNonQuery()
            End If
    
    

    Monday, September 19, 2011 3:24 PM
  • that is because you wrote the code to do so , follow the below pattern

     

    Check if the record with the same code exists

    if the record exists increment in the integer

    if the record does not exists insert a row

     

    Dim code As String
    Dim num As Integer = 0
    Dim check As Boolean = False
    
    do while check = false
    
    code = "wwf-capg/" + Date.Today.ToString("yyyyMMdd") + "-" + num.ToString
    
     Sqlquery1 = ("SELECT * FROM table code WHERE code = '" + code + "'")
    
    
           Cmd1 = New OleDbCommand(Sqlquery1, Myconn)
            Cmd1.ExecuteNonQuery()
            Dim adapter As New OleDbDataAdapter(Cmd1)
            adapter.Fill(table)
    
    
            If table.Rows.Count > 0 Then
            'increment in the integer because the same code exists
            num += 1
    
            Else
            'write the code here to insert in to the data base.
    
    Exit Do 'exit the do while loop after inserting the record
            End IF
    
    loop
    
    


    Please mark those posts as answer which answers your question. Faraz
    • Marked as answer by Rhyck Monday, September 19, 2011 9:26 PM
    Monday, September 19, 2011 5:11 PM
  • Well, I tried the exacte code you gave above,

    it inserted the first row very well, then when you click again in order to send a second row, the program gets stuck, it freezes.


    I currently have no idea whats going on with this increment code...

     

    • Edited by Rhyck Monday, September 19, 2011 9:29 PM
    Monday, September 19, 2011 9:27 PM
  • put the code in a try catch block if some error occurs it will capture it.

    'only this part of the code can produce an error.
    try
      Cmd1 = New OleDbCommand(Sqlquery1, Myconn)
            Cmd1.ExecuteNonQuery()
            Dim adapter As New OleDbDataAdapter(Cmd1)
            adapter.Fill(table)
    catch ex as exception
    msgbox (ex.message)
    end try
    


    If you have a lot of data in your database , the do while loop could take some time to finish (for testing purpose delete all the rows in your database and start your program again).


    Please mark those posts as answer which answers your question. Faraz
    • Marked as answer by Rhyck Tuesday, September 20, 2011 9:25 PM
    • Unmarked as answer by Rhyck Tuesday, September 20, 2011 9:25 PM
    • Marked as answer by Rhyck Tuesday, September 20, 2011 9:25 PM
    Tuesday, September 20, 2011 3:40 AM
  • Well Its still doing the same thing
     
    I mean it stuck, and freezes when it comes to add a second row!


    I am wondering if we can use the For loop instead of the while loop or maybe both, I don't know.

    Still trying to make it work.

     

    here is the new error message:

     

    • Edited by Rhyck Wednesday, September 21, 2011 3:36 AM
    Tuesday, September 20, 2011 9:50 PM
  • please paste the complete code of this form
    Please mark those posts as answer which answers your question. Faraz
    • Marked as answer by Rhyck Wednesday, September 21, 2011 3:56 PM
    Wednesday, September 21, 2011 7:06 AM
  • Here is the complete code:

    Imports System.Data.OleDb
    
    Public Class increment
    
        Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
            Dim Myconn As OleDbConnection
            Dim Cmd1, Cmd2 As OleDbCommand
            Dim Sqlquery1, Sqlquery2 As String
            Dim num As Integer = 0
            Dim code As String
            Dim table As New DataTable
    
    
            Myconn = New OleDbConnection("Provider=SQLOLEDB;Data Source=RHYCK-PC\SQLEXPRESS;Integrated Security=SSPI;Initial Catalog=rhyckincrement")
            Myconn.Open()
            code = "wwf-capg/" + Date.Today.ToString("yyyyMMdd") + "-"
            code = code + "" & num
    
            Sqlquery1 = ("SELECT code FROM gorhyck WHERE code ='" + code + "'")
            Cmd1 = New OleDbCommand(Sqlquery1, Myconn)
            Cmd1.ExecuteNonQuery()
            Dim adapter As New OleDbDataAdapter(Cmd1)
            adapter.Fill(table)
            Dim check As Boolean = False
            Do While check = False
                If table.Rows.Count > 0 Then
                    num += 1
    
                Else
    
                    Sqlquery2 = ("INSERT INTO gorhyck VALUES('" + code + "')")
                    Cmd2 = New OleDbCommand(Sqlquery2, Myconn)
                    Cmd2.ExecuteNonQuery()
                    Exit Do
                End If
            Loop
    
            Label1.Text = "Data Sent !"
    
        End Sub
    End Class
    

    Wednesday, September 21, 2011 3:59 PM
  • The error in these two lines

     code = "wwf-capg/" + Date.Today.ToString("yyyyMMdd") + "-"
            code = code + "" & num

    Do it like this

     

      code = "wwf-capg/" & Date.Today.ToString("yyyyMMdd") & "-" & num
            
    

     


    The code which you used will give you out put like (on second increment)

    wwf-capg/Date.Today.ToString20111231-1wwf-capg/Date.Today.ToString20111231-2

     

    Below is only for your information do not use it in your code

    Here you violated very basic law of programing

     code = code + "" & num
    

    You can not add a integer value in to a string , if you need to combine them use "&" operator instead of "+"


    Please mark those posts as answer which answers your question. Faraz
    • Marked as answer by Rhyck Wednesday, September 21, 2011 6:10 PM
    Wednesday, September 21, 2011 4:33 PM
  • Sorry for the violation.

    I tried with the new line code, but still the same error message:

    Wednesday, September 21, 2011 6:20 PM
  • Sqlquery2 = ("INSERT INTO gorhyck VALUES('" + code + "')")
                    Cmd2 = New OleDbCommand(Sqlquery2, Myconn)
                    Cmd2.ExecuteNonQuery()
                    chk = true ' make chk true to stop the do while counter
    '                Exit Do
                End If
            Loop
    


    Please mark those posts as answer which answers your question. Faraz
    • Marked as answer by Rhyck Thursday, September 22, 2011 4:02 PM
    Thursday, September 22, 2011 7:35 AM
  • Still the same error.

    I am Sorry, Faraz did you try to make this code work on your computer? Just want to make sure its not the DataBase the problem.

    Here is the same error again:

    Thursday, September 22, 2011 4:09 PM
  • Can you upload your form with this data base file.

    Use skydrive or any other uploading facility.


    Please mark those posts as answer which answers your question. Faraz
    • Marked as answer by Rhyck Monday, September 26, 2011 4:48 AM
    Thursday, September 22, 2011 4:40 PM
  • Hi Faraz, Here is the link for the Form and the Data File:

    https://skydrive.live.com/redir.aspx?cid=d8d189b8b9f02ecb&resid=D8D189B8B9F02ECB!102&authkey=ohOEz47mDwI%24

    Thanks for your time, I really appreciate your help. I hope this will work!

    Monday, September 26, 2011 4:54 AM
  • sorry for being so late,

    If you have a database in *.MDF format, please upload it i do not have a SQL management studio installed on my current system and i do not want to install it due to some testing purpose.


    Please mark those posts as answer which answers your question. Faraz
    • Marked as answer by Rhyck Wednesday, September 28, 2011 2:44 AM
    Tuesday, September 27, 2011 3:07 PM
  • I even tried using MS Acces Database but still getting the same error.

    Here is the link of the Application and the MS Access Database.

    http://dl.dropbox.com/u/23032057/Rhyck_Test_Incrementation.rar

     

    here is the error message with Acces Database.

    Wednesday, September 28, 2011 2:49 AM
  • If table.rows.count is >0 at the start of the loop, it will always be >0. The condition for the loop to exit is that the number of rows in the tables is zero, but as you don't remove any rows, this never happens. Hence you get an overflow after 2 Giga iterations. That's the maximum Integer value.
    Armin
    • Marked as answer by Rhyck Wednesday, September 28, 2011 5:12 PM
    Wednesday, September 28, 2011 4:29 AM
  • Thanks Armin Zingler,

    Ok, Then what to do? what is your proposition?

    Wednesday, September 28, 2011 5:13 PM
  • Ok, Then what to do? what is your proposition?

    I was only looking at the small code part from the screenshot. I'm afraid, I don't understand what's the final goal.

    Armin
    • Marked as answer by Rhyck Wednesday, September 28, 2011 6:07 PM
    Wednesday, September 28, 2011 5:35 PM
  • The main goal is to record a row each time we click the Submit button.

    So, the data to insert looks like this:  "www-text/20110928-0". The last number increments each time we submit the row if the number is already taken.

    Here is the complete code:

     

    Imports System.Data.OleDb
    
    Public Class increment
    
        Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
            Dim Myconn As OleDbConnection
            Dim Cmd1, Cmd2 As OleDbCommand
            Dim Sqlquery1, Sqlquery2 As String
            Dim num As Integer = 0
            Dim code As String
            Dim table As New DataTable
    
    
            Myconn = New OleDbConnection("Provider=SQLOLEDB;Data Source=RHYCK-PC\SQLEXPRESS;Integrated Security=SSPI;Initial Catalog=rhyckincrement")
            Myconn.Open()
            code = "wwf-capg/" & Date.Today.ToString("yyyyMMdd") & "-"
    & num      
            Sqlquery1 = ("SELECT code FROM gorhyck WHERE code ='" + code + "'")
            Cmd1 = New OleDbCommand(Sqlquery1, Myconn)
            Cmd1.ExecuteNonQuery()
            Dim adapter As New OleDbDataAdapter(Cmd1)
            adapter.Fill(table)
            Dim check As Boolean = False
            Do While check = False
                If table.Rows.Count > 0 Then
                    num += 1
    
                Else
    
                    Sqlquery2 = ("INSERT INTO gorhyck VALUES('" + code + "')")
                    Cmd2 = New OleDbCommand(Sqlquery2, Myconn)
                    Cmd2.ExecuteNonQuery()
    
    check = True
                    Exit Do
                End If
            Loop
    
            Label1.Text = "Data Sent !"
    
        End Sub
    End Class

    • Edited by Rhyck Wednesday, September 28, 2011 6:18 PM
    Wednesday, September 28, 2011 6:15 PM
  • I think I see now. You just want to get the maximum running number from the same day in the database, then increment it by one to write a new record, right?

       Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
    
          Dim Myconn As OleDbConnection
          Dim Cmd1, Cmd2 As OleDbCommand
          Dim Sqlquery1, Sqlquery2 As String
          Dim NewNum As Integer
          Dim codeBase, NewCode As String
          Dim maxValue As Object
    
          Myconn = New OleDbConnection("Provider=SQLOLEDB;Data Source=RHYCK-PC\SQLEXPRESS;Integrated Security=SSPI;Initial Catalog=rhyckincrement")
          Myconn.Open()
    
          'Retrieve last value
          codeBase = "wwf-capg/" & Date.Today.ToString("yyyyMMdd") & "-"
          Sqlquery1 = "SELECT max(code) FROM gorhyck WHERE code LIKE ?"
    
          Cmd1 = New OleDbCommand(Sqlquery1, Myconn)
          Cmd1.Parameters.AddWithValue("code", codeBase & "%")
    
          maxValue = Cmd1.ExecuteScalar
    
          'Calc new value
          If maxValue Is Nothing Then
             NewNum = 0
          Else
             Dim LastCode = maxValue.ToString
             Dim LastNum = Integer.Parse(LastCode.Substring(codeBase.Length))
    
             NewNum = LastNum + 1
          End If
    
          NewCode = codeBase & NewNum
    
          'Write new record
          Sqlquery2 = "INSERT INTO gorhyck (code) VALUES(?)"
    
          Cmd2 = New OleDbCommand(Sqlquery2, Myconn)
          Cmd2.Parameters.AddWithValue("code", NewCode)
          Cmd2.ExecuteNonQuery()
    
          Myconn.Close()
    
          Label1.Text = "Data Sent !"
    
       End Sub
    

    Is the database in a multi-user environment? Then we'd have to modify it.

    I don't know what you want to do with the DataTable and DataAdapter.

    I was not able to test it, but I hope this is what you are looking for.


    Armin
    • Marked as answer by Rhyck Wednesday, September 28, 2011 7:31 PM
    Wednesday, September 28, 2011 7:08 PM
  • yes the Database is a multiuser environment!

    Yes it is what I am looking for, kind of. Just being able to increment the last number if the code already exists.

    it looks like this:

    Insert code; code = www-capg/20110928-0. if code exists, then increment the last num ---> www-capg/20110928-1, if exists then

    increment the last num ----> www-capg/20110928-2 and so on.

    that way we know the number of rows added each day.


    I just tried your code and here is the error message:

    • Edited by Rhyck Wednesday, September 28, 2011 8:07 PM
    Wednesday, September 28, 2011 7:41 PM
  • Rhyck,

    I just see that you are using SQL Server 2008, so why don't you use the System.Data.SqlClient namespace instead of OleDb?

    Anyway, here is the multi-user safe version:
    (I assume you have a unique key on the field code)

       Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
    
          Dim Myconn As OleDbConnection
          Dim Cmd1, Cmd2 As OleDbCommand
          Dim Sqlquery1, Sqlquery2 As String
          Dim NewNum As Integer
          Dim codeBase, NewCode As String
          Dim maxValue As Object
    
          Myconn = New OleDbConnection("Provider=SQLOLEDB;Data Source=RHYCK-PC\SQLEXPRESS;Integrated Security=SSPI;Initial Catalog=rhyckincrement")
          Myconn.Open()
    
          'Retrieve last value
          codeBase = "wwf-capg/" & Date.Today.ToString("yyyyMMdd") & "-"
          Sqlquery1 = "SELECT max(code) FROM gorhyck WHERE code LIKE ?"
    
          Cmd1 = New OleDbCommand(Sqlquery1, Myconn)
          Cmd1.Parameters.AddWithValue("code", codeBase & "%")
    
          maxValue = Cmd1.ExecuteScalar
    
          'Calc new value
          If maxValue Is Nothing Then
             NewNum = 0
          Else
             Dim LastCode = maxValue.ToString
             Dim LastNum = Integer.Parse(LastCode.Substring(codeBase.Length))
    
             NewNum = LastNum + 1
          End If
    
          'Write new record
          Sqlquery2 = "INSERT INTO gorhyck (code) VALUES(?)"
    
          Cmd2 = New OleDbCommand(Sqlquery2, Myconn)
          Cmd2.Parameters.Add("code", OleDbType.VarWChar) 'modify to correct type here
    
          Do
             NewCode = codeBase & NewNum
             Cmd2.Parameters("code").Value = NewCode
    
             Try
                Cmd2.ExecuteNonQuery()
                Exit Do
             Catch ex As OleDbException When ex.ErrorCode = 12345 'replace with error number
                NewNum += 1
             End Try
          Loop
    
          Myconn.Close()
    
          Label1.Text = "Data Sent !"
    
       End Sub
    

    Look at my two comments inside. I don't know which error number is the one indicating a key constraint violation.

     

    BTW, it's not necessary to mark all of my messages as the answer even it they are not. :-) (the previous ones)

     


    Armin
    Wednesday, September 28, 2011 8:21 PM

  • I just tried your code and here is the error message:

    What's inside variable LastCode?

    Armin
    Wednesday, September 28, 2011 8:24 PM
  • When I use SqlClient I always get an error on the system, I don't know why but it the reason why I am using OleDb instand of SqlClient.

    About the variable LastCode, I think there is nothing in there since it is the first row.

    Also, I am new in VB Language, still learning...

    Wednesday, September 28, 2011 9:05 PM
  • About the variable LastCode, I think there is nothing in there since it is the first row.

    What you think is less important than what you see. ;-) So please tell me the content. It can not be the first row because it is in the ELSE-branch. If no matching record is found, it would go into the IF-branch. In addition, if LastCode was Nothing, a NullReferenceException would have occured in the line before.


    Armin

    • Edited by Armin Zingler Wednesday, September 28, 2011 9:48 PM "I" changed to "It"
    Wednesday, September 28, 2011 9:44 PM
  • Hi Armin, sorry I was away

    Still the same error and there is Nothing in the variable LastCode

    Here is the error message:

    Monday, October 10, 2011 5:04 PM
  • I think you may be better off separating the parts of the refcode in the database into the text, the date, and a sequence number. I made a table names EtcStore in a database called testing like this:

    USE [testing]
    GO
    
    /****** Object:  Table [dbo].[EtcStore]    Script Date: 10/10/2011 20:24:59 ******/
    SET ANSI_NULLS ON
    GO
    
    SET QUOTED_IDENTIFIER ON
    GO
    
    CREATE TABLE [dbo].[EtcStore](
    	[CodeText] [nvarchar](50) NOT NULL,
    	[CodeDate] [date] NOT NULL,
    	[CodeSequence] [int] NOT NULL,
    	[etc] [nvarchar](50) NULL,
     CONSTRAINT [PK_EtcStore] PRIMARY KEY CLUSTERED 
    (
    	[CodeText] ASC,
    	[CodeDate] ASC,
    	[CodeSequence] ASC
    )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
    ) ON [PRIMARY]
    
    GO
    
    
    


    and then I created a form in VS with a textbox for the text part, a datetimepicker, and a button. I put the following code on the button:

    Private Sub Button1_Click(sender As System.Object, e As System.EventArgs) Handles Button1.Click
        Dim textPart = codeText.Text
        Dim jobDate = codeDate.Value.Date
    
        Dim connStr As New SqlConnectionStringBuilder
        connStr.DataSource = ".\SQLEXPRESS"
        connStr.InitialCatalog = "testing"
        connStr.IntegratedSecurity = True
    
    
        Using sqlConn As New SqlConnection(connStr.ToString)
    
            Dim sql = "INSERT INTO EtcStore (CodeText, CodeDate, CodeSequence, etc) values (@CodeText, @CodeDate, (SELECT COALESCE(MAX(CodeSequence),-1)+1 FROM EtcStore WHERE CodeDate=@CodeDate AND CodeText=@CodeText), @etc)"
            Dim sqlCmd = New SqlCommand(sql, sqlConn)
    
            Dim sqlParam As SqlParameter
    
            sqlParam = New SqlParameter With {.ParameterName = "@CodeText", .DbType = DbType.String, .Size = 50, .Value = textPart}
            sqlCmd.Parameters.Add(sqlParam)
    
            sqlParam = New SqlParameter With {.ParameterName = "@CodeDate", .DbType = DbType.Date, .Value = jobDate}
            sqlCmd.Parameters.Add(sqlParam)
    
            sqlParam = New SqlParameter With {.ParameterName = "@etc", .DbType = DbType.String, .Size = 50, .Value = "some sort of text data"}
            sqlCmd.Parameters.Add(sqlParam)
    
            sqlConn.Open()
            Dim transac = sqlConn.BeginTransaction()
            sqlCmd.Transaction = transac
    
            Try
                sqlCmd.ExecuteNonQuery()
                transac.Commit()
            Catch ex As Exception
                transac.Rollback()
                MsgBox("Oops: " & ex.Message)
            End Try
    
        End Using
    
    End Sub
    


    and it worked :)

    Querying in SSMS with

    SELECT [CodeText]
          ,[CodeDate]
          ,[CodeSequence]
          ,[etc]
      FROM [testing].[dbo].[EtcStore]
      order by CodeDate, CodeSequence
    


    gave me

    CodeText                                           CodeDate   CodeSequence etc
    -------------------------------------------------- ---------- ------------ --------------------------------------------------
    firstTry                                           2011-10-06 0            some sort of text data
    firstTry                                           2011-10-06 1            some sort of text data
    firstTry                                           2011-10-06 2            some sort of text data
    firstTry                                           2011-10-06 3            some sort of text data
    firstTry                                           2011-10-06 4            some sort of text data
    secondTry                                          2011-10-12 0            some sort of text data
    secondTry                                          2011-10-12 1            some sort of text data
    secondTry                                          2011-10-12 2            some sort of text data
    secondTry                                          2011-10-12 3            some sort of text data
    secondTry                                          2011-10-12 4            some sort of text data
    
    (10 row(s) affected)
    
    
    

    ('scuse the formatting)

    I used (or at least tried to) a transaction just in case two or more people try the same thing at just the same time, in the hope that it will prevent the same sequence number from being used more than once.

    I'm not too sure about the transaction part of the code, you will want to make sure it's done correctly. I looked at Making SQL transaction in DB using ASP.NET 2.0 and VB for inspiration (ignore that it refers to ASP.NET).

    To get your unique code, just stick the individual parts together.

    HTH,

    Andrew

    Monday, October 10, 2011 7:39 PM
  • Hi Armin, sorry I was away

    Still the same error and there is Nothing in the variable LastCode

    Here is the error message:


    The SQL selects only those records where the field 'code' matches the pattern "wwf-capg/yyyyMMdd%". This means that Max(code) is never empty. Therefore I can't explain why LastCode is empty.

    Armin
    Wednesday, October 12, 2011 3:06 PM
  • Thank you Andrew for your help and time.

    I exactly repeated your code and got the same results but the only difference is that the field CodeSequence does not increment!!!,

    Here is the result:

     

    Thursday, October 13, 2011 8:33 AM
  • Thank you Andrew for your help and time.

    I exactly repeated your code and got the same results but the only difference is that the field CodeSequence does not increment!!!,

    That is as intended in my code: you've used a different value for CodeText each time. If you used the same value more than once (on the same date) then it would increment the value of CodeSequence.

    --
    Andrew

    Thursday, October 13, 2011 9:05 AM
  • Thank you Andrew for your help and time.

    I exactly repeated your code and got the same results but the only difference is that the field CodeSequence does not increment!!!,

    Also, you've probably already done it, but if you do want the sequence number to be for the date regardless of the CodeText, you can change the SQL from

    "INSERT INTO EtcStore (CodeText, CodeDate, CodeSequence, etc) values (@CodeText, @CodeDate, (SELECT COALESCE(MAX(CodeSequence),-1)+1 FROM EtcStore WHERE CodeDate=@CodeDate AND CodeText=@CodeText), @etc)"

    to

    "INSERT INTO EtcStore (CodeText, CodeDate, CodeSequence, etc) values (@CodeText, @CodeDate, (SELECT COALESCE(MAX(CodeSequence),-1)+1 FROM EtcStore WHERE CodeDate=@CodeDate), @etc)"

    HTH,

    Andrew

    • Marked as answer by Rhyck Saturday, October 15, 2011 12:42 AM
    Thursday, October 13, 2011 6:05 PM
  • Many thanks Andrew, it worked!

    But, the reason I wanted the CodeSequence to look like this one: capg/20111014-0 (the last number is the number to be incremented) is that the research in the DataBase will be based on it (the unique value)!

    Is it possible to have the CodeSequence look like capg/20111014-0  

                                                                                capg/20111014-1

                                                                                capg/20111014-2

                                                                                  and so forth...   

    ?    

     

    Saturday, October 15, 2011 12:59 AM
  • But, the reason I wanted the CodeSequence to look like this one: capg/20111014-0 (the last number is the number to be incremented) is that the research in the DataBase will be based on it (the unique value)!

    The research in the database needs to use the combination of the three parts to get a value of CodeSequence. However, having the parts separated makes it easier to select by date and so on.

    If you want to display the CodeSequence like that, just put the parts together when you show the values, something like

    TextBox1.Text = String.Format("{0}/{1}-{2}", codeText, codeDate.ToString("yyyyMMdd"), codeSequence)

    It is only when you show it to the end-user that it needs to look like that.

    --
    Andrew

    Saturday, October 15, 2011 10:01 AM
  • Ok Andrew you are right.

    I have an idea, for the DateBase search, I am going to put 3 Text Boxes for CodeText, CodeDate and CodeSequence where the user can type each value in each TextBox. I didn't try it yet but I think it should work.

    So the search criteria is going to be like

    SELECT * FROM EtcStore WHERE CodeText = textPart AND CodeDate = jobDate AND CodeSequence = TextBoxCodeSequence.Text
    
    Saturday, October 15, 2011 3:47 PM
  • I have an idea, for the DateBase search...

    You can do better than giving the user textboxes to type in: you can query the database for the distinct values of the CodeText and present them in a drop-down control (thus removing the chance of typos, and the user doesn't have to remember what the codes are), the date can be presented as a datetimepicker (unless there are so few dates that a drop-down could be used), and the sequence number could also be a drop-down presenting numbers from zero the maximum value in the database, again, as long as there aren't too many to be practical in a drop-down. You can get the maximum by querying the database. You could even present the maximum value for the code and date as the user enters them.

    HTH,

    Andrew

    Saturday, October 15, 2011 4:25 PM
  • Yes thats better thanks!

    Could you please show me the code for the last part? (Display "values for the code and date as users enters them") ?

    Thank you for your help and time Andrew,

    I really appreciated!

    Saturday, October 15, 2011 4:55 PM
  • Could you please show me the code for the last part? (Display "values for the code and date as users enters them") ?

    Go on, try programming it yourself :) Once the user has selected a code and a date, query the database for the max value of the sequence number where the code is equal to what the user selected and the date is equal to what the user entered.

    --
    Andrew

    Saturday, October 15, 2011 8:02 PM