从ASP/C＃Web表单插入数据到Access数据库

问题描述：

我正在ASP/C＃中编写一个页面，我必须采取我创建的表单并将其插入MS Access数据库。在MS Access数据库是建立具有以下字段：从ASP/C＃Web表单插入数据到Access数据库

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

，我发现了以下错误：

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

我无法弄清楚哪个参数缺少值。这里是堆栈跟踪：

[ 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

这里是代码隐藏页：

   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; 
        } 
       }

任何帮助表示赞赏。谢谢！

检查了这一点http://*.com/questions/5893837/using-parameters-inserting-data-into-access-database – Arshad 2013-05-13 13:06:00

是'ID'确实是一个自动增量字段或您需要设置值“手动“？ – Corak 2013-05-13 13:12:11

也许一个或多个列的命名有所不同？ – Steve 2013-05-13 13:13:28

答

试试这个

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;

答

要确保你有正确的身份，最好是调用IDENT_CURRENT（ '表名'）

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

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

请参阅Here一个解释。

从ASP/C＃Web表单插入数据到Access数据库

相关推荐