Trying to update and insert using the same button in vb.net 1

ms901Boss · Apr 6, 2021

I have created a program where the user enters a release number and retrieve the data to a DatagridView connecting to a IBM db2 database. The options the users currently have is: Clear, Retrieve, Validate, and Update. The issue I am having is if the record is not in the database it doesn't update. I need to find a way to insert records that are not in the table and update records that are in the database.

Here is an example below:

Here is the program with release number: 18851

In the database here is what is listed:

The database does not have column: 27-PRODWK, 70-SCHPROD, 81-AB%, 82-ARM%, 83-SHAFT%, 84-FIT%, 85-HDW%, 86-FIN%, 87-WELDCMP

If the end users enter data in the datagrid it will not update because it hasn't been inserted yet.(This is the issue)
Can you help with this?

Andrzejek · Apr 7, 2021

If...

ms901Boss said:
The database does not have column: 27-PRODWK, 70-SCHPROD, 81-AB%, 82-ARM%, 83-SHAFT%, 84-FIT%, 85-HDW%, 86-FIN%, 87-WELDCMP

so, where do you want to save the data from these columns in the datagrid?

ms901Boss said:
it will not update because it hasn't been inserted yet.(This is the issue)

At the beginning of your Update logic, check if you have any record(s) to update. Simple SELECT statement will do it. If you have a record(s) coming back - do the Update, if not - do the Insert.

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

ms901Boss · Apr 7, 2021

Here is the update queries I have:

Code:

 Private Sub UpdateSql(ByRef con As ADODB.Connection, ByVal strColumn As String,
                          ByVal strRelease As String, ByVal rCell As DataGridViewCell)
        Dim strUpdate, strWhere1, strWhere2 As String
        Dim strNewSql As String
        Dim strValue As String
        Dim intRecsAffected As Integer

        If ((Not IsNothing(rCell.Value)) And (strRelease > " ")) Then
            strValue = rCell.Value.ToString
            'strUpdate = "update JOBSCOPEDB.PPUSRFS set "
            strUpdate = "update PPUSRFS set "
            strWhere1 = " where SEARCH_KEY_UF = "
            strWhere2 = " and DATA_ITEM_UF = "
            strNewSql = ""
            If (strValue > " ") Then
                strNewSql = strUpdate
                strNewSql = strNewSql + " ALPHA_VALUE_UF = " + "'" + strValue + "'" _
                                                               + strWhere1 + " '" + strRelease + "'" _
                                                               + " and DATA_ITEM_UF = '" + strColumn + "'"

                If (strNewSql > " ") Then
                    con.Execute(strNewSql, intRecsAffected)
                End If
            End If
        End If
    End Sub]

Code:

Private Sub UpdateSql2(ByRef con As ADODB.Connection, ByVal strColumn As String,
                          ByVal strRelease As String, ByVal rCell As DataGridViewCell)
        Dim strUpdate, strWhere1, strWhere2 As String
        Dim strNewSql As String
        Dim strValue As String
        Dim intRecsAffected As Integer

        If ((Not IsNothing(rCell.Value)) And (strRelease > " ")) Then
            strValue = rCell.Value.ToString
            strUpdate = "update PPUSRFS set "
            strWhere1 = " where SEARCH_KEY_UF = "
            strWhere2 = " and DATA_ITEM_UF = "
            strNewSql = ""
            If (strValue > " ") Then
                strNewSql = strUpdate
                strNewSql = strNewSql + " NUMERIC_VALUE_UF = " + "'" + strValue + "'" _
                                                               + strWhere1 + " '" + strRelease + "'" _
                                                               + " and DATA_ITEM_UF = '" + strColumn + "'"

                If (strNewSql > " ") Then
                    con.Execute(strNewSql, intRecsAffected)

                End If
            End If
        End If
    End Sub

I have a private sub that calls the update query

Code:

