使用 vb.net 数据表和数据行功能在 sql 数据库中插入一条记录

Insert a record in sql database using vb.net datatable and datarow features

提问by Pallab Pal

I am trying to insert a record in sql database using vb.net dataadapter, datatable, and datarow features. I use the following code but it gives me an error:

我正在尝试使用 vb.net 数据适配器、数据表和数据行功能在 sql 数据库中插入一条记录。我使用以下代码，但它给了我一个错误：

Object reference not set to an instance of an object

Object reference not set to an instance of an object

Imports System.Data.SqlClient
Public Class Form1  
    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click

        Dim cn As New SqlConnection("Data Source=.\SQLEXPRESS;Initial Catalog=drpractice;Integrated Security=True")
        Dim da As New SqlDataAdapter
        Dim ds As New DataSet

        Try
            cn.Open()
            da.SelectCommand = New SqlCommand("SELECT * FROM [emp_tbl]", cn)
            da.Fill(ds) 
            Dim dt As New DataTable
            dt = ds.Tables("emp_tbl")

            'Error in this line(Object reference not set to an  instance of an object)'
            Dim dr As DataRow = dt.NewRow() 

            dr.Item("emp_id") = TextBox1.Text.Trim
            dr.Item("emp_name") = TextBox2.Text.Trim
            dr.Item("salary") = TextBox3.Text.Trim
            dr.Item("age") = TextBox4.Text.Trim
            dr.Item("emp_group") = TextBox5.Text.Trim
            dt.Rows.Add(dr)
            da.Update(ds)
            MsgBox("Record Successfully Inserted")
        Catch ex As Exception
            MsgBox(ex.Message)
        End Try
    End Sub
End Class 

回答by user2417929

check this out : Dim dr As DataRow = new dt.NewRow()

看看这个：Dim dr As DataRow = new dt.NewRow()

回答by Eldar Zeynalov

You have done everything good but change the following line:

你已经做的一切都很好，但更改以下行：

da.Update(ds)

As following:

如下：

Dim ESCBuilder As SqlCommandBuilder = New SqlCommandBuilder(da)
    ESCBuilder.GetUpdateCommand()
    da.UpdateCommand = ESCBuilder.GetUpdateCommand()
    da.Update(ds)

回答by ANonymousest

Based on the feedback here and elsewhere, the following code worked for me:

根据此处和其他地方的反馈，以下代码对我有用：

    TestDataSet.GetChanges()
    testTableAdapter.Fill(TestDataSet.log_test)
    log_testDataGridView.Refresh()

What I needed to do was, create a new row, and go get the next value for the Primary Key(VARCHAR, NOT INT, because revisions got an "R" appended to the PK...not my rule...the rule of the company).

我需要做的是，创建一个新行，然后获取主键的下一个值（VARCHAR，NOT INT，因为修订版在 PK 后附加了一个“R”...不是我的规则...规则该公司的）。

I wanted to put the refresh as close to the getting of the PK, which I had last after assigning values to the new datarow so it would get the latest Max +1.

我想让刷新尽可能接近 PK 的获取，这是我在为新数据行分配值后最后一次获取的，因此它会获得最新的 Max +1。

So, I put the above code just before looking up the PK, and after assigning values to the datarow, other than PK. The code above caused the datarow to blank out. So, I put the above code just prior to the creation of the new DataRow.

所以，我把上面的代码放在查找 PK 之前，以及在为数据行分配值之后，而不是 PK。上面的代码导致数据行空白。因此，我将上述代码放在创建新 DataRow 之前。

For my code, this caused the code to get the latest data from the SQL table, then add the new datarow, and finally determine the last PK. Because the data used to populate the datarow is off my form, and there is no caclulations, the code runs fast enough for my needs. I suspect, if the connection to my database was slow, and/or, the number of people running the same process were substantial, I would have errors, such as duplicate PKs.

对于我的代码，这导致代码从SQL表中获取最新数据，然后添加新的数据行，最后确定最后的PK。因为用于填充数据行的数据不在我的表单中，并且没有计算，所以代码运行速度足以满足我的需要。我怀疑，如果与我的数据库的连接速度很慢，和/或运行相同进程的人数很多，我就会出现错误，例如重复的 PK。

My answer to that would be to assign the datarow field values to variables, run the refresh, then assign the variables to the fields and save immediately.

我对此的回答是将数据行字段值分配给变量，运行刷新，然后将变量分配给字段并立即保存。

Perhaps another way would be to get the new PK, then save an empty record, and then fill the record, except that enough of the fields in my table are REQUIRED so I might as well not try creating a blank record first.

也许另一种方法是获取新的 PK，然后保存一个空记录，然后填充该记录，除了我的表中足够多的字段是 REQUIRED 所以我最好不要先尝试创建一个空白记录。

回答by user3525900

Imports System.Data.SqlClient
Public Class Form4
    Dim con As New SqlConnection
    Dim cmd As New SqlCommand
    Dim str As String
    Dim count As Integer

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        con = New SqlConnection("server=SHREE-PC;database=Hospital;INTEGRATED SECURITY=SSPI;")
        con.Open()
      ‘  cmd = New SqlCommand("select * from Doctor", con)
        str = "insert into Doctor values('" & TextBox1.Text & "','" & TextBox2.Text & "','" & TextBox3.Text & "' )"
        cmd = New SqlCommand(str, con)
        count = cmd.ExecuteNonQuery()
        MessageBox.Show(count & " Record inserted")

       con.close()
    End Sub

Imports System.Data.SqlClient
Public Class Form4
    Dim con As New SqlConnection
    Dim cmd As New SqlCommand
    Dim str As String
    Dim count As Integer
   Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
     con = New SqlConnection("server=SHREE-PC;database=Hospital;INTEGRATED SECURITY=SSPI;")
        con.Open()
        cmd = New SqlCommand("select * from Patient", con)
        cmd = New SqlCommand("Delete from Patient where Name ='" & TextBox1.Text & "'", con)
        cmd = New SqlCommand("Delete from Patient where Address='" & TextBox2.Text & "'", con)
        cmd = New SqlCommand("Delete from Patient where Dieses='" & TextBox3.Text & "'", con)
        cmd = New SqlCommand("Delete from Patient where Patient_no=" & TextBox4.Text & "", con)
‘you can take any row in your program
        count = cmd.ExecuteNonQuery()
        MessageBox.Show("Record Deleted")
    End Sub