1 Reply Latest reply: Aug 25, 2011 7:19 AM by 883350 RSS



      I want to create an excel file that will start up with a form asking some data. I have made a port of the code, but I'm block at the date condition.
      My code is:

      Private Sub cmdSearch_Click()

      If Me.txtuser.Value = "" Then
      MsgBox "Please enter a user name.", vbExclamation, "No user name selected"
      Exit Sub
      End If
      If Me.txtpass.Value = "" Then
      MsgBox "Please enter the password.", vbExclamation, "Password not entered"
      End If
      If Me.txtdba.Value = "" Then
      MsgBox "Please enter database.", vbExclamation, "Database not selected"
      End If
      If Me.txtstart.Value = "" Then
      MsgBox "Please enter a starting date.", vbExclamation, "Starting date empty"
      End If
      If Me.txtend.Value = "" Then
      MsgBox "Please enter a end date.", vbExclamation, "End date empty"
      End If

      Dim username As String
      Dim password As String
      Dim sid As String
      Dim startdate As String
      Dim enddate As String
      Dim OraDatabase As Object
      Dim OraDynaSet As Object
      Dim OraSession As Object

      username = txtuser.Value
      password = txtpass.Value
      sid = txtdba.Value
      startd = txtstart.Value
      endd = txtend.Value

      ' connect to database
      Set objSession = CreateObject("OracleInProcServer.XOraSession")
      Set objdatabase = objSession.OpenDatabase(sid, username & " / " & password, 0)
      ' run scripts
      objdatabase.ExecuteSQL "DROP TABLE TTT_DATEPAR"
      objdatabase.ExecuteSQL "CREATE TABLE TTT_DATEPAR(STARTDATE date, ENDDATE date)"
      objdatabase.ExecuteSQL "INSERT INTO TTT_DATEPAR VALUES ('startd', 'endd')"

      Set OraDynaSet = Nothing
      Set OraDatabase = Nothing
      Set OraSession = Nothing

      End Sub

      Private Sub cmdCancel_Click()
      Unload Me
      End Sub
      The error is Run-time error '440': SQL execution error, ORA-01858: a non-numeric character was found where a numeric was expected
      and is Debugging line
      objdatabase.ExecuteSQL "INSERT INTO TTT_DATEPAR VALUES ('startd', 'endd')"
      I type date like 01-JAN-2001 and it's recognized by the script in lines startd = txtstart.Value and endd = txtend.Value.
      If i write date directly in the line it works fine, but I need to be entered in the form.
      I tried this lines
      objdatabase.ExecuteSQL "INSERT INTO TTT_DATEPAR VALUES ( startd, endd)" --------- error ORA-00984 column not allowed here
      objdatabase.ExecuteSQL "INSERT INTO TTT_DATEPAR VALUES ( 'startd' & ", " & 'endd' )" ----- error wrong number of argument or invalid property assigment
      objdatabase.ExecuteSQL "INSERT INTO TTT_DATEPAR VALUES ( 'startd' & , & 'endd' )" ------ error error ora-00917 missing comma
      objdatabase.ExecuteSQL "INSERT INTO TTT_DATEPAR VALUES ( 'startd', & 'endd')"----- error ora-01745 invallid host/bind variable name.

      Any other ideas?
      I use Windows7 with Office 2010. I have installed Oracle client 11.2.0 and Oracle Express Edition 10gE.