Private Sub UpdateDatabase()
        On Error GoTo errH

        Dim con As New ADODB.Connection
        Dim rs As New ADODB.Recordset
        Dim strPath As String
        Dim intImportRow As Integer
        Dim objType As String
        Dim strRelease, strUsername, strPassword, strTable, strDatabase, strDsn, strSystem As String

        strDsn = ComboBox1.Text
        strSystem = txtSystem.Text
        strUsername = txtUser.Text
        strPassword = txtPassword.Text
        If con.State <> 1 And strUsername <> "" And strPassword <> "" Then
            con.Open("{iSeries As ODBC Driver};System=" + strSystem + ";Dsn=" + strDsn + "; Uid=" + strUsername + "; Pwd=" + strPassword + ";")
        Else
            MessageBox.Show("Please enter the correct UserName And Password", "Login Error")
            txtUser.Focus()
            con = Nothing
        End If

        For Each dgvRow As DataGridViewRow In gridUserEntries.Rows

            If (Not IsNothing(dgvRow.Cells(0))) Then
                If (Not (IsNothing(dgvRow.Cells(0).Value))) Then

                    rs.ActiveConnection = con
                    strRelease = dgvRow.Cells(0).Value.ToString
                    Call UpdateSql(con, "27 PRODWK", strRelease, dgvRow.Cells(1))
                    Call UpdateSql(con, "28 SHIPMON", strRelease, dgvRow.Cells(2))
                    Call UpdateSql(con, "30 %COMPL", strRelease, dgvRow.Cells(3))
                    Call UpdateSql(con, "31 TGTSHIP", strRelease, dgvRow.Cells(4))
                    Call UpdateSql(con, "70 SCHPROD", strRelease, dgvRow.Cells(5))
                    Call UpdateSql2(con, "81 AB%", strRelease, dgvRow.Cells(6))
                    Call UpdateSql2(con, "82 ARM%", strRelease, dgvRow.Cells(7))
                    Call UpdateSql2(con, "83 SHAFT%", strRelease, dgvRow.Cells(8))
                    Call UpdateSql2(con, "84 FIT%", strRelease, dgvRow.Cells(9))
                    Call UpdateSql2(con, "85 HDW%", strRelease, dgvRow.Cells(10))
                    Call UpdateSql2(con, "86 FIN%", strRelease, dgvRow.Cells(11))
                    Call UpdateSql(con, "87 WELDCMP", strRelease, dgvRow.Cells(12))
                    'con.Close()
                End If
            End If
        Next
        con.Close()
        con = Nothing
        MessageBox.Show("Jobscope Is updated")
        Exit Sub
errH:
        'MsgBox(Err.Description)
        con = Nothing

    End Sub

This works for the update, but how do I include the insert part?

Insert query:

