嘿伙计们如何在VB.NET中连接数据并将数据添加到ms访问数据库？ [英] Hey guys how can I connect and add data to ms access database in VB.NET ?

问题描述

它仍然给我错误！有时语法错误（在查询表达式中缺少操作）

，有时这样：不允许更改'ConnectionString'属性。连接的当前状态是打开的。}



我尝试了什么：

it is still giving me errors !!! sometimes syntax errors (missing operation in query expression)
and sometimes this :"Not allowed to change the 'ConnectionString' property. The connection's current state is open."}

What I have tried:

Dim provider As String
    Dim dataFile As String
    Dim connString As String
    Dim myConnection As OleDbConnection = New OleDbConnection
    Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

    End Sub

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        provider = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source ="

        dataFile = "D:\Dat.accdb;"

        connString = provider & dataFile
        myConnection.ConnectionString = connString

        myConnection.Open()
        Dim str As String
        str = "insert into Dat ([No], [Date], [Name], [Gender],[Age],[Phone],[Desease],[Address],[Building],[Room No],[Room Type],[Price]) VALUES (@No,@Date,@Name,@Gender,@Age,@Phone,@Desease,@Address,@Building,@Room No,@Room Type,@Price) "


        Dim cmd As OleDbCommand = New OleDbCommand(str, myConnection)
        cmd.Parameters.Add(New OleDbParameter("No", CType(TextBox1.Text, String)))
        cmd.Parameters.Add(New OleDbParameter("Name", CType(TextBox2.Text, String)))
        cmd.Parameters.Add(New OleDbParameter("Gender", CType(TextBox3.Text, String)))
        cmd.Parameters.Add(New OleDbParameter("Age", CType(TextBox4.Text, String)))
        cmd.Parameters.Add(New OleDbParameter("Phone", CType(TextBox6.Text, String)))
        cmd.Parameters.Add(New OleDbParameter("Desease", CType(TextBox7.Text, String)))
        cmd.Parameters.Add(New OleDbParameter("Address", CType(TextBox8.Text, String)))
        cmd.Parameters.Add(New OleDbParameter("Building", CType(TextBox10.Text, String)))
        cmd.Parameters.Add(New OleDbParameter("Room No", CType(TextBox11.Text, String)))
        cmd.Parameters.Add(New OleDbParameter("Room Type", CType(TextBox12.Text, String)))
        cmd.Parameters.Add(New OleDbParameter("Price", CType(TextBox13.Text, String)))
        Try
            cmd.ExecuteNonQuery()
            cmd.Dispose()
            myConnection.Close()
            TextBox1.Clear()
            TextBox2.Clear()
            TextBox3.Clear()
            TextBox4.Clear()
            TextBox5.Clear()
        Catch ex As Exception
            MsgBox(ex.Message)
        End Try
    End Sub

    Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
        Me.Close()

    End Sub
End Class

推荐答案

如果您声明一个连接对象 - 并且您这样做 - 您应该始终确保它已关闭 - 如果您的查询失败，连接将保持打开状态，然后您将收到您描述的错误。在Try ... Catch上使用Finally块，然后关闭那里的连接。



然后......假设你没有懒惰并创建了一个数据库是基于字符串的 - 即Age，Price等存储为数字而不是字符串 - 然后您需要验证用户输入的值以防止INSERT失败。最好的方法是使用适当的TryParse方法在方法开头转换文本框内容，并向用户报告问题而不是向前推进。用户会犯错误：如果你不允许这样做，那么你的应用程序就会失败，或者更糟糕的是，你的数据会变成垃圾。

如果你把它们存储为字符串，那么它会回来咬你很快......



和BTW：帮自己一个忙，并停止使用Visual Studio默认名称 - 你可能还记得TextBox8是手机号码今天，但是当你必须在三周内修改它时，你会吗？使用描述性名称 - 例如tbMobileNo - 您的代码变得更容易阅读，更自我记录，更易于维护 - 并且编码速度更快，因为Intellisense可以通过三次击键来tbMobile，其中TextBox8需要思考大概和8次击键......

If you declare a single connection object - and you do - you should always ensure that it is closed - if your query fails, the connection will remain open and then you will get the errors you describe. Use a Finally block on your Try ... Catch, and close the connection there.

Then ... assuming you haven't been lazy and created a database that is all string based - i.e. Age, Price, and so forth are stored as numbers instead of strings - then you need to verify the values the user entered to prevent your INSERT failing. The best way to do that is your use the appropriate TryParse method to convert the textbox content at the beginning of your method, and report problems to the user instead of plunging ahead. Users make mistakes: if you don't allow for that then your app will fail, or worse, your data will be rubbish.
If you have stored them as strings, that will come back to bite you fairly soon...

And BTW: Do yourself a favour, and stop using Visual Studio default names for everything - you may remember that "TextBox8" is the mobile number today, but when you have to modify it in three weeks time, will you then? Use descriptive names - "tbMobileNo" for example - and your code becomes easier to read, more self documenting, easier to maintain - and surprisingly quicker to code because Intellisense can get to to "tbMobile" in three keystrokes, where "TextBox8" takes thinking about and 8 keystrokes...

这篇关于嘿伙计们如何在VB.NET中连接数据并将数据添加到ms访问数据库？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！