Solved

recordset failure with odbc server

Posted on 2004-04-06
18
356 Views
Last Modified: 2010-05-02
im using vb.net and i have set up a remote odbc connection kinda like this

conn.ConnectionString = "DSN=path;Server=myServer;uid=User;pwd=pass"

i can connect to the server just fine, but when i pass an SQL statement to get a recordset, thats when I get problems.  the error that comes up says that i have invalid SQL syntax.  

the only problem is that i do not have invalid SQL syntax.  when i run the same exact query in access, it works jsut fine.  

btw, im using ado to connect to the odbc server.....if anyone knows howto do it in ado.net id also entertain that idea.

thanks
0
Comment
Question by:bramsquad
  • 8
  • 7
  • 2
  • +1
18 Comments
 
LVL 19

Expert Comment

by:Shauli
ID: 10768854
Post your sql string here

S
0
 
LVL 29

Expert Comment

by:leonstryker
ID: 10769004
The problem here is that ADO.NET does not have recordsets and requires a totally different way of accessing data from the database.  Take a look here:

http://samples.gotdotnet.com/quickstart/howto/doc/adoplus%5Cemployees.aspx

Leon
0
 
LVL 8

Author Comment

by:bramsquad
ID: 10769018
my sql string works perfectly in access, so i know its not that.......but either way, here it is

