copying data over from grid, then edit

Question

0.00/5 (No votes)

See more:

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

C#

<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

Posted 7-Feb-12 7:40am

BBCokeley

Updated 7-Feb-12 8:15am

Christian Graus

v3

Add a Solution

3 solutions

Solution 1

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 ?

Posted 7-Feb-12 8:01am

Christian Graus

Comments

BBCokeley 7-Feb-12 14:10pm

could not post the entire code in comment box, so i redone it below.

Solution 2

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.

Posted 7-Feb-12 8:09am

BBCokeley

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Christian Graus · Accepted Answer · 2012-02-07T08:19:00

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.

C#

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);
                  }

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.