CODEWITHSUNDEEP
vb.netsqldataadapter
Rodwan Alburie
Rodwan Alburie

Reputation: 9

ExecuteNonQuery() and DA.Update()

I have been trying to insert data from a Data Table into a SQL Server table.

This is the code I'm using for this part:

Public conn As New SqlConnection("Server= DESKTOP-C1JI0OA\SQLEXPRESS; TrustServerCertificate=True; Encrypt=true; TrustServerCertificate=true; Trusted_Connection=True; Database = Fingerprint reader;")
Dim da As New SqlDataAdapter
Dim ds As New DataSet
ds.Tables.Add(dt1)
da.TableMappings.Add("Table", "dbo.fingerprintslogs")
ds.Tables(0).TableName = "Table"

conn.Open()
For Each row As DataRow In ds.Tables(0).Rows

    EmployeeID = row(0)
    Weekday1 = row(1)
    FirstCheckIn = row(2)
    LastCheckout = row(3)

    Using command As New SqlCommand("INSERT INTO Fingerprintslogs (EmployeeID, WeekDay,FirstCheckIn,LastCheckOut ) VALUES (@EmployeeID, @WeekDay, @FirstCheckIn, @LastCheckOut)", conn), adapter As New SqlDataAdapter With {.InsertCommand = command}
        With command.Parameters
            .Add("@EmployeeID", SqlDbType.Int, 0)
            .Add("@WeekDay", SqlDbType.VarChar, 50)
            .Add("@FirstCheckIn", SqlDbType.DateTime, 0)
            .Add("@LastCheckOut", SqlDbType.DateTime, 0)
        End With
        command.Parameters("@EmployeeID").Value = EmployeeID
        command.Parameters("@WeekDay").Value = Weekday1
        command.Parameters("@FirstCheckIn").Value = FirstCheckIn
        command.Parameters("@LastCheckOut").Value = LastCheckout
        command.ExecuteNonQuery()

    End Using
Next
 conn.Close()

Users in previous questions here1 here2 here3 had recommended using a Data Adapter.Update() to insert the data into the SQL DB, when I used .Update on this code (as shown below):

Public conn As New SqlConnection("Server= DESKTOP-C1JI0OA\SQLEXPRESS; TrustServerCertificate=True; Encrypt=true; TrustServerCertificate=true; Trusted_Connection=True; Database = Fingerprint reader;")
    Dim da As New SqlDataAdapter
    Dim ds As New DataSet
    ds.Tables.Add(dt1)
    da.TableMappings.Add("Table", "dbo.fingerprintslogs")
    ds.Tables(0).TableName = "Table"
    
    conn.Open()
    For Each row As DataRow In ds.Tables(0).Rows

        EmployeeID = row(0)
        Weekday1 = row(1)
        FirstCheckIn = row(2)
        LastCheckout = row(3)

        Using command As New SqlCommand("INSERT INTO Fingerprintslogs (EmployeeID, WeekDay,FirstCheckIn,LastCheckOut ) VALUES (@EmployeeID, @WeekDay, @FirstCheckIn, @LastCheckOut)", conn), adapter As New SqlDataAdapter With {.InsertCommand = command}
            With command.Parameters
                .Add("@EmployeeID", SqlDbType.Int, 0)
                .Add("@WeekDay", SqlDbType.VarChar, 50)
                .Add("@FirstCheckIn", SqlDbType.DateTime, 0)
                .Add("@LastCheckOut", SqlDbType.DateTime, 0)
            End With
            command.Parameters("@EmployeeID").Value = EmployeeID
            command.Parameters("@WeekDay").Value = Weekday1
            command.Parameters("@FirstCheckIn").Value = FirstCheckIn
            command.Parameters("@LastCheckOut").Value = LastCheckout
            adapter.Update(ds.Tables(0))

        End Using    
    Next
     conn.Close()

I had the first column of data (sample of data shown after this) repeated 1620 times, (which is actually the total number of rows I have in the data table), but when using command.ExecuteNonQuery() the data were inserted correctly into the DB. I just noticed that it is slightly "Not ordered" I would say, but that is not a big of a problem I guess?

Employee ID Weekday FirstCheckIn LastCheckOut
10 Monday 04/15/2024 0:00:00 04/15/2024 15:04:00
10 Tuesday 04/16/2024 8:46:00 04/16/2024 14:41:00
10 Wednesday 04/17/2024 8:34:00 04/17/2024 0:00:00
10 Thursday 04/18/2024 7:50:00 04/18/2024 15:18:00
10 Saturday 04/20/2024 9:00:00 04/20/2024 15:10:00
10 Sunday 04/21/2024 8:16:00 04/21/2024 15:53:00
10 Monday 04/22/2024 8:41:00 04/22/2024 15:22:00

My question is: why did this happen? And would using command.ExecuteNonQuery() cause any troubles? Or in other terms: what is the reason of the different results between the two scenarios?

Upvotes: 0

Views: 86

Answers (1)

Albert D. Kallal
Albert D. Kallal

Reputation: 49309

I see little reason to create + use a dataset here. A dataset is a "collection" of tables, and you don't have nor need such a collection of tables.

If the column names in dt1 are the same, then you can use that to update the data base.

Hence this:

    Dim strSQL As String =
        "SELECT EmployeeID, WeekDay, FirstCheckIn, LastCheckOut FROM Fingerprintslogs "

    Using conn As New SqlConnection(My.Settings.TEST4)
        Using cmdSQL As New SqlCommand(strSQL, conn)
            conn.Open()
            Dim daupdate As New SqlDataAdapter(cmdSQL)
            Dim cmdBuild As New SqlCommandBuilder(daupdate)
            daupdate.Update(dt1)

        End Using
    End Using

And even your existing code never needed to use some dataset anyway, but if column names match up, then above should work, and work without any looping.

Upvotes: 1

Related Questions