×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Contact US

*
*
*
*

Thanks. We have received your request and will respond promptly.

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

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

thread1867-1809458

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

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

(OP)
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?

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

If...

Quote (ms901Boss)

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?

Quote (ms901Boss)

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

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

(OP)
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

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

Maybe something like this....?

CODE

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

    If intRecsAffected = 0 then
        'No records were Updated, time to Insert
        Call YourInsertSub
    End if
End If

---- Andy

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

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

(OP)
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.

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

First small problem is – you do not assign a value to your variable strRelease. There is no line of code like
strRelease = “XYZ”
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:
If Not ("Select * from jobscopedb.ppusrfs where search_key_uf= '" + strRelease + "'") Then
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

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

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

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

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

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

(OP)
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?

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

(OP)
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.

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

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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login
Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close