Remove From My Forums

Can't insert in Access database : OleDbException (0x80004005): ... variant...

Question
0

Sign in to vote

User550036194 posted

This is the message I get when trying to insert a record in an access database with ASPNET 1.0 visual web developper

OleDbException (0x80004005): You tried to assign the Null value to a variable that is not a Variant data type.

The INSERT command was generated automatically... The problem comes from the autonumbered key field..

InsertCommand="INSERT INTO Eveux2006_II(Identificateur, Prenom, Nom, email, Laboratoire, Ville, Interet, Sujet, Titre, Utilisateur, Ordinateur, Navigateur, Creation, Modification) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)"

if I remove the Identificateur (identifier key field) from the list I can insert a record but the fields are not put in their right place (I works properly in the Query Builder dialog but not from the web page)...

Is there any hint to this problem ?

Thank you in advance

Dominique Massiot
massiot@cnrs-orleans.fr

Monday, March 20, 2006 4:42 AM

All replies

0

Sign in to vote

User143635943 posted
post your entier web page code here.
And also check whether Allow Zero Length property is Set to YES for all text fields.

Regards

Kuldeep Deokule

Tuesday, March 21, 2006 1:59 PM
0

Sign in to vote

User550036194 posted

I could send you the whole file but the only not working feature is the insert :

This writing fails with an Access database while it works for a mySQL database
InsertCommand="INSERT INTO Eveux2006_II(Identificateur, Prenom, Nom, email, Laboratoire, Ville, Interet, Sujet, Titre, Utilisateur, Ordinateur, Navigateur, Creation, Modification) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)"

This writing succeeds (removing the "Identificateur field" wich is the primary key of the database. You cannot allow null for this field...
InsertCommand="INSERT INTO Eveux2006_II(Prenom, Nom, email, Laboratoire, Ville, Interet, Sujet, Titre, Utilisateur, Ordinateur, Navigateur, Creation, Modification) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)"

If one refers to the SQL database, it is necessary to define the increment method for syntax one to work, how is that done in the Access one ? This may be the clue to the problem.

Thank you in advance

Dominique

Wednesday, March 22, 2006 3:21 AM
0

Sign in to vote

User143635943 posted
Hello,
Are you using any bound control to deal with database OR Just couple of textboxes and submit button.

Please tell me and if possible please paste the entier code. I know the problem with not wroking feature i.e. "insertcommand", but posting entier code will make picture clear.

Regards

Kuldeep Deokule

Wednesday, March 22, 2006 2:26 PM

User550036194 posted

Following is the full page code and error description.

I just use a Formview to add the new record, connected to the access database.

As is it generates an error upon Insert

If I change the insert order to

InsertCommand="INSERT INTO `Register` (`FristName`, `LastName`, `Laboratory`, `email`, `Password`, `Count`, `CountDatabase`, `WebPage`, `UserStatus`, `LastLogin`, `Created`, `Used`, `UsedDatabase`, `Nom_de_l'ordinateur_distant`, `Nom_d'utilisateur`, `Type_de_navigateur`, `Date_de_dernière_modification`) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)"

it inserts a new record but with an error in filling the fields..

Thank you for your help...

Dominique
------------------------
Error message (in french, sorry)

Vous avez essayé d'affecter la valeur Null à une variable qui n'est pas du type de données Variant.

Description : Une exception non gérée s'est produite au moment de l'exécution de la demande Web actuelle. Contrôlez la trace de la pile pour plus d'informations sur l'erreur et son origine dans le code.

Détails de l'exception: System.Data.OleDb.OleDbException: Vous avez essayé d'affecter la valeur Null à une variable qui n'est pas du type de données Variant.

Erreur source:

Une exception non gérée s'est produite lors de l'exécution de la demande Web actuelle. Les informations relatives à l'origine et l'emplacement de l'exception peuvent être identifiées en utilisant la trace
 de la pile d'exception ci-dessous.

Trace de la pile:


[OleDbException (0x80004005): Vous avez essayé d'affecter la valeur Null à une variable qui n'est pas du type de données Variant.]
   System.Data.OleDb.OleDbCommand.ExecuteCommandTextForSingleResult(tagDBPARAMS dbParams, Object& executeResult) +267
   System.Data.OleDb.OleDbCommand.ExecuteCommandText(Object& executeResult) +192
   System.Data.OleDb.OleDbCommand.ExecuteCommand(CommandBehavior behavior, Object& executeResult) +48
   System.Data.OleDb.OleDbCommand.ExecuteReaderInternal(CommandBehavior behavior, String method) +106
   System.Data.OleDb.OleDbCommand.ExecuteNonQuery() +108
   System.Web.UI.WebControls.SqlDataSourceView.ExecuteDbCommand(DbCommand command, DataSourceOperation operation) +401
   System.Web.UI.WebControls.SqlDataSourceView.ExecuteInsert(IDictionary values) +447
   System.Web.UI.DataSourceView.Insert(IDictionary values, DataSourceViewOperationCallback callback) +72
   System.Web.UI.WebControls.FormView.HandleInsert(String commandArg, Boolean causesValidation) +388
   System.Web.UI.WebControls.FormView.HandleEvent(EventArgs e, Boolean causesValidation, String validationGroup) +623
   System.Web.UI.WebControls.FormView.OnBubbleEvent(Object source, EventArgs e) +95
   System.Web.UI.Control.RaiseBubbleEvent(Object source, EventArgs args) +35
   System.Web.UI.WebControls.FormViewRow.OnBubbleEvent(Object source, EventArgs e) +109
   System.Web.UI.Control.RaiseBubbleEvent(Object source, EventArgs args) +35
   System.Web.UI.WebControls.LinkButton.OnCommand(CommandEventArgs e) +115
   System.Web.UI.WebControls.LinkButton.RaisePostBackEvent(String eventArgument) +163
   System.Web.UI.WebControls.LinkButton.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument) +7
   System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument) +11
   System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData) +174
   System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +5102

