how to insert NULL values : The Official Microsoft ASP.NET Forums

Reply

zubair258

Member

112 Points

how to insert NULL values

Jan 07, 2013 06:03 PM|LINK

i have many columns in database , and values not Null Allowed in column, when empty values come so i want to insert null values into columns, how it possible ?

<mSet id="WbrTransfer">

      <mRow id="CenMst">
        <mCol id="CenNbr">CDE</mCol>
        <mCol id="FulNme">NAME</mCol>
        <mCol id="Adr1">610 FREEDOM BUSINESS CENTER</mCol>
        <mCol id="Adr2"></mCol>
        <mCol id="CtyNme">KOP</mCol>
        <mCol id="SteCde">PA</mCol>
        <mCol id="ZipCde"></mCol>
        <mCol id="TelNbr"></mCol>
        <mCol id="FaxNbr"></mCol>
        <mCol id="UpdDte">11</mCol>
        <mCol id="UpdTim">12</mCol>
        <mCol id="UpdUsr">233</mCol>
        <mCol id="TimeStamp">12</mCol>
        <mCol id="TimStp">1234</mCol>
      </mRow>
    </mSet>

when i insert data of null then give me that error

Cannot insert the value NULL into column &#39;gdenbr&#39;, table &#39;webservice.dbo.ordlin&#39;; column does not allow nulls. INSERT fails.
The statement has been terminated.

here is store procedure 

