CODEWITHSUNDEEP
c#asp.netdatabase
adL
adL

Reputation: 79

Inserting Data in Access Database from ASP/C# Web Form

I'm writing a page in ASP/C# where I have to take a form that I have created and insert it into a MS Access Database. The MS Access database is setup with the following fields:

ID, UserName, Passwrd, FirstName, LastName, Address, Address2, City, State, ZipCode, Email, Gender, Age, ShirtSize, PantSize, EmailSubscribe.

I'm getting the following error:

Exception Details: System.Data.OleDb.OleDbException: No value given for one or more required parameters.

I can't figure out which parameter is missing a value. Here is the stack trace:

[  OleDbException (0x80040e10): No value given for one or more required parameters.]
   System.Data.OleDb.OleDbCommand.ExecuteCommandTextErrorHandling(OleDbHResult hr) +992156
   System.Data.OleDb.OleDbCommand.ExecuteCommandTextForSingleResult(tagDBPARAMS dbParams, Object& executeResult) +255
   System.Data.OleDb.OleDbCommand.ExecuteCommandText(Object& executeResult) +188
   System.Data.OleDb.OleDbCommand.ExecuteCommand(CommandBehavior behavior, Object& executeResult) +58
   System.Data.OleDb.OleDbCommand.ExecuteReaderInternal(CommandBehavior behavior, String method) +161
   System.Data.OleDb.OleDbCommand.ExecuteNonQuery() +113
   ASP.ado_aspx.SubmitForm(Object sender, EventArgs e) in e:\ectserver\ADELEO10\ado.aspx:165
   System.Web.UI.WebControls.Button.OnClick(EventArgs e) +111
   System.Web.UI.WebControls.Button.RaisePostBackEvent(String eventArgument) +110
   System.Web.UI.WebControls.Button.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument) +10
   System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument) +13
   System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData) +36
   System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +1565

Here is the code behind the page:

                public void SubmitForm(object sender, EventArgs e)
                {
                    if(Page.IsValid)
                    {
                        string provider = ConfigurationManager.ConnectionStrings["databaseConnString"].ProviderName; 

                        DbProviderFactory factory = DbProviderFactories.GetFactory(provider);

                       //Open Connection
                        DbConnection conn = factory.CreateConnection();

                        //Assign Connection String
                        conn.ConnectionString = ConfigurationManager.ConnectionStrings["databaseConnString"].ConnectionString; 
                        //Connection Open
                        conn.Open();

                        //Initialize Command
                        DbCommand comm = conn.CreateCommand();
                        //Tell command which connection it will use
                        comm.Connection = conn;
                        //Give command SQL to execute
                        comm.CommandText ="Insert into userinfo(UserName, Passwrd, FirstName, LastName, Address, Address2, City, State, ZipCode, Email, Gender, Age, ShirtSize, PantSize, EmailSubscribe) values(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";                     

                        DbParameter param;

                        param = comm.CreateParameter();
                        param.DbType = DbType.String;
                        param.Direction = ParameterDirection.Input;
                        param.Value = userName.Text;

                        comm.Parameters.Add(param);                     

                        param = comm.CreateParameter();
                        param.DbType = DbType.String;
                        param.Direction = ParameterDirection.Input;
                        param.Value = password.Text;

                        comm.Parameters.Add(param); 

                        param = comm.CreateParameter();
                        param.DbType = DbType.String;
                        param.Direction = ParameterDirection.Input;
                        param.Value = firstName.Text;

                        comm.Parameters.Add(param);                                 

                        param = comm.CreateParameter();
                        param.DbType = DbType.String;
                        param.Direction = ParameterDirection.Input;
                        param.Value = lastName.Text;

                        comm.Parameters.Add(param);

                        param = comm.CreateParameter();
                        param.DbType = DbType.String;
                        param.Direction = ParameterDirection.Input;
                        param.Value = address.Text;

                        comm.Parameters.Add(param); 

                        param = comm.CreateParameter();
                        param.DbType = DbType.String;
                        param.Direction = ParameterDirection.Input;
                        param.Value = address2.Text;

                        comm.Parameters.Add(param); 

                        param = comm.CreateParameter();
                        param.DbType = DbType.String;
                        param.Direction = ParameterDirection.Input;
                        param.Value = city.Text;

                        comm.Parameters.Add(param);

                        param = comm.CreateParameter();
                        param.DbType = DbType.String;
                        param.Direction = ParameterDirection.Input;
                        param.Value = state.Text;

                        comm.Parameters.Add(param);

                        param = comm.CreateParameter();
                        param.DbType = DbType.Int32;
                        param.Direction = ParameterDirection.Input;
                        param.Value = Int32.Parse(zipCode.Text);

                        comm.Parameters.Add(param);

                        param = comm.CreateParameter();
                        param.DbType = DbType.String;
                        param.Direction = ParameterDirection.Input;
                        param.Value = email.Text;

                        comm.Parameters.Add(param);

                        param = comm.CreateParameter();
                        param.DbType = DbType.String;
                        param.Direction = ParameterDirection.Input;
                        param.Value = GenderRadioGroup.Text;

                        comm.Parameters.Add(param);

                        param = comm.CreateParameter();
                        param.DbType = DbType.Int32;
                        param.Direction = ParameterDirection.Input;
                        param.Value = Int32.Parse(age.Text);

                        comm.Parameters.Add(param);

                        param = comm.CreateParameter();
                        param.DbType = DbType.String;
                        param.Direction = ParameterDirection.Input;
                        param.Value = drpShirtSizeCategory.SelectedValue;

                        comm.Parameters.Add(param);

                        param = comm.CreateParameter();
                        param.DbType = DbType.String;
                        param.Direction = ParameterDirection.Input;
                        param.Value = drpPantSizeCategory.SelectedValue;

                        comm.Parameters.Add(param);

                        param = comm.CreateParameter();
                        param.DbType = DbType.String;
                        param.Direction = ParameterDirection.Input;
                        param.Value = EmailRadioGroup.Text;

                        comm.Parameters.Add(param);

                        //Execute command get back result via reader
                        int totalCount = comm.ExecuteNonQuery();

                        DbCommand comm2 = conn.CreateCommand();
                        comm2.CommandText = "Select @@Identity";
                        comm2.Connection = conn;

                        String id = comm2.ExecuteScalar().ToString();
                        conn.Close();

                        lblMessage.Text = "ID of User Added = " +id;
                    }
                }

