|
Hi
I have 3 tables
Product(productID , productName , styleID , artistID )
, Artist(artistID , artistName )
, Style(styleID , styleName ) and relationShips :
Style 1 ---> N Product
Artist 1 ---> N Product
and this Command :
oleDbCommand = new OleDbCommand (
"select Product.productName , Artist.artistName , Style.styleName from ( Product inner join Artist on Product.artistID = Artist.artistID inner join Style on Product.styleID = Style.styleID )where productID = @productID", oleDbConnection );
But I got error :
Syntax error (missing operator) in query expression 'Product.artistID = Artist.artistID inner join Style on Product.styleID = Style.styleID'.
|
|
|
|
|
Try this:
SELECT Product.productName, Artist.artistName, Style.styleName
FROM Product
INNER JOIN Artist on Product.artistID = Artist.artistID
INNER JOIN Style on Product.styleID = Style.styleID
WHERE productID = @productID
Note: The only difference is that I removed the brackets. With the brackets it was expecting a subquery which you don't use here.
My: Blog | Photos
WDevs.com - Open Source Code Hosting, Blogs, FTP, Mail and More
|
|
|
|
|
Oh i dont know what should i do
this doesnt work :
Colin Angus Mackay wrote:
oleDbCommand = new OleDbCommand ( "select Product.productID , Artist.artistName , Style.styleName from Product inner join Artist on Product.artistID = Artist.artistID inner join Style on Product.styleID = Style.styleID where productID = @productID", oleDbConnection );
oleDbCommand = new OleDbCommand ( "select Product.productID , Artist.artistName , Style.styleName from Product inner join Artist on Product.artistID = Artist.artistID inner join Style on Product.styleID = Style.styleID where productID = @productID", oleDbConnection );
But this works :
Colin Angus Mackay wrote:
oleDbCommand = new OleDbCommand ( "select Product.productID , Artist.artistName , from Product inner join Artist on Product.artistID = Artist.artistID where productID = @productID", oleDbConnection );
oleDbCommand = new OleDbCommand ( "select Product.productID , Artist.artistName , from Product inner join Artist on Product.artistID = Artist.artistID where productID = @productID", oleDbConnection );
|
|
|
|
|
The code you attribute to me is not my code. I always write SELECT, FROM, INNER JOIN, WHERE etc. in upper case. Also I never put a space before a comma, only after it.
The code you say that works has a syntax error in it. This is obviously not the code you are using. If you are going to take code from your application please copy and paste it properly rather than rewrite it.
Also, in what way does the first not work? The only difference between the two is that in the second the join to "Style" has been removed. Could it be that the Style table has no rows that join on to the Product table? If that is correct then, naturally, the result set will be empty.
My: Blog | Photos
WDevs.com - Open Source Code Hosting, Blogs, FTP, Mail and More
|
|
|
|
|
Hi
First
i didnt add "Colin Angus Mackay wrote:"
and dont know how it was added to my reply and i'm sorry.
Second
i got syntax error on first command and on first not
|
|
|
|
|
Hi
First
i didnt add "Colin Angus Mackay wrote:"
and dont know how it was added to my reply and i'm sorry.
Second
i got syntax error on first command and on second not
|
|
|
|
|
WDI wrote:
dont know how it was added to my reply and i'm sorry.
Okay, fair enough then.
WDI wrote:
i got syntax error on first command and on second not
Hmmm.... There is an additional comma before the FROM on the second command. So I can only assume that it wasn't copied and pasted from visual studio properly.
What is the exact error? It is a bit vague saying "I got a syntax error" without actually seeing the error message. SQL Server contains almost 4000 error messages that help point to the cause of the error. This is useful information and should not be discarded while searching for a solution.
My: Blog | Photos
WDevs.com - Open Source Code Hosting, Blogs, FTP, Mail and More
|
|
|
|
|
i 've Pasted from code :
Has not Error
oleDbCommand = new OleDbCommand ( "select Product.productID , Artist.artistName from Product inner join Artist on Product.artistID = Artist.artistID where productID = @productID", oleDbConnection );
--------------------------------------------------------------------------------------
Has Error :
oleDbCommand = new OleDbCommand ( "select Product.productID , Artist.artistName , Style.styleName from Product inner join Artist on Product.artistID = Artist.artistID inner join Style on Product.styleID = Style.styleID where productID = @productID", oleDbConnection );
Error :
Syntax error (missing operator) in query expression 'Product.artistID = Artist.artistID inner join Style on Product.styleID = Style.styleID'.
|
|
|
|
|
|
I'm sure . i got error when i have 2 inner join cuz i test each of theme seperatly.
|
|
|
|
|
Hi,
I am reviewing existing code and stumbled across something that, to me - and I'm new to ADO.NET, seems very awkward. A DataSet is filled using a OleDbDataAdapter. The weird thing is that the command does not affect any records at all, intentionally. It looks something like "SELECT ... WHERE 0 = 1".
The comment describing the method containing this code says "Retrieve an empty dataset that contains the database structure". DataSet Quick Whatch reports the dataset to be empty after the fill.
Does this SELECT statement really do any good? 
Thanks for comments, ideas or advise!
|
|
|
|
|
I don't use datasets because they tend to encourage the data layer to get too close to the presentation layer (not good design). The SELECT statement returns, as you've already noticed, an empty set. But that isn't to say that nothing at all was returned. I'm guessing that the empty DataSet is subsequently used to look at the data schema without actually needing to get any data. Obviously, it would be wasteful to get data that isn't going to be used.
My: Blog | Photos
WDevs.com - Open Source Code Hosting, Blogs, FTP, Mail and More
|
|
|
|
|
This is usually a way to get an empty dataset with the correct stucture. It may be used for collecting and adding new rows to the database or perhaps merging data from another program layer before updating the database. I sometimes use this in large stored procedures which produce mutitable datasets; some of the tables may be empty, but my data layer expects the table structure to be there so I can add relationships, so I use a WHERE 0 = 1 to create an empty table.
|
|
|
|
|
Thank you for your informative answer!
However, let's extend my question somewhat. I understand why someone would want to do this, and when to use it. What I don't understand is why this is done in light of what the code does next.
instanceDS = m_InstDb.GetInstanceDBDefinition();
string[] arl = new string[1];
arl[0]="T_Project";
instanceDS = m_InstDb.GetDataSetFromTables(arl);
Doesn't the fourth line of code overwrite what was initially in 'instanceDS'? If it does, then the first line should be superflous, or am I wrong? Does preparing 'instanceDS' do any good when its about to be assigned something else?
The first assignment to 'instanceDS' takes a long time (according a profiling tool I use), because the DataSet is "filled" with the structure of several tables, and my task as of now is to optimize the code for speed.
Grateful for more help!
|
|
|
|
|
Yes, that's not great code. In the most optomistic light, it looks like it might have been introduced during modification of the code (where it might have been used before the re-assignment). In the worst case, I would check if the variable instanceDS has a scope wider than the local procedure and make sure it is not used from within GetDataSetFromTables(). That would be REALLY bad. Also check that no other objects are affected from within GetInstanceDBDefinition(); it might be modifying some properties for later use(side effects).
|
|
|
|
|
Hi,
I've just started working with ADO.NET, through which I read from and update a MS Access data base. Now, what I want to do is to first update exactly one row in a data base, and then retrieve the updated row. Currently, this is performed in two steps. However, I want to reduce these two steps into one batch statement like this "UPDATE....; SELECT...." to increase performance.
OleDbCommand.ExecuteReader() throws an exception saying "Characters found after end of SQL statement.". What does this mean? I have tried to google my way to an answer, but I get mixed messages and become more and more confused the more I read.
Is it possible to batch several statements, and query them on the same connection using MS Access? This is where people seem to disagree.
Also, I can't use a stored procedure (which would be ideal btw), because the SELECT-statement returns a value, and that's not possible with MS Access  .
Any help and/or suggestions would be much appreciated!
Thanks
|
|
|
|
|
I have your problem too.
So i had to execute each statement seperatly to work e.g to insert a row and get its ID :
oleDbCommand = new OleDbCommand ( "insert into myTable ... " , oleDbConnection );
.
.
.
add parameters
oleDbCommand.ExecuteNonQuery();
oleDbCommand = new OleDbCommand ( "select @@IDENTITY" , oleDbConnection );
int retValue = Convert.ToInt16( oleDbCommand.ExecuteScalar() );
|
|
|
|
|
I've just learned that Access can't handle batching of statements, whereas SQL Server (probably among others) does. In other words, another weekness of MS Access, which I'm terribly stuck with 
Thank you for your comment anyway!
|
|
|
|
|
hi
i want to connect to a sql database from a stored procedure of another sql database and insert data into a table.
shelly
|
|
|
|
|
|
Hello friends!
I want to change the owner of the object
I have table whose owner is dbo but i want to change it to hov
can u please tell me complete syntax of that
Also when we right click on table and select design table , here we find menu like Trigger then Show permission then show dependency like that
If I chose show permission here we find list all users/userdefined database roles/public like that , can u please explain that thing (window) to me what that mean by?
T.I.A
Shashank
|
|
|
|
|
What are you talking about ? I got to Design Table in SQL Server, then I could not follow your instructions - are you using SQL Server, or something else ?
Not that you need to use SQL Server, but you could provide at least some clue, seeing as your question is not about SQL, ADO, or ADO.NET.
Christian Graus - Microsoft MVP - C++
|
|
|
|
|
under2811Hi
I am asking two thing first is how to change owner of a table
i.e. I have table whose owner is 'dbo' and now I want to change owner dbo to owner 'hov'
How to change that???
second thing i asked if we open E.M here we find lots of table suppose if I right click on one table say employees then we get menus like new table,design table,open table like that if i cliked on design table , we find another more menus like set primary keys,triggers,show permissions like that now i want to know what this term 'show permission' is???
please explain in detail
shashank
|
|
|
|
|
Ah, I see. It shows the users of the database, and what permissions each of them has in regard to that table.
Or, if you don't know what it is, don't mess with it 
The answer to question 1 is probably sp_changeobjectowner. Look it up in the SQL Server books that come with the product.
Christian Graus - Microsoft MVP - C++
|
|
|
|
|
I don't know how to do it from enterprize manager, but you can run sp_changeobjectowner from query analyzer to change the ownership of a table.
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
