current community

your communities

more stack exchange communities

Stack Exchange
tour help
careers 2.0
Take the 2-minute tour ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote 1 down vote favorite

I need to uplad data from excel into a database, but I need to check first if there is data in the table for each upload so that I Update or Insert data.

To diferentiate Update or Insert, I'm usign a SQL IF EXIST command, which works okay in SQL. When I try this in Excel VBA I get an error message: "Command text was not set for the command object."

See code below

Dim strSQL As String
Dim Value As String
Dim Reference As String 

    Set RCconn = New ADODB.Connection
    Set TuneCMD = New ADODB.Command
    ' Establish Recordset
    Set Results = New ADODB.Recordset
    'Establish a Connection
    With RCconn
       .Provider = "SQLOLEDB"
       .ConnectionString = ConStr
    End With
    'Open the connection
    RCconn.Open

'i Columns
For i = 5 To 10 '16
'j rows
For j = 6 To 60 '145

Value= Sheets("Value").Cells(j, i)
Reference= "W_F/P_" & Sheets("Reference").Cells(j, i)

stringTest = "IF EXISTS (SELECT * FROM UploadTable WHERE Ref = '" & Reference &  "') "
stringTest = stringTest & "UPDATE Val "
stringTest = stringTest & "SET Val = '" & Value & "' "
stringTest = stringTest & "where Ref = '" & Reference & "' "
stringTest = stringTest & "Else "
stringTest = stringTest & "INSERT INTO UploadTable (Val , Ref ) "
stringTest = stringTest & "values ('" & Value & "', '" & Reference & "')"

RCconn.Execute strSQL

Next
Next

Set Results = Nothing
RCconn.Close
Set RCconn = Nothing

Is it the case that 'IF EXIST' can not be used in VBA? Is there a work around?

Thanks

share|improve this question
add comment

1 Answer

up vote 2 down vote accepted

ADODB.Connection.Execute sends a pass-through query to your database. It doesn't matter what SQL statement was in that string; if your database can understand it, it will be executed. So inspect your SQL query again.

Try this:

Put a breakpoint on the line RCconn.Execute strSQL. When the debugger breaks there, inspect the value of strSQL. Copy it and execute in SQL Server Management Studio directly. If that doesn't work, correct your code that builds that string. If it works, then there is some problem with your ConnectionString. In that case, check that the userID and password you are using in the ConnectionString to connect has adequate privileges.

share|improve this answer
    
Thanks Pradeep. I tried adding 'Sheets("Test_Page").Cells(j, i) = stringTest' before 'RCconn.Execute strSQL'. Coping the output into SQL Server Maagement Studio alows me to run the query with no errors. –  Selrac Nov 14 '13 at 11:17
    
In your connection string, if you set the User ID=sa and Password=<whatever your sa password> temporarily, does it work? –  Pradeep Kumar Nov 14 '13 at 11:30
2  
Thanks Pradeep. My problem was with 'RCconn.Execute strSQL'. I changed to 'RCconn.Execute stringTest' and all works great. Thanks for confirming on the posibility to use 'if esxist' and thanks also to settin me in the right track to resolve this issue. Much appreciated –  Selrac Nov 14 '13 at 12:05
    
+ 1 Nicely Done :) –  Siddharth Rout Nov 14 '13 at 12:43
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.