how to auto generate record ID?

[ssfftt]

[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

 

[ssfftt]

I think i am talking about autonumbering in adding a new record, am i right?

 

[ssfftt]

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?

 

[sevenhalo]

What's the table layout and Input query you used?

 

[ssfftt]

I think i am confusing myself now.
I better show the code:

here is the code to set the dataset into insert mode:

    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.

 

[ssfftt]

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:

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)

 

[sevenhalo]

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.

 

[ssfftt]

ok, what's your prefered way to do this? I mean adding a new record to MS SQL database in VB.NET?

 

[sevenhalo]

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?

 

[ssfftt]

u mean i shouldnt have UID there in my query at all?

 

[sevenhalo]

Nope, SQL will generate that for you.

The select statement is ok using it, but not the insert.

 

[ssfftt]

and i am actually reading some articles, so i dont even need sqldataadapter?
maybe all i need is a sqlconnection and a sqlcommand?

 

[sevenhalo]

[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).

 

[ssfftt]

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?

 

[sevenhalo]

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)