OLEDB connection won't open

Question

0.00/5 (No votes)

See more:

Hi All,

I am using VS2010 to connect to a SQLServer 2000 instance on a remote machine.
I am using a OLEDB connection string that just will not open. I'm wondering if anyone can shed some light on why this connection just won't open. Any help is greatly appreciated. Thank you!
Code below.

C#

public string RDDTConnectionString = @"Provider=sqloledb;Data Source=MyServer;Initial Catalog=MyDataBase;Integrated Security=SSPI;";

public OleDbConnection ConnString() 
{
    OleDbConnection connString = new OleDbConnection();
    connString.ConnectionString = RDDTConnectionString;
    return connString;
}

private void btnSaveDealSheet_Click(object sender, EventArgs e)
{
    try
    {
        if (ConnString().State == ConnectionState.Closed)
        {
            ConnString().Open();                   
            MessageBox.Show("ConnString.State:" + ConnString().State);
        }
        //Execute SQL
        OleDbCommand InsertCommand = GetInsertDealSheetCommand();
        InsertedID = Convert.ToInt32(InsertCommand.ExecuteScalar());
        //Return inserted Identity
        MessageBox.Show("DealSheet Successfully Added");
    }
    catch (Exception ex) 
    {
        MessageBox.Show("error: " + ex.Message); ;
    }
}

public void OpenConnectionString()
{           
    try
    {
        ConnString().Open();

        if (ConnString().State == ConnectionState.Open)
        {
            MessageBox.Show("opened");
        }
    }
    catch (Exception ex)
    {
        MessageBox.Show("Error: " + ex.Message); 
    }
}

Posted 5-Dec-11 22:34pm

Jennifer McCartthy

Updated 5-Dec-11 22:44pm

Reiss

v3

Add a Solution

Comments

Reiss 6-Dec-11 4:50am

Are you getting any error messages - if so update the question to add the details.

Jennifer McCartthy 6-Dec-11 6:14am

I'm not getting any errors at all. Just my message boxes telling me the connection state is closed.

2 solutions

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)

Reiss · Answer 1 · 2011-12-05T22:49:00

Solution 1

Can you switch it to use a SqlConnection[^] - This sould be more efficient

Your connection string would then be Data Source=myServerAddress;Initial Catalog=myDataBase;Integrated Security=SSPI;¹

¹Taken from www.connectionstrings.com[^]

Posted 5-Dec-11 22:49pm

Reiss

Updated 5-Dec-11 22:50pm

v2

Comments

Jennifer McCartthy 6-Dec-11 6:14am

Hi Reiss,

Thank you for your help.

Initially I wrote the app with a SQLConnection only to find that it is not compatible with SQLServer 2000 when using VS2010. I would love more than anything to use a SQL connection.

Thanks again for your help, its very much appreciated.

Kind Regards
Jen

Reiss · Answer 2 · 2011-12-06T03:38:00

I think I have spotted your error - you call the ConnString() method multiple times creating a new connection instance each time.

Your code should be

C#

public string RDDTConnectionString = @"Provider=sqloledb;Data Source=MyServer;Initial Catalog=MyDataBase;Integrated Security=SSPI;";


public OleDbConnection ConnString() 
{
    OleDbConnection connString = new OleDbConnection();
    connString.ConnectionString = RDDTConnectionString;
    return connString;
}
 
private void btnSaveDealSheet_Click(object sender, EventArgs e)
{
    try
    {
        OleDbConnection connString = ConnString() ; // added
        if (connString.State == ConnectionState.Closed) //changed
        {
            connString.Open();// changed                   
            MessageBox.Show("ConnString.State:" + connString.State);//changed
        }
        //Execute SQL
        OleDbCommand InsertCommand = GetInsertDealSheetCommand(); // check this method to be sure you don't create a new connection instance
        InsertedID = Convert.ToInt32(InsertCommand.ExecuteScalar());
        //Return inserted Identity
        MessageBox.Show("DealSheet Successfully Added");
    }
    catch (Exception ex) 
    {
        MessageBox.Show("error: " + ex.Message); ;
    }
}
 
public void OpenConnectionString()
{           
    try
    {
        OleDbConnection connString = ConnString() ; // added
        connString.Open(); //changed
 
        if (connString.State == ConnectionState.Open)
        {
            MessageBox.Show("opened");
        }
    }
    catch (Exception ex)
    {
        MessageBox.Show("Error: " + ex.Message); 
    }
}