Database connection issue

[firoz.raj]

can someone tell me ???. Why the following code is not working ???. let me know some idea.

 private bool OpenConnection(ref OleDbConnection conn)
        {
            try
                {
                conn = new OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Imp\product_Table.mdb");
                conn.Open();

                if (conn.State!=conn.Open )
                     {
                    MessageBox.Show("Connection is Not Open");
                    return false;
                   
                     }
                else
                    {

                    return true;
                    
                     }


                
                   }
            catch (Exception ex)
                   {
                MessageBox.Show(ex.Message);
                 return false;
                   }
        }

[Lightning]

if (conn.State!=conn.Open )

Should be:

if (conn.State!=ConnectionState.Open )

[danasegarane]

That should be

 if (con.State != ConnectionState.Open)
            {

            }

[firoz.raj]

Still not working .it says The name 'ConnectionState' does not exist in the current context.here is the following code.

private bool OpenConnection(ref OleDbConnection conn)
         {
                           conn = new OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Imp\product_Table.mdb");
                conn.Open();

                 if (conn.State!=ConnectionState.Open ) 
                     {
                    MessageBox.Show("Connection is Not Open");
                    return false;
                   
                     }
                else
                    {

                    return true;
                    
                     }


                
                   }
           
                   }
          }

[danasegarane]

You are missing namespace System.Data

if (con.State != System.Data.ConnectionState.Open )

[firoz.raj]

Resolved

[danasegarane]

already using System.Data.OleDb ; is included at the very bigining of code window.

additional why the following code is not working ???

OleDbConnection conn=new OleDbConnection();

Not working means ?

[Techno]

here is a better and more elegant code:

private bool OpenConnection(ref OleDbConnection conn)
{
	conn = new OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Imp\product_Table.mdb");
	conn.Open();
	return conn.State == ConnectionState.Open; 
}

shorter lines of code and easy to read.

[firoz.raj]

here is a better and more elegant code:

why the following code is not working ???.i simple want to fill my dataset.after filliling the listbox.let me know some idea.

private void BtFillListBox_Click(object sender, EventArgs e)
          {
            if (!OpenConnection())
            {
                MessageBox.Show("Connection is Not Open");
                this.Dispose(true);

            }
               OleDbCommand cmd = new OleDbCommand();
               cmd.CommandText = "Select * from employees";
               DataSet ds = new DataSet("Employees");
               OleDbDataAdapter MyAdapter = new OleDbDataAdapter();
            //   MyAdapter.SelectCommand = OleDbCommand;
               MyAdapter.Fill(ds, "Employees");
               
                  
                       

               }

[Techno]

when you say it doesnt work, what happens?
you commented out the MyAdapter.SelectCommand. this is incorrect, the DataAdapter needs this to execute the select command to fill the dataset. this select command is stored in the object you created called cmd.

MyAdapter.SelectCommand = cmd;

[firoz.raj]

Still i am getting error .Select Command.Connection Property has not been initialized.here is the following code.what i have written.let me know please .

using System;
using System.Data.OleDb ;
using System.Windows.Forms;
using System.Data;

namespace MakeConnection
{
    public partial class Form1 : Form
       
     {
       
    private  OleDbConnection conn = new OleDbConnection();
        
        
        public Form1()
        {
            InitializeComponent();
        }

        private void Form1_Load(object sender, EventArgs e)
        {
            if (!OpenConnection()) 
                {
               MessageBox.Show("Connection is Not Open");
               this.Dispose(true);
          
                  }
                   filllistbox();
             
                }





   public  bool OpenConnection()
         {
            try
                {
            // OleDbConnection conn = new OleDbConnection();
                conn = new OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Imp\product_table.mdb");
                conn.Open();

                 if (conn.State!=ConnectionState.Open ) 
                        {
                    MessageBox.Show("Connection is Not Open");
                    return false;
                        }
                  else
                        {
                    return conn.State == ConnectionState.Open;
                        }
                
                   }
            catch (Exception ex)
                  {
                MessageBox.Show(ex.Message);
                return false;
                  }
           }

       


       
        private void CloseConnection()
           {
            try
             {

            if (conn.State  == ConnectionState.Open )
                    {
                conn.Close() ;
                     }
                conn.Dispose(); 
                
               }
             catch(Exception ex)
                {
              MessageBox.Show(ex.Message);
              conn.Close(); 
               
                }
              }







