hi,

im making a page where users click on the "REGISTER" button to register into the program. the program gives the user a username by taking the first 3 characters of the first name and first 3 characters of the last name. if the username already exists i just add an incrementing number to it, for example username1, username2..etc.

i dont get any error when i run this code but its not inserting it into the database either and i cant figure out what the problem is.
Any help is greatly appreciated!
thanks from now!

private void button1_Click(object sender, EventArgs e)
        {
            System.Data.SqlClient.SqlConnection con = new SqlConnection();
            con.ConnectionString = "Data Source=.\\SQLEXPRESS;AttachDbFilename=|DataDirectory|\\AltarianChessDB.mdf;Integrated Security=True;User Instance=True";

            con.Open();

            SqlDataAdapter da = new SqlDataAdapter("SELECT Username from Players", con);
            DataTable dt1 = new DataTable();
            da.Fill(dt1);

 string uname = firstnametxt.Text.Substring(0, 3) + secondnametxt.Text.Substring(0, 3);
            bool found = false;

            int j=0;

            for (int i = 0; i < dt1.Rows.Count; i++)
            {
                if (dt1.Rows[i][0].ToString().Substring(0,6) == uname)
                {
                    found = true;
                    j=i;
                }
            }

            if (found == true)
            {
                string numstring = dt1.Rows[j][0].ToString().Substring(6, dt1.Rows[j][0].ToString().Length);
                int num = Convert.ToInt16(numstring) + 1;
                numstring = num.ToString();
                uname = uname + numstring;

            }
            con.Close();
            con.Open();
            int creditvalue = 30;
           
            System.Data.SqlClient.SqlCommand cmd = new SqlCommand();
            cmd.Connection = con;
           
cmd.CommandText=("insert into Players (Username,FirstName,SecondName,PlanetOfOrigin,Password,Credit) values ('"+uname+"','"+firstnametxt.Text.Trim()+"','"+secondnametxt.Text.Trim()+"','"+planetoforigintxt.Text.Trim()+"','"+passwordtxt.Text.Trim()+"','"+creditvalue+"')");
            cmd.ExecuteNonQuery();
             MessageBoxButtons buttonTyperegistered = MessageBoxButtons.OK;
            MessageBoxIcon iconTyperegistered = MessageBoxIcon.Information;
            DialogResult resultregistered = MessageBox.Show("You've successfully registered to play!\n Your username is " + uname, "Altarian Chess", buttonTyperegistered, iconTyperegistered, 0, 0);
           
           con.Close();

            Form4 frm4 = new Form4();
            frm4.Show();
            frm4.Visible = true;

            Form3 frm3 = new Form3();
            frm3.Hide();
            frm3.Visible = false;
 
        }

First, you should always wrap your database code in try/catch blocks. So many things can go wrong with database code, such as connection problems, column-width issues, format conversions, etc. Opening a connection and running a command/SQL against a connection should always be in a try/catch.

You have "creditvalue" defined as an INT in the code, but in your INSERT statement, you have it wrapped in single-quotes. How is it defined in your table?

Also, you close, then open the connection to the database, right before your INSERT - shouldn't cause a problem, but is unnecessary.

-Mike

hi thanks for your reply,

i added try catch blocks and debugged the code line by line but it doesnt throw any exception and doesnt give any error but still doesnt insert into the database!! im so stuck right now :(
is there anything perhaps that i need to do outside the code? the only thing i did manually outside the code (but still within the c#.net environment) was to create a new database and add a table with its columns..i followed the steps from this website to do that:

http://www.homeandlearn.co.uk/csharp/csharp_s12p2.html

thanks for the help. i cant figure out what the problem could be :(

OK, there's an issue with how VS2005 organizes the databases you add to a project. If you'll notice, there will be 2 databases in your project - one at the "root" level of your project, and another in the "bin" folder of your project.

In order to get my copy to work, I did this:

In the Solution Explorer, click once on the database, and look down in the "Properties" section, for the parm "CopyToOutputDirectory" and set it to "Do Not Copy". Then, in the Server Explorer, right-click on the database and select "Modify Connection...". Hit the "Browse..." button and select the copy of the DB in the "bin" folder. You may see that that version of the database already has your record in it.

Anyway, it's because there are 2 versions of your database, and the Server Explorer is looking at the "root" level, and your program is working with the one in the "bin" folder.

Let me know if that works for you. Here's a link to an article that helps.

Hint: the code you use to add a digit to an already-existing user has a problem - but I'll leave that for you to work on ;)

thanks for your reply.
so i tried what you said and it seemed to be working but what happened now is that the database in the bin folder seems to have deleted on its own so the program is back to showing the old database again...but when i insert again it doesnt show and now theres no database in my bin folder...?
also is there any change i should make with the connection string?
thanks!

Wow. I had the same thing happen to me - the "bin" folder version of the database vanished, and the only one left is the one in the "root" folder.

Not the greatest solution, but I added this line to my "Form1()" method (it would work in any other initialization location):

AppDomain.CurrentDomain.SetData("DataDirectory", "Path-to-Root-Folder");

The "Path-to-Root-Folder" value could be set up to be read from a config file at runtime, or the statement could be eliminated in the final version, since the "DataDirectory" portion of the connection string will check the .EXE location for the database by default.

I stopped/restarted the dev environment and ensured that it now works consistently.

ok so im not the only one these weird things are happening to! :O

so what i did is i set the "copy to output directory" back to "copy always" then built the solution again just to get the database file back in my bin folder...now i set everything back to how you explained before. (by the way i realized it gets deleted when i close visual studio then open it again).

so i added that line of code as is to my form method im using..but i didnt quite understand if i write that line as is or what should i write for values? i wrote it as is and got this error now when defining my connection string:

Invalid value for key 'attachdbfilename'.

thanks

The line you added to your initialization:

AppDomain.CurrentDomain.SetData("DataDirectory", "Path-to-Root-Folder");

the "Path-to-Root-Folder" value should be replaced with the actual path to where your project is residing. Say, for example, that is "C:\Projects\CardGame", the statement would look like:

AppDomain.CurrentDomain.SetData("DataDirectory", "C:\\Projects\\CardGame");

Again, you can remove this statement when you deploy, because one of the default locations the program will search for the database is in the same folder as the .EXE. Or, you can load the value for the path from your App.Config file.

hi,
thanks so much for your reply. so ive been playing around with the things youve explained trying to figure out whats going on in the system.

so i added that line you mentioned in 2 of my forms...one for login and one for registration..but when i performed a "login" in my program, it kept giving some error saying "An attempt to attach an auto-named database for file ... failed. A database with the same name exists, or specified file cannot be Opened, or it is located on UNC share"....and when i did a registration, it showed as if its done succesfully but when i looked at the table contents it wasnt showing again...
so i removed the lines from each form and tried to register and login again and for some odd reason it now works and is not deleting the database file in the bin folder when i close visual studio and open it again (which it kept doing before)! the only thing is everytime i open visual studio again i have to modify the connection to the database in the bin folder because it keeps setting it back to the one in the project folder.
is there anyway to keep the "modify connection" part fixed on the database file in the bin folder? and if not will this cause any problems when i deploy the application?

thanks so much again for your help!! i appreciate it!

This article has been dead for over six months. Start a new discussion instead.