How to use C# tableadapter wizard with vendor specific SQL keywords?
Hi. I really like the new tableadapter wizards in VS 2005 and how they can speed up development. However, I'm running into a problem I need help with. I am using the table adapter against DB2 and there are special keywords that the designer isn't allowing.
i.e.
Select field1, field2
from table
for fetch only with ur;
When I enter this into the query wizard and then move off the SQL pane to another pane, the wizard quotes the sql so it looks like so:
Select field1, field2
from table
FOR "fetch" "only" "with" ur;
Hence my predicament. When I try to run this or press the ok button to save the query, it won't run because the odbc driver isn't expecting fetch, only, and with to be in double quotes. Is this a known issue or is there some key token to indicate that the keywords should be left alone.
In case anyone is wondering why this would be a problem, in DB2 the keywords above indicate to not use record locking on a table. In an enterprise situation with potentially thousands of users, record locking is not acceptable. In order for our enterprise to use the new enhancements made in VS 2005 to the tableadapter, we need a way around what the designer is doing.
In addition, this sql goes into the dataset.designer.cs file and I can't manually hack that file up without it being regenerated everytime I press Save. When I do, it also messes with the designer so bad that it affects other things on the dataset designer screen and it becomes unusable.
Please advise.
On another note, for the person who keeps moving this post to the sql server forum,please don't. My question is as it pertains to the C# VS 2005 IDE not SQL Syntax or the tools for developing sql syntax, or anything like it. I want to know if those who programmed the VS 2005 IDE have a way around what your IDE is doing to my SQL. The SQL is valid DB2 SQL. It has nothing to do with any SQL Forum on this site. If it did, I would have posted it there. I'm not dumb. I need help with the IDE that your company claims is ENTERPRISE worthy!!
Hi Jon,
I don't have a DB2 database readily available to test the specific behavior but...
In general you should be able to work around this by removing the added quotes in the SQL statement window on the "Enter a SQL statement" page of the TableAdapter wizard after you dismiss the Query Builder page. When you complete the wizard you may see an error message but as long as you are confident your syntax is correct you can ignore the error.
If the above doesn't work, then the other option is to enter the query without the "for fetch only with ur" part when you go through the wizard and then add it to the command text in the DataSet Designer. You can change the command text by clicking the TableAdapter part of your DataTable/TableAdapter and then expanding SelectCommand in the properties window. Add the additional statements to the CommandText subproperty.
I hope this helps.
Jay,
Thanks for your response. I have used this as a work around, but what I found was that in a query where there are parameters, the designer will erase the parameters every time I press Save or make a change. I have to go back in and add the parameters manually through the designer. It's been getting extremely annoying.
If no one else knows of a way around what the designer is doing, should this be entered as a bug? If so, how would I go about doing that?
Karen,
Thanks for that info. I've entered it in as a bug.
For anyone else running into something similar, here are the steps I'm using as a workaround.
1. Design the tables, queries, etc without the vendor specific keywords.
2. To get the keywords into the sql statement without using the designer, for the specific query, I just click inside the CommandText property. (Don't click the elipse button or you'll get the wizard.) In the one one line that you can see, I scroll all the way to the end and manually type the FOR FETCH ONLY WITH UR there. Press Enter or move off the field to save it.
3. Now, if your query was using parameters, those parameters are gone. I have to manually add the parameters back via the parameters property.
This will work, but only do it when you are finished with designing the table because any changes you make afterwards, will cause your parameters or anything special you added to a query to get lost.
