C＃ASP.NET中的新SQL错误

本文介绍了C＃ASP.NET中的新SQL错误的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

原始错误已修复，现在已更改为.... System.Data.SqlClient.SqlException（0x80131904）：无法将值NULL插入列'ID'，表 'C：\USERS\BRIAN\DESKTOP\WEBSITE（DONT CHANGE）\THOMAS县公立WORKS\THOMAS县公立WORKS\APP_DATA\SIGNDATABASE.MDF.dbo.SignDatabase';列不允许空值。 INSERT失败。该语句已终止。在System.Data.SqlClient.SqlConnection.OnError（SqlException异常，布尔breakConnection，Action`1 wrapCloseInAction）在System.Data.SqlClient.SqlInternalConnection.OnError（SqlException异常，布尔breakConnection，Action`1 wrapCloseInAction）在System.Data.SqlClient的System.Data.SqlClient上的System.Data.SqlClient.TdsParser.TryRun（RunBehavior runBehavior，SqlCommand cmdHandler，SqlDataReader dataStream，BulkCopySimpleResultSet bulkCopyHandler，TdsParserStateObject stateObj，Boolean& dataReady）中的.TdsParser.ThrowExceptionAndWarning（TdsParserStateObject stateObj，Boolean callerHasConnectionLock，Boolean asyncClose） System.Data.SqlClient.SqlCommand.RunExecuteReaderTds中的.SqlCommand.FinishExecuteReader（SqlDataReader ds，RunBehavior runBehavior，String resetOptionsString）（CommandBehavior cmdBehavior，RunBehavior runBehavior，Boolean returnStream，Boolean async，Int32 timeout，Task& task，Boolean asyncWrite，SqlDataReader ds，布尔值describeParameterEncryptionRequest）at System.Data.SqlClient.SqlCommand.RunExecuteReader（CommandBehavior cmdBehavior，RunBehavior runBehavior，Boolean returnStream，String method，TaskCompletionSource`1 completion，Int32 timeout，Task&任务，布尔asyncWrite）在System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery（TaskCompletionSource`1完成，字符串methodName中，布尔sendToPipe，的Int32超时，布尔asyncWrite）在System.Data.SqlClient.SqlCommand.ExecuteNonQuery（）在Thomas_County_Public_Works.Sign_Database。 Button1_Click（Object sender，EventArgs e）位于C：\ Users \ brian \Desktop \Website（Dont Change）\托马斯县公共工程\托马斯县公共工程\签名Database.aspx.cs：第27行ClientConnectionId：da77b3bd-eef6-46b4-9833-92c9466232e3错误号码：515，州：2，班级：16 这是我所做的修复。但是在解决了这些问题之后，现在抛出上面的代码。

namespace Thomas_County_Public_Works { public partial class Sign_Database：System.Web.UI.Page { protected void Page_Load（ object sender，EventArgs e） { } protected void Button1_Click（ object sender，EventArgs e） { try { SqlConnection con = new SqlConnection（ConfigurationManager.ConnectionStrings [ DBCONNECT]的ConnectionString）。 con.Open（）; string insert = 插入SignDatabase（ [日期]，[道路名称]，[位置]，[签名ID]，[MUTCD]，[代码]，[工作描述]，[尺寸长度]，[尺寸宽度]，[衬背]，[薄片]， [支持类型]，[支持材料]，[签名损坏]，[支持损坏]，[签名修复]，[支持修复]，[工作订单号]，[阻塞]，[评级日期]，[签名条件]， [支持条件]，[背景反射率]，[文字符号反射率]，[大符号]，[安装在头上]，[无反射率]）值（@ Date，@ RoadName，@ Location，@ SignID，@ MUTCD，@代码，@ DescriptionofWork，@ SizeLength，@ SizeWidth，@后盾，@塑料布，@ SupportType，@ SupportMaterial，@ SignDamage，@ SupportDamage，@ SignRepair，@ SupportRepair，@ WorkOrderNumber，@梗阻，@ RATINGDATE，@ SignCondition，@ SupportCondition， @ BackgroundReflectivity，@ TextSymbolReflectivity，@ LargeSign，@ MountedOverhead，@ NoReflectivityTaken）; SqlCommand cmd = new SqlCommand（insert，con）; cmd.Parameters.AddWithValue（ @ Date，TextBox1.Text）; cmd.Parameters.AddWithValue（ @ RoadName，TextBox3.Text）; cmd.Parameters.AddWithValue（ @ Location，TextBox2.Text）; cmd.Parameters.AddWithValue（ @ SignID，TextBox4.Text）; cmd.Parameters.AddWithValue（ @ MUTCD，TextBox5.Text）; cmd.Parameters.AddWithValue（ @ Code，DropDownList1.Text）; cmd.Parameters.AddWithValue（ @ DescriptionofWork，TextBox6.Text）; cmd.Parameters.AddWithValue（ @ SizeLength，TextBox7.Text）; cmd.Parameters.AddWithValue（ @ SizeWidth，TextBox8.Text）; cmd.Parameters.AddWithValue（ @ Backing，DropDownList2.Text）; cmd.Parameters.AddWithValue（ @ Sheeting，DropDownList3.Text）; cmd.Parameters.AddWithValue（ @ SupportType，DropDownList4.Text）; cmd.Parameters.AddWithValue（ @ SupportMaterial，DropDownList5.Text）; cmd.Parameters.AddWithValue（ @ SignDamage，DropDownList6.Text）; cmd.Parameters.AddWithValue（ @ SupportDamage，DropDownList7.Text）; cmd.Parameters.AddWithValue（ @ SignRepair，DropDownList8.Text）; cmd.Parameters.AddWithValue（ @ SupportRepair，DropDownList9.Text）; cmd.Parameters.AddWithValue（ @ WorkOrderNumber，TextBox9.Text）; cmd.Parameters.AddWithValue（ @ Obstruction，TextBox10.Text）; cmd.Parameters.AddWithValue（ @ RatingDate，TextBox11.Text）; cmd.Parameters.AddWithValue（ @ SignCondition，DropDownList10.Text）; cmd.Parameters.AddWithValue（ @ SupportCondition，DropDownList11.Text）; cmd.Parameters.AddWithValue（ @ BackgroundReflectivity，TextBox12.Text）; cmd.Parameters.AddWithValue（ @ TextSymbolReflectivity，TextBox13.Text）; cmd.Parameters.AddWithValue（ @ LargeSign，DropDownList12.Text）; cmd.Parameters.AddWithValue（ @ MountedOverhead，DropDownList13.Text）; cmd.Parameters.AddWithValue（ @ NoReflectivityTaken，DropDownList14.Text）; cmd.ExecuteNonQuery（）; con.Close（）; } catch （例外情况） { Response.Write（ex）; } } } }