-----------------------------------
Full page code

<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Default.aspx.cs" Inherits="test_Default" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<title>Untitled Page</title>

</head>

<body>

<div>

<asp:FormView ID="FormView1" runat="server" DataKeyNames="Identificateur" DataSourceID="AccessDataSource1">

Identificateur:

<asp:Label ID="IdentificateurLabel1" runat="server" Text='<%# Eval("Identificateur") %>'>

</asp:Label>

FristName:

<asp:TextBox ID="FristNameTextBox" runat="server" Text='<%# Bind("FristName") %>'>

</asp:TextBox>

LastName:

<asp:TextBox ID="LastNameTextBox" runat="server" Text='<%# Bind("LastName") %>'>

</asp:TextBox>

Laboratory:

<asp:TextBox ID="LaboratoryTextBox" runat="server" Text='<%# Bind("Laboratory") %>'>

</asp:TextBox>

email:

<asp:TextBox ID="emailTextBox" runat="server" Text='<%# Bind("email") %>'>

</asp:TextBox>

Password:

<asp:TextBox ID="PasswordTextBox" runat="server" Text='<%# Bind("Password") %>'>

</asp:TextBox>

Count:

<asp:TextBox ID="CountTextBox" runat="server" Text='<%# Bind("Count") %>'>

</asp:TextBox>

CountDatabase:

<asp:TextBox ID="CountDatabaseTextBox" runat="server" Text='<%# Bind("CountDatabase") %>'>

</asp:TextBox>

WebPage:

<asp:TextBox ID="WebPageTextBox" runat="server" Text='<%# Bind("WebPage") %>'>

</asp:TextBox>

UserStatus:

<asp:TextBox ID="UserStatusTextBox" runat="server" Text='<%# Bind("UserStatus") %>'>

</asp:TextBox>

LastLogin:

<asp:TextBox ID="LastLoginTextBox" runat="server" Text='<%# Bind("LastLogin") %>'>

</asp:TextBox>

Created:

<asp:TextBox ID="CreatedTextBox" runat="server" Text='<%# Bind("Created") %>'>

