Need help with a Connection String

PDS8475

Active member
Joined
Jun 25, 2019
Messages
41
Programming Experience
Beginner
Hi
I have a connection string for a access database that is reading it's path from a file.
The code for the connection string and SQL query is

C#:
            string ConString = System.IO.File.ReadAllText("FixIT.con");
            ConString = ConString.Replace(System.Environment.NewLine, string.Empty);
            string FullString = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + ConString;
            System.Data.OleDb.OleDbConnection conn = new System.Data.OleDb.OleDbConnection();
            conn.ConnectionString = FullString;
            OleDbCommand cmd = new OleDbCommand("INSERT into Donations (Name, Address, Phone, Email, DateDonated, Type, Make, Model, Notes, EnteredBy) Values(@n, @a, @p. @em, @d, @i,            @IT, @m, @MO, @s, @NO)");
            
        
             try
            {
                conn.Open();
                cmd.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                MessageBox.Show("Failed to connect to data source");
            }
            finally
            {
                conn.Close();
            }

I can see that the connection string "FullString" is
"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\\Users\\Paul\\Documents\\FixIT.mdb"

However I just get the messagebox saying failed to connect to data source. As well checking the Database file shows that no record has been entered.

I can not figure out why this wont connect.
 
Of course that's the message you get. That's the message you're telling it to display. You should be actually examining the exception to see what it says the issue was.

The issue is fairly obvious though. You have 11 parameter placeholders in your SQL code but you don;t add any parameters to the command. Where's the data supposed to be coming from? That means that the issue actually has nothing at all to do with the connection string and it's the ExecuteNonQuery that's failing rather than the Open. That's why you need to actually look at the information the debugger provides you with.
 
Here is what I see that is wrong, which you can improve.
  1. How do you know this reflects to a path; the file you want to read, and where is the location of your textfile, and why are you not path building using common practices? string ConString = System.IO.File.ReadAllText("FixIT.con");
  2. Why are you not putting the contents of the connection string into the quotation marks and have the constring equal that instead?
  3. Why are you reading the connection string from a file?
  4. You wouldn't need to use string.Replace if you bullet number 2
  5. This is really bad practice to string strings together like this, you should be using string.Concat instead of this string FullString = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + ConString;
  6. Again, refer to bullet number 2 and have your whole connection string inside one string.
  7. You're not passing in your connection string after your command statement OleDbCommand cmd = new OleDbCommand("INSERT into Donations (Name, Address, Phone, Email, DateDonated, Type, Make, Model, Notes, EnteredBy) Values(@n, @a, @p. @em, @d, @i, @IT, @m, @MO, @s, @NO)");
  8. On bullet 7, you are also passing in 10 values and providing 11 parameters including a typo after @p.
  9. Also on bullet 7, you do not specify any parameter values along side the parameter placeholders. (@p, value)
  10. There is no need to open a connection, if there may be one already open. You should check first.
  11. Your lack of using blocks would be more sufficient here, especially on your command. Using blocks are self disposing and you may find a link to that in my signature.
  12. catch (Exception ex) If you output a custom message in the exception box, how are you know which exception actually was thrown? Exception ex covers a boad spectrum of exceptions. You would be better to have explicit exception cased with multiple catch { } blocks to catch the range of different exceptions you code may throw. You can then add your exception ex as the last exception to be caught if all other exceptions are not thrown.

The following is for bullet 12 and is only an example. Catch exceptions are not for catching and ignoring errors, they are for catching errors and malfunctions and offer you an opportunity to rectify the error or handle it accordingly.
C#:
            catch (OleDbException dbex)
            {

            }
            catch (OverflowException ofe)
            {

            }
            catch (Exception ex) /* if the above exceptions aren't thrown, the below one will catch all others. Each exception of which ever exceptions you want to catch should be handled separately and not all in one base exception block like below */
            {
                MessageBox.Show("Failed to connect to data source");
            }

