Microsoft Access Developer's Guide to SQL Server (Mary, Andy or anyone) question

  • Thread starter Thread starter KD
  • Start date Start date
K

KD

Access 2000 SP-3
SQL Server 2000, SP unknown

I bought the book when I created my first SQL Server application in 2001,
and it saved me many times over. I've been using code to populate combo
boxes in disconnected adp's. Recently, this started failing, and the only
difference I can see is that the code is failing with parameters that are
longer than just a few characters.

For example, this works fine:
Row Source Type: UseLocalFile
Row Source: procABC 0, -2

So does this:
Row Source: procDEF Batches, CodeNum

This one fails:
Row Source: procGHI 00078660003296SS3365000, 0

The failure occurs (when I "Break on all Errors") in module modLocalStorage,
procedure HandleLocalStorage, at
rst.Open Source:=ctl.RowSource

The error I get is:
Run-time error '-2147217900 (80040e14)':
Line 1: Incorrect syntax near 'SS3365000'

Note the truncated parameter (from '00078660003296SS3365000' to 'SS3365000')
However, when I look up the value of ctl.RowSource in the immediate window,
it contains the correct non-truncated parameter.

The stored procedure does work when I test it from query analyzer. It's a
simple "Select" procedure, accepting two parameters.

This is driving me nuts. I simply cannot figure out why this error is
happening. I searched the MSKB and newsgroups hi & lo, and although there
are many references to "Line 1: Incorrect syntax", none of them appear to
apply.
Please, someone point me in the right direction.

Sincerely,
Klaus
 
I don't have their book handy. What is the first parameter of the proc
defined as?

In your first example (procABC 0, -2), you're passing it a number. In the
example that doesn't work (procGHI 00078660003296SS3365000, 0), you're
trying to pass it a string, only you haven't got quotes around the string.

In the event that it accepts a variant, try procGHI
"00078660003296SS3365000", 0
 
Doug,

thanks for replying. The function specified in the combo box's Row Source
Type (UseLocalFile) takes care of the quotes. This may have been a bad
example, since XYZ0007866003365000-abc would have failed just as well.
However, procDEF Batches, CodeNum (both parameters are strings, no quotes)
does not fail.

The code to the book is available for download at the publisher's website.
Although I assume it would be OK to do so, since it appears to be available
to anyone, I don't just want to provide the direct link to the download, but
it's easy enough to find. The code in question, which is quite clever, is in
Chapter 11, Unbound ADPs: List Box and Combo Box Techniques for Unbound
ADPs. It basically creates an XML for each combo box/list box using this
approach, which contains the lookup data from the stored procedure.

The modules are basLocalStorage, and the two class modules,
ControlsToFillLocally, and ControlToFillLocally.
The procedure UseLocalFile, which is the combo box rowsource tpye, is
located in basLocalStorage, which also contains HandleLocalStorage, where
the error occurs (rst.Open Source:=ctl.RowSource).

Thanks,
Klaus
 
Doug,

thanks for replying. The function specified in the combo box's Row Source
Type (UseLocalFile) takes care of the quotes. This may have been a bad
example, since XYZ0007866003365000-abc would have failed just as well.
However, procDEF Batches, CodeNum (both parameters are strings, no quotes)
does not fail.

The code to the book is available for download at the publisher's website.
Although I assume it would be OK to do so, since it appears to be available
to anyone, I don't just want to provide the direct link to the download, but
it's easy enough to find. The code in question, which is quite clever, is in
Chapter 11, Unbound ADPs: List Box and Combo Box Techniques for Unbound
ADPs. It basically creates an XML for each combo box/list box using this
approach, which contains the lookup data from the stored procedure.

The modules are basLocalStorage, and the two class modules,
ControlsToFillLocally, and ControlToFillLocally.
The procedure UseLocalFile, which is the combo box rowsource tpye, is
located in basLocalStorage, which also contains HandleLocalStorage, where
the error occurs (rst.Open Source:=ctl.RowSource).

Thanks,
Klaus
 
When we wrote the book, we had beta bits given to us directly from the
Access team that worked with disconnected recordsets, etc. that ended
up getting yanked from the RTM bits, and some of the code bombs in the
release version of A2k, although i don't think that's your specific
problem.

I'm not sure about the parameter issue with the code you are using
(it's been a long time since we wrote the book), but it could possibly
be a bug in A2k if the exact same code works fine with shorter
parameter values. Since it's an incorrect syntax error message, try
writing all of the values out to variables and use debug.print
statements to see what's getting sent before and after. To see what
the server is receiving, create a Profile trace to look at the
conversation. That way you'll be able to determine if SQLS is getting
a truncated string from Access.

-- Mary
MCW Technologies
http://www.mcwtech.com
 
Back
Top