Member Avatar for serkan sendur
serkan sendur 821 Banned

For small applications it is proper to use AccessDataSource object in order to access the database directly from asp.net page. When i first used it for inserting data with formview, it generated the following error :
"You tried to assign the Null value to a variable that is not a Variant data type"
today i tried the same thing again and it generated the same error again.
I found out that in the generated mark up, although id field is of type autonumber and primary key, visual studio generates a parameter for that field in the markup of the aspx page. I deleted those parameters from the insert command of AccessDataSource and FormViews Insert and Edit Item templates. Then it worked as it should. I think that is a bug of visual studio ide.

  • 1 Contributor
  • 0 Replies
  • 108 Views

Dani AI

Generated

was right to remove the automatically generated parameter for the AutoNumber primary key. The Visual Studio designer can and does scaffold parameters for every field it sees, and when the Access/JET engine receives a parameter for an AutoNumber column with no value it can raise the runtime error about assigning Null to a non-Variant type. The real fix is to keep the identity column out of the INSERT path rather than passing a Null into it.

Practical steps that consistently fix this:

  • Edit the AccessDataSource markup so the InsertCommand explicitly lists only the insertable columns (exclude the AutoNumber column).
  • Remove the corresponding InsertParameter (or any binding) for the ID field.
  • In the FormView, do not include a two-way binding for the identity field in the InsertItemTemplate; either remove the control or make it read-only.
  • Set the FormView DataKeyNames to the primary key so updates and deletes still have the key available without including it in INSERTs. If you need to change parameters at runtime, handle the AccessDataSource.Inserting event and remove or adjust the key parameter there.

If the application needs the new identity value after insert, retrieve it explicitly (for Access you can use the same connection and run SELECT @@IDENTITY immediately after the insert) or capture it via your data-access logic. For production web apps, consider moving from Access to SQL Server/Express for better concurrency and reliability.

See the framework docs for details on the data controls and key handling: AccessDataSource class and FormView.DataKeyNames.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.