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!

Register Log in
  • Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

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

Status
Not open for further replies.
ms901Boss

ms901Boss

Programmer
Mar 19, 2021
16
US
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
ProgramPic_mjloz0.png


In the database here is what is listed:

ProgramQuery_smxkey.png



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?
 
 https://files.engineering.com/getfile.aspx?folder=1218623b-0214-4a2a-9825-7672c654ea0c&file=ProgramPic.PNG
Sort by date Sort by votes
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
Click to expand...
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)
Click to expand...
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
 
Upvote 0 Downvote
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
 
Upvote 0 Downvote
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
 
Upvote 0 Downvote
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.
 
Upvote 0 Downvote
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
 
Upvote 0 Downvote
It looks to me like those updates are wide open to SQL injection attacks. You should be using parametrized database updates.
 
Upvote 0 Downvote
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
 
Upvote 0 Downvote
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?
 
Upvote 0 Downvote
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.
 
Upvote 0 Downvote
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
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

T
  • Locked
  • Question
How to send email from vb.net program using modern authentication
Replies
3
Views
676
strongm
strongm
Andrzejek
  • Locked
  • Question
VB.NET 2022 Read from Excel 1
Replies
4
Views
732
Rick_Stanich
Rick_Stanich
IlyaRabyy
  • Locked
  • Question
Is the program running? - HOW-TO
Replies
1
Views
197
strongm
strongm
IlyaRabyy
  • Locked
  • Question
Who deleted the file? - HOW-TO 1
Replies
16
Views
193
IlyaRabyy
IlyaRabyy
Auguy
  • Locked
  • Question
VB.net 2022 RDLC Report Column Width
Replies
1
Views
255
Auguy
Auguy

Part and Inventory Search

Sponsor

Back
Top