我尝试了什么： 我试过看过这个视频 与数据库连接（SQL server database）在ASP.NET中使用Visual Studio 2012-15 - YouTube [ ^ ]

解决方案

您不能只使用我所知道的任何语言在变量名称中添加空格： SQL参数没有什么不同。改变这个：

... @ MUTCD，@ Code，@工作描述，@尺寸长度，......

对此：

... @ MUTCD，@ Code，@ DescriptionOfWork，@ SizeLength，...

并对所有其他参数执行相同操作。 然后在设置值时执行匹配的名称;

... cmd.Parameters.AddWithValue（@ DescriptionOfWork，TextBox6。文本）; cmd.Parameters.AddWithValue（@ SizeLength，TextBox7.Text）; cmd.Parameters.AddWithValue（@ SizeWidth，TextBox8.Text）; ...

BTW：帮自己一个忙，并停止使用Visual Studio默认名称 - 你可能还记得TextBox8是今天的手机号码，但是当你必须在三周内修改它时，你会这样吗？使用描述性名称 - 例如tbMobileNo - 您的代码变得更容易阅读，更自我记录，更易于维护 - 并且编码速度更快，因为Intellisense可以通过三次击键来tbMobile，其中TextBox8需要思考大约8个击键...

space 不允许命令参数

cmd.Parameters.AddWithValue（ @Road Name ，TextBox3.Text）;

删除所有参数的空间并尝试。它应该工作

cmd.Parameters.AddWithValue（ @ RoadName ，TextBox3.Text）;

类似于所有值

values （ @ Date ， @ Road 姓名

， 将其更改为

值（ @ Date ， @ RoadName ，

The Original error was fixed now its changed to.... System.Data.SqlClient.SqlException (0x80131904): Cannot insert the value NULL into column 'Id', table 'C:\USERS\BRIAN\DESKTOP\WEBSITE(DONT CHANGE)\THOMAS COUNTY PUBLIC WORKS\THOMAS COUNTY PUBLIC WORKS\APP_DATA\SIGNDATABASE.MDF.dbo.SignDatabase'; column does not allow nulls. INSERT fails. The statement has been terminated. at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction) at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction) at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose) at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady) at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString) at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async, Int32 timeout, Task& task, Boolean asyncWrite, SqlDataReader ds, Boolean describeParameterEncryptionRequest) at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, TaskCompletionSource`1 completion, Int32 timeout, Task& task, Boolean asyncWrite) at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(TaskCompletionSource`1 completion, String methodName, Boolean sendToPipe, Int32 timeout, Boolean asyncWrite) at System.Data.SqlClient.SqlCommand.ExecuteNonQuery() at Thomas_County_Public_Works.Sign_Database.Button1_Click(Object sender, EventArgs e) in C:\Users\brian\Desktop\Website(Dont Change)\Thomas County Public Works\Thomas County Public Works\Sign Database.aspx.cs:line 27 ClientConnectionId:da77b3bd-eef6-46b4-9833-92c9466232e3 Error Number:515,State:2,Class:16 This are the fixes I have made. But after fixing those issues now its throwing the above code.

