|
Make a function:
CREATE FUNCTION [dbo].[RoundMinutes]
(@dDateTime datetime)
RETURNS DateTime AS
BEGIN
declare @minutes int
set @minutes = datepart(n,@dDateTime)
set @minutes = case when @minutes < 7 then -@minutes
when @minutes < 23 then -@minutes+15
when @minutes < 37 then -@minutes+30
when @minutes < 52 then -@minutes+45
else 60-@minutes
end
return(dateadd(n,@minutes,@dDateTime))
END
Call it like this:
select dbo.roundminutes(datefield) as RoundedDateField from mytesttable
--EricDV Sig---------
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 have to populate some datas from sql server database and display it directly in a CSV Format or CSV file. When i run the project it should provide me an option to whether open or save or cancel the file. when i click open it should be opened and be viewed in a excel sheet in CSV format and when i click save it should ask the destination folder and should be saved there in CSV format and when i click cancel it should be cancelled and the application should be closed. pls note that all these actions should happen in the same browser and should not be redirected to anyother page. Can anybody give me the detailed description and code in Asp.Net using C# .It's very Urgent.
Regards
Vijay.
|
|
|
|
|
Hi
Anyone please tell me that we can work only with Database.MDF file without sql server in asp.net 2.0.
if it is possible please tell me the details...
Regards
RPL
r_palanivel83 10:01 4 Jan '06
|
|
|
|
|
r_palanivel83 wrote: Anyone please tell me that we can work only with Database.MDF file without sql server in asp.net 2.0.
I wouldn't think so. SQL Server is not a file based database - although SQL Server 2005 Express edition does blur that distinction a little. When you access a SQL Server database you access the SQL Server, not the file. Only SQL Server can access the file directly - everything else must go through the server.
|
|
|
|
|
r_palanivel83 wrote: Anyone please tell me that we can work only with Database.MDF file without sql server in asp.net 2.0.
if it is possible please tell me the details...
Nope. You have to have SQL Server/SQL Server 2005 Express if you want to work with .mdf files. If you want to work with a file based database, then Access would be the way to go. You may want to look at Firebird SQL.
You will see a delete button on each of your posts. Press it. - Colin Angus Mackay
|
|
|
|
|
I have installed SQL 2005 Beta 2.It was not working properly so I have uninstall it.Now I am trying to install SQL 2005 Express that comes with Visual Studio.net 2005 but I couldnt install it because not all of the Beta 2 files have been removed (and they dont appear in the Add/Remove Programs in the control panel) so I used msiinv.exe and msiexec.exe in the command prompt to remove it but this way didnt solve my problem.Could any one tell me how could I Remove These Beta 2 files from my computer?.
I think this link will explain my problem for you more clearly:
http://blogs.msdn.com/astebner/archive/2005/07/01/using-msiinv-to-gather-information-about-what-is-installed-on-a-computer.aspx[^]
My problem is similar to Syed Irtaza Ali problem written in the comments in the link above.
I am too late but i will never give up
|
|
|
|
|
Hi ALL,
Is this the right place to ask "SQL Server 2005 - Reporting Services"
related questions? If not, direct me to the right place.
I'm new to MS-SS-Reporting Services.
I'm able to create some reports.
I just wanted to create a report like this with multi-level
columns.
Population Report
C1, C2, C3....Cities
------------------------------------------------------------------
Country-1 | Country-2
------------------------------------------------------------------
State-1 | State-2 | State-3 |
------------------------------------------------------------------
C1 | C2 | C3 |
------------------------------------------------------------------
| | |
| | |
Is it possible in MS-SS-Reporting Services?
If so, kindly let me know how to do.
As of now, I'm able create like this:
----------------------------------------------
Country | State | City | Population
----------------------------------------------
India TamilNadu Chennai ---
Trichy ---
Madurai ---
Coimbatore ---
-----------------------------------
Kerala Trivandrum ---
Cochin ---
Ennore ---
Kottayam ---
----------------------------------------------
SriLanka *** *** ---
Thanks in advance,
Sarvan AL
|
|
|
|
|
Yes it is fairly easy, You have to experiment with Matrix for report1 and use table for report2.
You also have to learn how use the embeded "IIF" statement to get your layout right. It's all trial and errror and plenty of RTFM.
Blog Have I http:\\www.frankkerrigan.com
|
|
|
|
|
Hi Frank,
Thanks for your suggestions.
Since I'm totally new to MS-SS-RS, let me
go through the manuals about "Matrix" report and
embedded IIF statement, and come back.
Thanks once again,
Sarvan AL
|
|
|
|
|
I need proffisionaly ADO.net Databaseconnection string Codingwith Sql server ....Pls help me
Thanking U
somasundaram
|
|
|
|
|
|
I have problem with Pass through queries. The problem is as follows:
The scenario is Pass Through Query (PTQ) has to execute a stored procedure on Sql Server (versions should be a problem in this case). So when PTQ is connecting to the SqlServer 2005 which is local to the application is working fine and returning results. The same is not working fine when PTQ has to execute SP which is on remote SqlServer.
I checked the ODBC connections they are fine.
Please help me.
Intelligence is measured by common sense not by how many scholarly books you read.
|
|
|
|
|
hello frndz,
am creating an application using vb.net and sql server. my table contain eno field as a primary key. when ever i run that appliction and give some no tho that field. if that no is already exist it was shown not allowed duplicate and some error msg. but am n need of such thing in a msg box..(ie "Your entered eno is already exist please try different no"). i think this is done using a trigger for table in sql. But i dont get such things. if any one tell me how to write trigger for that criteria? please help me....
thank you..
|
|
|
|
|
Please don't use SMS speak in the forum.
Right - your primary key, is it meant to be a unique identifier? If so, why not use an identity column and then let the database worry about setting it?
Arthur Dent - "That would explain it. All my life I've had this strange feeling that there's something big and sinister going on in the world."
Slartibartfast - "No. That's perfectly normal paranoia. Everybody in the universe gets that."
Deja View - the feeling that you've seen this post before.
|
|
|
|
|
hello,
am working with that identity function. but it is not helpful to me.cause in my application the user only give value for that primary key field(actually that is a 4 digit no and it is unique by nature) whwenever creating new files there is achance for giving same no. that time only we have to show some message that is a primary key so please try to fill someother no. am developing this application with sqlserver 2000 and vb.net 2005. i couldnt able to create trigger for primary key. please help me....
|
|
|
|
|
No trigger needed use an identity field
<br />
USE CREATE TABLE [dbo].[MyTalve](<br />
[ID] [int] IDENTITY(1,1) NOT NULL<br />
<br />
)<br />
<br />
You get the picture ?
Blog Have I http:\\www.frankkerrigan.com
|
|
|
|
|
sorry frnd. am a fresher for sql idont get such think identity field what is it? is it give some message (ie your entered no is already there) Please help me am in urgent. want to submit my proj today it self Please help me....
|
|
|
|
|
hai,
am working with that identity function. but it is not helpful to me.cause in my application the user only give value for that primary key field(actually that is a 4 digit no and it is unique by nature) whwenever creating new files there is achance for giving same no. that time only we have to show some message that is a primary key so please try to fill someother no. am developing this application with sqlserver 2000 and vb.net 2005. i couldnt able to create trigger for primary key. please help me....
|
|
|
|
|
Hello All,
I'm buildling an egovernment web application using ASP.NET (VS 2003) that will be used by a medium sized organization with 1000 users as a start.
My Code was in a single VB module which has all the functions that run the system, and the pages only call those functions.
I have built a wrapper class to ease communication with the database (in order words, to completely eliminate any worries about database and focus on the business objects)
so i ended up with functions dealing with business objects, and i can now really go highspeed in buildling the app.
However My problem is:
it's Easy to do the coding now, very easy.
but each function that deals with business objects will require calling another function that also deals with business objects.
so it's like (for example)
function RegisterUser(a,b,c,d,e)
if Func1(a,b,e)=true then
func2(a,b)
func3(b,c)
func4(a,b,c,d,e)
end if
func5(a,b,"ok")
end function
each one of these functions queries the database for some information.
so let me make it easier for u, when a user logs in, The application actually made 30 queries to the database and with each query, it's opening a connection and closing it afterwards.
when the user creates a new document, it's like 120 queries.
when the user clicks the search button, it's about 400 queries.
that's cause i made coding easier by wrappers.
the application itself works JUST fine.
but i got two issues, 1. what if there are 1000 users, and 2. what's the good approach to a better programming.
so, i really need your help guys.
i need to figure out, is Open/Query/Close connections worse than
Open/alot of queries/close. i think yes, but i need a professional opinion.
also, is it a good approach to do wrappers like that.
is the current situation harmless and SQL SERVER 2000 can deal with these too much connections and has some way of Caching?
i can explain more with code.
Thanks in Advance.
Anmar
|
|
|
|
|
Anmar_Code wrote: i need to figure out, is Open/Query/Close connections worse than
Open/alot of queries/close. i think yes, but i need a professional opinion.
No, it is not worse - it is the recommended design. (Aquire connection, execute query, release connection)
[ADDITIONAL]
The main thing that reduces performance is the round trips to the server, not the opening and closing of connections. You should reduce the number of queries you have to make.
[/ADDITIONAL]
Now, if you have a method that performs two queries one after another then keep the connection open a little longer to make the second query - if you can you can even put two SQL Queries into on SqlCommand object (just separate them with semi-colons) or even better use stored procedures.
However, if you are calling 400 queries for one action then I really do recommend that you look at what you are doing and seeing if they can be done in less queries. Stored Procedure can help becuase they can do many things at once (including conditional stuff, so you INSERT in to table A, and if some value is this then INSERT also in Table B, but if not then UPDATE table C, etc.)
It looks to me if you are doing stuff like
func2(a,b)
func3(b,c)
func4(a,b,c,d,e)
And all those methods do is run SQL Queries then you could rationalise them into Stored Procedures.
|
|
|
|
|
Thank u for ur information
i will trace the queries and see what i can do...
i might however, ask some other questions although they might sound a little silly...
Thanks alot...
Anmar
|
|
|
|
|
You may also want to look at the Data Application blocks for your connection to the database which gives you an instant DAL (Data Application Layer), some people link them some people don't
Search the MS site for "Data Application Block" under patterns and practices.
Blog Have I http:\\www.frankkerrigan.com
|
|
|
|
|
I've a DataSet with lots of DataTableAdapters which all work fine, but i'm getting a very strange behavior when merging two unchanged DataSets:
pseudo code:
MyDataSet ds1 = new MyDataSet();
new MyTableAdapter().Fill(ds1.MyTable);
MyDataSet ds2 = new MyDataSet();
new MyTableAdapter().Fill(ds2.MyTable);
ds1.Merge(ds2, true, MissingSchemaAction.Error) // Need to preserve changes because there might be some, but not in this test case.
The Select statement isn't anything special either: 'SELECT Column1, Column2, ... FROM MyTable' and the Database entries are garanted to be unchanges between queries.
Problem:
After merging those two MyDataSets all rows of MyTable are marked as changed, but really aren't (i'm absolutly positive about that).
On the other hand modifying one row in ds1.MyTable before merging results in one changed entry afterwards, which is correct...
I'm disecting this for hours now, but can't find a solution for it.
Funny that nobody else does seem to have such problems, all articles and forum entries i've found releate to changes NOT reflected within the DataTable, which works perfectly well in my case...
Does anyone have an idea or suggestions on how this problem came to be?
Thank's in advance!
-- modified at 11:40 Wednesday 6th December, 2006
Variables won't; constants aren't. (Osborne's Law)
|
|
|
|
|
Who can give me some advice about the following:
I have to make a table for keeping information about clients. Because a minority of the clients have two addresses e.g. invoice address and delivering address, i thought that for reason of efficiecy it would be better to put the necessary fields for keeping the address information in a separate table,so i built three tables :
1) tblClients with the fields : ClientID, Name, Client since
2) tblClientAddresses with the fields: ClientId, AddressId, Street, Postcode, City
3) tblAddressTypes with the fields: AddressID, Description.
AddressID = 1 stands for DeleveringAddress , AddressID = 2 stands for invoicing address and 3 stands for delvering address and invoicing address is the same.
There's a one to many relation between tblClients and tblClientAddresses based on the field ClientID.
There's also a one to many relation between tblAddressTypes and tblClientAddresses.
I would like to build a class Clients with the fields :ClientID, Name and Client Since, and within this class two other classes : class ClientAddresses and Addrestypes ( both nested classes)
If all the neccerary fields for keeping the information of the client would be in on table and thus in one single class the the following function would be possible te make and work:
Public shared Function GetClient() As List (of Client)
Dim cnn As SqlConnection = GetConnection()
Dim selectstatement As String = "Select ... From tblClients"
Dim selectCommand As New SqlCommand(Selectstatement, cnn)
cnn.Open()
Dim reader As SqlDataReader = selectCommand.ExecuteReader()
Dim client As New List (Of Client)
Do While Reader.Read
Dim c As new Client
c.ClientId = reader("ClientId").ToString
...
The question is : How can i do the same with this three tables instead of one
in order to fill a list with all the fields out of this three tables?
ODRAP
|
|
|
|
|
There are many ways. You could use a DataSet with two tables and a data relation, or you could loop through the outer list and then read in matching items from the inner list or... You get the idea anyway.
Arthur Dent - "That would explain it. All my life I've had this strange feeling that there's something big and sinister going on in the world."
Slartibartfast - "No. That's perfectly normal paranoia. Everybody in the universe gets that."
Deja View - the feeling that you've seen this post before.
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
