|
hi,
i have to display all the sold product names of a particular month along with the date on which they are sold .
This month and year will be selected by user using dropdown list .
How should i write query to compare selected month and year with the "Date" field of datetime type in DB stored in format mm/dd/yyyy hh:mm:ss.
Please Guide.
yog
hui gfgh kgdgrt njjn hjgkn
|
|
|
|
|
Use Year and Month functions in your query
SELECT product.name<br />
from product inner join sales on sales.productid = product.productit<br />
where Year(SalesDate) = @Year <br />
and Month(SalesDate) = @Month
this saves you having to work out how many days in a year.
Blog Have I http:\\www.frankkerrigan.com
|
|
|
|
|
There is a two way:--
1. You can change date format before storing it into database.
2. You can get date with time from database and change it format according to the requirement. And compare it.
|
|
|
|
|
Hello.
I am working on a project which involves reading from and writing to an excel file. I'm having a few bugs at the moment. I can only get it to write if there is already data saved in the excel file. Second, after it does save the data, my attempt to re-read it only results in OLE stating that there is only one column of data, even though there is obviously 3. It works if i resave it in Excel, but that's not really a viable option as this program is supposed to automate things. If anyone has experience with OLE and has a solution or at least a suggestion for some of these problems, please post a response.
Thanks in advance.
-- Steven Hunt
|
|
|
|
|
Yes I've done this and you want to look on codeproduct for a free class that allows writing/reading of excel files.
Blog Have I http:\\www.frankkerrigan.com
|
|
|
|
|
You can use ADO.net (or just plain ADO) and treat the excel sheet like a datbase table. Use the Jet Engine with an excel specific connection string and reference the appropriate sheet as $SheetName (sheet name prefixed with $). I have used Carlos Antonini's C++ ADO wrapper classes successfully with Excel in this way. ADO.Net with DataTables/DataReaders and SQL commands works as well.
|
|
|
|
|
Hey guys
Using Sql/express
Table 1 :
I set 3 fields to be primary keys for Table1 (i have just marked each field with a key (primary key)(is there anything else to do)?
Table 2 :
I should add a foreign key for this table linked to the 3 primary keys of table1
Can this be done /how? Any hint for that?
thx
Regards
Ramy
|
|
|
|
|
charchabil03 wrote: Can this be done /how?
Yes, you are permitted upto 16 columns to form part of a key (although if you are using 16 columns, you might want to review your design).
Your foreign key must match the primary key. So if you have int, char(5), datetime as your primary key, the foreign key must also be int, char(5), datetime.
|
|
|
|
|
Read books online for SQL server (I'm making an assumption your using it).
Create PK single value
ALTER TABLE dbo.Table ADD CONSTRAINT [PK_ID] PRIMARY KEY CLUSTERED
Create FK
ALTER TABLE [dbo].[MySecondTable] WITH NOCHECK ADD CONSTRAINT [FK_Table_ID] FOREIGN KEY([TableID])
REFERENCES [dbo].[Table] ([ID])
Blog Have I http:\\www.frankkerrigan.com
|
|
|
|
|
Is there a way for me to check if a database exist programmatically using C# before I create a new database? I would also like to check if a table already exists in the database or if a column already exists in the table, thanks.
|
|
|
|
|
Connect to the master db then
<br />
<br />
IF EXISTS (SELECT name FROM master.dbo.sysdatabases WHERE name = N'MyTable')<br />
Select 'We have a table
Blog Have I http:\\www.frankkerrigan.com
|
|
|
|
|
Frank, the sample code you provided checks to see if a database table exist, correct? Is there something similar to check if a database exist? Is there some method in the Sql.Client namespace that I can pass in an sql command string to check if a database or table exist and it will return me a true or false? Thanks.
|
|
|
|
|
Hey,
We're trying to generate a chart using SQL reporting services 2000 which will have a bar chart and a line chart (from a different dataset) on top - this line chart will need a second 'y' axis to display the appropriate scale (something like a pareto chart).
RS 2000 does not seem to be capable of doing this (seemingly simple) chart...
I've been googling, but i can't seem to find very much information (apart from other people having the same problem). Has anyone encountered this before?
Cheers.
|
|
|
|
|
I am newly in visual studio 2005.
Why we use MissingSchemaAction ?
And what is its meaning?
|
|
|
|
|
Hi
for connect to access (*.mdb) I use Add-Ins Menu
Now, how can I connect VB6 to SQl Server 2000
Hello Friends
|
|
|
|
|
This[^] should be a lot helpful.
|
|
|
|
|
I have a scenario to Update 8 tables at a time
All the 8 tables are having a same column, that i have to set the value based on the field
I have the receivable tables havind a column InvoiceId, if i pass one invoiceid to sp or query, based on the InvoiceId i have to set the value null
for that i know only to write the 8 UPDATE query statements, if u have any other idea means Please Guide Me
Thanks
-
|
|
|
|
|
Aloha,
We need to make a small Win app. that work together with an Access database. Everything works, but it's awfully slow 
It takes around 60 milliseconds for our code to perform a retrieve a scalar value from the database. This is terrible, since we have quite a lot of queries to the database.
Our code is quite simple:
DbConnection dbConnection = dbProviderFactory.CreateConnection();<br />
dbConnection.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=database.mdb";<br />
<br />
dbConnection.Open();<br />
DbCommand dbCommand = dbProviderFactory.CreateCommand();<br />
dbCommand.Connection = dbConnection;<br />
<br />
dbCommand.CommandType = CommandType.Text;<br />
dbCommand.CommandText = sql;<br />
<br />
object obj = dbCommand.ExecuteScalar();<br />
<br />
dbCommand.Connection.Close();<br />
<br />
return obj;
Is it supposed to be so slow or are we doing something terribly wrong? Our SQL is just a simple select from two tables.
SELECT [text] FROM Question, UserText WHERE Question.QuestionTitle = UserText.TextId " +<br />
"AND QuestionId = " + controlId.ToString() + " AND LanguageId = " + languageId;
Thank you,
|
|
|
|
|
MS Access performance sucks 
If performance is a concern then you may want to consider SQL Express ...
From a quick glance, your query looks okay.
|
|
|
|
|
We all now that MS Access performance sucks... it's just a lot worse than I thought it would be
|
|
|
|
|
phimix wrote: We all now that MS Access performance sucks...
Yep.
phimix wrote: it's just a lot worse than I thought it would be
I think it is a Your mileage may vary type of thing. I know a guy who is using an Access database that has 50-60 tables with 160,000+ records in each table, with 6-10 users and not having any trouble with it.
I'd look at the queries and general structure of the database and look for any small bottlenecks. Your performance issue could be something very small and subtle that is compounding into a performance issue 
|
|
|
|
|
phimix wrote: Is it supposed to be so slow or are we doing something terribly wrong? Our SQL is just a simple select from two tables.
I have had pretty good luck with Access in small environments. How long does this query take to run from the Access database as a query?
I'd love to help, but unfortunatley I have prior commitments monitoring the length of my grass. :Andrew Bleakley:
|
|
|
|
|
what is primary key, candidate key, composite key and unique key? How many primary keys can be there in a table? composite key can act as a foreign key?
Mohinder Singh
|
|
|
|
|
Many of the database makers have tutorials available on their web sites.
An example database tutorial visit ...
http://www.geekgirls.com/menu_databases.htm[^]
Also, make a search here at Code Project, there are many database tutorials at various levels
modified 1-Aug-19 21:02pm.
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
