【求助】用VBA向SQL server数据库插入一行，要求主键id的自动生成，代码应该如何实现

AntoniaQiao

  【求助】用VBA向SQL server数据库插入一行，要求主键id的自动生成，代码应该如何实现
我自己试的，如果没有主键cus_id 这一列，可以实现向数据库插入一列。代码如下：
Private Sub CommandButton1_Click()
Application.ScreenUpdating = False

Dim cn As New ADODB.Connection '定义数据链接对象 ，保存连接数据库信息；请先添加ADO引用
Dim strCn As String, strSQL As String '字符串变量
strCn = "Provider=sqloledb;Server=QIAOCY;Database=business;Uid=sa;Pwd=sa;"  '定义数据库连接字符串
cn.Open strCn   '与数据库建立连接，如果成功，返回连接
If UserForm1.TextBox1.Text = "" Or UserForm1.TextBox2.Text = "" Or UserForm1.TextBox3.Text = "" Or UserForm1.TextBox4.Text = "" Or UserForm1.TextBox5.Text = "" Or UserForm1.TextBox8.Text = "" Then
  MsgBox "有空值"
  End If

   
  strSQL = " insert into  customerlist(cus_name,cus_company,cus_dep,cus_position,cus_phone1,cus_phone2,cus_phone3,cus_mail)  values ('" & UserForm1.TextBox1.Text & "','" & UserForm1.TextBox2.Text & "','" & UserForm1.TextBox3.Text & "','" & UserForm1.TextBox4.Text & "'," & UserForm1.TextBox5.Text & "," & UserForm1.TextBox6.Text & "," & UserForm1.TextBox7.Text & ",'" & UserForm1.TextBox8.Text & "') "
  
  cn.Execute strSQL
  cn.Close
   
   
Application.ScreenUpdating = True
    Unload UserForm1
   
    Exit Sub
   
End Sub

1. 
   

复制代码

但是我想在数据库的表格里加一行cus_id，并且设置为主键，每当插入一行数据，cus_id的值加1，应该如何实现，求大神帮忙。

山中老人

SELECT Max(ID)+1 as ID FROM  customerlist

  strSQL = " insert into  customerlist(ID,cus_name,cus_company,cus_dep,cus_position,cus_phone1,cus_phone2,cus_phone3,cus_mail)  values ((SELECT Max(ID)+1 as ID FROM  customerlist),'" & UserForm1.TextBox1.Text & "','" & UserForm1.TextBox2.Text & "','" & UserForm1.TextBox3.Text & "','" & UserForm1.TextBox4.Text & "'," & UserForm1.TextBox5.Text & "," & UserForm1.TextBox6.Text & "," & UserForm1.TextBox7.Text & ",'" & UserForm1.TextBox8.Text & "') "

amazeyeli

ALTER TABLE table_name
ALTER COLUMN column_name PRIMARY KEY identity(1,1)


PRIMARY KEY 主键
identity(1,1) 自增长，起始为1，步长1

amazeyeli

然后insert into忽略主键即可，和你写的一模一样

dy_ysl

留下印记，备用