CODEWITHSUNDEEP
sqlvba
Robert Hardy
Robert Hardy

Reputation: 1

Excel VBA to update an SQL date field

I'm trying up date an SQL table (Date field) with a date cell in excel and getting an error

Error #-2147217913: Operand type clah int is incompatible with Date

Connection to the database is fine.

This is the code i'm using

Private Sub CommandButton1_Click()
On Error GoTo FormLoadError
    Dim conn As ADODB.Connection
    Dim rs As ADODB.Recordset
    Dim sConnString As String
    Dim FromDate As Date
    Dim ToDate As Date
    Dim FromNumber As String
 
    Sheet2.Range("a2:zz9999").ClearContents
 
    ' Create the connection string.
    sConnString = "Provider=SQLOLEDB;Data Source=SQL;" & _
                  "Initial Catalog=M2MTECLIVE;" & _
                  "Integrated Security=SSPI;"
      
    ' Create the Connection and Recordset objects.
    Set conn = New ADODB.Connection
    Set rs = New ADODB.Recordset
    
     ' Open the connection and execute.
    conn.Open sConnString
    FromDate = Worksheets("Parameters").Range("b4")   'Cell be contains a date in the format YYYY-MM-DD
    Set rs = conn.Execute("update dbo.M2MDates set FD = " & FromDate)
    conn.Close

Any ideas ?

Upvotes: 0

Views: 826

Answers (1)

Parfait
Parfait

Reputation: 107747

Consider using ADO parameters via ADO command object which avoids need of concatenating and punctuating values to SQL statement:

conn.Open sConnString

' PREPARED STATEMENT WITH QMARKS ?
strSQL = "update dbo.M2MDates set FD = ?"
FromDate = Worksheets("Parameters").Range("b4") 

Set cmd = New ADODB.Command
With cmd
    .ActiveConnection = conn
    .CommandText = strSQL
    .CommandType = adCmdText

    '  BIND DATE PARAMETER
    .Parameters.Append .CreateParameter("date_prm", adDate, adParamInput, , FromDate)

    '  EXECUTE ACTION QUERY (NO RECORDSET)
    .Execute
End With

conn.Close
Set cmd = Nothing: Set conn = Nothing

Upvotes: 2

Related Questions