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

NBoddie 1 Reputation point
2021-04-09T00:29:20.983+00:00

I have created a program that allows user to enter data in the datagridview and select a button. The button options are : Clear, Retrieve and Update. All buttons currently work. For the update button I call a Private Sub called Updatedatabase which includes two other calls ( UpdateSql and UpdateSql2)

Here is UpdateSql:
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

Here is UpdateSql2:
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

Here is the Updatedatabase:

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

That all works fine. I need to a way to say if the update option is select and the column is not in the database I need to insert it.

This what I need for the insert:

 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 )"

How do I add this insert part to make it do either or?

VB
VB
An object-oriented programming language developed by Microsoft that is implemented on the .NET Framework. Previously known as Visual Basic .NET.
2,568 questions
0 comments

3 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Xingyu Zhao-MSFT 5,356 Reputation points
    2021-04-09T07:19:26.853+00:00

    Hi @NBoddie ,
    Here's an example of updating data ( including insert,update and delete) back into database from DataGridView. 

        Dim myDA As OleDbDataAdapter  
    Dim myDataSet As DataSet  
  
    Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click  
        Me.Validate()  
        Me.myDA.Update(Me.myDataSet.Tables("MyTable"))  
        Me.myDataSet.AcceptChanges()  
    End Sub  
  
    Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load  
        Using con As OleDbConnection = New OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=yourDBFilePath;")  
            Dim cmd As OleDbCommand = New OleDbCommand("SELECT * FROM Table1", con)  
            con.Open()  
            myDA = New OleDbDataAdapter(cmd)  
  
            'One CommandBuilder object is required. It automatically generates DeleteCommand,UpdateCommand and InsertCommand for DataAdapter object '   
            Dim builder As OleDbCommandBuilder = New OleDbCommandBuilder(myDA)  
            myDataSet = New DataSet()  
            myDA.Fill(myDataSet, "MyTable")  
            DataGridView1.DataSource = myDataSet.Tables("MyTable").DefaultView  
        End Using  
    End Sub

    You can refer to the code to update your database.
    Hope it could be helpful.

    Best Regards,
    Xingyu Zhao
    *
    If the answer is helpful, please click "Accept Answer" and upvote it.
    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

    1 person found this answer helpful.
    0 comments
  2. Duane Arnold 3,211 Reputation points
    2021-04-09T07:29:18.65+00:00

    Your Update button should be called Save, since it now is to have a dual purpose. And in order to update some data in the database table, I will assume you had to retrieve the data to be updated, and it's at that point that you know that you were retrieving data for update that you could have set a gblnUpdateFlag = true ('g' means global) that the Save button logic would check if it was to take the update code path or insert code path and set the gblnUpdateFlag = false when done.

    You could also learn how to prevent SQL injection attacks by using parametrized T-SQL to prevent the attack on inline T-SQL queries or data persistence inline T-SQL for data doing an insert or update.

    It doesn't matter if it's a Web or Windows desktop program UI that a user is entering data at the screen. SQLI can happen with a sophisticated user at the screen and the program doesn't protect from the SQLI attacks.

    https://portswigger.net/web-security/sql-injection

    https://social.technet.microsoft.com/wiki/contents/articles/53104.vb-net-sql-injection-protection-using-parameterized-queries.aspx

    0 comments
  3. Karen Payne MVP 35,031 Reputation points
    2021-04-09T11:13:33.273+00:00

    Couple of ideas

    Sticking with current code for MS-Access

    • You could create unique indices if this is MS-Access then wrap you execution of an INSERT in a try/catch statement so if you attempt to insert an existing record defined by the indices the database will throw back an exception.
    • Do a SELECT first to see if the INSERT would create a duplicate

    Moving away from record sets and using SQL-Server see the following Microsoft TechNet Wiki page.

    Then there is the option for data adapters as per XingyuZhao-MSFT reply and heed DuaneArnold-0443 on SQL injection.

    0 comments