SQL 语句的结束位置缺少分号

[Selina_kk]

SQL中插入不重复记录的语句，运行时总是提示语句的结束位置缺少分号，哪位大虾帮我看一下呗？真迷糊了。


connectionstring = "Provider = Microsoft.ACE.OLEDB.12.0;Data Source=" & My.Application.Info.DirectoryPath & "\MyCalculateDB.accdb;" '路径为G:\VBprogremm\Calculate\Calculate\bin\Debug

Dim querystring As String

Try

If CheckBox1.Checked = False Then

querystring = "insert into projectdata(province,city,returnperiod,groundtype,antiseismic,us1,us2,A,zh,wk1,wk2,earthquakeeffect,projectname) values ( '" & ComboBox1.Text & "','" & ComboBox2.Text & "','" & returnperiod & "','" & groundtype & "','" & ComboBox3.Text & "','" & us1 & "','" & us2 & "','" & 0 & "','" & zh & "','" & wk1 & "','" & wk2 & "','" & TextBox8.Text & "','" & TextBox3.Text & "' ) select projectname from projectdata where not exists(select * from projectdata where projectname = '" & TextBox3.Text & " ')"

Else

querystring = "insert into projectdata(province,city,returnperiod,groundtype,antiseismic,us1,us2,A,zh,wk1,wk2,earthquakeeffect,projectname) values ( '" & ComboBox1.Text & "','" & ComboBox2.Text & "','" & returnperiod & "','" & groundtype & "','" & ComboBox3.Text & "','" & us1 & "','" & us2 & "','" & A & "','" & zh & "','" & wk1 & "','" & wk2 & "','" & TextBox8.Text & "','" & TextBox3.Text & "' ) select projectname from projectdata where not exists(select * from projectdata where projectname = '" & TextBox3.Text & " ')"


End If

Using conn As New OleDb.OleDbConnection(connectionstring)
conn.Open() '打开连接
Dim cmd As New OleDb.OleDbCommand(querystring, conn)
cmd.ExecuteNonQuery()
End Using

MsgBox("数据保存成功！")
Catch ex As Exception
MsgBox(ex.Message)
End Try

[Selina_kk]

[Wicho开源开发中]

断点跟踪下

[以专业开发人员为伍]

select projectname from projectdata .... 前边写个分号呗。

[devmiao]

拼接sql不正确。

[Selina_kk]

[Quote=引用 3 楼 的回复:]

select projectname from projectdata .... 前边写个分号呗。
[/Quote]

这个嘛，，，，还是不对呢？？？提示“在SQL语句结尾之后找到字符”，是个什么情况呀？

我不会了，真的不会了，书上没有，老师也没教过，就这句还是从网是扒下来的呢。。。。

[Selina_kk]

[Quote=引用 4 楼 的回复:]

拼接sql不正确。
[/Quote]

说具体点呗？？？

[ParanoidKing]

貌似Access不支持多语句。先把select拎出来查询，没有记录的时候再执行insert吧。

[Selina_kk]

自己顶一下吧！大家能不能说具体点呢？我是新新新新新手

[Wicho开源开发中]

分开执行 使用事务

[ParanoidKing]

