question

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

A datagridview that Update and Insert the database

I have a datagridview and I have four buttons: Clear, retrieve, Update, and Insert. All the buttons are currently work but the issue I am having is if the record is not in the database I need it to insert. So I nee to combine the update and insert button but I'm not sure how to do that.


Here is the Update 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=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 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

Here is the Insert 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

How do I combine the insert and update together to if the record is in the database and to insert if the record is not in the database?


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.

1 Answer

karenpayneoregon avatar image
0 Votes"
karenpayneoregon answered

Hello,

Drop using old method for working with data. In your case look at a DataAdapter or a TableAdapter. With more experience you move to Entity Framework or Entity Framework Core but coming from Recordsets that is a huge leap.

When looking a TableApapter's the data wizard uses a BindingSource which provides the majority of features Recordset did.


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.