使用storeprocedure插入数据 [英] inserting data using storeprocedure
本文介绍了使用storeprocedure插入数据的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
先生m使用这些代码通过storeprocedure将值插入数据库，但值未插入数据库m没弄错..pls事先感谢您.
m从数据库中填充城市和地点下拉列表，并将那里的ID插入数据库，但无法插入...
m输入错误的输入类型的格式不正确，如下所示.
存放程序:
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:
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:
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:
<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:
database Design:
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
)推荐答案
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...
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
there is no need to make change in store procedure
Please Let Me know ur feedback
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
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
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
where dropdown ddlcity contains city id(integer values) as text
have happy coding
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 .
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 .
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.
@Min_Price varchar,
@Max_Price varchar,这篇关于使用storeprocedure插入数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
