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?