Forum: Ruby Problem in database updation through Adapter in VB.Net...

Announcement (2017-05-07): www.ruby-forum.com is now read-only since I unfortunately do not have the time to support and maintain the forum any more. Please see rubyonrails.org/community and ruby-lang.org/en/community for other Rails- und Ruby-related community platforms.
r2destini (Guest)
on 2006-03-04 09:55
(Received via mailing list)
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"
This topic is locked and can not be replied to.