I have a table, named systems, with fields as follows.
field datatype
___________________
system varchar(50)
modelID int
.... ....
... .... etc...
The modelID field is related to the Models table. Therefore, the value it expects is a number that already exists in a Models.modelID field.
In the systems table, I allow nulls for the model ID field. If I edit the table directly through SQL front end it allows me to enter a system name for example and leave modelID value as NULL.
My problem occurs when I try inserting into the table from ASP. Whether I leave the value I'm passing to the stored procedure for insert blank or NULL it gives me problems.
Is this a typical issue that can be resolved with a simple property change?
Please help me resolve this, I need to have the ability to leave certain fields empty even though they are related to other tables. Is that possible?
I need to pass in null/blank value in the date field or declare the field as string and convert date back to string.
I tried the 2nd option but I am having trouble converting the two digits of the recordset (rs_get_msp_info(2), 1, 2))) into a four digit yr. But it will only the yr in two digits. The mfg_start_date is delcared as a string variable
option 1 I will have to declare the mfg_start_date as date but I need to send in a blank value for this variable in the stored procedure. It won't accept a null or blank value.
Hello all, Can some one help me with this issue. It seems to be a common question and I have tried a lot of options but nothing seems to work for me. I have a text box and this is linked to ajax calendar extender. They get a date and I am inserting/updating the date in a sql 2000 database using a stored procdure. This is the code I use to grab the data from my web form If txtTrimsEntryDate.Text = String.Empty Then _oTrim.TrimsEntryDate = System.Data.SqlTypes.SqlDateTime.Null Else_oTrim.TrimsEntryDate = CDate(txtTrimsEntryDate.Text) End If If txtEstCompletion.Text = String.Empty Then _oTrim.EstCompletionDate = System.Data.SqlTypes.SqlDateTime.Null Else_oTrim.EstCompletionDate = CDate(txtEstCompletion.Text) End If If txtProjectClosedDate.Text = String.Empty Then _oTrim.CloseDate = System.Data.SqlTypes.SqlDateTime.Null Else_oTrim.CloseDate = CDate(txtProjectClosedDate.Text) End If _oTrim.InsertOrUpdateAProject()
This is the code for the stored Procedure Dim oSql As clsSqlServer = Nothing oSql = New clsSqlServer(AppSettings(_dbName))oSql.AddParameter("@projTrimsEntryDate ", _trimsEntryDate, SQLDataType.SQLDateTime, ParameterDirection.Input) oSql.AddParameter("@projStartDate", _startDate, SQLDataType.SQLDateTime, ParameterDirection.Input)oSql.AddParameter("@estCompletionDate", _estCompletionDate, SQLDataType.SQLDateTime, ParameterDirection.Input) oSql.AddParameter("@closeDate", _closeDate, SQLDataType.SQLDateTime, ParameterDirection.Input) Now when it inserts or updates if the value is null then it inserts "01/01/1900" in the database. I want the value to be Null in database. Is there a way to do this.
Is there any way of excluding an entire row if a particular field contains a null value? Even if other fields in the row aren't null... This is in SQL Server 2005
I would like to force a column to be null all the time. I cannot alter the table structure or alter the code that inserts data.
create table dbo.tblCustomer ( CID int IDENTITY(1,1) not null, Fnamevarchar(20) not null, Lnamevarchar(20) not null, Extravarchar(20) null
[Code] ....
So when this is executed the field Extra is always NULL
INSERT INTO tblCustomer (Fname, Lname, Extra) VALUES ('bob', 'smith', 'ignore'), ('jane', 'doe', 'empty') update dbo.tblCustomer set Extra = 'something'
If I've understood After triggers correctly the data will be written and the trigger will fire and overwrite. To avoid 2 writes
I could create an INSTEAD OF trigger
CREATE TRIGGER TR_I_Customer ON tblCustomer INSTEAD OF INSERT AS BEGIN SET NOCOUNT ON INSERT INTO tblCustomer (Fname, Lname, Extra) SELECT Fname, Lname, NULL FROM Inserted END
This will not write the "extra" field twice. However if a new Nullable column were added; it would be v.easy to forget to update the Instead Of trigger. Everything would still work OK but I would be effectively ignoring the new column as well.
What I would like in the instead of trigger is do something like this...
UPDATE INSERTED SET Extra=NULL
Continue with insert without supplying column /value list.
What would be the best way of achieving this, trigger is the only way I could think of?
Caseinfo shows when a particular case entered and exited a particular project. If the project hasn't ended yet, then the end date is NULL.
Steps shows the steps the case has gone through and the dates of those particular steps.
I need to join the tables to show the steps the case went through during a particular project, but I'm having trouble with the NULL values in the end dates.
If I join the tables so that the step date is between the start and end dates of the project, then I get no step information for the cases where the end date is NULL (that is, where the project hasn't ended yet).
Does anybody have any ideas?
Here are my tables, the query that shows the main idea (with the wrong result), and my expected results.
insert @caseinfo select 10, '2006-12-23', '2006-12-27' union all select 20, '2006-12-23', NULL union all select 30, '2006-12-23', NULL union all select 40, '2007-1-15', '2007-3-4'
insert @steps select 10, 1, '2006-12-24' union all select 10, 2, '2007-1-3' union all select 10, 3, '2007-2-5' union all select 20, 1, '2006-12-26' union all select 20, 2, '2007-1-7' union all select 20, 3, '2007-1-9' union all select 30, 1, '2007-1-14' union all select 40, 1, '2007-1-23' union all select 40, 2, '2007-3-2' union all select 40, 3, '2007-4-16'
--- the main idea (with the wrong results)
select * from @caseinfo c left join @steps s on s.caseid = c.caseid and s.stepdate between c.startdate and c.enddate
insert @expresult select 10, '2006-12-23', '2006-12-27', 1, '2006-12-24' union all select 20, '2006-12-23', NULL, 1, '2006-12-26' union all select 20, '2006-12-23', NULL, 2, '2007-1-7' union all select 20, '2006-12-23', NULL, 3, '2007-1-9' union all select 30, '2006-12-23', NULL, 1, '2007-1-14' union all select 40, '2007-1-15', '2007-3-4', 1, '2007-1-23' union all select 40, '2007-1-15', '2007-3-4', 2, '2007-3-2'
hi guys, I'm using SQLServer 2005 and I was wondering how I check if some variable is null or not? I tryied to do as the code show but it doesn't work:
declare @number int
set @number = (select number from numbers where number_id = 1)
I have an SQL database table that includes a BirthDate field. I would like to have this field as optional when adding a record, but, SQL insists on throwing an exception if the field is null.
After the "DAYS" column, some fields are coming null. We don't want it to come null. Beside this, i.e; in the "MONDAY"column, the same "NAMESURNAME" is coming more then one. How can I do the grouping by "NAMESURNAME"?
MachType DayPeriod Monday Tuesday Wednesday Thursday Friday Saturday Negative- || NIGHT || NULL || CAN AK || NULL || FİRDEVS ATEŞ || NULL || NULL Negative- || NIGHT || NULL || NULL || CEM TEK || NULL || NULL || NULL Negative- || NIGHT || NULL || NULL || NULL || FİRDEVS ATEŞ || MÜKREMİN EKELİK || NULL Negative- || NIGHT || NULL || CAN AK || CEM TEK || FİRDEVS ATEŞ || NULL || NULL Negative- || NIGHT || NULL || NULL || NULL || NULL || ÖMER CANOĞLU || NULL
select MachineType,DAYS,
CASE WHEN DIALYSISDAY= 'MONDAY'THEN NAMESURNAME END AS MONDAY,
CASE WHEN DIALYSISDAY= 'TUESDAY' THEN NAMESURNAME END AS TUESDAY,
CASE WHEN DIALYSISDAY= 'WEDNESDAY' THEN NAMESURNAME END AS WEDNESDAY,
CASE WHEN DIALYSISDAY= 'THURSDAY' THEN NAMESURNAME END AS THURSDAY,
CASE WHEN DIALYSISDAY= 'FRIDAY' THEN NAMESURNAME END AS FRIDAY,
CASE WHEN DIALYSISDAY= 'SATURDAY' THEN NAMESURNAME END AS SATURDAY
from
(SELECT SICK.MACHINETYPE,SICK.NAMESURNAME ,
CASE
WHEN SUBSTRING(DAYPERIOD, DATEPART(dw, SEANCE.SEANCEDATE), 1)= 'N' THEN 'NIGHT' ELSE '' END AS DAYS,
CASE
WHEN SICK.MACHINETYPE= 1 THEN 'Negative-'
WHEN SICK.MACHINETYPE= 2 THEN 'B(+)'
WHEN SICK.MACHINETYPE= 3 THEN 'C(+)B(+)'
WHEN SICK.MACHINETYPE= 4 THEN 'C(+)' END AS MachineType,
CASE
WHEN (DATEPART(dw, SEANCE.SEANCEDATE)) = 1 THEN 'MONDAY'
WHEN (DATEPART(dw, SEANCE.SEANCEDATE)) = 2 THEN 'TUESDAY'
WHEN (DATEPART(dw, SEANCE.SEANCEDATE)) = 3 THEN 'WEDNESDAY'
WHEN (DATEPART(dw, SEANCE.SEANCEDATE)) = 4 THEN 'THURSDAY'
WHEN (DATEPART(dw, SEANCE.SEANCEDATE)) = 5 THEN 'FRIDAY'
WHEN (DATEPART(dw, SEANCE.SEANCEDATE)) = 6 THEN 'SATURDAY' END AS DIALYSISDAY
FROM SEANCE,SICK WHERE SUBSTRING(DAYPERIOD, DATEPART(dw, SEANCE.SEANCEDATE), 1)= 'N' and
SUBSTRING(SICK.EUCLID,1,5)= '18016' AND SEANCEDATEBETWEEN '20080301' AND '20080307'
AND SICK.EUCLID=SEANCE.EUCLID )AS T ORDER BY MachineType
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
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
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!
Hi, All records of a table must be deleted if a field (field1) contains NULL or is made empty by a previous update command. I did this but it only works when the field is made empty, not when it contains NULL: dim field1 as string = ""...sql = "delete from mytable WHERE (field1=@field1)" comd = New SqlCommand(sql, oConnection) comd.Parameters.AddWithValue("@field1", field1) comd.ExecuteNonQuery() How can i do that? Thanks Tartuffe
Hi, I am building a website in ASP.net C# for a university project, and would like to search a table (Member) for a field (UserName) using a session variable Session["sUserName"]. If that field is null, then I would like to insert that session variable into the field to start to create a new user. However, I am getting errors saying that I am using invalid expression terms. My code is; //Create the Command, passing in the SQL statement and the ConnectionString queryString = "SELECT UserName FROM Member WHERE (UserName = @myUsername); "; SqlCommand cmd = new SqlCommand(queryString, sqlConn);cmd.Parameters.Add(new SqlParameter("@myUsername", Convert.ToString(Session["sUserName"]))); //If UserName is null, display confirmation, else display errorif (UserName == null) ; {UserNameCheckLabel.Text = "Username okay"; String queryString = "INSERT INTO Member (UserName) VALUES(@myUsername); ";SqlCommand cmd = new SqlCommand(queryString, sqlConn); cmd.Parameters.Add(new SqlParameter("@myUsername", Convert.ToString(Session["sUserName"]))); }else; {UserNameCheckLabel.Text = "That username is in use"; } I have a feeling I should be checking the database for the UserName, but I'm not sure whether to put this in the SELECT statement part or as a method... I would be most grateful for any advice! Many thanks, Chima
Another table with an uniqueidentifier which references the user iD
I want to set the value of the iD in one of the entries in the second table to null.
I tried this: string assigned is null, i am passing this as a method parameter ............... string queryString = "UPDATE [table2] SET AssignedUserId=@assigned WHERE ProblemId = @id"; System.Data.IDbCommand dbCommand = new System.Data.SqlClient.SqlCommand(); ................... System.Data.IDataParameter dbParam_au_pr = new System.Data.SqlClient.SqlParameter(); dbParam_au_pr.ParameterName = "@assigned"; if (assigned == null) { dbParam_au_pr.Value = null; } else { dbParam_au_pr.Value = assigned; } dbParam_au_pr.DbType = System.Data.DbType.String ; dbCommand.Parameters.Add(dbParam_au_pr);
I also tried using "" instead of null, or not using that "if" statement at all. I get an error that says:
Parameterized Query '(@id nvarchar(1),@assigned nvarchar(4000))UPDATE [tracker_Probl' expects parameter @assigned, which was not supplied. Please help with this as soon as possible.Thanks
I am having a problem with an update statement. I am using compareallvalues with a SQL data source. When one of the old values is null the update does not go through. i.e. This does not work if something is NULL UPDATE myTable SET something = @somethingWHERE ID = @o_ID AND something = @o_something Thank You,Jason
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 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?
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!
I am importing an Access .mdb file into MS SQL server, and empty fields where the default value is "", change into NULL. This is a problem when I re-export a result set and have to apply a procedure to clean these values. Is there a way to eliminate this? . . . . and what have I missed?
I added a new field to an existing ETL process which uses SSIS to ingest a CSV file. The new field in the file, Call_Transaction_ID, will not always be populated for every record and so can be NULL or empty for certain records.
Here's the problem:After the file is extracted into a staging table, the Call_Transaction_ID field is showing blank or empty when it has no ID for that particular record. The problem is when I try to then ETL this data into a fact table - I'm trying to set the Call_Transaction_ID field to -1 if it is NULL or empty, however SQL Server doesn't see the field as empty even though there is no value in the field so -1 will NEVER return.
Using a WHERE DATALENGTH(Call_Transaction_ID) = 0 returns 0 records, again because SQL Server doesn't see the field as empty or NULL.
What do I do now to get around this? How do I fix it?
I have a report where some fields are blank, i need to put 0 when the field is blank, i tried the following : IIf (Fields!X.Value = System.DBNull, 0, Field!X.Value)
this is complaining about System.DBNull is a type in system and cannot be used in comparison, what other types of fields can I use here to compare against Empty data? is there anything basic I am missing?
I have a concated code from excel when I have to pick some values for db update, the problem is when one field is empty, this field is date type and I don't now how can I validate if UserDate_02 field is null or not, I get it from a cell in excel and later when I get all the code I update the db, this is the line :
UPDATE ITEMS SET UserField_01='AA',UserDate_01='01/28/2014',UserYesNo_01=1,UserDate_02=''WHERE LotNumber='134034143017297'and ItemCode='G22221'
Hi!I got a column, with different dates in it (Ddata type "nvarchar")...when running a SELECT on this column, I'm trying to filter thoserecordsets out, WHERE this column is NULL (I checked the table, thereare "empty" fields in the column):"SELECT bla FROM bla WHERE myColumn NOT NULL"but I still receive those "empty" fields in my resultset ...so I tried it with:SELECT bla FROM bla WHERE myColumn <> ''again, "empty" fields in my resultset ....what's going wrong there? is there a possibility to check what kind ofvalue I got in my column!?Thanks!Peter
SELECT * FROM PABX INNER JOIN LOGIN ON (PABX.COD_CLIENTE = LOGIN.COD_CLIENTE) AND LEFT(LOGIN.TELEFONE1,3) = LEFT(PABX.NRTELEFONE,3) LEFT JOIN AUXILIAR ON (AUXILIAR.ORIGEM=LOGIN.LOCALIDADE) WHERE pabx.COD_cliente = 224 and SUBSTRING(PABX.NRTELEFONE,4,1) NOT IN ('9', '8', '7') AND LOGIN.UF = RIGHT(PABX.LOCALIDADE,2) AND LOGIN.LOCALIDADE <> PABX.LOCALIDADE AND PABX.CLASSIFICA IS NULL AND PABX.LOCALIDADE <> AUXILIAR.DESTINO AND (BLOQUEADO = 0 OR BLOQUEADO IS NULL)
But It has a problem because when AUXILIAR.DESTINO returns null (it means there is no registry) the condition AND PABX.LOCALIDADE <> AUXILIAR.DESTINO doesn't work, like 'SAO PAULO' is different from 'NULL' but for my query no it's not even equal, and this condition ommit the results....how can I solve it ?
PS: Both auxiliar.destino and pabx.localidade is varchar(255)
