添加和编辑按钮不能在sql和C#中工作 [英] add and edit button dosen't work in sql and C#
问题描述
这是我的代码:
使用系统;
使用 System.Collections.Generic;
使用 System.ComponentModel;
使用 System.Data;
使用 System.Drawing;
使用 System.Linq;
使用 System.Text;
使用 System.Windows.Forms;
使用 System.Data.SqlClient;
名称空间电影
{
public partial class Form1:Form
{
string SqlStr;
SqlCommand SqlCmd;
SqlDataAdapter SqlDa;
SqlDataReader SqlDr;
public Form1()
{
InitializeComponent();
}
private void BtnAdd_Click( object sender,EventArgs e)
{
SqlConnection CN = new SqlConnection( Data Source =(local); Initial Catalog = Movies; Integrated Security = True);
尝试
{
CN.Open();
SqlStr = 插入电影([名称];
SqlStr = SqlStr + ,[Director];
SqlStr = SqlStr + < span class =code-string> ,[Year];
SqlStr = SqlStr + ,[Type]);
SqlStr = SqlStr + 值(' + TxtName.Text + ',' + TxtDirector.Text + < span class =code-string>',;
SqlStr = SqlStr + ' + TxtYear.Text + ',;
SqlStr = SqlStr + ' + TxtType.Text + ',;
SqlCmd = new SqlCommand(SqlStr,CN);
MessageBox.Show( باموفقیتثبتشد) ;
清除();
}
finally
{
CN.Close();
}
}
public void Clear()
{
TxtName。 Text = ;
TxtDirector.Text = ;
TxtYear.Text = ;
TxtType.Text = ;
}
private void BtnEdit_Click(< span class =code-keyword> object sender,EventArgs e)
{
SqlConnection CN = new SqlConnection( Data Source =(local); Initial Catalog = Movies; Integrated Security = True);
尝试
{
CN.Open();
SqlStr = @ UPDATE Movies
SET Name = @Name,
Director = @Director,
Year = @Year,
Type = @ Type,
WHERE Id_Movies = @id;
SqlCmd = new SqlCommand(SqlStr,CN);
SqlCmd.Parameters.AddWithValue( @ Name,TxtName.Text);
SqlCmd.Parameters.AddWithValue( @ Director,TxtDirector.Text);
SqlCmd.Parameters.AddWithValue( @ Year,TxtYear.Text);
SqlCmd.Parameters.AddWithValue( @ Type,TxtType.Text);
SqlCmd.Parameters.AddWithValue( @ Id,TxtId.Text);
MessageBox.Show( 已成功编辑。) ;
清除();
}
最后
{
CN.Close();
}
}
私有 void TxtSearch_Click( object sender,EventArgs e)
{
SqlConnection CN = new SqlConnection(< span class =code-string> Data Source =(local); Initial Catalog = Movies; Integrated Security = True);
尝试
{
CN.Open();
SqlStr = 选择姓名,董事,年份,类型;
SqlStr = SqlStr + 来自电影;
SqlStr = SqlStr + 其中Id_Movies =' + TxtId.Text + ';
SqlCmd = new SqlCommand(SqlStr,CN);
SqlDr = SqlCmd.ExecuteReader();
SqlDr.Read();
if (!SqlDr.HasRows)
{
MessageBox.Show( اطلاعاتموردنظروجودندارد);
}
else
{
TxtName.Text = SqlDr [ 名称]。ToString();
TxtDirector.Text = SqlDr [ Director]。ToString();
TxtYear.Text = SqlDr [ Year]。ToString();
TxtType.Text = SqlDr [ Type]。ToString();
}
}
最后
{
CN.Close();
}
}
}
}
当我点击调试并更改文本并点击编辑它说是编辑的secsussefull但在sql server中没有任何变化。为什么?如何解决?
你永远不会执行命令。例如插入
SqlStr = 插入电影([名称];
SqlStr = SqlStr + ,[Director];
SqlStr = SqlStr + ,[Year];
SqlStr = SqlStr + ,[Type]);
SqlStr = SqlStr + 值(' + TxtName.Text + ',' + TxtDirector.Text + < span class =code-string>',;
SqlStr = SqlStr + ' + TxtYear.Text + ',;
SqlStr = SqlStr + ' + TxtType.Text + ',;
SqlCmd = new SqlCommand(SqlStr,CN);
MessageBox.Show( باموفقیتثبتشد) ;
清除();
你应该调用ExecuteNonQuery .Lieke
SqlStr = < span class =code-string> < span class =code-string>插入电影([名称];
SqlStr = SqlStr + ,[Director];
SqlStr = SqlStr + ,[Year];
SqlStr = SqlStr + ,[Type]);
SqlStr = SqlStr + 值(' + TxtName.Text + ',' + TxtDirector.Text + < span class =code-string>',;
SqlStr = SqlStr + ' + TxtYear.Text + ',;
SqlStr = SqlStr + ' + TxtType.Text + ',;
SqlCmd = new SqlCommand(SqlStr,CN);
SqlCmd.ExecuteNonQuery(); // 缺少这个
MessageBox.Show( باموفقیتثبتشد);
清除( );>
附注1:我也可以看到你按照指导更正了更新,但是在我写的时候,总是使用参数,所以对插入和选择做同样的事情。
附注2:始终使用try..catch块。修改数据时可能会出现很多问题,因此进行适当的错误处理是件好事
附注3:最好始终使用事务
希望这会有所帮助:)
这是代码的更新版本,修复了一些问题:
使用系统;
使用 System.Collections.Generic;
使用 System.ComponentModel;
使用 System.Data;
使用 System.Drawing;
使用 System.Linq;
使用 System.Text;
使用 System.Windows.Forms;
使用 System.Data.SqlClient;
名称空间电影
{
public partial class Form1:Form
{
public Form1()
{
InitializeComponent();
}
public void Clear()
{
TxtName。 Text = ;
TxtDirector.Text = ;
TxtYear.Text = ;
TxtType.Text = ;
}
private static SqlConnection CreateConnection()
{
return new SqlConnection( 数据源=(本地);初始目录=电影;集成安全性=真);
}
private void BtnAdd_Click( object sender,EventArgs e)
{
using (SqlConnection connection = CreateConnection())
使用(SqlCommand command = new SqlCommand( 插入电影([姓名],[导演],[年份],[类型])VALUES(@ Name,@ Director,@ Yes,@ Type),connection))
{
command.Parameters.AddWithValue( @ Name,TxtName.Text);
command.Parameters.AddWithValue( @ Director,TxtDirector.Text);
command.Parameters.AddWithValue( @ Year,TxtYear.Text);
command.Parameters.AddWithValue( @ Type,TxtType.Text);
尝试
{
connection.Open();
command.ExecuteNonQuery();
MessageBox.Show( 已成功插入。);
清除();
}
catch (SqlException ex)
{
MessageBox.Show(ex.Message);
// TODO:在某处记录错误
}
}
}
private void BtnEdit_Click( object sender,EventArgs e)
{
using (SqlConnection connection = CreateConnection())
使用(SqlCommand command = new SqlCommand( UPDATE Movies SET Name = @ Name,Director = @Director,Year = @Year,Type = @Type WHERE Id_Movies = @id,connection))
{
command.Parameters.AddWithValue( @ Name,TxtName.Text) ;
command.Parameters.AddWithValue( @ Director,TxtDirector.Text);
command.Parameters.AddWithValue( @ Year,TxtYear.Text);
command.Parameters.AddWithValue( @ Type,TxtType.Text);
command.Parameters.AddWithValue( @ Id,TxtId.Text);
尝试
{
connection.Open();
command.ExecuteNonQuery();
MessageBox.Show( 已成功编辑。);
清除();
}
catch (SqlException ex)
{
MessageBox.Show(ex.Message);
// TODO:在某处记录错误
}
}
}
private void TxtSearch_Click( object sender,EventArgs e)
{
using (SqlConnection connection = CreateConnection())
使用(SqlCommand command = new SqlCommand( SELECT Name,Director,Year,Type FROM Movies WHERE Id_Movies = @id,connection))
{
command.Parameters.AddWithValue ( @ Id,TxtId.Text);
尝试
{
connection.Open();
使用(SqlDataReader reader = command.ExecuteReader(CommandBehavior.CloseConnection))
{
if (!reader.Read())
{
MessageBox.Show( 找不到电影。);
}
else
{
TxtName.Text =( string )reader [ 名称];
TxtDirector.Text =( string )reader [ 导演跨度>];
TxtYear.Text =( string )reader [ 年跨度>];
TxtType.Text =( string )reader [ 类型跨度>];
}
}
}
catch (SqlException ex)
{
MessageBox.Show (ex.Message);
// TODO:在某处记录错误
}
}
}
}
}
- 使用参数化查询无处不在以避免 SQL Injection [ ^ ]漏洞;
- 执行查询,而不是仅创建它们然后扔掉它们;
- 从数据库中捕获并显示/记录异常;
- 包装实现<的对象code> IDisposable 在
中使用
块;- 不要使用字段来存储生命周期有限的对象单个方法调用;
- 提取重复的代码以将
SqlConnection
对象创建为单独的方法;- 传递
CommandBehavi或.CloseConnection
到ExecuteReader
方法,以便在SqlDataReader $ c $时立即关闭连接c>被处理;
- 检查
读取
方法的返回值,而不是忽略它,然后调用HasRows
this is my code :
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.SqlClient;
namespace Film
{
public partial class Form1 : Form
{
string SqlStr;
SqlCommand SqlCmd;
SqlDataAdapter SqlDa;
SqlDataReader SqlDr;
public Form1()
{
InitializeComponent();
}
private void BtnAdd_Click(object sender, EventArgs e)
{
SqlConnection CN = new SqlConnection("Data Source=(local);Initial Catalog=Movies;Integrated Security=True");
try
{
CN.Open();
SqlStr = "Insert into Movies( [Name]";
SqlStr = SqlStr + " ,[Director]";
SqlStr = SqlStr + " ,[Year]";
SqlStr = SqlStr + " ,[Type] ) ";
SqlStr = SqlStr + " Values('" + TxtName.Text + "','" + TxtDirector.Text + "',";
SqlStr = SqlStr + "'" + TxtYear.Text + "',";
SqlStr = SqlStr + "'" + TxtType.Text + "',";
SqlCmd = new SqlCommand(SqlStr, CN);
MessageBox.Show("با موفقیت ثبت شد");
Clear();
}
finally
{
CN.Close();
}
}
public void Clear()
{
TxtName. Text = "";
TxtDirector.Text = "";
TxtYear.Text = "";
TxtType.Text = "";
}
private void BtnEdit_Click(object sender, EventArgs e)
{
SqlConnection CN = new SqlConnection("Data Source=(local);Initial Catalog=Movies;Integrated Security=True");
try
{
CN.Open();
SqlStr = @"UPDATE Movies
SET Name = @Name,
Director = @Director,
Year = @Year,
Type = @Type,
WHERE Id_Movies = @id";
SqlCmd = new SqlCommand(SqlStr, CN);
SqlCmd.Parameters.AddWithValue("@Name", TxtName.Text);
SqlCmd.Parameters.AddWithValue("@Director", TxtDirector.Text);
SqlCmd.Parameters.AddWithValue("@Year", TxtYear.Text);
SqlCmd.Parameters.AddWithValue("@Type", TxtType.Text);
SqlCmd.Parameters.AddWithValue("@Id", TxtId.Text);
MessageBox.Show("Edited successfully.");
Clear();
}
finally
{
CN.Close();
}
}
private void TxtSearch_Click(object sender, EventArgs e)
{
SqlConnection CN = new SqlConnection("Data Source=(local);Initial Catalog=Movies;Integrated Security=True");
try
{
CN.Open();
SqlStr = " select Name,Director,Year,Type ";
SqlStr = SqlStr +" from Movies" ;
SqlStr = SqlStr + " where Id_Movies='"+TxtId.Text+"' ";
SqlCmd = new SqlCommand(SqlStr, CN);
SqlDr = SqlCmd.ExecuteReader();
SqlDr.Read();
if (!SqlDr.HasRows)
{
MessageBox.Show("اطلاعات مورد نظر وجود ندارد");
}
else
{
TxtName.Text = SqlDr["Name"].ToString();
TxtDirector.Text = SqlDr["Director"].ToString();
TxtYear.Text = SqlDr["Year"].ToString();
TxtType.Text = SqlDr["Type"].ToString();
}
}
finally
{
CN.Close();
}
}
}
}
when i click on debug and change the texts and click on edit it say's edited secsussefull but in sql server nothing change . why ? how to fix it ?
You never execute the command. For example in insert
SqlStr = "Insert into Movies( [Name]"; SqlStr = SqlStr + " ,[Director]"; SqlStr = SqlStr + " ,[Year]"; SqlStr = SqlStr + " ,[Type] ) "; SqlStr = SqlStr + " Values('" + TxtName.Text + "','" + TxtDirector.Text + "',"; SqlStr = SqlStr + "'" + TxtYear.Text + "',"; SqlStr = SqlStr + "'" + TxtType.Text + "',"; SqlCmd = new SqlCommand(SqlStr, CN); MessageBox.Show("با موفقیت ثبت شد"); Clear();
you should call the ExecuteNonQuery. LIke
SqlStr = "Insert into Movies( [Name]"; SqlStr = SqlStr + " ,[Director]"; SqlStr = SqlStr + " ,[Year]"; SqlStr = SqlStr + " ,[Type] ) "; SqlStr = SqlStr + " Values('" + TxtName.Text + "','" + TxtDirector.Text + "',"; SqlStr = SqlStr + "'" + TxtYear.Text + "',"; SqlStr = SqlStr + "'" + TxtType.Text + "',"; SqlCmd = new SqlCommand(SqlStr, CN); SqlCmd.ExecuteNonQuery(); // This was missing MessageBox.Show("با موفقیت ثبت شد"); Clear();
Side note 1: Also I can see that you corrected the update as guided but as I wrote, always use parameters so do the same thing for insert and select.
Side note 2: Always use try..catch blocks. Many things can go wrong when modifying the data so it's a good thing to have proper error handling
Side note 3: And it would be best to always use transactions
Hope this helps :)
Here's an updated version of your code with some of the issues fixed:
using System; using System.Collections.Generic; using System.ComponentModel; using System.Data; using System.Drawing; using System.Linq; using System.Text; using System.Windows.Forms; using System.Data.SqlClient; namespace Film { public partial class Form1 : Form { public Form1() { InitializeComponent(); } public void Clear() { TxtName. Text = ""; TxtDirector.Text = ""; TxtYear.Text = ""; TxtType.Text = ""; } private static SqlConnection CreateConnection() { return new SqlConnection("Data Source=(local);Initial Catalog=Movies;Integrated Security=True"); } private void BtnAdd_Click(object sender, EventArgs e) { using (SqlConnection connection = CreateConnection()) using (SqlCommand command = new SqlCommand("Insert into Movies([Name], [Director], [Year], [Type]) VALUES (@Name, @Director, @Year, @Type)", connection)) { command.Parameters.AddWithValue("@Name", TxtName.Text); command.Parameters.AddWithValue("@Director", TxtDirector.Text); command.Parameters.AddWithValue("@Year", TxtYear.Text); command.Parameters.AddWithValue("@Type", TxtType.Text); try { connection.Open(); command.ExecuteNonQuery(); MessageBox.Show("Inserted successfully."); Clear(); } catch (SqlException ex) { MessageBox.Show(ex.Message); // TODO: Log the error somewhere } } } private void BtnEdit_Click(object sender, EventArgs e) { using (SqlConnection connection = CreateConnection()) using (SqlCommand command = new SqlCommand("UPDATE Movies SET Name = @Name, Director = @Director, Year = @Year, Type = @Type WHERE Id_Movies = @id", connection)) { command.Parameters.AddWithValue("@Name", TxtName.Text); command.Parameters.AddWithValue("@Director", TxtDirector.Text); command.Parameters.AddWithValue("@Year", TxtYear.Text); command.Parameters.AddWithValue("@Type", TxtType.Text); command.Parameters.AddWithValue("@Id", TxtId.Text); try { connection.Open(); command.ExecuteNonQuery(); MessageBox.Show("Edited successfully."); Clear(); } catch (SqlException ex) { MessageBox.Show(ex.Message); // TODO: Log the error somewhere } } } private void TxtSearch_Click(object sender, EventArgs e) { using (SqlConnection connection = CreateConnection()) using (SqlCommand command = new SqlCommand("SELECT Name, Director, Year, Type FROM Movies WHERE Id_Movies = @id", connection)) { command.Parameters.AddWithValue("@Id", TxtId.Text); try { connection.Open(); using (SqlDataReader reader = command.ExecuteReader(CommandBehavior.CloseConnection)) { if (!reader.Read()) { MessageBox.Show("Movie not found."); } else { TxtName.Text = (string)reader["Name"]; TxtDirector.Text = (string)reader["Director"]; TxtYear.Text = (string)reader["Year"]; TxtType.Text = (string)reader["Type"]; } } } catch (SqlException ex) { MessageBox.Show(ex.Message); // TODO: Log the error somewhere } } } } }
- Use parameterized queries everywhere to avoid SQL Injection[^] vulnerabilities;
- Execute the queries, instead of just creating them and then throwing them away;
- Catch and display / log exceptions from the database;
- Wrap objects which implement
IDisposable
in ausing
block;- Don't use fields to store objects whose lifetime is limited to a single method call;
- Extract the repeated code to create the
SqlConnection
object into a separate method;- Pass
CommandBehavior.CloseConnection
to theExecuteReader
method, so that the connection is closed as soon as theSqlDataReader
is disposed;- Check the return value of the
Read
method, rather than ignoring it and then callingHasRows
;
这篇关于添加和编辑按钮不能在sql和C#中工作的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!