how to add null values in dropdownlist to sql server by using asp.net

Question

I have created two table in SQL server they are:

TblStudent:

TblStudent

TblProvince:

TblProvince

In Asp.net Design Form:

<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Default4.aspx.cs" Inherits="Default4" %>

<!DOCTYPE html>

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title></title>
</head>
<body>
    <form id="form1" runat="server">
        <table>
            <tr>
                <td>
                    <asp:Label ID="Label1" runat="server" Text="StudentName"></asp:Label>
                </td>
                <td>
                    <asp:TextBox ID="txtStudentName" runat="server"></asp:TextBox>
                </td>
            </tr>
            <tr>
                <td>
                    <asp:Label ID="Label2" runat="server" Text="Province"></asp:Label>
                </td>
                <td>
                    <asp:DropDownList ID="ddlProvince" runat="server"></asp:DropDownList>
                </td>
            </tr>
            <tr>
                <td colspan="2">
                    <asp:Button ID="btnSave" runat="server" Text="Save" OnClick="btnSave_Click" />
                </td>
            </tr>
        </table>
    </form>
</body>
</html>

In Asp.net Back-End :

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;
using System.Data.SqlClient;

public partial class Default4 : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {
        if (!IsPostBack)
        {
            //bound data from TblProvince to ddlProvince 
            using (SqlConnection con  = DBManager.getConnection())
            {
                string sql = @"Select ProvinceId, Province From TblProvince";
                con.Open();
                SqlDataAdapter da = new SqlDataAdapter(sql, con);
                DataSet ds = new DataSet();
                da.Fill(ds, "dtProvince");
                ddlProvince.DataSource = ds.Tables["dtProvince"];
                ddlProvince.DataValueField = "ProvinceId";
                ddlProvince.DataTextField = "Province";
                ddlProvince.DataBind();
                ddlProvince.Items.Insert(0, new ListItem("Please Select", "NULL"));
            }
        }
    }
    protected void btnSave_Click(object sender, EventArgs e)
    {
        using (SqlConnection con = DBManager.getConnection())
        {
            string sql = @"Insert Into TblStudent(Name, ProvinceId) Values(@Name, @ProvinceId)";
            SqlCommand cmd = new SqlCommand(sql, con);
            cmd.Parameters.Add("@Name", SqlDbType.NChar).Value = txtStudentName.Text;
            cmd.Parameters.Add("@ProvinceId", SqlDbType.Int).Value = Convert.ToInt32(ddlProvince.SelectedValue);
            con.Open();
            cmd.ExecuteNonQuery();
        }
    }
}

Question:
When I do not select in ddlProvince why when u save it to database it shows an error Input string was not in a correct format.

what is the error u are getting and what is your question, i know you mentioned your qst, can you expand a bit — Tummala Krishna Kishore
– Tummala Krishna Kishore, Commented Mar 22, 2016 at 4:56
please check this solution on save click try to check if(ddlprovince.SelectedValue !=null){//enter your save operation} else{//through a msg to select a dropdown value} — Tummala Krishna Kishore
– Tummala Krishna Kishore, Commented Mar 22, 2016 at 5:01
Ok !! When i save data to database without select dropdownlist it show error "Input string was not in a correct format." but when i select other item in dropdownlist it nothing error — Sam Ath
– Sam Ath, Commented Mar 22, 2016 at 6:10
did you tried my approach? what my approach says, if the ddl value is null , DB will not hit so there are less chances of getting null value to your Table — Tummala Krishna Kishore
– Tummala Krishna Kishore, Commented Mar 22, 2016 at 6:16

Saravanan Sachi · Accepted Answer · 2016-03-22 05:04:20Z

1

It throws "Input string was not in a correct format" error because you are trying to convert null to Int.

cmd.Parameters.Add("@ProvinceId", SqlDbType.Int).Value = Convert.ToInt32(ddlProvince.SelectedValue);

You can check whether SelectedValue is null before converting it to Int as below.

cmd.Parameters.Add("@ProvinceId", SqlDbType.Int).Value = (ddlProvince.SelectedValue == null) ? null :  Convert.ToInt32(ddlProvince.SelectedValue)

answered Mar 22, 2016 at 5:04

Saravanan Sachi

2,6425 gold badges36 silver badges44 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

how to add null values in dropdownlist to sql server by using asp.net

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related