none
Datagridview Row Deletion/Row Update issues (VB.NET / SQL Server 3.5 Compact Edition)

    Question

  • Ok so i have a Form, and I've a Database.

    Using the Dataset Wizard, i created a dataset.

    I dragged the Dataset on the Form, thus creating the DataGridView.

    On the form also i have a few TextBoxes and a Button that takes the information from the TextBoxes and adds a new row to the Dataset and then updates the underlying Database with this new row. This works all and well.

    My problem comes in when i'm either deleting a Row from the DataGridView, or updating a Cell of a Row in the DataGridView.

    When i click a Row on the DataGridView and hit the delete key on my keyboard, i get the following exception:

    OverflowException was unhandled

    Value was either too large or too small for an Int32.


    I get the same exception when i update a Cell on the DataGridView.

    When adding a Row to the Database i do the following to save the changes back to the Database:

    Me.Validate()
    Me.BindingSource.EndEdit()
    Me.TableAdapter.UpdateAll(DataSet)

    And it works.

    I use the same 3 Lines of Code when Deleting a Row from the DataGridView and Updating a Cell. But i get the exception when i do? And it confuses the heck out of me.

    What am i doing wrong?

    Thanks in Advance
    Friday, December 11, 2009 1:38 PM

Answers

  • Mailed. Thanks for the help sofar.

    Accept my apologize abuot of my delay.
    I don't know why, but Visual Studio table adapter generator, generate two parameters for "ID Number" unique column in both Update and Delete commands.
    You can see this in DebutConsulting_EmployeeDataSet.Designer.vb like as:
    Me._adapter.DeleteCommand.Parameters.Add(New Global.System.Data.SqlServerCe.SqlCeParameter("@p2", Global.System.Data.SqlDbType.Int, 0, Global.System.Data.ParameterDirection.Input, true, 0, 0, "ID Number", Global.System.Data.DataRowVersion.Original, Nothing))
    Me._adapter.DeleteCommand.Parameters.Add(New Global.System.Data.SqlServerCe.SqlCeParameter("@p3", Global.System.Data.SqlDbType.NVarChar, 0, Global.System.Data.ParameterDirection.Input, true, 0, 0, "ID Number", Global.System.Data.DataRowVersion.Original, Nothing))
    '------------------------
    Me._adapter.UpdateCommand.Parameters.Add(New Global.System.Data.SqlServerCe.SqlCeParameter("@p9", Global.System.Data.SqlDbType.Int, 0, Global.System.Data.ParameterDirection.Input, true, 0, 0, "ID Number", Global.System.Data.DataRowVersion.Original, Nothing))
    Me._adapter.UpdateCommand.Parameters.Add(New Global.System.Data.SqlServerCe.SqlCeParameter("@p10", Global.System.Data.SqlDbType.NVarChar, 0, Global.System.Data.ParameterDirection.Input, true, 0, 0, "ID Number", Global.System.Data.DataRowVersion.Original, Nothing))

    One parameter from NVarchar type and another from Int type.

    So, you must MANUALLY change the Int parameter type to NVarChar to workarounding this problem like following:
    Me._adapter.DeleteCommand.Parameters.Add(New Global.System.Data.SqlServerCe.SqlCeParameter("@p2", Global.System.Data.SqlDbType.NVarChar, 0, Global.System.Data.ParameterDirection.Input, true, 0, 0, "ID Number", Global.System.Data.DataRowVersion.Original, Nothing))
    Me._adapter.DeleteCommand.Parameters.Add(New Global.System.Data.SqlServerCe.SqlCeParameter("@p3", Global.System.Data.SqlDbType.NVarChar, 0, Global.System.Data.ParameterDirection.Input, true, 0, 0, "ID Number", Global.System.Data.DataRowVersion.Original, Nothing))
    '------------------------ Me._adapter.UpdateCommand.Parameters.Add(New Global.System.Data.SqlServerCe.SqlCeParameter("@p9", Global.System.Data.SqlDbType.NVarChar, 0, Global.System.Data.ParameterDirection.Input, true, 0, 0, "ID Number", Global.System.Data.DataRowVersion.Original, Nothing))
    Me._adapter.UpdateCommand.Parameters.Add(New Global.System.Data.SqlServerCe.SqlCeParameter("@p10", Global.System.Data.SqlDbType.NVarChar, 0, Global.System.Data.ParameterDirection.Input, true, 0, 0, "ID Number", Global.System.Data.DataRowVersion.Original, Nothing))

    With best regards, Yasser Zamani
    • Marked as answer by GaaTY Monday, December 14, 2009 7:44 AM
    Monday, December 14, 2009 7:30 AM

