Issue with SQL Records Not Updating

[DaniwebOS]

Hi guys, I added a extra line of code to test for records in the database and if they exist to update them before inserting them instead. For some odd reason it's not working and it simply just inserts the data causing duplicates. Any suggestions?

<%@ Import Namespace="System.Data"%>
<%@ Import Namespace="System.Data.OleDb"%>
<script runat="server">
    Sub btnUpload_OnClick(sender As Object, e As EventArgs)
        Dim strPath As String = "csv/"
        Dim dt As DataTable
        If Me.fiUpload.HasFile Then

            Me.fiUpload.SaveAs(Server.MapPath(strPath & fiUpload.FileName))

            '*** Read CSV to DataTable ***'
            dt = CsvCreateDataTable(strPath,fiUpload.FileName)

            '*** Insert to Database ***'
            InsertToDatabase(dt)
        End IF  
    End Sub

    '*** Convert CSV to DataTable ***'
    Function CsvCreateDataTable(ByVal strPath,ByVal strFilesName) As DataTable
        Dim objConn As New OleDbConnection
        Dim dtAdapter As OleDbDataAdapter 
        Dim dt As New DataTable


        Dim strConnString As String
        strConnString = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source="&Server.MapPath(strPath) & _
";Extended Properties='TEXT;HDR=Yes;FMT=Delimited;Format=Delimited(,)'"


        objConn = New OleDbConnection(strConnString)
        objConn.Open()

        Dim strSQL As String        
        strSQL = "SELECT * FROM " & strFilesName

        dtAdapter = New OleDbDataAdapter(strSQL, objConn)
        dtAdapter.Fill(dt)

        dtAdapter = Nothing

        objConn.Close()
        objConn = Nothing

        Return dt '*** Return DataTable ***'

    End Function

    Function InsertToDatabase(ByVal dt)

        Dim objConn As System.Data.OleDb.OleDbConnection
        Dim objCmd As System.Data.OleDb.OleDbCommand
        Dim strConnString,strSQL As String
        Dim i As Integer


        strConnString = "Provider=SQLOLEDB;" & _
                        "Server=test-sql\testing;" & _
                        "Database=Test;"& _
                        "uid=test;" & _
                        "pwd=test!!@3242gfgf8143f5d;"
        objConn = New System.Data.OleDb.OleDbConnection(strConnString)
        objConn.Open()

        '*** Loop Insert ***'
        For i = 0 To dt.Rows.Count - 1  
        Try 
                'EXTRA NEW LINE OF CODE TO UPDATE RECORDS
                Dim updateCmd As String = "UPDATE TESThrtime Set sickTime = @sickTime, @vacationTime WHERE empFullName = '@empFullName'" 

                    strSQL = "INSERT INTO MARRhrtime (empFullName,sickTime,VacationTime) " & _
                     "VALUES ('" & dt.Rows(i)("empFullName") & "','" & dt.Rows(i)("sickTime") & "','" & dt.Rows(i)("vacationTime") & "')"
                     objCmd = New System.Data.OleDb.OleDbCommand() 
                 With objCmd 
                        .Connection = objConn 
                        .CommandType = CommandType.Text 
                        .CommandText = strSQL 
                 End With 
                    objCmd.ExecuteNonQuery() 
                    Me.lblText.Text = Me.lblText.Text & "[" & dt.Rows(i)("empFullName") & "] Inserted <br>"
                Catch err As Exception 
                    Me.lblText.Text = Me.lblText.Text & "[" & dt.Rows(i)("empFullName") & "] Not Insert <br>"
                End Try 

        Next            
        objCmd = Nothing
        objConn.Close()
        objConn = Nothing

    End Function

</script>

[JorgeM]

It could be me that I may be confused, but where is the code that handles " code to test for records in the database and if they exist to update them before inserting them instead"

[DaniwebOS]

Line 68-69

[JorgeM]

Yes, I did see that, but line 69 is only creating a variable called "updateCmd" as assigning a string value to it. there is no SQL related code here..

Dim updateCmd As String = "UPDATE TESThrtime Set sickTime = @sickTime, @vacationTime WHERE empFullName = '@empFullName'"

[DaniwebOS]

How would you correct this so that it actually does what it's supposed to. I want it to do the following

