Hi this is my first post here

Using Windows 7 64bit, Excel 2010 and ODBC Driver 2.07 b1

Am used to using odbc within excel with dbisam but have just moved over to EBD in recent weeks.

In EDB, I am able to perform basic Statements such as

SELECT * From Products WHERE ID = 4545

and

UPDATE Products SET StatusID = 5 WHERE ID = 4545

However, as soon as I use 'WHERE ID = ?' Excel will crash.  It crashes when using 32 Bit DSN or 64 bit DSN.

This is something I was using frequently with DBISAM where Excel would prompt for a parameter value from either a text box or cell reference. It opened up much potential for integration of our excel and Delphi(EDB) systems.

Can anyone give me any insight into how ODBC/Excel handles these ? parameters. I really need these to work again!

NB. Unless using dates, I was having to use 'WHERE X = CAST(? as Y)' to match data types. That one took me a few months to work out!



Thanks

John

Hi John,

Been a long time since I used the ElevateDB ODBC driver but I believe it
uses named parameters instead of ?

Have you tried SELECT * FROM Products WHERE Id = :id


Chris Holland [Team Elevate]

On 07/02/2012 12:57, Johnny McRae wrote:
> Hi this is my first post here
>
> Using Windows 7 64bit, Excel 2010 and ODBC Driver 2.07 b1
>
> Am used to using odbc within excel with dbisam but have just moved over to EBD in recent weeks.
>
> In EDB, I am able to perform basic Statements such as
>
> SELECT * From Products WHERE ID = 4545
>
> and
>
> UPDATE Products SET StatusID = 5 WHERE ID = 4545
>
> However, as soon as I use 'WHERE ID = ?' Excel will crash.  It crashes when using 32 Bit DSN or 64 bit DSN.
>
> This is something I was using frequently with DBISAM where Excel would prompt for a parameter value from either a text box or cell reference. It opened up much potential for integration of our excel and Delphi(EDB) systems.
>
> Can anyone give me any insight into how ODBC/Excel handles these ? parameters. I really need these to work again!
>
> NB. Unless using dates, I was having to use 'WHERE X = CAST(? as Y)' to match data types. That one took me a few months to work out!
>
>
>
> Thanks
>
> John
>

>Have you tried SELECT * FROM Products WHERE Id = :id
>
>Chris Holland [Team Elevate]

Hi Chris, thanks for the reponse

I have just tried this 'WHERE Id = :id' and it returns column names without any records. No crashing though!
Excel doesn't seem to recognise it as a parameter so no prompt appears for asking for a value.
Excited, I setup a 'named range' A1 with the value 4545 but still just the column names are returned.

I would have thought that as excel reads through the sql it would

-see the ? then
-request the value from user/spreadsheet then
-send query to odbc driver with the '?' already substituted for desired value

If this was the case then xl would still ask for parameter value regardless of whether i'm using EBD or DBISAM driver. But no. ???

John

Johnny,

<< However, as soon as I use 'WHERE ID = ?' Excel will crash.  It crashes
when using 32 Bit DSN or 64 bit DSN. >>

It's a bug in the way that the SQLDescribeParam API call is declared in the
driver.  I'm doing a new EDB build sometime this week, and I will be
including a fix for this in that build.

Thanks,
--
Tim Young
Elevate Software
www.elevatesoft.com

Fantastic News

Thanks for this Tim

John

Just to complete this thread. . .

The updated driver works great!

Many thanks Tim

John

Johnny McRae wrote:

Just to complete this thread. . .

The updated driver works great!

Many thanks Tim

John

I am still having problems using Excel (Office 2007) with Version 2.08 Build 3. I am getting and error messag3 1011 saying the value will be truncated when I use parameters or if I do not get that message, I just get blank data. Do I have the latest buil?

Bob,

<< I am still having problems using Excel (Office 2007) with Version 2.08
Build 3. I am getting and error messag3 1011 saying the value will be
truncated when I use parameters or if I do not get that message, I just get
blank data. Do I have the latest buil? >>

This is an old post, but if you don't have this resolved yet, please contact
me at support@elevatesoft.com along with more information on what you're
trying to do (how you define the query, etc).

If you have any other questions, please let me know.

Tim Young
Elevate Software
www.elevatesoft.com