使用 vb.net 将 MS Access 行插入数据库
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/21961951/
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
Inserting MS Access Row Into Database using vb.net
提问by DidIReallyWriteThat
So im trying to add fields to a database. It is .mdb database, microsoft access.
所以我试图将字段添加到数据库中。它是 .mdb 数据库，microsoft access。
The Name of the table is Contacts.
表的名称是联系人。
Dim con As New OleDb.OleDbConnection
Dim dbProvider As String
Dim dbSource As String
Dim ds As New DataSet
Dim da As OleDb.OleDbDataAdapter
Dim sql As String
dbProvider = "PROVIDER=Microsoft.Jet.OLEDB.4.0;"
dbSource = "Data Source= C:\Users\Owner\Desktop\Contacts.mdb"
con.ConnectionString = dbProvider & dbSource
con.Open()
sql = "INSERT INTO Contacts (FName, LName, Age, Address Line 1, Address Line 2, City, State, Zip, Home Phone, Work Phone, Email, Sex) VALUES (a, b, c,d,e,f,g,h,i,j,k)"
da = New OleDb.OleDbDataAdapter(Sql, con)
da.Fill(ds, "Contacts")
My Error is Syntax error in INSERT INTO statement. Which makes no sense, whatsoever. What am i doing wrong?
我的错误是 INSERT INTO 语句中的语法错误。这没有任何意义，无论如何。我究竟做错了什么？
EDIT* I solvedmy riginal problem by adding [] around certian fields as suggested, thanks. Now I am getting...
编辑* 我按照建议通过在 certian 字段周围添加 [] 解决了我的原始问题，谢谢。现在我越来越...
No value given for one or more required parameters.
没有为一个或多个必需参数指定值。
The database has a primary ID field that autoincrements, does this change anything?
数据库有一个自动递增的主 ID 字段，这会改变什么吗？
回答by Luc Morin
Put column names with spaces between squares brackets []
在方括号 [] 之间放置带空格的列名
For example [Address Line 1]
例如 [Address Line 1]
Cheers
干杯
回答by Steve
As other answers have already explained you need to use square brackets around column names that contain spaces, but also you need to add a value for the fields otherwise you cannot execute the command.
正如其他答案已经解释的那样，您需要在包含空格的列名周围使用方括号，但您还需要为字段添加一个值，否则您将无法执行该命令。
I will try to show a complete example
我将尝试展示一个完整的例子
Dim dbProvider = "PROVIDER=Microsoft.Jet.OLEDB.4.0;"
Dim dbSource = "Data Source= C:\Users\Owner\Desktop\Contacts.mdb"
Dim sql = "INSERT INTO Contacts (FName, LName, Age, " & _
"[Address Line 1], [Address Line 2], City, State, Zip, " & _
"[Home Phone], [Work Phone], Email, Sex) " & _
"VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)"
Using con = New OleDb.OleDbConnection(dbProvider & dbSource)
Using cmd = new OleDb.OleDbCommand(sql, con)
con.Open()
cmd.Parameters.AddWithValue("@p1", "Value For FName")
cmd.Parameters.AddWithValue("@p2", "Value For LName")
cmd.Parameters.AddWithValue("@p3", Convert.ToInt32("Value For Age"))
.... and so on for the other parameters .....
cmd.ExecuteNonQuery()
End Using
End Using
In this example I have inserted 12 placeholders for the parameters (?) and then added the first 3 parameters out of 12 required. Note that with OleDb the parameter collection still requires to add the parameters with a name (@pX), but when executing the query the parameter value is picked following the same order of the placeholder.
在此示例中，我为参数 (?) 插入了 12 个占位符，然后添加了所需的 12 个参数中的前 3 个。请注意，对于 OleDb，参数集合仍需要添加具有名称 ( @pX) 的参数，但在执行查询时，参数值将按照占位符的相同顺序选取。
Also I have used the Using statement to close and dispose the disposable objects like the connection and the command.
我还使用 Using 语句来关闭和处理一次性对象，如连接和命令。
Finally, an Insert query is normally executed using ExecuteNonQuery from the OleDbCommand and there is no need to use an OleDbAdapter and call Fill to load a DataSet when no SELECT query is executed
最后，通常使用 OleDbCommand 中的 ExecuteNonQuery 执行插入查询，并且无需使用 OleDbAdapter 并在未执行 SELECT 查询时调用 Fill 来加载数据集
回答by Havenard
Tables and fields names composed of a single word can be written directly, such as FName.
由单个单词组成的表名和字段名可以直接写，如FName.
Names like Address Line 1howover will need to be wrapped between []in order to make the synthax consistent.
为了使合成器一致，Address Line 1需要将诸如howover 之类的名称包裹起来[]。
INSERT INTO Contacts (FName, LName, Age, [Address Line 1], [Address Line 2], City, State, Zip, [Home Phone], [Work Phone], Email, Sex) VALUES (...
The same applies to objects named with reserved words.
这同样适用于以保留字命名的对象。
回答by Adeilde Alves
If I understood what your needs are correctly:
如果我理解您的需求是正确的：
Translated from : Se entendi o que está precisando é isto aqui,veja o código:
翻译自：Se entendi o que está precisando é isto aqui,veja o código：
comando = New OleDbCommand("INSERT INTO TabClientes(NomeCliente,Telefone,CEP,Rua,Bairro,Cidade,Estado)" & _
"Values (@nome,@telefone,@cep,@rua,@bairro,@cidade,@estado)")
comando.Connection = conn
comando.Parameters.AddWithValue("@nome", txtnome.Text)
comando.Parameters.AddWithValue("@telefone", mkbtelefone.Text)
comando.Parameters.AddWithValue("@cep", mkbcep.Text)
comando.Parameters.AddWithValue("@rua", txtrua.Text)
comando.Parameters.AddWithValue("@bairro", txtbairro.Text)
comando.Parameters.AddWithValue("@cidade", txtcidade.Text)
comando.Parameters.AddWithValue("@estado", txtestado.Text)
' comando.ExecuteNonQuery()
If comando.ExecuteNonQuery Then
MessageBox.Show("Registro salvo com sucesso ...")
Else
MessageBox.Show("Erro ao tentar gravar o registro ...")
End If
conn.Close()
