|
Hi,
I am new for database query. I am using Ms Access to store data. In Ms Access the date is in format of MM-DD-YYYY. I want to retrive that date in DD-MM-YYYY format. Can any body tell me how to convert this date format.
Thanks in advance.
Mike
|
|
|
|
|
Format$(mydate,"dd-mm-yyyy")
----------
Some problems are so complex that you have to be highly intelligent and well informed just to be undecided about them.
- Laurence J. Peters
|
|
|
|
|
Hi,
I'm wondering if it's possible to join ALTER TABLE statements so I don't have to have a bunch of ExecuteNonQuery commands.
ALTER TABLE Products ALTER COLUMN ItemNumber TEXT(15)
ALTER TABLE ItemsSold ALTER COLUMN ItemNumber TEXT(15)
thanks,
Ron
|
|
|
|
|
As far as I know this is not possible.
Regards,
Rainer.
Rainer Stropek
Visit my blog at http://www.cubido.at/rainers
|
|
|
|
|
|
You could create this stored procedure, then just execute it with a delimited list of table names:
CREATE PROCEDURE AlterMyTables
@cTablesToAlter varchar(500)
AS
declare @cSQL varchar(100)
declare @nLoc int
declare @nLoc2 int
select @nLoc = 1
while (@nLoc<len(@cTablesToAlter))
begin
select @nLoc2 = CHARINDEX(',',@cTablesToAlter,@nLoc + 1 )
if (@nLoc2=0)
select @nLoc2 = len(@cTablesToAlter) +1
select @cSQL = 'ALTER TABLE ' + SUBSTRING(@cTablesToAlter,@nLoc,@nLoc2-@nLoc) + ' ALTER COLUMN ItemNumber TEXT'
exec(@cSQL)
select @nLoc = @nLoc2 +1
end
----------
Some problems are so complex that you have to be highly intelligent and well informed just to be undecided about them.
- Laurence J. Peters
|
|
|
|
|
Thanks Eric, I'll give it a try.
Ron
|
|
|
|
|
I'm working on my first data base project. We're recording some historical information from a product, and it seems natural to go ahead an put it in a data base for later retrieval.
Here are some of the things I'm considering:
ODBC: I want independence from the actual DBMS. I'll provide a 'default' data base with the product, but want to give the customer the option of having us record our information directly in his data base.
SQL Server 2005 Express: I'm looking at this for my 'default' data base. It's free, and the install appears to be easy.
MFC: Yeah, I know MFC is horribly old school, but this is an add-on to an existing MFC-based product. .NET is out of the question. We're developing using VS.NET 2003, by the way.
Service: I don't know if it makes a difference, but this will probably be implemented as a service.
Do you guys have any sources or suggestions for 'best practices', tips, or "do's and don'ts"?
Software Zen: delete this;
|
|
|
|
|
Gary Wheeler wrote: ODBC: I want independence from the actual DBMS
I'd ignore ODBC, and go for implementing a provider model. You build a DAL (Data Access Layer) that you can swap out and replace with a new one if you change your database. You are likely to have to change a lot of code anyway even if you do go the ODBC route. Different databases use different dialects of SQL so any advantages of using a generic connection like ODBC or OleDB are usually just a matter of perception rather than of reality.
The way I usually go about a DAL is to have it handle the database connections internally (an abstract base class), getting its settings from a configuration file. The public methods on the DAL classes are just proxies to the stored procedures and there is very little processing going on other than marshalling datatypes.
The abstract base class has some generic functionality that calls the stored procedures and returns the data. The public methods will take parameters and pass them to the stored procedure. The results are then returned from the method (as generic types - not database specific types).
This way, if you change your database all you need to do is change the DAL classes.
If you write your DAL against SQL Server 2005 Express Edition then it will work without change if you migrate to a fuller SQL Server edition.
Scottish Developers events:
* .NET debugging, tracing and instrumentation by Duncan Edwards Jones and Code Coverage in .NET by Craig Murphy
* Developer Day Scotland: are you interested in speaking or attending?
My: Website | Blog
|
|
|
|
|
Thanks, Colin ![Rose | [Rose]](https://codeproject.global.ssl.fastly.net/script/Forums/Images/rose.gif) .
I was hoping that, since I'm only inserting data into the data base, that ODBC was sufficiently portable. If I don't issue SQL I construct myself, would that be sufficient?
Colin Angus Mackay wrote: If you write your DAL against SQL Server 2005 Express Edition then it will work without change if you migrate to a fuller SQL Server edition.
That was part of the appeal to SQL Server 2005 Express.
Software Zen: delete this;
|
|
|
|
|
Hello Everyone
Iam working(porting) on a legacy application which uses MFC DAO based classes to access MS-Access. I need to execute stored procedure on the SQL Server database how can I do that using CDaoQueryDef?
Thanks in advance for your time and help.
regards,
KS
|
|
|
|
|
I have a app with a grid and am working on updating data from the grid to the data base. the update is triggered off a menu item. the code is as follows:
try
{
// New data entered to Contract QA_Setup table
DataAccess da = new DataAccess();
string sproc = "prc_QA_Setup_upd";
string dbAction = "Update";
string tableName = "QA_Setup";
// Update to find grid changes
//ug.Update();
ug.UpdateData();
// to retrieve ds with modified rows to grid
ds.GetChanges(DataRowState.Modified);
// Call to DAL to update QA_Setup with new data
da.updateDB(ds, sproc, dbAction, tableName);
}
This passes to the DAL and tries to run:
private void prepareUpdate(string sproc, string tableName)
{
// Create parameters for the QA_Setup dataset via Sqlcommand
SqlCommand cmd = new SqlCommand(sproc, conn);
// call set cmd properties
defineParams(tableName, cmd);
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = sproc;
//RunProc(sproc);
//RunProc(cmd);
dap.UpdateCommand = cmd;
}
However I get a System.Data.DBConcurrencyException, what am I missing?
|
|
|
|
|
I'm retrieving data from a mdb table and inserting it into a sqlserver table. The tables source and destination tables are identical. The mdb table has a PK that is set to autonum in the mdb and identity=yes in sql server.
The tables in the sql server are all empty. I want to simple insert all the data from the datatable into the sql server table including the original PK and autonum values.
I'm using a sqldataadapter and a commandbuilder to automatically gen the insert statements. But the cb's strips off the pk field from its statement. I basically want to invoke the adapters.update method against the datatable and have all the rows inserted. I've played around with SET IDENTITY_INSERT table ON but still the same issue. Seems to be that the commandbuilder is querying the schema of the table and noting the pk is an identiy column and is stripping it out of the insert statement.
PS I've already thought about manually generating the sql statements but its very slow as I have to cycle through each row and do this.
Dim cnn As New SqlConnection("server=glenn;database=test1;uid=sysadm;pwd=sysadm")
Dim adp As SqlDataAdapter
Dim cbd As SqlCommandBuilder
strSql = ""
strSql = "SET IDENTITY_INSERT " & c_strTable & " ON"
cmd = New SqlCommand(strSql, cnn)
cnn.Open()
cmd.ExecuteNonQuery()
adp = New SqlDataAdapter("select * from table", cnn)
cbd = New SqlCommandBuilder(adp)
Console.WriteLine(cbd.GetInsertCommand.CommandText) ' missing pk column? why?
adp.update(datatable)
Thanks
Glenn
|
|
|
|
|
Hi i am tryin to get the max of collection of calculated averages but when i try to use the simple option of using the max outside the avg function i get this error "Server: Msg 130, Level 15, State 1, Line 2
Cannot perform an aggregate function on an expression containing an aggregate or a subquery." so im just wondering if anyone has any idea how to avoid this problem
this is my code
Select 'Team Averages ' =
max(avg(case Code when 'A' then 4.0 when 'B' then 3.0 when 'C' then 2.0 when 'D' then 1.0 else 0.0 end))
From ScoutingReport sr
Where sr.Type = 1
And sr.ScoutId = 11619
group by matchid
and this is my output at the moment there are 3 teams and the averages are
-----------------------------
Team Averages
1.812500
2.937500
1.187500
-----------------------------
thanks in advance Tim
|
|
|
|
|
You've not told it how to group the averages, so it does not know of which to return the MAX().
SELECT matchid, AVG((CASE Code WHEN 'A' THEN 4.0
WHEN 'B' THEN 3.0
WHEN 'C' THEN 2.0
WHEN 'D' THEN 1.0
ELSE 0.0 END)
FROM ScoutingReport AS sr
WHERE sr.Type = 1
AND sr.ScoutId = 11619
GROUP BY matchid
The above will return the average for each matchid
If you want to find the MAX() of that then
SELECT MAX(average)
FROM
(
SELECT matchid, AVG((CASE Code WHEN 'A' THEN 4.0
WHEN 'B' THEN 3.0
WHEN 'C' THEN 2.0
WHEN 'D' THEN 1.0
ELSE 0.0 END) AS average
FROM ScoutingReport AS sr
WHERE sr.Type = 1
AND sr.ScoutId = 11619
GROUP BY matchid
)
If that doesn't give you the answer you want then you might need to explain more how you want the calculation to work.
Scottish Developers upcoming sessions include:
.NET debugging, tracing and instrumentation by Duncan Edwards Jones and Code Coverage in .NET by Craig Murphy
My: Website | Blog
|
|
|
|
|
thanks alot that is about 90% there it work fine when its just the averages but when i include the matchid(which i need) i still have then same problem this is the query at the moment
SELECT matchid, MAX(average)
FROM( SELECT matchid, AVG(CASE Code WHEN 'A' THEN 4.0
WHEN 'B' THEN 3.0
WHEN 'C' THEN 2.0
WHEN 'D' THEN 1.0
ELSE 0.0 END) AS average
FROM ScoutingReport AS sr
WHERE sr.Type = 1
AND sr.ScoutId = 11619
GROUP BY matchid) as t
group by matchid
this is what is returned from this query
814 2.531250
833 2.937500
938 1.187500
but the max average is 2.937500
so i need
833 2.937500
anymore help would be appreciated thanks again
|
|
|
|
|
|
thanks alot for your help with this i have managed to get the min and the max using this
Tim
|
|
|
|
|
I want to execute some tasks peridically on SQL Server 2000 but these are not realted to an action performed on my db's tables (doeasn't works with trigger). I just want to be executed my code regulary from time to time.
Any suggestion is welcome. Thanks.
|
|
|
|
|
SQL Agent jobs.
how vital enterprise application are for proactive organizations leveraging collective synergy to think outside the box and formulate their key objectives into a win-win game plan with a quality-driven approach that focuses on empowering key players to drive-up their core competencies and increase expectations with an all-around initiative to drive up the bottom-line. But of course, that's all a "high level" overview of things
--thedailywtf 3/21/06
|
|
|
|
|
db maintenance plan might work as well, but that really just runs a SQL Job. You can also use a DTS which can be scheduled ( as you've already guessed in a SQL Job).
Look where you want to go not where you don't want to crash.
Bikers Bible
|
|
|
|
|
Thanks for the advice, the SQL Job is a really good thing.
|
|
|
|
|
I'm building a new application that had a table (Tbl1) with 4 date fields in it. Lets call them date1, date2, date3, date4 and the DBA wants me to remove them. He is telling me that it is ineffecient to have 4 date feilds in my table. He wanted me to break them out into two other tables one with the dateType and one with the date itself. I told him that the performance difference will be negligable but it will be easier for me to code. I want to pull back all four dates in one record anyway, so why do I have to break them out. Is it really bad design to have a date field that allows nulls?
how vital enterprise application are for proactive organizations leveraging collective synergy to think outside the box and formulate their key objectives into a win-win game plan with a quality-driven approach that focuses on empowering key players to drive-up their core competencies and increase expectations with an all-around initiative to drive up the bottom-line. But of course, that's all a "high level" overview of things
--thedailywtf 3/21/06
-- modified at 8:41 Wednesday 14th June, 2006
|
|
|
|
|
ToddHileHoffer wrote: Is it really bad design to have a date field that allows nulls?
No.
The main question is what do the dates represent? If date1..date4 represent a continuation of the same thing then it may be better normalised to pull that out to a separate table. If they represent separate things then leave them where they are.
[ADDITION]
And using a performance argument on a DBA is virtually an exercise in pointlessness. In my experience they are hyper on normalisation to the detriment of everything else.
[/ADDITION]
[ADDITION2]
Damn! I just realised I've now got a dual role. I've recently joined a new company and I'm primarily a developer, but I'm also a DBA... GET THAT TABLE NORMALISED MAN!!! JUMP TO IT!!!! JUMP TO IT!!!
[/ADDITION2]
Scottish Developers upcoming sessions include:
.NET debugging, tracing and instrumentation by Duncan Edwards Jones and Code Coverage in .NET by Craig Murphy
My: Website | Blog
-- modified at 11:40 Wednesday 14th June, 2006
|
|
|
|
|
Colin Angus Mackay wrote: GET THAT TABLE NORMALISED MAN!!! JUMP TO IT!!!! JUMP TO IT!!!
----------
Some problems are so complex that you have to be highly intelligent and well informed just to be undecided about them.
- Laurence J. Peters
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