Code:

 Private Sub InsertRelease(strRelease As String, rowInserted As Boolean)
        On Error GoTo errH

        Dim con As New ADODB.Connection
        Dim rs As New ADODB.Recordset
        Dim strPath As String
        Dim intImportRow As Integer
        Dim objType As String
        Dim strUsername, strPassword, strTable, strDatabase, strDsn, strSystem As String
        Dim sqlStr As String
        Dim sqlStr1, sqlStr2, sqlStr3, sqlStr4, sqlStr5, sqlStr6, sqlStr7, sqlStr8, sqlStr9, sqlStr10, sqlStr11 As String

        Dim intRecsAffected As Integer
        Dim boolRowInserted As Boolean

        strDsn = ComboBox1.Text
        strSystem = txtSystem.Text
        strUsername = txtUser.Text
        strPassword = txtPassword.Text
        'If con.State <> 1 And strUsername <> "" And strPassword <> "" Then
        'con.Open("{iSeries As ODBC Driver};System=" + strSystem + ";Dsn=" + strDsn + "; Uid=" + strUsername + "; Pwd=" + strPassword + ";")
        'Else
        'MessageBox.Show("Please enter the correct UserName And Password", "Login Error")
        'txtUser.Focus()

        'con = Nothing
        'End If
        con.Open("{iSeries As ODBC Driver};System=" + strSystem + ";Dsn=" + strDsn + ";Uid=TEST1;Pwd=TEST;")


        sqlStr = "insert into jobscopedb.ppusrfs (search_key_uf,DATA_ITEM_UF, ALPHA_VALUE_UF) values ('" + strRelease + "' ,'27 PRODWK','' )"
        sqlStr1 = "insert into jobscopedb.ppusrfs (search_key_uf,DATA_ITEM_UF, ALPHA_VALUE_UF) values ('" + strRelease + "' ,'28 SHIPMON','' )"
        sqlStr2 = "insert into jobscopedb.ppusrfs (search_key_uf,DATA_ITEM_UF, NUMERIC_VALUE_UF) values ('" + strRelease + "' ,'30 %COMPL',0 )"
        sqlStr3 = "insert into jobscopedb.ppusrfs (search_key_uf,DATA_ITEM_UF, ALPHA_VALUE_UF) values ('" + strRelease + "' ,'31 TGTSHIP','' )"
        sqlStr4 = "insert into jobscopedb.ppusrfs (search_key_uf,DATA_ITEM_UF, ALPHA_VALUE_UF) values ('" + strRelease + "' ,'70 SCHPROD','' )"
        sqlStr5 = "insert into jobscopedb.ppusrfs (search_key_uf,DATA_ITEM_UF, NUMERIC_VALUE_UF) values ('" + strRelease + "' ,'81 AB%',0 )"
        sqlStr6 = "insert into jobscopedb.ppusrfs (search_key_uf,DATA_ITEM_UF, NUMERIC_VALUE_UF) values ('" + strRelease + "' ,'82 ARM%',0 )"
        sqlStr7 = "insert into jobscopedb.ppusrfs (search_key_uf,DATA_ITEM_UF, NUMERIC_VALUE_UF) values ('" + strRelease + "' ,'83 SHAFT%',0 )"
        sqlStr8 = "insert into jobscopedb.ppusrfs (search_key_uf,DATA_ITEM_UF, NUMERIC_VALUE_UF) values ('" + strRelease + "' ,'84 FIT%',0 )"
        sqlStr9 = "insert into jobscopedb.ppusrfs (search_key_uf,DATA_ITEM_UF, NUMERIC_VALUE_UF) values ('" + strRelease + "' ,'85 HDW%',0 )"
        sqlStr10 = "insert into jobscopedb.ppusrfs (search_key_uf,DATA_ITEM_UF, NUMERIC_VALUE_UF) values ('" + strRelease + "' ,'86 FIN%',0 )"
        sqlStr11 = "insert into jobscopedb.ppusrfs (search_key_uf,DATA_ITEM_UF, NUMERIC_VALUE_UF) values ('" + strRelease + "' ,'87 WELDCMP',0 )"

        con.Execute(sqlStr, intRecsAffected)
        con.Execute(sqlStr1, intRecsAffected)
        con.Execute(sqlStr2, intRecsAffected)
        con.Execute(sqlStr3, intRecsAffected)
        con.Execute(sqlStr4, intRecsAffected)
        con.Execute(sqlStr5, intRecsAffected)
        con.Execute(sqlStr6, intRecsAffected)
        con.Execute(sqlStr7, intRecsAffected)
        con.Execute(sqlStr8, intRecsAffected)
        con.Execute(sqlStr9, intRecsAffected)
        con.Execute(sqlStr10, intRecsAffected)
        con.Execute(sqlStr11, intRecsAffected)
        con.Close()
        con = Nothing
        boolRowInserted = (intRecsAffected > 0)
        If (boolRowInserted) Then
            MessageBox.Show("Release " + strRelease + " added to F4 Screen in Jobscope", " Release Added ")
            btnValidate.Enabled = True
            btnRetrieve.Enabled = True

        Else
            MessageBox.Show("Release " + strRelease + " not added")
        End If
        Exit Sub
errH:
        MsgBox(Err.Description)
        con = Nothing

    End Sub

Andrzejek · Apr 7, 2021

Maybe something like this....?

Code:

If (strNewSql > " ") Then
    con.Execute(strNewSql, intRecsAffected)[blue]

    If intRecsAffected = 0 then[/blue][green]
        'No records were Updated, time to Insert[/green][blue]
        Call YourInsertSub
    End if[/blue]
End If

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

ms901Boss · Apr 8, 2021

How do I add the insert Function to the update? I created a button and add this

