none
Autonumber generation in VB. Net

    Question

  • hi

    please check following code:

    --------------------------------------------

     

    Private Sub AutoTransNumber()

     

    Dim temp As Integer = 0

     

    Try

    strSql =

    "Select Max(Header_Key) 'ID' From ISP_Sales_Inv_Hdr"

    cmd =

    New SqlCommand(strSql, Conn)

    dr = cmd.ExecuteReader

    dr.Read()

     

    If dr.HasRows = True Then

     

    While dr.Read()

    temp = dr.Item(

    "Header_Key") + 1

     

    End While

     

    Else

    temp = 1

    MsgBox(temp)

     

    End If

    dr.Close()

     

    Catch ex As Exception

     

    End Try

    MsgBox(temp)

    Inv_noTextBox.Text = temp

     

    End Sub

    --------------------------------------------------------------------

     

    In my table Header_Key is varchar field.

    I want to generate auto number.

    but it is showing "0" only.

    Where i am making mistake?

    please check and let me know.

    Thanks in advance

    Saturday, December 18, 2010 8:05 AM

Answers

  • First of all,

     

    thank you vary much to devender bijanai and konrad neitzel for suggesting me

    but i got the solution like this :

    strSql =

    "Select ISNULL(Max(Header_Key),0) 'ID' From ISP_Sales_Inv_Hdr"

    cmd =

    New SqlCommand(strSql, Conn)

    dr = cmd.ExecuteReader

    dr.Read()

    Inv_noTextBox.Text = dr.Item(0) + 1

     

    now whenever my form is loading i am getting updated number like 1, 2, 3.

    Thank you very much once again to both of you.

     

     

    • Marked as answer by Discovery1 Saturday, December 18, 2010 11:44 AM
    Saturday, December 18, 2010 11:43 AM

All replies

  • u cant add an int value with varchar value ... u hav to first convert varchar value into int.

     

    for your solution ... try this..:--

    Select Max(convert(int,Header_Key))+1 as 'ID' From ISP_Sales_Inv_Hdr

     

     


    --------------------------------- Devender Bijania
    Saturday, December 18, 2010 9:41 AM
  • Hi,

    first of all: Header_Key should be a number only? Why do you make it an varchar? I would just use the correct datatype instead.

    You get an exception. So you get the 0 from inside the exception handling.

    In T-SQL, Max() is not a valid operation on strings.

    So my suggestion is:
    - use bigint instead of varchar
    - make it an identity, that is automatically increased.

    With kind regards,

    Konrad

    Saturday, December 18, 2010 9:46 AM
  • First of all,

     

    thank you vary much to devender bijanai and konrad neitzel for suggesting me

    but i got the solution like this :

    strSql =

    "Select ISNULL(Max(Header_Key),0) 'ID' From ISP_Sales_Inv_Hdr"

    cmd =

    New SqlCommand(strSql, Conn)

    dr = cmd.ExecuteReader

    dr.Read()

    Inv_noTextBox.Text = dr.Item(0) + 1

     

    now whenever my form is loading i am getting updated number like 1, 2, 3.

    Thank you very much once again to both of you.

     

     

    • Marked as answer by Discovery1 Saturday, December 18, 2010 11:44 AM
    Saturday, December 18, 2010 11:43 AM