从网格复制数据，然后进行编辑 [英] copying data over from grid, then edit
本文介绍了从网格复制数据，然后进行编辑的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
您好，请在这里再次哑光
抱歉，如果说明中的问题有点含糊.
我已经从datagridview中的表中获得数据，并且已经从数据库中传递了所有数据
我想做的是:
1.单击编辑按钮，以便将数据复制到另一个表中.
2.更改值，然后将其保存回原始值.
将数据复制到编辑表时出现问题
要复制数据，只需按一下按钮
表名是使用文本框传递的，所以我无法解决问题，因为它与手动键入相同，就像我在上面获取表时所做的一样.
它引发的错误是:
ExecuteReader需要打开且可用的连接.连接的当前状态已关闭.
还有.....
查询表达式''= 10248''中的语法错误(缺少运算符).
还为什么当我声明连接为打开时关闭?，是否在某些地方关闭?
如果需要，我可以在此处发布整个操作..
请帮我开车发疯...大声笑
对不起，我不够清楚
整个表单代码如下
hi matt here again
sorry if the question is a bit vague in discription.
i have data from a table in a datagridview, allready passed from a data base
what i am trying to do is:
1. click an edit button, so the data is copied over into another table.
2. change the values then save it back to the original.
problem on copying over the data to the editing table
For copying over the data, its simply pressing a button
the table name is passed over using a textbox, so i cant figure out the problem as it is the same as typing it manually, as i did above to fetch the table.
the error it is throwing is:
ExecuteReader requires an open and available connection. The connection''s current state is closed.
and also.....
Syntax error (missing operator) in query expression ''= 10248''.
also why is it closed when i declare the connection as open?, is it closing some where??
if needed i can post the entire operation here..
please help driving me nuts atm...lol
sorry for not being clear enough
the entire form code is as follows
<pre>
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.OleDb;
using System.Data.SqlClient;
namespace northwind
{
public partial class MainScreen : Form
{
bool connected = false;// used for testing connection
OleDbConnection con = new OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + "E:\\data cent\\Northwind.mdb");
string SelectedLine; // primary key value
String[] OldValues = new String[20];// length of 20 string held in an array
String[] DataColumnNames = new String[20];
public MainScreen()
{
InitializeComponent();
}
private void MainScreen_Load(object sender, EventArgs e)
{
hiddenBox.Hide();
editingTableView.Hide();
}
private void connectButton_Click(object sender, EventArgs e)
{
//SqlConnection connection = new SqlConnection(@"Data Source=soacsqlserver\sholesqlbsc;Initial Catalog=StepSample_matt;Integrated Security=True");
string connectionString = GetConnectionString();
using (OleDbConnection con= new OleDbConnection(connectionString))
{
con.Open();
connectButton.BackColor = Color.Green;
connectButton.Text = "Connected";
connected = true;
}
}
private void logoutButton_Click(object sender, EventArgs e)
{
Form1 fm1 = new Form1();
tableView.DataSource = null;
connected = false;
this.Close();
fm1.Show();
}
private void exitButton_Click(object sender, EventArgs e)
{
tableView.DataSource = null;
connected = false;
MessageBox.Show("Exiting");
Application.Exit();
}
static private string GetConnectionString()
{ // string vary depending on machine accessing data base
return "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + "E:\\data cent\\Northwind.mdb";
// home string return "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + "E:\\data cent\\Northwind.mdb";
// Uni String return "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + "I:\\data cent\\Northwind.mdb";
}
private void ordersButton_Click(object sender, EventArgs e)
{
if (connected == false)
{
MessageBox.Show("not connected");
}
if (connected == true)
{
tableSelected.Text = "Orders";
hiddenBox.Show();
editingTableView.Show();
string connectionString = GetConnectionString();
DataSet ReturnedTable = new DataSet();
BindingSource bs = new BindingSource();
//SelectedTable += ordersButton.Click;
string queryString = " SELECT * FROM Orders" + ";";
try
{
OleDbDataAdapter adapter = new OleDbDataAdapter(queryString, connectionString);
adapter.Fill(ReturnedTable, "Returned Table");
bs.DataSource = ReturnedTable.Tables[0];
tableView.DataSource = bs;
tableView.Update();
adapter.Dispose();
}
catch (Exception ex) // Show the text message associated with the exception in a message box
{
MessageBox.Show(ex.Message);
}
}
}
private void editButton_Click(object sender, EventArgs e)
{
DataSet ReturnedTable = new DataSet();
BindingSource bs = new BindingSource();
string connectionString = GetConnectionString();
GetColumnNames();
SelectedLine = tableView.SelectedCells[0].Value.ToString();
string queryString = "SELECT * FROM " + tableSelected.Text + " WHERE " + DataColumnNames[0] + "= " + SelectedLine + ";";
try
{
OleDbDataAdapter adapter = new OleDbDataAdapter(queryString, connectionString);
adapter.Fill(ReturnedTable, "Returned Table");
bs.DataSource = ReturnedTable.Tables[0];
editingTableView.DataSource = bs;
editingTableView.Update();
for (int i = 0; i < editingTableView.Rows[0].Cells.Count - 1; i++)
{
OldValues[i] = editingTableView.Rows[0].Cells[i].Value.ToString();
}
}
catch (Exception ex) // Show the text message associated with the exception in a message box
{
MessageBox.Show(ex.Message);
}
}
private void GetColumnNames()
{
string connectionString = GetConnectionString();
using (OleDbConnection con = new OleDbConnection(connectionString))
{
OleDbCommand command = con.CreateCommand();
command.CommandText = "SELECT column_name AS Col_Name FROM INFORMATION_SCHEMA.Columns WHERE TABLE_NAME = '" + tableSelected.Text + "';";
string tempString = null;
try
{
OleDbDataReader columnDr = command.ExecuteReader();
int i = 0;
while (columnDr.Read())
{
DataColumnNames[i] = columnDr.GetString(0);
tempString += DataColumnNames[i].ToString() + " ";
i++;
}
columnDr.Close();
}
catch (Exception ex) // Show the text message associated with the exception in a message box
{
MessageBox.Show(ex.Message);
}
}
}
}
}
粘贴整个表单非常有用，但是如果没有整个图片很难解释，先前的表单是登录名和初始屏幕.
谢谢大家
appologise greatly for pasting the entire form, but its hard to explain without the whole picture, the previouse forms are a login and a splash screen.
thanks all
推荐答案
据我所知，您的连接始终处于打开状态，但是在尝试进行数据库调用时不使用该连接.整个事情有点混乱.我删除了您虚假的答案"帖子，您应该像以前一样编辑您的回复，以将所有代码放入其中.仍然可以确定是哪条线引发了错误，但是我认为整个设计有点错综复杂，而且不同的部分并没有按应有的方式协同工作.我会在需要时打开连接，并使用它建立连接.您是否尝试过使用调试器来查看与您正在建立的数据库调用关联的连接以及连接是否打开?
好，我现在看到了.
As far as I can tell, you have a connection that is always open, but which you do not use when you try to make your DB call. The whole thing is kind of colvuluted. I deleted your fake ''answer'' post, you should edit your reply, as I did, to put all the code in there. It would still help to be sure of what line is throwing the error, but I think the whole design is a bit thrown together and the different parts are just not working together as they should. I''d be opening the connection when I need it, and using it to make my connection. Have you tried using your debugger to see what connection is associated to the DB call you''re making and if it''s open ?
OK, I see it now.
string queryString = "SELECT * FROM " + tableSelected.Text + " WHERE " + DataColumnNames[0] + "= " + SelectedLine + ";";
try
{
OleDbDataAdapter adapter = new OleDbDataAdapter(queryString, connectionString);
adapter.Fill(ReturnedTable, "Returned Table");
bs.DataSource = ReturnedTable.Tables[0];
editingTableView.DataSource = bs;
editingTableView.Update();
for (int i = 0; i < editingTableView.Rows[0].Cells.Count - 1; i++)
{
OldValues[i] = editingTableView.Rows[0].Cells[i].Value.ToString();
}
}
catch (Exception ex) // Show the text message associated with the exception in a message box
{
MessageBox.Show(ex.Message);
}
首先，我是对的.您传入一个连接字符串，您没有使用您创建的连接对象，并且连接的事实甚至可能是此代码无法连接的原因.其次，在文本框中构建SQL是不安全的，SQL注入意味着我可以擦除您的数据库.第三，此代码假定您的列的内容不是文本，是吗?可以帮助您逐步完成调试器，并查看要尝试传递的SQL，但首先，您需要修复连接，以便您的代码使用它.
In the first place, I was right. You pass in a connection string, you''re NOT using the connection object you created, and the fact it''s connected, might even be the reason this code cannot connect. Second, building SQL out of text boxes is not safe, SQL injection means I can erase your DB. Third, this code assumes the content of your column is not text, is that right ? It would help you a lot to step through the debugger, and see the SQL you''re trying to pass, but in the first instance, you need to fix your connection so that your code uses it.
此处有很多问题.这篇文章很长，很难阅读.当然，知道哪一行会引发错误会有所帮助.
除非内部发生，否则您尚未发布调用ExecuteReader的行.当然，我看不到此代码中打开任何连接.由于数字10248不会出现在您发布的任何代码中，而是在错误消息中指定的，因此我可以确定，尽管您发布了大量的代码，但仍然缺少一些代码.这个数字是否出现在您的代码库中?怎么生成正在调用的SQL?
Lots of issues here. this post is quite long and hard to read. Certainly it would help to know what line throws the error.
You''ve not posted the line that calls ExecuteReader, unless it''s happening internally. Certainly I see no connection being opened in this code. As the number 10248 does not appear in any code you posted, but is specified in the error message, I feel certain that, despite you posting tons of code, some is missing. Does this number appear in your code base ? How is the SQL generated that is being called ?
对不起，因为不够清晰
整个表单代码如下
使用系统；
使用System.Collections.Generic;
使用System.ComponentModel;
使用System.Data;
使用System.Drawing;
使用System.Linq;
使用System.Text;
使用System.Windows.Forms;
使用System.Data.OleDb;
使用System.Data.SqlClient;
命名空间northwind
{
公共局部类MainScreen:Form
{
bool connected = false;//用于测试连接
OleDbConnection con =新的OleDbConnection(@"Provider = Microsoft.Jet.OLEDB.4.0; Data Source =" +"E:\\ data cent \\ Northwind.mdb");
字符串SelectedLine; //主键值
String [] OldValues = new String [20];//数组中保存的20个字符串的长度
String [] DataColumnNames =新的String [20];
公共MainScreen()
{
InitializeComponent();
}
私有void MainScreen_Load(对象发送者，EventArgs e)
{
hiddenBox.Hide();
editingTableView.Hide();
}
私有void connectButton_Click(对象发送者，EventArgs e)
{
//SqlConnection连接=新的SqlConnection(@数据源= soacsqlserver \ sholesqlbsc;初始目录= StepSample_matt;集成安全性= True");
字符串connectionString = GetConnectionString();
使用(OleDbConnection con = new OleDbConnection(connectionString))
{
con.Open();
connectButton.BackColor =颜色.绿色;
connectButton.Text =已连接";
连接=真;
}
}
私有void logoutButton_Click(对象发送者，EventArgs e)
{
Form1 fm1 =新的Form1();
tableView.DataSource = null;
已连接=假;
this.Close();
fm1.Show();
}
私有void exitButton_Click(对象发送者，EventArgs e)
{
tableView.DataSource = null;
已连接=假;
MessageBox.Show(退出");
Application.Exit();
}
静态私有字符串GetConnectionString()
{//字符串因计算机访问数据库而异
返回"Provider = Microsoft.Jet.OLEDB.4.0; Data Source =" +"E:\\ data cent \\ Northwind.mdb";
//主页字符串返回"Provider = Microsoft.Jet.OLEDB.4.0; Data Source =" +"E:\\ data cent \\ Northwind.mdb";
//Uni字符串返回"Provider = Microsoft.Jet.OLEDB.4.0; Data Source =" +"I:\\ data cent \\ Northwind.mdb";
}
私有无效ordersButton_Click(对象发送者，EventArgs e)
{
如果(已连接==假)
{
MessageBox.Show(未连接");
}
如果(已连接== true)
{
tableSelected.Text =订单";
hiddenBox.Show();
editingTableView.Show();
字符串connectionString = GetConnectionString();
DataSet ReturnedTable = new DataSet();
BindingSource bs = new BindingSource();
//SelectedTable + = ordersButton.Click;
string queryString ="SELECT * FROM Orders" +;";
试试
{
OleDbDataAdapter适配器=新的OleDbDataAdapter(queryString，connectionString);
adapter.Fill(ReturnedTable，"Returned Table");
bs.DataSource = ReturnedTable.Tables [0];
tableView.DataSource = bs;
tableView.Update();
adapter.Dispose();
}
catch(Exception ex)//在消息框中显示与异常关联的文本消息
{
MessageBox.Show(ex.Message);
}
}
}
私有void editButton_Click(对象发送者，EventArgs e)
{
DataSet ReturnedTable = new DataSet();
BindingSource bs = new BindingSource();
字符串connectionString = GetConnectionString();
GetColumnNames();
SelectedLine = tableView.SelectedCells [0] .Value.ToString();
字符串queryString ="SELECT * FROM" + tableSelected.Text +"WHERE" + DataColumnNames [0] +"=" + SelectedLine +;";
试试
{
OleDbDataAdapter适配器=新的OleDbDataAdapter(queryString，connectionString);
adapter.Fill(ReturnedTable，"Returned Table");
bs.DataSource = ReturnedTable.Tables [0];
editingTableView.DataSource = bs;
editingTableView.Update();
for(int i = 0; i< editingTableView.Rows [0] .Cells.Count-1; i ++)
{
OldValues [i] = editingTableView.Rows [0] .Cells [i] .Value.ToString();
}
}
catch(Exception ex)//在消息框中显示与异常关联的文本消息
{
MessageBox.Show(ex.Message);
}
}
私有void GetColumnNames()
{
字符串connectionString = GetConnectionString();
使用(OleDbConnection con = new OleDbConnection(connectionString))
{
OleDbCommand命令= con.CreateCommand();
command.CommandText =从INFORMATION_SCHEMA中选择SELECT column_name AS Col_Name.列WHERE TABLE_NAME =""+ tableSelected.Text +"'';;
字符串tempString = null;
试试
{
OleDbDataReader columnDr = command.ExecuteReader();
int i = 0;
while(columnDr.Read())
{
DataColumnNames [i] = columnDr.GetString(0);
tempString + = DataColumnNames [i] .ToString()+";
i ++;
}
columnDr.Close();
}
catch(Exception ex)//在消息框中显示与异常关联的文本消息
{
MessageBox.Show(ex.Message);
}
}
}
}
}
粘贴整个表单很有意义，但是如果没有整个图片就很难解释，以前的表单是登录名和初始屏幕.
sorry for not being clear enough
the entire form code is as follows
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.OleDb;
using System.Data.SqlClient;
namespace northwind
{
public partial class MainScreen : Form
{
bool connected = false;// used for testing connection
OleDbConnection con = new OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + "E:\\data cent\\Northwind.mdb");
string SelectedLine; // primary key value
String[] OldValues = new String[20];// length of 20 string held in an array
String[] DataColumnNames = new String[20];
public MainScreen()
{
InitializeComponent();
}
private void MainScreen_Load(object sender, EventArgs e)
{
hiddenBox.Hide();
editingTableView.Hide();
}
private void connectButton_Click(object sender, EventArgs e)
{
//SqlConnection connection = new SqlConnection(@"Data Source=soacsqlserver\sholesqlbsc;Initial Catalog=StepSample_matt;Integrated Security=True");
string connectionString = GetConnectionString();
using (OleDbConnection con= new OleDbConnection(connectionString))
{
con.Open();
connectButton.BackColor = Color.Green;
connectButton.Text = "Connected";
connected = true;
}
}
private void logoutButton_Click(object sender, EventArgs e)
{
Form1 fm1 = new Form1();
tableView.DataSource = null;
connected = false;
this.Close();
fm1.Show();
}
private void exitButton_Click(object sender, EventArgs e)
{
tableView.DataSource = null;
connected = false;
MessageBox.Show("Exiting");
Application.Exit();
}
static private string GetConnectionString()
{ // string vary depending on machine accessing data base
return "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + "E:\\data cent\\Northwind.mdb";
// home string return "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + "E:\\data cent\\Northwind.mdb";
// Uni String return "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + "I:\\data cent\\Northwind.mdb";
}
private void ordersButton_Click(object sender, EventArgs e)
{
if (connected == false)
{
MessageBox.Show("not connected");
}
if (connected == true)
{
tableSelected.Text = "Orders";
hiddenBox.Show();
editingTableView.Show();
string connectionString = GetConnectionString();
DataSet ReturnedTable = new DataSet();
BindingSource bs = new BindingSource();
//SelectedTable += ordersButton.Click;
string queryString = " SELECT * FROM Orders" + ";";
try
{
OleDbDataAdapter adapter = new OleDbDataAdapter(queryString, connectionString);
adapter.Fill(ReturnedTable, "Returned Table");
bs.DataSource = ReturnedTable.Tables[0];
tableView.DataSource = bs;
tableView.Update();
adapter.Dispose();
}
catch (Exception ex) // Show the text message associated with the exception in a message box
{
MessageBox.Show(ex.Message);
}
}
}
private void editButton_Click(object sender, EventArgs e)
{
DataSet ReturnedTable = new DataSet();
BindingSource bs = new BindingSource();
string connectionString = GetConnectionString();
GetColumnNames();
SelectedLine = tableView.SelectedCells[0].Value.ToString();
string queryString = "SELECT * FROM " + tableSelected.Text + " WHERE " + DataColumnNames[0] + "= " + SelectedLine + ";";
try
{
OleDbDataAdapter adapter = new OleDbDataAdapter(queryString, connectionString);
adapter.Fill(ReturnedTable, "Returned Table");
bs.DataSource = ReturnedTable.Tables[0];
editingTableView.DataSource = bs;
editingTableView.Update();
for (int i = 0; i < editingTableView.Rows[0].Cells.Count - 1; i++)
{
OldValues[i] = editingTableView.Rows[0].Cells[i].Value.ToString();
}
}
catch (Exception ex) // Show the text message associated with the exception in a message box
{
MessageBox.Show(ex.Message);
}
}
private void GetColumnNames()
{
string connectionString = GetConnectionString();
using (OleDbConnection con = new OleDbConnection(connectionString))
{
OleDbCommand command = con.CreateCommand();
command.CommandText = "SELECT column_name AS Col_Name FROM INFORMATION_SCHEMA.Columns WHERE TABLE_NAME = ''" + tableSelected.Text + "'';";
string tempString = null;
try
{
OleDbDataReader columnDr = command.ExecuteReader();
int i = 0;
while (columnDr.Read())
{
DataColumnNames[i] = columnDr.GetString(0);
tempString += DataColumnNames[i].ToString() + " ";
i++;
}
columnDr.Close();
}
catch (Exception ex) // Show the text message associated with the exception in a message box
{
MessageBox.Show(ex.Message);
}
}
}
}
}
appologise greatly for pasting the entire form, but its hard to explain without the whole picture, the previouse forms are a login and a splash screen.
这篇关于从网格复制数据，然后进行编辑的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
