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

  • Thread starter Thread starter r2destini
  • Start date Start date
R

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.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.


r2destini said:
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
Click to expand...


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
database through adaptor error occures (Coloured Red).
Click to expand...


Sorry, could not see "Coloured" text, many people set their news reader in
plain text format.
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
Click to expand...


From 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

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"
Click to expand...
 
Back
Top