Inserting And Retriving Datetime Field In Database MSSQL 2000
Jan 10, 2006
Hi, Assume I have a table name "myTime". This table is simply only have 1 (one) DATETIME field "MyTestTime" (also serve as a primary number).
Table MyTime
- MyTestTime : SQLTYPE DATETIME
To insert a new row into this field, I simply wrote :
SqlCommand sqlCommand = new SqlCommand("insert into MyTime values('2006-01-09')", sqlConnection);
I got the value of "2006-01-09" from a textbox or other relevan control.
I realize when I try to use "SELECT * FROM MyTime" statement, MSSQL server 2000 automatically convert my date value from "2006-01-09" to "01/09/2006" (from YYYY-MM-DD to MM/DD/YYYY). I don't know why this one must be converted to MM/DD/YYYY automatically (I believe this behavior is depend on some "setting option" in my MSSQL server - but I don't know which one).
The challenge is :
In my country, the actual date format is like German Date format (DD-MM-YYY). Well I know this is only "Customization" problem. But how insert datetime value given from sql query to a datetime variable?
// Connect to database, make a query, get the datareader result, and bla bla bla
DateTime aDateTime = new DateTime;
aDateTime = Convert.ToDateTime(myDataReader["PostDate"].ToString());
// close connection
My question is
How can I make sure that aDateTime's day is 09 not 01. How my program know that 09 is day not month. I can't use string.split() method because it's possible that my database setting will change from "mm-dd-yyyy" to "dd-mm-yyyy"
I have been trying to add values to a database and it keeps failing i have no idea what i am doing wrong please help the code is asp.net using vb. I have been having serious trouble passing check boxes in forms from day one both singularly and dynamically from datagrids if someone could show me some sample code of how to pass these sort of values into the component and on to the query in this way i would very much appreciate it.
Fuzzygoth
the error returned is
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.
Exception Details: System.Data.SqlClient.SqlException: INSERT statement conflicted with COLUMN FOREIGN KEY constraint 'tblStation_FK00'. The conflict occurred in database 'TealSQL', table 'tblTravelPoint', column 'travelpointID'. The statement has been terminated.
I have marked the area of the code the error is returned in the colour Violet and with a ##
The code i am using is below
## The aspx page ##
<%@ Page Language="vb" Debug="true" Trace="True" Inherits="Devotion2Motion.AdminComp" Src="../CodeBehind/AdminModule.vb" %> <!-- Binds the ActivityResortInfo.ascx user control to the page --> <%@ Register TagPrefix="UserContol" TagName="D2MHeader" Src="../UserControls/Header.ascx" %> <%@ Register TagPrefix="UserContol" TagName="D2MFooter" Src="../UserControls/Footer.ascx" %> <%@ Register TagPrefix="UserContol" TagName="TravelPointDD" Src="../UserControls/TravelPointDD.ascx" %> <script language="vb" runat="server">
Sub Page_Load()
If IsPostback = True Then
Dim aInternational As Integer
Dim Station As String = Request.Form("StationFrm") Dim Type As String = Request.Form("TypeFrm") Dim Address1 As String = Request.Form("address1Frm") Dim Address2 As String = Request.Form("address2Frm") Dim City As String = Request.Form("cityFrm") Dim International As String = Request.Form("InternationalFrm") Dim TravelPoint As Integer = Request.Form("_ctl5:dsTravelPointDD")
If IsNothing(International) Then aInternational = "0" Else aInternational = "1" End If
Dim AdminTravelPoints As New Devotion2Motion.AdminComp() ' Select the country dropdown list
AdminTravelPoints.AddStation(Station, Type, Address1, Address2, City, aInternational, TravelPoint)
End If
Dim ReadResultTable As New Devotion2Motion.AdminComp() ' Select the country dropdown list dsResultSet.DataSource = ReadResultTable.GetStationtbl() dsResultSet.DataBind()
End Sub
</script>
<!-- This UserControl Pulls in the header UserControl and the Div Tag Positions it #css reffrence is TopControl --> <Div Class="TopControl"> <UserContol:D2MHeader runat="server"/> </Div>
##the vb componet that passess to the sql query ##
Public Function AddStation(ByVal Station As String, ByVal Type As String, ByVal Address1 As String, ByVal Address2 As String, ByVal City As String, ByVal aInternational As Integer, ByVal TravelPoint As Integer) As SqlDataReader
' Create Instance of Connection and Command Object Dim myConnection As New SqlConnection(ConfigurationSettings.AppSettings("strConn")) Dim myCommand As New SqlCommand("sp_call_Station_Insert", myConnection)
' Mark the Command as a SPROC myCommand.CommandType = CommandType.StoredProcedure
' Add Parameters to SPROC Dim parameterStation As New SqlParameter("@Station", SqlDbType.NVarChar, 50) parameterStation.Value = Station myCommand.Parameters.Add(parameterStation)
' Add Parameters to SPROC Dim parameterType As New SqlParameter("@Type", SqlDbType.NVarChar, 50) parameterType.Value = Type myCommand.Parameters.Add(parameterType)
' Add Parameters to SPROC Dim parameterAddress1 As New SqlParameter("@Address1", SqlDbType.NVarChar, 50) parameterAddress1.Value = Address1 myCommand.Parameters.Add(parameterAddress1)
' Add Parameters to SPROC Dim parameterAddress2 As New SqlParameter("@Address2", SqlDbType.NVarChar, 50) parameterAddress2.Value = Address2 myCommand.Parameters.Add(parameterAddress2)
' Add Parameters to SPROC Dim parameterCity As New SqlParameter("@City", SqlDbType.NVarChar, 50) parameterCity.Value = City myCommand.Parameters.Add(parameterCity)
' Add Parameters to SPROC Dim parameteraInternational As New SqlParameter("@aInternational", SqlDbType.Int, 4) parameteraInternational.Value = aInternational myCommand.Parameters.Add(parameteraInternational)
' Add Parameters to SPROC Dim parameterTravelPoint As New SqlParameter("@TravelPoint", SqlDbType.Int, 4) parameterTravelPoint.Value = TravelPoint myCommand.Parameters.Add(parameterTravelPoint)
' Execute the command myConnection.Open()
## Dim result As SqlDataReader = myCommand.ExecuteReader(CommandBehavior.CloseConnection)
' Return the datareader Return result
End Function
## The sql stored procedure ##
CREATE PROCEDURE [dbo].[sp_call_Station_Insert] ( @Station As nVarChar(50), @Type As nVarChar(50), @address1 As nVarChar(50), @address2 As nVarChar(50), @City As nVarChar(50), @aInternational As nVarChar(50), @TravelPoint As Int ) AS
DECLARE @ConVale As nVarChar(50)
SET @ConVale = (SELECT Station FROM tblStation WHERE @Station = Station)
If @ConVale = @Station
BEGIN SELECT * FROM tblStation END ELSE BEGIN insert into tblStation (Station, Type, address1, address2, city, International, TravelPoint) values (@Station, @Type, @address1, @address2, @City, @aInternational, @TravelPoint) SELECT * FROM tblStation
I keep getting the following error message but I don't see what's wrong with my code
Server Error in '/Admin' Application.
Arithmetic overflow error converting expression to data type datetime.The statement has been terminated. Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. Exception Details: System.Data.SqlClient.SqlException: Arithmetic overflow error converting expression to data type datetime.The statement has been terminated.Source Error:
Line 147:cmdSql.Parameters.Add("@event_active","1") Line 148: Line 149:cmdSql.ExecuteNonQuery() Line 150: Line 151:pnlForm.Visible = FalseSource File: c:hostingwebhost4lifememberNYACOAadmincalendar.aspx Line: 149 Stack Trace:
I have a datetime field in a database which I am programmatically inserting values into. The field can be null or not null. However, I am having problems inserting NULL as part of my SQLCommand. The value is pulled from a text box (linked to a calendar extender) and when I select a value it is being inserted fine. If I just leave the box blank though, I want the field to be set to NULL. I have tried adding in ,DBNULL.Value, as part of my VALUES(…) string, but this throws an exception. I Have tried just inserting ‘’ but that also throws an exception (“The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value�), so I don’t know how I can insert this value when the field is blank? Can anyone shed some light please? Thanks
HiI am trying to insert value retrieved from Now() into a datetime field in my MSDE database, but I am getting the following error, and I have no idea what is going wrong.Arithmetic overflow error converting expression to data type datetime.The statement has been terminated.Here is the code I am using:Dim user As String = MyContext.User.Identity.Name.ToString Dim TimeDate As DateTime = Now() Dim status As String = "Pending"
With SqlOrders.InsertParameters .Item("UserName").DefaultValue = user .Item("OrderDate").DefaultValue = TimeDate .Item("Status").DefaultValue = status End With SqlOrders.Insert()The date is being returned in this format 23/03/2006 02:01:52, which is the same format as it should appear in the database. could anyone please tell me where I am going wrong?
Hi, I have a problem when I insert a date in a datetime field in a MSSQLServer. That's my problem: if the server is in english version, I have to insert date with this code:
DateTime.Today.ToString("MM/dd/yyyy")
instead if the server is in italian version, I have to insert date with this code:
DateTime.Today.ToString("dd/MM/yyyy")
Is there a way to insert a date in standard way, without knowing the server version?
Using Server Explorer in VB 2005, I am manually entering data in a table in a SQL Server 2005 Express database that includes a DateTime field. I have tried every conceivable format, but no matter what I try get one of these 2 errors:
1. String was not recognized as valid DateTime
2. Operand type class; text incompatible with DateTime
I have Googled this to death, but no example which involves trying to enter the data manually, say from Server Explorer.
Formats tried include all datetime formats (mmddyy, yymmdd, using dashes or slashes, enclosing in single quotes or pound signs).
I would appreciate if someone could please give me an example that I can literally insert without error.
I have a really simple query which i can't figure out why its not working. I have a table called 'ADMIN' which has a datetime field called 'date_edited'. Because the majority of records have never been edited, i have allowed null values and they are filled with 'NULL' in each record. How ever, when i try:
SELECT * FROM ADMIN WHERE date_edited = NULL
I get no records, but i can see and know i have hundreds! I know i'm doing somthing really stupid, but for life of me can't figure it out! :eek:
Hello.I've read many topics about this problem but i couldn't figure it out.I use form where user must insert 2 dates using texboxes.-One is required and other is optional.Sql 2000 is inserting either '20061105' or '2006.11.05' on insert update but select query returns 05.11.2006 on my report. Question 1.How do I insert or update dates from my form where date is entered dd.mm.yyyy to sql 2000 table?question 2. What to do if user left optional texbox date empty.I'm using SP and function with arguments (byval texbox1.text as date, byval texbox2.text as date)and parameters @date1, sqldbtype date =texbox1.text
Hi all, having a little problem with saving dates to sql databaseI've got the CreatedOn field in the table set to datetime type, but every time i try and run it i get an error kicked up Error "The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.The statement has been terminated."I've tried researching it but not been able to find something similar. Heres the code: DateTime createOn = DateTime.Now;string sSQLStatement = "INSERT INTO Index (Name, Description, Creator,CreatedOn) values ('" + name + "','" + description + "','" + userName + "','" + createOn + "')"; Any help would be much appreciated
Edited by SomeNewKid. Please post code between <code> and </code> tags.
This is probaly the easiest question you've ever read but here goes.
I have a simple checkbox value that i want to insert into the database but whatever i do it does not seem to let me.
Here is my code:
Sub AddSection_Click(Sender As Object, e As EventArgs) Dim myCommand As SqlCommand Dim insertCmd As String ' Build a SQL INSERT statement string for all the input-form ' field values. insertCmd = "insert into Customers values (@SectionName, @SectionLink, @Title, @NewWindow, @LatestNews, @Partners, @Support);" ' Initialize the SqlCommand with the new SQL string. myCommand = New SqlCommand(insertCmd, myConnection) ' Create new parameters for the SqlCommand object and ' initialize them to the input-form field values. myCommand.Parameters.Add(New SqlParameter("@SectionName", SqlDbType.nVarChar, 50)) myCommand.Parameters("@SectionName").Value = Section_name.Value
If New_window.Checked = false Then myCommand.Parameters.Add(New SqlParameter("@NewWindow", SqlDbType.bit, 1)) myCommand.Parameters("@NewWindow").Value = 0 else myCommand.Parameters.Add(New SqlParameter("@NewWindow", SqlDbType.bit, 1)) myCommand.Parameters("@NewWindow").Value = 1 End If
If Latest_news.Checked = false Then myCommand.Parameters.Add(New SqlParameter("@LatestNews", SqlDbType.bit, 1)) myCommand.Parameters("@LatestNews").Value = 0 else myCommand.Parameters.Add(New SqlParameter("@LatestNews", SqlDbType.bit, 1)) myCommand.Parameters("@LatestNews").Value = 1 End If
If Partners.Checked = false Then myCommand.Parameters.Add(New SqlParameter("@Partners", SqlDbType.bit, 1)) myCommand.Parameters("@Partners").Value = 0 else myCommand.Parameters.Add(New SqlParameter("@Partners", SqlDbType.bit, 1)) myCommand.Parameters("@Partners").Value = 1 End If
If Support.Checked = false Then myCommand.Parameters.Add(New SqlParameter("@Support", SqlDbType.bit, 1)) myCommand.Parameters("@Support").Value = 0 else myCommand.Parameters.Add(New SqlParameter("@Support", SqlDbType.bit, 1)) myCommand.Parameters("@Support").Value = 1 End If
myCommand.Connection.Open() ' Test whether the new row can be added and display the ' appropriate message box to the user. Try myCommand.ExecuteNonQuery() Message.InnerHtml = "Record Added<br>" & insertCmd Catch ex As SqlException If ex.Number = 2627 Then Message.InnerHtml = "ERROR: A record already exists with " _ & "the same primary key" Else Message.InnerHtml = "ERROR: Could not add record, please " _ & "ensure the fields are correctly filled out" Message.Style("color") = "red" End If End Try
Hi, I am working with "sql server enterprise manager". How can I insert datetime(i.e. date) value in the datetime column in my database using an sql command?
Eg. If I have a table named Table1 in my database, and this table is composed of one primary key column and one datetime column (named dateofreceipt), how should the syntax look like?
I have a text column in my db which stores more than 8000 characters. When I retrieve the values from the column in query analyzer (I have set the output buffer to 8000), it only shows me first 8000 chars only. How do I display all the text from the text field?
what i understand if if the data field is integer or money, not string, then i need to do a convert(datatype, value) in the insert but how come its still not working INSERT INTO [Product] ([Title], [Description], [Processor], [Motherboard], [Chipset], [RAM], [HDD], [OpticalDrive], [Graphics], [Sound], [Speakers], [LCD], [Keyboard], [Mouse], [Chassis], [PSU], [Price]) VALUES (@Title, @Description, @Processor, @Motherboard, @Chipset, @RAM, @HDD, @OpticalDrive, @Graphics, @Sound, @Speakers, @LCD, @Keyboard, @Mouse, @Chassis, @PSU, convert(smallmoney, @Price))
See my code below... it nicely insert in database data...
but I wish: if user of program in field "txtPozicija" try insert in database same data detail like is in "Pozicija" field in database that he be stopped and informed by message: you can not insert TWO SAME data in the "Pozicija" table.
any advice here?
I AM TRY SOLVE THIS LIKE BELOW, NO ERROR BUT NOT WORK using System; using System.Data; using System.Configuration; using System.Collections; using System.Web; using System.Web.Security; using System.Web.UI; using System.Web.UI.WebControls; using System.Web.UI.WebControls.WebParts; using System.Web.UI.HtmlControls; using System.Data.SqlClient;
if (e.CurrentStepIndex == 1) { //Register user into the database not hear because wizard have one step only // SaveDataDB(); } } protected void CustomValidator1_ServerValidate(object source, ServerValidateEventArgs args) { if (ddRegija.SelectedIndex == 0) args.IsValid = false; } protected void CustomValidatorPozicija_ServerValidate(object source, ServerValidateEventArgs args) { string ConnStr = ConfigurationManager.ConnectionStrings["croestate_dbConnectionString"].ConnectionString; SqlConnection Conn = new SqlConnection(ConnStr);
try { Conn.Open();
String sqlQuery = "SELECT Pozicija FROM PozicijaObjekta WHERE Pozicija='" + txtPozicija.TemplateControl + "'"; SqlCommand cmd = new SqlCommand(sqlQuery, Conn); SqlDataReader dr = cmd.ExecuteReader(); if (dr.Read() == true) args.IsValid = false;
Hi, I'm inserting a datetime values into sql server 2000 from c#
SQL server table details Table nameate_test columnname datatype No int date_t DateTime
C# coding SqlConnection connectionToDatabase = new SqlConnection("Data Source=.\SQLEXPRESS;Initial Catalog=testdb;Integrated Security=SSPI"); connectionToDatabase.Open(); DataTable dt1 = new DataTable(); dt1.Columns.Add("no",typeof(System.Int16)); dt1.Columns.Add("date_t", typeof(System.DateTime)); DataRow dr = dt1.NewRow(); dr["no"] = 1; dr["date_t"] = DateTime.Now; dt1.Rows.Add(dr); for(int i=0;i<dt1.Rows.Count;i++) { string str=dt1.Rows["no"].ToString(); DateTime dt=(DateTime)dt1.Rows["date_t"]; string insertQuery = "insert into date_test values(" + str + ",'" + dt + "')"; SqlCommand cmd = new SqlCommand(insertQuery, connectionToDatabase); cmd.ExecuteNonQuery(); MessageBox.Show("saved"); } When I run the above code, data is inserted into the table The value in the date_t column is 2007-07-09 22:10:11 000.The milliseconds value is always 000 only.I need the millisecond values also in date_t column. Is there any conversion needed for millisecond values?
We want to add a default date to our database tables. Looking at other database samples people use all sorts of dates to add as default date e.g. 1/1/1997 or the getdate() function. Is it good practice to set a default date and what should the default date be???? Newbie
I have this problem of inserting my query into database field. My code is as of below. The @AVERAGESCORE parameter is derived from Dim averagescore As Single = (122 * 1 + 159 * 2 + 18 * 3 + 3 * 4 + 0 * 5) / (122 + 159 + 18 + 3 + 0) and the value returned is (averagescore.toString("0.00")) However, I have error inserting the averagescore variable into a field of datatype float during the transaction. I have no problems when using non transactional sql insert methods. What could be the problem? Try Dim i As Integer For i = 0 To arraySql.Count - 1 myCommand = New SqlCommand Dim consolidatedobjitem As ConsolidatedObjItem = arraySql(i) myCommand.CommandText = sqlStr myCommand.Connection = myConnection myCommand.Transaction = myTrans With myCommand.Parameters
End With myCommand.ExecuteNonQuery() Next myTrans.Commit() myConnection.Close() Catch ex As Exception Console.Write(ex.Message) myTrans.Rollback() myConnection.Close() End Try
How Can I get Identitiy field from database while inserting new row in sql server 2005 compact edition.
Ex:
I am inserting row in a table through SqlQuery ("insert into ....") in which one of the field is of type Identity which generates number automatically. I want that number to pick up that number and used it in child table....
Can anyone help me on this!I've got more than a 1000 records in a SQL server database.The problem is that the the date field is set to varchar, and that gives a lot of trouble. (for example by sorting a table, it's a mess)How can i make sure that i will have a table with the date field set to datettime en that those 1000 records still will be in it. thanks in advance!
I have some database tables, and each one of them have a creation_date and modified_date in them. I was trying to figure out a way where when a row in one of these tables is changed using Enterprise Manager (Database -> Tables -> select table -> right click -> select all rows -> change a field in a row inside a table), is there a way apart from triggers, such that the "modified_date" column for that row get changed to 'getdate()' (rather picks up the current datetime).
I am using MSSQL 2005 but we have to work in compatibility mode with MSSQL 2000 and also we are only allowed to use 2000-compatible features, now my question:
I need to programmatically copy a database within the same server (including all stored procs, triggers and tables, indices) ...
Can anybody give me a hint what is the best way for reaching this target??
dear all i tried to connect to online mssql2000 database from enterprise manager,but i can't as i have a proxy server.how can i overcome this proxy server during connection ?
I am trying to find a solution to a thorny problem I have encountered...
I am a newbie in Sql Server 2000, but have quite a bit of experience in databases, both on PC and mainframe.
I am currently writing a program, which needs, among other things, to create Sql Server databases, to be located NOT in the default Mssql2000 directory, but in the user's home directory.
I am using an ADO connection and trying to execute it, as follows:
The text of the relevant part of the program goes like this:
Dim strCreateDatabase As String Dim MyCn As ADODB.Connection
Set MyCn = New ADODB.Connection
With MyCn .ConnectionTimeout = 25 .Provider = "sqloledb" .Properties("Data Source").Value = "MOP" .Properties("Initial Catalog").Value = "" .Properties("Integrated Security").Value = "SSPI" .Open End With
I'm running 2000 databases in a 2005 server. Can anyone tell me if there are adverse effects in doing this? As I understand it, the 2005 performance benefits are available to databases running as 2000(Ver80) But some of the new futures may not be available.. And any documentation from Microsoft/white papapers regarding this subjects are appreciated..
Hello, I am using datareader to retrive the varchar (max) value from the database. But it is reteriving only 8000 why ? I am not using normal varchar datatype. Below is the sample code. Convert.ToString(_oDr["MyVar"]) where _oDr is the datareader MyVar is defined of type varchar (max) in the sql 2005 database. Any help will be appreciated.
Currently we have a variety of SQL 2000 (and 2005) database servers, we are having issues with the maintenance plan of a few SQL2000 boxes where they no longer have enough hard disk space to do a full index-rebuild on the system.
Now we want to re-build the databases indexes approximately once a week, or maybe a little less often, in the past this has worked fine with maintenance plans.
However, we now have issues because we have some databases in offline mode, and we are quite low on disk space with no plans for hardware upgrades anytime soon. The temporary solution is to turn the index rebuilds off.
I have been working on a script that will:
Cycle through each database and within that database:
Go through each table Run a DBCC DBREINDEX on the table Move on to the next table Once the reindexing of one database is complete IF the database is not in simple mode
Backup the transaction log Run a DBCC SHRINKDATABASE with the required amount of free space Go to the next database until all are complete.The logic is quite simple but so far this has not worked, it would appear something is locking the transaction log until the script exits.
Now the script works fine excluding the shrinkdatabase, I always get: DBCC execution completed. If DBCC printed error messages, contact your system administrator. [SQLSTATE 01000] Shrinking database: inf_dev target percentage: 10 at: Aug 2 2007 5:33PM [SQLSTATE 01000] Cannot shrink log file 2 (INF_PROD_Log) because all logical log files are in use. [SQLSTATE 01000]
Where I'm indexing the INF_Prod database. A DBCC LOGINFO shows something along the lines of:
Clearly there is something in the log file towards the end. However, I don't know why this is happening as I'm running the script in the master database and I've backed up the transaction log of the database I'm working on. I've tried doing Full backup + Transaction log + Shrink, it fails. I've tried waiting 10minutes in the script + shrink, it also fails.
However, if I open a query analyzer and do a backup log, then a shrink it works perfectly every time. However in the script it always fails no matter what I do.
