This is my query.I am trying to figure out after the case statement
[code]...
How come I cannot get anything out of this?? I am trying how many days after more than 2 days.... Is there a way to do this?
[code]...
my app requires an elapsed time calculation that I have done like this
CONVERT(Varchar, (DATEPART(dd, GETDATE() - DateAdded)-1) * 24 +
DATEPART(hh,GETDATE() - DateAdded)) + 'h:' + CONVERT(Varchar, DATEPART(mi, GETDATE() - DateAdded)) + 'm' AS ElapsedTime
This works.. I can then colour my grid view cells accordingly using the substring function.. the problem is that I have been told that using GETDATE three times is not a good use of resources, is it possible to use the Datediff function to get the same result?
i'm very new to this world, and trying to develope a system that calculates Holidays. I want to exclude saturdays and sunday but it doesnt.
[code]...
I am trying to write some SQL to populate a gridview.
The sql should print out 3 columns:
Name, County, LastCollection (the amount of days since the last collection)
I have previously calculated the amount of days since last collection individually like this:
[Code]....
But am stuck on doing it for more than 1 record to populate the gridview which should show all the records and their last collection date.
I have a GridView that populates the days between the start and end date. What i want to ask is that is it possible to display a word "Overdue" (If possible "Overdue by 7 days") when the datediff is negative (eg: -7 days) The following are parts my codes for now:
<asp:TemplateField HeaderText="Date Raised" SortExpression="DateRaised">
<ItemTemplate>
<asp:Label ID="Label1" runat="server" Text='<%# Bind("DateRaised","{0:d}") %>'></asp:Label>
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="Expected Close Date" SortExpression="DateClose">
<ItemTemplate>
<asp:Label ID="Label2" runat="server" Text='<%# Bind("DateClose","{0:d}") %>'></asp:Label>
</ItemTemplate>
</asp:TemplateField>
<asp:BoundField DataField="DaysTaken" HeaderText="Allocated Days" />
<asp:BoundField DataField="RemainingDays" HeaderText="Remaining Days" />
<asp:SqlDataSource ID="SqlDS_UnresolvedIssues" runat="server"
ConnectionString="<%$ ConnectionStrings:DatabaseConnectionString_PITLog %>"
SelectCommand="SELECT [IssueLog].[DateRaised], [IssueLog].[DateClose], DATEDIFF(dd,DateRaised,DateClose) AS DaysTaken, DATEDIFF(dd, GETDATE() ,DateClose) AS RemainingDays FROM [IssueLog]
INNER JOIN [ActDetails] ON [IssueLog].[ActID]=[ActDetails].[ActID] WHERE [ActDetails].[ProjID]=@ProjID AND [ActDetails].[DevPhase]=@DevPhase AND NOT [IssueLog].[IssueStatus]=@IssueStatus">
<SelectParameters>
<asp:ControlParameter ControlID="DD_PMProjID" Name="ProjID" />
<asp:ControlParameter ControlID="DD_DevPhase" Name="DevPhase" />
<asp:Parameter DefaultValue="COMP" Name="IssueStatus" />
</SelectParameters>
</asp:SqlDataSource>
By the way, I am using Web Developer 2008, VB.net.
I have to believe that I'm just missing something silly on this one. It SHOULD work... but alas, it is not. Here is my code:
SELECT s.[id], s.[familyid], s.[lastname], s.[firstname], s.[middlename], s.[suffix], s.[nickname], s.[dob], fix(datediff(d, isnull(s.DOB, Now()), NOW()) / 365) as AGE, s.[allergies], s.[notes], s.[photographsOK], s.[school], f.[city], f.[state] FROM [Students] s, [families] f, [employee] e where s.[familyid] = f.[id] and f.[franchise] = e.[franchisename] and e.[username] = @Username order by f.[lastname]"
This was working fine before I put the "IsNull"handler in the datediff function to handle problems with null dates. Now I get the following error when I try to run it.Wrong number of arguments used with function in query expression 'fix(datediff('d', isnull(s.[DOB], now()), NOW()) / 365)'. I even tried using just the ISNULL(s.[DOB], NOW()) without the Datediff and get the same error.
I have some windows application with database. Now I want to make a server.
Like one pc will host MS SQL server 2005 and other pc can access that database. How can I do this?
and what will be the connection string for this. and mind it, its for windows application developped by C#.net
I have a column metricId in table Metric which have string values like '010277' or '0103444'.
I have to remove first four digits from the metricid and then get the Max value of the column.
total noob question here. I'm toying around with Silverlight, and need to connect to a database. When I connect in server explorer (VS 2010) it works fine, because it's using my windows account to authenticate. However, when I start debugging the server side stuff is running on a different account (NT-SERVICE or something to that effect) which has no privileges on the SQL server. I've tried embedding my username/password like this:
connectionString="Data Source=.;Initial Catalog={DATABASE};Integrated Security=True;User={DOMAIN}{USERNAME};Password={redacted}"
but it doesn't seem to be working. I keep getting an error:The server did not provide a meaningful reply; this might be caused by a contract mismatch, a premature session shutdown or an internal server error.
Does anyone know how i would generate an XML string using an XDR or XSL file in VB.net getting data from a SQL database (or from an my vb.net object), or even point me in the right direction
View 5 RepliesI want to create a program that allows me to enter the name of a table, a field along with a value. The program should concatenate the values to form a valid SQL select statement. I can also modify the program to include multiple field names and values. Example: -Select "FieldName" from "TableName" where "FieldName"= "Value" I have created 3 textboxes called and 3 labels named Select, From and Where This is my code here, i would need an assistance on the right way to achieve this, maybe mine is wrong.
[Code]....
in my database table there are 200 records.in description column record value is like .....
sometextLASER
sometext1LASER
sometext2LASER
i want to delete LASER from every record using sql.can enyone tell me how to do it.
I'm a web developer and I work primarily in Django -- I've never used ASP.Net or anything like that before, but I'm troubleshooting a problem for a friend's parents / client so I need some advice!
It's pretty simple: I need to change the parameters of their SQL Server database connection because of some server upgrades that happened at their hosting company -- is it ok for me to go in and just edit a .cs file, or does it need to be compiled or some other business?
Literally just need to change the parameters of
mySqlConn = new SqlConnection("server=hostname;database=dbname;uid=username;pwd=password;");
I have a query that converts my string value I have stored in the database (nchar(8)). I have a stored procedure that gets the value and converts to date and when I run the select part of my stored proc to test it it successfully returns the mm/dd/yy format that I want: ex: 02/28/2010 However when I call this stored proc from my program I am getting an exception:
<Exception> Conversion failed when converting datetime from character string.
</Exception> Puzzled why the query works fine but .net gives me an exception? does anyone know how to solve this?
The part of my query that does this conversion is:
[Code]....
i have a SP
[Code]....
as you can see from the SP, i need save in some variable because i have to create a dynamic SQL query who select with an "or" or "AND" from web form parametersthe problem is concatenate the SP parameter to my query
actually, this SP returns the string : "select * from...." and not the resultset!
I am still learning about datatype conversions, but what I am trying to do is
SELECT (month + '/' + year) AS ExpirationDate
Like this it gives me an error since the two columns are int, and I am trying to add the /
I know I have to CAST or CONVERT, how this is done?
I'm trying to compare a string from a label to a varchar(50) in my database. String Occasion = '1 2010-07-07 10:00' compared to varchar(50) OccasionID = '1 2010-07-07 10:00'
[Code]....
[Code]....
I've checked that the values really are identical in the debug. I was under the impression that no conversion is neccessary between string and varchar, but maybe I'm wrong.
I have created a new folder on my website called CanadaPost. I have check with my shared hosting service and they have told me that I have read and write access on the folder - CanadaPost
I have the following code....right now my program gets hung up (no error message) on the line TextWriter tw = new StreamWriter("~/CanadaPost/" + CartId + ."txt", false);
What would be the correct way of writing a string out to a text file?
[Code]....
How to create a SQL Server 2005 connection string .... if my site path on server is
[URL]
I have a table with three fileds userID, userName, userPassword.I have 100 rows in that table, now I want to update the userPassword column with new random generated string password.
View 1 RepliesI am new for web hosting.My hosting provider use plesk panel for manage websites.I have created MS SQL database using plesk panel. but every time I am getting error when trying to connect ro database. I am using the following connection string.
<add name="ACN" connectionString="Data Source=209.59.179.205SQLEXPRESS;Initial Catalog=ACN;User ID=xxxxx;Password=xxxxxx" providerName="System.Data.SqlClient" />
U can check error detail by just clicking on Login button on [URL]
I am confuse in understanding date storing in sql server and retrieving it in C# and converting date formats. guide me on the following basics:
1. In SQL Server datashould be stored as datetime or as string ?
2. To get date in different format (in SQL Server) why we convert it in string, is there no way that date can be obtained as date but in different formats ?
3. For getting date in some particular format is it better to convert date in format from SQL Server or it should be converted in C# ?
4. On C# end what should data type to hold date ?
5. In C# what should data type (string or date) if date is coming from database ?
6. In C# Cant we format date when its data type is date ?
I am having trouble connecting to a ms sql database on the server. The hosting company said that the .sql file was imported successfully, but when I try to use the 'login' or 'create account' features im getting an error page saying:
Failed to generate a user instance of SQL Server due to failure in retrieving the user's local application data path. make sure the user has a local user profile on the computer. The connection will be closed.
I am sure all the connection info is correct so it is really frustrating trying to work the problem. I have listed the connection string in my web.config file to see if anyone can see anything wrong with it.
I have a string in code behind like this
string html = "<asp:CheckBox ID="CheckBox1" runat="server" />";
So how to insert it into aspx page and when the page is rendering, it convert my string as i write it own in the webpag
I have a column (data type timestamp) in SQL Server 2008.Now I want to show this timestamp value in ASP.Net C# app as string. Is there any way to do that?I tried it using regular data fetching in ASP.Net but it produced System.byte[] as output rather than actual value. In SQL Server Management Studio values are represented as 0x000000000000B3C0
View 2 Replies
