Insert or update into SQL based on a condition

[indupriya9]

I have a table in sql server 2000 with four fields. I would like to insert or update to this table using VBA in Excel 2003. Individually the updates and inserts work. The trouble is with the condition where the recordcount always returns more than 0 rows I guess.

Here is the code I have written so far.

Private Sub CommandButton2_Click()

On Error Resume Next

Worksheets("Report").Select
Worksheets("Report").Shapes("Text Box 1").Select
CommentsText = Mid(Selection.Characters.Text, 25)
Worksheets("Report").Shapes("Text Box 3").Select
IssuesText = Mid(Selection.Characters.Text, 9)
sqlstring = "UPDATE comments"
sqlstring = sqlstring & " SET Comments = '" & CommentsText & "'"
sqlstring = sqlstring & ",Issues = '" & IssuesText & "'"
sqlstring = sqlstring & " WHERE Title = '" & Trim(Worksheets("Report").ComboBox1.Value) & "' AND Month_ = '" & Trim(Worksheets("Report").ComboBox2.Value) & "'"
oName = Worksheets("Report").ComboBox1.Value
oDate = Trim(Format(Worksheets("Report").ComboBox2.Value, "mmmm-yyyy"))
Options = "(Title, Month_, Issues, Comments)"
Values = "('" & Trim(Worksheets("Report").ComboBox1.Value) & "','" & Trim(Worksheets("Report").ComboBox2.Value) & "','" & IssuesText & "','" & CommentsText & "');"

sqlstring1 = "Insert into comments" & Options & "VALUES" & Values
'sqlstring = sqlstring & "("  "," "," & CommentsText & "," & IssuesText & ")"
'MsgBox sqlstring

updatecond = "select * from comments where Title = '" & oName & "' AND Month_ = '" & oDate & "'"

Dim cnComments As ADODB.Connection
Set cnComments = New ADODB.Connection
Dim strConn As String
strConn = "PROVIDER=SQLOLEDB;DATA SOURCE=SQLserver;INITIAL CATALOG=Reporting;INTEGRATED SECURITY=SSPI"
cnComments.Open strConn
cnComments.Execute (updatecond)
Dim rsComments As ADODB.Recordset
Set rsComments = New ADODB.Recordset
MsgBox rsComments.RecordCount
'rsupdate = cnComments.Execute(updatecond)
'rsComments.Open (updatecond)
If Not (rsComments.BOF Or rsComments.EOF) Then
'MsgBox sqlstring
'If rsComments.RecordCount > 0 Then
'If rsComments.RecordCount > 0 Then
MsgBox sqlstring
With rsComments
 .ActiveConnection = cnComments
 .Open sqlstring
 .Close
End With
Else
MsgBox sqlstring1
cnComments.Execute sqlstring1
End If
cnComments.Close
Set rsComments = Nothing
Set cnComments = Nothing
    
End Sub

I know it might be just a simple thing but I am not able to get this right.

Thanks for all your help.
ip

 

[SkipVought]

Hi,

Please point out the statement that is not functioning.

Skip,

_[red][/red]

 

[indupriya9]

Hi Skip

Here is the code I think that is not functioning.

If Not (rsComments.BOF Or rsComments.EOF) Then

What I would like to achieve is if a record already exists for the title and month, I would like to update the exisitng record, but if a record does not exisit for the title and month then I would like to insert a new record.

 

[PHV]

At the time you test the .BOF and .EOF properties the recordset is just instantiated and not open ...

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886