Question

INSERT INTO 语句的语法错误

Imports System.Data
Imports System.Data.OleDb

Partial Class registerDB
Inherits System.Web.UI.Page

Protected Sub Button1_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button1.Click
'建立connection对象
Dim conn As New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Me.Server.MapPath("~\App_Data\mydb.mdb"))

'建立command对象
Dim cmd1 As New OleDbCommand("select id from [user] where id=" & "'" & Me.TextBox1.Text & "'", conn)
Dim cmd2 As New OleDbCommand("insert into [user](id,password,[name],birthday,telephone,e-mail) values(" _
& "'" & Me.TextBox1.Text & "','" & Me.TextBox2.Text & "','" & Me.TextBox4.Text & "','" _
& Me.TextBox5.Text & "','" & Me.TextBox6.Text & "','" & Me.TextBox7.Text & "')", conn)

'打开数据库连接
conn.Open()

'建立Datareader对象
Dim dr As OleDbDataReader = cmd1.ExecuteReader()
If dr.HasRows Then
' dr.Close()
Me.Label1.Text = "用户名已经存在!"
Else
cmd2.ExecuteNonQuery()
End If
conn.Close()

Me.Label1.Text = Me.TextBox1.Text & "的用户添加成功!"
End Sub
End Class

Answer 1

是SQL语法错误，这是Access数据库的问题，
错误出现在这里。
insert into [user](id,password,[name],birthday,telephone,e-mail）
表名是Access里的关键字，你已经加了方括号，name和password也是关键字，所以也要加上方括号。还有，e-mail，最好不要加上减号。要写成这样也行，email...以免出现语法错误。

Answer 2

建议你用最容易的方法，就是用数据库自带的跟踪器去跟踪（SQL2000叫时间探测器；SQL
2005是SQL
Server
Profiler）先启动探测器，然后运行你的插入动作程序，你就可以在探测器上找到这条对数据库操作的插入语句，你就可以发现插入失败在哪里，或者复制到查询分析器里去试试就知道哪里错了。
如果跟踪的数量很大，SQL2000需要打上SP3补丁；
事件探测器其实是很有用的，对于调试程序，希望对你有用。

Answer 3

sql1="insert into shjia ([username],[password],[name],[bank],[card],[email],[nickname],[qq],[address],[postcard],[logintime]) values('"& temid &"','"& request("password") &"','"& request("name") &"','"& request("bank") &"','"& card &"','"& request("email") &"','"& request("nickname") &"','"& request("qq") &"','"& request("address") &"','"& request("postcode") &"',#"+now+"#)"
conn.execute sql1,0
'建立connection对象
Dim conn As New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Me.Server.MapPath("~\App_Data\mydb.mdb"))

'建立command对象
Dim cmd1 As New OleDbCommand("select id from [user] where id=" & "'" & Me.TextBox1.Text & "'", conn)
Dim cmd2 As New OleDbCommand("insert into [user](id,password,[name],birthday,telephone,e-mail) values(" _
& "'" & Me.TextBox1.Text & "','" & Me.TextBox2.Text & "','" & Me.TextBox4.Text & "','" _
& Me.TextBox5.Text & "','" & Me.TextBox6.Text & "','" & Me.TextBox7.Text & "')", conn)
'建立connection对象
Dim conn As New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Me.Server.MapPath("~\App_Data\mydb.mdb"))

Answer 4

sql1="insert into shjia ([username],[password],[name],[bank],[card],[email],[nickname],[qq],[address],[postcard],[logintime]) values('"& temid &"','"& request("password") &"','"& request("name") &"','"& request("bank") &"','"& card &"','"& request("email") &"','"& request("nickname") &"','"& request("qq") &"','"& request("address") &"','"& request("postcode") &"',#"+now+"#)"
conn.execute sql1,0
为了防止数据库结构设计的不合理，最好是使用标准的sql语句来做

Answer 5

SQL语法问题啊