Hope these suggestions help. And you may want to read over this Exception.Message Property (System) and this Exception Class (System)
 
Thanks I rewrote the code and also rewrote the message box to give the exception


C#:
private void Save_button_Click(object sender, EventArgs e)
        {
          

            string ConString = System.IO.File.ReadAllText("FixIT.con");
            ConString = ConString.Replace(System.Environment.NewLine, string.Empty);
            System.Data.OleDb.OleDbConnection conn = new System.Data.OleDb.OleDbConnection();
            conn.ConnectionString = @"Provider=Microsoft.ACE.OLEDB.12.0;" + @"Data Source=" + ConString;
          
            
            try
            {
                string n = Name_textBox.Text;
                string a = Address_textBox.Text;
                string p = Phone_textBox.Text;
                string em = Email_textBox.Text;
                string d = Date_textBox.Text;
                string i = EnteredBy_textBox.Text;
                string IT = Type_cmbBx.Text;
                string m = Make_textBox.Text;
                string MO = Model_textBox.Text;
                string s = Serial_textBox.Text;
                string NO = Notes_textBox.Text;

                conn.Open();
                String my_querry = "INSERT INTO Donations (Name, Address, Phone, Email, DateDonated, Type, Make, Model, Serial, Notes, EnteredBy) Values(@n, @a, @p, @em, @d, @IT, @m, @MO, @s, @NO, @i)";
                OleDbCommand cmd = new OleDbCommand(my_querry, conn);
                cmd.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                MessageBox.Show("Failed due to " + ex.Message);
            }
            finally
            {
                conn.Close();
            }
        }

Now it seems to be connecting, if I put a breakpoint on the line OleDbCommand cmd = new OleDbCommand(my_querry, conn);
it states that conn is open in the Autos section of visual studio
but it is throwing up the exception

Failed due to No value given for one or more required parameters

I have looked at my query and there is the same amount of fields as values. All fields are named the same in the database and are in the same order. The fields in the database are also set to not required.

I have no idea what is causing this error
 
Here is what I see that is wrong, which you can improve.
  1. How do you know this reflects to a path; the file you want to read, and where is the location of your textfile, and why are you not path building using common practices? string ConString = System.IO.File.ReadAllText("FixIT.con");
  2. Why are you not putting the contents of the connection string into the quotation marks and have the constring equal that instead?
  3. Why are you reading the connection string from a file?
  4. You wouldn't need to use string.Replace if you bullet number 2
  5. This is really bad practice to string strings together like this, you should be using string.Concat instead of this string FullString = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + ConString;
  6. Again, refer to bullet number 2 and have your whole connection string inside one string.
  7. You're not passing in your connection string after your command statement OleDbCommand cmd = new OleDbCommand("INSERT into Donations (Name, Address, Phone, Email, DateDonated, Type, Make, Model, Notes, EnteredBy) Values(@n, @a, @p. @em, @d, @i, @IT, @m, @MO, @s, @NO)");
  8. On bullet 7, you are also passing in 10 values and providing 11 parameters including a typo after @p.
  9. Also on bullet 7, you do not specify any parameter values along side the parameter placeholders. (@p, value)
  10. There is no need to open a connection, if there may be one already open. You should check first.
  11. Your lack of using blocks would be more sufficient here, especially on your command. Using blocks are self disposing and you may find a link to that in my signature.
  12. catch (Exception ex) If you output a custom message in the exception box, how are you know which exception actually was thrown? Exception ex covers a boad spectrum of exceptions. You would be better to have explicit exception cased with multiple catch { } blocks to catch the range of different exceptions you code may throw. You can then add your exception ex as the last exception to be caught if all other exceptions are not thrown.

