Problem in database updation through Adapter in VB.Net..

[r2destini]

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.Ole Db
Module Module1
Private Const s As String = "Provider=Micro soft.Jet.OLEDB. 4.0;Data
Source=D:\Rabi\ Database\DBTest-1.mdb;Persist Security Info=False"
Public Con As OleDb.OleDbConn ection
Public adopt As OleDb.OleDbData Adapter
Public ds As DataSet
Public sql As String

Dim cmdDel As New OleDb.OleDbComm and, sDelSql As String
Dim cmdIns As New OleDb.OleDbComm and, sInsSql As String
Dim cmdUpd As New OleDb.OleDbComm and, sUpdSql As String
Dim Param As New OleDb.OleDbPara meter

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.Coun t - 1
row = Table.Rows(i)
Select Case row.RowState
Case DataRowState.De leted
Console.WriteLi ne("[Deleted]")
Case DataRowState.Mo dified
Console.WriteLi ne("[Modified]")
Case DataRowState.Ad ded
Console.WriteLi ne("[Added]")
Case DataRowState.Un changed
Console.WriteLi ne("[Unchanged]")
End Select
For j = 0 To Table.Columns.C ount - 1
If row.RowState <> DataRowState.De leted Then
Console.WriteLi ne("{0}", row.Item(j))
End If
Next
Console.WriteLi ne()
Next
End Sub
Public Sub Main()
Try
Con = New OleDb.OleDbConn ection(s)
sql = "Select * from Artist"
adopt = New OleDbDataAdapte r(sql, Con)
ds = New DataSet
Catch ex As Exception
Console.WriteLi ne(ex.ToString)
Console.ReadLin e()
End Try

sDelSql = "Delete From Artist Where Id = ?"
cmdDel.Connecti on = Con
cmdDel.CommandT ext = sDelSql
Param = cmdDel.Paramete rs.Add("Id", OleDb.OleDbType .Integer)
Param.SourceCol umn = "@ID"
Param.SourceVer sion = DataRowVersion. Original
adopt.DeleteCom mand = cmdDel

sUpdSql = "Update Artist Set Name = ? Where Id = ?"
cmdUpd.Connecti on = Con
cmdUpd.CommandT ext = sUpdSql
Param = cmdUpd.Paramete rs.Add("Name", OleDb.OleDbType .Char)
Param.SourceCol umn = "@Name"
Param.SourceVer sion = DataRowVersion. Current
Param = cmdUpd.Paramete rs.Add("Id", OleDb.OleDbType .Integer)
Param.SourceCol umn = "@Id"
Param.SourceVer sion = DataRowVersion. Original
adopt.UpdateCom mand = cmdUpd

sInsSql = "Insert Into Artist (Id,Name) Values(?,?)"
cmdIns.Connecti on = Con
cmdIns.CommandT ext = sInsSql
Param = cmdIns.Paramete rs.Add("Id", OleDb.OleDbType .Integer)
Param.SourceCol umn = "@Id"
Param.SourceVer sion = DataRowVersion. Current
Param = cmdIns.Paramete rs.Add("Name", OleDb.OleDbType .Char)
Param.SourceCol umn = "@Name"
Param.SourceVer sion = DataRowVersion. Current
adopt.UpdateCom mand = cmdIns

Try
Con.Open()
If Con.State = ConnectionState .Open Then
adopt.MissingSc hemaAction =
MissingSchemaAc tion.AddWithKey
adopt.Fill(ds, "Artist")
Con.Close()

Dim Tables As DataTableCollec tion
Dim Table As DataTable
Dim Cols As DataColumnColle ction
Dim Col As DataColumn
Dim Rows As DataRowCollecti on
Dim Row As DataRow

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

Console.WriteLi ne("Original Table Looks Like")
Display(Table)
Console.ReadLin e()

Console.WriteLi ne("Id 1 delete")
Rows.Find(1).De lete()
Console.WriteLi ne("deleted")
Display(Table)
Console.ReadLin e()

Console.WriteLi ne("Id 2 Modify")
Row = Rows.Find(2)
Row.BeginEdit()
Row("Name") = "Mantu"
Row.EndEdit()
Console.WriteLi ne("Updated")
Display(Table)
Console.ReadLin e()

Console.WriteLi ne("Id 1 Add")
Row = Table.NewRow
Row("Id") = 4
Row("Name") = "Deepak"
Rows.Add(Row)
Console.WriteLi ne("Added")
Display(Table)
Console.ReadLin e()

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

End If
Catch ex As Exception
Console.WriteLi ne(ex.ToString)
Console.ReadLin e()
End Try
End Sub
End Module
The Exact error what I got is :

"System.Data.Ol eDb.OleDbExcept ion: Parameter ?_1 has no default value.
at System.Data.Com mon.DbDataAdapt er.Update(DataR ow[] dataRows,
DataTableMappin g tableMapping)
at System.Data.Com mon.DbDataAdapt er.Update(DataS et dataSet, String
srcTable)
at ADONetTest.Modu le1.Main() in
D:\Rabi\DotNetP rac\ADONetTest\ ADONetTest\Modu le1.vb:line 176"

This String is generated by "Ex.ToStrin g"

[AHadiA]

Hi r2

Correct the following :
1. Replace any @ with "" . when you set SourceColumn there is no need
for @ char
for example : Param.SourceCol umn = "@Id" -- >
Param.SourceCol umn = "Id"
2.You assigned incorrect sqlcommand
sInsSql = "Insert Into Artist (Id,Name) Values(?,?)"
cmdIns.Connecti on = Con
cmdIns.CommandT ext = sInsSql
Param = cmdIns.Paramete rs.Add("Id", OleDb.OleDbType .Integer)
Param.SourceCol umn = "@Id"
Param.SourceVer sion = DataRowVersion. Current
Param = cmdIns.Paramete rs.Add("Name", OleDb.OleDbType .Char)
Param.SourceCol umn = "@Name"
Param.SourceVer sion = DataRowVersion. Current
adopt.UpdateCom mand = cmdIns --- > adopt.InsertCom mand =
cmdIns

After applying this changes, it will work ( i test it )
I hope this help
A.Hadi