C# Help Needed

[Aquilla]

I am quite new with C#, i have decided to go from PHP to Software Development and has thus far been studying a whole lot of books and tutorials. But this code down here is giving me trouble, please note this is a very basic operation I'm trying to just understand how things work...

Any help would be appreciated.

The following Code does nothing, it does not INSERT a new row into the database. I get the SELECT procedure to work but not this? Any Ideas?

Thanks

private void button1_Click(object sender, EventArgs e)
        {
            string FirstName;
            string LastName;
            string Country;

            FirstName = Convert.ToString(textBox1.Text);
            LastName = Convert.ToString(textBox2.Text);
            Country = Convert.ToString(textBox3.Text);

            //GREATE CONNECTION TO DATABASE
            SqlConnection myConnection = new SqlConnection("Data Source=.\\SQLExpress;AttachDbFilename=|DataDirectory|\\DataBaseAccessDb.mdf;Integrated Security=True;User Instance=True;");
            

            SqlCommand myCommand = myConnection.CreateCommand();
            myCommand.CommandType = CommandType.Text;
            myCommand.CommandText = "INSERT INTO Users (FirstName, LastName, Country) VALUES (@FirstName, @LastName, @Country)";
            myCommand.Parameters.AddWithValue("@FirstName", FirstName);
            myCommand.Parameters.AddWithValue("@LastName", LastName);
            myCommand.Parameters.AddWithValue("@Country", Country);
            try
            {
                myConnection.Open();
                myCommand.ExecuteNonQuery();

            }
            catch (Exception ex)
            {
                richTextBox1.Text = "There was an Error: " + Convert.ToString(ex);
                throw;
            }
            myConnection.Close();
            
        }

 

[hyarion]

I think I see your problem - you've done nearly everything except actually make the connection.

I'm not a c# guru but I know my way around most of it.

Looking at one of my small projects with a database I have this which you are missing:
MySqlCommand cmd = new MySqlCommand(query, connection);

After that you then add your values and such and then later the executenonquery() as you've been doing.

 

[bin3]

Don't know offhand, but I think ...

//CREATE CONNECTION TO DATABASE
SqlConnection myConnection = new SqlConnection("Data Source=.\\SQLExpress;AttachDbFilename=|DataDirectory|\\DataBaseAccessDb.mdf;Integrated Security=True;User Instance=True;");
SqlCommand myCommand = myConnection.CreateCommand()

is the same as your MySqlCommand cmd ... line

What I suspect is that you are not actually firing the query. An update / insert or delete is still a query, so I would expect the line <b>myCommand.ExecuteNonQuery();</b> to be a bit different. To me the ExecuteNonQuery implies you are attempting a DDL statement or something similar, i.e. creating / dropping tables / indexes / views / truncating tables, etc, etc.

There should be a method for Execute Query with no Result Set, though even there, there should be a way of finding out how many rows were affected, i.e. something like rowsAffected = myCommand.ExecuteInsert();

(Quick search implies it is probably something like cmdqry.ExecuteScalar()) ...

http://www.java2s.com/Tutorial/CSharp/0560__ADO.Net/Executenonquerytoinsertarecordrow.htm

 

[stevenv]

Would be nice to post the exeption text and stack trace.

 

[thomeval]

Don't know offhand, but I think ...

//CREATE CONNECTION TO DATABASE
SqlConnection myConnection = new SqlConnection("Data Source=.\\SQLExpress;AttachDbFilename=|DataDirectory|\\DataBaseAccessDb.mdf;Integrated Security=True;User Instance=True;");
SqlCommand myCommand = myConnection.CreateCommand()

is the same as your MySqlCommand cmd ... line

What I suspect is that you are not actually firing the query. An update / insert or delete is still a query, so I would expect the line <b>myCommand.ExecuteNonQuery();</b> to be a bit different. To me the ExecuteNonQuery implies you are attempting a DDL statement or something similar, i.e. creating / dropping tables / indexes / views / truncating tables, etc, etc.

There should be a method for Execute Query with no Result Set, though even there, there should be a way of finding out how many rows were affected, i.e. something like rowsAffected = myCommand.ExecuteInsert();

(Quick search implies it is probably something like cmdqry.ExecuteScalar()) ...

http://www.java2s.com/Tutorial/CSharp/0560__ADO.Net/Executenonquerytoinsertarecordrow.htm

ExecuteNonQuery() is used for non-SELECT queries (it returns the number of results affected). ExecuteScalar() is used to return a single result (such as the result of a sum or count query).

 

[stevenv]

Also, this

catch (Exception ex)
{
   richTextBox1.Text = "There was an Error: " + Convert.ToString(ex);
   throw;
}

should be

