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

  • Question

  • 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

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

    <html xmlns="http://www.w3.org/1999/xhtml" >

    <head runat="server">

    <title>Untitled Page</title>

    </head>

    <body>

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

    <div>

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

    <EditItemTemplate>

    Identificateur:

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

    </asp:Label><br />

    FristName:

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

    </asp:TextBox><br />

    LastName:

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

    </asp:TextBox><br />

    Laboratory:

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

    </asp:TextBox><br />

    email:

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

    </asp:TextBox><br />

    Password:

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

    </asp:TextBox><br />

    Count:

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

    </asp:TextBox><br />

    CountDatabase:

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

    </asp:TextBox><br />

    WebPage:

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

    </asp:TextBox><br />

    UserStatus:

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

    </asp:TextBox><br />

    LastLogin:

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

    </asp:TextBox><br />

    Created:

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

    </asp:TextBox><br />

    Used:

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

    UsedDatabase:

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

    column1:

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

    </asp:TextBox><br />

    column2:

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

    </asp:TextBox><br />

    Type_de_navigateur:

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

    </asp:TextBox><br />

    Date_de_dernière_modification:

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

    </asp:TextBox><br />

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

    <InsertItemTemplate>

    FristName:

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

    </asp:TextBox><br />

    LastName:

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

    </asp:TextBox><br />

    Laboratory:

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

    </asp:TextBox><br />

    email:

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

    </asp:TextBox><br />

    Password:

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

    </asp:TextBox><br />

    Count:

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

    </asp:TextBox><br />

    CountDatabase:

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

    </asp:TextBox><br />

    WebPage:

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

    </asp:TextBox><br />

    UserStatus:

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

    </asp:TextBox><br />

    LastLogin:

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

    </asp:TextBox><br />

    Created:

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

    </asp:TextBox><br />

    Used:

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

    UsedDatabase:

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

    column1:

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

    </asp:TextBox><br />

    column2:

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

    </asp:TextBox><br />

    Type_de_navigateur:

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

    </asp:TextBox><br />

    Date_de_dernière_modification:

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

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

    <ItemTemplate>

    Identificateur:

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

    </asp:Label><br />

    FristName:

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

    LastName:

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

    Laboratory:

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

    </asp:Label><br />

    email:

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

    Password:

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

    Count:

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

    CountDatabase:

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

    </asp:Label><br />

    WebPage:

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

    UserStatus:

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

    </asp:Label><br />

    LastLogin:

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

    Created:

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

    Used:

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

    UsedDatabase:

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

    Enabled="false" /><br />

    column1:

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

    column2:

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

    Type_de_navigateur:

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

    </asp:Label><br />

    Date_de_dernière_modification:

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

    </asp:Label><br />

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

    &nbsp;<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` = ?">

    <InsertParameters>

    <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&#232;re_modification" Type="DateTime" />

    </InsertParameters>

    <DeleteParameters>

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

    </DeleteParameters>

    <UpdateParameters>

    <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&#232;re_modification" Type="DateTime" />

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

    </UpdateParameters>

    </asp:AccessDataSource>

    </div>

    </form>

    </body>

    </html>

    Thursday, March 23, 2006 8:37 AM
  • 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
  • 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
  • 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