how to auto generate record ID?

ssfftt

Well-known member
Joined
Oct 27, 2005
Messages
163
Programming Experience
1-3
[solved]how to auto generate record ID?

I just learnt how to use "Data form wizard" in VB.NET, on the form, it asks for an ID (which is one of the columns of my User table) as well as values for other columns when adding new record. In real world, user wont have idea on inputing an ID.I have a User table in my SQL db, when and have a form for new user registratin. instead of asking user for the userID, i'd like the application generate an user ID automatically for the new user while uploading user details into database.can anyone help me on this and be more specific in code?thanks
 
Last edited:
in the SQL Entreprise Manager, i changed the primary key column (UID) in table User :
Identity -> yes.
Identity Seed -> 1
Identity Increment ->1

In VB.NET, when i add a new user, input all details except the user ID, it gives Null value error, why is that?
 
I think i am confusing myself now.
I better show the code:

here is the code to set the dataset into insert mode:
VB.NET:
    Private Sub frmUserRegistration_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        Me.BindingContext(DataSetNUR, "CYMUSER").AddNew()
    End Sub

and i have a button "register", my purpose is that when user clicks on "register", the input details are inserted as a new user into database.
can anyone help please, be more specific in code.
 
on the User table, i have UID, UNAME, PW, GENDER, AGE
"UID" is pk, "UNAME" is unique value, "UID, UNAME, and PW" are not null

the insert query (commandtext) is generated automatically in the visual designer within the SqlDataAdapter:
VB.NET:
INSERT INTO User
                      (UID, UNAME, GENDER, AGE, PW)
VALUES     (@UID, @UNAME, @GENDER, @AGE, @PW);
                          SELECT     UID, UNAME, GENDER, AGE, PW
                           FROM         User
                           WHERE     (UID = @UID)
 
When you use "Me," you're refferring to the form. I think you might be setting the bindingcontext to the wrong control? I may be wrong, I've never seen it done this way. :confused:
 
sqlCommand.ExecuteNonQuery

Before I go on a rant about it; I think I noticed something. Why is UID getting inserted if it's an identity field?
 
and i am actually reading some articles, so i dont even need sqldataadapter?
maybe all i need is a sqlconnection and a sqlcommand?
 
VB.NET:
[SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][SIZE=2] strConn [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]String[/COLOR][/SIZE][SIZE=2] = "Your connection string to MS SQL"
[/SIZE][SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][SIZE=2] strSQL [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]String[/COLOR][/SIZE][SIZE=2] = "INSERT INTO tblUser (UName, PWD, Gnd) VALUES (@User, @Pwd, @Gnd)"
[/SIZE][SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][SIZE=2] sqlConn [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]New[/COLOR][/SIZE][SIZE=2] SqlClient.SqlConnection(strConn)
[/SIZE][SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][SIZE=2] sqlCmd [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]New[/COLOR][/SIZE][SIZE=2] SqlClient.SqlCommand(strSQL, sqlConnn)
sqlCmd.Parameters.Add("@User", txtUser.Text.trim)
sqlCmd.Parameters.Add("@Pwd", txtPwd.Text.trim)
sqlCmd.Parameters.Add("@Gnd", txtGnd.Text.trim)
[/SIZE][SIZE=2][COLOR=#0000ff]Try
[/COLOR][/SIZE][SIZE=2]sqlConn.Open()
sqlCmd.ExecuteNonQuery()
[/SIZE][SIZE=2][COLOR=#0000ff]Catch[/COLOR][/SIZE][SIZE=2] ex [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] whatever_exceptions_you_want_to_handle
[/SIZE][SIZE=2][COLOR=#0000ff]Finally
[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]If[/COLOR][/SIZE][SIZE=2] sqlConn.State = ConnectionState.Open [/SIZE][SIZE=2][COLOR=#0000ff]Then[/COLOR][/SIZE][SIZE=2] : sqlConn.Close() : [/SIZE][SIZE=2][COLOR=#0000ff]End[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]If
[/COLOR][/SIZE][SIZE=2]sqlCmd.Dispose()
sqlConn.Dispose()
[/SIZE][SIZE=2][COLOR=#0000ff]End[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]Try
[/COLOR][/SIZE]

I've done everything I can to stay away from datasets and dataadapters. They're tools for Q n'D Development (Quick n'Dirty).
 
that looks very very good, but since i have had a sqldataadapter already which contains the query, can i actually use it? or i better get rid of it just do it in the way that u suggested?
 
Strip the insert sql command out of it (minus the UID field) and use that as strSQL. Make the parameter names the same and you should be good to go.

(The example isn't complete, I didn't line it up word for word with your example. You will need to do some tweaking)
 
Back
Top