我的insert方法在其中一列中返回NULL值 [英] My insert method is returning a NULL value in one of the columns
问题描述
我的insert方法在其中一列中返回NULL值。在其中一列中,从表中检索值。当我在SQL Server中执行它时,它工作得很好。当我使用存储过程在Visual Studio中插入此行时。根据用户选择的内容,列的值是另一个表中的主键。
我尝试过:
创建一个存储过程,它在SQL Server中完美执行。我的Web应用程序是一个3层应用程序,具有数据访问层,业务逻辑层,表示层。我写的代码只填写表中5列中的4列,例外是一列返回空值
这个 我的代码 表示层
使用系统;
使用 System.Collections.Generic;
使用 System.Linq;
使用 System.Web;
使用 System.Web.UI;
使用 System.Web.UI.WebControls;
使用 System.Data;
使用 System.Data.SqlClient;
使用 System.Data.Sql;
使用 System.Configuration;
使用 BLL;
使用 DAL;
使用 System.Drawing;
命名空间 XcellIT
{
public partial class WebForm1:System.Web.UI.Page
{
private BusinessLogicLayer bll = new BusinessLogicLayer();
受保护 void Page_Load( object sender,EventArgs e)
{
if (IsPostBack!= true )
{
// FillAthlete
ddlAthleteName.DataSource = bll.FillAthlete();
ddlAthleteName.DataTextField = 名称;
ddlAthleteName.DataValueField = UserID;
ddlAthleteName.DataBind();
ddlAthleteName.Items.Insert( 0 , new ListItem( SELECT, NA跨度>));
// FillDiscipline
ddlDisciplineName.DataSource = bll.FillDiscipline ();
ddlDisciplineName.DataTextField = 名称;
ddlDisciplineName.DataValueField = DisciplineID;
ddlDisciplineName.DataBind();
ddlDisciplineName.Items.Insert( 0 , new ListItem( SELECT, NA跨度>));
// FillMeeting
ddlMeeting.DataSource = bll.FillMeeting ();
ddlMeeting.DataTextField = MeetingName;
ddlMeeting.DataValueField = MeetingName;
ddlMeeting.DataBind();
ddlMeeting.Items.Insert( 0 , new ListItem( SELECT, NA跨度>));
lblResult.Visible = false ;
lblResult.Text = ;
}
}
受保护 void Add_Click( object sender,EventArgs e)
{
try
{
ATHLETE_STATISTICS ar = new ATHLETE_STATISTICS();
ar.AthleteID = Convert.ToInt32(ddlAthleteName.SelectedValue);
ar.DisciplineID = Convert.ToInt32(ddlDisciplineName.SelectedValue);
ar.MeetingID = Convert.ToInt32(ddlMeeting.SelectedIndex);
ar.Result = Convert.ToDouble(txtResult.Text);
ar.Placement = Convert.ToInt32(ddlPlaced.SelectedIndex);
BusinessLogicLayer bl = new BusinessLogicLayer();
尝试
{
如果(bl。 AddAthleteStatsnew(ar)== true )
{
lblResult.Text = 已添加;
Response.Redirect( Home.aspx);
}
else
{
lblResult.Text = 发生错误。未成功添加;
}
}
catch (Exception exc)
{
lblResult.Visible = true ;
lblResult.Text = lblResult.Text + exc.ToString();
}
}
catch (SqlException exc)
{
lblResult.Visible = true ;
lblResult.Text = lblResult.Text + exc.ToString();
}
catch (Exception exc)
{
lblResult.Visible = 真跨度>;
lblResult.Text = lblResult.Text + exc.ToString();
}
}
}
}
业务逻辑层
public bool AddAthleteStatsnew(ATHLETE_STATISTICS ar)
{
< span class =code-keyword> return db.AddAthleteStatisticsNew(ar);
}
数据访问层
public bool AddAthleteStatisticsNew(ATHLETE_STATISTICS amr)
{
SqlParameter [] pars = new SqlParameter []
{
new SqlParameter( @ AthleteID,amr.AthleteID),
// new SqlParameter( @MeetingDisciplineID,amr.MeetingDisciplineID),
new SqlParameter( @ MeetingID,amr.MeetingID),
new SqlParameter( @ DisciplineID,amr.DisciplineID),
new SqlParameter( @ FinishingResult,amr.Result),
new SqlParameter( @ Placement,amr.Placement)
};
return DBHelper.ExecuteNonQuery( spAthleteStatsnew ,CommandType.StoredProcedure,pars);
}
存储过程
INSERT INTO ATHLETE_MEETING_DISCIPLINE_RESULT(AthleteID,MeetingDisciplineID,FinishingResult,Placement)
VALUES(@AleleteID,(选择me.MeetingDisciplineID来自MEETING_EVENTS就像我在哪里.MeetingID = @MemeID和me.DisciplineID = @DisciplineID),@ FinishingResult,@ Placement)
这对您来说很容易调试,您真的需要自己完成它。你可以比发布它的时间更快地完成它。
你说你试图插入5列,但你的SQL是:
INSERT INTO ATHLETE_MEETING_DISCIPLINE_RESULT(AthleteID,MeetingDisciplineID,FinishingResult ,放置)
这只有4列,所以当然没有什么可以进入第5列。 ;)
My insert method is returning a NULL value in one of the columns. In one of columns the value is retrieved from a table. When I executed it in SQL Server it worked perfectly. When I used the stored procedure to insert this row in Visual Studio. The value of the column is a primary key in another table based on what the user has selected.
What I have tried:
Created a stored procedure, it executes perfectly in SQL Server. My web application is a 3-tier application with a Data Access Layer, Business Logic Layer, Presentation Layer. The code I wrote only fills in 4 of the 5 columns in the table, the exception is one column that returns a null value
This is my code in the Presentation Layer
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;
using System.Data.Sql;
using System.Configuration;
using BLL;
using DAL;
using System.Drawing;
namespace XcellIT
{
public partial class WebForm1 : System.Web.UI.Page
{
private BusinessLogicLayer bll = new BusinessLogicLayer();
protected void Page_Load(object sender, EventArgs e)
{
if (IsPostBack != true)
{
// FillAthlete
ddlAthleteName.DataSource = bll.FillAthlete();
ddlAthleteName.DataTextField = "Name";
ddlAthleteName.DataValueField = "UserID";
ddlAthleteName.DataBind();
ddlAthleteName.Items.Insert(0, new ListItem("SELECT", "NA"));
//FillDiscipline
ddlDisciplineName.DataSource = bll.FillDiscipline();
ddlDisciplineName.DataTextField = "Name";
ddlDisciplineName.DataValueField = "DisciplineID";
ddlDisciplineName.DataBind();
ddlDisciplineName.Items.Insert(0, new ListItem("SELECT", "NA"));
// FillMeeting
ddlMeeting.DataSource = bll.FillMeeting();
ddlMeeting.DataTextField = "MeetingName";
ddlMeeting.DataValueField = "MeetingName";
ddlMeeting.DataBind();
ddlMeeting.Items.Insert(0, new ListItem("SELECT", "NA"));
lblResult.Visible = false;
lblResult.Text = "";
}
}
protected void Add_Click(object sender, EventArgs e)
{
try
{
ATHLETE_STATISTICS ar = new ATHLETE_STATISTICS();
ar.AthleteID = Convert.ToInt32(ddlAthleteName.SelectedValue);
ar.DisciplineID = Convert.ToInt32(ddlDisciplineName.SelectedValue);
ar.MeetingID = Convert.ToInt32(ddlMeeting.SelectedIndex);
ar.Result = Convert.ToDouble(txtResult.Text);
ar.Placement = Convert.ToInt32(ddlPlaced.SelectedIndex);
BusinessLogicLayer bl = new BusinessLogicLayer();
try
{
if (bl.AddAthleteStatsnew(ar) == true)
{
lblResult.Text = "Added";
Response.Redirect("Home.aspx");
}
else
{
lblResult.Text = "Error occured. Not successfully added";
}
}
catch(Exception exc)
{
lblResult.Visible = true;
lblResult.Text = lblResult.Text + exc.ToString();
}
}
catch(SqlException exc)
{
lblResult.Visible = true;
lblResult.Text = lblResult.Text + exc.ToString();
}
catch (Exception exc)
{
lblResult.Visible = true;
lblResult.Text = lblResult.Text + exc.ToString();
}
}
}
}
Business Logic Layer
public bool AddAthleteStatsnew(ATHLETE_STATISTICS ar)
{
return db.AddAthleteStatisticsNew(ar);
}
Data Access Layer
public bool AddAthleteStatisticsNew(ATHLETE_STATISTICS amr)
{
SqlParameter[] pars = new SqlParameter[]
{
new SqlParameter("@AthleteID" , amr.AthleteID),
//new SqlParameter("@MeetingDisciplineID" , amr.MeetingDisciplineID),
new SqlParameter("@MeetingID" ,amr.MeetingID),
new SqlParameter("@DisciplineID",amr.DisciplineID),
new SqlParameter("@FinishingResult",amr.Result),
new SqlParameter("@Placement", amr.Placement)
};
return DBHelper.ExecuteNonQuery("spAthleteStatsnew", CommandType.StoredProcedure, pars);
}
The Stored Procedure
INSERT INTO ATHLETE_MEETING_DISCIPLINE_RESULT(AthleteID,MeetingDisciplineID,FinishingResult,Placement)
VALUES(@AthleteID,(SELECT me.MeetingDisciplineID FROM MEETING_EVENTS As me WHERE me.MeetingID= @MeetingID AND me.DisciplineID= @DisciplineID),@FinishingResult,@Placement)
This is so easy for you to debug, you really need to work through it on your own. You could have had it done faster than the time it took to post this.
You said you are trying to insert into 5 columns but your SQL is:
INSERT INTO ATHLETE_MEETING_DISCIPLINE_RESULT(AthleteID,MeetingDisciplineID,FinishingResult,Placement)
That is only 4 columns so of course nothing is going to go into the 5th column. ;)
这篇关于我的insert方法在其中一列中返回NULL值的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!