Get Rid Of Time From SQL Server's Data By Using ASPX
Mar 20, 2007
Hi,
I have the problem in accessing data from MS-SQL server by using Dreamwearver MX 's VB.NET page. Although it works well, in the date format, it also display time. Please see the following code.
SELECT student_ID, Material_ID, Borrow_Date, Due_Date, Renew_Date, Status, include_with
FROM dbo.Record
WHERE student_ID = @student_ID
To remove time, I added the following code as follows. After that, I can preview and works well without having time.
SELECT student_ID, Material_ID, convert(varchar(10),Borrow_Date,103), convert(varchar(10),Due_Date,103), convert(varchar(10),Renew_Date,103), Status, include_with
FROM dbo.Record
WHERE student_ID = @student_ID
But, when I browse, I get the runtime error. When I look more details in server, the error said " Parser Error Message: The server tag is not well formed." and error in <MM:DataSet"
Hi,I have a form page with an insert like this:<asp:SqlDataSource ID="addProductDataSource" runat="server" InsertCommand="INSERT INTO test( title1, firstName1, lastName1, dateOfBirth ) VALUES ( @title1, @firstName1, @lastName1 @dateOfBirth )" ConnectionString="<%$ ConnectionStrings:yourQuoteCentreConnectionString %>"> <InsertParameters> <asp:ControlParameter ControlID="dateOfBirth" Name="dateOfBirth" Type="DateTime" /> <asp:ControlParameter ControlID="title1" Name="title1" PropertyName="Text" /> <asp:ControlParameter ControlID="firstName1" Name="firstName1" PropertyName="Text" /> <asp:ControlParameter ControlID="lastName1" Name="lastName1" PropertyName="Text" /> </InsertParameters></asp:SqlDataSource> In default.aspx.vb I have:Protected Sub Wizard1_FinishButtonClick(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.WizardNavigationEventArgs) Handles Wizard1.FinishButtonClick 'CREATE DATETIME FROM USERS BIRTH DATE Dim dateOfBirth As New System.DateTime(applicantYear.SelectedValue, applicantMonth.SelectedValue, applicantMonth.SelectedValue, 0, 0, 0) 'INSERT TO DATABASE addProductDataSource.Insert()End SubI would like to reference the variable dateOfBirth created in default.aspx.vb in the insert query on the page, or ideally move the insert query into the aspx.vb page. Would this make more sense to do?Thanks,Paul
We are using SQL Server 2008 as our database and use Access as a GUI. I am looking to create a form in Access where employees can access their time card and request changes from management. I want to use the format from the attached screen shot for the form. I pretty much know how to do it all, the only point of complication is trying to figure out the easiest way to get the transaction punch record data on employee_punch_record into a format where I can easily populate the form in the horizontal format you see in the screen shot.
I am not super strong in SQL, but figure I can do it using a formatting table of some sort. quick and easy way to move transaction records into a more horizontally oriented record?
When we need to add or edit a member on a entity, for columns with foreign key, we get a search box to look up the value. Here the default search is based on name. We want the code to be the default search column. I see that when i changed in the backend .aspx file and restarted IIS, the change happened and now the default search is code. I just want to know if there will be any impacts going forward ? may be during upgrades or migrations ?
Hello All, I am just getting started on aspx, coming from an asp background. I created the following connectionString in my web.config file:<connectionStrings> <add name="OuWebDB" connectionString="server=OurServer;database=OurDatabase;uid=TheUser;pwd=ThePassword"providerName="System.Data.SqlClient" /> </connectionStrings>
when I run this aspx file, from a machine, I get the following error: An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server) Help!!
I would like to edit the Folder.aspx, or mainpage.aspx file in sql server reporting services. for example instead of showing sql server reporting services on the top of the folder.aspx page, shows Testing ...
hi friends, i need a code for storing and receiving an image to/fro SQL SERVER 2000 (in C#). i had searched some sites, all are in VB for windows forms not for website. finally i got a code from some site. it is working for storing purpose. not working for receiving purpose. the code for receiving and displaying purpose is (in a fresh page) private void Page_Load(object sender, System.EventArgs e){ // Put user code to initialize the page here MemoryStream stream = new MemoryStream (); SqlConnection connection = new SqlConnection (@"server=INDIAINDIA;database=iSense;uid=sa;pwd=india"); try { connection.Open (); SqlCommand command = new SqlCommand ("select Picture from Image", connection); byte[] image = (byte[]) command.ExecuteScalar (); stream.Write (image, 0, image.Length); Bitmap bitmap = new Bitmap (stream); Response.ContentType = "image/gif"; bitmap.Save (Response.OutputStream, ImageFormat.Gif); } finally { connection.Close (); stream.Close (); }}what s the problem is.........i'm getting an exception at Bitmap instantiation.(i.e Bitmap bitmap = new Bitmap (stream);)exception is "Parameter is not valid" what is the problem with that coding? was it correct? do u have any code for this in C#? if so, pls provide that......help me........pls.............
USE [Testing] GO /****** Object: Table [dbo].[Testing] Script Date: 4/25/2014 11:08:18 AM ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON
[Code] ....
It seems to work fine with one million records.
Each primary key is unique, but the begindate is non-unique, and i guess even if i use datetime2 and add nanoseconds, from what i have read, there is a chance that i could have a duplicate datetime since the date is imported via XML from multiple sources.
how do i insert into a datatime field of sql server just the time that is 10:45:00 PM, I do not want the date just the timeinsert into tblTest (LoginTime) values (@_Time)will insert the 10/10/2005 10:45:00 Pm but i just want 10.:45:00 pm how do i do that.
Hi! I'va tried to find but I wasn't successful... I have a column in my database which I want it to be in minutes.seconds.cents of second... Is it possible to define this data type in sql server? Thanks for any precious reply!
I am trying to call a Web Servie within a SSIS package. I want to pull a row at a time from a SQL Server 2005 table and pass this data values onto the web service.
I'm wondering what is the best way to store time-series data in an SQL database?? I've done a bit of investigating on the rrdtool (round robin database tool) that is used in a lot of nix based solutions for monitoring network equipment. I have a need to collect performance data from servers and routers and then produces some nice graphs from that data. I'm just not sure who i should store that data without the database growing to some huge size.
i want to show data Party Name and Time interval wise. here is my table from where i will fetch data. so pasting table data here.
Call start Call duration Ring duration Direction Is_Internal Continuation Party1Name Park_Time ------------------------- ---------------- ------------- --------- ----------- ------------ --------------- ----------- 2015/06/08 08:06:08 00:02:28 2 I 0 0 Emily 0 2015/06/08 08:16:38 00:00:21 0 I 0 1 Line 2.0 0 2015/06/08 08:16:38 00:04:13 5 I 0 0 Jen 0
[code]...
now i am not being able to cross join this CTE with my table to get data party name wise and time interval wise. say for if no data exist for a specific time interval then it will show 0 but each party name should repeat for time interval 9:00:00 - 9:30:00 upto 17:30:00. i like to add what filter need to apply to get data for incoming, outgoing, call transfer and miss call.
For Incoming data calculation where direction='I' and Is_Internal=0 and continuation=0 and RIGHT(convert(varchar,[call duration]),8)<> '00:00:00' For outgoing data calculation
Hi guys, When I thought everything is okay with this script, I got a new problem... I have a VBA's script from Excel 2003 that builds sql script and retrieves data from SQL SERVER 2000. in order to make the sql running, I need to use a multi - batch processing, to pass and execute every command line once a time.
Up to here, I am using a test case with Account number = '123456' and getting the desire results. The code below is running okay with the test case, but when changing the account number (mark as yellow in the code) to include all the accounts (or just one other account), I am getting the following ERROR: run - time error '-2147217871 (80040e31)' - [Microsoft] [ODBC SQL Server Driver] time out expired.
Now, if I take the same code, with the condition that generates the ERROR, and try it into SQL Server, I get the results without errors. Thanks in advance, Aldo.
Below the code:
Code Snippet Function QuerySalesAging() '-------------------------------------------------------------- 'MUST !!! References: Microsoft ActiveX Data Object 2.1 Library '--------------------------------------------------------------
Dim ConnString As New ADODB.Connection Dim RecordSet As New ADODB.RecordSet
'Report Criterias Criteria05 = " AND " & "Accounts.ACCOUNTKEY Between " & AccountKeyAsRange ' -- ==> With AccountKeyAsRange = '123456' AND '123456' it works okay. ' -- ==> With any other value, in example AccountKeyAsRange = '123456' AND '9999999999' it get's ERROR.
CmdLine01 = " USE " & CompanyName
' Check and drop temporary table TemporaryTableName = "CTE" ' The table is a regular one CmdLine02 = " if object_id('" & TemporaryTableName & "') is not null exec('DROP TABLE " & TemporaryTableName & "') "
CmdLine03 = " SELECT ..." CmdLine03 = CmdLine03 & " INTO " & TemporaryTableName CmdLine03 = CmdLine03 & " FROM ..." CmdLine03 = CmdLine03 & " WHERE " & "(" & Replace(Criteria05, "AND", "") & ")" CmdLine03 = CmdLine03 & " ORDER BY ..."
Is there a way to keep track in real time on how long a stored procedure is running for? So what I want to do is fire off a trace in a stored procedure if that stored procedure is running for over like 5 minutes.
I encountered a very strange problem again. Why the time series displayed on the chart are so strange? The Key time column I chose for my time series algorithm is cal_month(e.g 199001...), but why the date displayed on the time series chart is like :05/06/2448? (it should be like 199001..?) What is that data? And where exactly did it come from? What is the exact cause of this?
Hope it is clear for your help.
I am really confused on this and thanks a lot for your kind advices and help and I am looking forward to hearing from you shortly.
I'm trying to use a Select statement on Full-Text-Indexed field in SQL Server.The .aspx page does not like the " " inside my SQL statement. How do I get around this?Select parts.ProductNumber, T1.Description FROM T1, T2 WHERE T2.Ground='1' and T1.ProductNumber=T2.System and CONTAINS(T1.Description, ' "5001*" ')It does not like the " around the 5001* how do I escape them or whatever to make it work?- Rex
how I can load the CSV file data into the sql server table. I know there are ways like bulk insert and other to load the csv file data into the table. But in my case the table doesn't exist and has to be created at the run time. With simple insert in temp table we do like select * into #temp from tablename and that creates the temp table. So. I need something like that which create the temp table and load the data into it. because the CSV file would have different number of columns and names so I can not create the table structure in advance. I have to create the table at run time.
Hi!Im building a site for internal use for a company i work at.And im having hard to decide what to do. The alternatives arecomma separated lists vs separate tables. For example.. I will use a number of different type of objekt that all will hold 1 or more document links, phone numbers, emails etcAs far as i can i i have 2 possibilitys. One is to add textfields for each of these subojects in each objects tables. Then i add all info as commaseparrated lists in 2-3 layers wich i then save in the respecive objects fields. (Look like something below for example ),comment'emailadress,comment'emailadress,Or i add 1 table for each subobject type wich manage the subobjects or link between different objectsfor example:table layout for email:id | parent_type | parent_id | comment | emailOr for example contacts a mediator table:id | parent_type | parent_id | contact_id Hope you understand what i mean, would really appriechiate suggestions, comments regarding what can be considered as "best practise" in these cases. Putting it in separate tables should make searches easier, and coding easier in my book. But again im thinking if perhaps commalists is a faster and more recource conservative way of doing it + to me it feels like (perhaps i have no reason to think this?) making tables for items like feels like a timebomb if you exhaust your idfields identity count. Though this app will be used by perhaps 100ppl at most so perhaps not an extremely utilized webapp.Anyway glad if you could give me some input!
I am trying to deploy an aspx application which talks to sql 2005. The application files are on a w2003 web server with asp.net 2.0 and the sql server is a 2003 server with sp1. when i attempt to browse this application i am prompted with "Unable to get records. Unable to connect to database. Login failed for user 'NT Authorityanonymous Logon'". The application is set to use windows authentication. However if i create the "anonymous" login in sql and make it a sysadmin, it works, of course. In my dev enviroment i have sql2005 and iis on same w2003 server and the same application WILL run and there is NO anonymous account, just using AD accounts.
Hello, I am using the web blog at http://weblogs.asp.net/scottgu/archive/2007/01/11/tip-trick-how-to-upload-a-sql-file-to-a-hoster-and-execute-it-to-deploy-a-sql-database.aspx to deploy an SQL script on my database. The sql script is named aspnetdb.sql and is placed in my main directory. Now using the file RunSQL.aspx which is also placed in the main directory (www.genealogy-prototype.com/RunSQL.aspx) I have changed the parts in the file which need changing:// Url of the T-SQL file you want to run string fileUrl = "http://genealogy-prototype.com/aspnetdb.SQL";
// Connection string to the server you want to execute against string connectionString = @"Server=genealogy-prototype.com;User ID=marko;Password= 123;Initial Catalog=aspnetdb";but when I go to my page genealogy-prototype.com/RunSQL.aspx I am getting an error. However I'm not sure what this error is as it says I need to change custom errors to off in web.config, i have done this but it still says i have it on... I'm guessing it must be something to do with the connection string i have given or the file url. My hosting company is freezone.co.uk, my ms sql database name is aspnetdb my username for this database it marko and there is no password. Is the connection string i have given wrong? I have been trying this for over a week, any help would be great!!Thank you, Mark
I have a stored procedure that is used to create a DataDictionary for the database. Basically, the sproc generates HTML and you can then run it from Query Analyzer by Saving to a file.
What I want to do is to run this from an aspx page.
Hello, Can someone kindly point out what is wrong with the following code file. I'm trying to: - fill a dropdown from a db on page load (this works!) - when user selects from the list and hits a button, pass the dropdown value to a second query - use the second query to make another call to the db and fill a data grid
In the code below, if I swap an actual value (eg '1005') into the command and comment out the .Parameter.Add statements, the dategrid is filled sucessfully. Otherwise, when the button is pressed, nothing is displayed.
Thanks
PS comments about my coding approach are welcome - I'm new to aspx...
<%@ Language="VBScript" Debug="true"%> <%@ Import Namespace="System.Data" %> <%@ Import Namespace="System.data.SqlClient" %> <html> <head> <script language="vb" runat="server"> Dim dbConnection As SqlConnection Dim ds_Teams,ds_Agents As DataSet Dim sqlCmd_Teams,sqlCmd_Agents As SqlDataAdapter
Dim dbConn = "server=csacd01;uid=mpierce;pwd=cabledog;database=clearviewacd"
Dim sql_select_agents = "" & _ "SELECT last_name + ', ' + first_name AS name, agent_no " & _ "FROM dbo.users " & _ "WHERE (team_no = @Team) AND (NOT (agent_no = '1029')) " & _ " AND (NOT (last_name IS NULL)) " & _ " AND (NOT (first_name IS NULL)) " & _ " AND (NOT (first_name = 'FirstName')) " & _ "ORDER BY last_name"
Dim teamList = "teamList" Dim agentList = "agentList"
Sub Page_Load(Sender As Object, E As EventArgs) if not (IsPostBack) ds_Teams = new DataSet() dbConnection = New SqlConnection(dbConn) sqlCmd_Teams = New SqlDataAdapter(sql_select_teams, dbConnection) sqlCmd_Teams.Fill(ds_Teams, teamList) dbConnection.close()
dropdownlist_Teams.DataSource=ds_Teams.Tables(teamList).DefaultView dropdownlist_Teams.DataBind() end if End Sub
sub Get_Agents(Sender As Object, E As EventArgs) ds_Agents = new DataSet() dbConnection = New SqlConnection(dbConn) sqlCmd_Agents = new SqlDataAdapter(sql_select_agents, dbConnection)
Hi! i'm dani, from spain. I´m looking for sombody to help me. The trouble is as i have indicated. I get the next error when my page aspx needs to acced to the SQL Bd: "Error of beginning of session of the user '(null)'. Reason: it is not associate to a connection of confidence SQL Server." I hope a reply. Thanks and greethings.
Hi All,I am trying to run aspx website on IIS version 6. It works locally with WEb Matrix but when I put the site into the IIS server, it cannot connect to the database. THe IIS is sonfigured and all users have full permissions but the error that is returned is:Login failed for user '(null)'. Reason: Not associated with a treusted SQL Server connection.SQL Server 2000 is installed and a connection is open to the database in question. Would be very greatfull for ANY help on this, being tryin to get this workin for days!!Collette.
Hi, i am trying to retrive some datetime set from the database (2 posts in this table), senastposted is a datetime field.when i run:SELECT senastposted FROM ftp WHERE senastposted <= 2005-10-28i get this result:
senastposted 0 record(s) affected.and when i run: SELECT senastposted FROM ftp WHERE senastposted >= 2005-10-28i get this result:
senastposted 25-10-2005 29-10-2005 2 record(s) affected.what i want is to retrive the first value based on the date:25-10-2005any idea why SELECT senastposted FROM ftp WHERE senastposted <= 2005-10-28 dosent work?( i have tried to change the dates to 28-10-2005 and 28/10-2005)
I am using the web blog at http://weblogs.asp.net/scottgu/archive/2007/01/11/tip-trick-how-to-upload-a-sql-file-to-a-hoster-and-execute-it-to-deploy-a-sql-database.aspx
to deploy an SQL script on my database. The sql script is named aspnetdb.sql and is placed in my main directory.
Now using the file RunSQL.aspx which is also placed in the main directory (www.genealogy-prototype.com/RunSQL.aspx) I have changed the parts in the file which need changing:
// Url of the T-SQL file you want to run string fileUrl = "http://genealogy-prototype.com/aspnetdb.SQL";
// Connection string to the server you want to execute against string connectionString = @"Server=genealogy-prototype.com;User ID=marko;Password=</span> <span lang=EN>123;Initial Catalog=aspnetdb";
but when I go to my page genealogy-prototype.com/RunSQL.aspx I am getting an error. However I'm not sure what this error is as it says I need to change custom errors to off in web.config, i have done this but it still says i have it on...
I'm guessing it must be something to do with the connection string i have given or the file url. My hosting company is freezone.co.uk, my database name is aspnetdb my username for this database it marko and there is no password. Is the connection string i have given wrong?
I have been trying this for over a week, any help would be great!! Thank you, Mark
