DateTime.Now, An Allowed Null Field, And My Insert Function From My Dataset
Jun 6, 2008
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()),
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.
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
Hey im trying to store a category name and the date into a database. For some reason i keep getting this error Implicit conversion from data type datetime to int is not allowed. Use the CONVERT function to run this query. This error is the error im getting back from the database. the datetime field in the database is a datatype (DateTime) so what exactly is going on ?protected void InsertNewCat_Click(object sender, EventArgs e) { string insertSql = "INSERT into Category (CategoryName,Date) VALUES (@Category, @Date)"; string conString = WebConfigurationManager.ConnectionStrings["ProCo"].ConnectionString; SqlConnection con = new SqlConnection(conString); SqlCommand cmd = new SqlCommand(insertSql, con); cmd.Parameters.AddWithValue("@Category", NewCat.Text); cmd.Parameters.AddWithValue("@Date",DateTime.Now);
Im getting this error below when I try to do an insert into my database. I have no idea why this is happening, please help!this is my sqldatasource:<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:myConnectionString %>" DeleteCommand="DELETE FROM [tblDiaryEntries] WHERE [DiaryEntryID] = @DiaryEntryID" InsertCommand="INSERT INTO [tblDiaryEntries] ([DiaryEntry], [Subject], [EntryDate], [UserId]) VALUES (@DiaryEntry, @Subject, @EntryDate, @UserId)" SelectCommand="SELECT [DiaryEntry], [Subject], [EntryDate], [DiaryEntryID], [UserId] FROM [tblDiaryEntries]" UpdateCommand="UPDATE [tblDiaryEntries] SET [DiaryEntry] = @DiaryEntry, [Subject] = @Subject, [EntryDate] = @EntryDate, [UserId] = @UserId WHERE [DiaryEntryID] = @DiaryEntryID"> <DeleteParameters> <asp:Parameter Name="DiaryEntryID" Type="Int32" /> </DeleteParameters> <UpdateParameters> <asp:Parameter Name="DiaryEntry" Type="String" /> <asp:Parameter Name="Subject" Type="String" /> <asp:Parameter Name="EntryDate" Type="String" /> <asp:Parameter Name="UserId" Type="Object" /> <asp:Parameter Name="DiaryEntryID" Type="Int32" /> </UpdateParameters> <InsertParameters> <asp:Parameter Name="DiaryEntry" Type="String" /> <asp:Parameter Name="Subject" Type="String" /> <asp:Parameter Name="EntryDate" Type="String" /> <asp:Parameter Name="UserId" Type="Object" /> </InsertParameters> </asp:SqlDataSource> Am I doing something wrong? Server Error in '/mine' Application.
Implicit conversion from data type sql_variant to uniqueidentifier is not allowed. Use the CONVERT function to run this query. 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: Implicit conversion from data type sql_variant to uniqueidentifier is not allowed. Use the CONVERT function to run this query.Source Error:
An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below. 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
I have defined fields in a table as datetime fields and allow nulls. SQL 6.5 is placing 1-Jan-1900 in all of these fields causing me to have to filter out these dates when retrieving a recordset. How do I get rid of this?
Hi there ; This Problem is goin to make me crazy! I've got a table with couple of fields, 2 of them are DateTime, in Sql Express 05 . I used asp.net 2.0 to insert datetime values, directly, using sth like DateTime.Now.ToString() . i use 2 selects as follows : 1)select * from X where Date1='8/9/2006 11:17:41 AM' 2)select * from X where Date2='8/9/2006 11:17:41 AM' #1 is OK, but the second one returns a row with all columns set to NULL . (X is the name of the table) Thanks in advance
How to replace DateTime field with null value in SQL 2005 server I create a stored procedure aa, It works well, but sometimes I hope to replace CreateDate field with null value,I don't know how to doIt seems that datetime type is not null value create aa @CreateDate DatetimeAsUpdate cw set CreateDate=@CreateDate
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:
There was a similar question to mine a few time ago about conditional execution, but I'm having some problems with it to which I couldn't find any answer, could anyone help?
My condition is checking if a DateTime from a DB table is null. I use the Execute SQL Task to retrieve the record (SELECT TOP 1 * FROM myTable WHERE conditions = TRUE), then I map 2 fields to their respective global variables.
Then I created the green arrow connector and used a precedence expression based on sucess. I used the expression IsNull(@variable). For some reason, even if the @variable comes as null - I checked the info in the DB - it still comes out as '1999-11-30'
I've looked around and the IsNull method can be used to also change the value in the event of it being null, could it be changing my variables value to this even if I didn't pass that value as a parameter to the function? If so, how else can I test it for null? I've tried "== null", "= null" and even ".equals(null)" - which I know is absurd, but I'm running out of options here...
Could you enlighten me, please??? Thanks in advance!
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
Hello,I've been searching the web for quite some time to resolve the problemof "1/1/1900" returning in a datetime field in SQL that resulted from ablank (not NULL) value being passed to it through an ASP page.The solution is that a NULL value needs to passed to SQL from ASP.Thats fine...I understand the why the problem is happening and thesolution around it. HOWEVER, I can't seem to get the proper syntax towork in the ASP page. It seems no matter what I try the "1/1/1900"still results. Below are a few variations of the code that I havetried, with the key part being the first section. Does anyone have anysuggestions?!?!?______________cDateClosed = ""If(Request.Form("dateClosed")= "") ThencDateClosed = (NULL)end ifsql="UPDATE rfa SET "&_"dateClosed='"& cDateClosed &"', "&_"where rfaId='"& Request.Form("RFAID")&"'"_____________________________cDateClosed = ""If(Request.Form("dateClosed") <> "") ThencDateClosed = (NULL)end ifsql="UPDATE rfa SET "&_"dateClosed='"& cDateClosed &"', "&_"where rfaId='"& Request.Form("RFAID")&"'"_____________________________cDateClosed = ""If(Request.Form("dateClosed")= "") ThencDateClosed = NULLend ifsql="UPDATE rfa SET "&_"dateClosed='"& cDateClosed &"', "&_"where rfaId='"& Request.Form("RFAID")&"'"_______________Thanks in advance!!!!
=CountDistinct(IIF(Fields!Released_DT.Value = Fields!Date2.Value, Fields!Name.Value, Nothing)) Released_DT = a date - 09/03/2015 or 09/02/2015 Date2 = returns another date value in this case 09/03/2015
What I'm trying to do is: count distinct number of people (Fields!Name.Value) if the Relased_DT = Date2.My IIF statement is returning a zero value.
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 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?
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 need to insert a null valvue when the user does not impute any text. here is my code If cell_phone.Text = "" Then cell_phone.Text = "dbnull.value" End IfDim mySqlConnection As New SqlConnection mySqlConnection.ConnectionString = ConfigurationManager.ConnectionStrings("Call_ListConnectionString").ConnectionString Dim strSQL As String = "INSERT INTO Employees(Clock_ID, Last_Name, First_Name, Cell_Phone, Home_Phone, Work_Phone, Email, Primary_Day_Phone, Primary_Night_Phone, Blackberry_Number) VALUES ('" & clock_id.Text & "','" & last_name.Text & "','" & first_name.Text & "','" & work_phone.Text & "','" & home_phone.Text & "','" & cell_phone.Text & "','" & email.Text & "','" & prime_day_lst.SelectedValue & "','" & prime_night_lst.SelectedValue & "','" & blackberry.Text & "')"Dim mySqlCommand As New SqlCommand(strSQL, mySqlConnection) mySqlConnection.Open() mySqlCommand.ExecuteNonQuery() mySqlConnection.Close() THanks Mike
hi all!I have a task, for example, to create a record for bill. I have table which represents this bill entity (Bill_ID, Amount, CreationDate, ExposureDate, PaymentDate)In table definition date fields allow null. I would like to create bill, which means insert record: (new_bill_id, 1000, 2007.12.11, null, null) But it couses exception. Smth like: System.Data.SqlTypes.SqlTypeException, date should be not null. How could I do it?Please advice!
Hello everyone, I am using DTS to transform data from foxpro.when it meets the date of NULL,it fails and says"Insert Error , column 4( 'Coloumn_name ',DBTYPE_DBTIMESTAMP), Status 6 data overflow ". And then i try to open the DTS Transform Data task and do a preview of the data from the 'Source' side.The Null values are treated as "1899-12-30". After reading something ,i try to write an ActiveX scripting Task to solve this problem using the function of "IsDate()",but it fails with exceptions. Now I don't know what to do.could anyone give me some suggestion?Anything will be appreciated!ThankX in advance!
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 have a function that updates a table in sql server If a field is left blank I want the database field set to Null. I use:sqlCmd.Parameter.Add("@somefield, someintvalue) to pass all my values to the database. if someintvalue is null I want to set @somefield to dbnull How can I do this? I get errors if I submit a null integer when there is a foreign key constraint. What is the best way to handle this? should I just make all parameters objects? so that I can set them to DBNull.Value? Also on a side note, when you are populating fields from a datatable, is there a better way to set the values (i.e. of a textbox) than cheking for DBNull before assigning?
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.
string fileName = "d:\shiporder.xml"; DataSet dataSet = new DataSet(); dataSet.ReadXML(fileName); //connection string string cmd = "INSERT INTO Orders (OrderID, OrderPerson) VALUES (dataSet.Tables[0].Row[0][0].ToString(),dataSet.Tables[0].Row[0][1].ToString); SqlConnection con = new SqlConnection(conection string); SqlCommand mycmd = new SqlCommand(cmd,con); con.Open(); mycmd.ExecuteNonQuery(); con.Close(); it gives me this error dataSet.Tables its not permitted in this context. valid expression are constants, constant expression, . Columns name are not permitted
Data from as400 imports into SQL with blank fields which is the way as400 outputs records. How can you insert previous record of data null or blank field. ex:
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?
I am using SQL Server 2000. I have created a Store Procedure to insert information, 4 of the fields are date types. I am using OleDb Data Provider (System.Data.OleDb) namespace.
The dates are filled by a form (web form) on submit event, I created a class that has functions to create my OleDb Parameters. I add the parameters to the command and execute it through a SQL Server Stored Procedure. In the event that I must have coded something wrong, I tested in the SQL Query Analyzer and the IN parameters for the date I used the getDate() method. This is where I know it is OleDb and SQL Server Parameters.
What Converstion Format should I use in the SP for the date? OleDb.date are doubles from some date in 1979 or something or another. So I used an OleDbDataType.DBDate. It seems that when the Stored Procedure uses the IN Parameters dates provided by OleDbDataType.Date or DBDate, that it doesn't like the int format. I am guessing that I am not converting the date in the parameters in the Insert of the Store Procedure... this is basically what I have...
Function to add parameters and execute SP
private int _startdate = DateTime.Now; private int _finishdate = DateTime.Now.AddDays(30);
CREATE PROCEDURE myInsert @StartDate datetime, @FinishDate datetime AS
INSERT INTO myTable (STARTDATE, FINISHDATE) VALUES (Convert(datetime, @StartDate), Convert(datetime, @FinishDate))
Please keep in mind that I am using System.Data.OleDb namespace..... please don't tell me to use SQLClient, it seems since I've been so adaptive of using OleDb, that it sould work just as well. I am way too far into this to change my Provider to SQL Client, but I promise myself that the next project (if using SQL Server, I will be using SQLClient and I will keep using OleDb for Oracle.) *sigh*
I want to create a function that searches for allowed characters within a table range (that contains the allowed characters) and replace any characters outside this range with a space.
For example -
'Bill123?', 'Jones12.z-' 'John&12/', 'QWERT123&4'
Wanted results – the single quotes are there to show the space for the replaced characters.
'Bill123 ' 'Jones12.z ' 'John&12 ' 'QWERT123 4'
Example SQL data
CREATE TABLE [Common].[AllowedCharacters] ( [Character] [varchar](1) NOT NULL, [Replacement] [varchar](10) NULL, [AlwaysInclude] [bit] NOT NULL) GO SET ANSI_PADDING OFF
[code]....
The function will wrap around the column names and I know it can be done without a table validate the characters but it must be done this way.
If I have a table with 1 or more Nullable fields and I want to make sure that when an INSERT or UPDATE occurs and one or more of these fields are left to NULL either explicitly or implicitly is there I can set these to non-null values without interfering with the INSERT or UPDATE in as far as the other fields in the table?
EXAMPLE:
CREATE TABLE dbo.MYTABLE( ID NUMERIC(18,0) IDENTITY(1,1) NOT NULL, FirstName VARCHAR(50) NULL, LastName VARCHAR(50) NULL,
[Code] ....
If an INSERT looks like any of the following what can I do to change the NULL being assigned to DateAdded to a real date, preferable the value of GetDate() at the time of the insert? I've heard of INSTEAD of Triggers but I'm not trying tto over rise the entire INSERT or update just the on (maybe 2) fields that are being left as null or explicitly set to null. The same would apply for any UPDATE where DateModified is not specified or explicitly set to NULL. I would want to change it so that DateModified is not null on any UPDATE.
INSERT INTO dbo.MYTABLE( FirstName, LastName, DateAdded) VALUES('John','Smith',NULL)
INSERT INTO dbo.MYTABLE( FirstName, LastName) VALUES('John','Smith')
INSERT INTO dbo.MYTABLE( FirstName, LastName, DateAdded) SELECT FirstName, LastName, NULL FROM MYOTHERTABLE