The following is for bullet 12 and is only an example. Catch exceptions are not for catching and ignoring errors, they are for catching errors and malfunctions and offer you an opportunity to rectify the error or handle it accordingly.
C#:
            catch (OleDbException dbex)
            {

            }
            catch (OverflowException ofe)
            {

            }
            catch (Exception ex) /* if the above exceptions aren't thrown, the below one will catch all others. Each exception of which ever exceptions you want to catch should be handled separately and not all in one base exception block like below */
            {
                MessageBox.Show("Failed to connect to data source");
            }

Hope these suggestions help. And you may want to read over this Exception.Message Property (System) and this Exception Class (System)

Hi in answer to question 3
The database file will not be on the PC that this program is. So I have created a button on another form that only enables or is visible if certain people log on, this opens a open file dialog so that an administrator can chose the path to the database, The path is then stored in the file FixIT.con
 
Your error is because of this ::
Also on bullet 7, you do not specify any parameter values along side the parameter placeholders. (@p, value)
If i wasn't clear, see this example. Look at the forth snipped on this link, do you see how i use parameters :: form connection to SQL server

NOTE: That example is for MySQL, but the use of parameters is more or less the same.
 
Your error is because of this ::

If i wasn't clear, see this example. Look at the forth snipped on this link, do you see how i use parameters :: form connection to SQL server

NOTE: That example is for MySQL, but the use of parameters is more or less the same.

You will have to forgive me I have had a stroke , Therefore my head gets really fuzzy after trying to concentrate for a while. I will have a look tomorrow when my head is clearer .
Thanks for the help
 
Before this line :: cmd.ExecuteNonQuery();
You need to add parameters ::
C#:
                        cmd.Parameters.AddWithValue("@n", Your string here);
                        cmd.Parameters.AddWithValue("@a", Your string here);
                        cmd.Parameters.AddWithValue("@p", Your string here);
                        cmd.Parameters.AddWithValue("@em", Your string here);

And so on..... add the rest of your parameter placeholders that are in your command query, as I have done above and then try execute again. You obviously need to replace the Your string here with the values you want to insert from your assumed UI
 
C#:
conn.ConnectionString = @"Provider=Microsoft.ACE.OLEDB.12.0;" + @"Data Source=" + ConString;
The point of a verbatim string literal, i.e. a string literal prefixed with the '@' symbol, is to allow you to include backslashes without having to escape them. You have no backslashes in your literal - your file path is contained in a variable - so the verbatim string literal is pointless. It won't hurt, unless you need to escape something else, but it won;t help. Also, there is NEVER a reason to concatenate two literals.
 
Last edited:
Before this line :: cmd.ExecuteNonQuery();
You need to add parameters ::
C#:
                        cmd.Parameters.AddWithValue("@n", Your string here);
                        cmd.Parameters.AddWithValue("@a", Your string here);
                        cmd.Parameters.AddWithValue("@p", Your string here);
                        cmd.Parameters.AddWithValue("@em", Your string here);

And so on..... add the rest of your parameter placeholders that are in your command query, as I have done above and then try execute again. You obviously need to replace the Your string here with the values you want to insert from your assumed UI
Thanks for that it works perfectly now. What is stupid is that last night my brain wasn't seeing the lines. I opened up a example web site this morning that I was using last night and the lines are right there in the example of the code.
 
The point of a verbatim string literal, i.e. a string literal prefixed with the '@' symbol, is to allow you to include backslashes without having to escape them. You have no backslashes in your literal - your file path is contained in a variable - so the verbatim string literal is pointless. It won't hurt, unless you need to escape something else, but it won;t help. Also, there is NEVER a reason to concatenate two literals.

Thanks I have now deleted the @ symbols from the connection string. I was wondering what they meant but included them because the examples I had been working from had them.
 
Thanks I have now deleted the @ symbols from the connection string. I was wondering what they meant but included them because the examples I had been working from had them.
The examples would have used them because they included a literal file path in the connection string and the slashes would have to have been escaped otherwise. File and folder paths is the most common use for verbatim string literals.
 
Back
Top Bottom