Yet another syntax issue.

  • Thread starter Thread starter AkAlan via AccessMonster.com
  • Start date Start date
A

AkAlan via AccessMonster.com

I can get this stored procedure to run when I call it manually but not with
VBA. Here is the code:

Dim cnn As ADODB.Connection
Dim cmd As ADODB.Command
Dim prm As ADODB.Parameter
Dim strJcn As String

Set cnn = CurrentProject.Connection
Set cmd = New ADODB.Command

strJcn = "'" & Me![STATUS_ESR_JCN] & "'"
Set cmd.ActiveConnection = cnn
cmd.CommandText = "pr_ReOpenEsr"
cmd.CommandType = adCmdStoredProc
Set prm = cmd.CreateParameter("@Jcn", adBSTR, adParamInput, , strJcn)
cmd.Parameters.Append prm
cmd.Execute

It runs through with no error, it just doesnt delete the two date fields I
want it to. I run another sp almost exactly like this one for a subform and
it runs just fine the only difference is that the parameter for that one is
an integer.

Here is the pr_ReOpen sp SQL :

UPDATE dbo.ESR
SET ESR_CLOSE_DATE = NULL, ESR_CLOSE_TIME = NULL
WHERE (ESR_JCN = @JCN)

I have tried it without the single quote around the strJcn variable and same
thing. Thanks for your help.
 
Why are you enclosing in single quotes ' if you pass this string value as a
parameter?

(Personally, instead of using adBSTR, I use adChar, adWChar, adVarChar and
adVarWChar and I take the precaution of setting the size.)
 
I thought you had to pass string and date variables using single quotes. The
dataype of the strJcn in the table is nvarchar. It was a 9 digit text value
before upsizing from an mdb. I have tried the potions you suggested even
before posting but I get an error 3708 Parameter Object is improperly defined.
Inconsistant or Incomplete information was provided. So I'm at a loss for
whether or not to use quotes or which datatype to pass to the sp. I'm trying
all combonations till I get it right. Like I said if I just open the sp from
the queries collection and type in a valid job Number, it works just fine.
Thanks once again for helping Sylvain, you have been a great help these last
few weeks.


Sylvain said:
Why are you enclosing in single quotes ' if you pass this string value as a
parameter?

(Personally, instead of using adBSTR, I use adChar, adWChar, adVarChar and
adVarWChar and I take the precaution of setting the size.)
I can get this stored procedure to run when I call it manually but not with
VBA. Here is the code:
Click to expand...
[quoted text clipped - 32 lines]
same
thing. Thanks for your help.
Click to expand...
Click to expand...
 
You don't show us the code with the definition of the parameter(s) for the
stored procedure, so I cannot tell you why you get the error 3708.

However, for the string value, you need to use quotes only when you are
building a string representation of the SQL statement; otherwise it would be
impossible for the compilateur to know where your string ends:

UPDATE dbo.ESR
SET ESR_CLOSE_DATE = NULL, ESR_CLOSE_TIME = NULL
WHERE (ESR_JCN = 'blablabla ...')

but you don't use when you have a variable or a parameter. With the
following syntax, the compilateur doesn't have to know where the string ends
because it's not a literal value:

UPDATE dbo.ESR
SET ESR_CLOSE_DATE = NULL, ESR_CLOSE_TIME = NULL
WHERE (ESR_JCN = @JCN)

Same thing for datetime fields.

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: http://cerbermail.com/?QugbLEWINF


AkAlan via AccessMonster.com said:
I thought you had to pass string and date variables using single quotes.
The
dataype of the strJcn in the table is nvarchar. It was a 9 digit text
value
before upsizing from an mdb. I have tried the potions you suggested even
before posting but I get an error 3708 Parameter Object is improperly
defined.
Inconsistant or Incomplete information was provided. So I'm at a loss for
whether or not to use quotes or which datatype to pass to the sp. I'm
trying
all combonations till I get it right. Like I said if I just open the sp
from
the queries collection and type in a valid job Number, it works just fine.
Thanks once again for helping Sylvain, you have been a great help these
last
few weeks.


Sylvain said:
Why are you enclosing in single quotes ' if you pass this string value as
a
parameter?

(Personally, instead of using adBSTR, I use adChar, adWChar, adVarChar and
adVarWChar and I take the precaution of setting the size.)
I can get this stored procedure to run when I call it manually but not
with
VBA. Here is the code:
Click to expand...
[quoted text clipped - 32 lines]
same
thing. Thanks for your help.
Click to expand...
Click to expand...
Click to expand...
 
Sylvain, I do have the code for the parameter definition in the first post.
Alos, it's not the stored procedure that is giving me fits, its the VBA code.
When I run the stored procedure from the query list, it prompts me for a Jcn
and runs as I expect it should, replaceing a null value in the two date
fields. When I run it from the code I posted It runs trough with no error, it
just doesn't alter the record, it's as if I passed no valid Jcn. I am using
the Locals window and can see that the intJcn variable is being populated
with the correct value. I have tried every possible combination I can think
of and it still won't work. The only time I get the 3708 error is when I
change the parameter type to something other than adBSTR like adChar, adWChar,
adVarChar . Like I said, I am doing the exact same thing with another stored
procedure that works fine, the only difference is that the variable I use
there is an integer, not a string.

So here is a recap if you are still inclined to help me.

The datatype of the Jcn in the table is nvarchar

The variable I use to grab the Jcn from the form is string
I have tried all of the following ways of populating the variable

strJcn = "'" & Me![STATUS_ESR_JCN] & "'"
strJcn = Me![STATUS_ESR_JCN]
strJcn = "001162001" (which is a valid Jcn )
strJcn = Me.[STATUS_ESR_JCN]
strJcn = "Me![STATUS_ESR_JCN] "


Thanks again. Alan
 
Figured it out, if the parameter is a string, you have to pass the length
when you set it. The Set prm line should have looked like this:
Set prm = cmd.CreateParameter("@Jcn", adBSTR, adParamInput, 9 , strJcn)


I can get this stored procedure to run when I call it manually but not with
VBA. Here is the code:

Dim cnn As ADODB.Connection
Dim cmd As ADODB.Command
Dim prm As ADODB.Parameter
Dim strJcn As String

Set cnn = CurrentProject.Connection
Set cmd = New ADODB.Command

strJcn = "'" & Me![STATUS_ESR_JCN] & "'"
Set cmd.ActiveConnection = cnn
cmd.CommandText = "pr_ReOpenEsr"
cmd.CommandType = adCmdStoredProc
Set prm = cmd.CreateParameter("@Jcn", adBSTR, adParamInput, , strJcn)
cmd.Parameters.Append prm
cmd.Execute

It runs through with no error, it just doesnt delete the two date fields I
want it to. I run another sp almost exactly like this one for a subform and
it runs just fine the only difference is that the parameter for that one is
an integer.

Here is the pr_ReOpen sp SQL :

UPDATE dbo.ESR
SET ESR_CLOSE_DATE = NULL, ESR_CLOSE_TIME = NULL
WHERE (ESR_JCN = @JCN)

I have tried it without the single quote around the strJcn variable and same
thing. Thanks for your help.
Click to expand...
 
Back
Top