How To Insert NULL Char Values In SQLSERVER With A SQL Sentence?
Jul 20, 2005
Hi everyone!
I am working with Delphi v7 and MS SQLServer.
I am trying to insert data in a table with a SQL sentence. Some of the
fields of my table are type char or varchar, and they can have null
values.
¿What do i have to write in the SQL sentence to insert a null value in
those fields?
I tried with '', an empty String, but it doesnt work, the tables
stores an empty String (logical :-)).
In the SQLServer GUI you have to press CTRL + 0 to insert a NULL
value, but how can i tell this to the SQLServer through a SQL
Sentence?
Hi all I am trying to populate a page with data from a SQL DB however one field is null. The problem is this is causing a StrongTypingException and I am not sure how I should handle this to stop the apllication crashing. I am trying to assign a bit value from a SQL Db to checkboxI have tried putting if (Convert.IsDBNull(contentRow.pag_status) == false){//Do what I want} but this still throws the exception Can anyone help!?
i have insert/update SQL sentence, but sometimes there are empty values because there not required in the database so sometimes the sql sentence look this way:
INSERT INTO EquipmentAndPlace (EquipmentID,EquipmentEmdaNo,EquipmentPlace,EquipmentIDForRecognize, EquipmentRemarks,EquipmentLastChecked) VALUES ('3','','2','1','','12/1')
with empty values, but then it doent update in the dataBase-only if all the values appear- what the solution of it? Thanks
Im trying to setup a SQL server 2000 database to use membership & roles. Running aspnet_regsql.exe gives me the following errorsSetup failed.Exception:An error occurred during the execution of the SQL file 'InstallCommon.sql'. The SQL error number is 515 and the SqlException message is: Cannot insert the value NULL into column 'Column', table 'tempdb.dbo.#aspnet_Permissions_________________________________________________________________________________________________000000008000'; column does not allow nulls. INSERT fails.Warning: Null value is eliminated by an aggregate or other SET operation.The statement has been terminated.----------------------------------------Details of failure----------------------------------------SQL Server: Database: [AddressVerification]SQL file loaded:InstallCommon.sqlCommands failed:CREATE TABLE #aspnet_Permissions( Owner sysname, Object sysname, Grantee sysname, Grantor sysname, ProtectType char(10), [Action] varchar(20), [Column] sysname)INSERT INTO #aspnet_PermissionsEXEC sp_helprotectIF (EXISTS (SELECT name FROM sysobjects WHERE (name = N'aspnet_Setup_RestorePermissions') AND (type = 'P')))DROP PROCEDURE [dbo].aspnet_Setup_RestorePermissionsSQL Exception:System.Data.SqlClient.SqlException: Cannot insert the value NULL into column 'Column', table 'tempdb.dbo.#aspnet_Permissions_________________________________________________________________________________________________000000008000'; column does not allow nulls. INSERT fails.Warning: Null value is eliminated by an aggregate or other SET operation.The statement has been terminated. at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) at System.Data.SqlClient.SqlCommand.RunExecuteNonQueryTds(String methodName, Boolean async) at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe) at System.Data.SqlClient.SqlCommand.ExecuteNonQuery() at System.Web.Management.SqlServices.ExecuteFile(String file, String server, String database, String dbFileName, SqlConnection connection, Boolean sessionState, Boolean isInstall, SessionStateType sessionStatetype)Ive run this many times on other servers and have never come across this problem before. Has anyone else ? Does anyone know the cause and if there is a fix ??** I am logged in with full admin privaliges tiaMark.
I'd like to use an insert command to insert whatever data that is on the unbound form. The first three fields are required and I check those values prior to executing the code below. But some fields may be null.
I'm sorry, this question has a pretty long preamble, but bear with me as this will fully explain my problem, and save a number of clarification questions.I am using Visual Web Developer 2005 Express, with SQL Server 2005. After reading a Microsoft article saying that it was much better to use a three tier architecture with data adapters than to write explicit queries, I have used this approach extensively, and generally it works very well. Typical code patterns are: - Dim taPassenger As New ShippingTableAdapters.VPassengerTableAdapter Dim tbpassenger As Shipping.VPassengerDataTable Dim trpassenger As Shipping.VPassengerRow ..... ' to process all passengers from a voyage: - tbpassenger = taPassenger.GetDataByVOYid(Voyageid) For Nbr As Integer = 1 To tbpassenger.Count trpassenger = tbpassenger(Nbr - 1) ' I now have the row available in properly-typed fields, eg trpassenger.vpxName ... Next ' to read and process a single passenger tbpassenger = tapassenger.GetDataByVPXid(VPXid) trpassenger = tbpassenger(0)The table adapter has been created with Insert,Update, Delete methods. Thus you can assign values to row fields, and then update the row: - trpassenger.VPXName = "New Name" .... tapassenger.update(trpassenger)There are actually several overloads of the update method: you could have written a field list, like this, where the names (VPXid etc) would be defined in my program as variables of the appropriate type for the corresponding column. tapassenger.update(VPXid, VPXName, VPXAge, ....HOWEVER, this doesn't work properly if you have non-string data that could be null. Thus if the fourth field is a foreign key that could be null, if you write Dim VPXVoyId as Nullable(of GUID) VPXVoyid = nothing tapassenger.update(VPXid, VPXname, VPXAge, VPXVoyid, ...or Trpassenger.VPXVoyid = Nothing tapassenger.update(trpassenger) you do NOT put a null value into the database. Instead you store a value of "00000000-0000-0000-0000-000000000000". You can solve this annoying problem in two ways: -A. If you will ALWAYS be storing a null value at this point of your program, then simply write the update statement like this: - tapassenger.update(VPXid, VPXname, VPXAge, Nothing, ...Here "Nothing" actually means "DBNull" even though it doesn't mean this when used in a normal VB assignment (and neither does DBNull).B You can use the SetxxxxNULL method: - If trpassenger.VPXVoyid = nothing then trpassenger.SetVPXVoyidNull End If tapassenger.update(trpassenger) Thus when the program logic is complex and there are many paths to the update statement I will often put statements such as this in front of the update statement, using SetxxxxxNull for all non-string values that may be null. You don't need to worry about string values because the Nullvalue property can be used to simply say that Null values are empty, and it all works properly, but for reasons unknown Microsoft have prohibited this for other data types. Now at last we get to my question, which is "How do I do the equivalent for an Insert?" tapassenger.insert does not have any overloads, and the only form is tapassenger.insert(field list). But this creates the problem above: if VPXVoyid = nothing then I insert the not-null value of "00000000-0000-0000-0000-000000000000". If I COULD use the form tapassenger.insert(trpassenger), then I'd have another problem: if you haven't set trpassenger (for example with trpassenger = tbpassenger(0)) then it will not exist, so you will get an exception when you try to reference it. Yet you can't write If isnothing(trpassenger) then trpassenger = new shipping.vpassengerrow) (I've tries this both as an assignment, and as a Dim statement) end ifSo far I can think of only two solutions, both awful: -a/ Use multi-choice logic to choose an appropriate insert statement. If there is only one or two possibly-null fields, I could write:- If field4 = nothing then trpassenger.insert (VPXid, VPXname, VPXAge, Nothing, .. However if there is more than one possibly-null field, you need (2 to the power of nbr-of-possibly-null-fields) insert statements, with of course a lot of testing to choose which oneb/ I could insert the record with nulls, then read it back and update it. Surely there is a better way! Help! Thank you, Robert Barnes.
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 have a projects table with 2 foreign key fields that both link to the same employees table because a project has a Package Engineer (PkgEngineerID) and a Contract Administrator (PkgContrAdmin). When I try to insert a record with null values for either or both of these foreign keys I get an error:
The data in row xxx was not commited. The record can't be added or changed. Referential integrity rules require a related record in table 'tblEmployees'. The transaction ended in the trigger. The batch has been aborted.
An insert statement for the above would look something like the following:
I have a pivot transform that pivots a batch type. After the pivot, each batch type has its own row with null values for the other batch types that were pivoted. I want to group two fields and max() the remaining batch types so that the multiple rows are displayed on one row. I tried using the aggregate transform, but since the batch type field is a string, the max() function fails in the package. Is there another transform or can I use the aggragate transform another way so that the max() will work on a string?
I developed the following T-SQL query that runs successfully, but I was looking for a more efficient and concise way to do this. Is there a CTE that can replace all of these case statements? I've updated my query as below. Although this sample query works, it's not working for my real data. Instead, I get an error. At the bottom is the error part of my real query.I copied all of the tables from the first query block below. But when I wrote the bottom query block, it underlined in red the words "answer" and "question." It says "Invalid column name".Â
if exists (select * from tempdb.dbo.sysobjects o where o.xtype in ('U') and o.id = object_id(N'tempdb..#hard_values') ) DROP TABLE #hard_values; if exists (select * from tempdb.dbo.sysobjects o where o.xtype in ('U') and o.id = object_id(N'tempdb..#dummy_data') ) DROP TABLE #dummy_data; if exists (select * from tempdb.dbo.sysobjects o where o.xtype in ('U') and o.id = object_id(N'tempdb..#temp')
Hi I have a table, which contains Char and NChar NOT NULL columns Now I need to change it to NULL, when I use the following command, it fails for the following error,
The command I used, ALTER TABLE <TableName> ALTER COLUMN <ColName> CHAR NULL ALTER TABLE <TableName> ALTER COLUMN <ColName> NCHAR NULL
Msg 8152, Level 16, State 13, Line 1 String or binary data would be truncated. The statement has been terminated.
But for the same table, the below command executes fine, ALTER TABLE <TableName> ALTER COLUMN <ColName> SMALLINT NULL
Also I can change the NULLABILITY from NOTNULL to NULL using Enterprise Manger, editing the table using Table Design and selecting Allow Nulls option.
Hi, What is the difference updating a null value to char/varchar type column
versus empty string to char/varchar type column?Which is the best to do and why? Could anyone explain about this?
Example:
Table 1 : tCountry - Name varchar(80) nullable Table 2 :tState - Name char(2) nullable Table 3 :tCountryDetails - countryid,state (char(2) nullable) - May the country contain state or no state So,when the state is not present for the country ,i have two options may be - null,'' tCountryDetails.State = '' or tCountryDetails.State = null?
I have a DTSX package which reads values from a fixed-length text file using a data reader and writes some of the column values from the file to an Oracle table. We have used this DTSX several times without incident but recently the process started inserting NULL values for some of the columns when there was a valid value in the source file. If we extract some of the rows from the source file into a smaller file (i.e 10 rows which incorrectly returned NULLs) and run them through the same package they write the correct values to the table, but running the complete file again results in the NULL values error. As well, if we rerun the same file multiple times the incidence of NULL values varies slightly and does not always seem to impact the same rows. I tried outputting data to a log file to see if I can determine what happens and no error messages are returned but it seems to be the case that the NULL values occur after pulling in the data via a Data Reader. Has anyone seen anything like this before or does anyone have a suggestion on how to try and get some additional debugging information around this error?
SampleID  Rep_Number  Product  Protein  Fat  Solids
In the flat file SampleID and Product are populated in the first row only, rest of the rows only have values for Rep_Number, Protein, Fat, Solids.
SampleID and Product are blank for the rest of the rows. So my task is to fill those blank rows with the first row that has the sampleID and Product and load into the table.
I have SQL Server 2012 SSIS. I have Excel source and OLE DB Destination.I have problem with importing CustomerSales column.CustomerSales values like 1000.00,2000.10,3000.30,NotAvailable.So I have decimal values and nvarchar mixed in on Excel column. This is requirement for solution.However SSIS reads only numeric values correctly and nvarchar values are set as Null. Why?
CREATE TABLE [dbo].[Import_CustomerSales]( Â [CustomerId] [nvarchar](50) NULL, Â [CustomeName] [nvarchar](50) NULL, Â [CustomerSales] [nvarchar](50) NULL ) ON [PRIMARY]
can somebody explain me how I can assign a NULL value to a datetime type field in the script transformation editor in a data flow task. In the script hereunder, Row.Datum1_IsNull is true, but still Row.OutputDatum1 will be assigned a value '0001-01-01' which generates an error (not a valid datetime). All alternatives known to me (CDate("") or Convert.ToDateTime("") or Convert.ToDateTime(System.DBNull.Value)) were not successful. Leaving out the ELSE clause generates following error: Error: Year, Month, and Day parameters describe an un-representable DateTime.
I need to send some long emails, problem is when I send a long body text CDO breaks up the message with spaces in the middle of words. I read on this forum the solution would be to insert CR/LF into the message so that CDO would not have to break it up at every 1k of text. I have tried using CHAR(13) as BOL suggests, but this is not doing as intended, it inserts a space and not the CR/LF I need.
Any suggestions how to insert CR/LF or why my installation is not responding to CHAR(13) is there some other escape code I could use?
table2 is intially populated (basically this will serve as historical table for view); temptable and table2 will are similar except that table2 has two extra columns which are insertdt and updatedt
process: 1. get data from an existing view and insert in temptable 2. truncate/delete contents of table1 3. insert data in table1 by comparing temptable vs table2 (values that exists in temptable but not in table2 will be inserted) 4. insert data in table2 which are not yet present (comparing ID in t2 and temptable) 5. UPDATE table2 whose field/column VALUE is not equal with temptable. (meaning UNMATCHED VALUE)
* for #5 if a value from table2 (historical table) has changed compared to temptable (new result of view) this must be updated as well as the updateddt field value.
My query "select blah, blah, rank from tablewithscores" will return results that can legitimately hold nulls in the rank column. I want to order on the rank column, but those nulls should appear at the bottom of the list
I am getting this error: "Cannot insert the value NULL into column 'OrderID', table 'outman.outman.Contact'; column does not allow nulls. INSERT fails." -- But my value is not null. I did a response.write on it and it show the value. Of course, it would be nice if I could do a breakpoint but that doesn't seem to be working. I'll attach a couple of images below of my code, the error, and the breakpoint error.
Server Error in '/' Application.
Cannot insert the value NULL into column 'OrderID', table 'outman.outman.Contact'; column does not allow nulls. INSERT fails. 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: Cannot insert the value NULL into column 'OrderID', table 'outman.outman.Contact'; column does not allow nulls. INSERT fails.Source Error:
Line 89: sContact.Phone = sPhone.Text.Trim Line 90: sContact.Email = sEmail.Text.Trim Line 91: sContact.Save() Line 92: Line 93: Dim bContact As Contact = New Contact()Source File: F:InetpubwwwrootOutman KnifeCheckout.aspx.vb Line: 91 Stack Trace:
hi i am using formview i have a date field textbox which selects date from ajax calender now my point is when i make this field blank at that time in my gridview rather then displaying the date field blank it shows me the value 1 Jan 1900 this is the default date of sqlserver which it returns when we leave the field '' now when i pass the value null it automatically takes it as '' in my class file my function is public object checkdate(TextBox txtdate) { if (txtdate.Text == "") { return null; } else { return txtdate.Text; } } in my cs file i wrote object checkemptydate = obj.checkdate(nextvaccinationdate); string values = patientID + ",'" + vaccination + "',convert(varchar,'" + vaccinationdate + "',106)," + checkemptydate + ",'" + remarks + "'"; note that i even tried this too string values = patientID + ",'" + vaccination + "',convert(varchar,'" + vaccinationdate + "',106)," + null + ",'" + remarks + "'"; and string values = patientID + ",'" + vaccination + "',convert(varchar,'" + vaccinationdate + "',106),convert(varchar,'" + checkemptydate + "',106),'" + remarks + "'"; but nothing is working it passes '' not null how could i solve this problem? shweta
I have an SSIS package that imports data from an Excel file, replaces any value in Excel that reads "NULL" to "", then writes the data to a couple of databases.
What I have discovered today, is I have two columns of dates, an admit date and discharge date column, and what I need to do is anywhere I have a null value in the discharge date column, I have to replace it with the value in the admit date column.Â
I have searched around online and tried a few things using the Replace funtion in Derived columns but no dice so far.Â
Is there a way to avoid entering column names in the excel template for me to create an excel file froma dynamic excel using openrowset. I have teh following code but it works fien when column names are given ahead of time. If I remove the column names from the template and just to Select * from the table and Select * from sheet1 then it tells me that column names donot match. Server: Msg 213, Level 16, State 5, Line 1Insert Error: Column name or number of supplied values does not match table definition. here is my code... SET @sql1='select * from table1'SET @sql2='select * from table2' IF @File_Name = '' Select @fn = 'C:Test1.xls' ELSE Select @fn = 'C:' + @File_Name + '.xls' -- FileCopy command string formation SELECT @Cmd = 'Copy C:TestTemplate1.xls ' + @fn -- FielCopy command execution through Shell Command EXEC MASTER..XP_CMDSHELL @cmd, NO_OUTPUT -- Mentioning the OLEDB Rpovider and excel destination filename set @provider = 'Microsoft.Jet.OLEDB.4.0' set @ExcelString = 'Excel 8.0;HDR=yes;Database=' + @fn exec('insert into OPENrowset(''' + @provider + ''',''' + @ExcelString + ''',''SELECT * FROM [Sheet1$]'') '+ @sql1 + '') exec('insert into OPENrowset(''' + @provider + ''',''' + @ExcelString + ''',''SELECT * FROM [Sheet2$]'') '+ @sql2 + ' ')
Please be easy on me...I haven't touched SQL for a year. Why given;
Code Snippet USE [Patients] GO /****** Object: Table [dbo].[Patients] Script Date: 08/31/2007 22:09:29 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET ANSI_PADDING ON GO CREATE TABLE [dbo].[Patients]( [PID] [int] IDENTITY(1,1) NOT NULL, [ID] [varchar](50) NULL, [FirstName] [nvarchar](50) NULL, [LastName] [nvarchar](50) NULL, [DOB] [datetime] NULL, CONSTRAINT [PK_Patients] PRIMARY KEY CLUSTERED ( [PID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] GO SET ANSI_PADDING OFF
Hi, I ve a table, I want to fetch certain rows based on the value of a Column. That column is nullable, and contains NULL values.I used the following query,SELECT Col_A FROM TABLE1 WHERE SOME_ID = 1317 AND Col_B NOT IN (8,9) Here, Col_B contains NULL values too. I need to fetch all rows where Col_B is not 8 or 9.Now, if I use "NOT IN", it does not work. I tried reading on it and got to know why it does not work. Even "NOT EXISTS" does not help. But still I've to fetch my values. How do I do that?Thanks & Regards,Jahanzeb
I have tried doing a search, as I figured this would be a common problem, but I wasn't able to find anything. I know that my SP is functional because when I use VWD execute the query outside of the webpage, I get the correct results -however I have to ensure that a field is either entered, or set to <NULL>. In my SET's I want it to use the wildcards. What I want is to do a search (plenty of existing topics on that, however none were of help to me). If a field is entered, then it is included in the search. Otherwise it should be ignored. In my VB I have the standard stored procedure call, passing in values to all of the parameters in the stored proc below: CREATE PROCEDURE dbo.SearchDog@tagnum int,@ownername varchar(50), @mailaddress varchar(50),@address2 varchar(50),@city varchar(50),@telephone varchar(50),@doggender varchar(50),@dogbreed varchar(50),@dogage varchar(50),@dogcolour varchar(50),@dogname varchar(50),@applicationdate varchar(50)AS IF @tagnum=-1 SET @tagnum=NULL SET @ownername = '%'+@ownername+'%' SET @mailaddress = '%'+@mailaddress+'%' SET @address2='%'+@address2+'%' SET @city = '%'+@city+'%' SET @telephone='%'+@telephone+'%' SET @dogcolour='%'+@dogcolour+'%' SET @dogbreed='%'+@dogbreed+'%' SET @dogage='%'+@dogage+'%' SET @doggender='%'+@doggender+'%' SET @dogname='%'+@dogname+'%' SET @applicationdate='%'+@applicationdate+'%' SELECT DISTINCT * FROM DogRegistry WHERE ( TagNum = @tagnum OR OwnerName LIKE @ownername OR MailAddress LIKE @mailaddress OR Address2 LIKE @address2 OR City LIKE @city OR Telephone LIKE @telephone OR DogGender LIKE @doggender OR DogBreed LIKE @dogbreed OR DogAge LIKE @dogage OR DogColour LIKE @dogcolour OR DogName LIKE @dogname OR ApplicationDate LIKE @applicationdate ) AND TagNum > 0GO I don't know why it is creating links inside my SP -ignore them. TagNum is the primary key, if that makes a difference. On the webpage, it ONLY works when every field has been filled (and then it will only return 1 row, as it should, given the data entered). Debugging has shown that when nothing is entered it passes "". Any ideas?
I am trying to retrieve data from two different tables. One of the tables has more than 20 columns some of which are null. I would like to retrieve data from both tables excluding the columns which have null values. How do I do this?
Would this take care of null values in either a.asset or b.asset?
SELECT convert(decimal(15,1),(sum(isnull(a.asset,0))/1000.0)+(sum(isnull(b.asset,0))/1000.0)) as total_assets
What's throwing me off is that there are multiple a.asset or b.asset for each unique ID. It seems to work, but I'm not following the logic too well. If I were doing this in another language, I would loop through, summing a.asset and b.asset wherever it's not null for each unique ID.
How can I use "Derived Column" to check if a Datetime value is null or not and if null to insert 00/00/00 instead. ?
The background being that while using a "Derived Column" to change a Column from a (DT_DATE) to a (DT_DBTIMESTAMP) everytime I get a null value it see's it as a error.
And the column in particular has ~ 37 K blank / null fields so Im getting a lot of errors
So far I have tried to use something like
ISNULL([Column 34])
Or
SELECT ISNULL(ID, '00/00/0000') FROM [Column 34]
Or
SELECT ISNULL(au_id, '00/00/0000 00:00') AS ssn FROM [Column 34
but none seems to work [Column 34] being the offending column.
What a normally use is just a simple "(DT_DBTIMESTAMP)[Column 34]" in the expression column, which seems to work well, but here I get alot of errors