try
 if it exists 
        update sql
        else
          insert sql

[JorgeM]

Ok, so the concept would be something like this...

Perform a SQL Select, I assume you want to know if empFullName exists for a certain user. So for example...

select count(*) from TESThrtime where empFullName = @empFullName

Save the results of that query to a variable. if the results are > 0 then you know that there is an existing record.

Dim empExists as Integer = 0
 ' Do you SQL stuff and assign value to empExists
If empExists <> 0 Then
 ' update SQL
Else
 ' insert into SQL
End if

[DaniwebOS]

The code still inserts it, basically ignores the new code I created and it makes sense that it does because how would it handle a csv file with 100 rows to insert without creating or including it into my for loop. Basically initally without including the new code it would convert a csv file into a datatable which then gets read and inserted into the database by a loop that reads it until it reaches the last line. What I'd like to do is if a new csv file gets inserted and the employees already exist to update their records only.

<%@ Import Namespace="System.Data"%>
<%@ Import Namespace="System.Data.OleDb"%>
<script runat="server">
    Sub btnUpload_OnClick(sender As Object, e As EventArgs)
        Dim strPath As String = "csv/"
        Dim dt As DataTable
        If Me.fiUpload.HasFile Then

            Me.fiUpload.SaveAs(Server.MapPath(strPath & fiUpload.FileName))

            '*** Read CSV to DataTable ***'
            dt = CsvCreateDataTable(strPath,fiUpload.FileName)

            '*** Insert to Database ***'
            InsertToDatabase(dt)
        End IF  
    End Sub

    '*** Convert CSV to DataTable ***'
    Function CsvCreateDataTable(ByVal strPath,ByVal strFilesName) As DataTable
        Dim objConn As New OleDbConnection
        Dim dtAdapter As OleDbDataAdapter 
        Dim dt As New DataTable


        Dim strConnString As String
        strConnString = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source="&Server.MapPath(strPath) & _
";Extended Properties='TEXT;HDR=Yes;FMT=Delimited;Format=Delimited(,)'"


        objConn = New OleDbConnection(strConnString)
        objConn.Open()

        Dim strSQL As String        
        strSQL = "SELECT * FROM " & strFilesName

        dtAdapter = New OleDbDataAdapter(strSQL, objConn)
        dtAdapter.Fill(dt)

        dtAdapter = Nothing

        objConn.Close()
        objConn = Nothing

        Return dt '*** Return DataTable ***'

    End Function

    Function InsertToDatabase(ByVal dt)

        Dim objConn As System.Data.OleDb.OleDbConnection
        Dim objCmd As System.Data.OleDb.OleDbCommand
        Dim strConnString,strSQL As String
        Dim i As Integer


        strConnString = "Provider=SQLOLEDB;" & _
                        "Server=test;" & _
                        "Database=Test;"& _
                        "uid=test;" & _
                        "pwd=wefsf4r35453fd;"
        objConn = New System.Data.OleDb.OleDbConnection(strConnString)
        objConn.Open()

        'New Code
        Dim selectEmpFullName = "SELECT COUNT(*) FROM TESThrtime WHERE = @empFullName"
        Dim empExists As Integer = 0 
        if empExists <> 0 
        Dim updateSQL = "UPDATE TESThrtime Set sickTime = @sickTime, @vacationTime WHERE empFullName = '@empFullName'" 
        else 

        '*** Loop Insert ***'
        For i = 0 To dt.Rows.Count - 1  
        Try                 
                    strSQL = "INSERT INTO TESThrtime (empFullName,sickTime,VacationTime) " & _
                     "VALUES ('" & dt.Rows(i)("empFullName") & "','" & dt.Rows(i)("sickTime") & "','" & dt.Rows(i)("vacationTime") & "')"
                     objCmd = New System.Data.OleDb.OleDbCommand() 
                 With objCmd 
                        .Connection = objConn 
                        .CommandType = CommandType.Text 
                        .CommandText = strSQL 
                 End With 
                    objCmd.ExecuteNonQuery() 
                    Me.lblText.Text = Me.lblText.Text & "[" & dt.Rows(i)("empFullName") & "] Inserted <br>"
                Catch err As Exception 
                    Me.lblText.Text = Me.lblText.Text & "[" & dt.Rows(i)("empFullName") & "] Not Insert <br>"
                End Try 

        Next            
        objCmd = Nothing
        objConn.Close()
        objConn = Nothing
        End if
    End Function

