Connecting to a custom database in ASP.Net

Question

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>

toddles2000 · Accepted Answer · 2012-03-03 06:03:21Z

2

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.

answered Mar 3, 2012 at 6:03

toddles2000

1,1321 gold badge8 silver badges16 bronze badges

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

2 Comments

Geo Over a year ago

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.

toddles2000 Over a year ago

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.

Bauhaus · Accepted Answer · 2012-03-03 06:02:49Z

1

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.

answered Mar 3, 2012 at 6:02

Bauhaus

5298 silver badges22 bronze badges

2 Comments

Bauhaus Over a year ago

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.

Geo Over a year ago

The ID was messing my INSERT. Thanks for the help

Geo · Accepted Answer · 2012-03-05 00:53:10Z

0

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.

answered Mar 5, 2012 at 0:53

Geo

3,2007 gold badges45 silver badges88 bronze badges

Collectives™ on Stack Overflow

Connecting to a custom database in ASP.Net

3 Answers 3

2 Comments

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related