的ExecuteScalar();随着SCOPE_IDENTITY()生成" System.InvalidCastException:指定强制转换为无效" [英] ExecuteScalar(); With scope_identity() Generating "System.InvalidCastException: Specified cast is not valid"
问题描述
我已经拥有它接受各种数据(通过文本框和的CheckBoxList)的一种形式,在click事件,他们插入所有的数据到一个表,然后选择在SCOPE_IDENTITY其存储在一个变量来使用它的插入使用循环的CheckBoxList项目到另一个表
据许多答案和例子,这应该很好地工作..但它给了我这个错误:
异常详细信息:System.InvalidCastException:指定强制转换为无效。
第66行:INT NewBrandId =(INT)comm.ExecuteScalar();
下面是我的LinkButton方法code:
保护无效lnkbtnUploadAndSubmit_Click(对象发件人,EventArgs的)
{
SqlConnection的康恩=新的SqlConnection(ConfigurationManager.ConnectionStrings [MOODbCenterConnection]的ConnectionString。);
SqlCommand的通讯=新的SqlCommand(INSERT INTO品牌(名优产品,BrandLogo,BrandWebsite,IsBrandVisible)VALUES(@Name,@Logo,@Website,@IsVisible);选择SCOPE_IDENTITY();,康涅狄格州);
comm.Parameters.Add(@名称,System.Data.SqlDbType.NVarChar,50);
。comm.Parameters [@名]值= txtbxBrandName.Text;
comm.Parameters.Add(@标志,System.Data.SqlDbType.Text);
comm.Parameters [@标志]值= fileuploadLogo.PostedFile.FileName。
comm.Parameters.Add(@网站,System.Data.SqlDbType.Text);
comm.Parameters [@网站]值= txtbxWebsite.Text。
comm.Parameters.Add(@可见性,System.Data.SqlDbType.Bit);
。comm.Parameters [@可见性]值= chkbxIsPublished.Checked;
conn.Open();
INT NewBrandId =(INT)comm.ExecuteScalar();
conn.Close();
的foreach(在chkbxlstCuisines.Items列表项丽)
{
如果(li.Selected)
{
conn.Open();
SqlCommand的COMM2 =新的SqlCommand(INSERT INTO BrandCuisines(CuisineId,BrandId)VALUES(@CuisineId,@NewBrandId),康涅狄格州);
comm2.Parameters.Add(@ CuisineId,System.Data.SqlDbType.Int);
comm2.Parameters [@ CuisineId]值= li.Value。
comm2.Parameters.Add(@ NewBrandId,System.Data.SqlDbType.Int);
comm2.Parameters [@ NewBrandId]值= NewBrandId。
comm2.ExecuteNonQuery();
conn.Close();
}
}
}
编辑奇怪的查询工作正常,当我直接在Visual Studio中的SQL服务器EX preSS运行它!
怪异的解释这个奇怪的问题
首先解决由alexb:
我做到了:)我的SQL Server 2008的回报 System.Decimal包装成对象。 尝试使用System.Convert.ToInt32 而不是演员。
例:INT NewBrandId = System.Convert.ToInt32(comm.ExecuteScalar());
由我第二个解决方法:
,而不是使用SELECT SCOPE_IDENTITY();我使用选择 BrandId从品牌WHERE BrandId = @@标识,它给你更多 controlm但我个人prefare的 第一个解决方案
N.B:SELECT @@身份;也要 工作就好了
谢谢你们,我希望它帮助别人!
究竟 comm.ExecuteScalar()
的回报?
也许它返回长
或双
包装成对象
。< BR>
记不太清了,但好像我曾面临一些1-2年前。
I've have a form which accept various data (through textboxes and a checkboxlist) and on the click event they insert all the data into a table and selects the scope_identity then store it in a variable to use it in the insertion of the checkboxlist items using a loop into another table
according to many answers and examples this should work perfectly!..but it gives me this error :
Exception Details: System.InvalidCastException: Specified cast is not valid.
Line 66: int NewBrandId = (int)comm.ExecuteScalar();
Here's my linkbutton method code :
protected void lnkbtnUploadAndSubmit_Click(object sender, EventArgs e)
{
SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["MOODbCenterConnection"].ConnectionString);
SqlCommand comm = new SqlCommand("INSERT INTO Brands (BrandName, BrandLogo, BrandWebsite, IsBrandVisible) VALUES (@Name, @Logo, @Website, @IsVisible); SELECT scope_identity();", conn);
comm.Parameters.Add("@Name", System.Data.SqlDbType.NVarChar, 50);
comm.Parameters["@Name"].Value = txtbxBrandName.Text;
comm.Parameters.Add("@Logo", System.Data.SqlDbType.Text);
comm.Parameters["@Logo"].Value = fileuploadLogo.PostedFile.FileName;
comm.Parameters.Add("@Website", System.Data.SqlDbType.Text);
comm.Parameters["@Website"].Value = txtbxWebsite.Text;
comm.Parameters.Add("@IsVisible", System.Data.SqlDbType.Bit);
comm.Parameters["@IsVisible"].Value = chkbxIsPublished.Checked;
conn.Open();
int NewBrandId = (int)comm.ExecuteScalar();
conn.Close();
foreach (ListItem li in chkbxlstCuisines.Items)
{
if (li.Selected)
{
conn.Open();
SqlCommand comm2 = new SqlCommand("INSERT INTO BrandCuisines (CuisineId, BrandId) VALUES (@CuisineId, @NewBrandId)", conn);
comm2.Parameters.Add("@CuisineId", System.Data.SqlDbType.Int);
comm2.Parameters["@CuisineId"].Value = li.Value;
comm2.Parameters.Add("@NewBrandId", System.Data.SqlDbType.Int);
comm2.Parameters["@NewBrandId"].Value = NewBrandId;
comm2.ExecuteNonQuery();
conn.Close();
}
}
}
Edit Strangely the query works fine when I run it directly in the sql server express of the visual studio!
THE Weird Explanation For This Weird Problem
First Solution by alexb :
I did it :) My sql server 2008 returns System.Decimal packed into 'object'. Try to use System.Convert.ToInt32 instead of cast.
Example : int NewBrandId = System.Convert.ToInt32(comm.ExecuteScalar());
Second Solution by me :
instead of using "SELECT scope_identity();" I used "SELECT BrandId FROM Brands WHERE BrandId = @@Identity;" it gives you even more controlm but I personally prefare the first solution
N.B : "SELECT @@Identity;" would also work just fine
Thanks guys and I hope it help others!
What exactly comm.ExecuteScalar()
returns?
Maybe it returns long
or double
packed into object
.
can't remember exactly, but seems like I had faced something 1-2 years ago.
这篇关于的ExecuteScalar();随着SCOPE_IDENTITY()生成&QUOT; System.InvalidCastException:指定强制转换为无效&QUOT;的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!