namespace Thomas_County_Public_Works { public partial class Sign_Database : System.Web.UI.Page { protected void Page_Load(object sender, EventArgs e) { } protected void Button1_Click(object sender, EventArgs e) { try { SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["dbconnect"].ConnectionString); con.Open(); string insert = "Insert into SignDatabase([Date],[Road Name],[Location],[Sign ID],[MUTCD],[Code],[Description of Work],[Size Length],[Size Width],[Backing],[Sheeting],[Support Type],[Support Material],[Sign Damage],[Support Damage],[Sign Repair],[Support Repair],[Work Order Number],[Obstruction],[Rating Date],[Sign Condition],[Support Condition],[Background Reflectivity],[Text Symbol Reflectivity],[Large Sign],[Mounted Overhead],[No Reflectivity Taken]) values(@Date,@RoadName,@Location,@SignID,@MUTCD,@Code,@DescriptionofWork,@SizeLength,@SizeWidth,@Backing,@Sheeting,@SupportType,@SupportMaterial,@SignDamage,@SupportDamage,@SignRepair,@SupportRepair,@WorkOrderNumber,@Obstruction,@RatingDate,@SignCondition,@SupportCondition,@BackgroundReflectivity,@TextSymbolReflectivity,@LargeSign,@MountedOverhead,@NoReflectivityTaken)"; SqlCommand cmd = new SqlCommand(insert, con); cmd.Parameters.AddWithValue("@Date", TextBox1.Text); cmd.Parameters.AddWithValue("@RoadName", TextBox3.Text); cmd.Parameters.AddWithValue("@Location", TextBox2.Text); cmd.Parameters.AddWithValue("@SignID", TextBox4.Text); cmd.Parameters.AddWithValue("@MUTCD", TextBox5.Text); cmd.Parameters.AddWithValue("@Code", DropDownList1.Text); cmd.Parameters.AddWithValue("@DescriptionofWork", TextBox6.Text); cmd.Parameters.AddWithValue("@SizeLength", TextBox7.Text); cmd.Parameters.AddWithValue("@SizeWidth", TextBox8.Text); cmd.Parameters.AddWithValue("@Backing", DropDownList2.Text); cmd.Parameters.AddWithValue("@Sheeting", DropDownList3.Text); cmd.Parameters.AddWithValue("@SupportType", DropDownList4.Text); cmd.Parameters.AddWithValue("@SupportMaterial", DropDownList5.Text); cmd.Parameters.AddWithValue("@SignDamage", DropDownList6.Text); cmd.Parameters.AddWithValue("@SupportDamage", DropDownList7.Text); cmd.Parameters.AddWithValue("@SignRepair", DropDownList8.Text); cmd.Parameters.AddWithValue("@SupportRepair", DropDownList9.Text); cmd.Parameters.AddWithValue("@WorkOrderNumber", TextBox9.Text); cmd.Parameters.AddWithValue("@Obstruction", TextBox10.Text); cmd.Parameters.AddWithValue("@RatingDate", TextBox11.Text); cmd.Parameters.AddWithValue("@SignCondition", DropDownList10.Text); cmd.Parameters.AddWithValue("@SupportCondition", DropDownList11.Text); cmd.Parameters.AddWithValue("@BackgroundReflectivity", TextBox12.Text); cmd.Parameters.AddWithValue("@TextSymbolReflectivity", TextBox13.Text); cmd.Parameters.AddWithValue("@LargeSign", DropDownList12.Text); cmd.Parameters.AddWithValue("@MountedOverhead", DropDownList13.Text); cmd.Parameters.AddWithValue("@NoReflectivityTaken", DropDownList14.Text); cmd.ExecuteNonQuery(); con.Close(); } catch (Exception ex) { Response.Write(ex); } } } }

What I have tried: Ive tried following this video Connection With Database (SQL server database) In ASP.NET using Visual Studio 2012-15 - YouTube[^]

解决方案

You can't just put spaces in variables names in any language I know: and SQL parameters are no different. Change this:

... @MUTCD,@Code,@Description of Work,@Size Length, ...

TO this:

... @MUTCD,@Code,@DescriptionOfWork,@SizeLength, ...

And do the same for all other parameters. Then do the matching names when you are setting the values;

... cmd.Parameters.AddWithValue("@DescriptionOfWork", TextBox6.Text); cmd.Parameters.AddWithValue("@SizeLength", TextBox7.Text); cmd.Parameters.AddWithValue("@SizeWidth", TextBox8.Text); ...

BTW: Do yourself a favour, and stop using Visual Studio default names for everything - you may remember that "TextBox8" is the mobile number today, but when you have to modify it in three weeks time, will you then? Use descriptive names - "tbMobileNo" for example - and your code becomes easier to read, more self documenting, easier to maintain - and surprisingly quicker to code because Intellisense can get to to "tbMobile" in three keystrokes, where "TextBox8" takes thinking about and 8 keystrokes...

space is not allowed for the command paramters

cmd.Parameters.AddWithValue("@Road Name", TextBox3.Text);

remove the space for all the params and try. it should work

cmd.Parameters.AddWithValue("@RoadName", TextBox3.Text);

similarly for all the values

values(@Date,@Road Name

, change it to

values(@Date,@RoadName,