</script>

[JorgeM]

What I'd like to do is if a new csv file gets inserted and the employees already exist to update their records only.

Unfortunately, I do not have time develop this code for you. You are on the right track. however, the logic you need to follow is that while you loop through the rows in the datatable you created, that is when you will check to see if the record exists.

Start Loop
Read the first row of the datatable
Find the column with the employee Name
Perform a sql select query to see if the employee exists
If the employee exists
    Update
Else
    Insert
End If
Catch errors
Next Record
All records completed end the function

[DaniwebOS]

It's okay, I'll continue to work on this for a few more days. Hopefully I'll figure this out. Before you had posted that last post that's exactly what I had in mind. So the following needs to happen:
empExists = SELECT COUNT(*) FROM TESThrtime WHERE = @empFullName
if empExists <> 0
update...

The question is could empExists = SELECT COUNT(*) FROM TESThrtime WHERE = dt.Rows(i)(@empFullName)
work?

[JorgeM]

Yes but you simply need to get the correct syntax and also add in an equal sign in the where clause. If you get stuck, troubleshoot with actual values to make sure the query works then replace it with variables.

[DaniwebOS]

Hi again,

I wrote this code, you'll notice line 65 is new along with 70-77 and the end if line on 91. However, I can't figure out why its not compiling. I'll provide the code first and then I'll provide the error:

<%@ Import Namespace="System.Data"%>
<%@ Import Namespace="System.Data.OleDb"%>
<script runat="server">
    Sub btnUpload_OnClick(sender As Object, e As EventArgs)
        Dim strPath As String = "csv/"
        Dim dt As DataTable
        If Me.fiUpload.HasFile Then

            Me.fiUpload.SaveAs(Server.MapPath(strPath & fiUpload.FileName))

            '*** Read CSV to DataTable ***'
            dt = CsvCreateDataTable(strPath,fiUpload.FileName)

            '*** Insert to Database ***'
            InsertToDatabase(dt)
        End IF  
    End Sub

    '*** Convert CSV to DataTable ***'
    Function CsvCreateDataTable(ByVal strPath,ByVal strFilesName) As DataTable
        Dim objConn As New OleDbConnection
        Dim dtAdapter As OleDbDataAdapter 
        Dim dt As New DataTable


        Dim strConnString As String
        strConnString = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source="&Server.MapPath(strPath) & _
";Extended Properties='TEXT;HDR=Yes;FMT=Delimited;Format=Delimited(,)'"


        objConn = New OleDbConnection(strConnString)
        objConn.Open()

        Dim strSQL As String        
        strSQL = "SELECT * FROM " & strFilesName

        dtAdapter = New OleDbDataAdapter(strSQL, objConn)
        dtAdapter.Fill(dt)

        dtAdapter = Nothing

        objConn.Close()
        objConn = Nothing

        Return dt '*** Return DataTable ***'

    End Function

    Function InsertToDatabase(ByVal dt)

        Dim objConn As System.Data.OleDb.OleDbConnection
        Dim objCmd As System.Data.OleDb.OleDbCommand
        Dim strConnString,strSQL As String
        Dim i As Integer


        strConnString = "Provider=SQLOLEDB;" & _
                        "Server=test;" & _
                        "Database=Test;"& _
                        "uid=dasdad;" & _
                        "pwd=dfadfawefasdfsf;"
        objConn = New System.Data.OleDb.OleDbConnection(strConnString)
        objConn.Open()
        'New code
        Dim updateSQL
        '*** Loop Insert ***'
        For i = 0 To dt.Rows.Count - 1  
        Try

        Dim empExists As Integer = 0 
        empExists = "SELECT COUNT(*) FROM TESThrtime WHERE = ('" & dt.Rows(i)("@empFullName")& "')"

        if (empExists <> 0) then

         updateSQL = "UPDATE TESThrtime Set sickTime = @sickTime, @vacationTime WHERE empFullName = ('" & dt.Rows(i)("@empFullName")&"')" then

            Else
                    strSQL = "INSERT INTO TESThrtime (empFullName,sickTime,VacationTime) " & _
                     "VALUES ('" & dt.Rows(i)("empFullName") & "','" & dt.Rows(i)("sickTime") & "','" & dt.Rows(i)("vacationTime") & "')"
                     objCmd = New System.Data.OleDb.OleDbCommand() 
                 With objCmd 
                        .Connection = objConn 
                        .CommandType = CommandType.Text 
                        .CommandText = strSQL 
                 End With 
                    objCmd.ExecuteNonQuery() 
                    Me.lblText.Text = Me.lblText.Text & "[" & dt.Rows(i)("empFullName") & "] Inserted <br>"
                Catch err As Exception 
                    Me.lblText.Text = Me.lblText.Text & "[" & dt.Rows(i)("empFullName") & "] Not Insert <br>"
                End Try 
         End If
        Next            
        objCmd = Nothing
        objConn.Close()
        objConn = Nothing

    End Function


