Solved

Cannot Insert Record with Formview Control with autonumber field

Posted on 2006-11-18
5
634 Views
Last Modified: 2011-04-27
I am using VS2005 (VB) and am developing an ASP.NET page that will allow folks to enter in requests.  The DB behind the scenes is Microsoft Access.  Each request has a primary key (isrID) which is an autonumber field.  I'm using the FormView control to insert the data.

When creating and specifying the data source for the formview control, the only way that it will allow INSERT, DELETE, and UPDATE statements is if I choose the field with the primary key (isrID).  The formview control's default mode is set to "Insert."  The formview control sets the DataKeyNames property of the formview control to isrID, and does not display anything for the isrID field when rendered (in InsertMode).  The resulting <InsertItemTemplate> is as follows:

        <InsertItemTemplate>
            isrLast:
            <asp:TextBox ID="isrLastTextBox" runat="server" Text='<%# Bind("isrLast") %>'></asp:TextBox><br />
            isrFirst:
            <asp:TextBox ID="isrFirstTextBox" runat="server" Text='<%# Bind("isrFirst") %>'></asp:TextBox><br />
            isrEmail:
            <asp:TextBox ID="isrEmailTextBox" runat="server" Text='<%# Bind("isrEmail") %>'></asp:TextBox><br />
            isrDate:
            <asp:TextBox ID="isrDateTextBox" runat="server" Text='<%# Bind("isrDate") %>'></asp:TextBox><br />
            isrFacility:
            <asp:TextBox ID="isrFacilityTextBox" runat="server" Text='<%# Bind("isrFacility") %>'></asp:TextBox><br />
            isrDept:
            <asp:TextBox ID="isrDeptTextBox" runat="server" Text='<%# Bind("isrDept") %>'></asp:TextBox><br />
            isrHeaderDesc:
            <asp:TextBox ID="isrHeaderDescTextBox" runat="server" Text='<%# Bind("isrHeaderDesc") %>'></asp:TextBox><br />
            isrProjectType:
            <asp:TextBox ID="isrProjectTypeTextBox" runat="server" Text='<%# Bind("isrProjectType") %>'></asp:TextBox><br />
            isrUrgency:
            <asp:TextBox ID="isrUrgencyTextBox" runat="server" Text='<%# Bind("isrUrgency") %>'></asp:TextBox><br />
            isrDesc:
            <asp:TextBox ID="isrDescTextBox" runat="server" Text='<%# Bind("isrDesc") %>'></asp:TextBox><br />
            <asp:LinkButton ID="InsertButton" runat="server" CausesValidation="True" CommandName="Insert"
                Text="Insert"></asp:LinkButton>
            <asp:LinkButton ID="InsertCancelButton" runat="server" CausesValidation="False" CommandName="Cancel"
                Text="Cancel"></asp:LinkButton>
        </InsertItemTemplate>

...and the InsertCommand is:

InsertCommand="INSERT INTO [Request] ([isrID], [isrLast], [isrFirst], [isrEmail], [isrDate], [isrFacility], [isrDept], [isrHeaderDesc], [isrProjectType], [isrUrgency], [isrDesc]) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)"

When I insert data, I get the following error message:

    You tried to assign the Null value to a variable that is not a Variant data type.

    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: You tried to assign the Null value to a variable that is not a Variant data type.

Clearly, it's not liking me trying to assign what must be a null for the isrID field into the autonumbered field in the database.  If I take out the [isrID] field and the first question mark from the INSERT INTO statement, I get the following error:

    Data type mismatch in criteria expression.
    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: Data type mismatch in criteria expression.

So, how do I insert a record into a table with an autonumber?  Or should I switch to a GUID (that would be nasty, because we'll be wanting to use the ID as a true identifier, not just something behind the scenes).

Worth a lot of points to me...
0
Comment
Question by:kmoloney
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
5 Comments
 
LVL 2

Author Comment

by:kmoloney
ID: 17972948
P.S. - even if I switched to a GUID, wouldn't know how to use the Guid.NewGuid() function to assign the value to the row.
0
 
LVL 2

Author Comment

by:kmoloney
ID: 17972952
...errr, to the field.
0
 
LVL 2

Author Comment

by:kmoloney
ID: 17972997
Think I solved my own question...

I removed the [isrID] and "?" from the insert statement, but I didn't remove [isrID] from the InsertParameters block.  After taking out:

<asp:Parameter Name="isrID" Type="Int32" />

between the <InsertParameters></InsertParameters> tags, it worked ok, at least locally; we'll see how it works after I post it to the web.

0
 
LVL 2

Author Comment

by:kmoloney
ID: 18119600
Everything seems to be okay.  Answered own question.
0
 

Accepted Solution

by:
PashaMod earned 0 total points
ID: 18135119
Closed, 0 points refunded.
PashaMod
Community Support Moderator
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Just a quick little trick I learned recently.  Now that I'm using jQuery with abandon in my asp.net applications, I have grown tired of the following syntax:      (CODE) I suppose it just offends my sense of decency to put inline VBScript on a…
Today is the age of broadband.  More and more people are going this route determined to experience the web and it’s multitude of services as quickly and painlessly as possible. Coupled with the move to broadband, people are experiencing the web via …
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…
Are you ready to implement Active Directory best practices without reading 300+ pages? You're in luck. In this webinar hosted by Skyport Systems, you gain insight into Microsoft's latest comprehensive guide, with tips on the best and easiest way…

738 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question