|
How come this exact CREATE TABLE statement works for connections to MSDE database but does not for Access .mdb? Code is in Visual C++.
-----------------------------------------
HRESULT hr;
::CoInitialize(NULL);
char CnnStr[200] = _T("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\\test_db.mdb; User Id=admin;Password=;");
hr = m_Conn.CreateInstance( __uuidof( ADODB::Connection ) );
m_Conn->Open(CnnStr, "", "", NULL);
m_Conn->Execute("CREATE TABLE Employees (Lastname VARCHAR(25) PRIMARY KEY, First VARCHAR(25)", NULL, 1);
m_Conn->Close();
|
|
|
|
|
Nevermind. I figured it out.
|
|
|
|
|
I have a single function that I would like to see what it is returning. I am thinking specifically of:
DATEPART(month, getdate())
Could anyone tell me how to execute that just on its own?
Cheers,
Jim
|
|
|
|
|
select can be used to return anything, including the return value of a function.
So select datepart(..... will work fine.
Christian
I have several lifelong friends that are New Yorkers but I have always gravitated toward the weirdo's. - Richard Stringer
|
|
|
|
|
Does anyone have any really good style guides for SQL? I wouldn't usually ask that but I wanted to know if there was a definitive guide.
Cheers,
Jim
|
|
|
|
|
One thing for sure, It should be easy to read and understand.
<italic>Work hard and a bit of luck is the key to success. You don`t need to be genius, to be rich.
|
|
|
|
|
I don't know if this is a "really good style guide", but you could start with this... (Copied from our intranet Wiki):
Use proper indenting. It increases readability. (So does proper naming: See SqlNamingConventions)
Comments
When using comments in a SQL script or in StoredProcedures, you should use the line comment -- instead of the block comment . This makes it easier to refactor, experiment and debug code, since you are able to use block comments to temporarily disable parts of the script.
Descriptive header comments
When a procedure or script contains a descriptive header (as it always should). You should state not only information about parameters and returnvalues, but also which objects that are available outside of its scope it may modify. If you use temporary objects that are created outside of the script or procedure, you should state that also.
Indenting
This document suggests a set of indenting rules for a readable layout of complex queries.
Only these SQL keywords should be at a start of a line, at the same indenting level in the same query (except subqueries, which can be placed at their own indenting levels as a whole following the same rules.):
SELECT
FROM
WHERE
GROUP BY
HAVING
At the next indenting level you should naturally put the following items
- Column names in the select, if there are multiple columns in the select part.
- JOINs.
- Additional conditions in a WHERE clause starting with the words AND or OR.
- Additional GROUP BY conditions.
- Additional conditions in a HAVING clause if you put them on seperate lines.
- ON conditions of a JOIN could be placed on the next line, one more indentation than the JOIN.
Examples:
SELECT
p.Name,
k.Name
FROM Persons p
LEFT JOIN Kids k
ON p.PersonPK = k.ParentPersonFK
WHERE k.Age > 18
AND k.Height < 175
<br><br>
SELECT
Gender,
COUNT(*)
FROM Persons
WHERE Gender = 'To be confirmed...'
GROUP BY Gender
HAVING COUNT(*) > 0
Example showing the indenting of a subquery:
SELECT Something
FROM Somewhere
WHERE Something IN
(
SELECT
x
FROM
AnotherTable
WHERE Comment LIKE '%free sex%'
)
<hr>
ArjanEinbu
[Modified:]Fixed: The indenting sample didn't show the indenting[/Modified]
[Modified again:]It does... In IE... (I was using tha fox...)[/Modified again]
|
|
|
|
|
I have a questino in respect of dateadd.
I was on this website:
http://www.schemamania.org/jkl/booksonline/SQLBOL70/html/da-db_5.htm
and I saw that they did a select on the data. I am curious how they could do that though as they are only going to have a specific point in time returned to them. How can they receive a span of times if there is only one point in time being returned?
Cheers,
Jim
|
|
|
|
|
Examples
This example prints a listing of a time frame for titles in the pubs database. This time frame represents the existing publication date plus 21 days.
USE pubs
GO
SELECT DATEADD(day, 21, pubdate) AS timeframe
FROM titles
GO
It's simple - they get a date, and they add 21 days to it. That's what the '21' parameter is for, and what DATEADD does.
Christian
I have several lifelong friends that are New Yorkers but I have always gravitated toward the weirdo's. - Richard Stringer
|
|
|
|
|
I think you are missing my question:
From what I could tell, this function returned a time and not a time frame. Hence, how could a timespan of objects be returned?
Cheers,
Jim
|
|
|
|
|
It's not returning a timeframe at all. What it returns represents a timeframe, 21 days after the date that's in the database. If you look at the values returned, they are all dates. The timeframe is in the fact that 21 days has been added to the time in the DB.
Christian
I have several lifelong friends that are New Yorkers but I have always gravitated toward the weirdo's. - Richard Stringer
|
|
|
|
|
Sorry, your dialog is confusing me. What do you mean returning a time frame 21 days after the date that is in the database? Is it a single point in time or is in many points in time?
I thought that the documentation said that it was a specific time, however I do not see how the function could work as it does if that were true.
Cheers,
Jim
|
|
|
|
|
Esmo2000 wrote:
I thought that the documentation said that it was a specific time
Yeah, that's right.
Esmo2000 wrote:
however I do not see how the function could work as it does if that were true.
I don't see why.
DATEADD(datepart, number, date)
Function takes a datepart, which is just an enum to say which part of the date to increment, a number to say how much you increment by, and a date as a starting point. What can it return, BUT the starting date, incremented by the amount specified ?
Christian
I have several lifelong friends that are New Yorkers but I have always gravitated toward the weirdo's. - Richard Stringer
|
|
|
|
|
I ran a query on a set of data which did not return an empty set. I then ran some additional commands after it. I want to see the result of my query as at present it does not show.
How can I do this?
Cheers,
Jim
|
|
|
|
|
Esmo2000 wrote:
I then ran some additional commands after it
What tools are we talking about here? Is this in the SQL Server Query Analyser?
Also, what sort of commands? INSERT/UPDATE/DELETE or something else?
Esmo2000 wrote:
I want to see the result of my query as at present it does not show.
I'm not entirely sure what you are looking for here. Do you want to see a before and after view of the data?
Do you want to know more?
WDevs.com - Open Source Code Hosting, Blogs, FTP, Mail and Forums
|
|
|
|
|
Colin Angus Mackay wrote:
What tools are we talking about here? Is this in the SQL Server Query Analyser?
Yes, SQL query analyzer.
Colin Angus Mackay wrote:
Also, what sort of commands? INSERT/UPDATE/DELETE or something else?
set @Query = master.dbo.xp_cmdshell bcp stuff
exec (@query)
drop stuff
Colin Angus Mackay wrote:
I'm not entirely sure what you are looking for here. Do you want to see a before and after view of the data?
Anything like that, even just knowing if anything has changed.
Cheers,
Jim
|
|
|
|
|
Finish with a select to show the end result. Otherwise, you'll just get the '1 row affected' type messages.
Christian
I have several lifelong friends that are New Yorkers but I have always gravitated toward the weirdo's. - Richard Stringer
|
|
|
|
|
First some background:
I am working on a very big project in .NET. I have a client and server applications. I use ADO.NET to manage the data. The server is the only application which access the data source, and it passes the data to the clients using disconnected DataSets. The clients do not have access to the data source. So far I worked with DataGrids very easily, using several custom column styles.
My Problem:
My system engineer wants me to show in one grid columns from several connected tables (exactly like MS-Access queries, or SQL JOIN). What I mean is that I have two tables with a DataRelation defined between them (the child table contains a foriegn key of the parent table), and I want to view columns from both tables in the grid.
The requirement from the grid is that I will be able to edit fields of existing rows (adding new rows by the user is not done in the grid itself, so this feature I do not need). What I also need is that the columns will support custom ColumnStyle.
For Example: I have two tables ORDER and CUSTOMER, and the ORDER table contains a foriegn key CUSTOMER_ID, which is related to the ID field from CUSTOMER table. I want to show both ORDER_DATE (using a DateColumnStyle) and CUSTOMER_EMAIL (using EMAILColumnStyle). Both ColumnStyles are implemented and works perfectly with DataTables and DataViews.
I searched the internet for solutions, and realized that I cannot show in one grid columns from several DataTables. I can't apply SQL JOIN statements on DataSets (only on data sources, and using DataAdapters which I don't have in my clients). I almost gave up (I was quite surprised how little material there is on the subject, because it looks to me as this is a very simple feature and a must in data-oriented applications).
The only solution I found was the JoinView class from http://support.microsoft.com/kb/325682/EN-US/. I read about the limitations published on the site and tried the object. It works! As long as you want a ReadOnly view of the data. If you want to edit columns, only the main table (the child) can be edited - columns from other tables are read only (Which I guess I can live with, though I don't want to). But trying to apply custom ColumnStyles fails. Giving MappingNames to the ColumnStyles fails, and the grid still looks the same as before (without the ColumnStyles). As I said my custom ColumnStyles work for DataView.
Does anyone have a solution to the very common problem (using JoinView or not)of viewing to joined tables in a grid? Does anyone have actual exprerience with JoinView and can tell if it is any good?
|
|
|
|
|
a dataGrid.SelectedRowIndex gives information about the selected row from the grid which corresponds to the same row in the table that the grid is bound to.
this is only true if data on the dataGrid is sorted as this of the sourceTable, ...how can i find the correct RowIndex if the user sorts the grid in a different way then select any row (in this case the SelectedRowIndex will not match the one in the sourceTable form the dataSet).
if there is a way to figure this out, or work around ... please tell me.
thanx
|
|
|
|
|
Hello,
I have a table Order. There is a field called "Symbol", another field
called "Price",and several other fields. Now I want to select the
minimum price records with distinct "Symbol".
Id Symbol Price Volume Col1 Col2 Col3...
---------------------------------------------------------------------
1 AAA 19 1000 xxxx xxxx xxxx
2 AAA 40 4000 xxxx xxxx xxxx
3 CBC 150 50000 xxxx xxxx xxxx
4 CBC 149 4000 xxxx xxxx xxxx
I want records 1 and 4 to be selected, since CBC has shortest price 149
and AAA has shortest price 19. Can anyone help me out in this regard?
Thanks in advance
|
|
|
|
|
How about
select distinct(symbol), min(price) from Order group by symbol
Database FAQ
|
|
|
|
|
Thanks for the reply but the problem is not that simple.. I want to
select the whole record not just 2 fields.. and when I select all
records the distinct stops working.. I tried sub query.. didn't
work..
|
|
|
|
|
Hey, another place where my correlation trick is useful!
SELECT Order.*
FROM Order
INNER JOIN
(
SELECT Symbol, MIN(Price) AS MinPrice
FROM Order
GROUP BY Symbol
) O2 ON Order.Symbol = O2.Symbol AND Order.Price = O2.MinPrice Caveat: this trick won't work directly if there's more than one row for a given Symbol with the minimum Price value. If there is you'll get every row with that price.
Stability. What an interesting concept. -- Chris Maunder
|
|
|
|
|
Hey.. this solves my problem.. Thanks a lot..
Best Regards,
Zishan
|
|
|
|
|