Add to a table in the c# asp.net using the ASPNETDB.MDF

Question

Is there a way to access a table that I have added to the default ASPNETDB.MDF database that is given to you when you create a new web page in asp.net 4.0. I have added a table called BuilderTable with 5 columns. Also I need it to increment ID on its own. I have set the is identity option to yes and the identity increment to 1 so how can I get it to work without sending it a value for the ID column in my code behind. Here is said code in c#:(I have it within a switch statement so dont mind the case 1)

     case "1":
            if (Roles.IsUserInRole(usr.UserName, "Builder") == false)//if the user is not already in the builder role then add them
            {
                Roles.AddUserToRole(usr.UserName, "Builder");//here we add the user into the builder role

                string connection = ConfigurationManager.ConnectionStrings["ApplicationServices"].ConnectionString;
                SqlConnection conn = null;
                conn = new SqlConnection(connection);
                conn.Open();

                using (SqlCommand cmd = new SqlCommand())
                {
                    int nothing = 0;
                    string query = String.Format("INSERT INTO 'BuilderTable' VALUES('{0}', '{1}', '{2}', '{3}', '{4}')", nothing, p.fName, p.lName, p.Address, usr.Email);
                    cmd.Connection = conn;
                    cmd.CommandType = CommandType.Text;
                    cmd.CommandText = query;
                    cmd.ExecuteNonQuery();
                }
            }
            break;

This code results in the error:

Incorrect syntax near 'BuilderTable'

Thanks in advance!

EDIT Thought I should clarify I have this declared elsewhere in the code

      string userName = Request.QueryString["user"];
    MembershipUser usr = Membership.GetUser(userName);
    ProfileCommon p = Profile.GetProfile(usr.UserName);

@valamas-AUS It solved one error but now it says An explixit value for the identity column in table BuilderTable can only be specified when a column lis is used and IDENTITY_INSERT is ON Any idea what that means? — Andy
– Andy, Commented Jun 19, 2013 at 23:29
@valamas-AUS Ahh yes this is the second question in my post. How can I insert without sending anything in to the ID field. — Andy
– Andy, Commented Jun 19, 2013 at 23:34
@valamas-AUS Ahh! I see I did not know you could do that. Ill try it now — Andy
– Andy, Commented Jun 19, 2013 at 23:35

Valamas · Accepted Answer · 2013-06-19 23:47:08Z

1

You need to take away the single quotes from around the table name. I.e. 'BuilderTable'

(You should be using parametrized queries. Look up "Sql Injection Attacks".)

Your identity error is due to you trying to insert values into the identity field. You should insert values by specifying the fields.

 INSERT INTO BLAH (Fields,,,,) VALUES(Values,,,,,)

edited Jun 19, 2013 at 23:47

answered Jun 19, 2013 at 23:37

Valamas

24.8k25 gold badges111 silver badges181 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Add to a table in the c# asp.net using the ASPNETDB.MDF

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related