</script>

Compiler Error Message: BC30205: End of statement expected.

Source Error:

Line 73: if (empExists <> 0) then
Line 74:
Line 75: updateSQL = "UPDATE MARRhrtime Set sickTime = @sickTime, @vacationTime WHERE empFullName = ('" & dt.Rows(i)("@empFullName")&"')" then
Line 76:
Line 77: Else

Line 75 where bold apparantly is where the problem exists

[DaniwebOS]

Made some progress but I believe my logic is apparantly wrong because it will compile but it won't insert or even update the database and it appears to go to the catch error line because it inserts NOT INSERTED regardless of the results...

Here's what I've done

<%@ Import Namespace="System.Data"%>
<%@ Import Namespace="System.Data.OleDb"%>
<script runat="server">
    Sub btnUpload_OnClick(sender As Object, e As EventArgs)
        Dim strPath As String = "csv/"
        Dim dt As DataTable
        If Me.fiUpload.HasFile Then

            Me.fiUpload.SaveAs(Server.MapPath(strPath & fiUpload.FileName))

            '*** Read CSV to DataTable ***'
            dt = CsvCreateDataTable(strPath,fiUpload.FileName)

            '*** Insert to Database ***'
            InsertToDatabase(dt)
        End IF  
    End Sub

    '*** Convert CSV to DataTable ***'
    Function CsvCreateDataTable(ByVal strPath,ByVal strFilesName) As DataTable
        Dim objConn As New OleDbConnection
        Dim dtAdapter As OleDbDataAdapter 
        Dim dt As New DataTable


        Dim strConnString As String
        strConnString = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source="&Server.MapPath(strPath) & _
";Extended Properties='TEXT;HDR=Yes;FMT=Delimited;Format=Delimited(,)'"


        objConn = New OleDbConnection(strConnString)
        objConn.Open()

        Dim strSQL As String        
        strSQL = "SELECT * FROM " & strFilesName

        dtAdapter = New OleDbDataAdapter(strSQL, objConn)
        dtAdapter.Fill(dt)

        dtAdapter = Nothing

        objConn.Close()
        objConn = Nothing

        Return dt '*** Return DataTable ***'

    End Function

    Function InsertToDatabase(ByVal dt)

        Dim objConn As System.Data.OleDb.OleDbConnection
        Dim objCmd As System.Data.OleDb.OleDbCommand
        Dim strConnString,strSQL As String
        Dim i As Integer


        strConnString = "Provider=SQLOLEDB;" & _
                        "Server=test;" & _
                        "Database=Test;"& _
                        "uid=dasdad;" & _
                        "pwd=dasdaefadaddv;"
        objConn = New System.Data.OleDb.OleDbConnection(strConnString)
        objConn.Open()
        'New code
        Dim updateSQL
        '*** Loop Insert ***'
        For i = 0 To dt.Rows.Count - 1  
        Try

            Dim empExists As Integer = 0
            empExists = "SELECT COUNT(*) FROM TESThrtime WHERE = ('" & dt.Rows(i)("@empFullName")& "')"

                if (empExists <> 0) then
                         updateSQL = "UPDATE TESThrtime Set sickTime = @sickTime, @vacationTime WHERE empFullName = ('" & dt.Rows(i)("@empFullName")&"')"
                         objCmd = New System.Data.OleDb.OleDbCommand() 
                        With objCmd 
                                .Connection = objConn 
                                .CommandType = CommandType.Text 
                                .CommandText = strSQL
                        End With 
                        objCmd.ExecuteNonQuery() 
                        'No need for anything to return and confirm the employee has been updated
                 Else
                        strSQL = "INSERT INTO TESThrtime (empFullName,sickTime,vacationTime) " & _
                        "VALUES ('" & dt.Rows(i)("empFullName") & "','" & dt.Rows(i)("sickTime") & "','" & dt.Rows(i)("vacationTime") & "')"
                        objCmd = New System.Data.OleDb.OleDbCommand() 
                        With objCmd 
                                .Connection = objConn 
                                .CommandType = CommandType.Text 
                                .CommandText = strSQL 
                        End With
                        objCmd.ExecuteNonQuery() 
                        Me.lblText.Text = Me.lblText.Text & "[" & dt.Rows(i)("empFullName") & "] Inserted <br>"
                        End If 
                        Catch err As Exception 
                        Me.lblText.Text = Me.lblText.Text & "[" & dt.Rows(i)("empFullName") & "] Not Insert <br>"

        End Try 

        Next            
        objCmd = Nothing
        objConn.Close()
        objConn = Nothing

    End Function


