im make program using vb6 n tried to get data from msaccess database ,display it in vb6 and save the data to oracle table database..

The table name in oracle is testcoba. Now i can save the data from vb6 to oracle table. But i want is the data will not be added when the data already exists in table.

i hv 2 datetimepicker call DTPawal & DTPakhir. DTP awal is start date and DTPakhir is end date. so user select the date first in dtpawal n dtp akhir picker. then the data will display in vb6 and save it to oracle database.

so how i can filter the data adding based by date from user select in datetimepicker?? if the date (from 2 datetimepicker) is already selected before n the data from the date is already exists in table, they will not be added. but if the date (from 2 datetimepicker) never selected before then the data will adding in oracle table..

Sub datagrid2oracledb(connstr As String, dtfmt As String)
Dim oconn As New ADODB.Connection, rsa As ADODB.Recordset
Dim i As Integer, strSQL As String, row

Dim rs As ADODB.Recordset
Set rs = Adodc1.Recordset

On Error GoTo Error_Handler
10 oconn.Open connstr
MsgBox dg1.ApproxCount & "?" & cnt

If rs.Fields(2).Value = Format$(DTPawal.Value, "MM/dd/yyyy") Then
MsgBox "date already exists"
With rs
Do While Not .EOF
30 strSQL = "INSERT INTO testcoba (name,title,checktime)VALUES ('" & .Fields(0).Value & "','" & .Fields(1).Value & "',TO_DATE('" & .Fields(2).Value & "', '" & dtfmt & "'))"
40 Set rs = oconn.Execute(strSQL)
50 Set rs = Nothing
End With
60 oconn.Close
70 Set oconn = Nothing
MsgBox "Done"
Exit Sub
End If

If (Erl <> 25) Then
MsgBox i & ":" & Erl & ":" & Err.Number & ":" & Err.Description
End If
End Sub

im tried adding this in my code,but not work : If rs.Fields(2).Value = Format$(DTPawal.Value, "MM/dd/yyyy") Then MsgBox "date already exists"

pls help me about this..

1 Year
Discussion Span
Last Post by Shark_1

Without opening recordset how do you access it.
Create a SQL query statement to match data between two dates. If there then exit or insert new data.
I just give you an example

'Creating a function to open recordset
'It is at module lavel to reuse it
'So it is declared Publicly

Public Function getrecordset(cnnConnection As ADODB.Connection, sQry As String) As ADODB.Recordset
          Dim rstrecord As ADODB.Recordset
          Set rstrecord = New ADODB.Recordset

          rstrecord.CursorType = adOpenDynamic
          rstrecord.LockType = adLockPessimistic
          rstrecord.CursorLocation = adUseClient
          rstrecord.Source = sQry
          Set rstrecord.ActiveConnection = cnnConnection
          Set getrecordset = rstrecord

End Function

Now time to open and check the condition

Dim adrecset As New ADODB.Recordset

adcon.ConnectionString = constr

adsql = "select * From sales Where sale_date Between Date1 and Date2"
Set adrecset = getrecordset(adconpersonal, adsql)

If adrecset.RecordCount = 0 Then

    'Make your codes to insert new data
    'Like adcon.execute("YOUR INSERT STATEMENT HERE")
End If

Hope it can help you.

This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.