使用vb.net将MS Access行插入数据库

问题描述

所以我试图添加字段到数据库。它是.mdb数据库，微软访问。

So im trying to add fields to a database. It is .mdb database, microsoft access.

表的名称是联系人。

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")

我的错误是INSERT INTO声明。这没有任何意义，无论如何。我做错了什么？

My Error is Syntax error in INSERT INTO statement. Which makes no sense, whatsoever. What am i doing wrong?

编辑*
我通过添加[]围绕certian字段解决了我的严重问题，建议，谢谢。现在我变得...

EDIT*I solvedmy riginal problem by adding [] around certian fields as suggested, thanks. Now I am getting...

没有给一个或多个必需的参数赋值。

No value given for one or more required parameters.

推荐答案

由于其他答案已经解释过，您需要使用方括号

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

在本例中，我为参数（？）插入了12个占位符，然后添加了12个前13个参数。请注意，使用OleDb时，参数集合仍然需要添加具有名称（ @pX ）的参数，但是在执行查询时，将按照占位符的相同顺序选择参数值。

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.

我也使用了using语句来关闭和处理一次性对象，如连接和命令。

Also I have used the Using statement to close and dispose the disposable objects like the connection and the command.

最后，插入查询通常使用OleDbCommand中的ExecuteNonQuery执行，并且不需要使用OleDbAdapter，并且在没有执行SELECT查询时调用Fill加载DataSet

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

这篇关于使用vb.net将MS Access行插入数据库的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！