Any help is appreciated. Thanks!

Upvotes: 0

Views: 2441

Answers (2)

Nico de Wit
Nico de Wit

Reputation: 73

To make sure you have the correct identity it is better to call IDENT_CURRENT('TableName')

DbCommand comm2 = conn.CreateCommand();
comm2.CommandText = "Select IDENT_CURRENT('userinfo')";
comm2.Connection = conn;

String id = comm2.ExecuteScalar().ToString();
conn.Close();

Please see Here for an explanation.

Upvotes: 0

Rene
Rene

Reputation: 556

Try this

string provider = ConfigurationManager.ConnectionStrings["databaseConnString"].ProviderName; 

        DbProviderFactory factory = DbProviderFactories.GetFactory(provider);

       //Open Connection
        DbConnection conn = factory.CreateConnection();

        //Assign Connection String
        conn.ConnectionString = ConfigurationManager.ConnectionStrings["databaseConnString"].ConnectionString; 
        //Connection Open
        conn.Open();

        //Initialize Command
        DbCommand comm = conn.CreateCommand();
        //Tell command which connection it will use
        comm.Connection = conn;
        //Give command SQL to execute
        comm.CommandText ="Insert into userinfo(UserName, Passwrd, FirstName, LastName, Address, Address2, City, State, ZipCode, Email, Gender, Age, ShirtSize, PantSize, EmailSubscribe) values(@UserName, @Passwrd, @FirstName, @LastName, @Address, @Address2, @City, @State, @ZipCode, @Email, @Gender, @Age, @ShirtSize, @PantSize, @EmailSubscribe)";                     


        SqlParameter userName = new SqlParameter("@UserName", SqlDbType.VarChar);
        userName .Value = userName.Text;
        comm.Parameters.Add(userName);

        SqlParameter password = new SqlParameter("@Passwrd", SqlDbType.VarChar);
        password .Value = password.Text;
        comm.Parameters.Add(password);

        SqlParameter firstName = new SqlParameter("@FirstName", SqlDbType.VarChar);
        firstName .Value = FirstName.Text;
        comm.Parameters.Add(firstName);

        SqlParameter lastName = new SqlParameter("@LastName", SqlDbType.VarChar);
        lastName .Value = lastName.Text;
        comm.Parameters.Add(lastName);


        SqlParameter address = new SqlParameter("@Address", SqlDbType.VarChar);
        address.Value = address.Text;
        comm.Parameters.Add(address);

        SqlParameter address2 = new SqlParameter("@Address2", SqlDbType.VarChar);
        address2.Value = address2.Text;
        comm.Parameters.Add(address2); 

        SqlParameter city = new SqlParameter("@City", SqlDbType.VarChar);
        city.Value = city.Text;
        comm.Parameters.Add(city); 

        SqlParameter state = new SqlParameter("@State", SqlDbType.VarChar);
        state.Value = state.Text;
        comm.Parameters.Add(state); 

        SqlParameter zipCode = new SqlParameter("@ZipCode", SqlDbType.VarChar);
        zipCode.Value = zipCode.Text;
        comm.Parameters.Add(zipCode);

        SqlParameter email = new SqlParameter("@Email", SqlDbType.VarChar);
        email.Value = email.Text;
        comm.Parameters.Add(email);

        SqlParameter genderRadioGroup = new SqlParameter("@GenderRadioGroup", SqlDbType.VarChar);
        genderRadioGroup.Value = genderRadioGroup.Text;
        comm.Parameters.Add(genderRadioGroup);

        SqlParameter age = new SqlParameter("@Age", SqlDbType.VarChar);
        age.Value = age.Text;
        comm.Parameters.Add(age);

        SqlParameter drpShirtSizeCategory = new SqlParameter("@ShirtSize", SqlDbType.VarChar);
        drpShirtSizeCategory.Value = drpShirtSizeCategory.Text;
        comm.Parameters.Add(drpShirtSizeCategory);

        SqlParameter drpPantSizeCategory = new SqlParameter("@PantSize", SqlDbType.VarChar);
        drpPantSizeCategory.Value = drpPantSizeCategory.Text;
        comm.Parameters.Add(drpPantSizeCategory);

        SqlParameter emailRadioGroup = new SqlParameter("@EmailSubscribe", SqlDbType.VarChar);
        emailRadioGroup.Value = EmailRadioGroup.Text;
        comm.Parameters.Add(emailRadioGroup);



        //Execute command get back result via reader
        int totalCount = comm.ExecuteNonQuery();

        DbCommand comm2 = conn.CreateCommand();
        comm2.CommandText = "Select @@Identity";
        comm2.Connection = conn;

        String id = comm2.ExecuteScalar().ToString();
        conn.Close();

        lblMessage.Text = "ID of User Added = " +id;

Upvotes: 0

Related Questions