inserting data using storeprocedure

Question

0.00/5 (No votes)

See more:

Sir m using these codes to insert value to database using storeprocedure but the value is not inserting in database m not getting what is wrong..pls help thanks in advance.

m filling city and locality dropdownlist from database and inserting there id to database but not able to insert...

m getting error input type is not in correct format something like this.

Store procedure:

SQL

ALTER Procedure dbo.Post_Requirement_Buy
(
 
	@Property_Type varchar(50),
	@City_Id int,
	@Locality_Id int,
	@Min_Price varchar(15),
	@Max_Price varchar(15),
	@Bedrooms int,
	@Build_up_area varchar(20),
	@Key_Features varchar(100),
	@Name varchar(50),
	@Email varchar(50),
	@Mobile_No numeric
)
as
begin
insert into tbl_post_requirement_buying
(
Property_Type,City_Id,Locality_Id,Min_Price,Max_Price,
Bedrooms,Build_up_area,Key_Features,Name,Email,Mobile_No
)
values(
@Property_Type,
@City_Id,
@Locality_Id,
@Min_Price,
@Max_Price,
@Bedrooms,
@Build_up_area,
@Key_Features,
@Name,
@Email,
@Mobile_No 
)
end

code behind:

C#

protected void btnSubmit_Click(object sender, EventArgs e)
    {

        
        
            SqlConnection con = new SqlConnection(str);
            SqlCommand cmdins = new SqlCommand("dbo.Post_Requirement_Buy", con);
            cmdins.CommandType = CommandType.StoredProcedure;
            cmdins.Parameters.AddWithValue("@Property_Type", ddlpropertytype.SelectedItem.Text);
            cmdins.Parameters.AddWithValue("@City_Id",Convert.ToInt32(ddlcity.SelectedItem.Text));
            cmdins.Parameters.AddWithValue("@Locality_Id",Convert.ToInt32(ddllocality.SelectedItem.Text));
            cmdins.Parameters.AddWithValue("@Min_Price", ddlminprice.SelectedItem.Text);
            cmdins.Parameters.AddWithValue("@Max_Price", ddlmaxprice.SelectedItem.Text);
            cmdins.Parameters.AddWithValue("@Bedrooms", Convert.ToInt32(ddlbedrooms.SelectedItem.Text));
            cmdins.Parameters.AddWithValue("@Build_up_area", txtarea.Text + ' ' + ddlarea.SelectedItem.Text);
            cmdins.Parameters.AddWithValue("@Key_Features", txtfeatures.Text);
            cmdins.Parameters.AddWithValue("@Name", txtname.Text);
            cmdins.Parameters.AddWithValue("@Email", txtemail.Text);
            cmdins.Parameters.AddWithValue("@Mobile_No", txtmobileno.Text);

            con.Open();

            cmdins.ExecuteNonQuery();

            lblmessage.Text = "Submitted Sucessfully";

            con.Close();
        


    }

Form Design:

ASP.NET

