Problem in database updation through Adapter in VB.Net


#1

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”