Code:

Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
        Dim strRelease As String
        Dim sqlStr, sqlStr1, sqlStr2, sqlStr3, sqlStr4, sqlStr5, sqlStr6, sqlStr7, sqlStr8, sqlStr9, sqlStr10, sqlStr11 As String

        If Not ("Select * from jobscopedb.ppusrfs where search_key_uf= '" + strRelease + "'") Then
            Call UpdateDatabase()
        Else
            sqlStr = "insert into jobscopedb.ppusrfs (search_key_uf,DATA_ITEM_UF, ALPHA_VALUE_UF) values ('" + strRelease + "' ,'27 PRODWK','' )"
            sqlStr1 = "insert into jobscopedb.ppusrfs (search_key_uf,DATA_ITEM_UF, ALPHA_VALUE_UF) values ('" + strRelease + "' ,'28 SHIPMON','' )"
            sqlStr2 = "insert into jobscopedb.ppusrfs (search_key_uf,DATA_ITEM_UF, NUMERIC_VALUE_UF) values ('" + strRelease + "' ,'30 %COMPL',0 )"
            sqlStr3 = "insert into jobscopedb.ppusrfs (search_key_uf,DATA_ITEM_UF, ALPHA_VALUE_UF) values ('" + strRelease + "' ,'31 TGTSHIP','' )"
            sqlStr4 = "insert into jobscopedb.ppusrfs (search_key_uf,DATA_ITEM_UF, ALPHA_VALUE_UF) values ('" + strRelease + "' ,'70 SCHPROD','' )"
            sqlStr5 = "insert into jobscopedb.ppusrfs (search_key_uf,DATA_ITEM_UF, NUMERIC_VALUE_UF) values ('" + strRelease + "' ,'81 AB%',0 )"
            sqlStr6 = "insert into jobscopedb.ppusrfs (search_key_uf,DATA_ITEM_UF, NUMERIC_VALUE_UF) values ('" + strRelease + "' ,'82 ARM%',0 )"
            sqlStr7 = "insert into jobscopedb.ppusrfs (search_key_uf,DATA_ITEM_UF, NUMERIC_VALUE_UF) values ('" + strRelease + "' ,'83 SHAFT%',0 )"
            sqlStr8 = "insert into jobscopedb.ppusrfs (search_key_uf,DATA_ITEM_UF, NUMERIC_VALUE_UF) values ('" + strRelease + "' ,'84 FIT%',0 )"
            sqlStr9 = "insert into jobscopedb.ppusrfs (search_key_uf,DATA_ITEM_UF, NUMERIC_VALUE_UF) values ('" + strRelease + "' ,'85 HDW%',0 )"
            sqlStr10 = "insert into jobscopedb.ppusrfs (search_key_uf,DATA_ITEM_UF, NUMERIC_VALUE_UF) values ('" + strRelease + "' ,'86 FIN%',0 )"
            sqlStr11 = "insert into jobscopedb.ppusrfs (search_key_uf,DATA_ITEM_UF, NUMERIC_VALUE_UF) values ('" + strRelease + "' ,'87 WELDCMP',0 )"
        End If

I get an error: (local Variable) strRelease As String
Variable 'strRelease' is used before it has been assigned a value. A null reference exception could result at runtime.

Andrzejek · Apr 9, 2021

First small problem is – you do not assign a value to your variable strRelease. There is no line of code like
[tt]strRelease = “XYZ”[/tt]
Second small problems – it is the same with other variables, as sqlStr, sqlStr1, sqlStr2, … sqlStr11
The biggest problem – what are you trying to achieve with this statement:[tt]
If Not ("Select * from jobscopedb.ppusrfs where search_key_uf= '" + strRelease + "'") Then[/tt]
The above will ALWAYS evaluate to TRUE, any (non empty) string will evaluate to TRUE

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

SaltyTheFrog · Apr 9, 2021

It looks to me like those updates are wide open to SQL injection attacks. You should be using parametrized database updates.

Andrzejek · Apr 9, 2021

SaltyTheFrog - absolutely!
But before ms901Boss gets to parametrized queries, he/she (?) needs to have correct syntax / logic in the code. IMO

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

ms901Boss · Apr 12, 2021

I have corrected the code and created a insert Sub:

Code:

Private Sub InsertDatabase()
        On Error GoTo errH

        Dim con As New ADODB.Connection
        Dim rs As New ADODB.Recordset
        Dim strPath As String
        Dim intImportRow As Integer
        Dim objType As String
        Dim strRelease, strUsername, strPassword, strTable, strDatabase, strDsn, strSystem As String

        strDsn = ComboBox1.Text
        strSystem = txtSystem.Text
        strUsername = txtUser.Text
        strPassword = txtPassword.Text
        If con.State <> 1 And strUsername <> "" And strPassword <> "" Then
            '  con.Open("{iSeries As ODBC Driver};System=DEV-JOBSCOPE;Dsn=DEVELOP;Uid=" + strUsername + ";Pwd=" + strPassword + ";")
            con.Open("{iSeries As ODBC Driver};System=" + strSystem + ";Dsn=" + strDsn + "; Uid=" + strUsername + "; Pwd=" + strPassword + ";")
        Else
            MessageBox.Show("Please enter the correct UserName And Password", "Login Error")
            txtUser.Focus()
            con = Nothing
        End If

        For Each dgvRow As DataGridViewRow In gridUserEntries.Rows

            If (Not IsNothing(dgvRow.Cells(0))) Then
                If (Not (IsNothing(dgvRow.Cells(0).Value))) Then

                    rs.ActiveConnection = con
                    strRelease = dgvRow.Cells(0).Value.ToString
                    Call UpdateInsertSql2(con, "27 PRODWK", strRelease, dgvRow.Cells(1))
                    Call UpdateInsertSql2(con, "28 SHIPMON", strRelease, dgvRow.Cells(2))
                    Call UpdateInsertSql(con, "30 %COMPL", strRelease, dgvRow.Cells(3))
                    Call UpdateInsertSql2(con, "31 TGTSHIP", strRelease, dgvRow.Cells(4))
                    Call UpdateInsertSql2(con, "70 SCHPROD", strRelease, dgvRow.Cells(5))
                    Call UpdateInsertSql(con, "81 AB%", strRelease, dgvRow.Cells(6))
                    Call UpdateInsertSql(con, "82 ARM%", strRelease, dgvRow.Cells(7))
                    Call UpdateInsertSql(con, "83 SHAFT%", strRelease, dgvRow.Cells(8))
                    Call UpdateInsertSql(con, "84 FIT%", strRelease, dgvRow.Cells(9))
                    Call UpdateInsertSql(con, "85 HDW%", strRelease, dgvRow.Cells(10))
                    Call UpdateInsertSql(con, "86 FIN%", strRelease, dgvRow.Cells(11))
                    Call UpdateInsertSql(con, "87 WELDCMP", strRelease, dgvRow.Cells(12))

                End If
            End If
        Next
        con.Close()
        con = Nothing
        MessageBox.Show("Jobscope Is Inserted")
        Exit Sub