<table cellpadding="0" width="100%" cellspacing="20" border="0px" style="background-color: White">
            <tr>
                <td colspan="2">
                     <span style="font-size: small; font-family: Arial">Fields marked with
                    </span> <span style="color: #FF3300; font-size: small; font-family: Arial;">*</span><span>
                        style="font-size: small; font-family: Arial"> are mandatory:</span><br />
                </td>
            </tr>
            <tr>
                <td style="height: 20px" class="style4" colspan="2">
                        <asp:Label ID="lblmessage" runat="server" Text="" ForeColor="Green">
                </td>
            </tr>
            <tr>
                <td style="height: 52px">
                     
                </td>
                <td style="height: 52px">
                    <span style="font-family: Arial; font-size: medium">Requirement
                    :</span><br />
                    <asp:RadioButton ID="rbtnfb" Checked="true" Text="For Buying" GroupName="radio" runat="server">
                    
                    <asp:RadioButton ID="rbtnfr"  Text="For Rent" GroupName="radio" runat="server">  
                    <span style="font-size: x-small; font-family: Arial; ">       
                    <span style="text-decoration: underline">Select Atleast One Option</span></span><br />
                    <%--<asp:Label ID="lblerror" ForeColor="Red" runat="server" Text="">--%>
                   
                </td>
            </tr>
            <tr>
                <td>
                    <span style="font-size: small; font-family: Arial">Property Type :</span><span style="color: #FF0000">* </span>
                </td>
                <td>
                    <asp:DropDownList ID="ddlpropertytype" runat="server" CssClass="validate[required] radio"
                        Width="270px">
                        <asp:ListItem Value="">--Select--
                        <asp:ListItem Value="Commercial">Commercial
                        <asp:ListItem Value="Residential">Residential
                        <asp:ListItem Value="Plot">Plot
                        <asp:ListItem Value="Bunglow">Bunglow
                        <asp:ListItem Value="Villa">Villa
                        
                    
                    <%--<asp:TextBox ID="txtfname" runat="server" CssClass="validate[required]" />--%>
                </td>
            </tr>
            <tr>
                <td>
                    <span style="font-size: small; font-family: Arial">City :</span><span style="color: #FF0000">* </span>
                </td>
                <td>
                    <asp:DropDownList ID="ddlcity" runat="server" Height="22px" CssClass="validate[required] radio"
                        Width="270px" EnableViewState="True">
                        <asp:ListItem Value="">--Select--
                        
                    <%--<asp:TextBox ID="txtlname" runat="server" CssClass="validate[required]" />--%>
                </td>
            </tr>
            <tr>
                <td>
                    <span style="font-size: small; font-family: Arial">Locality :</span><span style="color: #FF0000">* </span>
                </td>
                <td>
                    <asp:DropDownList ID="ddllocality" runat="server" Height="22px" CssClass="validate[required] radio"
                        Width="270px">
                        <asp:ListItem Value="">--Select--
                        
                    
                    <%--<asp:TextBox ID="txtemail" runat="server" CssClass="validate[required,custom[email]" />--%>
                </td>
            </tr>
            <tr>
                <td>
                    <span style="font-size: small; font-family: Arial">Price :</span><span style="color: #FF0000">* </span>
                </td>
                <td>
                    <asp:DropDownList ID="ddlminprice" runat="server" CssClass="validate[required] radio">
                        <asp:ListItem Value="">--Select--
                        <asp:ListItem Value="1">15 lacs
                        <asp:ListItem Value="2">20 Lacs
                        <asp:ListItem Value="3">30 Lacs
                        <asp:ListItem Value="4">40 Lacs
                        <asp:ListItem Value="5">50 Lacs
                        <asp:ListItem Value="6">60 Lacs
                        <asp:ListItem Value="7">70 Lacs
                        <asp:ListItem Value="8">80 Lacs
                        <asp:ListItem Value="9">90 Lacs
                        <asp:ListItem Value="10">1 crore
                        <asp:ListItem Value="11">1.2 crores
                        <asp:ListItem Value="12">1.4 crores
                        <asp:ListItem Value="13">1.6 crores
                        <asp:ListItem Value="14">1.8 crores
                        <asp:ListItem Value="15">2 crores
                    
                        
                    <asp:DropDownList ID="ddlmaxprice" runat="server" CssClass="validate[required] radio">
                        <asp:ListItem Value="">--Select--
                        <asp:ListItem Value="1">15 lacs
                        <asp:ListItem Value="2">20 Lacs
                        <asp:ListItem Value="3">30 Lacs
                        <asp:ListItem Value="4">40 Lacs
                        <asp:ListItem Value="5">50 Lacs
                        <asp:ListItem Value="6">60 Lacs
                        <asp:ListItem Value="7">70 Lacs
                        <asp:ListItem Value="8">80 Lacs
                        <asp:ListItem Value="9">90 Lacs
                        <asp:ListItem Value="10">1 crore
                        <asp:ListItem Value="11">1.2 crores
                        <asp:ListItem Value="12">1.4 crores
                        <asp:ListItem Value="13">1.6 crores
                        <asp:ListItem Value="14">1.8 crores
                        <asp:ListItem Value="15">2 crores
                    
                </td>
            </tr>
            <tr>
                <td>
                    <span style="font-size: small; font-family: Arial">Bedrooms :
                </span></td>
                <td>
                    <asp:DropDownList ID="ddlbedrooms" runat="server" 
                        Width="270px">
                        <asp:ListItem Value="">--Select--
                        <asp:ListItem Value="1">0
                        <asp:ListItem Value="2">1
                        <asp:ListItem Value="3">2
                        <asp:ListItem Value="4">3
                        <asp:ListItem Value="5">4
                        <asp:ListItem Value="6">5
                        <asp:ListItem Value="7">6
                        <asp:ListItem Value="8">7
                        <asp:ListItem Value="9">8
                        <asp:ListItem Value="10">9
                        <asp:ListItem Value="11">10


                    
                    <%--<asp:DropDownList ID="ddlState" runat="server" CssClass="validate[required] radio">
                                   
                                --%>
                </td>
            </tr>
            <tr>
                <td>
                    <span style="font-size: small; font-family: Arial">Built Up Area :</span><span style="color: #FF0000">* </span>
                </td>
                <td>
                    <asp:TextBox ID="txtarea" runat="server" CssClass="validate[required,custom[number]]"
                        Width="134px">
                     <asp:DropDownList ID="ddlarea" runat="server" CssClass="validate[required] radio">
                        <asp:ListItem Value="">--Select--
                        <asp:ListItem Value="fe">sqft
                        <asp:ListItem Value="me">sqm
                        <asp:ListItem Value="ya">sqyds
                    
                </td>
            </tr>
            <tr>
                <td valign="top">
                    <span style="font-size: small; font-family: Arial">Key Features :</span>
                </td>
                <td>
                    <asp:TextBox ID="txtfeatures" runat="server" Height="83px" TextMode="MultiLine" Width="270px">
                </td>
            </tr>
            <tr>
                <td>
                    <span style="font-size: small; font-family: Arial">Your Name :</span><span style="color: #FF0000">* </span>
                </td>
                <td>
                    <asp:TextBox ID="txtname" runat="server" CssClass="validate[required,custom[onlyLetterSp]]"
                        Width="270px">
                    <%--<asp:TextBox ID="txtZip" runat="server" CssClass="validate[required,custom[integer]] text-input" />--%>
                </td>
            </tr>
            <tr>
                <td>
                    <span style="font-size: small; font-family: Arial">Your Email :</span><span style="color: #FF0000">* </span>
                </td>
                <td>
                    <asp:TextBox ID="txtemail" runat="server" CssClass="validate[required,custom[email]"
                        Width="270px">
                    <%-- <input class="validate[required] checkbox" type="checkbox" id="agree" name="agree" />--%>
                </td>
            </tr>
            <tr>
                <td>
                    <span style="font-size: small; font-family: Arial">Mobile No :</span><span style="color: #FF0000">* </span>
                </td>
                <td>
                    <asp:TextBox ID="txtmobileno" runat="server" CssClass="validate[required,custom[onlyNumberSp]]"
                        Width="270px">
                </td>
            </tr>
            <tr>
                <td>
                </td>
                <td>
                    <asp:Button class="btn" ID="btnSubmit" runat="server" Text="Submit" Width="87px" 
                        onclick="btnSubmit_Click" />
                         
                    <asp:Button class="btn" ID="btnreset" runat="server" type="reset" UseSubmitBehavior="false" 
                        Text="Reset" Width="87px" onclick="btnreset_Click"/>
                </td>
            </tr>
            <tr>
                <td colspan="2">
                    <%-- <asp:Label ID="lblResult" runat="server" Font-Bold="true" />--%>
                </td>
            </tr>
        </table>

