question

NBoddie-3697 avatar image
0 Votes"
NBoddie-3697 asked karenpayneoregon answered

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

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?



dotnet-visual-basic
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

XingyuZhao-MSFT avatar image
1 Vote"
XingyuZhao-MSFT answered

Hi @NBoddie-3697 ,
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.


5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

DuaneArnold-0443 avatar image
0 Votes"
DuaneArnold-0443 answered DuaneArnold-0443 edited

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

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

karenpayneoregon avatar image
0 Votes"
karenpayneoregon answered

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.



5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.