All replies

  • Two are possible:
    1. You had been entered some data in data grid which are in conflict to the related column in database e.g. you enter 4000000000 for an Int32 column.
    2. If this exception does not occur in .UpdateAll(DataSet); put code and exception's stack-trace Please.
    With best regards, Yasser Zamani
    Friday, December 11, 2009 1:56 PM
  • 1. Example Column Name, Value: Test, DataType: nvarchar(System.String), New Value Entered: Testing. Then the exception occurs. And when deleting a row.

    2. Thats exactly where it happens. So i guess i dont need to paste the stack-trace?
    Friday, December 11, 2009 2:16 PM
  • 1. Example Column Name, Value: Test, DataType: nvarchar(System.String), New Value Entered: Testing. Then the exception occurs. And when deleting a row.

    2. Thats exactly where it happens. So i guess i dont need to paste the stack-trace?

    OK,
    Are you sure, your DataSet synchronized with your Database? if no, delete yourDataSet.xsd file from solution explorer and recreate your DataSet from your changed Database again then clean and rebuild your solution and see if any error occured.
    With best regards, Yasser Zamani
    Friday, December 11, 2009 2:22 PM
  • It still happens. I even created the whole project again with a new Database.
    Friday, December 11, 2009 2:40 PM
  • It still happens. I even created the whole project again with a new Database.

    It's very stange. can you mail me your project? yasser.zamani@live.com.NO_SPAM (remove .NO_SPAM)
    With best regards, Yasser Zamani
    Friday, December 11, 2009 3:02 PM
  • Mailed. Thanks for the help sofar.
    Friday, December 11, 2009 6:00 PM
  • Mailed. Thanks for the help sofar.

    Accept my apologize abuot of my delay.
    I don't know why, but Visual Studio table adapter generator, generate two parameters for "ID Number" unique column in both Update and Delete commands.
    You can see this in DebutConsulting_EmployeeDataSet.Designer.vb like as:
    Me._adapter.DeleteCommand.Parameters.Add(New Global.System.Data.SqlServerCe.SqlCeParameter("@p2", Global.System.Data.SqlDbType.Int, 0, Global.System.Data.ParameterDirection.Input, true, 0, 0, "ID Number", Global.System.Data.DataRowVersion.Original, Nothing))
    Me._adapter.DeleteCommand.Parameters.Add(New Global.System.Data.SqlServerCe.SqlCeParameter("@p3", Global.System.Data.SqlDbType.NVarChar, 0, Global.System.Data.ParameterDirection.Input, true, 0, 0, "ID Number", Global.System.Data.DataRowVersion.Original, Nothing))
    '------------------------
    Me._adapter.UpdateCommand.Parameters.Add(New Global.System.Data.SqlServerCe.SqlCeParameter("@p9", Global.System.Data.SqlDbType.Int, 0, Global.System.Data.ParameterDirection.Input, true, 0, 0, "ID Number", Global.System.Data.DataRowVersion.Original, Nothing))
    Me._adapter.UpdateCommand.Parameters.Add(New Global.System.Data.SqlServerCe.SqlCeParameter("@p10", Global.System.Data.SqlDbType.NVarChar, 0, Global.System.Data.ParameterDirection.Input, true, 0, 0, "ID Number", Global.System.Data.DataRowVersion.Original, Nothing))

    One parameter from NVarchar type and another from Int type.

    So, you must MANUALLY change the Int parameter type to NVarChar to workarounding this problem like following:
    Me._adapter.DeleteCommand.Parameters.Add(New Global.System.Data.SqlServerCe.SqlCeParameter("@p2", Global.System.Data.SqlDbType.NVarChar, 0, Global.System.Data.ParameterDirection.Input, true, 0, 0, "ID Number", Global.System.Data.DataRowVersion.Original, Nothing))
    Me._adapter.DeleteCommand.Parameters.Add(New Global.System.Data.SqlServerCe.SqlCeParameter("@p3", Global.System.Data.SqlDbType.NVarChar, 0, Global.System.Data.ParameterDirection.Input, true, 0, 0, "ID Number", Global.System.Data.DataRowVersion.Original, Nothing))
    '------------------------ Me._adapter.UpdateCommand.Parameters.Add(New Global.System.Data.SqlServerCe.SqlCeParameter("@p9", Global.System.Data.SqlDbType.NVarChar, 0, Global.System.Data.ParameterDirection.Input, true, 0, 0, "ID Number", Global.System.Data.DataRowVersion.Original, Nothing))
    Me._adapter.UpdateCommand.Parameters.Add(New Global.System.Data.SqlServerCe.SqlCeParameter("@p10", Global.System.Data.SqlDbType.NVarChar, 0, Global.System.Data.ParameterDirection.Input, true, 0, 0, "ID Number", Global.System.Data.DataRowVersion.Original, Nothing))

    With best regards, Yasser Zamani
    • Marked as answer by GaaTY Monday, December 14, 2009 7:44 AM
    Monday, December 14, 2009 7:30 AM
  • Yasser

    Thank you SOOO much. It works. Both for deletion and updating!

    And i think i know why i generates 2 parameters...

    You'll see i didnt explicitly create my update and delete commands, and the ID Number column excepts NULL values. So in the Update/Delete Commands, the expression evaluates the ID Number = <aVvalue> OR ID Number = NULL.

    Thanks alot for your help. Its much appreciated.
    Monday, December 14, 2009 7:49 AM
  • Yasser

    Thank you SOOO much. It works. Both for deletion and updating!

    And i think i know why i generates 2 parameters...

    You'll see i didnt explicitly create my update and delete commands, and the ID Number column excepts NULL values. So in the Update/Delete Commands, the expression evaluates the ID Number = <aVvalue> OR ID Number = NULL.

    Thanks alot for your help. Its much appreciated.

    Thanks, i enjoy solving this, Gaaty.
    With best regards, Yasser Zamani
    Tuesday, December 15, 2009 10:45 AM