database Design:

SQL

create table tbl_post_requirement_buying
(
	Req_ID int identity(1,1)primary key,
	Property_Type varchar(50)not null,
	City_Id int references tbl_City(City_Id) not null,
	Locality_Id int references tbl_Locality(Locality_Id)not null,
	Min_Price varchar(15) not null,
	Max_Price varchar(15) not null,
	Bedrooms int null,
	Build_up_area varchar(20) not null,
	Key_Features varchar(100)null,
	Name varchar(50)not null,
	Email varchar(50)not null,
	Mobile_No numeric not null  
)

Posted 27-Sep-12 8:16am

Raj.Rautela

Updated 28-Sep-12 1:59am

v3

Add a Solution

Comments

Mohamed Mitwalli 27-Sep-12 14:54pm

what the error message says ?

Raj.Rautela 28-Sep-12 6:17am

thanks for improving my question sir...error is in parameters i think m passing wrong parameters...i got 3 ans here i'll try them and get back to u..thanks again...

Christiaan Rakowski 27-Sep-12 16:04pm

The code looks to be ok, could it be a problem with the SQL connection string?
Try putting a breakpoint at the ExecuteNonQuery and see if that returns a 1 or throws an exception.

Raj.Rautela 28-Sep-12 6:18am

thanks for the ans...i'll do that...

