谁能帮我改一下这段代码？

[lianghengchao]

'----------------------------------------------------- 
1、引用Microsft activex data objects 2.0 library 
2、添加数据时没有对表内相同的商品名进行判断 
3、如果数据存在相同的商品名时只显示第一条记录 
4、txtq代表您的表名 
5、ID的数据类型为numeric 
6、数据库为SQL2000 
'----------------------------------------------------- 
Private Sub Combo1_Click() 
Set conn = New ADODB.Connection 
Set rs = New ADODB.Recordset 
conn.ConnectionString = "Driver={sql server};server=AA;uid=sa;pwd=;database=BB" 'AA代表连接服务器名称或IP,BB代表连接数据库名 
conn.ConnectionTimeout = 20 
conn.Open 
rs.Open "select * from [txtq] where (商品名='" & Trim(Combo1.Text) & "')", conn, adOpenStatic, adLockReadOnly, adCmdText 
If rs.EOF = True And rs.BOF Then 
Else 
rs.MoveFirst 
Text1.Text = rs.Fields(2) 
Text2.Text = rs.Fields(3) 
Text3.Text = rs.Fields(4) 
Text4.Text = rs.Fields(5) 
Text5.Text = rs.Fields(6) 
rs.Close 
conn.Close 
  Set rs = Nothing 
  Set conn = Nothing 
End If 
End Sub 
Private Sub Command1_Click() '添加 
Set conn = New ADODB.Connection 
Set rs = New ADODB.Recordset 
conn.ConnectionString = "Driver={sql server};server=AA;uid=sa;pwd=;database=BB" 'AA代表连接服务器名称或IP,BB代表连接数据库名 
conn.ConnectionTimeout = 20 
conn.Open 
rs.Open "select * from [txtq]", conn, adOpenKeyset, adLockOptimistic, adCmdText 
rs.AddNew 
rs.Fields(1) = Trim(Combo1.Text) 
rs.Fields(2) = Trim(Text1.Text) 
rs.Fields(3) = Trim(Text2.Text) 
rs.Fields(4) = Trim(Text3.Text) 
rs.Fields(5) = Trim(Text4.Text) 
rs.Fields(6) = Trim(Text5.Text) 
rs.Update 
rs.Close 
  Set rs = Nothing 
  Set conn = Nothing 
Unload Me 
MsgBox "增加成功", vbInformation, "提示" 
Me.Show 
End Sub 
Private Sub Command2_Click() '删除 
Set conn = New ADODB.Connection 
Set rs = New ADODB.Recordset 
If Trim(Combo1.Text) = "" Then 
Exit Sub 
End If 
Dim SureQ As Integer 
SureQ = MsgBox("您真的要删除商品名为" & Trim(Combo1.Text) & " 的记录吗？", vbYesNo + 32, "提示") 
If SureQ = 6 Then 
Cancel = 0 
conn.ConnectionString = "Driver={sql server};server=AA;uid=sa;pwd=;database=BB" 'AA代表连接服务器名称或IP,BB代表连接数据库名 
conn.ConnectionTimeout = 20 
conn.Open 
rs.Open "delete from [txtq] where (商品名='" & Trim(Combo1.Text) & "')", conn, adOpenKeyset, adLockOptimistic, adCmdText 
conn.Close 
Set rs = Nothing 
Set conn = Nothing 
Unload Me 
MsgBox "删除成功", vbInformation, "提示" 
Me.Show 
Else 
    Cancel = -1 
  End If 
End Sub 
Private Sub Form_Load() 
Set conn = New ADODB.Connection 
Set rs = New ADODB.Recordset 
Set mrc = New ADODB.Recordset 
conn.ConnectionString = "Driver={sql server};server=AA;uid=sa;pwd=;database=BB" 'AA代表连接服务器名称或IP,BB代表连接数据库名 
conn.ConnectionTimeout = 20 
conn.Open 
mrc.Open "select Count(商品名) AS lCount,商品名 from [txtq] group by 商品名 order by 商品名", conn, adOpenStatic, adLockReadOnly, adCmdText 
If mrc.EOF = True And mrc.BOF Then 
Else 
Do Until mrc.EOF 
      strUserName = mrc("商品名") 
      Combo1.AddItem strUserName 
      mrc.MoveNext 
  Loop 
  mrc.Close 
