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

Question

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:

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 

Answer 1

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

Answer 2

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)

Answer 3

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

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.

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.

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.

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.

Answer 4

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