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”