3 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

solanki.net · Accepted Answer · 2012-09-27T19:18:00

Solution 3

if u get this kind of problem then u should check some point

1. execute your procedure using hard code data .if procedure executing successfully it means problem in front end .
2. then whatever values u r passing from front end ,take all value and execute procedure with these values mean pass these values to procedure as hard code values .then u can find easily where u r doing mistake .

SQL

u have declared Min_Price and Max_Price only varchar but did not set size of varchar 
so it will accept only single character . but from front end from dropdown u r passing 2 digit number that is why its not stored in procedure because it will giver error like :String or binary data would be truncated.

SQL

@Min_Price varchar,
    @Max_Price varchar,

Posted 27-Sep-12 19:18pm

solanki.net

Updated 27-Sep-12 19:27pm

v2

Comments

Raj.Rautela 28-Sep-12 6:13am

thanks sir...i'll try your suggestion..thanks a lot..

solanki.net 28-Sep-12 6:55am

your welcome

Raj.Rautela 28-Sep-12 7:41am

now m getting input type not in corrent format
error
cmdins.Parameters.AddWithValue("@City_Id",Convert.ToInt32(ddlcity.SelectedItem.Text));

Raj.Rautela 28-Sep-12 7:45am

SqlConnection con = new SqlConnection(str);
SqlCommand cmdins = new SqlCommand("dbo.Post_Requirement_Buy", con);
cmdins.CommandType = CommandType.StoredProcedure;
cmdins.Parameters.AddWithValue("@Property_Type", ddlpropertytype.SelectedItem.Text);
cmdins.Parameters.AddWithValue("@City_Id",Convert.ToInt32(ddlcity.SelectedItem.Text));
cmdins.Parameters.AddWithValue("@Locality_Id",Convert.ToInt32(ddllocality.SelectedItem.Text));
cmdins.Parameters.AddWithValue("@Min_Price", ddlminprice.SelectedItem.Text);
cmdins.Parameters.AddWithValue("@Max_Price", ddlmaxprice.SelectedItem.Text);
cmdins.Parameters.AddWithValue("@Bedrooms", Convert.ToInt32(ddlbedrooms.SelectedItem.Text));
cmdins.Parameters.AddWithValue("@Build_up_area", txtarea.Text + ' ' + ddlarea.SelectedItem.Text);
cmdins.Parameters.AddWithValue("@Key_Features", txtfeatures.Text);
cmdins.Parameters.AddWithValue("@Name", txtname.Text);
cmdins.Parameters.AddWithValue("@Email", txtemail.Text);
cmdins.Parameters.AddWithValue("@Mobile_No", txtmobileno.Text);

