up vote 1 down vote favorite

Im trying to create a small application where a user submit personal info and the data are saved on an Access DB.

The error that is returning does not make any sense because I am trying to insert the correct amount of values into the table.

The values are first,last,age, and gender.

This is my code followed by the error that i'm getting when i press the submit button.

Thanks for all your help. 

<pre>
<%@ Page Language="C#" %>
<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Data.OleDb" %>

<script runat="server">
public void btnSubmit_Click(object sender, EventArgs e)
{
    // Create and configure connection string.
    OleDbConnection c = new OleDbConnection();
    c.ConnectionString = 
        "Provider=Microsoft.ACE.OLEDB.12.0;" +
        "Data Source=d:/ectserver/gnicolai/Database/application.accdb;";

    // Open connection.
    c.Open();

    // Get data from textboxes.
    string last = txtLastName.Text;
    string first = txtFirstName.Text;
    string gender = txtGender.Text;
    int  age = int.Parse(txtAge.Text);

    // Compose SQL command string.
    string sql = "INSERT INTO Applicant VALUES"  + 
        "('" + last + "', '" + first + 
        "', '" + gender + "',"  + age + ");";

    // Show SQL insert statement.
    litMessage.Text = 
        "Data submitted with SQL query string<br />" + sql;

    // Create command object and execute insert statement.
    OleDbCommand command = new OleDbCommand(sql, c);
    command.ExecuteNonQuery();

    // Close connection.
    c.Close();
}
</script>

<html>

<head>
<title>grocery-display3.aspx Example</title>
<link rel="stylesheet" class="text/css" 
                       href="../examples.css" />
<style type="text/css">
td  { padding-left:0.15cm;
      padding-right:0.15cm;
      padding-top:0.15cm;
      padding-bottom:0.15cm; }
</style>
</head>

<body>

<h2>PersonInfo3 Example</h2>

<p>Submit age, gender, and age for a person; 
     store this information in a database.
     Retrieve this data with the ViewPersons page. </p>

<form id="frmSelect" runat="server">

<table>
<tr> 
    <td class="r">Last Name</td>
    <td><asp:TextBox ID="txtFirstName" runat="server" 
            CssClass="ctrl" /></td>
</tr>
<tr> 
    <td class="r">First Name</td>
    <td><asp:TextBox ID="txtLastName" runat="server" 
            CssClass="ctrl" /></td>
</tr>
<tr> 
    <td class="r">Gender</td>
    <td><asp:TextBox ID="txtGender" runat="server"
            CssClass="ctrl"  /></td>
</tr>
<tr> 
    <td class="r">Age</td>
    <td><asp:TextBox ID="txtAge" runat="server" 
            CssClass="ctrl" /></td>    
</tr>
<tr> 
    <td> </td>
    <td><asp:Button ID="btnSubmit" runat="server"
            Text="Submit" CssClass="ctrl" Width="128px"
            OnClick="btnSubmit_Click" /></td>
</tr>
</table>

<p><asp:RangeValidator ID="RangeValidator1" Type="Integer" runat="server" 
      ControlToValidate="txtAge" Display="Static" MinimumValue="0" 
      MaximumValue="130" ErrorMessage="Age must be between 0 and 130" /></p>

<p><asp:Literal ID="litMessage" runat="server" /></p>
</form>

</body>
</html>         

</pre>


<strong>Error</strong>

<pre>
Server Error in '/' Application.

Number of query values and destination fields are not the same.

Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. 

Exception Details: System.Data.OleDb.OleDbException: Number of query values and destination fields are not the same.

Source Error: 


Line 32:     // Create command object and execute insert statement.
Line 33:     OleDbCommand command = new OleDbCommand(sql, c);
Line 34:     command.ExecuteNonQuery();
Line 35:         
Line 36:     // Close connection.

Source File: d:\DePaul\Winter 2012\IT 330\Projects\Proj5-Nicolaides\Proj5-Nicolaides\Default.aspx    Line: 34 

Stack Trace: 


[OleDbException (0x80004005): Number of query values and destination fields are not the same.]
   System.Data.OleDb.OleDbCommand.ExecuteCommandTextErrorHandling(OleDbHResult hr) +992124
   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.default_aspx.btnSubmit_Click(Object sender, EventArgs e) in d:\DePaul\Winter 2012\IT 330\Projects\Proj5-Nicolaides\Proj5-Nicolaides\Default.aspx:34
   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


</pre>
share|improve this question

3 Answers

up vote 0 down vote accepted

The ID field that I had on the database table was messing with my insert statement. After viewing the table in design view, I removed the "ID" field and my insert statement works as it should.

share|improve this answer
up vote 2 down vote

The most likely issue is that your table definition has more than 4 columns. You need to either give the extra columns a default insert value (if your db supports it) or change your insert code to match.

It's also worth pointing out that this code is subject to sql injection attacks. You should never build a sql statement like that and feed directly to the database. Instead turn it into a sql parameter and then send.

share|improve this answer
i know that is subject to attacks but it is not going to be published this way. think of this as a rough draft. I never did something like this before so I am just modeling the basics. I have an "ID" column on my table. Does this matter? I thought the primary key would generate automatically. – Geo Nicolaidis Mar 3 '12 at 6:18
Fair enough on just getting it to work as a starting point. Make sure your Id field is set to autonumber. You may also need to modify your insert statement to include the field names as others mentioned. – toddles2000 Mar 3 '12 at 20:30
I fixed it. Thanks for the help – Geo Nicolaidis Mar 5 '12 at 0:51
up vote 1 down vote

You may need to specify the columns you are trying to populate in your table explicitly. What are the names of the columns you're trying to insert to?

Try changing your code to this:

string sql = "INSERT INTO Applicant" +
    "(LastName, FirstName, Gender, Age)" +
    "VALUES"  + 
    "('" + last + "', '" + first + 
    "', '" + gender + "',"  + age + ");";

Replace the LastName, etc column names with yours.

share|improve this answer
Thanks but this is not working – Geo Nicolaidis Mar 3 '12 at 6:28
You may have some columns in your access table that require a value to be inserted. Are any of them set to not null? Or is there a primary key that needs a value? You mention an "ID" field above but the primary key will not insert a default value unless you've set it to an 'autonumber' column. – Bauhaus Mar 3 '12 at 6:37
The ID was messing my INSERT. Thanks for the help – Geo Nicolaidis Mar 5 '12 at 0:51

Your Answer

 
or
required, but never shown
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.