Hello there,
I got a little code there are inserting a record into my msSQL database..
But i cant insert the datetime, for one reason?
The problem is in line 7 () where i want to insert DateTime.Now
1 Protected Sub SendPmTilAfviste(ByVal modtager As String, ByVal festID As String)
2 ' Connection
3 Dim conn As SqlConnection = New SqlConnection("Data Source=.SQLEXPRESS;AttachDbFilename=|DataDirectory|ASPNETDB.MDF;Integrated Security=True;User Instance=True")
4 conn.Open()
5
6 ' SQL-kommandoen
7 Dim cmd As SqlCommand = New SqlCommand("INSERT INTO marcisoft_PMsystem(laest,fra,modtager,sendt,overskrift,besked) VALUES('<b>Ny!</b>','webmaster1','" + modtager + "','" + DateTime.Now + "','A headline','text text the id " + festID + "')", conn)
8 cmd.ExecuteNonQuery()
9
10 conn.Close()
11 conn = Nothing
12
13 End Sub
If i using with ' around
It show up with a error, out of range..
And if i dont, it show up with another error, problem near 02
(02 could be the clock or the month, think clock)
Hi everyone,I need help in inserting a date time string to sql sever. For example, how do you insert textbox1.text="2006-08-30 09:00:00" into a datatable column names starttime (type datetime) in sql sever? How do I covert the format of this string before I do the insert?Thanks.a123.
Hi everyone,How do you insert this string value lable1.text="2006-08-30 09:00:00" into a data column like startdate (type: datetime) in sql sever?How do I convert this string value before I insert it into sql sever? Thank you very much.a123.
Does anyone have a simple way of inserting a NULL value into sql 2000 datetime. I'm using vb.net. All I want is if the user does not enter a date in a textbox to to send a NULL value to the DB instead of having the db enter the default value as 1/1/1900. Thank you
I have very little C# experience and I am in over my head here. This script will insert all data into the data base after I have removed all refference to the date.
I know I have to change the datatype in code, but I have tried everything I can think of.
Any suggestion would be greatly appreciated.
The .aspx.cs file is below:
using System; using System.Configuration; using System.Collections; using System.ComponentModel; using System.Data; using System.Data.SqlClient; using System.Drawing; using System.Web; using System.IO; using System.Web.SessionState; using System.Web.UI; using System.Web.UI.WebControls; using System.Web.UI.HtmlControls;
private int SaveToDB(string newsTitle, string newsBody, string d, byte[] imgbin, string imgcontenttype) { //use the web.config to store the connection string SqlConnection connection = new SqlConnection(ConfigurationSettings.AppSettings["connString"]);
SqlCommand command = new SqlCommand( "INSERT INTO Cover (cover_title,cover_body,cover_date,cover_image,cover_imgcontenttype) VALUES ( @news_title,@news_body,@news_date,@img_data,@img_contenttype )", connection );
I'm still having a problem inserting date fields into sql server.
I don't understand how it accepts datetime.
I have all of my date columns defined with datetime format and all of the dates are coming out as the default of: 01/01/1900.
I tried to insert the data as string and sql server doesn't understand that format. Here's some of the code:
We're going from flat VSAM files to an sql server database. This is one huge sql insert statement with about 75 fields being loaded into a table so I'll only post one the date fields. Here's where I call the String functions from:
First, I have to uncomp the field from binary to String:
(lsDet1 is a concatenated String of the SQL Values to be inserted)
And Here are the two functions: The date field is coming in like: 1991112 where if the first character is a 1, the year is 1900 and if the first character is a 0, the year is 2000. I get correct fields in my message box like 1996/12/31 but then I don't know what sql server does to it in datetime format. When I check the database table it looks like: 01/12/1900
Maybe there is something wrong with my Convert_Date function;
Public Function CompToStr(aCompdata() As Byte) As String 'This is one way in which you can unpack a comp field. As I mentioned, 'you might be better off designing a flexible class to do the 'conversions. At minimum, this function should be expanded to 'accept a data picture as a param (decimal placement and so on).
Dim lsRtnStr As String Dim lsHoldStr As String Dim llCount As Long
For llCount = 1 To (UBound(aCompdata) + 1) Step 1 'loop thru the passed array. lsHoldStr = Hex(aCompdata(llCount - 1)) 'Convert the byte to a Hex string. If Len(Trim$(lsHoldStr)) = 1 Then 'if the highorder nibble was 0 lsHoldStr = "0" & Trim$(lsHoldStr) 'pad it with a leading zero. End If lsRtnStr = lsRtnStr & lsHoldStr 'Concat it to the return string. lsHoldStr = "" 'clear the var for the next pass. Next lsRtnStr = Replace$(lsRtnStr, "C", " ") 'Positive sign replacement. lsRtnStr = Replace$(lsRtnStr, "D", "-") 'Negative sign replacement. lsRtnStr = Replace$(lsRtnStr, "F", " ") 'Unsigned - implicit positive. lsRtnStr = Trim$(lsRtnStr)
llCount = 0 llCount = InStr(1, lsRtnStr, "-")
If llCount > 0 Then lsRtnStr = Right$(lsRtnStr, 1) & Left$(lsRtnStr, (Len(lsRtnStr) - 1)) End If CompToStr = lsRtnStr 'Return the hex string. End Function __________________________________________________ ___________________
Public Function Convert_Date(ByRef ls_sdate As String) As String
'incoming date Dim ls_scent , ls_smonth, ls_sday, ls_syear As String
How do i insert this string "23:58:44.000 UTC Wed May 11 2005" into thedatabase with datetime data format...I tried using:insert into table (startime) values ('23:58:44.000 UTC Wed May 112005')but got Syntax error converting datetime from character string error.So how do i do it??? Thx.
Hello, What is the query for inserting a data and time value for sql ce? and how can i select the rows that are between 2 date values? have tried everything but cant make it work.. thnx in advance. by the way i am using sql mobile 2005 and vs2005..
the column type of stime and etime in Dabase both are datetime -------------------------------------------------------------------------------------------------- public DateTime loginTime { get { //got user enter page's time return DateTime.Now; } }
//record user's using time when user click exit button protected void Button1_Click(object sender, EventArgs e) { int Member_No = Int32.Parse(cls_login2.GetCurrent_login_Member_no(this.Page)); int Lesson_No = Int32.Parse(Lesson_no); int Prestep = 1; string RemoteIp = Request.ServerVariables["REMOTE_ADDR"].ToString(); string sqlConn = "Insert into pro_Study_log (Member_no,Lesson_no,Study_log_stepid,Study_log_stime,Study_log_etime,Study_log_ip) "; sqlConn += "values(" + Member_No + "," + Lesson_No + "," + Prestep + "," + loginTime + ", getdate(), '" + RemoteIp + "')"; SqlConnection connLog = new SqlConnection(StrConn); SqlCommand cmdLog = new SqlCommand(sqlConn, connLog); cmdLog.CommandType = CommandType.Text; connLog.Open(); cmdLog.ExecuteNonQuery(); connLog.Close(); ----------------------- and then I got error message on column Study_log_stime I also trid set the dateimt tostring , still doesn't work..
I don't know where I did wrong... pleae help many thanks
I keep trying to insert a Value in a field called Category and DateTIme in a field called date but I keep getting this error The datetime field in the database is a data type DateTime. I converted the varible into a ToString, I put it in a Convert.In32() method, I even set the varible to a DateTime datatype. doesnt work. whats wrong ? Server Error in '/WebSite1' Application.
Compilation Error Description: An error occurred during the compilation of a resource required to service this request. Please review the following specific error details and modify your source code appropriately. Compiler Error Message: CS1502: The best overloaded method match for 'System.Data.SqlClient.SqlParameterCollection.AddWithValue(string, object)' has some invalid argumentsSource Error:
Line 33: cmd.Parameters.AddWithValue("@Category", NewCat.Text); Line 34: DateTime Date = DateTime.Now; Line 35: cmd.Parameters.AddWithValue("@Date",Date.ToString); Line 36: Line 37: trySource File: c:Documents and SettingsCompaq_OwnerMy DocumentsVisual Studio 2008WebSitesWebSite1AdminCat.aspx.cs Line: 35 Show Detailed Compiler Output:
I am doing a temporary retro-upgrade right now. So, I know this isn't exactly in the scope of ASP.Net. Ordinally my posts are. However, I need a VBScript example of how to insert the Date only into the DateTime field of an SQL 2000 Server. By default, if you try to, the server automatically adds the date "1/1/1900". Can anyone help me please?
Hi I'm using DetailView and I have a text box which show the date. I have formated the date as shortDate {0:d} format. I want to insert/update null if that text box is empty. I have tried putting null value in my Update statement in sqlDataSource. And I'm getting error saying can't convert varchar to smalldatetime. If I don't set null value as above, some large date (01/01/2033) has been inserted in my database. Can anyone help me? Moe
I would like to be able to insert a time value only into a SQL 7 table colum which has been set as a datetime datatype. When I insert '12:34:44 PM' into the colum it actually inserts '1900-1-1 12:34:44 PM'. An Access table will allow you to insert the time value without adding the '1/1/1900' date value. Can this be done in SQL7?
I am trying to Insert or Update a record in MSSQL with a datetime variable which is modified using the DateAdd function.
Basically, I have a table of Coupons which need to expire 'x' days in the future. When I use GetDate() for the Issue Date, I have no problems. But then, when I use DateAdd to return a date in the future, I can not Insert or Update this result into the record.
I get various errors having to do with type mismatch or function not found, etc.
In my C# code I have a Class property that takes the value DateTime.Min upon initialisation, but when I try to insert this into the database column (yes, it is DateTime data type :)) I get an 'Unexpected Error' from SQL Server Mobile.
How do i insert the following information(Membership Username & Date Time) to my insert command in a SQLDatasource?<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:DMC_dbconn %>" DeleteCommand="DELETE FROM [comment] WHERE [commentno] = @commentno" InsertCommand="INSERT INTO [comment] ([customerno], [comment], [commentdate], [category]) VALUES (Membership.GetUser, @comment, DateTime.Now, @category)" SelectCommand="SELECT * FROM [comment]" UpdateCommand="UPDATE [comment] SET [customerno] = @customerno, [comment] = @comment, [commentdate] = @commentdate, [category] = @category WHERE [commentno] = @commentno">
Short version: The best/fastest way to load large amounts of data from a comma delimited text file into an SQL Server table. Where the text file contains date fields in ccyy/mm/dd format and the SQL Server table defines those fields as datetime data types.
Details: When I attempt to load files (using either bcp or BULK INSERT) containing datetime data the load process errors because the datetime fields in my text file are in ccyy/mm/dd format and the default format for SQL Server is mm/dd/yy. I have been unable to change the default format by using the SET DATEFORMAT statement (apparently the SET DATEFORMAT statement will not work for bcp because bcp runs outside of the SQL Server session???). The only alternatives that I have come up with are: 1) Change the format of date fields in the text file from ccyy/mm/dd to mm/dd/ccyy. 2) Create a temporary table that defines the date fields as a char(n) datatype. Then load the data into the temp table. Then SET the DATEFORMAT to ccyy/mm/dd. Then copy the temp table into the permanent table (the permanent table using datetime data types).
Both of these alternatives would require additional processing time. Since this is a process that loads large amounts of data on a monthly (soon to be weekly) basis, speed is of the essence.
however, it fails when i try to insert the value 0000-00-00 00:00:00. ie., the following insert statement fails
INSERT INTO TEST VALUES('0000-00-00 00:00:00')
The error thrown is,
Server Msg 242, Level 16, State 3, Line 1 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.
BULK INSERT itis.taxonomic_units FROM '<dir path to input file>/taxonomic_units.txt' WITH ( FIELDTERMINATOR = '|', ROWTERMINATOR = '|', KEEPIDENTITY, KEEPNULLS
)
Here is a sample of a row that I get an error when it is processed through the above BULK INSERT statement: 50||Bacteria||||||||invalid||No review; untreated NODC data|unknown|unknown||1996-06-13 14:51:08.0||||1|10|07/29/1996|| The error is:
Server: Msg 4864, Level 16, State 1, Line 1Bulk insert data conversion error (type mismatch) for row 1, column 17 (initial_time_stamp).
Just to make things easier on anyone who tries to help me solve this problem, the field that causes my Bulk insert statement to choke contains the data: "1996-06-13 14:51:08.0". Why is this happening? Any thoughts on how to solve it? I have been scouring help articles all day with no resolution to this problem.
I got a few *.csv files which I have to import in a table. The Insert looks like this:
bulk insert actocashdb.dbo.MyTable from 'c:UpdatesPLUArtikel_01.csv' with ( fieldterminator = ';', DATAFILETYPE = 'char', tablock, codepage = 'ACP' )
There is a field which contains a Date and Time in US-Format (e.g. 08/15/2007 03:15:00 PM). Some of my SQL - Servers (in fact they are just MSDE) are configured as German language. So, when I try to Insert the File I get a Type Mismatch Error, because the MSDE awaits the Date in German Format (e.g. 15.08.2007 15:15:00). Are there any options somewhere so I can Insert this Date? NOTE: It is not possible to reconfigure the MSDE to English.
I’m looking for feedback on the Best/Right way to Insert nulls into SQL dateTime field in SQL DB from a web UI textbox.
Option 1: Presently implemented: Dim dtFollowUpDate = IIf(dtDateFollowUp.Text = "", System.Data.SqlTypes.SqlDateTime.Null, dtDateFollowUp.Text) Although ithis does what is needed it generates the following inner exception
ParamValue {System.Data.SqlTypes.SqlDateTime} Object[System.Data.SqlTypes.SqlDateTime] {System.Data.SqlTypes.SqlDateTime} System.Data.SqlTypes.SqlDateTimeDayTicks <error: an exception of type: {System.Data.SqlTypes.SqlNullValueException} occurred> Integer IsNull True Boolean
Option 2: Dim dtFollowUpDate = IIf(dtDateFollowUp.Text = "", System.DBNull.Value, dtDateFollowUp.Text) No exceptions, no problems that I have seen yet.
Option 3: Dim dtNull As System.Data.SqlTypes.INullable Dim dtFollowUpDate = IIf(dtDateFollowUp.Text = "", dtNull, dtDateFollowUp.Text) No exceptions, no problems that I have seen yet.
I'm importing data form an Excel file to a Sql Server Database. Some of the data imported represents time as a double type so i convert the times into DateTime to be inserted into the database. The time values that aren't available in the Excel file are 0.. so what i want to do is insert null into the database for all the values that are 0 in the excel file... How do i do that based on this code i have so far:protected void ButtonImport_Click(object sender, EventArgs e){PanelUpload.Visible = false;PanelView.Visible = false;PanelImport.Visible = true;LabelImport.Text = "";OleDbCommand objCommand = new OleDbCommand();objCommand = ExcelConnection(); OleDbDataReader reader;reader = objCommand.ExecuteReader(); while (reader.Read()){DateTime? in_1 = null;DateTime? out_1 = null;DateTime? in_2 = null;DateTime? out_2 = null; int emp_id = Convert.ToInt32(reader["emp_id"]);DateTime date_entry = Convert.ToDateTime(reader["date_entry"]);if (Convert.ToDouble(reader["in_1"]) != 0)in_1 = ConvertDoubleToDateTime((double)reader["in_1"]);if (Convert.ToDouble(reader["out_1"]) != 0)out_1 = ConvertDoubleToDateTime((double)reader["out_1"]);if (Convert.ToDouble(reader["in_2"]) != 0)in_2 = ConvertDoubleToDateTime((double)reader["in_2"]);if (Convert.ToDouble(reader["out_2"]) != 0)out_2 = ConvertDoubleToDateTime((double)reader["out_2"]); ImportIntoAttendance(emp_id, date_entry, in_1, out_1, in_2, out_2);} reader.Close();}protected void ImportIntoAttendance(int emp_id, DateTime date_entry, DateTime? in_1, DateTime? out_1, DateTime? in_2, DateTime? out_2){ SqlDataSource AttendanceDataSource = new SqlDataSource();AttendanceDataSource.ConnectionString = ConfigurationManager.ConnectionStrings["SalariesConnectionString1"].ToString();AttendanceDataSource.InsertCommandType = SqlDataSourceCommandType.Text;AttendanceDataSource.InsertCommand = "INSERT INTO Attendance (emp_id, date_entry, in_1, out_1, in_2, out_2) " +"VALUES ('" + emp_id + "', '" + date_entry + "', '" + in_1 + "', '" + out_1 + "', " +"'" + in_2 + "', '" + out_2 + "')"; int rowsAffected = 0;try{rowsAffected = AttendanceDataSource.Insert();}catch(Exception ex){LabelImport.Text += "<font color=red>" + ex + "</font><br />";} }private DateTime ConvertDoubleToDateTime(double dbTime){string[] SplitTime = dbTime.ToString().Split('.');string hours = SplitTime[0];string minutes = String.Empty;string time = String.Empty; if (dbTime.ToString().IndexOf('.') != -1){if (SplitTime[1].Length >= 1){if (SplitTime[1].Length == 1)minutes = Convert.ToString(Convert.ToDouble(SplitTime[1]) * 10);else if (SplitTime[1].Length > 1)minutes = SplitTime[1];}}elseminutes = "00";time = hours + ":" + minutes;return Convert.ToDateTime(time);}
I have a table adapter for one of my SQL2005 tables, and in two different fields I accept a date time. Now 99% of the times, new rows to this table will be filled out using DateTIme.Now(), as a Time Stamp is what I'm going for.
Here is the line of code in question...cops_current_data_adapter.Insert(ProductOrder, Convert.ToInt16(Session["StationId"].ToString()), PartNumber, DateTime.Now, DateTime.Now, Convert.ToInt16(qty), 0); The second DateTime.Now is the one that can be null, and it's throwing a formatting error everytime I try and drop it in there. It's a FormatException, and there's not much more to the example except unhelpful tips like be careful when conveting a string to a dateTime, which I'm not doing. Needless to say for the code to compile, and then throw a Format error at runtime is a bit frustraiting. Any suggestions would be most appreciated
Is there a way to adjust a date and time depending on the users locale before inserting into the database?
Users login in and their country of residence is stored in the database on signup. My server is in the US and I want to reset part of the users db record when it's the end of the day in their country so I need to enter the date and time into the db plus or minus the time difference, is there a function written to do this already or would i need to write my own?
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?
