紧急求解一个查询问题

[cfan1981]

Private Sub cmdOK_Click()
Dim cnn As ADODB.Connection
Dim mrc As ADODB.Recordset
Dim currentuser As String
Dim strcon As String

MousePointer = vbHourglass
Set cnn = New ADODB.Connection
cnn.Provider = "sqloledb"
cnn.Open "data source = fan; initial catalog = book;", "sa", "sa"
MousePointer = vbDefault

If Trim(txtUserName.Text = "") Then
MsgBox "请输入用户名！", vbOKOnly + vbExclamation, "警告"
txtUserName.SetFocus
Else
If Trim(txtPassword.Text = "") Then
MsgBox "请输入密码！", vbOKOnly + vbExclamation, "警告"
txtPassword.SetFocus

Else
Set mrc = New ADODB.Recordset
mrc.Open "select * from user where username = ' " & txtUserName.Text & " '", cnn, adOpenForwardOnly, adLockReadOnly
If mrc.EOF = True Then
MsgBox "没有此用户！请重新输入！", vbOKOnly + vbExclamation, "警告"
txtUserName.Text = ""
txtPassword.Text = ""
txtUserName.SetFocus
Else
If Trim(mrc.Fields(2)) = Trim(txtPassword.Text) Then
currentuser = Trim(txtUserName.Text)
Me.Hide
MDIForm1.Show
mrc.Close
MsgBox "欢迎登陆！", vbOKOnly + vbExclamation, "Welcome!"
Else
MsgBox "密码错误！", vbOKOnly + vbExclamation, "警告"
txtUserName.Text = ""
txtPassword.Text = ""
txtUserName.SetFocus
End If
End If
End If
End If

End Sub



当执行到mrc.Open "select * from user where username = ' " & txtUserName.Text & " '", cnn, adOpenForwardOnly, adLockReadOnly
时提示user附近有错误，请问应该怎样纠正？多谢了

[bugchen888]

mrc.Open "select * from [user] where [username] = ' " & txtUserName.Text & " '", cnn, adOpenForwardOnly, adLockReadOnly

[cfan1981]

大家帮帮忙啊

[OracleRoob]

--在SQL语句中，对于系统保留字作表名或字段名的，需要加[]用来与保留字区分

mrc.Open "select * from [user] where [username] = ' " & txtUserName.Text & " '", cnn, adOpenForwardOnly, adLockReadOnly

[cfan1981]

这样写又找不到记录了

[cfan1981]

请教一下还有哪里有问题？非常感谢了

[ashzs]

mrc.Open "select * from [user] where [username] = ' " & txtUserName.Text & " '", cnn, adOpenForwardOnly, adLockReadOnly

你上面的sql：' "和" '"中间各有个空格！？？是不是因为这两个空格所以查不到内容！？

而且你的
mrc.Open "select * from [user] where [username] = ' " & txtUserName.Text & " '", cnn, adOpenForwardOnly, adLockReadOnly
代码最好这么写：

Dim SqlString As String

SqlString = "select * from user where username = '" & txtUserName.Text & "'"

mrc.Open SqlString, cnn, adOpenForwardOnly, adLockReadOnly

[OracleRoob]

--正如楼上所说，应该去掉空格
--你可以print出这个串，使用这个串是否能查处数据

dim strSQL as string

strSQL = "select * from [user] where [username] = '" & txtUserName.Text & "'"
mrc.Open strSQL, cnn, adOpenForwardOnly, adLockReadOnly

[hf95248]

如何把按字符数字排序转换成安数字排序

现在查询结果是
客户1
客户10
客户11
客户2
客户20
客户21
客户3
客户31
而要要求的结果是
客户1
客户2
客户3
客户10
客户11
客户20
客户21
客户31

[bugchen888]

order by convert(int, substring(字段, 2, patindex('[1-9]', 字段) - 2)

[hycheng163]

同意 ashzs((可以包含中文字符))
我以前对VB那些字符不理解，也出现过类似的情况

[hongfof]

mrc.Open "select * from user where username = ' " & txtUserName.Text & " '",
cnn, adOpenForwardOnly, adLockReadOnly

你这里面的表名user应该该一下因为user是sql中的关键字了它会把它认为是关键字而不是表名执行了。

[liujx_1999]

建議lz把要執行的SQL打印出來﹐然后到查詢分析器里執行是否有錯