将数据插入表时出错 [英] Error while inserting data into the table

问题描述

Helo Friends，



我在asp.net中执行以下语句时出错。

SQL中ExpDate的数据类型是日期。之前运行良好。



错误：将varchar数据类型转换为日期时间数据类型会导致超出范围的值。

Helo Friends,

I getting error while executing following statement in asp.net.
The datatype of ExpDate in SQL is date.This was working well before.

Error:The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.

string str = string.Format("insert into Expense(VoucherNo,ExpDate,Expid,ExpSubid,Reimbursetype,Amount,Advance,BillNo,Destination,Km,From1,To1,Others,Narration,StaffCode,Status,CompId,ClientBill,CLTId)values('" + txtvoucher.Text + "',convert(datetime,'" + txtexpdate.Text + "',103)," + Dropdownexp.SelectedValue + "," + DropDownexpsub.SelectedValue + ",'" + Rstr + "','" + txtamount.Text + "','" + txtadvance.Text + "','"+ Btxt +"','"+ Dtxt +"','" + Ktxt + "','" + Ftxt + "','" + Ttxt + "','"+ Otxt +"','" + txtnarr.Text + "','" + ViewState["staffcode"].ToString() + "','" + str2 + "','" + ViewState["compid"].ToString() + "','N','" + drpclient0.SelectedValue + "')");
                                
                                int res = db.CommandExecute(str);

如何解决这个错误..请帮助我

How to solve this error..Please help me

推荐答案

请不要介意，但你的代码都搞砸了。此外，它是sql注入广泛开放。请改用参数化查询。例如：

Please don't mind but your code is all messed up. Plus it is wide open for sql injection. Use parameterized query instead. For example:

DateTime ExpDateValue = new DateTime();//Just a guess. Assign date from control on the page or whatever.
string VoucherNoValue = txtVoucher.Text;//Just a guess. Assign date from control on the page or whatever.
 
//Similarly declare all the variables. Keep Datatype exactly as the database table field

string str = "insert into Expense(VoucherNo,ExpDate,Expid,ExpSubid,Reimbursetype,Amount,Advance,BillNo,Destination,Km,From1,To1,Others,Narration,StaffCode,Status,CompId,ClientBill,CLTId)values(@VoucherNo,@ExpDate,@Expid,@ExpSubid,@Reimbursetype,@Amount,@Advance,@BillNo,@Destination,@Km,@From1,@To1,@Others,@Narration,@StaffCode,@Status,@CompId,@ClientBill,@CLTId)";

SqlCommand com = new SqlCommand(str,yourConnectionObject);
com.CommandType = CommandType.Text;
//Now pass the values to parameters like:
com.Parameters.Add(new SqlParameter("VoucherNo",VoucherNoValue));
com.Parameters.Add(new SqlParameter("ExpDate",ExpDateValue));
//Keep adding parameters.

这篇关于将数据插入表时出错的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！