where is the error???

[Alejandro]

I have write this code in form of access ADP file:

Private Sub Comando10_Click()

Dim pasa As Variant
Dim SQLt As String
Dim conta, fornec As Integer


conta = Me.conter
fornec = Me.forter

SQLt = "exec inse1 @conta='" & conta & "', @forne= '" & fornec & "' "
DoCmd.RunSQL SQLt

i have the message

incorrect synatx near '47' when i click teh buttom , if write this code
with one parameter in the store procedure the code works but when i include
the second dont work.

Thanks in advance

Alejandro Carnero

 

[Douglas J. Steele]

What sort of values is the stored procedure expecting for the parameters
@conta and @forne? You're including quotes around their values, which is
only correct if the parameters are defined as char or varchar.

Just as an aside, the third Dim statement in your code isn't doing what you
probably think it is.

Dim conta, fornec As Integer

declares conta to be a variant, and fornec to be an integer. If you want
them both to be integers, you need to use

Dim conta As Integer, fornec As Integer

Remember, too, that Integer in VBA is NOT the same as Integer in SQL Server.
The SQL Server Integer is actually VBA's Long Integer.

 

[Alejandro]

I have write the code again but dont work this is the code

Private Sub Comando10_Click()
Dim SXQL As String
Dim conta As Variant
Dim fornec As Variant

conta = 1
fornec = 3

SQL = "exec inse1 @conta='" & conta & "', @forne= '" & fornec & "' "
DoCmd.RunSQL SXQL

End Sub

and this is the procedure


ALTER proc inse1 @conta int, @forne int as
insert xfinanceiro (conta, forne )
select @conta, @forne


the procedure works good in Query analyzer but i can not write this in form
(((((

Thanks Alejandro

 

[Norman Yuan]

remove ' (single quote mark) around the parameter value, since they are
numeric value (integer, according to your SP), like this:

SQL = "exec inse1 @conta=" & conta & ", @forne= " & fornec

The reason it work in Query Analyser is that you are asked to type in the
value and QA knows it is numeric value.

 

[Vadim Rapp]

Hello Douglas:
You wrote in conference microsoft.public.access.adp.sqlserver on Sun, 18
Jul 2004 06:25:33 -0400:

DJS> You're including quotes around their values, which is only correct if
DJS> the parameters are defined as char or varchar.

really? ado or sql server wouldn't convert?


Vadim

 

[Vadim Rapp]

Hello Alejandro:
You wrote in conference microsoft.public.access.adp.sqlserver on Sun, 18
Jul 2004 04:50:10 -0300:


A> DoCmd.RunSQL SQLt

change DoCmd.RunSQL to activeproject.connection.execute

According to Access Help on RunSQL method, "This method only applies to
Microsoft Access databases (.mdb)". Apparently, it fails to correctly pass
named parameters to the stored procedure.

Vadim