|
Hi,
I am using ADO to query my Access database and need to run one sql query that conditionally issues one of two complex select statements depending upon the value of a particular field in a table. The two select statements query different tables. I'm trying to use something like:
SELECT IIF((SELECT PropertyValue FROM PropertyTable WHERE PropertyCode=10)=0,
(SELECT ...), (SELECT ...))
But this only lets me return one column, whereas my select statements need to return many columns. The CASE statement seems to have the same limitation.
Any advice would really help!
Joanne
|
|
|
|
|
Sub-selects can only ever return a single value if used in the way that you have tried. If you absolutely need to meet your requirements with a single SQL statement then you may try something like:
SELECT ...
WHERE (SELECT PropertyValue FROM PropertyTable
WHERE PropertyCode=10)=0)
UNION ALL
SELECT ...
WHERE (SELECT PropertyValue FROM PropertyTable
WHERE PropertyCode=10)<>0)
ORDER BY ...The first select returns a resultset if property 10 does not exist, otherwise the second select returns its resultset. Note that both resultsets must return the same number of columns, with the same datatypes.
Hope this helps.
Andy
|
|
|
|
|
Thanks Andy, this does exactly what I was trying to do.
Works like a charm.
Joanne
|
|
|
|
|
I get the following error when I compile a project.
C1083: Cannot open type library file: 'C:\\Program Files\\Common Fi les\\SYSTEM\\ADO\\msado25.tlb': No such file or directory<br />
Error executing cl.exe.
I got MSADO20.tlb and MSADO21.tlb in my machine, but not MSADO25.tlb
Is this right location[^] to download MSADO2.5?
As per MSDN, 2.8[^] is the latest version.
What's the diff between MSADO 2.0, 2.1, 2.5 and 2.8?
TIA
"Whidbey"..."Orcas"...Roadmap
This signature was created by "Code Project Quoter".
|
|
|
|
|
If you're running Windows 2000 and haven't obtained MDAC 2.6, the type libraries for version 2.5 are kept in msado15.dll. The latest versions are always found in msado15.dll; the .tlb files are shipped with each new version of MDAC in order to target the previous versions.
For example, I have MDAC 2.7 SP1 installed on this Win2k machine, so I have msado20.tlb, msado21.tlb, msado25.tlb and msado26.tlb.
If you're using SQL Server 2000, you should install at least MDAC 2.6; since SQL Server 2000 should be at minimum SP2, you should have MDAC 2.6 SP1.
You can check your version of MDAC using MDAC Component Checker[^].
|
|
|
|
|
Mike Dimmick wrote:
the type libraries for version 2.5 are kept in msado15.dll.
 I thought version 2.5 will be kept in msado25.dll
