locked
how to insert null value into database? RRS feed

  • Question

  • this is my query and i want to insert null value into the numeric fields credit limit,credit days and parentparty if user enters nothing to these fields  for this i have used this:

    object creditDays=null ;

    object creditLimit=null ;

    object parentparty=null ;

    if (this.txtCreditDays.Text == "")

    {

    creditDays = System.DBNull.Value;

    }

    if (this.txtCreditLimit.Text == "")

    {

    creditLimit = System.DBNull.Value;

    }

    if (this.ddlSister.SelectedValue == "")

    {

    parentparty = System.DBNull.Value;

    }

    sql = "insert into Party(PartyType,PartyName,ContactPerson,Address1,Address2,Address3,City,PinCode," +

    "State,PhoneNo,MobileNo,Fax,Email,WebSite,CstLst,CstNo,CstDate,VatNo,ParentParty,CreditLimit,CreditDays," +

    "Regularity,Rating,RegistrationNumber,EccNo,ExicesRange,Division,Commission,PanNo,RC_NO,PLA_NO," +

    "DateOfNotification,StartDate,EndDate ) " +

    " values('" + this.ddlPartyType.SelectedValue + "','" + this.txtPartYName.Text + "','" + txtContactPerson.Text + "', " +

    " '" + this.txtAddress1.Text + "', '" + this.txtAddress2.Text + "','" + this.txtAddress3.Text + "'," +

    " '" + ddlCity.SelectedItem.Text + "','" + this.txtPin.Text + "','" + ddlState.SelectedItem.Text + "'," +

    " '" + this.txtPhone.Text + "','" + this.txtMob.Text + "' ,'" + txtFax.Text + "','" + txtEmail.Text + "'," +

    "'" + txtWebsite.Text + "','" + ddlTaxCategory.SelectedValue + "','" + txtSaleTaxNo.Text + "'," +

    " '" + txtSaleTaxDate.Text + "','" + txtvat.Text + "'," + parentparty + "," + creditLimit + "," +

    " " + creditDays + ",'" + ddlRegularity.SelectedValue + "', '" + ddlRating.SelectedValue + "'," +

    " '" + txtReg.Text + "','" + txtEcc.Text + "', '" + this.txtRange.Text + "'," +

    " '" + txtDivision.Text + "','" + txtCommission.Text + "','" + txtPanNo.Text + "'," +

    " '" + txtRc.Text + "','" + txtPla.Text + "','" + txtNotification.Text + "'," +

    " '" + txtStartDate.Text + "','" + txtEndDate.Text + "')";

    but instead null it goes nothing which sql-server does not accept pls help .I am using C#.Net

    Friday, October 10, 2008 6:23 AM

Answers

  • First do a google search for SQL Injection.  Then change your database call over to a parameterized call, and the TSQL below will allow you to pass an empty string, in the input and set a null value:

     

    Code Snippet

    insert into Party(PartyType,PartyName,ContactPerson,Address1,Address2,Address3,City,PinCode,

    State,PhoneNo,MobileNo,Fax,Email,WebSite,CstLst,CstNo,CstDate,VatNo,ParentParty,CreditLimit,CreditDays,

    Regularity,Rating,RegistrationNumber,EccNo,ExicesRange,Division,Commission,PanNo,RC_NO,PLA_NO,

    DateOfNotification,StartDate,EndDate )

    VALUES

    (@PartyType, @PartyName, @ContactPerson, @Address1, @Address2, @Address3, @City, @PinCode,

    @State, @PhoneNo, @MobileNo, @Fax, @Email, @WebSite, @CstLst, @CstNo, @CstDate, @VatNo, NULLIF(@ParentParty, ''),

    NULLIF(@CreditLimit, ''), NULLIF(@CreditDays, ''), @Regularity, @Rating, @RegistrationNumber, @EccNo, @ExicesRange,

    @Division, @Commission, @PanNo, @RC_NO, @PLA_NO, @DateOfNotification, @StartDate, @EndDate)

     

     

     

    Friday, October 10, 2008 3:22 PM