errH:
        'MsgBox(Err.Description)
        con = Nothing

    End Sub

This is inserting data into the database, but I need to some way combine the update and insert in one button. If the update didn't update the field then I need to insert the field in the database. How do I do that with the code I have provided?

ms901Boss · Apr 15, 2021

I have it working now, I add a Sub to Insert.
Code:

Code:

Private Sub InsertSql2(ByRef con As ADODB.Connection, ByVal strColumn As String,
                           ByVal strRelease As String, ByVal rCell As DataGridViewCell)
        Dim strInsert, strWhere1, strWhere2 As String
        Dim strNewSql As String
        Dim strValue As String
        Dim intRecsAffected As Integer

        If ((Not IsNothing(rCell.Value)) And (strRelease > " ")) Then
            strValue = rCell.Value.ToString
            strValue = rCell.Value.ToString
            strInsert = "insert into jobscopedb.ppusrfs"
            strWhere1 = "(search_key_uf, SET_CODE_UF, DATA_ITEM_UF, ALPHA_VALUE_UF)  "
            strWhere2 = "values('" + strRelease + "'" + ", " + "'" + "RL" + "'" + ", " + "'" + strColumn + "'" + ", " + "'" + strValue + "'" + ")"

            strNewSql = ""
            If (strValue > " ") Then
                strNewSql = strInsert
                strNewSql = strNewSql + strWhere1 + strWhere2
                If (strNewSql > " ") Then
                    con.Execute(strNewSql, intRecsAffected)
                End If
            End If
        End If
    End Sub

In the update sub I call insert if the fill was blank

Code:

 Private Sub UpdateSql(ByRef con As ADODB.Connection, ByVal strColumn As String,
                          ByVal strRelease As String, ByVal rCell As DataGridViewCell)
        Dim strUpdate, strWhere1, strWhere2 As String
        Dim strNewSql As String
        Dim strValue As String
        Dim intRecsAffected As Integer

        If ((Not IsNothing(rCell.Value)) And (strRelease > " ")) Then
            strValue = rCell.Value.ToString

            strUpdate = "update PPUSRFS set "
            strWhere1 = " where SEARCH_KEY_UF = "
            strWhere2 = " and DATA_ITEM_UF = "
            strNewSql = ""
            If (strValue > " ") Then

                strNewSql = strUpdate
                strNewSql = strNewSql + " ALPHA_VALUE_UF = " + "'" + strValue + "'" _
                                                               + strWhere1 + " '" + strRelease + "'" _
                                                               + " and DATA_ITEM_UF = '" + strColumn + "'"

                If (strNewSql > " ") Then
                    con.Execute(strNewSql, intRecsAffected)
                    If intRecsAffected = 0 Then
                        'Insert records
                        Call InsertSql2(con, strColumn, strRelease, rCell)

                    End If
                End If
            End If
        End If
    End Sub

Thanks everyone for your help.

Andrzejek · Apr 15, 2021

Isn't that exactly what I recommended on 7 Apr 21 17:51... [ponder]

BTW - why are you using ADODB instead of native NET's ADO?

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Trying to update and insert using the same button in vb.net 1

ms901Boss

Programmer

Andrzejek

Programmer

ms901Boss

Programmer

Andrzejek

Programmer

ms901Boss

Programmer

Andrzejek

Programmer

SaltyTheFrog

Programmer

Andrzejek

Programmer

ms901Boss

Programmer

ms901Boss

Programmer

Andrzejek

Programmer

Similar threads

Part and Inventory Search

Sponsor