|
Have your tried DateTime.ToShortDateString?
A good summary of the assorted DateTime stuff is at [^]. I keep that page bookmarked.
CQ de W5ALT
Walt Fair, Jr., P. E.
Comport Computing
Specializing in Technical Engineering Software
|
|
|
|
|
Walt,
Thanks for the pointer the page dose have a lot of great formats etc on it. I have added it to my favorites.
I had to change the Single Quotes to # around each of the parameter dates and it worked great.
Thanks for all the help and pointers.
SquireDude
|
|
|
|
|
Walt Fair, Jr. wrote: DateTime.ToShortDateString
Not ideal, as it depends on regional settings (and could contain all kinds of nonsense), whereas the database content does not. An explicit format would be the preferred one here.
Here is an alternative summary[^].
|
|
|
|
|
True! Thanks for pointing that out, Luc.
CQ de W5ALT
Walt Fair, Jr., P. E.
Comport Computing
Specializing in Technical Engineering Software
|
|
|
|
|
No problem.
|
|
|
|
|
Hi,
I need some feedback on some code generation software I'm in the process of re-developing for SQL Server (2005/2008).
Its called SSCodeGen (for 2005/2008) and the new version (8.3) is now up at www.RaisinTech.com/SSCodeGen.exe, It will
- Generate the 4 standard (A/I/U/D) stored procedures for a table (text/image field not currently handled)
- Generate the execute statements for a stored procedure (slightly more complete than that generated from SS Man Studio)
- Generate the sql for S/I/U/D statements for one or 2 tables on the database.
Obviously it is free, and it doesn't require installation, you can just download and run.
There is a version for SQL Server 7/2000 at http://users.quista.net/thealey/.
Any comments appreciated
BillyTheKidney
|
|
|
|
|
This is a good excercise and every developer should build such a tool, I am continually astonished to see some quite skilled dev hand coding crud procedures and methods.
Try extending it to include the c#/VB stubs to call the stored proc. Also think about parameterised queries instead of procs, I am currently changing mine to do this because sql compact does not support procs  .
Then go on to create the model class for the table. Take into account where you have a view of the same data and add the additional fields into the model class.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Thanks for the input, appreciated.
The old SQL Server 7/2000 version generates the old VB/ASP code for calling a stored proc, though I haven't got round to changing it for the 2005/8 version as yet.
I would be interested to see your code generation software.
BillyTheKidney
|
|
|
|
|
i have to insert into two tables
1st table orders
orderno int primary key autoincrement,
username nvarchar(50) ,
shopname nvarchar(50),
email nvarchar(100)
2nd table is Orderdetails
serialno int primarykey autoincrement,
orderno int foreignkey,
categorynamee nvarchar(50),
itemkey nvarchar(50),
itemkeyname nvarchar(50),
currentqty decimal,
salesprice decimal,
quantity int,
total decimal
can you give example which helps me
|
|
|
|
|
I am assuming you are using SQL 2005 or better.
Firstly, you need to insert into the order table and get the order number by using scope_identity. Then use this to insert into the order details. The whole lot needs wrapping in a single transaction
(clue - the bold words should be used for searching)
Bob
Ashfield Consultants Ltd
|
|
|
|
|
I couldn't find any reference to if it's a free upgrade from 2008. I'm assuming not ... but then again I find it hard to believe so many people will spent tens of thousands for a new license for point release?
|
|
|
|
|
Free if you have Software Assurance, 7499$ per socket for the standard edition if you don't. It's not considered a service pack (like server 2008 R2 was not considered a service pack)
|
|
|
|
|
In MS SQL 2008 I'm often doing big SELECT * statements from a couple tables looking for data in various fields, and have to scroll sideways in the SSMS window.
What I'd like to do is for every field in whatever table I'm selecting, to pivot so its name goes into FieldName, and its value into FieldValue. Then I can use sorts and quickly find what I'm looking for.
I've seen some articles on PIVOTs but they are almost always hardcoded for a specific table, and use aggregates. I want something dynamic and simple.
ie: SELECT * FROM AnyTable
Column 1, Column 2, Column 3
Data 1, Data 2, Data 3
Into:
Column 1, Data 1
Column 2, Data 2
Column 3, Data 3
I'm guessing some stored procedure is necessary but I haven't been able to find any. Does anyone have something simple in the toolbox?
|
|
|
|
|
Ahh now I have to explain to my collegues why I am sitting here chuckling - quick, generic and pivot all in the same sentence.
There is nothing quick or generic about pivot tables. I have an article[^] that uses dynamic sql to avoid the hard coding but it is not quick and generic.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
for example two columns of table1 , have relation to the id column of table2
for example:
tbl_city: (id , title)
tbl_person: (id , name , CurrentCityID , LastCityID , ...)
now , I want to make two relations from tbl_person to tbl_city
CurrentCityID and LastCityID must relation to tbl_city
this query is wrong:
SELECT tbl_person.name , tbl_city.title , tbl_city.title
FROM tbl_person
JOIN tbl_city ON tbl_person.CurrentCityID = tbl_city.id
JOIN tbl_city ON tbl_person.LastCityID = tbl_city.id
can you help me ?
H.R
|
|
|
|
|
Yes, but you may want an AND instead.
|
|
|
|
|
Maybe you ar looking for aliases in sql queries?
I think this query is the right one for you:
SELECT
person.name as personName, currentCity.title as currentCityTitle, lastCity.title as lastCityTitle
FROM tbl_person as person
JOIN tbl_city as currentCityON person.CurrentCityID = currentCity.id
JOIN tbl_city as lastCity ON person.LastCityID = lastCity.id
Note the as keyword used in the querry.
I have no smart signature yet...
|
|
|
|
|
Stanciu Vlad wrote: Note the as keyword used in the querry.
I've always left the as out.
|
|
|
|
|
That is a common practice, but in the previous example I assumed the questioner has no or little knoledge about aliases, therefore I followed a more explicit approach.
I have no smart signature yet...
|
|
|
|
|
Hi Friend,
I have one question about database management...
I have one Procedure which take 30 min to execute....
I have crate front end for that Process, the scenario is when user press the button to execute that Procedure but find no response and close the browser what happen for that procedure after this, i mean that procedure is executed but did't stop....that procedure is using server resource but unmanaged how to managed that.
Thanks and regard's in advance.
Sasmi
|
|
|
|
|
In case of SQL server 2008, the stored procedure won't execute if the calling program is stopped. Which database or you speaking of?
|
|
|
|
|
i am talking about sql Server 2005...
|
|
|
|
|
I believe it should also behave the same way. You can try to replicate the scenario using following in the stored procedure:
waitfor delay '00:00:10'
insert into SomeTable values ('some value')
This will cause a delay of 10 seconds before the insert. While SQL is waiting for the delay to get over, you can stop the calling application of yours and then check if the insert happened.
BTW is 30 min execution time expected or not? If not, you can check the execution plan and it would give you an idea why so much time is taken.
|
|
|
|
|
First and foremost, what user is going to sit there and wait for 30 minutes for the query to execute? I would spend my time optimising the query rather than worry about what happens WHEN the user closes the browser.
Anyone who has a stored proc taking that amount of time to execute while a user sits waiting deserves any and all problems which they get from it.
Bob
Ashfield Consultants Ltd
|
|
|
|
|
Hi, thanks for looking
I need to sort the results of my query, lets say i have the following values in my Articles table
Id, Title
--------------------------
1, "The less you talk..."
2, The long and...
3, -ABC of Math
4, *Something else
The result should look as follows:
3, -ABC of Math
4, *Something else
1, "The less you talk..."
2, The long and...
Notice that the title may o may not have or may have more than 1 special character at the beggining
Thanks in advance for any help
Alexei Rodriguez
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
