Syntax error or access violation while executing stored proc

[Alira]

Hi!

I'm getting this error...
Here is my code:
Dim db As Connection
Dim rsLog As ADODB.Recordset
Dim comLog As New ADODB.Command

Set db = New Connection
db.CursorLocation = adUseClient
db.Open "PROVIDER=MSDASQL;dsn=mydcn;uid=Win2000;pwd=;database=mydatabase;"

With comLog

.ActiveConnection = db
.CommandType = adCmdStoredProc
.CommandText = usagelogupdate
.Execute

End With

StoredProc:

CREATE PROCEDURE [dbo].[mytable] AS
DECLARE @var char(3)
SET @var = datepart(mm,GETDATE())

delete from mytable where substring(myfield,1,3) = @var
GO

??? Have a great day!

 

[MarkSweetland]

Since you declared the @var as a char(3), you might want to use the DateName function that returns the character name (August) of the current month rather than datepart which returns the integer representing the month (8)

SET @var = datename(month,GETDATE())
/* char(3) truncates August to Aug */


delete from mytable where substring(myfield,1,3) = @var



Mark