</script>

I'm not sure what I'm missing but the code worked great before I tried to develop the update code. Regardless it, shouldn't go to the ELSE statement if the "if then" fails... I feel like its skipping if then + the else condition and going straight to the catch error.

[JorgeM]

This SQL query doesn't look correct to me. Always test your queries before using variables to make sure actual values work and the syntax is correct.

In this example..
empExists = "SELECT COUNT(*) FROM TESThrtime WHERE = ('" & dt.Rows(i)("@empFullName")& "')"

A typical SQL query with a WHERE clause would look like...

SELECT field FROM table WHERE field = value

In your example code, after the WHERE I dont see you evaluating the field with a value. I would guess that your syntax should be...

empExists = "SELECT COUNT(*) FROM TESThrtime WHERE empFullName = ('" & dt.Rows(i)(""@empFullName"")& "')"

In addition, as I mentioned before, all you are doing here is assigning the SQL string to a variable called empExists. Where are you running this query? I dont see that in your code.

[DaniwebOS]

I'm guessing you haven't inserted a datatable before but how this originally was working before the update code was in this manner:

User clicks on the upload button, on click runs the command that inserts the file into a folder on our server. The file has rows with data seperated by three columns, which gets read line by line and converted into a datatable. Now the datatable needs to go someone so it gets inserted to the database which dt contains all the rows of data. All I was trying to do is to create a if else statement that supports the logic of updating the database if the user's full name already exist else run the insert if it doesn't exist.

Before all of this:

              strSQL = "INSERT INTO TESThrtime (empFullName,sickTime,vacationTime) " & _
                        "VALUES ('" & dt.Rows(i)("empFullName") & "','" & dt.Rows(i)("sickTime") & "','" & dt.Rows(i)("vacationTime") & "')"

The above command works great if I remove all of the update code, however you would traditionally see the following

INSERT INTO TESThrtime (empFullname, sickTime, vacationTime) Values = (@empFullName,@sickTime,@vacationTime)

but since the data table contains what were trying to insert we need to include dt.Rows(i) and have the loop go down row by row until it reaches the last line. So my logic was to have dt.Rows(i) that contains the empfullname and their times to UPDATE if dt.Rows(i) "which ever row its on at the time" already exists. So let's remember dt holds the data and to accomplish anything we need to include .Rows(i) which is where we get dt.Rows(i)

[JorgeM]

I'm guessing you haven't inserted a datatable before

Actually that assumption is not correct.

****

Yes, i have an idea of what you are trying to do. I am simply pointing out that this part of your code isn't doing anything except assigning a value to a string.

Please take a look at the following lines of your code...