意思是这样（没写过VB，不保证正确）

  connectionstring = "Provider = Microsoft.ACE.OLEDB.12.0;Data Source=" & My.Application.Info.DirectoryPath & "\MyCalculateDB.accdb;" '路径为G:\VBprogremm\Calculate\Calculate\bin\Debug

   Dim querystring As String   

  Try
     
  If CheckBox1.Checked = False Then   

  querystring = "insert into projectdata(province,city,returnperiod,groundtype,antiseismic,us1,us2,A,zh,wk1,wk2,earthquakeeffect,projectname) values ( '" & ComboBox1.Text & "','" & ComboBox2.Text & "','" & returnperiod & "','" & groundtype & "','" & ComboBox3.Text & "','" & us1 & "','" & us2 & "','" & 0 & "','" & zh & "','" & wk1 & "','" & wk2 & "','" & TextBox8.Text & "','" & TextBox3.Text & "' )" 'select projectname from projectdata where not exists(select * from projectdata where projectname = '" & TextBox3.Text & " ')"
     
  Else

   querystring = "insert into projectdata(province,city,returnperiod,groundtype,antiseismic,us1,us2,A,zh,wk1,wk2,earthquakeeffect,projectname) values ( '" & ComboBox1.Text & "','" & ComboBox2.Text & "','" & returnperiod & "','" & groundtype & "','" & ComboBox3.Text & "','" & us1 & "','" & us2 & "','" & A & "','" & zh & "','" & wk1 & "','" & wk2 & "','" & TextBox8.Text & "','" & TextBox3.Text & "' )" 'select projectname from projectdata where not exists(select * from projectdata where projectname = '" & TextBox3.Text & " ')"
     

  End If

  Dim querystring_select As String
  '查询是否已存在记录
  querystring_select = "select projectname from projectdata where not exists(select * from projectdata where projectname = '" & TextBox3.Text & " ')"

   Using conn As New OleDb.OleDbConnection(connectionstring)   
  conn.Open() '打开连接
  ' Dim cmd As New OleDb.OleDbCommand(querystring, conn)   
  'cmd.ExecuteNonQuery() 
   Dim cmd As New OleDb.OleDbCommand(conn)
   cmd.CommandText = querystring_select
   Dim selectObj As Object
   selectObj = cmd.ExexuteScalar()
   If DbNull.Value.Equals(selectObj) Then
      '如果不存在记录就插入数据
      cmd.CommandText = querystring
      cmd.ExecuteNonQuery() 
   End If  

  End Using

   MsgBox("数据保存成功！")
   Catch ex As Exception
   MsgBox(ex.Message)
   End Try

[Selina_kk]

我试试。。。

[ZhangNan20100811]

querystring 先只执行 insert 语句，如果成功，insert 语句没问题；在只执行 select 语句。
分开测试，如果都行，那肯定是 插入数据后又查询，SQL报错。

[Selina_kk]

[Quote=引用 13 楼 的回复:]
querystring 先只执行 insert 语句，如果成功，insert 语句没问题；在只执行 select 语句。
分开测试，如果都行，那肯定是 插入数据后又查询，SQL报错。
[/Quote]

嗯~~嗯~~
我现在能确定的是insert可以插入记录
问题是在同样的数据，按了几次保存按钮，程序中就插入几条，一样一样地~~~~

[Selina_kk]

[ParanoidKing]

可以插入同样的记录，说明数据库设计不合理，约束得不够
不动数据库的话，就先判断数据是否存在，不存在就insert，存在就不做

[Selina_kk]

嗯 嗯 嗯

[siweng_zyd]

每次insert之前，clear一下sql

[yz小天]

设置页面的过期时间，你那是重复提交数据了。。。

[wind_cloud2011]

在插入数据前，先查询这条记录，如果有相同的记录就不插入数据了

[wind_cloud2011]

 Public Sub insertdata(ByVal s1 As String, ByVal s2 As String, ByVal s3 As String)
        Dim sql As String
        Dim con As New SqlClient.SqlConnection("Data Source=10.168.1.5;Initial Catalog=data;User ID=sa;password=sa;Integrated Security=False")
        con.Open()
        Dim sqlCommand As SqlClient.SqlCommand = New System.Data.SqlClient.SqlCommand(String.Format("select Count(*) from newtable where a= '{0}'", s1), con)
        Dim rowsAffected As Integer = sqlCommand.ExecuteScalar()
        If sqlCommand.ExecuteScalar() > 0 Then
            MessageBox.Show(s1 & " 数据已经存在")
        Else
            sql = "insert into newtable(a,b,c) values('" + s1 + "','" + s2 + "','" + s3 + "')"
            sqlCommand.CommandText = sql
            sqlCommand.ExecuteNonQuery()
            MessageBox.Show(s1 & " 成功添加!")
        End If
        sqlCommand.Dispose()
        sqlCommand.Clone()
    End Sub