Hi Friends,

I am new to .Net. So I don't know much.

I am facing a problem in updating database through ADO.Net

I am creating the dataset and there is no problem in the updation and
deletion or insertion in the dataset but when I am updating the
database through adaptor error occures (Coloured Red).

For ref the code follows:



Code:

Imports System.Data.OleDb
Module Module1
   Private Const s As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data
Source=D:\Rabi\Database\DBTest-1.mdb;Persist Security Info=False"
   Public Con As OleDb.OleDbConnection
   Public adopt As OleDb.OleDbDataAdapter
   Public ds As DataSet
   Public sql As String

   Dim cmdDel As New OleDb.OleDbCommand, sDelSql As String
   Dim cmdIns As New OleDb.OleDbCommand, sInsSql As String
   Dim cmdUpd As New OleDb.OleDbCommand, sUpdSql As String
   Dim Param As New OleDb.OleDbParameter

   Public Sub Display(ByRef Table As DataTable)
       Dim row As DataRow
       Dim col As DataColumn
       Dim i, j As Integer

       For i = 0 To Table.Rows.Count - 1
           row = Table.Rows(i)
           Select Case row.RowState
               Case DataRowState.Deleted
                   Console.WriteLine("[Deleted]")
               Case DataRowState.Modified
                   Console.WriteLine("[Modified]")
               Case DataRowState.Added
                   Console.WriteLine("[Added]")
               Case DataRowState.Unchanged
                   Console.WriteLine("[Unchanged]")
           End Select
           For j = 0 To Table.Columns.Count - 1
               If row.RowState <> DataRowState.Deleted Then
                   Console.WriteLine("{0}", row.Item(j))
               End If
           Next
           Console.WriteLine()
       Next
   End Sub
   Public Sub Main()
       Try
           Con = New OleDb.OleDbConnection(s)
           sql = "Select * from Artist"
           adopt = New OleDbDataAdapter(sql, Con)
           ds = New DataSet
       Catch ex As Exception
           Console.WriteLine(ex.ToString)
           Console.ReadLine()
       End Try

       sDelSql = "Delete From Artist Where Id = ?"
       cmdDel.Connection = Con
       cmdDel.CommandText = sDelSql
       Param = cmdDel.Parameters.Add("Id", OleDb.OleDbType.Integer)
       Param.SourceColumn = "@ID"
       Param.SourceVersion = DataRowVersion.Original
       adopt.DeleteCommand = cmdDel

       sUpdSql = "Update Artist Set Name = ? Where Id = ?"
       cmdUpd.Connection = Con
       cmdUpd.CommandText = sUpdSql
       Param = cmdUpd.Parameters.Add("Name", OleDb.OleDbType.Char)
       Param.SourceColumn = "@Name"
       Param.SourceVersion = DataRowVersion.Current
       Param = cmdUpd.Parameters.Add("Id", OleDb.OleDbType.Integer)
       Param.SourceColumn = "@Id"
       Param.SourceVersion = DataRowVersion.Original
       adopt.UpdateCommand = cmdUpd

       sInsSql = "Insert Into Artist (Id,Name) Values(?,?)"
       cmdIns.Connection = Con
       cmdIns.CommandText = sInsSql
       Param = cmdIns.Parameters.Add("Id", OleDb.OleDbType.Integer)
       Param.SourceColumn = "@Id"
       Param.SourceVersion = DataRowVersion.Current
       Param = cmdIns.Parameters.Add("Name", OleDb.OleDbType.Char)
       Param.SourceColumn = "@Name"
       Param.SourceVersion = DataRowVersion.Current
       adopt.UpdateCommand = cmdIns

       Try
           Con.Open()
           If Con.State = ConnectionState.Open Then
               adopt.MissingSchemaAction =
