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"

tranferring data from Access to SQL Server Express
Have you stopped using ADO?
connection.close vs. connection.dispose
Locking a record in Access from vb.net
SqlDataReader 2.0