|
this should work for MSSQL
SELECT Description,
Case KQues
When 'Y' Then Null
Else Convert(Varchar(12),AddDate(month,12*(Period_ID - 7),Warning_Deadline),113)
End,
Case KQues
When 'Y' Then Null
Else Convert(Varchar(12),AddDate(month,12*(Period_ID - 7),Statutory_Deadline),113)
End,
Claim_Made,
Convert(Varchar(12),Date_Claim_Made,113)
Signed_Off_By,
KQues,
Parent_ID
FROM Client_Claims,
Standard_Claims
WHERE Client_ID = n
AND Standard_Claims.Claim_ID = Client_Claims.Claim_ID
|
|
|
|
|
I will give it a go. Cheers 
|
|
|
|
|
Hiya I am writing a program with SQL and an Access Database. I need to set the lenght of the text field (a column) to 600. Can I do this from my program??
I have tried setting it in the database in the design view but it will only allow a maximum of 255 chars. But it would be better to be able to set it from the program.
I am using CDatabase and CRecordset and its an ODBC database..
Thanks
grahamoj.
|
|
|
|
|
In access, create the field as a memo with no length e.g.
create table blah<br />
(<br />
field1 memo<br />
)
Dave.
|
|
|
|
|
Does anyone know of a product that will create XML bassed documentation for a SQL database like you can get with C# or by using NDoc?
Paul Watson wrote:
"At the end of the day it is what you produce that counts, not how many doctorates you have on the wall."
George Carlin wrote:
"Don't sweat the petty things, and don't pet the sweaty things."
|
|
|
|
|
I have problem with MS SQL Server during installation
firstly there was no problem with Standard Edition, then I uninstalled that and tried to install Enterprise Edition
and faced the following problem:
at the end of the installation the following message appears "... refer to sqlstd.log file"
now I have the same problem with Standard Edition
(I tried to install SQL Server on another computer, the same problem)
thanx.
|
|
|
|
|
ok .. can you tell us what is written in the log file..
it may explain the problem..
also what OS do you use ?
|
|
|
|
|
Hi, All:
I am writing my FIRST Windows Form Application in .NET to edit my MS Access database. In this Access DB, I have a table with two columns: EquipmentID and EquipmentDescription. Some other tables use the ID to refer the equipment. This application supports multiple users at the same time. That mean that it could be more than one user add new equipmment. I use in-memory DataSet to get user's input and then call Insert/Delete/Update methods. I am debating that whether I should use Access AutoNumber for the ID field, or I should just use DataReader to get the Max ID used, then Add 1 to create my new ID. The AutoNumber makes things simpler, but if I try to merge to DB later, I will have a lot of headaches. If I assign my own ID, I might have conflict with other users and fail the INSERT.
So what is the typical way to achieve this, I know a lot of people have done this, please give me some ideas. Thanks in advance.
Dion
|
|
|
|
|
If you use a GUID (Globally Unique Identifier) as the ID, you should have something that can be ported to any platform as well as solve your Identity problems. I have previously used a VB6 function that produced GUID's, and am sure you should find one for .Net.
Regards
Peet Schultz
Centurion SA
YASP
|
|
|
|
|
There're was an article on MSDN covering this issue, but I cannot find it.
What it suggests is to configure your EquipmentID to use negative autoincrement values:
<br />
ds.Tables["Equipment"].Columns["EquipmentID"].AutoIncrementSeed = -1;<br />
ds.Tables["Equipment"].Columns["EquipmentID"].AutoIncrementStep = -1;<br />
...and to modify your insert SP (or insert code if you use Access) to return the newly created identity value. SP would look like this:
<br />
CREATE PROCEDURE dbo.daInsertEquipment<br />
(<br />
@EquipmentID int OUT,<br />
@EquipmentDescription nvarchar(200)<br />
)<br />
AS<br />
INSERT INTO Equipment<br />
(EquipmentID, EquipmentDescription)<br />
VALUES (@EquipmentID, @EquipmentDescription) <br />
<br />
SET @id = SCOPE_IDENTITY()<br />
HTH
Alexandre Kojevnikov
MCP (SQL2K)
Leuven, Belgium
|
|
|
|
|
How to backup StoreProcedure, View, database... in Oracle.
Help me please!!!
Thank you.
***Hung***
|
|
|
|
|
Oracle has a myriad of ways to backup the database. I suggest you check out the Backup & REcovery Concepts guide for details on all of them.
Unfortunately there is no wasy way for me to tell you how to do this without knowing your environment in detail. Basically your options are: Export, Traditional cold backup, and backup using recovery manager (or EBU if you're on 7.X). The export is the simplest but not real efficient and can be costly in resources. Traditional works fine, but requires downtime and is also costly in resources. Rman is relatively efficient, but fairly complex to use and setup.
If yo need a quick backup, most dbas use export when in a pinch if there aren't any other options.
Mark Conger
Sonork:100.28396
|
|
|
|
|
I have problems when i use the #import directive to import the msado and msadox DLLs in the same application.
#import <C:\Archivos de programa\Archivos comunes\System\ado\msado15.dll> no_namespace rename ("EOF", "adoEOF")
#import <C:\Archivos de programa\Archivos comunes\System\ado\msadox.dll> no_namespace
Has anyone found this problem? and the solution?
Thanks in advance.
|
|
|
|
|
Don't use < and > between the path, use:
#import "msado15.dll" no_namespace rename("EOF", "adoEOF")
Rickard Andersson@Suza Computing
C# and C++ programmer from SWEDEN!
UIN: 50302279
E-Mail: nikado@pc.nu
Speciality: I love C#, ASP.NET and C++!
|
|
|
|
|
Hi all
I have a typed DataSet called MyDataSet and I populate it by calling Fill on the adapter eg
MyDataSet mds = new MyDataSet();
myAdapter.Fill(mds);
now I use this DataSet to populate a listbox eg
myListbox.DataSource = mds;
myListBox.DataTextField = "Text";
myListBox.DataValueField = "ID";
myListBox.DataBind();
but I don't really like this - what if my DataSet changes (as would happen early on in a project)
I may lose the ID or Text columns and the only time I would notice would be at runtime.
Is there any Information in the typed dataset that contains my column names? so I could do something like
myListbox.DataSource = mds;
myListBox.DataTextField = mds.Tables[0].colText.ColumnName;
myListBox.DataValueField = mds.Tables[0].colID.ColumnName;
myListBox.DataBind();
so that if say Text is remved from my DataSet schema then on next compilation colText would no longer exists and the compilation would break and then it can be fixed.
I see that the columns are in there but made internal - arrgghhhhh
Please help
Shaun Wilde
|
|
|
|
|
Shaun Wilde wrote:
Is there any Information in the typed dataset that contains my column names?
mydataset.Tables["mytablename"].Columns[index].ColumnName;
Mazy
"And the carpet needs a haircut, and the spotlight looks like a prison break
And the telephone's out of cigarettes, and the balcony is on the make
And the piano has been drinking, the piano has been drinking...not me...not me-Tom Waits
|
|
|
|
|
true - I did look at this before I posted -
but that still requires me to understand what info is in what column - if my column disapeared or moved (changed index) then it would still compile but it would go wrong at runtime - what I was hoping for was a more strongly typed solution (if one exists) that would be caught at compile time rather than runtime.
|
|
|
|
|
How about
myListBox.DataTextField = mds.TheTable.TextColumn.ColumnName;
myListBox.DataValueField = mds.TheTable.IDColumn.ColumnName;
Replacing TheTable with whatever your table is named.
It seems to work in the quick test app I threw together.
DataSet1 o = new DataSet1();
MessageBox.Show( o.Staging.DescriptionColumn.ColumnName );
Displays a message box with 'Description' in it.
Bruce Duncan, CP#9088, CPUA 0xA1EE, Sonork 100.10030
Blackadder: Baldrick, have you no idea what irony is?
Baldrick: Yeah, it's like goldy and bronzy only it's made of iron.
|
|
|
|
|
true - but as I said before the custom tool makes them internal which is great if you are using them
in the same project but no good if you wish to use them from a library eg as an object in the toolbox - I can't see any reason for them to be made internal other than to annoy people.
you can change the access specifiers but when the wizard runs again it will overwrite them 
thanks
Technically speaking the dictionary would define Visual Basic users as programmers.
But here again, a very generalized, liberal definition is being employed and it's wrong
- just plain wrong - Tom Archer 5/12/02
|
|
|
|
|
Ah, I see the problem now. That is annoying
Bruce Duncan, CP#9088, CPUA 0xA1EE, Sonork 100.10030
Blackadder: Baldrick, have you no idea what irony is?
Baldrick: Yeah, it's like goldy and bronzy only it's made of iron.
|
|
|
|
|
How can I pass,number of column instead of its name in my SLELECT statement.Like this:
Select * from mytable WHERE COLUMNNUMBER5='something'
Mazy
"And the carpet needs a haircut, and the spotlight looks like a prison break
And the telephone's out of cigarettes, and the balcony is on the make
And the piano has been drinking, the piano has been drinking...not me...not me-Tom Waits
|
|
|
|
|
Hi Mazdak
Mazdak wrote:
How can I pass,number of column instead of its name in my SLELECT statement.Like this:
Select * from mytable WHERE COLUMNNUMBER5='something'
I'm not aware of any database engines that allow you to directly do what you have asked for. However you should be able to persuade .Net to help you out with something like the following:
OleDbCommand comm = new OleDbCommand("mytable", conn) ;<br />
comm.CommandType = CommandType.TableDirect ;<br />
OleDbDataReader reader = comm.ExecuteReader(CommandBehaviour.SchemaOnly) ;<br />
DataTable dt = reader.GetSchemaTable() ;<br />
reader.Close() ;
The dt datatable should now contain details of each column in the table. Use the following to show the details on a grid:
this.dataGrid1.SetDataBinding(dt) ;
You should now be able to fabricate a new SQL Select statement by reading the appropriate column details (the ColumnName and DataType) from the datatable.
Hope this helps.
Andy
|
|
|
|
|
Thnkas Andy. Thats what I want. 
Mazy
"And the carpet needs a haircut, and the spotlight looks like a prison break
And the telephone's out of cigarettes, and the balcony is on the make
And the piano has been drinking, the piano has been drinking...not me...not me-Tom Waits
|
|
|
|
|
I'm working on a video rental system. I have a table of data that looks something like this:
DateRented RentalType
----------------------- -----------
11/11/2002 11:21:47 AM VHS
11/11/2002 11:44:28 AM VHS
11/13/2002 11:37:05 AM VHS
11/13/2002 10:54:14 PM DVD
11/14/2002 2:57:58 AM DVD
11/14/2002 2:58:31 AM DVD
11/14/2002 3:39:19 PM DVD
11/15/2002 10:38:32 AM VHS
11/15/2002 10:49:06 AM DVD
What I need is a query that will return three values--the date in question (day only), a count of all items that were rented, and a count of all DVDs that were rented. I have tried using the GROUP BY clause, but I can't seem to get it right. See, I don't care about the time in the date field. I just want a count for all items and DVD items for a particular day. So my output should look like this:
DateRented TotalRentals DVDRentals
-------------- --------------- ----------
11/11/2002 2 0
11/13/2002 2 1
11/14/2002 3 3
11/15/2002 2 1
Thanks in advance.
-Matt
------------------------------------------
The 3 great virtues of a programmer:
Laziness, Impatience, and Hubris.
--Larry Wall
|
|
|
|
|
Try something like:
SELECT
DateRented,
COUNT(1) As TotalRentals,
SUM(DVD) As DVDRentals
FROM
(
SELECT
CAST(
CAST(Year(DateRented) As varchar(4))
+ '/' + CAST(Month(DateRented) As varchar(2))
+ '/' + CAST(Day(DateRented) As varchar(2))
As datetime) As DateRented,
CASE
WHEN RentalType = 'DVD' THEN 1
ELSE 0
END As DVD
FROM
Rentals
) As R
GROUP BY
DateRented
ORDER BY
DateRented
"These people looked deep within my soul and assigned me a number based on the order in which I joined." - Homer
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
