Ndtyjky

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 

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

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)

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.

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