The error says the following file not found
'C:\\Program Files\\Common Files\\SYSTEM\\ADO\\msado25.tlb'
Mike Dimmick wrote:
since SQL Server 2000 should be at minimum SP2, you should have MDAC 2.6 SP1.
I don't have the SQL Server 2000 right now but I will be installing it soon. Will this installation takes care of MDAC 2.6?
Mike Dimmick wrote:
MDAC using MDAC Component Checker[^].
Thanks for the link. From this tool, I got MDAC 2.1 SP2 and MDAC 2.5 SP3..
"Whidbey"..."Orcas"...Roadmap
This signature was created by "Code Project Quoter".
|
|
|
|
|
What's the difference between:
FULL LEFT/RIGHT JOIN and just LEFT/RIGHT JOIN?
What does the word "FULL" tells the query analyzer? THanks.
norm
|
|
|
|
|
I don't think there is such a thing as a FULL LEFT or FULL RIGHT join. There is, however, such a thing as a FULL OUTER JOIN-- is that what you're talking about? There are also LEFT OUTER JOIN and RIGHT OUTER JOIN. I can see how you could've become confused. Here's the skinny:
A LEFT OUTER JOIN is the same as a LEFT JOIN.
A RIGHT OUTER JOIN is the same as a RIGHT JOIN.
A FULL OUTER JOIN is the same as a FULL JOIN.
A LEFT JOIN is all the rows from the left part of a join, with NULL values being permitted on the right if there's no match.
A RIGHT JOIN is all the rows from the right part of a join, with NULL values being permitted on the left if there's no match.
A FULL JOIN is the union of a LEFT JOIN and a RIGHT JOIN; you're guaranteed that all possible rows from both the left and right will be present, with NULL values filling in the blanks on the other side as necessary.
Does this help?
Regards,
Jeff Varszegi
|
|
|
|
|
|
I have created a deployment application that has the user provide db credentials. It then creates the database and then runs a .sql script to create all of the db objects. The script runs fine in Query Analyzer, but won't work in my deployment project. I get the following error message:
'CREATE VIEW' must be the first statement in a query batch.
'CREATE VIEW' must be the first statement in a query batch.
'CREATE VIEW' must be the first statement in a query batch.
'CREATE VIEW' must be the first statement in a query batch.
'CREATE VIEW' must be the first statement in a query batch.
What does this mean and why do I only get it in my deployment app and not in query analyzer?
Thanks.
-Matt
------------------------------------------
The 3 great virtues of a programmer:
Laziness, Impatience, and Hubris.
--Larry Wall
|
|
|
|
|
That's a great signature quote.
Try putting GO on its own line after each CREATE VIEW. Did this solve your problem? How is this script being run, anyway?
Thank you.
Jeff Varszegi
|
|
|
|
|
From what I understand, "GO" isn't part of the T-SQL language. Frankly, if that's the case, I'm not sure what in the world it's there for except as a delimiter (maybe?). I've tried to run the script with GO(s) in and I get a syntax error at the first one it comes to. I then just ignore all of the GO(s) when I'm reading the script and I get the error I mentioned in my first message.
Thanks for your help.
-Matt
------------------------------------------
The 3 great virtues of a programmer:
Laziness, Impatience, and Hubris.
--Larry Wall
|
|
|
|
|
Well, part of the problem is that I don't know what you mean by "running a script". I mean, are you using osql or something? If not, you will probably have to separate each one of the CREATE statements, etc. into a separate call; each one has to be in a separate "batch". In some contexts, you separate batches with GO; in others, you just execute them separately. Are you just executing the .sql file using a shell command or something? Try separating each CREATE statement into a separate file-- what happens?
Thank you.
Jeff Varszegi
|
|
|
|
|
I see what you are saying. What I am doing is opening a .sql file that I created using the "Generate SQL Script" command from Enterprise Manager. I script all of it to a single file and then read the entire contents into a string in my application. I am then setting the command text of my sql command to that string and running ExecuteNonQuery. How would I separate the create view calls out? Is there any way other than actually parsing the script or puting each create view into its own file?
Thanks.
-Matt
------------------------------------------
The 3 great virtues of a programmer:
Laziness, Impatience, and Hubris.
--Larry Wall
|
|
|
|
|
Well, you could always copy the statements into your source code. I can't think of any other way. It's an interesting situation.
Regards,
Jeff Varszegi
|
|
|
|
|
Your suggestion has helped me to figure it out. Instead of stripping out the GO statements, I am using them as a delimiter for each statement. This appears to be working, however, I am finding that some of the stored procedures I created had some improper syntax that (for some stupid only-microsoft-knows-why reason) Enterprise Manager had allowed. Now that I'm running it using C#, however, it is holding me to a more perfect syntax. Now that I've fixed those things in my script, everything seems to be working fine.
I really appreciate your help. It was exactly what I needed. I'm doing this for an installer project. I'm just amazed that nobody else (at least that I can find with google) has ever done this before. It's crazy!! 
Thanks a heap.
-Matt
------------------------------------------
The 3 great virtues of a programmer:
Laziness, Impatience, and Hubris.
--Larry Wall
|
|
|
|
|
Hello,
I am currently building a script to populate a database with an initial set of data. As the database will potentially be brand new (but may not be) when the scripts are run I want to find a way to set the value the next insert will put in an identity column after the initial set of records are in. If the database is brand new I don't want new records overwriting or causing problems with the initial set. If the database was previously populated the values are deleted and the initial set is re-inserted, but in this case I don't want the new records to be placed with IDs too far removed from the IDs used in the initial set.
I am fairly certain it can be done, but after trawling through the docs I can't seem to find what I am looking for - The initial value can be set when the table is created, but I'm doing that after the table is created.
Any ideas?
--Colin Mackay--
"In the confrontation between the stream and the rock, the stream always wins - not through strength but perseverance." (H. Jackson Brown)
|
|
|
|
|
Hi everyone,
I thought you might be interested in WiredNav, which has just been released by Mabry Software. WiredNav is an innovative self-wiring .NET control that I authored for filling, navigating, searching, updating, and reporting ADO.NET data tables. You can use the control in database applications without making any initial property settings or writing a single line of code.
There are no DataAdapter, DataSet, DataSource, or DataTable properties for the control. At runtime, the navigator automatically wires itself to every data object found on the Windows form to which it is added. With this technique, you only need one navigator per form instead of one per data table. WiredNav can be used with SQL, Oracle, OLEDB, and ODBC data adapters.
Along with the Windows Forms DataGrid control, WiredNav can connect to your favorite third-party grids:
- ComponentOne Flex Grid for .NET
- ComponentOne True DBGrid for .NET
- Infragistics UltraWinGrid
- Janus GridEX Control for .NET
- Xceed Grid for .NET
I designed WiredNav to address many of the data binding and other disconnected data problems that I’ve read about on this message board. In addition to self-wiring, WiredNav has the following features:
- Automatic data control switching for navigating and updating
- Data table filling based on selection criteria
- Regular, transaction, and automatic source table updating
- Update results lists with total rows modified, added, and deleted
- Update error lists with error resolution when updates fail
- Row search based on primary key values or other search criteria
- Report printing and viewing with table filtering
- Selection criteria saving when filling, searching, and reporting data tables
- Time-saving date codes for common date values used in selection criteria
- Text validation for TextBox and ComboBox input controls with error providers
- Customizable navigator bar with section hiding
- Auto-incrementing key value retrieval for SQL Server, Oracle, and Access 2000
- Globally unique identifier (GUID) value generation for UniqueIdentifier columns
- Navigator buttons context menu with customizable shortcut keys
If you build database applications using ADO.NET, you should check out WiredNav. I think this is how Microsoft envisioned disconnected data would be handled. A trial copy of WiredNav can be downloaded from Mabry’s web site at www.mabry.com.
LT
|
|
|
|
|
Iam totally new to SQL Data bases.
Is it that..SQL Databases have more than 1 login ID and Password..
I've been assigned an assigned in which I have to write aprogram which checks the access permission for a particular login id and password!
what all API's are used for this job??
kindly help me in this regard!
ranjani
|
|
|
|
|
I forgot to mention the language in which Iam coding!
I will have write a C++ code..
thanxxx
ranjani
|
|
|
|
|
Hi All,
I am storing details of many hotels in a database. Details are coming from different providers. And we store it into our database.
Now sometimes, details about the same hotel come from different providers. And each provider may use different ID or name to refer a hotel. So when we enter this details we end up entering dublicate rows.
For example. Provider A give the details of InterContinental hotel and they refer the hotel with the ID "Inter". Provider B also give the detail of the same hotel but they refer that hotel with the ID "InterCon".
So after saving the details of InterContinental provided by the provider A when I store the detail given by provider B I need to make out that details of InterContinental is already stored and so I should not store it second time.
But as the IDs are different how do I make it sure. More over I dont have any other ID etc.
Can any one give me answere quickly.
Regards,
Vaibhav
|
|
|
|
|
Hi Vaibhav,
Question 1: What would be the matching details of a particular hotel, which would help us to determine, whether the two records submitted by the two providers are of the same hotel?
Question 2: How can you be sure, that the hotel "Inter" sent by Provider A is the same as the hotel "InterCon" submitted by Provider B. If the address of "Intercon" is the same as that of "Inter", except for an extra comma, how can you determine that both the records are of the same hotel, programmatically?
Question 3: What is/are the field/s that is/are common and unchangeable for both the records?
As fas as your question goes.
If we assume that only the id changes, and the rest of the details remain the same, then you can design the database this way..
tblHotel<br />
----------<br />
HOTEL_ID (pk) (IDENTITY)<br />
HOTEL_NAME<br />
HOTEL_ADDRESS<br />
and so on for the unchanging fields.<br />
<br />
-----------
another table would be there,
tblProviderHotel<br />
-------------<br />
PROVIDER_HOTEL_ID (pk) identity (just a key field)<br />
HOTEL_ID (fk from tblHotel)<br />
PROVIDER_HOTEL_ID (this would be the id, given by the providers, "inter", "intercon" etc)<br />
PROVIDER_ID (fk from Provider table if you want)<br />
-------------
Now in this scenario (inter and intercon scenario), tblHotel would contain just one record for the hotel details, whereas the tblProviderHotel would contain two records with the same HOTEL_ID (from tblHotel) but different PROVIDER_HOTEL_IDs (inter and intercon)
Similarly, if a provider C sends you a record of the same hotel, but the id is differnt, say "Inter-Conti", then you can check if the rest of the fields such as HOTEL_ADDRESS, CITY, ZIP etc matches with any record in tblHotel and if it does as in thsi case, then take the HOTEL_ID for the matching record from the tblHotel and just make a new entry in the tblHotelProvider table, with the id that the provider gave you..
So now, you have 1 record in tblHotel, but 3 related records in tblHotelProvider all pointing to one hotel, but submitted by different providers..
Also this is implemented, assuming all the other details are matching for all the 3 hotel records provided by the providers..as I asked you earlier.
Please do let me know if this helps..
Cheers,
Rahul
I do not understand what I do. For what I want to do I do not do, but what I hate I do. - Romans 7:15
|
|
|
|
|
Hi Rahul,
First of all, thanx for giving it a try.
What would be the matching details of a particular hotel, which would help us to determine, whether the two records submitted by the two providers are of the same hotel?
Ans 1: There is nothing to be match by which we can be sure that the two hotels provided by different providers are same, otherewise problem should not arise
Ans 2: This is again second way of asking first question. You cant be sure that the "Inter" by provider A is same as the hotel "InterCon" by provider B. If you can assure that then porblem should not arise
Ans 3: Address etc are given by both providers but there is no garuntee that from both the providers you will get the same address string for the same hotel. As one can write "Street" while other can use "St." instead of.
Vaibhav.
|
|
|
|
|
Hi,
I found a lot of this -- "System.Data.Sqlclient.TdsParser" running when I run my multi-thread program. When I cancelled the command and closed the connection in my cancel thread, even I aborted my threads. This one is still running. Because the command and connection are gone, it even gave me some exceptions.
What can I do? How can I control this? How can I quit my thread totally, clearly and safely?
I would greatly appreciate your help.
|
|
|
|
|
Hi,
I set the Timeout time in a ConnectionString, like this:
"Password=;Username=sa;......Connect Timeout=60;pooling='false';"
Then I connect to sqlserver over and over again using this. But at about No.250 thread, the conn.Open() keeps waiting even after 60 seconds, 60 minutes.
Why this happen? Could anyone please help me?
Thanks in advance.
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