</asp:TextBox>

Used:

<asp:CheckBox ID="UsedCheckBox" runat="server" Checked='<%# Bind("Used") %>' />

UsedDatabase:

<asp:CheckBox ID="UsedDatabaseCheckBox" runat="server" Checked='<%# Bind("UsedDatabase") %>' />

column1:

<asp:TextBox ID="column1TextBox" runat="server" Text='<%# Bind("column1") %>'>

</asp:TextBox>

column2:

<asp:TextBox ID="column2TextBox" runat="server" Text='<%# Bind("column2") %>'>

</asp:TextBox>

Type_de_navigateur:

<asp:TextBox ID="Type_de_navigateurTextBox" runat="server" Text='<%# Bind("Type_de_navigateur") %>'>

</asp:TextBox>

Date_de_dernière_modification:

<asp:TextBox ID="Date_de_dernière_modificationTextBox" runat="server" Text='<%# Bind("Date_de_dernière_modification") %>'>

</asp:TextBox>

<asp:LinkButton ID="UpdateButton" runat="server" CausesValidation="True" CommandName="Update"

Text="Update">

</asp:LinkButton>

<asp:LinkButton ID="UpdateCancelButton" runat="server" CausesValidation="False" CommandName="Cancel"

Text="Cancel">

</asp:LinkButton>

</EditItemTemplate>

FristName:

<asp:TextBox ID="FristNameTextBox" runat="server" Text='<%# Bind("FristName") %>'>

</asp:TextBox>

LastName:

<asp:TextBox ID="LastNameTextBox" runat="server" Text='<%# Bind("LastName") %>'>

</asp:TextBox>

Laboratory:

<asp:TextBox ID="LaboratoryTextBox" runat="server" Text='<%# Bind("Laboratory") %>'>

</asp:TextBox>

email:

<asp:TextBox ID="emailTextBox" runat="server" Text='<%# Bind("email") %>'>

</asp:TextBox>

Password:

<asp:TextBox ID="PasswordTextBox" runat="server" Text='<%# Bind("Password") %>'>

</asp:TextBox>

Count:

<asp:TextBox ID="CountTextBox" runat="server" Text='<%# Bind("Count") %>'>

</asp:TextBox>

CountDatabase:

<asp:TextBox ID="CountDatabaseTextBox" runat="server" Text='<%# Bind("CountDatabase") %>'>

</asp:TextBox>

WebPage:

<asp:TextBox ID="WebPageTextBox" runat="server" Text='<%# Bind("WebPage") %>'>

</asp:TextBox>

UserStatus:

<asp:TextBox ID="UserStatusTextBox" runat="server" Text='<%# Bind("UserStatus") %>'>

</asp:TextBox>

LastLogin:

<asp:TextBox ID="LastLoginTextBox" runat="server" Text='<%# Bind("LastLogin") %>'>

</asp:TextBox>

Created:

<asp:TextBox ID="CreatedTextBox" runat="server" Text='<%# Bind("Created") %>'>

</asp:TextBox>

Used:

<asp:CheckBox ID="UsedCheckBox" runat="server" Checked='<%# Bind("Used") %>' />

UsedDatabase:

<asp:CheckBox ID="UsedDatabaseCheckBox" runat="server" Checked='<%# Bind("UsedDatabase") %>' />

column1:

<asp:TextBox ID="column1TextBox" runat="server" Text='<%# Bind("column1") %>'>

</asp:TextBox>

column2:

<asp:TextBox ID="column2TextBox" runat="server" Text='<%# Bind("column2") %>'>

</asp:TextBox>

Type_de_navigateur:

<asp:TextBox ID="Type_de_navigateurTextBox" runat="server" Text='<%# Bind("Type_de_navigateur") %>'>

</asp:TextBox>

Date_de_dernière_modification:

<asp:TextBox ID="Date_de_dernière_modificationTextBox" runat="server" Text='<%# Bind("Date_de_dernière_modification") %>'>

</asp:TextBox>

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

Identificateur:

