recordset failure with odbc server

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
LVL 8
bramsquadAsked:
Who is Participating?
 
unknown_routineConnect With a Mentor Commented:
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
 
ShauliCommented:
Post your sql string here

S
0
 
leonstrykerCommented:
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
Cloud Class® Course: MCSA MCSE Windows Server 2012

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

 
bramsquadAuthor Commented:
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
 
bramsquadAuthor Commented:
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
 
ShauliCommented:
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
 
bramsquadAuthor Commented:
textboxes, and they both are strings
0
 
bramsquadAuthor Commented:
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
 
unknown_routineCommented:
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
 
unknown_routineCommented:
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
 
bramsquadAuthor Commented:
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
 
unknown_routineCommented:
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
 
bramsquadAuthor Commented:
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
 
unknown_routineCommented:
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
 
bramsquadAuthor Commented:
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
 
unknown_routineCommented:
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
 
bramsquadAuthor Commented:
thank you for your patience.   much appriciated.

~b
0
 
unknown_routineCommented:
Thanks for points ,and I appreciate if you find the time to
update this thread  if you find the complete solution or workaroud.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.