MissingSchemaAction.AddWithKey
               adopt.Fill(ds, "Artist")
               Con.Close()

               Dim Tables As DataTableCollection
               Dim Table As DataTable
               Dim Cols As DataColumnCollection
               Dim Col As DataColumn
               Dim Rows As DataRowCollection
               Dim Row As DataRow

               Tables = ds.Tables
               Table = Tables("Artist")
               Rows = Table.Rows
               Cols = Table.Columns

               Console.WriteLine("Original Table Looks Like")
               Display(Table)
               Console.ReadLine()

               Console.WriteLine("Id 1 delete")
               Rows.Find(1).Delete()
               Console.WriteLine("deleted")
               Display(Table)
               Console.ReadLine()

               Console.WriteLine("Id 2 Modify")
               Row = Rows.Find(2)
               Row.BeginEdit()
               Row("Name") = "Mantu"
               Row.EndEdit()
               Console.WriteLine("Updated")
               Display(Table)
               Console.ReadLine()

               Console.WriteLine("Id 1 Add")
               Row = Table.NewRow
               Row("Id") = 4
               Row("Name") = "Deepak"
               Rows.Add(Row)
               Console.WriteLine("Added")
               Display(Table)
               Console.ReadLine()

               Con.Open()
               adopt.Update(ds, "Artist")
               Console.WriteLine("Done")

           End If
       Catch ex As Exception
           Console.WriteLine(ex.ToString)
           Console.ReadLine()
       End Try
   End Sub
End Module


The Exact error what I got is :

"System.Data.OleDb.OleDbException: Parameter ?_1 has no default value.
  at System.Data.Common.DbDataAdapter.Update(DataRow[] dataRows,
DataTableMapping tableMapping)
  at System.Data.Common.DbDataAdapter.Update(DataSet dataSet, String
srcTable)
  at ADONetTest.Module1.Main() in
D:\Rabi\DotNetPrac\ADONetTest\ADONetTest\Module1.vb:line 176"

This String is generated by "Ex.ToString"

See comments inline.


What does this mean "no problem in the updation and deletion or insertion in
the dataset"? Do you mean you can change values in DataSet?


but when I am updating the
Sorry, could not see "Coloured" text, many people set their news reader in
plain text format.

Show quoteHide quoteFrom your code, it seems that the two column in the DataTable are name "Id"
and "Name", NOT "@Id" and "@Name". Since you specified the
Param.SourceColumn="@Id" (and  "@Name"), the DataAdapter cannot find these
two columns when executing Update on the DataTable, so, it passes null  to
the parameter. On the other hand, the tabel in database must be defined this
way that the column(s) does not allow null but not default value is
specified. Thus, you get the error.

Your parameter in DataAdapter's command should be like this:

Param = cmdUpd.Parameters.Add("@Name", OleDb.OleDbType.Char)
Param.SourceColumn = "Name"
Param.SourceVersion = DataRowVersion.Current
Param = cmdUpd.Parameters.Add("@Id", OleDb.OleDbType.Integer)
Param.SourceColumn = "Id"
Param.SourceVersion = DataRowVersion.Original

The other important thing when using OleDb name space is that the
Parameter's name does not matters, its sequence order matters. You can:

sUpdSql = "Update Artist Set Name = ? Where Id = ?"
cmdUpd.Connection = Con
cmdUpd.CommandText = sUpdSql
Param = cmdUpd.Parameters.Add("@Whatever1", OleDb.OleDbType.Char)
Param.SourceColumn = "Name"
Param.SourceVersion = DataRowVersion.Current
Param = cmdUpd.Parameters.Add("@Whatever2", OleDb.OleDbType.Integer)
Param.SourceColumn = "Id"
Param.SourceVersion = DataRowVersion.Original

Here parameter "@Whatever1" will be passed to the first "?" in the SLQ
Statement and "@Whatever2" will be passed to the second "?", regardless what
the SourceColumn is. If you do the following, you are screwed:

sUpdSql = "Update Artist Set Name = ? Where Id = ?"
cmdUpd.Connection = Con
cmdUpd.CommandText = sUpdSql
Param = cmdUpd.Parameters.Add("@Whatever1", OleDb.OleDbType.Char)
Param.SourceColumn = "Id"
Param.SourceVersion = DataRowVersion.Current
Param = cmdUpd.Parameters.Add("@Whatever2", OleDb.OleDbType.Integer)
Param.SourceColumn = "Name"
Param.SourceVersion = DataRowVersion.Original


Show quoteHide quote

Uri class removes double slash
Socket error with HTTPWeb Request/Response when copying pictures
How many characters fit in an area?
bug found in Microsoft.Win32.RegistryValueKind.DWord conversion
VISUAL STUDIO.NET 2005. Exlude a file from project