<asp:Label ID="IdentificateurLabel" runat="server" Text='<%# Eval("Identificateur") %>'>

</asp:Label>

FristName:

<asp:Label ID="FristNameLabel" runat="server" Text='<%# Bind("FristName") %>'></asp:Label>

LastName:

<asp:Label ID="LastNameLabel" runat="server" Text='<%# Bind("LastName") %>'></asp:Label>

Laboratory:

<asp:Label ID="LaboratoryLabel" runat="server" Text='<%# Bind("Laboratory") %>'>

</asp:Label>

email:

<asp:Label ID="emailLabel" runat="server" Text='<%# Bind("email") %>'></asp:Label>

Password:

<asp:Label ID="PasswordLabel" runat="server" Text='<%# Bind("Password") %>'></asp:Label>

Count:

<asp:Label ID="CountLabel" runat="server" Text='<%# Bind("Count") %>'></asp:Label>

CountDatabase:

<asp:Label ID="CountDatabaseLabel" runat="server" Text='<%# Bind("CountDatabase") %>'>

</asp:Label>

WebPage:

<asp:Label ID="WebPageLabel" runat="server" Text='<%# Bind("WebPage") %>'></asp:Label>

UserStatus:

<asp:Label ID="UserStatusLabel" runat="server" Text='<%# Bind("UserStatus") %>'>

</asp:Label>

LastLogin:

<asp:Label ID="LastLoginLabel" runat="server" Text='<%# Bind("LastLogin") %>'></asp:Label>

Created:

<asp:Label ID="CreatedLabel" runat="server" Text='<%# Bind("Created") %>'></asp:Label>

Used:

<asp:CheckBox ID="UsedCheckBox" runat="server" Checked='<%# Bind("Used") %>' Enabled="false" />

UsedDatabase:

<asp:CheckBox ID="UsedDatabaseCheckBox" runat="server" Checked='<%# Bind("UsedDatabase") %>'

Enabled="false" />

column1:

<asp:Label ID="column1Label" runat="server" Text='<%# Bind("column1") %>'></asp:Label>

column2:

<asp:Label ID="column2Label" runat="server" Text='<%# Bind("column2") %>'></asp:Label>

Type_de_navigateur:

<asp:Label ID="Type_de_navigateurLabel" runat="server" Text='<%# Bind("Type_de_navigateur") %>'>

</asp:Label>

Date_de_dernière_modification:

<asp:Label ID="Date_de_dernière_modificationLabel" runat="server" Text='<%# Bind("Date_de_dernière_modification") %>'>

</asp:Label>

<asp:LinkButton ID="EditButton" runat="server" CausesValidation="False" CommandName="Edit"

Text="Edit">

</asp:LinkButton>

<asp:LinkButton ID="DeleteButton" runat="server" CausesValidation="False" CommandName="Delete"

Text="Delete">

</asp:LinkButton>

<asp:LinkButton ID="NewButton" runat="server" CausesValidation="False" CommandName="New"

Text="New">

</asp:LinkButton>

</ItemTemplate>

</asp:FormView>

<asp:AccessDataSource ID="AccessDataSource1" runat="server" DataFile="D:\Web\DataBase\dmfit.mdb"

DeleteCommand="DELETE FROM `Register` WHERE `Identificateur` = ?" InsertCommand="INSERT INTO `Register` (`Identificateur`, `FristName`, `LastName`, `Laboratory`, `email`, `Password`, `Count`, `CountDatabase`, `WebPage`, `UserStatus`, `LastLogin`, `Created`, `Used`, `UsedDatabase`, `Nom_de_l'ordinateur_distant`, `Nom_d'utilisateur`, `Type_de_navigateur`, `Date_de_dernière_modification`) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)"

SelectCommand="SELECT `Identificateur`, `FristName`, `LastName`, `Laboratory`, `email`, `Password`, `Count`, `CountDatabase`, `WebPage`, `UserStatus`, `LastLogin`, `Created`, `Used`, `UsedDatabase`, `Nom_de_l'ordinateur_distant` AS column1, `Nom_d'utilisateur` AS column2, `Type_de_navigateur`, `Date_de_dernière_modification` FROM `Register`"