catch (Exception ex)
{
   richTextBox1.Text = "There was an Error: " + ex.ToString();
   throw;
}

Although equivalent, using Convert.ToString() to invoke Exception.ToString() is bad form.

 

[stevenv]

ExecuteNonQuery() is used for queries that don't return results (such as rows or a number). ExecuteScalar() is used to return a single result (such as the number of rows affected by a query).

From MSDN:

You can use the ExecuteNonQuery to perform catalog operations (for example, querying the structure of a database or creating database objects such as tables), or to change the data in a database without using a DataSet by executing UPDATE, INSERT, or DELETE statements.

http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlcommand.executenonquery.aspx

 

[stevenv]

Something else: by rethrowing the exception in the catch block you are also preventing this code from executing:

myConnection.Close();

So maybe there other SqlConnection instances holding locks on the database (unless you restarted the application, in which case the connections might've been closed for you) but more information from the exception would help identifying this. This is where using statements come in handy:

using (SqlConnection myConnection = new SqlConnection("Data Source=.\\SQLExpress;AttachDbFilename=|DataDirectory|\\DataBaseAccessDb.mdf;Integrated Security=True;User Instance=True;"))
{
   SqlCommand myCommand = myConnection.CreateCommand();
   ...
}

Not necessary to include the myConnection.Close().

 

[Aquilla]

The thing is when i run the code, i do not get any exceptions, its as if the code runs trough everything correctly and then closes the connection. When i however check my Database Explorer, no new rows have been added. :/

Thanks for all the input guys!

 

[thomeval]

The thing is when i run the code, i do not get any exceptions, its as if the code runs trough everything correctly and then closes the connection. When i however check my Database Explorer, no new rows have been added. :/

Thanks for all the input guys!

Are you refreshing the data when you check with the Database Explorer?

One other thing worth checking is the result from ExecuteNonQuery(). The result should be 1, if the row was added successfully.

...
int rowsAffected = myCommand.ExecuteNonQuery();
richTextBox1.Text = rowsAffected + " rows were affected.";
...

 

[Aquilla]

Are you refreshing the data when you check with the Database Explorer?

One other thing worth checking is the result from ExecuteNonQuery(). The result should be 1, if the row was added successfully.

...
int rowsAffected = myCommand.ExecuteNonQuery();
richTextBox1.Text = rowsAffected + " rows were affected.";
...

I get 1 Row was affected. I have refreshed the data in the database Explorer and still it shows nothing :/

(Can it be possible that my SQL Server Express is not functioning as it should)...?

 

[dexster]

I copied and pasted your code into a new VS solution. Worked for me. Data successfully saved into the db.

Only change I made was hardcoding the firstname, lastname and country.

 

[graviti]

Try running your SELECT statement again, and see if it shows. Your data explorer might be having a funny moment. Also, do you have any rules set on the database.

Not a C# coder, but I don't understand this

FirstName = Convert.ToString(textBox1.Text);
LastName = Convert.ToString(textBox2.Text);
Country = Convert.ToString(textBox3.Text);

Why convert a String to a String. Surely textBox1.Text is already in the String format, so why call a toString on it.

 

[DominionZA]

Save yourself the effort and use a decent ORM tool.

Subsonic I used for many years and is excellent.

NHibernate I started using this year (Jan) and am enjoying it. Way more complex than Subsonic too configure, bit also more powerful and feature rich.

Since .NET 4 I moved from Subsonic to Entity Framework 4. My preferred ORM tool.

Sent from my HTC Desire HD using Tapatalk

 

[Fuma]

Are you sure you are refreshing the same table. You might be connected to a different instance. It happens to all of us.

 

[stevenv]

Is it possible that the file |DataDirectory|\\DataBaseAccessDb.mdf is being overwritten when you build or are you looking at a file different to the one being updated? Try searching your solution folder for all *.mdf files.

 

[Aquilla]

Are you sure you are refreshing the same table. You might be connected to a different instance. It happens to all of us.

Omw how embarrassing this is :/ You are quite correct Fuma and stevenv! It keeps on accessing the mdf in the bin/Debug and while the IDE was only refreshing the one in the main project folder. The code works now 100%, thanks you guys i appreciate it!

 

[Aquilla]

One last question, i have a new issue. The data that i have inserted into the Sql database now only shows the new entries when i restart VStudio completely. I refresh it but nothing, am i doing something wrong?

 

[Aquilla]

Never mind figured it out. Thanks

 

[stevenv]

Omw how embarrassing this is :/ You are quite correct Fuma and stevenv! It keeps on accessing the mdf in the bin/Debug and while the IDE was only refreshing the one in the main project folder. The code works now 100%, thanks you guys i appreciate it!

No worries, we've all had moments like that. Good to hear it's sorted.