Dim empExists As Integer = 0
empExists = "SELECT COUNT(*) FROM TESThrtime WHERE = ('" & dt.Rows(i)("@empFullName")& "')"
if (empExists <> 0) then
   updateSQL = "UPDATE TESThrtime Set sickTime = @sickTime, @vacationTime WHERE empFullName = ('" & dt.Rows(i)("@empFullName")&"')"
   objCmd = New System.Data.OleDb.OleDbCommand() 
   With objCmd 
        .Connection = objConn 
        .CommandType = CommandType.Text 
        .CommandText = strSQL
   End With 
   objCmd.ExecuteNonQuery() 

• The first line creates a variable and assigns it a value of 0
• The second line simply take a string, in this case its your SQL Select statement which is formatted incorrectly. You havent exectuted any SQL here.
• The third line starts you If..Then block. Since you havent executed a SQL query yet, this If statement is True because it will always be true at this point... empExists doesnt = 0 at this point, it equals the string that you assigned in the first line.

lets continue, now we are in your true part of the if..block...Next you are assigning a string value once again to a variable called "updateSQL". No SQL action has been taken yet.

The process continues...eventually you assign strSQL to the .CommandText instead of your updateSQL variable so that doesnt appear to be correct either.

In any case, I've been trying to help you not frustrate you. I could be completely off in my guidance here which I appologize for... hopefully someone else can give you the assistance you need.

[DaniwebOS]

I surely appreciate your help, not frustrated more like confused. In the first line as you've pointed out the variable gets assigned a 0 in preperation for the if condition to begin its process. But before the if condition we have assigned empExists to a query that should be correct... still don't get how its not. It's similar to a named parameter... just were dealing with data tables... so let's say this is the first time this datatable was uploaded its currently on its first row. First row of datatable should run the query of empExists = .... to determine if it already exists in the database. If it does we give empExists a value other than 0 and have it not equal to 0. Therefore it updates and if it does equal 0 we then follow through the "else". I may have my if and else backwards too since <> does confuse me all the time.

[JorgeM]

so let's say this is the first time this datatable was uploaded its currently on its first row. First row of datatable should run the query of empExists = .... to determine if it already exists in the database.

Lets stop at this point to clarify something to help you have a better understanding... this phrase in your response.. "should run the query of empExists".

That is incorrect. This line of code does NOT execute any SQL.

empExists = "SELECT COUNT(*) FROM TESThrtime WHERE = ('" & dt.Rows(i)("@empFullName")& "')"

So what does this line of code actually do?

It only takes a string of data and assigns it to the variable. No SQL code is executed here. You can only execute SQL code by using methods such as ExecuteNonQuery(), or ExecuteScalar(), or ExecuteReader() just like you are trying to do in line 11 of my previous post.

[DaniwebOS]

I see, so what I failed to do where two things that I apologize for.

1st thing - the query isn't correct because i'm missing the column it's supposed to equal to which truly is

empExists = "SELECT COUNT(*) FROM TESThrtime WHERE empFullName = ('" & dt.Rows(i)("@empFullName")& "')"

2nd thing I failed to do was to
write the following: Also the empExists is supposed to query a count of empFullName dt row is currently on and see which if condition to follow

            Dim empExists As Integer = 0
            empExists = "SELECT COUNT(*) FROM MARRhrtime WHERE empFullname = ('" & dt.Rows(i)("@empFullName")& "')"
            objCmd = New System.DataOleDb.OleDbCommand()
            With objCmd
                    .Connection = objConn
                    .CommandType = CommandType.Text
                    .CommandText = empExists
                if (empExists <> 0) then
                         updateSQL = "UPDATE MARRhrtime Set sickTime = @sickTime, @vacationTime WHERE empFullName = ('" & dt.Rows(i)("@empFullName")&"')"
                         objCmd = New System.Data.OleDb.OleDbCommand() 
                        With objCmd 
                                .Connection = objConn 
                                .CommandType = CommandType.Text 
                                .CommandText = strSQL
                        End With 
                        objCmd.ExecuteNonQuery() 
                        ...
                        ...
                        ..
                        .

[JorgeM]

You still have problems although you are getting closer. The code above isnt going to work yet. While you updatd line 7, you didnt execute the query. You have to execute it. Also, you are missing a block of code to read the results.You'll probably use ExecuteScalar()...its meant for queries that return one row, one column. When you read with ExecuteScalar(), the variable that you used will contain the count you are trying to do regarding empExists.