UpdateCommand="UPDATE `Register` SET `FristName` = ?, `LastName` = ?, `Laboratory` = ?, `email` = ?, `Password` = ?, `Count` = ?, `CountDatabase` = ?, `WebPage` = ?, `UserStatus` = ?, `LastLogin` = ?, `Created` = ?, `Used` = ?, `UsedDatabase` = ?, `Nom_de_l'ordinateur_distant` = ?, `Nom_d'utilisateur` = ?, `Type_de_navigateur` = ?, `Date_de_dernière_modification` = ? WHERE `Identificateur` = ?">

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

<asp:Parameter Name="FristName" Type="String" />

<asp:Parameter Name="LastName" Type="String" />

<asp:Parameter Name="Laboratory" Type="String" />

<asp:Parameter Name="email" Type="String" />

<asp:Parameter Name="Password" Type="String" />

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

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

<asp:Parameter Name="WebPage" Type="String" />

<asp:Parameter Name="UserStatus" Type="String" />

<asp:Parameter Name="LastLogin" Type="DateTime" />

<asp:Parameter Name="Created" Type="DateTime" />

<asp:Parameter Name="Used" Type="Boolean" />

<asp:Parameter Name="UsedDatabase" Type="Boolean" />

<asp:Parameter Name="column1" Type="String" />

<asp:Parameter Name="column2" Type="String" />

<asp:Parameter Name="Type_de_navigateur" Type="String" />

<asp:Parameter Name="Date_de_dernière_modification" Type="DateTime" />

</InsertParameters>

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

</DeleteParameters>

<asp:Parameter Name="FristName" Type="String" />

<asp:Parameter Name="LastName" Type="String" />

<asp:Parameter Name="Laboratory" Type="String" />

<asp:Parameter Name="email" Type="String" />

<asp:Parameter Name="Password" Type="String" />

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

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

<asp:Parameter Name="WebPage" Type="String" />

<asp:Parameter Name="UserStatus" Type="String" />

<asp:Parameter Name="LastLogin" Type="DateTime" />

<asp:Parameter Name="Created" Type="DateTime" />

<asp:Parameter Name="Used" Type="Boolean" />

<asp:Parameter Name="UsedDatabase" Type="Boolean" />

<asp:Parameter Name="column1" Type="String" />

<asp:Parameter Name="column2" Type="String" />

<asp:Parameter Name="Type_de_navigateur" Type="String" />

<asp:Parameter Name="Date_de_dernière_modification" Type="DateTime" />

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

</UpdateParameters>

</asp:AccessDataSource>

</div>

</form>

</body>

</html>

Thursday, March 23, 2006 8:37 AM

0

Sign in to vote

User143635943 posted
Hello,

First step:

remove "Identificatur" (i.e.autoincrement) field from INSERT query. That you'd done already.

Second step:

<InsertParameters>
<asp:Parameter Name="Identificateur" Type="Int32" />

Just remove "Identificatur" asp:parameter from InsertParameters of AccessDataSource control.

Regards

Kuldeep Deokule.

Thursday, March 23, 2006 11:31 PM
0

Sign in to vote

User550036194 posted

Great !! I missed this line...

But do you understand why the <asp:Parameter Name="Identificateur" Type="Int32" /> remains in the coding while the INSERT command does not require it (because the Insert command has been generated by the AccessDataSourse Control and the Formview control updated...)

In any case thanks a lot

Dominique

PS : I stayed in Pune for a week last year that's a surprise to see you're overthere (:

Friday, March 24, 2006 3:30 AM
0

Sign in to vote

User263336835 posted

Just wanted to say thank you! Used the info for a class project! worked like a charm!

Thursday, October 21, 2010 6:37 PM

Asked by:

Can't insert in Access database : OleDbException (0x80004005): ... variant...

Question

All replies

Vous avez essayé d'affecter la valeur Null à une variable qui n'est pas du type de données Variant.