        private void filllistbox()
         {
            if (!OpenConnection())
                   {
                MessageBox.Show("Connection is Not Open");
                this.Dispose(true);
                    }

            OleDbCommand cmd = new OleDbCommand();
            cmd.CommandText = "Select * from employees";
            
            
            OleDbDataAdapter dataloader = new OleDbDataAdapter();
             dataloader.SelectCommand = cmd;

              DataSet ds = new DataSet("Employees"); 
              dataloader.Fill(ds, "Employees");

        }











      }
 }

[Techno]

you should read the MSDN Documentation on how to use the DataAdapter classes.

you need to assign, as it says, the connection property of the select command.

OleDbCommand cmd = new OleDbCommand();
cmd.CommandText = "Select * from employees";

cmd.Connection = this.conn;

[firoz.raj]

you should read the MSDN Documentation on how to use the DataAdapter classes.

you need to assign, as it says, the connection property of the select command.

still i am getting error .when i tried to run.additional what is the uses of this.conn.here is it this Pointer ???.when i run using f8. i got the the following error.
The SelectCommand property has not been initialized before calling 'Fill'.

private void filllistbox()
         {
            if (!OpenConnection())
                   {
                MessageBox.Show("Connection is Not Open");
                this.Dispose(true);
                    }

            OleDbCommand cmd = new OleDbCommand();
            cmd.CommandText = "Select * from employees";
            cmd.Connection =this.conn ;
            
            OleDbDataAdapter dataloader = new OleDbDataAdapter();
            // dataloader.SelectCommand = cmd;

              DataSet ds = new DataSet("Employees"); 
              dataloader.Fill(ds, "Employees");

        }

[Techno]

I had stated the error previously and corrected the code

[firoz.raj]

how should i fill listbox ???.let me know please.any help would be highly appreciated.

 private void filllistbox()
         {
            if (!OpenConnection())
                   {
                MessageBox.Show("Connection is Not Open");
                this.Dispose(true);
                    }

             OleDbCommand cmd = new OleDbCommand();
             cmd.CommandText = "Select * from employees";
             cmd.Connection =this.conn ;
            
             OleDbDataAdapter dataloader = new OleDbDataAdapter();
            // dataloader.SelectCommand = cmd;

              DataSet ds = new DataSet("Employees"); 
              dataloader.Fill(ds, "Employees");
              ListBox lbox = new ListBox();
                

        }

[Techno]

not sure why you are creating a listbox control in code unless you are going to be adding it to the form dynamically?

simply set the datasource, datamember properties on the listbox in question to the dataset/datatable

[firoz.raj]

simply set the datasource, datamember properties on the listbox in question to the dataset/datatable

still i am getting error at this line For Each row As DataRow In ds.Tables(0).Rows.let me know someone please.here is the following code.what i have written.

private void filllistbox()
               {
            if (!OpenConnection())
                    {
                MessageBox.Show("Connection is Not Open");
                this.Dispose(true);
                     }

             OleDbCommand cmd = new OleDbCommand();
             cmd.CommandType = CommandType.Text;
             cmd.CommandText = "Select * from employees";
             cmd.Connection =this.conn ;
            
             OleDbDataAdapter dataloader = new OleDbDataAdapter();
             dataloader.SelectCommand = cmd;

              DataSet ds = new DataSet("Employees"); 
              dataloader.Fill(ds, "Employees");
               
              For Each row As DataRow In ds.Tables(0).Rows
                   { 
               ListBox1.Items.Add(row("Name").ToString);
                   }

              

                        
                   
                  }

[jmcilhinney]

Don't just tell us that there's an error. If there's an error then there's an error message. The IDE gives it to you to help you solve the problem. Logic dictates that, if you want us to help you solve the problem, you give it to us. You've already been prompted to do that at least twice in this thread alone.

[Techno]

the code you have highlighted you are using is VB.NET. but you are using C#. these are 2 different languages. you need to convert your code to C# equivelent.

foreach(DataRow currentRow in ds.Tables[0].Rows)
{
...
}

[firoz.raj]

still i am getting one error .'System.Data.DataRow' is a 'type' but is used like a 'variable'.datarow is a type means let me clarify please.

[Techno]