For example... dim results as integer = objCmd.ExcecuteScalar()

then... on line 8 if (results = 1) then (you have to be very specific on how you check to make sure there is an existing employee. Is it possible to have 2 records? then this would be false, but there are two records so you may need further quality control checks in place.)

There are various ways to implement your logic.

I'm writing this all from the top of my head....syntax and actual code may vary.

Then in line 10, while you assign that string to the variable, again, you arent executing it. On line 14, you are using the variable strSQL instead of updateSQL.

edit-- line 1 should be Dim empExists As String = "SELECT COUNT(*) FROM MARRhrtime WHERE empFullname = ('" & dt.Rows(i)(""@empFullName"") & "')"

you can get rid of line2. Also, notice the double, double quotes around @empFullName. The purpose is to escape the doublequotes you added.

[DaniwebOS]

Dim updateSQL
        '*** Loop Insert ***'
        For i = 0 To dt.Rows.Count - 1  
        Try

            Dim empExists As Integer = "SELECT COUNT(*) FROM TESThrtime WHERE empFullname = ('" & dt.Rows(i)("@empFullName")& "')"
            objCmd = New System.Data.OleDb.OleDbCommand() 
            With objCmd
                    .Connection = objConn
                    .CommandType = CommandType.Text
                    .CommandText = empExists
            End With
                    objCmd.ExecuteNonQuery()
                if (empExists <> 0) then
                         updateSQL = "UPDATE TESThrtime Set sickTime = ('" & dt.Rows(i)("sickTime") & "','" & dt.Rows(i)("vacationTime") & "') WHERE empFullName = ('" & dt.Rows(i)("@empFullName")&"')"
                         objCmd = New System.Data.OleDb.OleDbCommand() 
                        With objCmd 
                                .Connection = objConn 
                                .CommandType = CommandType.Text 
                                .CommandText = updateSQL
                        End With 
                        objCmd.ExecuteNonQuery() 
                        'No need for anything to return and confirm the employee has been updated
                 Else
                        strSQL = "INSERT INTO TESThrtime (empFullName,sickTime,vacationTime) " & _
                        "VALUES ('" & dt.Rows(i)("empFullName") & "','" & dt.Rows(i)("sickTime") & "','" & dt.Rows(i)("vacationTime") & "')"
                        objCmd = New System.Data.OleDb.OleDbCommand() 
                        With objCmd 
                                .Connection = objConn 
                                .CommandType = CommandType.Text 
                                .CommandText = strSQL 
                        End With
                        objCmd.ExecuteNonQuery() 
                        Me.lblText.Text = Me.lblText.Text & "[" & dt.Rows(i)("empFullName") & "] Inserted <br>"
                        End If 
                        Catch err As Exception 
                        Me.lblText.Text = Me.lblText.Text & "[" & dt.Rows(i)("empFullName") & "] Not Insert <br>"


        End Try 

I corrected a few things you've pointed out, I executed empExists now, assigned empExists directly to the query, and attemped to escape the doublequotes but realized there shouldn't be a reason to do so. Most importantly it's telling me there's an issue when I do that. If you count there shouldn't be a reason to add those double quotes. It won't compile if I do.

Also, I'm not sure about ExecuteScalar... isn't empExists the code that does two things for me? The first after it executes it then holds the value of the count of empFullName which the if condition reads the variable to compare if its not equal to 0?

[JorgeM]

and attemped to escape the doublequotes but realized there shouldn't be a reason to do so.

sorry...I got lost in tracking the open and closed quotes in your code and also didnt notice that some of those string where used for passing parameters in methods.. I'll leave the quoting syntax to you as I havent been copying and pasting your code in an IDE myself. I'm doing this visually which makes it harder for me.

Line 6's variable cant be an integer since you are assigning a string. That needs to be a string.
Line 13 cant be.. ExectuteNonQuery, because your intention is to perform a SQL Select query. Thats a query. a NonQuery is an UPDATE or an INSERT.

here is an example (not exactly for your code) of how to use ExecuteScalar() when you are interested in retreiving a single value from SQL. This should help you understand how to use ExecuteScalar() and apply it to your example.

