Access to SQL Server?

[cj92713696]

I wrote a Visual Basic 6 application that works great w/Access using the data environment. I want to port my existing app. to work w/SQL Server. I assumed it would be as easy as just changing my provider in my Data Environment to SQL Server. I have run into a problem w/my SQL variables. It appears as though Microsoft SQL Server doesn't assess the variables the same way Access does.

Here's an example query:

SELECT * FROM STORE WHERE
(Store.StoreCode = @StoreCode AND Store.StoreDesc = "1&quot
OR
(Store.StoreCode = @StoreCode AND Store.StoreDesc = "2"

In this example, I have the variable @StoreCode listed twice. The only way I can get Microsoft SQL Server to acknowledge that @StoreCode is a variable is to replace it with a ?. @StoreCode then becomes two seperate variables. Is there another way to declare variables w/the Data Environment that SQL Server will acknowledge?

Thanks,
CJ

 

[zemp]

The '@' character is a special character used by SQL Server to identify parameter variables in stored procedures. This symbol may be causing some problems.

Also with SQL server you should use single quotes around string variables.

You can also change your SQL statement to have only one occurance of your variable.

SELECT * FROM Store WHERE (Store.StoreCode = <intStoreCode>) AND (Store.StoreDesc = '1'
OR Store.StoreDesc = '2');

<intStoreCode> is the name of the variable. I selected integer to make it obvious that it was a variable. Change it to what ever type and name you need.

Just to let you know, I don't work with the dataenvironment so my comments are soley based on T-SQL with SQL server. Hopefully Ican be of some help.
Thanks and Good Luck!

zemp

 

[cj92713696]

If I have a query like . . .

SELECT * FROM STORE WHERE Store.StoreCode = 'ABC' AND @Option = &quot;A&quot; or @Option = &quot;B&quot;;

I know this query doesn't really make a lot of sense on the surface, but I have queries that are similiar having a variable declared twice in the syntax. The Visual Basic Data Environment wants to make @Option two seperate parameters when in fact I want it to be one. Is there a way to do this?

Thanks,
CJ