6 Replies - 551 Views - Last Post: 10 September 2013 - 04:01 AM Rate Topic: -----

#1 joshualewis  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 7
  • Joined: 24-October 12

Adding to Database with C#

Posted 10 September 2013 - 02:33 AM

I am working on my first application that involves a database and I'm having some issues. I have looked on a few different tutorials to try and find an answer, and after a few days I finally decided to see if I can get some help here.

The actual database I'm creating is much larger, but I have put it on hold until I can get this working, so I have created a smaller one with just a few entries. The following code is the practice database I am working on to figure out what I'm doing. The database connects correctly, but the table is not updated from the form when I call the addToDatabase method, and I can't seem to figure out why. Any help would be appreciated.


This is the database access class:

class Database
    {

        private const string connectionString = "Data Source=(LocalDB)\\v11.0;AttachDbFilename=|DataDirectory|\\Test.mdf;Integrated Security=True";
        public SqlConnection connection = new SqlConnection(connectionString);

        public void openConnection()
        {
            try
            {
                connection.Open();
            }
            catch
            {
                MessageBox.Show("Connection failed");
            }
        }

        public void closeConnection()
        {
            if (connection != null)
                connection.Close();
        }

        public void addToDatabase(string fName, string lName, int age)
        {
            string insertString = @"
                INSERT into dbo.NAMES
                (FirstName, LastName, Age)
                VALUES (@FirstName, @LastName, @Age)";

            SqlCommand cmd = new SqlCommand(insertString, connection);

            
            
            SqlParameter lNameParameter = new SqlParameter();
            lNameParameter.ParameterName = "@LastName";
            lNameParameter.Value = lName;
            cmd.Parameters.Add(lNameParameter);

            SqlParameter fNameParameter = new SqlParameter();
            fNameParameter.ParameterName = "@FirstName";
            fNameParameter.Value = fName;
            cmd.Parameters.Add(fNameParameter);

            SqlParameter ageParameter = new SqlParameter();
            ageParameter.ParameterName = "@Age";
            ageParameter.Value = age;
            cmd.Parameters.Add(ageParameter);
            
            openConnection();
            cmd.ExecuteNonQuery();
            closeConnection();
        }

    }



This is the code I'm using to add to the database:

public partial class Form1 : Form
    {
        Database database = new Database();

        public Form1()
        {
            InitializeComponent();
        }

        private void clearForm()
        {
            
            fNameBox.Text = "";
            lNameBox.Text = "";
            ageBox.Text = "";
        }

        private void addBtn_Click(object sender, EventArgs e)
        {
            database.addToDatabase(fNameBox.Text, lNameBox.Text, Convert.ToInt32(ageBox.Text));
            clearForm();
        }
    }



Is This A Good Question/Topic? 0
  • +

Replies To: Adding to Database with C#

#2 Michael26  Icon User is offline

  • DIC-head, major DIC-head
  • member icon

Reputation: 362
  • View blog
  • Posts: 1,534
  • Joined: 08-April 09

Re: Adding to Database with C#

Posted 10 September 2013 - 02:58 AM

Post deleted (irelevant)

This post has been edited by Michael26: 10 September 2013 - 03:00 AM

Was This Post Helpful? 0
  • +
  • -

#3 Michael26  Icon User is offline

  • DIC-head, major DIC-head
  • member icon

Reputation: 362
  • View blog
  • Posts: 1,534
  • Joined: 08-April 09

Re: Adding to Database with C#

Posted 10 September 2013 - 03:05 AM

Lets test this.
int i = cmd.ExecuteNonQuery();



See if the int has any value, ExecuteNonQuery() executes a Transact-SQL statement against the connection and returns the number of rows affected.
And also try to run the sql query directly in the server to see if it works.

This post has been edited by Michael26: 10 September 2013 - 03:07 AM

Was This Post Helpful? 0
  • +
  • -

#4 joshualewis  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 7
  • Joined: 24-October 12

Re: Adding to Database with C#

Posted 10 September 2013 - 03:11 AM

View PostMichael26, on 10 September 2013 - 05:05 AM, said:

Lets test this.
int i = cmd.ExecuteNonQuery();



See if the int has any value, ExecuteNonQuery() executes a Transact-SQL statement against the connection and returns the number of rows affected.
And also try to run the sql query directly in the server to see if it works.



It still isn't adding the information to the table. As for trying the sql query directly, I actually used Visual Studio to create the database. I don't think I currently have anything to query directly into the database. I'm rather new to databases.
Was This Post Helpful? 0
  • +
  • -

#5 Michael26  Icon User is offline

  • DIC-head, major DIC-head
  • member icon

Reputation: 362
  • View blog
  • Posts: 1,534
  • Joined: 08-April 09

Re: Adding to Database with C#

Posted 10 September 2013 - 03:38 AM

Check to see that you're hitting the right database. A copy will be copied to the bin/Debug folder so click on your MDF file and check the properties and make sure that Copy To Output Directory is set to No Not Copy.

The problem probably is when you created the MDF (database file) file in your Visual 2010 project, a copy of that was made inside the project's folder. When you run/debug the program, another copy of the this file is made and is put in the \bin\Debug\ folder.
This meant that all reads/writes were done to the copy in the bin\Debug folder. However, MDF file you are looking into to verify if records were inserted or not, was in the project's folder. So basically, there are two MDF files, and you were writing the records into one file, but was trying to find them in the other :)

When you add an MDF file into your VS2010 Project, VS2010 by default makes a connection to that MDF file. The MDF file used for this purpose was the one placed in the project's folder, NOT the one in bin\Debug\ folder.

So what I've done now is that I've removed the MDF file reference from my project, which removes the copy present in the project's folder. However, I DO have a copy of MDF file in the bin\Debug\ folder, which I connect to from within my application. And if I want to browse the MDF file outside my project, I use SQL Management Studio. The only problem here is that an MDF file can only be used by one program at a given time. So if I have to use it with my application, I have to take it offline from SQL Management studio, and vica versa.
Was This Post Helpful? 0
  • +
  • -

#6 joshualewis  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 7
  • Joined: 24-October 12

Re: Adding to Database with C#

Posted 10 September 2013 - 03:52 AM

Okay, I believe I'm following you. However, once I change it to never copy, it will not connect to the database. It gives me the messagebox in the catch of the openConnection method saying the connection failed.
Was This Post Helpful? 0
  • +
  • -

#7 Michael26  Icon User is offline

  • DIC-head, major DIC-head
  • member icon

Reputation: 362
  • View blog
  • Posts: 1,534
  • Joined: 08-April 09

Re: Adding to Database with C#

Posted 10 September 2013 - 04:01 AM

I can't think anything at the moment, maybe someone with more knowledge will show up. Sorry
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1