Dim strConnection As String = ConfigurationManager.ConnectionStrings("ConnectionString").ConnectionString
Dim strSelect As String = "SELECT COUNT(*) FROM TESThrtime WHERE empFullname = ('" & dt.Rows(i)("@empFullName")& "')"

Dim con As New SqlConnection(strConnection)
Dim cmd As New SqlCommand()
cmd.Connection = con
cmd.CommandType = CommandType.Text
cmd.CommandText = strSelect

con.Open()
Dim empExists As Integer = DirectCast(cmd.ExecuteScalar(), Int32)
con.Close()

If empExists >= 1 Then
 lblMsg.Text="At least one empFullname matched"
Else
 lblMsg.Text = "No match found for that empFullname"
End If

[DaniwebOS]

For i = 0 To dt.Rows.Count - 1  
        Try

            Dim strSelect As String = "SELECT COUNT(*) FROM TESThrtime WHERE empFullName = ('" & dt.Rows(i)("empFullName")& "')"
            objCmd = New System.Data.OleDb.OleDbCommand() 
            With objCmd
                    .Connection = objConn
                    .CommandType = CommandType.Text
                    .CommandText = strSelect
            End With
                    'objCmd.ExecuteReader()
            Dim empExists As Integer = DirectCast(objCmd.ExecuteScalar(),Int32)
                if (empExists >= 1) then
                         updateSQL = "UPDATE TESThrtime Set sickTime = ('" & dt.Rows(i)("sickTime") & "','" & dt.Rows(i)("vacationTime") & "') WHERE empFullName = ('" & dt.Rows(i)("empFullName")&"')"
                         objCmd = New System.Data.OleDb.OleDbCommand() 
                        With objCmd 
                                .Connection = objConn 
                                .CommandType = CommandType.Text 
                                .CommandText = updateSQL
                        End With 
                        objCmd.ExecuteNonQuery() 
                        'No need for anything to return and confirm the employee has been updated
                 Else
                        strSQL = "INSERT INTO TESThrtime (empFullName,sickTime,vacationTime) " & _
                        "VALUES ('" & dt.Rows(i)("empFullName") & "','" & dt.Rows(i)("sickTime") & "','" & dt.Rows(i)("vacationTime") & "')"
                        objCmd = New System.Data.OleDb.OleDbCommand() 
                        With objCmd 
                                .Connection = objConn 
                                .CommandType = CommandType.Text 
                                .CommandText = strSQL 
                        End With
                        objCmd.ExecuteNonQuery() 
                        Me.lblText.Text = Me.lblText.Text & "[" & dt.Rows(i)("empFullName") & "] Inserted <br>"
                        End If 
                        Catch err As Exception 
                        Me.lblText.Text = Me.lblText.Text & "[" & dt.Rows(i)("empFullName") & "] Not Insert <br>"


        End Try 

        Next            
        objCmd = Nothing
        objConn.Close()
        objConn = Nothing

    End Function

I took your suggestion on making SELECT COUNT(*)'s *variable a string even though I believe the return is an integer which is why the varable should be set to as a string... I feel like I'm getting close to solving this issue but I also feel like I may reach a wall I won't be able to overcome. Everything you mentioned makes sense to me but for some odd reason the above revised code doesn't work. It should, shouldn't it? There shouldn't be anything else to revise...

[JorgeM]

So the logic of you code "appears" to make sense now, but you still may have syntax problems and/or the references to your DB and controls may be incorrect. You have to keep in mind that can only comment on what I see posted in this thread. I dont have your full code and DB infront of me and every developer has their own techniques. The code that I've helped you with thus far is a mix between your code and mine. it doesnt mean that this is the way i would have approached this, and I'm sure other developers would provide you with other guidance.

What I would recommend at this point is to debug your code (step through it) using Visual Studio, or manually add in things that will write back to the screeen so you can check the values of your variables.

Also you have a Try block, but you didnt add in a "Catch" section. If the code fails, you wont see any errors because you are not "catching" them. you may want to read up on Try/Catch blocks.

[DaniwebOS]

I got it working! Yeah buddy....... I had to fix the update query, didn't occur to me that's primarly I'd like to believe where catch error was catching. Thanks for your help, really do appreciate it.

[JorgeM]

i'm very glad you got it working and SOLVED your issues...i'm tired and going take a break now...It was fun!