kmoloney
asked on
Cannot Insert Record with Formview Control with autonumber field
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:LinkBu tton>
<asp:LinkButton ID="InsertCancelButton" runat="server" CausesValidation="False" CommandName="Cancel"
Text="Cancel"></asp:LinkBu tton>
</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.OleDbExc eption: 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.OleDbExc eption: 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...
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"
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:LinkBu
<asp:LinkButton ID="InsertCancelButton" runat="server" CausesValidation="False" CommandName="Cancel"
Text="Cancel"></asp:LinkBu
</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.OleDbExc
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.OleDbExc
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...
ASKER
...errr, to the field.
ASKER
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></Insert Parameters > tags, it worked ok, at least locally; we'll see how it works after I post it to the web.
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></Insert
ASKER
Everything seems to be okay. Answered own question.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER