SQL Syntax error, Can't find it

Question

I am making an application for a school project. And constantly when I try and execute a stored procedure I get an error message saying invalid syntax.

This is my code from Vba and stored proceduer

CREATE PROCEDURE spVeranderPrijs
(
@ArtikelNr  integer,
@WijzigingsDatum Date,
@NieuwePrijs integer
)
AS 
BEGIN Transaction

Select * from artikelprijs
where artikelnr = @ArtikelNr
and einddatum = '2099-01-01'

  if @@ERROR <> 0

    update ArtikelPrijs set einddatum = @Wijzigingsdatum

    INSERT INTO Artikelprijs
    VALUES (@ArtikelNr, @NieuwePrijs, @WijzigingsDatum, '2099-01-01');

  BEGIN

    ROLLBACK
    RAISERROR ('Error tijdens het uitvoeren', 16 , 1)
    RETURN
  END 
Commit

GO

And my code in Vba

Private Sub Command6_Click()

Dim conn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim sConnString As String
sConnString = "Provider=SQLOLEDB;Data Source=.\SQLEXPRESS;" & _
  "Initial Catalog=KlantArtikelApp;" & _
  "Integrated Security=SSPI;"

Set conn = New ADODB.Connection
Set rs = New ADODB.Recordset

conn.Open sConnString
Set rs = conn.Execute("EXEC spVeranderprijs  '" & TxTArtikelNr & "', '" & TxTWijzigingsDatum & "' '" & TxTPrijs & "'")


End Sub

I cant seem to get it work, could anyone help?

Incorrect syntax near '35' ( which is the input i put into TxTPrijs ) — Martijn Dekkers, Sep 30 '16 at 10:29
[How to debug dynamic SQL in VBA](http://stackoverflow.com/questions/418960/managing-and-debugging-sql-queries-in-ms-access/1099570#1099570) . A simple `Debug.Print ` would have shown you the error. — Andre, Sep 30 '16 at 11:10

score 0 · Answer 1 · answered Sep 30 '16 at 10:29

CREATE PROCEDURE spVeranderPrijs
(
DECLARE @ArtikelNr  integer,
@WijzigingsDatum Date,
@NieuwePrijs integer
)
AS 
BEGIN Transaction

Select * from artikelprijs
where artikelnr = @ArtikelNr
and einddatum = '2099-01-01'

  if @@ERROR <> 0

    update ArtikelPrijs set einddatum = @Wijzigingsdatum

    INSERT INTO Artikelprijs
    VALUES (@ArtikelNr, @NieuwePrijs, @WijzigingsDatum, '2099-01-01');

  BEGIN

    ROLLBACK
    RAISERROR ('Error tijdens het uitvoeren', 16 , 1)
    RETURN
  END 
Commit

GO

I just added the declare statement. maybe that's why you are getting an invalid error message

score 0 · Answer 2 · answered Sep 30 '16 at 10:36

In this line:

conn.Execute("EXEC spVeranderprijs  '" & TxTArtikelNr & "', '" & TxTWijzigingsDatum & "' '" & TxTPrijs & "'")

you are passing in TxTArtikelNr and TxTPrijs inside single quotes. Your stored procedure expects an integer for these parameters, so lose the single quotes from the line and pass it like this (I've also added a missing comma between your last two parameters):

conn.Execute("EXEC spVeranderprijs  " & TxTArtikelNr & ", '" & TxTWijzigingsDatum & "', " & TxTPrijs)

score 0 · Answer 3 · answered Sep 30 '16 at 10:37

Your stored procedure expects 3 parameters. So you are missing a comma after TxTWijzigingsDatum.

Set rs = conn.Execute("EXEC spVeranderprijs " & TxTArtikelNr & ", '" & TxTWijzigingsDatum & "' ," & TxTPrijs)

There is no need to quote integer values. Only dates or strings.

This type of coding should be avoided though. If you use this code to insert strings that contain single quotes ('), they will break your query. You have to escape them or better yet use parameterized queries instead.

SQL Syntax error, Can't find it

3 Answers3