Linux SQL 语句不会插入到 DB.sdf 中
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3677058/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
SQL statement won't insert into DB.sdf
提问by Chris Leah
I am using Visual Studio 2008 and have connected a database correctly as I have done a login that works fine, although when I try to insert information submitted in the text boxes a different table, it doesn't enter after I end program to check it still has no data in. Any ideas?
我正在使用 Visual Studio 2008 并正确连接了一个数据库，因为我已经完成了一个工作正常的登录，但是当我尝试在文本框中插入提交给不同表的信息时，它在我结束程序检查后没有输入仍然没有数据。有什么想法吗？
Dim con As SqlCeConnection = New SqlCeConnection("Data Source=NESdb.sdf")
Dim myDA As SqlCeDataAdapter
Dim myDataSet As DataSet
Dim dt As New DataTable()
'Connect to database'
con.Open()
'Attempt to retrieve data'
Try ' Select username and password that match'
Dim cmd As SqlCeDataAdapter = New SqlCeDataAdapter("INSERT INTO ScrapVehicles(Fname, Lname, Add1, Add2, Town, PostCode, Telephone, Mob, Email, VehicleType, RegNo, Year, Make, Model, V5, Collected, CollectionDate)" + "VALUES('" & txtFname.Text & "', '" & txtLname.Text & "', '" & txtAdd1.Text & "', '" & txtAdd2.Text & "', '" & txtTown.Text & "', '" & txtPostCode.Text & "', '" & txtTelephone.Text & "', '" & txtMob.Text & "', '" & txtEmail.Text & "', '" & comboVehicleType.Text & "', '" & txtReg.Text & "', '" & comboYear.Text & "', '" & comboMake.Text & "', '" & txtModel.Text & "', '" & chkV5.Text & "', '" & chkCollected.Text & "', '" & dtpWhen.Text & "')", con)
'Catch errors'
Catch ex As Exception
End Try
'Close connection to database'
If con.State <> ConnectionState.Closed Then
con.Close()
End If
采纳答案by p.campbell
You're building up the cmdobject, but you don't execute it.
您正在构建cmd对象，但不执行它。
Suggest ditch the Adapter when inserting. Try this instead:
建议在插入时丢弃适配器。试试这个：
/*snipped values for brevity.*/
Dim insertSql As String = "INSERT INTO ScrapVehicles(Fname, Lname, Add1, Add2, Town) VALUES(@FName, @LName, @Add1, @Add2, @Town)"
conn.Open()
Dim cmd As New SqlCeCommand(insertSql, conn)
cmd.Parameters.Add(New SqlCeParameter("@FName", txtFirstName.Text.Trim()))
cmd.Parameters.Add(New SqlCeParameter("@LName", txtLastName.Text.Trim()))
cmd.Parameters.Add(New SqlCeParameter("@Add1", txtAdd1.Text.Trim()))
cmd.Parameters.Add(New SqlCeParameter("@Add2", txtAdd2.Text.Trim()))
cmd.ExecuteNonQuery()
conn.Close()
回答by codingbadger
You are missing a bracket at the end
你在最后缺少一个括号
& dtpWhen.Text & "'", con)
& dtpWhen.Text & "'", con)
should be
应该
& dtpWhen.Text & "')", con)
& dtpWhen.Text & "')", con)
回答by Damien_The_Unbeliever
Instead of using a SqlCeDataAdapter, use a SqlCeCommandobject. And after creating it, actually use it (call ExecuteNonQueryon it). And remove the Try, Catch Ex as Exception and End Try lines so that, if an error occurs, you'll actually see it.
使用SqlCeCommand对象而不是使用SqlCeDataAdapter。在创建之后，实际使用它（在其上调用ExecuteNonQuery）。并删除 Try、Catch Ex as Exception 和 End Try 行，这样，如果发生错误，您实际上会看到它。
That's what I can see from 30 seconds of looking.
这就是我从 30 秒的观察中可以看到的。
Edit
编辑
You should also look at using parametersrather than concatenating the INSERT statement together.
您还应该考虑使用参数而不是将 INSERT 语句连接在一起。