USE [webservice]
GO
/****** Object:  StoredProcedure [dbo].[GetDatanew]    Script Date: 01/08/2013 00:11:58 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER procedure [dbo].[GetDatanew]
 (@xmlstr ntext,
 @cennbr char(6) = NULL)
 as BEGIN
  declare @hDoc int
  exec sp_xml_preparedocument @hDoc OUTPUT,@xmlstr
  --This code updates old data.
  UPDATE cenmst 
  set cenmst.cennbr=xmlcenmst.cennbr,
      cenmst.fulnme=xmlcenmst.fulnme,
      cenmst.adr1=xmlcenmst.adr1,
      cenmst.adr2=xmlcenmst.adr2,
      cenmst.ctynme=xmlcenmst.ctynme,
      cenmst.stecde=xmlcenmst.stecde,
      cenmst.zipcde=xmlcenmst.zipcde,
      cenmst.telnbr=xmlcenmst.telnbr,
      cenmst.faxnbr=xmlcenmst.faxnbr,
      cenmst.upddte=xmlcenmst.upddte,
      cenmst.updtim=xmlcenmst.updtim,
      cenmst.updusr=xmlcenmst.updusr,
      cenmst.[timestamp]=xmlcenmst.[timestamp],
      cenmst.timstp=xmlcenmst.timstp
      FROM OPENXML(@hDoc, '/mDoc/mTrans/mSet/mRow/mCol', 1)
      with (cennbr char(6),fulnme char(36),adr1 char(36),adr2 char(36),ctynme char(20),stecde char(2),zipcde char(10),telnbr char(14),faxnbr char(14),upddte char(8),updtim char(8),updusr char(6),[timestamp] int,timstp int) xmlcenmst
      where cenmst.cennbr=xmlcenmst.cennbr
      -- this code insert new data. 
  insert into cenmst (cennbr, fulnme, adr1, adr2, ctynme ,stecde , zipcde, telnbr, faxnbr,upddte,updtim,updusr,[timestamp],timstp)
  select cennbr, fulnme, adr1, adr2, ctynme ,stecde , zipcde, telnbr, faxnbr,upddte,updtim,updusr,[timestamp],timstp
     from (SELECT * FROM OPENXML(@hDoc, '/mDoc/mTrans/mSet/mRow/mCol', 1)
     WITH ( id    VARCHAR(11),VALUE  VARCHAR(50) 'text()')) as sourceTable pivot (min(value) for id
 in ([CenNbr],[FulNme],[Adr1],[Adr2],[CtyNme],[SteCde],[ZipCde],[TelNbr],[FaxNbr],[UpdDte],[UpdTim],[UpdUsr],[TimeStamp],[TimStp]))AS P
where p.cenNbr not in ( select cennbr from cenmst)
 EXEC sp_xml_removedocument @hDoc
END

Zubair258

Edited by zubair258 on Jan 07, 2013 06:17 PM

Reply

AceCorban

Star

12318 Points

2269 Posts

Re: how to insert NULL values

Jan 07, 2013 06:08 PM|LINK

I'm confused. You are saying that the database is set up to not allow null values for these columns that you want to insert null values into?

"Dream as if you'll live forever, live as if you'll die today." --James Dean

Reply

zubair258

Member

112 Points

129 Posts

Re: how to insert NULL values

Jan 07, 2013 06:16 PM|LINK

actulay i am want to insert the XML values into database tables, but some values in missing in XML file, so i want to insert empty valuse replace on char and string values, and 0(zero) insert on Int values into database table, specifiy in store procedure plz

here is simple store procedure

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER procedure [dbo].[GetDatanew]
(@xmlstr ntext,
@cennbr char(6) = NULL)
as BEGIN
declare @hDoc int
exec sp_xml_preparedocument @hDoc OUTPUT,@xmlstr
--This code updates old data.
UPDATE cenmst
set cenmst.cennbr=xmlcenmst.cennbr,
      cenmst.fulnme=xmlcenmst.fulnme,
      cenmst.adr1=xmlcenmst.adr1,
      cenmst.adr2=xmlcenmst.adr2,
      cenmst.ctynme=xmlcenmst.ctynme,
      cenmst.stecde=xmlcenmst.stecde,
      cenmst.zipcde=xmlcenmst.zipcde,
      cenmst.telnbr=xmlcenmst.telnbr,
      cenmst.faxnbr=xmlcenmst.faxnbr,
      cenmst.upddte=xmlcenmst.upddte,
      cenmst.updtim=xmlcenmst.updtim,
      cenmst.updusr=xmlcenmst.updusr,
      cenmst.[timestamp]=xmlcenmst.[timestamp],
      cenmst.timstp=xmlcenmst.timstp
      FROM OPENXML(@hDoc, '/mDoc/mTrans/mSet/mRow/mCol', 1)
      with (cennbr char(6),fulnme char(36),adr1 char(36),adr2 char(36),ctynme char(20),stecde char(2),zipcde char(10),telnbr char(14),faxnbr char(14),upddte char(8),updtim char(8),updusr char(6),[timestamp] int,timstp int) xmlcenmst
      where cenmst.cennbr=xmlcenmst.cennbr
      -- this code insert new data.
insert into cenmst (cennbr, fulnme, adr1, adr2, ctynme ,stecde , zipcde, telnbr, faxnbr,upddte,updtim,updusr,[timestamp],timstp)
select cennbr, fulnme, adr1, adr2, ctynme ,stecde , zipcde, telnbr, faxnbr,upddte,updtim,updusr,[timestamp],timstp
     from (SELECT * FROM OPENXML(@hDoc, '/mDoc/mTrans/mSet/mRow/mCol', 1)
     WITH ( id    VARCHAR(11),VALUE VARCHAR(50) 'text()')) as sourceTable pivot (min(value) for id
in ([CenNbr],[FulNme],[Adr1],[Adr2],[CtyNme],[SteCde],[ZipCde],[TelNbr],[FaxNbr],[UpdDte],[UpdTim],[UpdUsr],[TimeStamp],[TimStp]))AS P
where p.cenNbr not in ( select cennbr from cenmst)
EXEC sp_xml_removedocument @hDoc
END

*************************** here is table script **********************************

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[cenmst](
   [cennbr] [char](6) NOT NULL,
   [fulnme] [char](36) NOT NULL,
   [adr1] [char](36) NOT NULL,
   [adr2] [char](36) NOT NULL,
   [ctynme] [char](20) NOT NULL,
   [stecde] [char](2) NOT NULL,
   [zipcde] [char](10) NOT NULL,
   [telnbr] [char](14) NOT NULL,
   [faxnbr] [char](14) NOT NULL,
   [upddte] [char](8) NOT NULL,
   [updtim] [char](8) NOT NULL,
   [updusr] [char](6) NOT NULL,
   [timestamp] [int] NOT NULL,
   [timstp] [int] NOT NULL,
CONSTRAINT [PK_cenmst] PRIMARY KEY CLUSTERED
(
   [cennbr] 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
GO

***************************** here is C# code in VS *********************************

service.asmx page

<%@ WebService Language="C#" CodeBehind="~/App_Code/Service.cs" class="Service" %>

here is Sevice.cs page

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Services;
using System.Data;
using System.Data.SqlClient;
using System.Xml;
using System.Text;
using System.Configuration;
using System.Xml.Linq;
[WebService(Namespace = "http://tempuri.org/")]
[WebServiceBinding(ConformsTo = WsiProfiles.BasicProfile1_1)]
// To allow this Web Service to be called from script, using ASP.NET AJAX, uncomment the following line.
// [System.Web.Script.Services.ScriptService]

public class Service : System.Web.Services.WebService
{
    SqlConnection con;
    SqlDataAdapter adap;
    DataSet ds, ds1;
    string connStr = ConfigurationManager.ConnectionStrings["myConnectionString"].ConnectionString;
    [WebMethod]
    public DataSet webservices()
    {
        con = new SqlConnection(@"Data Source=ZUBAIRKHALIL-HP\ZUBAIRKHALIL;Initial Catalog=webservice;Integrated Security=True;MultipleActiveResultSets=True");
        ds = new DataSet();
        ds.ReadXml(Server.MapPath("MDEMO_data.xml"));
        string strxml = XDocument.Load(Server.MapPath("MDEMO_data.xml")).ToString();
        SqlConnection sqlconn = new SqlConnection(connStr);
        SqlCommand sqlcmd = new SqlCommand();
        sqlcmd.Connection = sqlconn;
        sqlcmd.CommandType = CommandType.StoredProcedure;
        sqlcmd.CommandText = "GetDatanew";
        sqlcmd.Parameters.AddWithValue("@xmlstr", strxml);

        sqlconn.Open();
        sqlcmd.ExecuteNonQuery();
        sqlconn.Close();
        adap = new SqlDataAdapter("select * from CenMst", con);
        ds1 = new DataSet();
        adap.Fill(ds1, "CenMst");
        return ds1;

    }

}

****************************** here is XML FIle values **********************************************

<?xml version="1.0" encoding="UTF-8" ?>

<mDoc>

<mTrans id="Transfer Request">

    <mSet id="WbrTransfer">

      <mRow id="CenMst">
        <mCol id="CenNbr">CDE</mCol>
        <mCol id="FulNme">NAME</mCol>
        <mCol id="Adr1">610 FREEDOM BUSINESS CENTER</mCol>
        <mCol id="Adr2"></mCol>
        <mCol id="CtyNme"></mCol>
        <mCol id="SteCde"></mCol>
        <mCol id="ZipCde">12</mCol>
        <mCol id="TelNbr">(610)265-6606</mCol>
        <mCol id="FaxNbr">(610)337-6107</mCol>
        <mCol id="UpdDte">11</mCol>
        <mCol id="UpdTim">12</mCol>
        <mCol id="UpdUsr">233</mCol>
        <mCol id="TimeStamp">12</mCol>
        <mCol id="TimStp">1234</mCol>
      </mRow>
    </mSet>

</mTrans>

</mDoc>

Zubair258

Reply

TabAlleman

All-Star

15571 Points

2700 Posts

Re: how to insert NULL values

Jan 08, 2013 06:47 PM|LINK

In your stored procedure, do this everywhere that insert/update from your XML file:

UPDATE cenmst
set cenmst.cennbr=ISNULL(xmlcenmst.cennbr,''),

this will replace NULL with an empty space. Do this for each of the columns.

-Tab Alleman

Marked as answer by Catherine Shan - MSFT on Jan 14, 2013 01:21 AM

how to insert NULL values RSS

3 replies

zubair258

how to insert NULL values

AceCorban

Re: how to insert NULL values

zubair258

Re: how to insert NULL values

TabAlleman

Re: how to insert NULL values

This site is managed for Microsoft by Neudesic, LLC. | © 2013 Microsoft. All rights reserved.

Follow Us On:

Microsoft