c# - SQL查询将数据插入到asp.net中包含外键的表中
问题描述
这是包含添加按钮的代码,单击该按钮时应将文本框中的数据添加到此处的账单表中crid
,crname
,cid
,cname
和truckid
是外键。
单击添加按钮时,数据未添加到表中,并且没有显示错误,因此我不确定是什么原因造成的。
using System;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Web.UI;
namespace project
{
public partial class bill : System.Web.UI.Page
{
string strcon = ConfigurationManager.ConnectionStrings["con"].ConnectionString;
protected void Page_Load(object sender, EventArgs e)
{
GridView2.DataBind();
}
//add
protected void Button1_Click(object sender, EventArgs e)
{
if (BillCheck())
{
ScriptManager.RegisterClientScriptBlock(this, this.GetType(), "alertMessage",
"alert('This bill already exists. Please generate a new bill.')", true);
}
else
{
addNewBill();
}
}
//user defined function
void addNewBill()
{
if (TextBox1.Text.Trim().Equals(""))
{
ScriptManager.RegisterClientScriptBlock(this, this.GetType(), "alertMessage",
"alert(GR no cannot be blank')", true);
}
else
{
try
{
SqlConnection con = new SqlConnection(strcon);
if (con.State == ConnectionState.Closed)
{
con.Open();
}
SqlCommand cmd = new SqlCommand(
"insert into bill (GRNo,Date,from,to,crid,crname,cid,cname,package," +
"description,HSNcode,privatemark,invoiceno,value,truckid,paymentmode,actual,charged,amount)" +
" (@GRNo,@Date,@from,@to,(select crid from Consignor where crid='" + TextBox4.Text.Trim() +
"';)," +
"(select crname from Consignor where crname='" + TextBox5.Text.Trim() + "';)," +
"(select cid from Consignee where cid='" + TextBox8.Text.Trim() + "';)," +
"(select cname from Consignee where cname='" + TextBox9.Text.Trim() +
"';),@package,@description,@HSNcode,@privatemark," +
"@invoiceno,@value,@paymentmode,@actual,@charged,@amount);", con);
cmd.Parameters.AddWithValue("@GRNo", TextBox1.Text.Trim());
cmd.Parameters.AddWithValue("@Date", TextBox2.Text.Trim());
cmd.Parameters.AddWithValue("@from", TextBox7.Text.Trim());
cmd.Parameters.AddWithValue("@to", TextBox3.Text.Trim());
cmd.Parameters.AddWithValue("@crid", TextBox4.Text.Trim());
cmd.Parameters.AddWithValue("@crname", TextBox5.Text.Trim());
cmd.Parameters.AddWithValue("@cid", TextBox8.Text.Trim());
cmd.Parameters.AddWithValue("@cname", TextBox9.Text.Trim());
cmd.Parameters.AddWithValue("@package", TextBox6.Text.Trim());
cmd.Parameters.AddWithValue("@description", TextBox10.Text.Trim());
cmd.Parameters.AddWithValue("@HSNcode", TextBox11.Text.Trim());
cmd.Parameters.AddWithValue("@privatemark", TextBox12.Text.Trim());
cmd.Parameters.AddWithValue("@invoiceno", TextBox13.Text.Trim());
cmd.Parameters.AddWithValue("@value", TextBox14.Text.Trim());
cmd.Parameters.AddWithValue("@truckid", DropDownList1.SelectedItem.Value);
cmd.Parameters.AddWithValue("@paymentmode", DropDownList2.SelectedItem.Value);
cmd.Parameters.AddWithValue("@actual", TextBox21.Text.Trim());
cmd.Parameters.AddWithValue("@charged", TextBox22.Text.Trim());
cmd.Parameters.AddWithValue("@amount", TextBox23.Text.Trim());
cmd.ExecuteNonQuery();
con.Close();
ScriptManager.RegisterClientScriptBlock(this, this.GetType(), "alertMessage",
"alert('New Bill added Successfully!')", true);
clearForm();
GridView2.DataBind();
}
catch (Exception ex)
{
ScriptManager.RegisterClientScriptBlock(this, this.GetType(), "alertMessage",
"alert('" + ex.Message + "')", true);
}
}
}
public bool BillCheck()
{
try
{
SqlConnection con = new SqlConnection(strcon);
if (con.State == ConnectionState.Closed)
{
con.Open();
}
SqlCommand cmd = new SqlCommand("Select * from bill where GRNo='" + TextBox1.Text.Trim() + "';", con);
SqlDataAdapter da = new SqlDataAdapter(cmd);
DataTable dt = new DataTable();
da.Fill(dt);
if (dt.Rows.Count >= 1)
{
return true;
}
else
{
return false;
}
}
catch (Exception ex)
{
ScriptManager.RegisterClientScriptBlock(this, this.GetType(), "alertMessage",
"alert('" + ex.Message + "')", true);
return false;
}
}
void clearForm()
{
TextBox1.Text = "";
TextBox2.Text = "";
TextBox3.Text = "";
TextBox4.Text = "";
TextBox5.Text = "";
TextBox6.Text = "";
TextBox7.Text = "";
TextBox8.Text = "";
TextBox9.Text = "";
TextBox10.Text = "";
TextBox11.Text = "";
TextBox12.Text = "";
TextBox13.Text = "";
TextBox14.Text = "";
TextBox21.Text = "";
TextBox22.Text = "";
TextBox23.Text = "";
DropDownList1.SelectedValue = "";
DropDownList2.SelectedValue = "";
}
}
}
我尝试用值编写查询,但没有它仍然无法正常工作。我已尝试检查我的数据库以查看是否添加了行我已尝试从 select 语句中删除分号它仍然无法正常工作。 这是我的账单表列的图像。
解决方案
我认为你的 sql st 有问题,你不能像以前那样使用 param,试试这个
SqlCommand cmd = new SqlCommand("insert into bill (GRNo,Date,from,to,crid,crname,cid,cname,package," +"description,HSNcode,privatemark,invoiceno,value,truckid,paymentmode,actual,charged,amount)" + " (@GRNo,@Date,@from,@to,(select crid from Consignor where crid=@crid;)," +"(select crname from Consignor where crname=@crname;)," +"(select cid from Consignee where cid=@cid;)," +"(select cname from Consignee where cname=@cname;),@package,@description,@HSNcode,@privatemark," + "@invoiceno,@value,@paymentmode,@actual,@charged,@amount);", con);
推荐阅读
- hyperledger-fabric - 在 Hyperledger Fabric 中将另一个对等方加入我的频道时出现“错误:提案失败”
- jupyter-notebook - 如何在 Jupyter Lab 的 Markdown 笔记本中启用方程式编号?
- mysql - Hibernate 使用 ManyToOne 注释保存新条目
- google-apps-script - 从脚本属性和字符串获取电子表格时的不同行为
- android - 为什么 Dagger 无法识别 AppModule 中的 @Provides?
- html - 如何从 BootstrapCDN 自定义 Bootstrap 4.3
- pdf-generation - 带有投影仪演示的 RMarkdown 会生成具有错误边距的 ggplot 图。我应该怎么办?
- amazon-web-services - AWS CloudFormation:获取 ApiGateway 资源的 PathPart 属性
- r - 如何在ggplot2中正确格式化百分比图轴标签和条形图
- excel - 添加文件链接,使用 Split() 分隔文件名 - 错误:对象变量或未设置块变量