473,321 Members | 1,916 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,321 software developers and data experts.

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

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.v b:line 176"

This String is generated by "Ex.ToString"

Mar 4 '06 #1
1 1812
Hi r2

Correct the following :
1. Replace any @ with "" . when you set SourceColumn there is no need
for @ char
for example : Param.SourceColumn = "@Id" -- >
Param.SourceColumn = "Id"
2.You assigned incorrect sqlcommand
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 --- > adopt.InsertCommand =
cmdIns

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

Mar 5 '06 #2

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

2
by: Chris | last post by:
A weird issue...though hopefully not for everyone... I am trying to connect to a 10g database on a Red Hat Linux server from my 9i client on a XP pc. Both are on my local home network, behind...
1
by: Sylesh Nair | last post by:
could anyone give me a possible solution for a Windows Service (in C#) listening to a table in a database for insertion or updation. thanks
5
by: Jeff | last post by:
IDE: VS 2003 :NET OS: XP Pro My app have a form with a tab-control on it. The tab-control have 2 tabpages. One of the tabpages displays a datagrid, and the other tabpage displays details (order...
1
by: r2destini | last post by:
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...
0
by: prashant | last post by:
Hi, I am trying to set up Transactional replication with immediate updation. The configuration is as follows: 1. Publisher is SQL server 2000 Enterprise Edition, and Distributor is on the...
5
by: Usman Jamil | last post by:
Hi I've a class that creates a connection to a database, gets and loop on a dataset given a query and then close the connection. When I use netstat viewer to see if there is any connection open...
1
by: roshan56us | last post by:
Hi, i have problem in updation of access database, which is in backend and Visual basic as front end. its using 800 records in MS-Access but,while updating the database, it doesn't update sometime...
1
by: siri11 | last post by:
Hi everyone!!!!!!!!! If i update a record in a table in sqlserver from front end (c#.net),using a stored procedure for updating then after updation if i open the table then it is showing the same...
1
Curtis Rutland
by: Curtis Rutland | last post by:
How To Use A Database In Your Program Part II This article is intended to extend Frinny’s excellent article: How to Use a Database in Your Program. Frinny’s article defines the basic concepts...
0
by: okonita | last post by:
Hi all, I am having a DB2 connectivity problem that I hope someone can help me resolve. I need this to test Replication and such other things. What am I doing wrong here? Any help that I can get...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.