try to read the error message and understand what it means. your syntax is also incorrect. Are you using Visual Studio IDE? if so, then there is no excuse or any reason why your syntax should be incorrect

[Gambit001]

listBox1.Items.Add(DataRow("Name").tostring);

should be

listBox1.Items.Add(currentRow["Name"].tostring);

[Techno]

incorrect

take a look at your syntax.

[firoz.raj]

Are you using Visual Studio IDE? if so, then there is no excuse or any reason why your syntax should be incorrect

how many visual IDE Is Available?.additional when i tried to run the following code .i am still getting error. 'currentRow' is a 'variable' but is used like a 'method'

[jmcilhinney]

First up, in post #22 you were told that you need to change DataRow("Name") to currentRow["Name"] and you've instead changed it to currentRow("Name"). If you aren't going to follow the advice that people provide then there's not much point asking for it.

You've also been told MANY times that C# is a case-sensitive language and you still choose to ignore that. There is no method named "tostring". There is, however, a method named "ToString". Finally, while VB allows you to omit the parentheses on methods if the argument list is empty, C# requires ALL method calls to include parentheses.

[Gambit001]

incorrect

take a look at your syntax.

i was hoping he'd be able to guess the tostring bit himself lol

[firoz.raj]

yes now the code is working fine.additional can you tell me what is the uses of this keyword here??? .is it this pointer like c++??.let me clarify please.

cmd.Connection =this.conn ;

[Techno]

the use of this keyword:

http://msdn.microsoft.com/en-us/libr...bb(VS.71).aspx
http://msdn.microsoft.com/en-us/libr...(v=VS.71).aspx

[HowTo]

VB -> Me == C# ->this

[firoz.raj]

.as we know executeNonQuery Function returns No of Rows Affected.in the following code i want to see.how much record has beed deleted.but i am getting error.cannot convert from 'method group ' to 'string'.let me know please.

private void Del()
                    {
                        Int64   d;
                 OleDbCommand cmd=new OleDbCommand();
                   cmd.Connection =this.conn;
                   cmd.CommandText ="DELETE CDs.ArtistName FROM CDs";
             :)
                MessageBox.Show(d.ToString );
                      }

        private void Delete_Click(object sender, EventArgs e)
        {
            Del();
        }

[jmcilhinney]

As I have already stated, C# requires you to include parentheses on every method call, whether you're passing parameters or not. ToString is a method. You can't call it without parentheses.

[techgnome]

It doesn't matter anyways, since the command doesn't get executed... the code as it stands will simply display "0" in the messagebox.

-tg

[firoz.raj]

It doesn't matter anyways, since the command doesn't get executed... the code as it stands will simply display "0" in the messagebox.

it is working nice .what is a issue in the above code ???.Additional i have tried to insert textbox data into the listbox.but getting error at red line.let me know please.[Code]private void BtAdd_Click(object sender, EventArgs e)

[Techno]

CommandText requires a string.

you arent providing a string.

[firoz.raj]

Still i am getting expected error ;.at the following line.Kindly let me know the idea

[HowTo]

Still i am getting expected error ;.at the following line.Kindly let me know the idea.

cmd.CommandText ="insert into CDs(ArtistName )values ('"txtArtistName"')";

cmd.CommandText ="insert into CDs(ArtistName) Values ('"txtArtistName.Text"')";

[firoz.raj]

still i am getting ; Expected Error.at the following line .

cmd.CommandText ="insert into CDs(ArtistName )values ('"txtArtistName.text"')";

[Techno]

because you need to add the value of txtArtistName.Text into the string.

cmd.CommandText ="insert into CDs(ArtistName )values ('" + txtArtistName.text + "')";

for best practice you should be using StringBuilder to concatinate string as well as using SPROCS (Stored Procedures) for data insertion, deletion and retrieval for security and performance

[firoz.raj]

Well.Can SomeOne Tell me ?How should i refresh the list Box.after adding data
it should immediately go in the listbox.let me know please.

 private void BtAdd_Click(object sender, EventArgs e)
               {
           OleDbCommand cmd = new OleDbCommand();
            cmd.Connection = this._conn;
            cmd.CommandText ="insert into CDs(ArtistName )values ('" + txtArtistName.Text + "')";
            cmd.ExecuteNonQuery();
            //filllistbox();
                }

[Techno]

you need to rebind the data. so you need a method which binds the data and call that method.