Auto number stuck

[khabib28]

I have code like that

Call koneksi()
        cmd = New OleDbCommand("select * from customer order by nid_c desc", conn)
        dr = cmd.ExecuteReader
        dr.Read()
        If Not dr.HasRows Then
            cusnid.Text = "CR" + "00001"
        Else
            cusnid.Text = Val(Microsoft.VisualBasic.Mid(dr.Item("nid_c").ToString, 3, 4)) + 1
            If Len(cusnid.Text) = 1 Then
                cusnid.Text = "CR0000" & cusnid.Text & ""
            ElseIf Len(cusnid.Text) = 2 Then
                cusnid.Text = "CR000" & cusnid.Text & ""
            ElseIf Len(cusnid.Text) = 3 Then
                cusnid.Text = "CR00" & cusnid.Text & ""
            ElseIf Len(cusnid.Text) = 4 Then
                cusnid.Text = "CR0" & cusnid.Text & ""
            ElseIf Len(cusnid.Text) = 5 Then
                cusnid.Text = "CR" & cusnid.Text & ""

            End If
        End If

when the number on "CR10000" cannot increase to --> CR10001
help me..
thanks

[Shaggy Hiker]

A couple things first:

1) If you really only want the value from one column in one row, which is all you are currently getting, then you can use ExecuteScalar rather than using a datareader. ExecuteScalar is the FASTEST means to get a value from a DB table, but it will ONLY give you the first field from the first row.

So, you could do this:

cmd = New OleDbCommand("select rid_c from customer order by nid_c desc", conn)
Dim obj As Object = cmd.ExecuteScalar

If IsDBNull(obj) Then
 cusnid.Text = "CR" + "00001"
Else
  'The rest goes here.
End If

This will be a bit faster, though you won't see it, most likely, as either way should be pretty fast.

As to the question, you are running into problems because you are making cusnid do two different things. It is displaying the value, which is what it should do, but it is also holding the value as a string, which it is not well suited to. Instead, have an integer variable at form scope. Use that integer variable to hold the number part, and increment that integer variable as needed. Whenever you change the integer variable, then you can display it with:

cusnid.Text = "CR" & yourIntegerVariableHere.ToString

Note that this would not quite produce the pattern you are showing if you leave it like this. It would produce CR1, CR2, CR3, etc. That doesn't seem likely to be what you want, because you show things like CR00001. To get to that, you would change it to something like this:

cusnid.Text = "CR" & (yourIntegerVariableHere.ToString).PadLeft(5,"0"c)

What that does is takes the integer, converts it to a string, then takes that string and pads it out to a length of 5 characters using the character "0" to pad with.

[khabib28]

A couple things first:

1) If you really only want the value from one column in one row, which is all you are currently getting, then you can use ExecuteScalar rather than using a datareader. ExecuteScalar is the FASTEST means to get a value from a DB table, but it will ONLY give you the first field from the first row.

So, you could do this:

cmd = New OleDbCommand("select rid_c from customer order by nid_c desc", conn)
Dim obj As Object = cmd.ExecuteScalar

If IsDBNull(obj) Then
 cusnid.Text = "CR" + "00001"
Else
  'The rest goes here.
End If

This will be a bit faster, though you won't see it, most likely, as either way should be pretty fast.

As to the question, you are running into problems because you are making cusnid do two different things. It is displaying the value, which is what it should do, but it is also holding the value as a string, which it is not well suited to. Instead, have an integer variable at form scope. Use that integer variable to hold the number part, and increment that integer variable as needed. Whenever you change the integer variable, then you can display it with:

cusnid.Text = "CR" & yourIntegerVariableHere.ToString

Note that this would not quite produce the pattern you are showing if you leave it like this. It would produce CR1, CR2, CR3, etc. That doesn't seem likely to be what you want, because you show things like CR00001. To get to that, you would change it to something like this:

cusnid.Text = "CR" & (yourIntegerVariableHere.ToString).PadLeft(5,"0"c)

What that does is takes the integer, converts it to a string, then takes that string and pads it out to a length of 5 characters using the character "0" to pad with.

i need the value for many column