使用文本框值更新记录 [英] update records using textbox value
问题描述
我已经从数据表检索数据到文本框，我已经创建了一个带有下一个按钮的系统导航，直到这里它还可以。
现在我想要更新此文本框中的数据并将它们保存到数据库。
i有代码这个但是导致错误..每次更改文本框中的值..数据表的所有记录存储相同的值
what我做错了???
这里是我的代码：
I have retrieve data from datatable to textbox and i have created a system navigation with a next button and its ok until here.
now i want to update data in this textbox and save them to the database.
i have code this but results me with an error.. everytime a change a value in a textbox.. all the records of the datatable store the same values
what have i done wrong???
here is my code:
namespace Csharp
{
public partial class prova1 : System.Web.UI.Page
{
private DataTable GetData()
{
DataTable dt = new DataTable();
SqlConnection connection = new SqlConnection(ConfigurationManager.ConnectionStrings["ProvaConnectionString"].ConnectionString);
try
{
connection.Open();
SqlCommand sqlCmd = new SqlCommand("Select * From prova", connection);
SqlCommand sqlCmd2 = new SqlCommand("UPDATE Customer SET Code=''" + txt_kodi.Value + "'', Tel=''" + txt_tel.Value + "'', Mobile = ''" + txt_mobile.Value + "''");
SqlDataAdapter sqlDa = new SqlDataAdapter(sqlCmd);
sqlDa.Fill(dt);
}
catch (System.Data.SqlClient.SqlException ex)
{
string msg = "Fetch Error:";
msg += ex.Message;
throw new Exception(msg);
}
finally
{
connection.Close();
}
return dt;
}
protected void Page_Load(object sender, EventArgs e)
{
if (!Page.IsPostBack)
{
txt_kodi.Disabled = true;
txt_persh.Disabled = true;
txt_pershholl.Disabled = true;
txt_tel.Disabled = true;
txt_mobile.Disabled = true;
DataTable dt = GetData();
Hidden_id.Value = "0";
if (dt.Rows.Count > 0)
{
// Populate the TextBox with the first entry on page load
txt_kodi.Value = dt.Rows[0]["Code"].ToString();
txt_tel.Value = dt.Rows[0]["Tel"].ToString();
txt_mobile.Value = dt.Rows[0]["Mobile"].ToString();
//Then we store the DataTable in Session so that we will NOT
//query the DB on every postbacks
Session["dt"] = dt;
}
}
}
protected void btn_next_Click(object sender, EventArgs e)
{
int rowIndex = 0;
if (Hidden_id.Value.Trim() != null)
{
rowIndex = Convert.ToInt32(Hidden_id.Value) + 1;
if (Session["dt"] != null)
{
DataTable dt = (DataTable)Session["dt"];
if (rowIndex < dt.Rows.Count)
{
//get the next row entry on Button Click by setting the Row Index
txt_kodi.Value = dt.Rows[rowIndex]["Code"].ToString();
txt_tel.Value = dt.Rows[rowIndex]["Tel"].ToString();
txt_mobile.Value = dt.Rows[rowIndex]["Mobile"].ToString();
Session["dt"] = dt;
Hidden_id.Value = rowIndex.ToString();
}
else
{ Response.Write("Last record!"); }
}
}
}
protected void btn_modify_Click(object sender, EventArgs e)
{
SqlConnection connection = new SqlConnection(ConfigurationManager.ConnectionStrings["ProvaConnectionString"].ConnectionString);
SqlCommand sqlCmd2 = new SqlCommand("UPDATE prova SET Code=''" + txt_kodi.Value + "'', Tel=''" + txt_tel.Value + "'', Mobile = ''" + txt_mobile.Value + "''");
SqlDataAdapter sqlDa = new SqlDataAdapter(sqlCmd2);
SqlCommandBuilder cmdBuilder = new SqlCommandBuilder();
cmdBuilder = new SqlCommandBuilder(sqlDa);
int rowIndex = 0;
if (Hidden_id.Value.Trim() != null)
{
rowIndex = Convert.ToInt32(Hidden_id.Value);
if (Session["dt"] != null)
{
DataTable dt = (DataTable)Session["dt"];
rowIndex = 0;
System.Data.DataRow dRow = dt.Rows[rowIndex];
dRow["Code"] = txt_kodi.Value;
dRow["Tel"] = txt_tel.Value;
dRow["Mobile"] = txt_mobile.Value;
connection.Open();
sqlCmd2.Connection = connection;
sqlCmd2.ExecuteNonQuery();
connection.Close();
Session["dt"] = dt;
Hidden_id.Value = rowIndex.ToString();
Response.Write("data save!");
}
}
}
}
推荐答案
你的更新声明需要是修改后只更新一行或多行
your update statement needs to be amended so that it only updates a particular row or rows
"UPDATE Customer SET Code=''" + txt_kodi.Value + "'', Tel=''" + txt_tel.Value + "'', Mobile = ''" + txt_mobile.Value + "''"
需要有一个where子句来阻止它更新整个表格
即
needs to have a where clause on it to stop it updating the entire table
i.e.
UPDATE Customer SET Code = ''" + txt_kodi.Value + "'', Tel=''" + txt_tel.Value + "'', Mobile = ''" + txt_mobile.Value + "'' WHERE CustomerID = 1
我还建议您也使用参数化查询你的代码对SQL注入更安全
参数化查询示例 [ ^ ]
编辑[更新以添加如何更新ADO.NET中的记录]
I would also recommend that you get into using parameterised queries too they will make your code more secure against SQL Injection
Parameterised Queries Example[^]
EDIT [Updated to add how to Update Records in ADO.NET]
using (SQLConnection db = new SQLConnection(mydatabaseString))
{
SqlCommand cmd = new SqlCommand(sqlString, db);
int RowsEffected = cmd.ExecuteNonQuery();
}
这篇关于使用文本框值更新记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