End If 
rs.Open "select ID from [txtq] order by ID", conn, adOpenStatic, adLockReadOnly, adCmdText 
If rs.EOF = True And rs.BOF Then 
Label2.Caption = 1 
Else 
Label2.Caption = rs.Fields(0) + 1 
End If 
rs.Close 
Set rs = Nothing 
Set mrc = Nothing 
Set conn = Nothing 
End Sub 

改成本地数据库的！而且为什么我现在这样运行会出错的啊？
运行了它说我用户定义类型未定义？
New ADODB.Connection

[cheng_fengming]

你确定你得变量名定义了吗？ 比喻说rs,mrc(记录集) 要是没定义的话肯定会出错！
其他的好像没什么问题！
不过你得if语句 像If rs.EOF = True And rs.BOF Then 是不是应该将And改为or 啊？

我看你在每次局部操作的时候都要连接数据库
conn.ConnectionString = "Driver={sql server};server=AA;uid=sa;pwd=;database=BB" 'AA代表连接服务器名称或IP,BB代表连接数据库名
conn.ConnectionTimeout = 20
conn.Open

我的建议是你在公共模块连接数据库吧！这样连接一次就可以了，可以简化你的程序！
（个人建议 仅供参考）

[白发程序猿]

这个错误表示你没引用ado

[饮水需思源]

在工程中没有引用ado吧

[ljluck7687]

估计未引用ADO而导致ADODB.Connection未定义

[lyserver]

另外，使用new关键字进行COM对象绑定时，必须事先声明其类型，如：
Dim cn As Connection
Set cn=new Connection
但是，俺看不到事先声明的代码。

[dufuguang]

在你确定引用了ADO的前提下，建议你做如下改动 （只是建议，呵呵）
1。
If rs.EOF = True And rs.BOF Then '改成do while not rs.eof & es.bof ....loop 形式
Else
rs.MoveFirst
Text1.Text = rs.Fields(2)
Text2.Text = rs.Fields(3)
Text3.Text = rs.Fields(4)
Text4.Text = rs.Fields(5)
Text5.Text = rs.Fields(6)
rs.Close

2.
把链接数据库的代码写成模块，更简洁！

[lyserver]

代码有点乱，有的地方连接未释放或在错误的逻辑块里释放，加外不知你是否引用了ADODB，故修改了一部分，如下：

Private Sub Combo1_Click()
    Set conn = CreateObject("ADODB.Connection")
    Set rs = CreateObject("ADODB.Recordset")
    conn.ConnectionString = "Driver={sql server};server=AA;uid=sa;pwd=;database=BB" 'AA´ú±íÁ¬½Ó·þÎñÆ÷Ãû³Æ»òIP,BB´ú±íÁ¬½ÓÊý¾Ý¿âÃû
    conn.ConnectionTimeout = 20
    conn.Open
    rs.Open "select * from [txtq] where (ÉÌÆ·Ãû='" & Trim(Combo1.Text) & "')", conn, adOpenStatic, adLockReadOnly, adCmdText
    If rs.EOF And rs.BOF Then
    Else
        rs.MoveFirst
        Text1.Text = rs.Fields(2)
        Text2.Text = rs.Fields(3)
        Text3.Text = rs.Fields(4)
        Text4.Text = rs.Fields(5)
        Text5.Text = rs.Fields(6)
    End If
    rs.Close
    conn.Close
    Set rs = Nothing
    Set conn = Nothing
End Sub
Private Sub Command1_Click() 'Ìí¼Ó
    Set conn = CreateObject("ADODB.Connection")
    Set rs = CreateObject("ADODB.Recordset")
    conn.ConnectionString = "Driver={sql server};server=AA;uid=sa;pwd=;database=BB" 'AA´ú±íÁ¬½Ó·þÎñÆ÷Ãû³Æ»òIP,BB´ú±íÁ¬½ÓÊý¾Ý¿âÃû
    conn.ConnectionTimeout = 20
    conn.Open
    rs.Open "select * from [txtq]", conn, adOpenKeyset, adLockOptimistic, adCmdText
    rs.AddNew
    rs.Fields(1) = Trim(Combo1.Text)
    rs.Fields(2) = Trim(Text1.Text)
    rs.Fields(3) = Trim(Text2.Text)
    rs.Fields(4) = Trim(Text3.Text)
    rs.Fields(5) = Trim(Text4.Text)
    rs.Fields(6) = Trim(Text5.Text)
    rs.Update
    rs.Close
    db.Close
    Set rs = Nothing
    Set conn = Nothing
    Me.Hide
    MsgBox "Ôö¼Ó³É¹¦", vbInformation, "Ìáʾ"
    Me.Show
End Sub