con.Open();

cmdins.ExecuteNonQuery();

lblmessage.Text = "Submitted Sucessfully";

con.Close();

solanki.net 28-Sep-12 8:14am

Because u trying to convert Description of dropdown into integer so its not possible
cmdins.Parameters.AddWithValue("@City_Id",Convert.ToInt32(ddlcity.SelectedItem.Text));

ddlcity.SelectedItem.Text)// it return text Description of dropdown

u should try this

cmdins.Parameters.AddWithValue("@City_Id",Convert.ToInt32(ddlcity.SelectedValue.Tostring()));

Raj.Rautela 28-Sep-12 8:16am

thanks sir...i'll try this also..

Ganesh Nikam · Accepted Answer · 2012-09-27T18:34:00

Solution 2

hiii,

hey dude
i think you are wrong at this point

1) cmdins.Parameters.AddWithValue("@Build_up_area", txtarea.Text + " " + ddlarea.SelectedItem.Value);

you are trying to add varchar value to float which will give you error

so change datatype of build_up_area as varchar it will work fine

2) and change this

SQL

cmdins.Parameters.AddWithValue("@City_Id", ddlcity.SelectedItem.Text);
         cmdins.Parameters.AddWithValue("@Locality_Id", ddllocality.SelectedItem.Value);
        
         cmdins.Parameters.AddWithValue("@Bedrooms", ddlbedrooms.SelectedItem.Value);
        
To

<pre lang="sql">cmdins.Parameters.AddWithValue("@City_Id", Convert.ToInt32(ddlcity.SelectedItem.Text));
           cmdins.Parameters.AddWithValue("@Locality_Id", Convert.ToInt32(ddllocality.SelectedItem.Value));
           
           cmdins.Parameters.AddWithValue("@Bedrooms", Convert.ToInt32(ddlbedrooms.SelectedItem.Value));

where dropdown ddlcity contains city id(integer values) as text

have happy coding

Posted 27-Sep-12 18:34pm

Ganesh Nikam

Comments

Raj.Rautela 28-Sep-12 6:14am

thanks sir...i'll try your suggestion..thanks a lot..

Ganesh Nikam 28-Sep-12 6:29am

always welcome raj

Raj.Rautela 28-Sep-12 7:23am

sir tried your ans it is giving error input string not in corrent format.
cmdins.Parameters.AddWithValue("@City_Id",Convert.ToInt32(ddlcity.SelectedItem.Text));

Raj.Rautela 28-Sep-12 7:45am

SqlConnection con = new SqlConnection(str);
SqlCommand cmdins = new SqlCommand("dbo.Post_Requirement_Buy", con);
cmdins.CommandType = CommandType.StoredProcedure;
cmdins.Parameters.AddWithValue("@Property_Type", ddlpropertytype.SelectedItem.Text);
cmdins.Parameters.AddWithValue("@City_Id",Convert.ToInt32(ddlcity.SelectedItem.Text));
cmdins.Parameters.AddWithValue("@Locality_Id",Convert.ToInt32(ddllocality.SelectedItem.Text));
cmdins.Parameters.AddWithValue("@Min_Price", ddlminprice.SelectedItem.Text);
cmdins.Parameters.AddWithValue("@Max_Price", ddlmaxprice.SelectedItem.Text);
cmdins.Parameters.AddWithValue("@Bedrooms", Convert.ToInt32(ddlbedrooms.SelectedItem.Text));
cmdins.Parameters.AddWithValue("@Build_up_area", txtarea.Text + ' ' + ddlarea.SelectedItem.Text);
cmdins.Parameters.AddWithValue("@Key_Features", txtfeatures.Text);
cmdins.Parameters.AddWithValue("@Name", txtname.Text);
cmdins.Parameters.AddWithValue("@Email", txtemail.Text);
cmdins.Parameters.AddWithValue("@Mobile_No", txtmobileno.Text);

