vba 使用 Visual Studio 2012 (VisualBasic) 将数据输入到 SQL Server

Input Data into SQL Server using Visual Studio 2012 (VisualBasic)

提问by Monkey

I am trying to create a simple form for users to input data to book a course. They fill in the textboxes and press submit, and it needs to send an email to me with the information (I have this working) and it also needs to add it to a database (this is what I am struggling with). I also really need to understand the code so I can use it again in future.

我正在尝试为用户创建一个简单的表单来输入数据来预订课程。他们填写文本框并按提交，它需要向我发送一封包含信息的电子邮件（我有这个工作），它还需要将其添加到数据库中（这是我正在努力解决的问题）。我也确实需要了解代码，以便将来可以再次使用它。

I am making a new database/form just to get this working, then I will implement it to my working form that sends email as well.

我正在制作一个新的数据库/表单只是为了让它工作，然后我会将它实施到我的工作表单中，该表单也发送电子邮件。

Currently I have created a database with one table, containing: ID, Name, Course Name and Address

目前我已经创建了一个包含一个表的数据库，其中包含：ID、名称、课程名称和地址

I have created a form with 3 text boxes (txtName, txtCourseName and txtAddress) with a submit button, but from here I am not sure how to make it so I put values in the textboxes, click the submit button and they are added to the database. Any help would be great, thanks.

我创建了一个包含 3 个文本框（txtName、txtCourseName 和 txtAddress）和一个提交按钮的表单，但从这里我不确定如何制作它，所以我将值放入文本框中，单击提交按钮，然后将它们添加到数据库。任何帮助都会很棒，谢谢。

Fred

弗雷德

采纳答案by SMHasnain

Just create 3 textboxes and 1 button on the form copy all this code, dont forget to name the textboxes accordingly

只需在表单上创建 3 个文本框和 1 个按钮复制所有这些代码，不要忘记相应地命名文本框

Imports System.Data
Imports System.Data.OleDb

Public Class Form1

    Dim Con As OleDbConnection
    Dim cmd As New OleDbCommand

    Dim conString As String = "This part you have to do it your self you can go to this link to check whick string works for u   http://www.connectionstrings.com/"

    Public Sub CourseSave()

        Try

            Dim con As New OleDbConnection
            con.ConnectionString = conString
            con.Open()

            cmd.Connection = con

            cmd.CommandText = "insert into tablename(Name, [Course Name], Address) values('" & Me.txtName.Text & "', '" & Me.txtCourseName.Text & "', '" & Me.txtAddress.Text & "')"


            cmd.ExecuteNonQuery()

            con.Close()
            MsgBox("New Course Saved")

            con.Close()
            con.Dispose()

        Catch ex As Exception
            MsgBox(ex.Message)

        End Try

    End Sub


    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        CourseSave()
    End Sub
End Class

if you still get errors post me the error I might be able to help you.

如果您仍然遇到错误，请将错误寄给我，我也许可以帮助您。

回答by JonTout

If you are comfortable with adding a dataset to your project,

如果您对向项目添加数据集感到满意，

(Right Click Project > Add > New Item > Data > Dataset)

（右键单击“项目”>“添加”>“新项目”>“数据”>“数据集”）

Add a Table Adapter and build a SQL Select statement similar to the one below using Query Builder (which will also create your INSERTstatement)

添加一个表适配器并使用查询构建器构建一个类似于下面的 SQL Select 语句（这也将创建你的INSERT语句）

Select txtName, txtCourseName , txtAddress From MyTable

Select txtName, txtCourseName , txtAddress From MyTable

You can then call the Insert statement from your button code, along the lines of...

然后，您可以从按钮代码中调用 Insert 语句，沿着...

Dim sta As New YourDataTableAdapter.TableAdapter
sta.Insert(txtName, txtCourseName , txtAddress)
sta.Dispose()

Dim sta As New YourDataTableAdapter.TableAdapter
sta.Insert(txtName, txtCourseName , txtAddress)
sta.Dispose()

http://msdn.microsoft.com/en-us/library/6sb6kb28.aspx

http://msdn.microsoft.com/en-us/library/6sb6kb28.aspx