SELECT TOP 10 Time.Week_End_Date, Organization.District_Num, Organization.Region_Num, Account_Detail.Account_Num, Account_Detail.Account_Name, Volume_and_Revenue_DETAIL.Package_Quantity, Volume_and_Revenue_DETAIL.Gross_Amt_USD, Volume_and_Revenue_DETAIL.Base_or_Accessorial_Cd, Volume_and_Revenue_DETAIL.Customer_Shipment_Role_Cd FROM [Time], Organization, Volume_and_Revenue_DETAIL, Account_Detail GROUP BY Time.Week_End_Date, Organization.District_Num, Organization.Region_Num, Account_Detail.Account_Num, Account_Detail.Account_Name, Volume_and_Revenue_DETAIL.Package_Quantity, Volume_and_Revenue_DETAIL.Gross_Amt_USD, Volume_and_Revenue_DETAIL.Base_or_Accessorial_Cd, Volume_and_Revenue_DETAIL.Customer_Shipment_Role_Cd HAVING (((Time.Week_End_Date) Between #3/14/2004# And #3/20/2004#) AND ((Organization.District_Num)='52') AND ((Organization.Region_Num)='05')) ORDER BY Volume_and_Revenue_DETAIL.Gross_Amt_USD DESC;"
0
 
LVL 8

Author Comment

by:bramsquad
ID: 10769084
leon,

right now im referencing ado 2.7 into my project (and have been with every dot net app ive ever written).........thing is, ive never used nor learned ado.net (laziness i suppose).  ill look into ado.net for this problem, i just wanted to let you know thats not the reason its failing.

~b
0
 
LVL 19

Expert Comment

by:Shauli
ID: 10769158
Do you hardcode these values (#3/14/2004# #3/20/2004# '52' '05') or do you get it from a user interface, such as textboxes etc? and is 52 a string and not a number?

S
0
 
LVL 8

Author Comment

by:bramsquad
ID: 10769306
textboxes, and they both are strings
0
 
LVL 8

Author Comment

by:bramsquad
ID: 10769326
the reason i know this query works specifically, is because i went into break mode, got the value of "pstrSQL" which is the variable to where that statement is held, and copy and pasted it into access.  
0
 
LVL 15

Expert Comment

by:unknown_routine
ID: 10770328
Hi,

Change your SQL to this:( I tested it and it works)

SELECT TOP 10 Time.Week_End_Date, Organization.District_Num, Organization.Region_Num,
 Account_Detail.Account_Num, Account_Detail.Account_Name, Volume_and_Revenue_DETAIL.Package_Quantity,
 Volume_and_Revenue_DETAIL.Gross_Amt_USD, Volume_and_Revenue_DETAIL.Base_or_Accessorial_Cd,
Volume_and_Revenue_DETAIL.Customer_Shipment_Role_Cd
 FROM
[Time], Organization, Volume_and_Revenue_DETAIL, Account_Detail
GROUP BY Time.Week_End_Date, Organization.District_Num, Organization.Region_Num, Account_Detail.Account_Num,
 Account_Detail.Account_Name, Volume_and_Revenue_DETAIL.Package_Quantity, Volume_and_Revenue_DETAIL.Gross_Amt_USD,
 Volume_and_Revenue_DETAIL.Base_or_Accessorial_Cd, Volume_and_Revenue_DETAIL.Customer_Shipment_Role_Cd
 HAVING
 Time.Week_End_Date Between '#3/14/2004#' And '#3/20/2004#' AND Organization.District_Num='52'
 AND Organization.Region_Num='05'
 ORDER BY Volume_and_Revenue_DETAIL.Gross_Amt_USD DESC;

0
 
LVL 15

Expert Comment

by:unknown_routine
ID: 10770366
sorry still had a little bug, change to this:


SELECT TOP 10 Time.Week_End_Date, Organization.District_Num, Organization.Region_Num,
 Account_Detail.Account_Num, Account_Detail.Account_Name, Volume_and_Revenue_DETAIL.Package_Quantity,
 Volume_and_Revenue_DETAIL.Gross_Amt_USD, Volume_and_Revenue_DETAIL.Base_or_Accessorial_Cd,
Volume_and_Revenue_DETAIL.Customer_Shipment_Role_Cd
 FROM
[Time], Organization, Volume_and_Revenue_DETAIL, Account_Detail
GROUP BY Time.Week_End_Date, Organization.District_Num, Organization.Region_Num, Account_Detail.Account_Num,
 Account_Detail.Account_Name, Volume_and_Revenue_DETAIL.Package_Quantity, Volume_and_Revenue_DETAIL.Gross_Amt_USD,
 Volume_and_Revenue_DETAIL.Base_or_Accessorial_Cd, Volume_and_Revenue_DETAIL.Customer_Shipment_Role_Cd
 HAVING
 Time.Week_End_Date Between '3/14/2004' And '3/20/2004' AND Organization.District_Num='52'
 AND Organization.Region_Num='05'
 ORDER BY Volume_and_Revenue_DETAIL.Gross_Amt_USD DESC;
0
6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

 
LVL 8

Author Comment

by:bramsquad
ID: 10774583
how did you test it?  is all you changed is the data type of the date fields?

either way, i still get an error..............im 99% its not the query, i once read that odbc databases can only accept pass-through queries.....does anyone know about that?

ill up this to 1000 pts (ill do a 'points for ####' question after this one is accepted if anyone can help me nail this.
0
 
LVL 15

Expert Comment

by:unknown_routine
ID: 10775514
I  tested  my  second post and it run sucessfully.

I rechecked the syntax using query analyser and it runs successfully.

The SQL I gave you removes 2 bugs in  SQL expression you provided:

1: There  was wrong number of unnecessary  paranteses in the SQL

2: you can not enter explicit Dates using SQL using #   #.

after these corrections I made some tables with few fake data and query worked.
but still I don't have your underlying data so you may get different  results.

Now:

you say:  I still get an error :  Please explain what error you get? write error number and error description


ODBC ( as well as OLEDB) can handle this query very well, and there is no worry for that.

and finally tell me what database  your using? SQL sever? access? .....?

0
 
LVL 8

Author Comment

by:bramsquad
ID: 10775793
i dont know if youve heard of it but its a database called "data warehouse"  it might be the reason why its not working.  that server im assuming is an oracle server.  either way, we can (and always have been able to) write and execute queries via a "data warehouse" version of access.  so thats why i knew my query worked because ive executed it many times.  

right now im trying to do the same thing without the dwh-Access......strictly through vb.  i can connect to the server just fine.  however, when i run a query, i get the error

"Unsupported query syntax"

ive even tried very basic queries (where its almost impossible to have bad sytax) and the same message has shown for them as well.  

hope this info helps...

~b
0
 
LVL 15

Expert Comment

by:unknown_routine
ID: 10775931
Unsupported query syntax message is very informative.

It shows that your using a SQL lanuage thatSQL server does not recognize.

The SQL I fixed above it for SQL server and access.

Now before we go any further it is very important you make sure that your dealing oracle serve. so

we can work on the correct SQL format fot it.

By the way don't let Term 'Data warehouse'  scares you. lol

A data warehouse it simply a Database filled with a company bussiness data. So it's nothing more than a database.


0
 
LVL 8

Author Comment

by:bramsquad
ID: 10776268
its definately an oracle db

as far as this data warehouse version of access goes......this really confuses me.

we dont have to link tables, becuase they are already linked....sometimes i have to include fields i dont need due to how the db is set up.......and the most confusing thing is the fact that i access information in data warehouse through an ms access interface, i write queries and access that work, however, when i bring them to vb the sql syntax is wrong.  is the data warehouse software changing the sql code?
0
 
LVL 15

Expert Comment

by:unknown_routine
ID: 10776329
I understand what your saying, its a confusing situation, that is interfacing access with oracle.


As you said you write queries with access they work fine but when you send it using VB to send thjem to

oracle VB says sytax is wrong. this is because Oracle uses a different SQL syntax.

Now give me some time to work on this.
0
 
LVL 15

Accepted Solution

by:
unknown_routine earned 500 total points
ID: 10776443
ok Admitingly I'm not able to help anymore!
while been DB programmer for years, I have little exp with oracle.

Now:  to move on:

1: post the above working SQL in Oracle section of this forum and ask them to translate it to oracle SQL.
(you can point them to this thread)


2: once you got a working Oracle SQL, try it if still itsworking, if not then it's time to go The oracle DB admin

And ask for exact Database Structure.

sorry that I can not be of further help for this.
0
 
LVL 8

Author Comment

by:bramsquad
ID: 10776676
thank you for your patience.   much appriciated.

~b
0
 
LVL 15

Expert Comment

by:unknown_routine
ID: 10777105
Thanks for points ,and I appreciate if you find the time to
update this thread  if you find the complete solution or workaroud.
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

I’ve seen a number of people looking for examples of how to access web services from VB6.  I’ve been using a test harness I built in VB6 (using many resources I found online) that I use for small projects to work out how to communicate with web serv…
If you have ever used Microsoft Word then you know that it has a good spell checker and it may have occurred to you that the ability to check spelling might be a nice piece of functionality to add to certain applications of yours. Well the code that…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

706 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now