SQL插入代码失败 - 我知道我很累但为什么? [英] SQL Insert Code fails - I know I am tired but why ?
本文介绍了SQL插入代码失败 - 我知道我很累但为什么?的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
/// < 摘要 >
/// 获取一个Error对象并将其值保存到数据库
/ // < / summary >
/// < span class =code-summarycomment>< param name =e > < / PARAM 跨度> < span class =code-summarycomment>>
public void SaveError(错误e)
{
// 将错误事件保存到db
string connectionString = Properties.Settings.Default.PlatinumDBConnectionString;
string cmdString =
INSERT INTO dbo.Errors(eName,eInstance,eMessage,eModule,eProcedure,eUserId,eUserMessage,ecName,ecCode,ecDescription,ecMessage)VALUES(@ val1,@ va2,@ val3,@ val4,@ val5,@ val6,@ val7 ,@的Val8,@ val9,@ val10,@ VAl11难)跨度>;
使用(SqlConnection conn = new SqlConnection(connectionString))
{
conn.Open();
使用(SqlCommand cmd = new SqlCommand(cmdString,conn))
{
// 为参数赋值
cmd.Parameters.AddWithValue( @ val1,e.Name);
cmd.Parameters.AddWithValue( @ val2,e .Instance);
cmd.Parameters.AddWithValue( @ val3,e.Message);
cmd.Parameters.AddWithValue( @ val4,e.Module);
cmd.Parameters.AddWithValue( @ val5,e.Procedure);
cmd.Parameters.AddWithValue( @ val6,e.UserId);
cmd.Parameters.AddWithValue( @ val7,e.UserMessage);
cmd.Parameters.AddWithValue( @ val8,e.ErrorCode.Name);
cmd.Parameters.AddWithValue( @ val9,e.ErrorCode.Code);
cmd.Parameters.AddWithValue( @ val10,e.ErrorCode.Description);
cmd.Parameters.AddWithValue( @ val11,e.ErrorCode.Message);
cmd.ExecuteNonQuery();
}
conn.Close();
}
}
#endregion
}
我添加了交易命令,但仍然没有创建行
public void SaveError(错误e)
{
// 将错误事件保存到db
string connectionString = Properties.Settings.Default.PlatinumDBConnectionString;
string cmdString =
INSERT INTO dbo.Errors(eName,eInstance,eMessage,eModule,eProcedure,eUserId,eUserMessage,ecName,ecCode,ecDescription,ecMessage)VALUES(@ val1,@ va2,@ val3,@ val4,@ val5,@ val6,@ val7 ,@的Val8,@ val9,@ val10,@ VAl11难)跨度>;
使用(SqlConnection conn = new SqlConnection(connectionString))
{
conn.Open();
SqlTransaction trans = conn.BeginTransaction();
使用(SqlCommand cmd = new SqlCommand(cmdString,conn))
{
cmd.Transaction = trans;
// 为参数赋值
cmd.Parameters.AddWithValue( @ val1,e.Name);
cmd.Parameters.AddWithValue( @ val2,e .Instance);
cmd.Parameters.AddWithValue( @ val3,e.Message);
cmd.Parameters.AddWithValue( @ val4,e.Module);
cmd.Parameters.AddWithValue( @ val5,e.Procedure);
cmd.Parameters.AddWithValue( @ val6,e.UserId);
cmd.Parameters.AddWithValue( @ val7,e.UserMessage);
cmd.Parameters.AddWithValue( @ val8,e.ErrorCode.Name);
cmd.Parameters.AddWithValue( @ val9,e.ErrorCode.Code);
cmd.Parameters.AddWithValue( @ val10,e.ErrorCode.Description);
cmd.Parameters.AddWithValue( @ val11,e.ErrorCode.Message);
cmd.ExecuteNonQuery();
trans.Commit();
}
conn.Close();
}
}
这是表中的服务器转储
USE [PlatinumDB]
GO
/ * *****对象:表[dbo]。[错误]脚本日期:13/03/2015 12:33:17 PM ****** /
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo]。[错误](
[Id] [ bigint ] IDENTITY ( 1 , 1 ) NOT NULL ,
[eName] [ nchar ]( 50 ) NULL ,
[eInstance] [ nchar ]( 50 ) NULL ,
[eMessage] [ nvarchar ](max) NULL ,
[eModule] [ nvarchar ]( 50 ) NULL ,
[eProcedure] [ nvarchar ]( 50 ) NULL ,
[eUserId] [ nvarchar ]( 50 ) NULL ,
[eUserMessage] [ nvarchar ](最大值) NULL ,
[ecName] [ nchar ]( 50 ) NULL ,
[ecCode] [ nchar ]( 50 ) NULL ,
[ecDescription] [ nvarchar ](max) NULL ,
[ecMessage] [ nvarchar ](max) NULL ,
CONSTRAINT [PK_Errors] PRIMARY KEY CLUSTERED
(
[Id] ASC
) WITH (PAD_INDEX = OFF ,STATISTICS_NORECOMPUTE = < span class =code-keyword> OFF ,IGNORE_DUP_KEY = OFF ,ALLOW_R OW_LOCKS = ON ,ALLOW_PAGE_LOCKS = ON ) ON [ PRIMARY ]
) ON [ PRIMARY ] TEXTIMAGE_ON [ PRIMARY ]
GO
解决方案
你在那里没有使用任何交易。连接关闭后,您的INSERT将回滚。我假设您知道如何使用交易而忘记它在这里?我试图找到一个质量示例来为您提供链接,但我发现的所有示例都有一些怪癖,即使在MSDN上也是如此。如果你需要一个例子,请发表评论,我在这里写下一个例子。
两个问题导致插入失败。
首先是错字SQL(感谢manchanx提醒我这个)
其次我需要将CommandType设置为Text;
使用(SqlCommand cmd = new SqlCommand(cmdString,conn))
{
cmd.CommandType = CommandType.Text;
感谢aarif moh shaikh提出此问题。
我很感谢你们花时间来帮助我。 :)
/// <summary>
/// Takes an Error object and saves its values to the database
/// </summary>
/// <param name="e"></param>
public void SaveError(Error e)
{
// Save error incident to db
string connectionString = Properties.Settings.Default.PlatinumDBConnectionString;
string cmdString =
"INSERT INTO dbo.Errors(eName,eInstance,eMessage,eModule,eProcedure,eUserId,eUserMessage,ecName,ecCode,ecDescription,ecMessage) VALUES (@val1, @va2, @val3,@val4,@val5,@val6,@val7,@val8,@val9,@val10,@val11)";
using (SqlConnection conn = new SqlConnection(connectionString))
{
conn.Open();
using (SqlCommand cmd = new SqlCommand(cmdString, conn))
{
// assign value to parameters
cmd.Parameters.AddWithValue("@val1", e.Name);
cmd.Parameters.AddWithValue("@val2", e.Instance);
cmd.Parameters.AddWithValue("@val3", e.Message);
cmd.Parameters.AddWithValue("@val4", e.Module);
cmd.Parameters.AddWithValue("@val5", e.Procedure);
cmd.Parameters.AddWithValue("@val6", e.UserId);
cmd.Parameters.AddWithValue("@val7", e.UserMessage);
cmd.Parameters.AddWithValue("@val8", e.ErrorCode.Name);
cmd.Parameters.AddWithValue("@val9", e.ErrorCode.Code);
cmd.Parameters.AddWithValue("@val10", e.ErrorCode.Description);
cmd.Parameters.AddWithValue("@val11", e.ErrorCode.Message);
cmd.ExecuteNonQuery();
}
conn.Close();
}
}
#endregion
}
I added the transaction command but still no row created
public void SaveError(Error e)
{
// Save error incident to db
string connectionString = Properties.Settings.Default.PlatinumDBConnectionString;
string cmdString =
"INSERT INTO dbo.Errors(eName,eInstance,eMessage,eModule,eProcedure,eUserId,eUserMessage,ecName,ecCode,ecDescription,ecMessage) VALUES (@val1, @va2, @val3,@val4,@val5,@val6,@val7,@val8,@val9,@val10,@val11)";
using (SqlConnection conn = new SqlConnection(connectionString))
{
conn.Open();
SqlTransaction trans = conn.BeginTransaction();
using (SqlCommand cmd = new SqlCommand(cmdString, conn))
{
cmd.Transaction = trans;
// assign value to parameters
cmd.Parameters.AddWithValue("@val1", e.Name);
cmd.Parameters.AddWithValue("@val2", e.Instance);
cmd.Parameters.AddWithValue("@val3", e.Message);
cmd.Parameters.AddWithValue("@val4", e.Module);
cmd.Parameters.AddWithValue("@val5", e.Procedure);
cmd.Parameters.AddWithValue("@val6", e.UserId);
cmd.Parameters.AddWithValue("@val7", e.UserMessage);
cmd.Parameters.AddWithValue("@val8", e.ErrorCode.Name);
cmd.Parameters.AddWithValue("@val9", e.ErrorCode.Code);
cmd.Parameters.AddWithValue("@val10", e.ErrorCode.Description);
cmd.Parameters.AddWithValue("@val11", e.ErrorCode.Message);
cmd.ExecuteNonQuery();
trans.Commit();
}
conn.Close();
}
}
Here is the server dump from the table
USE [PlatinumDB]
GO
/****** Object: Table [dbo].[Errors] Script Date: 13/03/2015 12:33:17 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Errors](
[Id] [bigint] IDENTITY(1,1) NOT NULL,
[eName] [nchar](50) NULL,
[eInstance] [nchar](50) NULL,
[eMessage] [nvarchar](max) NULL,
[eModule] [nvarchar](50) NULL,
[eProcedure] [nvarchar](50) NULL,
[eUserId] [nvarchar](50) NULL,
[eUserMessage] [nvarchar](max) NULL,
[ecName] [nchar](50) NULL,
[ecCode] [nchar](50) NULL,
[ecDescription] [nvarchar](max) NULL,
[ecMessage] [nvarchar](max) NULL,
CONSTRAINT [PK_Errors] PRIMARY KEY CLUSTERED
(
[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
解决方案
You're using no transaction there. Your INSERT gets rolled back once the connection closes. I assume you know how to use a transaction and just forgot it here? I tried to find a quality example to provide you a link but all examples I found have some quirks, even on MSDN. If you need an example, please leave a comment and I write down an example here.
Two issues were causing the Insert to fail.
Firstly the typo in the SQL (Thank You manchanx for alerting me to this )
Secondly I needed to have the CommandType set to Text;
using (SqlCommand cmd = new SqlCommand(cmdString, conn)) { cmd.CommandType = CommandType.Text;
Thanks to aarif moh shaikh for raising this issue.
I appreciate the time taken by you guys to assist me. :)
这篇关于SQL插入代码失败 - 我知道我很累但为什么?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文