I have a class that get me a record and I want it to be passed back to the main program. I can not get it to work. Below is the part of the Class and the main program:
Class:
public DataTable ReadRecord(string DBCommand)
{
Connection();
SetupDataAdapter();
DBDataAdapter = new SqlDataAdapter(DBCommand, DBConnect);
try
{
DataTable DBDataTable = new DataTable();
DBDataAdapter.Fill(DBDataTable);
foreach (DataRow DBDataRow in DBDataTable.Rows) // GOOD - IT WORKS
{
TheID = DBDataRow["Name"].ToString();
MessageBox.Show("TheID=" + TheID);
}
Terminate();
return (DBDataTable);
}
catch
{
return null;
}
finally
{
}
}
Main:
private void buttonDBSelectRecord_Click(object sender, EventArgs e)
{
DBCommand =
" SELECT"
+ " [Name]"
+ " ,[Address_1]"
+ " FROM"
+ " [Company]"
+ " WHERE"
+ " [Company_ID] = " + "'000000'"
;
ClassDB DBConnection = new ClassDB();
ClassDB.Connection();
DBConnection.ReadRecord(DBCommand);
foreach (DataRow DBDataRow in DBDataTable.Rows) // BAD
{
TheID = DBDataRow["Name"].ToString();
MessageBox.Show("Login...TheID=" + TheID);
}
}
theLizardPosted Mar 19, 2010, 5:55 PM
using System;
using System.Collections;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Data.SqlClient;
using System.Text;
using System.Windows.Forms;
//why do you have these here
using System.Reflection;
using System.Drawing;
using System.Drawing.Printing;
using System.Security.Cryptography;
using System.IO;
namespace lizardMarquee
{
public class ClassDB
{
//These are not needed unless there is a specific use for them
//---------------------------------------------------------------
public string DBServer;
public string DBDatabase;
public string DBLogin_ID;
public string DBPassword;
//
public string DBSelect;
public string DBInsert;
public string DBUpdate;
public string DBDelete;
public string DBClose;
//
public string DBReadRecord;
public string DBLoadGrid;
public string DBLoadCombo;
public string DBLoadList;
//
//---------------------------------------------------------------
//These should be private
//---------------------------------------------------------------
private string dBConnect;
private SqlConnection DBConnection = null;
private SqlDataReader DBDataReader = null;
private SqlCommand DBCommand = null;
private SqlParameter DBParameter = null;
private SqlDataAdapter DBDataAdapter = null;
//
public ClassDB()
{
DBCommand = new SqlCommand();
DBParameter = new SqlParameter();
}
public ClassDB(string strCon)
{
DBConnect = strCon;
DBCommand = new SqlCommand();
DBParameter = new SqlParameter();
Connection();
}
//
// public void Connection(string DBServer, string DBDatabase, string DBLogin_ID, string DBPassword)
public void Connection()
{
//DBConnect = @"Data Source=.\SQLEXPRESS;Initial Catalog=temp;Integrated Security=True";
/*
@"Server=" + DBServer + ";"
+ "Database=" + DBDatabase + ";"
+ "User ID=" + DBLogin_ID + ";"
+ "Password=" + DBPassword + ";";
*/
//
try
{
DBConnection = new SqlConnection(DBConnect);
DBConnection.Open();
DBDataAdapter = SetupDataAdapter();
}
catch
{
}
finally
{
}
}
//make this a property of the class and use it to set your connection
public string DBConnect
{
get { return (dBConnect); }
set { dBConnect = value; }
}
public void Connection(string blank)
{
try
{
DBConnection = new SqlConnection(dBConnect);
DBConnection.Open();
SetupDataAdapter();
}
catch
{
}
finally
{
}
}
//
public void Terminate()
{
try
{
if (DBCommand != null)
{
DBCommand.Connection.Close();
DBCommand.Connection.Dispose();
}
}
catch (Exception Error)
{
}
finally
{
}
//
try
{
if (DBConnection != null)
{
DBConnection.Close();
DBConnection.Dispose();
}
}
catch
{
}
finally
{
}
}
//
//
public void LoadGridView(string sDBCommand, DataGridView DBDataGridView)
{
Connection();
DBDataAdapter = SetupDataAdapter();
DBCommand.CommandText = sDBCommand; //these must be in this order
DBDataAdapter.SelectCommand = DBCommand; //
DBDataAdapter.SelectCommand.Connection = DBConnection; //
try
{
DataSet DBDataSet = new DataSet();
DBDataAdapter.Fill(DBDataSet);
DBDataGridView.DataSource = DBDataSet.Tables[0];
Terminate();
//return (DBDataSet);
}
catch
{
//return null; this not needed
}
finally
{
}
}
//
private SqlDataAdapter SetupDataAdapter()
{
return new SqlDataAdapter();
}
//
}
}
if you adopt the above all you would need to do in your form is this.
ClassDB con = new ClassDB(@"Data Source=.\SQLEXPRESS;Initial Catalog=temp;Integrated Security=True"); //instantiate & connect
con.LoadGridView("SELECT name, Address, FROM Company WHERE Company_id = ' 00000' ORDER BY name", dataGridView);
con.Terminate();
I have compiled and loaded a grid view with the modified code, it all works so you should be able to do the same by changing just the Initial Catalog = your database name.
ReadRecord is something you would do if you want to manually load a grid view rather than use a DataAdapeter, so these things are for learning when you are more experienced and confident for you wanting to manually load controls.
GustavoPosted Mar 20, 2010, 6:36 AM
Ok, thanks.
I have changed everythign, but now I have an issue with the Connection(). I dont see where you want me to connect it, if you have the connection variables commented out. I will play with it. Night.
theLizardPosted Mar 20, 2010, 6:33 AM
GustavoPosted Mar 20, 2010, 5:30 AM
theLizardPosted Mar 20, 2010, 5:28 AM
also make the next reply a new thread because this one is getting too long, but we will continue the theme, ok?
GustavoPosted Mar 20, 2010, 5:21 AM
Its the 'static' stuff that I used that is messing it up. Let me try to get rid of all of them without messing up the other programs.
theLizardPosted Mar 20, 2010, 5:16 AM
I tried with the: ClassDB con = new ClassDB(); and all the other likes with con.* they give me an error.
Error 18 Member 'ICEPack.ClassDB.Connection()' cannot be accessed with an instance reference; qualify it with a type name instead
I don't understand what you mean.
if you do this, ClassDB con = new ClassDB();
you should not get the error above,
if you have done this ICEPack.ClassDB.Connection() to connect, all you would need to do is
con.Connection();
GustavoPosted Mar 20, 2010, 5:12 AM
Yes, I still have the original code you sent me. I will look at it.
GustavoPosted Mar 20, 2010, 5:09 AM
I tried with the: ClassDB con = new ClassDB(); and all the other likes with con.* they give me an error.
Error 18 Member 'ICEPack.ClassDB.Connection()' cannot be accessed with an instance reference; qualify it with a type name instead
theLizardPosted Mar 20, 2010, 5:06 AM
Do you still have the original code for the class i sent sqlCon?
if so, look at the functions of that incorporate this type of code
sqlCon con = new sqlCon();
int count = 0;
try
{
con.connect();
con.command(s); <--------
con.ExecuteReader(); <--------
while (con.read()) <-------- this is a function that simply replaces DBDataReader = con.DBCommand.ExecuteReader();
{
d.Items.Add(con.get("heading")); <---- and this replaces DBDataReader[Field_2].ToString()
Array.Resize(ref ids, ids.Length+1);
ids[ids.Length-1] = con.get("id");
}
}
while this is not absolutely necessary, it simplifies things making it a little easier to read the code.
GustavoPosted Mar 20, 2010, 4:56 AM
Yes, I understand. I will change some of the variables so I dont mess it up.
I will make the changes now.
theLizardPosted Mar 20, 2010, 4:51 AM
DBDataAdapter.SelectCommand = DBCommand;
DBDataAdapter.SelectCommand.Connection = DBConnection;
and they are not used...
before I go any further, you need to make sure that you do not have conflicting variable names, dont use DBCommand or DBCommandText in function calls like this
public void LoadListBox(string DBCommandText, ListBox DBListBox, string Field_1, string Field_2)
they are confusing with others with the same name.
this string DBCommandText is an sql statement to be executed so call it what it is, i simply call it sql
also youi don't need to use [ ] in your sql.
moving on, when you have removed referances to DataAdapter here do the same in the combo box function
rather than test for if(FIELD_2 != "") just make your sql select records thar are not ""
SELECT field list FROM table WHERE FIELD_2 != '' ORDER BY field, this way all you need to do int the while(reader.read()) is
DBListBox.Items.Add(DBDataReader[Field_1].ToString(), ", ", DBDataReader[Field_2]);
because the only records you would be reading are those that satisfy the criteria, do you understand what I mean?
GustavoPosted Mar 20, 2010, 4:39 AM
Ok, I made it work. I changed some variables to ones that I am using, is that OK? Also I had to change the line: DBListBox.Items.Add(DBDataReader[Field_1].ToString(), ", ", DBDataReader[Field_2]);
I like that line: !String.IsNullOrEmpty
public void LoadListBox(string DBCommandText, ListBox DBListBox, string Field_1, string Field_2)
{
Connection();
DBCommand.CommandText = DBCommandText;
DBDataAdapter.SelectCommand = DBCommand;
DBDataAdapter.SelectCommand.Connection = DBConnection;
DBListBox.Items.Clear();
try
{
DBDataReader = DBCommand.ExecuteReader();
while (DBDataReader.Read())
{
if (!String.IsNullOrEmpty(DBDataReader[Field_2].ToString()))
DBListBox.Items.Add(DBDataReader[Field_1].ToString() + ", " + DBDataReader[Field_2]);
}
}
catch (Exception err)
{
MessageBox.Show("CATCH..." + err);
}
finally
{
Terminate();
}
}
theLizardPosted Mar 20, 2010, 4:16 AM
public void LoadListBox(string sDBCommand, ListBox DBListBox, string Field_1, string Field_2)
{
ClassDB con = new ClassDB();
DBListBox.Items.Clear();
try
{
con.Connection();
con.DBCommand.CommandText = sDBCommand;
DBDataReader = con.DBCommand.ExecuteReader();
while (DBDataReader.Read())
{
if(!String.IsNullOrEmpty(DBDataReader[Field_2].ToString()))
DBListBox.Items.Add(DBDataReader[Field_1].ToString(), ", ", DBDataReader[Field_2]);
}
}
catch (Exception err)
{
}
finally
{
con.Terminate();
}
Make this work, but only change this function but don't think that this is the final version it is not., I will show you a better way after you have learned this way, ok?
GustavoPosted Mar 20, 2010, 3:51 AM
Sure... I would like to get rid of the DataAdapter and the table.
Teach me.
theLizardPosted Mar 20, 2010, 3:48 AM
you have done well with the ComboBox and ListBox and I can see that you are picking things up.
If i told you that you do not need to use DataAdapters or tables to do exacatly the same what would you say, do you want to learn how to do that?
I half suspected that you wanted to populate text boxes, what if I told you you could do that easily without using data tables? would you want to learn how to do that?
Remember there is ALWAYS a better way of doing things.
GustavoPosted Mar 20, 2010, 3:41 AM
OK, I will be patient. I will look at your code again and implement what I have missed.
theLizardPosted Mar 20, 2010, 3:39 AM
Same with combo and list boxes.
The power of what you are doing with this class will not be evident until it is like the class I sent you, where you can read, write and get individual fields with little more than a couple of lines of code in your application.
GustavoPosted Mar 20, 2010, 3:38 AM
BTW: The reason I wanted to do a ReadRecord was so I can populate the textBoxs on the form. I guess I can pass a GridView with one row and take out what I want. Unless you have a better idea?
GustavoPosted Mar 20, 2010, 3:33 AM
Here is the ClassDB.
GustavoPosted Mar 20, 2010, 3:09 AM
FYI: I changed too much to your code and its not working now. I will have to bring it back from a backup and step-by-step put your code back in.
theLizardPosted Mar 20, 2010, 2:33 AM
GustavoPosted Mar 20, 2010, 2:26 AM
Ok, good... I will do the Terminate() every time.
Yes, the grid is perfect.
theLizardPosted Mar 20, 2010, 2:26 AM
theLizardPosted Mar 20, 2010, 2:24 AM
YES, if you have changed your class according to my suggestions you will need to terminate() even if it is for the sole purpose of getting you to do this without thinking, it is to ensure that you do not forget to terminate when it is absolutely necessary. the terminate function only kills things if it needs to if there is nothing to kill it won't kill anything.
This is what I have in my main porgram now:
public void buttonLoadGrid_Click(object sender, EventArgs e)
{
DBCommand =
" SELECT"
+ " [Name]"
+ " ,[Address_1]"
+ " FROM"
+ " [Company]"
;
ClassDB DBConnection = new ClassDB();
ClassDB.Connection();
DBConnection.LoadDataGridView(DBCommand, dataGridViewGrid);
DBConnection.Terminate(); //make sure there is nothing left open.
}
This is ok, the grid is loading correct?
GustavoPosted Mar 20, 2010, 2:23 AM
1) About the: DBDataAdapter = SetupDataAdapter(); Yes, that one I understood, I though we can do that.
2) For now I just want to return the DBDataGridView so I can populate a grid in the main program. This is the code in the main program for the Grid.
public void buttonLoadGrid_Click(object sender, EventArgs e)
{
DBCommand =
" SELECT"
+ " [Name]"
+ " ,[Address_1]"
+ " FROM"
+ " [Company]"
;
ClassDB DBConnection = new ClassDB();
ClassDB.Connection();
DBConnection.LoadDataGridView(DBCommand, dataGridViewGrid);
}
3) I will update my code with your suggestions and send it as soon as I am done with the ComboBox and the ListBox... Unless you want me to leave those alone?
theLizardPosted Mar 20, 2010, 2:14 AM
I was stuck on the ReadRecord. In the DBClass I was able to print out the colums of the record, but it will not pass back to the main program.
this is the reason you should take one step at a time, what you want to do could be as simple as the grid but first you need to FULLY understand how things work.
what do you want to do on the main form with a table!
SetupDataAdapter();
DBDataAdapter = new SqlDataAdapter(DBCommand, DBConnect);
these two lines is basically the same as doing this
DBDataAdapter = SetupDataAdapter();
one step at a time, I have a working system to check your code so send me your updated code based on my suggestion and explain what you want to do wth the data table, is there a better way!!
GustavoPosted Mar 20, 2010, 2:04 AM
I took everything out (//Commented) in the LoadGridView and added your code. It works fine.
I will go and do the same changes to the ComboBox and ListBox now...correct?
At the bottom of your post, about the main program. Do I have to do a con.Terminate(), its already in the DBClass?
This is what I have in my main porgram now:
public void buttonLoadGrid_Click(object sender, EventArgs e)
{
DBCommand =
" SELECT"
+ " [Name]"
+ " ,[Address_1]"
+ " FROM"
+ " [Company]"
;
ClassDB DBConnection = new ClassDB();
ClassDB.Connection();
DBConnection.LoadDataGridView(DBCommand, dataGridViewGrid);
}
GustavoPosted Mar 20, 2010, 1:43 AM
I have not implemented your new changes yet.
But last night I got the GridView, ComboBox, ListBox to work. I was stuck on the ReadRecord. In the DBClass I was able to print out the colums of the record, but it will not pass back to the main program.
theLizardPosted Mar 20, 2010, 1:42 AM
theLizardPosted Mar 20, 2010, 1:39 AM
One thing at a time. have you got the grid working 100%
GustavoPosted Mar 20, 2010, 1:38 AM
I just printed out your code. I will lok at it and implement the changes.
yes, I knwo I have some variables and other stuff in there that is not needed. I will take some out. If I take some of them out now, then will ness up the main program in some areas where they do the update,delete, insert. I will get back to you when I get it cleaned up.
Thanks
GustavoPosted Mar 20, 2010, 1:35 AM
Thanks for the reply, but it styill does not work.
Hirendra SisodiyaPosted Mar 20, 2010, 12:35 AM
make changes in main:
private void buttonDBSelectRecord_Click(object sender, EventArgs e)
{
DBCommand =
" SELECT"
+ " [Name]"
+ " ,[Address_1]"
+ " FROM"
+ " [Company]"
+ " WHERE"
+ " [Company_ID] = " + "'000000'"
;
ClassDB DBConnection = new ClassDB();
ClassDB.Connection();
Datatable DBDataTable = DBConnection.ReadRecord(DBCommand);
foreach (DataRow DBDataRow in DBDataTable.Rows) // BAD
{
TheID = DBDataRow["Name"].ToString();
MessageBox.Show("Login...TheID=" + TheID);
}
}
thanks