[学习]利用SqlDataAdapter Insertcommand 获取刚新增的自动编号ID值

这句很重要 要用INSERTCOMMAND 必须要先有SELCOMMAND这个

  Dim  myAda  As  SqlDataAdapter  =   New  SqlDataAdapter( " Select * From [UserInfo] Where [U_ID] Is Null " , conn)

接下来就是INSERT了,注意 有二个SQL语句 " ;" Select  SCOPE_IDENTITY() As U_ID  (U_ID 为自动ID)

                sql  =   " INSERT INTO [UserInfo] ( "
                sql  +=   "   [U_Name] "
                sql  +=   "  ,[U_Password] "
                sql  +=   "   ,[U_EMail] "
                sql  +=   "  ,[U_Face] "
                sql  +=   "  ,[U_UserTruename] "
                sql  +=   "  ,[U_Sex] "
                sql  +=   "  ,[U_Age] "
                sql  +=   "  ,[U_Identity] "
                sql  +=   "  ,[U_Tel] "
                sql  +=   "  ,[U_Cellphone] "
                sql  +=   "  ,[U_Address] "
                sql  +=   "  ,[U_Zipcode] "
                sql  +=   " ,[U_MsnQQ] "
                sql  +=   " ,[U_About] "
                sql  +=   " ,[M_MaCode] "
                sql  +=   " ,[U_Score] "


                sql  +=   "  ) Values ( "
                sql  +=   "   @U_Name "
                sql  +=   "  ,@U_Password "
                sql  +=   "  , @U_EMail "
                sql  +=   "  ,@U_Face "
                sql  +=   "  ,@U_UserTruename "
                sql  +=   "  ,@U_Sex "
                sql  +=   "  ,@U_Age "
                sql  +=   "  ,@U_Identity "
                sql  +=   "  ,@U_Tel "
                sql  +=   "  ,@U_Cellphone "
                sql  +=   "  ,@U_Address "
                sql  +=   "  ,@U_Zipcode "
                sql  +=   " ,@U_MsnQQ "
                sql  +=   " ,@U_About "
                sql  +=   " ,@M_MaCode "
                sql  +=   " ,@U_Score "
                sql  +=   " );Select  SCOPE_IDENTITY() As U_ID "

myAda.InsertCommand  =   New  SqlCommand(sql, conn) 
                 With  myAda.InsertCommand 

                    .Parameters.Add( New  SqlParameter( " @U_Name " , SqlDbType.NVarChar,  40 ,  " U_Name " ))
                    .Parameters.Add( New  SqlParameter( " @U_Password " , SqlDbType.NVarChar,  200 ,  " U_Password " ))
                    .Parameters.Add( New  SqlParameter( " @U_Email " , SqlDbType.NVarChar,  300 ,  " U_Email " ))
                    .Parameters.Add( New  SqlParameter( " @U_Face " , SqlDbType.Int,  4 ,  " U_Face " ))
                    .Parameters.Add( New  SqlParameter( " @U_UserTruename " , SqlDbType.NVarChar,  100 ,  " U_UserTruename " ))
                    .Parameters.Add( New  SqlParameter( " @U_Sex " , SqlDbType.Bit,  1 ,  " U_Sex " ))
                    .Parameters.Add( New  SqlParameter( " @U_Age " , SqlDbType.NVarChar,  100 ,  " U_Age " ))
                    .Parameters.Add( New  SqlParameter( " @U_Identity " , SqlDbType.NVarChar,  100 ,  " U_Identity " ))
                    .Parameters.Add( New  SqlParameter( " @U_Tel " , SqlDbType.NVarChar,  100 ,  " U_Tel " ))
                    .Parameters.Add( New  SqlParameter( " @U_Cellphone " , SqlDbType.NVarChar,  100 ,  " U_Cellphone " ))
                    .Parameters.Add( New  SqlParameter( " @U_Address " , SqlDbType.NVarChar,  300 ,  " U_Address " ))
                    .Parameters.Add( New  SqlParameter( " @U_Zipcode " , SqlDbType.NVarChar,  100 ,  " U_Zipcode " ))
                    .Parameters.Add( New  SqlParameter( " @U_MsnQQ " , SqlDbType.NVarChar,  100 ,  " U_MsnQQ " ))
                    .Parameters.Add( New  SqlParameter( " @U_About " , SqlDbType.NVarChar,  - 1 ,  " U_About " ))
                    .Parameters.Add( New  SqlParameter( " @M_MaCode " , SqlDbType.NVarChar,  100 ,  " M_MaCode " ))
                    .Parameters.Add( New  SqlParameter( " @U_Score " , SqlDbType.Int,  4 ,  " U_Score " ))

                 End   With

建立DS 并获取 ID

Dim  ds  As   New  DataSet
                myAda.Fill(ds)

                 Dim  myDataRow  As  DataRow  =  ds.Tables( 0 ).NewRow()

                myDataRow( " U_Name " )  =   Me .U_Name.Text.Trim
                myDataRow( " U_Password " )  =  myappClass.EncryptPassword( Me .U_PasswordReg1.Text.Trim,  " MD5 " )
                myDataRow( " U_Email " )  =   Me .U_Email.Text.Trim
                myDataRow( " U_Face " )  =   Me .myhead.Value.Trim.Trim
                myDataRow( " U_UserTruename " )  =   Me .U_UserTruename.Text.Trim
                 If   Me .U_Sex.SelectedValue  =   1   Then
                    myDataRow( " U_Sex " )  =   True
                 Else
                    myDataRow( " U_Sex " )  =   False
                 End   If

                myDataRow( " U_Age " )  =   Me .U_Age.Text.Trim
                myDataRow( " U_Identity " )  =   Me .U_Identity.Text.Trim
                myDataRow( " U_Tel " )  =   Me .U_Tel.Text.Trim
                myDataRow( " U_Cellphone " )  =   Me .U_Cellphone.Text.Trim
                myDataRow( " U_Address " )  =   Me .U_Address.Text.Trim
                myDataRow( " U_Zipcode " )  =   Me .U_Zipcode.Text.Trim
                myDataRow( " U_MsnQQ " )  =   Me .U_MsnQQ.Text.Trim

                 If   Me .U_About.Text.Trim  <>   ""   Then
                    myDataRow( " U_About " )  =  myappClass.rpl( Me .U_About.Text.Trim)
                 Else
                    myDataRow( " U_About " )  =   Me .U_About.Text.Trim

                 End   If

                myDataRow( " M_MaCode " )  =  myappClass.getMacAdd().ToString
                myDataRow( " U_Score " )  =   1000    ' '注册赠送游戏喜金 2008-05-07

                ds.Tables( 0 ).Rows.Add(myDataRow)
                myAda.Update(ds)

                 Dim  myUID  As   String   =  ds.Tables( 0 ).Rows( 0 )( " U_ID " ).ToString
                ds.Dispose()

注意 ds.Tables(0).Rows(0)("U_ID").ToString

转载于:https://www.cnblogs.com/apiapia/archive/2008/06/06/1215417.html