LearningVB
MIS
I am working with a litle program that recives some data from a oracledatabase and gives me the opportunity to alter this material and so far it works perfect. I have made a program and uses spread to the manipulation of the data. My connction to the database is ADO.
Hello out there,
But the problem startet when I am going to restore the altered data in the database again. And after hours of trying several things this seems the last way out of the trouble.
Is there anyone out there who can give me a clou about an easy way of update a databasetable from VB?
Hopefully I will here from someone soon….
Best regard Stig
The last code I tryed are this:
Public Sub OppdaterKjemiskeKriterier(s As Object)
Dim i As Integer
Dim strSQL As String
Dim dteDato As Date
Dim dblDeklarert As Double
Dim dblMinimum As Double
Dim dblMaksimum As Double
Dim intDeklID As Integer
Dim cnn1 As ADODB.Connection
Dim strCnn As String
Dim cmdChange As ADODB.Command
Dim skrivDB As ADODB.Recordset
'BeginTrans
For i = 1 To s.MaxRows
s.Row = i
s.Col = 1
intDeklID = CInt(s.Text)
s.Col = 3
If Len(s.Text) > 0 Then
dblDeklarert = CDbl(s.Text)
Else
dblDeklarert = 0
End If
s.Col = 4
If Len(s.Text) > 0 Then
dblMinimum = CDbl(s.Text)
Else
dblMinimum = 0
End If
s.Col = 5
If Len(s.Text) > 0 Then
dblMaksimum = CDbl(s.Text)
Else
dblMaksimum = 0
End If
'strSQL = "UPDATE Dekl_Kjemiske_Kriterier SET Dato = " & Date
strSQL = "UPDATE Dekl_Kjemiske_Kriterier SET Deklarert = " & dblDeklarert
'strSQL = strSQL + ", Deklarert = " & dblDeklarert
strSQL = strSQL + ", Minimum = " & dblMinimum
strSQL = strSQL + ", Maximum = " & dblMaksimum
strSQL = strSQL + ", WHERE AltProdNr = " & lngAltProdNr
strSQL = strSQL + ", AND DefinisjonID = " & intDeklID
' Set cnn1 = New ADODB.Connection
' cnn1.Open strDatabase
Set cmdChange = New ADODB.Command
Set cmdChange.ActiveConnection = dbConn
cmdChange.CommandText = strSQL
Set skrivDB = New ADODB.Recordset
skrivDB.Open "Dekl_Kjemiske_Kriterier", dbConn, , , adCmdTable
ExecuteCommand cmdChange, skrivDB
Debug.Print strSQL
Debug.Print i
Debug.Print intDeklID
Next i
'CommitTrans
End Sub
Public Sub ExecuteCommand(cmdTemp As ADODB.Command, rsTemp As ADODB.Recordset)
Dim errLoop As Error
On Error GoTo err_execute
cmdTemp.Execute
On Error GoTo 0
rsTemp.Requery
Exit Sub
err_execute:
' If Errors.Count > 0 Then
' For Each errLoop In Errors
' MsgBox "Error nubmer: " & errLoop.Number & vbCr & _
' errLoop.Description
' nest errLoop
' End If
MsgBox "Her er noe feil jo"
Resume Next
End Sub
Hello out there,
But the problem startet when I am going to restore the altered data in the database again. And after hours of trying several things this seems the last way out of the trouble.
Is there anyone out there who can give me a clou about an easy way of update a databasetable from VB?
Hopefully I will here from someone soon….
Best regard Stig
The last code I tryed are this:
Public Sub OppdaterKjemiskeKriterier(s As Object)
Dim i As Integer
Dim strSQL As String
Dim dteDato As Date
Dim dblDeklarert As Double
Dim dblMinimum As Double
Dim dblMaksimum As Double
Dim intDeklID As Integer
Dim cnn1 As ADODB.Connection
Dim strCnn As String
Dim cmdChange As ADODB.Command
Dim skrivDB As ADODB.Recordset
'BeginTrans
For i = 1 To s.MaxRows
s.Row = i
s.Col = 1
intDeklID = CInt(s.Text)
s.Col = 3
If Len(s.Text) > 0 Then
dblDeklarert = CDbl(s.Text)
Else
dblDeklarert = 0
End If
s.Col = 4
If Len(s.Text) > 0 Then
dblMinimum = CDbl(s.Text)
Else
dblMinimum = 0
End If
s.Col = 5
If Len(s.Text) > 0 Then
dblMaksimum = CDbl(s.Text)
Else
dblMaksimum = 0
End If
'strSQL = "UPDATE Dekl_Kjemiske_Kriterier SET Dato = " & Date
strSQL = "UPDATE Dekl_Kjemiske_Kriterier SET Deklarert = " & dblDeklarert
'strSQL = strSQL + ", Deklarert = " & dblDeklarert
strSQL = strSQL + ", Minimum = " & dblMinimum
strSQL = strSQL + ", Maximum = " & dblMaksimum
strSQL = strSQL + ", WHERE AltProdNr = " & lngAltProdNr
strSQL = strSQL + ", AND DefinisjonID = " & intDeklID
' Set cnn1 = New ADODB.Connection
' cnn1.Open strDatabase
Set cmdChange = New ADODB.Command
Set cmdChange.ActiveConnection = dbConn
cmdChange.CommandText = strSQL
Set skrivDB = New ADODB.Recordset
skrivDB.Open "Dekl_Kjemiske_Kriterier", dbConn, , , adCmdTable
ExecuteCommand cmdChange, skrivDB
Debug.Print strSQL
Debug.Print i
Debug.Print intDeklID
Next i
'CommitTrans
End Sub
Public Sub ExecuteCommand(cmdTemp As ADODB.Command, rsTemp As ADODB.Recordset)
Dim errLoop As Error
On Error GoTo err_execute
cmdTemp.Execute
On Error GoTo 0
rsTemp.Requery
Exit Sub
err_execute:
' If Errors.Count > 0 Then
' For Each errLoop In Errors
' MsgBox "Error nubmer: " & errLoop.Number & vbCr & _
' errLoop.Description
' nest errLoop
' End If
MsgBox "Her er noe feil jo"
Resume Next
End Sub