AutoNumber still a problem !! can ne1 clear the mess?

[laghaterohan]

I am really at my wits end now....i am unable to find ne proper code for autonumber generation...if i do find its not working. plz i want it ..i am using sql server 2000 and vb.net 2005.
just tell me can we make use of max function ?
ie. first get the max of id and then increment it by 1 ?
like ("select max(id) from tablename)
and then txtbox.text=id+1
however i encounter several errors here...can we do autonumbering this way ??? ne ideas ?
plz help me out....'coz of this am unable to complete my project work...
wating for ur reply

cya
Rohan

[Jx_Man]

Try This :

Public Sub GenerateNotaId()
        Dim myReader As SqlDataReader
        conn = GetConnect()
        conn.Open()
        Dim temp As String
        Try
            Dim sql As String = "SELECT MAX(IDNOTA) AS 'KODE' FROM Nota "
            Dim comm As SqlCommand = New SqlCommand(sql, conn)
            myReader = comm.ExecuteReader
            If myReader.HasRows Then
                While myReader.Read()
                    temp = myReader.Item("KODE") + 1
                End While
            End If
            myReader.Close()
        Catch ex As Exception

        End Try
        conn.Close()
        If temp = 0 Then
            txtNoNota.Text = 1
        Else
            txtNoNota.Text = String.Concat(temp)
        End If
End Sub

Generate will shown in txtNoNota

[laghaterohan]

thanks but still getting an error ! can u plz tell which files to import ???

Try This :

Public Sub GenerateNotaId()
        Dim myReader As SqlDataReader
        conn = GetConnect()
        conn.Open()
        Dim temp As String
        Try
            Dim sql As String = "SELECT MAX(IDNOTA) AS 'KODE' FROM Nota "
            Dim comm As SqlCommand = New SqlCommand(sql, conn)
            myReader = comm.ExecuteReader
            If myReader.HasRows Then
                While myReader.Read()
                    temp = myReader.Item("KODE") + 1
                End While
            End If
            myReader.Close()
        Catch ex As Exception

        End Try
        conn.Close()
        If temp = 0 Then
            txtNoNota.Text = 1
        Else
            txtNoNota.Text = String.Concat(temp)
        End If
End Sub

Generate will shown in txtNoNota

[Jx_Man]

This some files to import :

Imports System.Data
Imports System.Data.SqlClient

what kind of error ??

[laghaterohan]

error like getconnect() not defined.....?

This some files to import :
Imports System.Data
Imports System.Data.SqlClient

what kind of error ??

[Jx_Man]

getconnect() is Function to connect to sqlserver.
put this code in module. my module named Koneksi

'In Module
'Declare outside of class
Imports System.Data
Imports System.Data.SqlClient

Module Koneksi
    Public conn As SqlConnection
    Public Function GetConnect()
        conn = New SqlConnection("server = MyServerName;database = MyDatabaseName;Trusted_Connection = yes")
        Return conn
    End Function
End Module

[laghaterohan]

IS THIS CODE WORKING FOR U ???
THOUGH I AM NOT GETTING ANY ERROR NOW IN THE CODE, WHEN I RUN I GET ID AS 1 IN MY ID BOX , HOWEVER , WHEN I INSERT A RECORD , THEN THE NUMBER IS NOT INCREMENTED TO NEXT RECORD....EVEN IF I LOAD MY FORM AGAIN I SHOULD GET TO SEE THE NEXT NUMBER BUT IT STARTS AGAIN FROM 1, THAT VIOLATES MY PRIMARY CONSTRAINT.......WHTS THE PROBL ????

I AM NOT UNDERSTANDING WHY THE AUTO GENERATE FACTOR IS GETTING SO LONG TO BE SOLVED AT MY END.....PLZ CLARIFY SOON !

AWATING RESPONSE

getconnect() is Function to connect to sqlserver.
put this code in module. my module named Koneksi

'In Module
'Declare outside of class
Imports System.Data
Imports System.Data.SqlClient

Module Koneksi
    Public conn As SqlConnection
    Public Function GetConnect()
        conn = New SqlConnection("server = MyServerName;database = MyDatabaseName;Trusted_Connection = yes")
        Return conn
    End Function
End Module

[Jx_Man]

>> IS THIS CODE WORKING FOR U ???
Yes...Its working for me.

>> WHEN I INSERT A RECORD , THEN THE NUMBER IS NOT INCREMENTED TO NEXT RECORD
Call that procedure after inserting data

>> EVEN IF I LOAD MY FORM AGAIN I SHOULD GET TO SEE THE NEXT NUMBER BUT IT STARTS AGAIN FROM 1, THAT VIOLATES MY PRIMARY CONSTRAINT
Check your database. If your data was inserted then it should working.

And please don't use capital letters. I am trying to help you.

[laghaterohan]

thanks a lot buddy ! problem is solved

>> IS THIS CODE WORKING FOR U ???
Yes...Its working for me.

>> WHEN I INSERT A RECORD , THEN THE NUMBER IS NOT INCREMENTED TO NEXT RECORD
Call that procedure after inserting data

>> EVEN IF I LOAD MY FORM AGAIN I SHOULD GET TO SEE THE NEXT NUMBER BUT IT STARTS AGAIN FROM 1, THAT VIOLATES MY PRIMARY CONSTRAINT
Check your database. If your data was inserted then it should working.

And please don't use capital letters. I am trying to help you.