con.Open();

cmdins.ExecuteNonQuery();

lblmessage.Text = "Submitted Sucessfully";

con.Close();

Ganesh Nikam 28-Sep-12 7:31am

please tell mi ddlcity.SelectedItem.Text values

Raj.Rautela 28-Sep-12 7:47am

sir wait i'll pasete code how m filling drop down..
SqlConnection cn = new SqlConnection(str);
SqlCommand cmd = new SqlCommand("select * from tbl_City", cn);
cn.Open();
SqlDataReader dr = cmd.ExecuteReader();
while (dr.Read() == true)
{
ddlcity.Items.Add(new ListItem(dr["City_Name"].ToString(),dr["City_Id"].ToString()));
}
dr.Close();
cn.Close();

Raj.Rautela 28-Sep-12 7:49am

m getting value mumbia..when debugged the code line by line..

Ganesh Nikam 28-Sep-12 7:47am

just tel mi ddlcity values

Ganesh Nikam 28-Sep-12 7:53am

just change cmdins.Parameters.AddWithValue("@City_Id",Convert.ToInt32(ddlcity.SelectedItem.Text)); to

cmdins.Parameters.AddWithValue("@City_Id",Convert.ToInt32(ddlcity.SelectedItem.Value));

Ganesh Nikam 28-Sep-12 7:56am

hey got it?

Raj.Rautela 28-Sep-12 8:01am

pls wait sir i'll try...

Raj.Rautela 28-Sep-12 8:04am

got it sir thank u so much....

Raj.Rautela 28-Sep-12 8:04am

but pls tell me where i was wrong....

Ganesh Nikam 28-Sep-12 8:08am

hey when you are selecting ddlcity.selecteditem.text you will get city name which gives conversion error. when you use ddlcity.selecteditem.value you will get city id which will we expect .

Raj.Rautela 28-Sep-12 8:10am

thank u sir...

Lakhan Aanjana · Accepted Answer · 2012-09-27T13:19:00

Hi Raj I have Tested Your code....with some changes...please remove try and catch

u will definitely find the error's cause . ...seems that ur passing wrong parameters .(there is problem in your parameter ...).

Hope It will help You....

here is my code which work fine..at my end...

//try
        //{
            SqlConnection con = new SqlConnection(@"Data Source=TITAN-165\ECC;Initial Catalog=SmsDatabase;Integrated Security=True");
            SqlCommand cmdins = new SqlCommand("dbo.Post_Requirement_Buy", con);
          cmdins.CommandType = CommandType.StoredProcedure;
          // cmdins.CommandType=comman
            cmdins.Parameters.AddWithValue("@Property_Type", "lakhan");
            cmdins.Parameters.AddWithValue("@City_Id",  1);
            cmdins.Parameters.AddWithValue("@Locality_Id",1);
            cmdins.Parameters.AddWithValue("@Min_Price",3);
            cmdins.Parameters.AddWithValue("@Max_Price",4);
            cmdins.Parameters.AddWithValue("@Bedrooms",5);
            cmdins.Parameters.AddWithValue("@Build_up_area",22);
            cmdins.Parameters.AddWithValue("@Key_Features", "lakhan");
            cmdins.Parameters.AddWithValue("@Name",  "lakhan");
            cmdins.Parameters.AddWithValue("@Email",  "lakhanp22@gmail.com");
            cmdins.Parameters.AddWithValue("@Mobile_No", "1235645666");


            con.Open();
            cmdins.ExecuteNonQuery();

           // lblmessage.Text = "Submitted Sucessfully";

            con.Close();
       // }
       // catch (Exception ex)
       // {
       ////  Response.Write(Exception ex)
       // }
 
    }

there is no need to make change in store procedure

Please Let Me know ur feedback

inserting data using storeprocedure

3 solutions

Solution 3

Solution 2

Solution 1

Add your solution here

Preview 0

Existing Members

...or Join us