Update command of data adpater

[simsima]

I am trying to create oledb data adapter update command

I have created the select command successfully, data gets filled to the data grid but i make some change to the data and want to write back the changes howeover it won't work because may update command might not be correct any help will be welcomed

Imports System
Imports System.Data
Imports System.Data.OleDb
Imports System.Data.Common

Public Class Form1
Friend WithEvents cmdSelectionpat As New OleDbCommand()
Friend WithEvents cmdInsertpat As New OleDbCommand()
Friend WithEvents cmdUpdatepat As New OleDbCommand()
Friend WithEvents cmdDeletepat As New OleDbCommand()

Dim dspat As New DataSet()
Dim dbconn As New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Password=;User ID=Admin;Data Source='C:\ant_tp_stats.mdb'")
Friend WithEvents dapat As New OleDbDataAdapter()
Dim parameter As OleDbParameter

public Sub New()

' This call is required by the Windows Form Designer.
InitializeComponent()

' Add any initialization after the InitializeComponent() call.

dapat.SelectCommand = cmdSelectionpat
dapat.InsertCommand = cmdInsertpat
dapat.UpdateCommand = cmdUpdatepat
dapat.DeleteCommand = cmdDeletepat

'the select command
cmdSelectionpat.CommandText = "select patno,pfirstname,psurname from tb_patient"

' the update command
cmdUpdatepat.CommandText = "UPDATE tb_patient SET PatNo = ?, pFirstName = ?, pSurname = ?" _
& "WHERE (PatNo = ?)"
cmdUpdatepat.Parameters.Add("patno", OleDbType.BigInt, 8, "patno")
cmdUpdatepat.Parameters.Add("pfirstname", OleDbType.VarChar, 8, "pfirstname")
cmdUpdatepat.Parameters.Add("psurname", OleDbType.VarChar, 8, "psurname")

cmdSelectionpat.Connection = dbconn
cmdUpdatepat.Connection = dbconn
end sub

Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
'update button

dapat.Update(dspat)
End Sub

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
'button to fill the data grid with the selection

Me.dbconn.Open()
dspat.Clear()
dapat.Fill(dspat)
Me.Dgvpatients.DataSource = dspat.Tables(0)
Me.OleDbConn.Close()
End Sub


end class

I get an error Saying no value given one or more parameters

 

[jebenson]

Try setting up your command objects like this:

cmdUpdatepat.CommandText = "UPDATE tb_patient SET PatNo = [red]@PatNo[/red], pFirstName = [red]@pFirstName[/red], pSurname = [red]@pSurname[/red] WHERE (PatNo = [red]@PatNo[/red])"
cmdUpdatepat.Parameters.Add("[red]@PatNo[/red]", OleDbType.BigInt, 8, "patno")
cmdUpdatepat.Parameters.Add("[red]@pFirstName[/red]", OleDbType.VarChar, 8, "pfirstname")
cmdUpdatepat.Parameters.Add("[red]@pSurname[/red]", OleDbType.VarChar, 8, "psurname")

I used to rock and roll every night and party every day. Then it was every other day. Now I'm lucky if I can find 30 minutes a week in which to get funky. - Homer Simpson

Arrrr, mateys! Ye needs ta be preparin' yerselves fer Talk Like a Pirate Day!

 

[simsima]

i have changed my code to what you have suggested but still no update is being applied to the data. this time there are no errors raised. just no updates to the data any more suggestions

cmdUpdatepat.CommandText = "update tb_patient set pfirstname=@pfirstname,psurname=psurnam,patno=@patno where (patno=?)"
cmdUpdatepat.Parameters.Add("@pfirstname", OleDbType.VarChar, 12, "pfirstname")
cmdUpdatepat.Parameters.Add("@psurname", OleDbType.VarChar, 8, "psurname")

Dim paramter As OleDbParameter = dapat.UpdateCommand.Parameters.Add("@patno", OleDbType.BigInt, 12, "patno")
paramter.SourceColumn = "patno"
paramter.SourceVersion = DataRowVersion.Original

Dim dsup As New DataSet()
dsup.Tables.Add("patup")
dbconn.Open()
dsup.Clear()
dapat.Fill(dsup.Tables("patup"))
dapat.Update(dsup, "patup")
Me.Dgvpatients.DataSource = dsup.Tables("patup")
dsup.Tables("patup").AcceptChanges()
dbconn.Close()

but

 

[simsima]

I gues No one knows what i am talking about

 

[Craig0201]

No. Your problems are trivial.

The reason why I won't help is because guys who don't know what they are doing messing with PATIENT tables scare me.

IMO, and please take this in the right way, you should find someone capable of doing this right as I don't think that you're competent to be playing with such vital data. At least go an take a few classes before you try to do something like this.

C

 

[Qik3Coder]

The following is given as a helpful suggestion, especially given the nature of your data.

Your database interactions should be in stored procedures
You should test the stored procs thoroughly before hooking them to application code.
The code to communicate with a database IS trivial compared to some of the other things you have to do in .Net, and there are plenty of examples here and on the web. You can probably find some in your .Net in 21 days book, if you look in the index, under ExecuteNonQuery

Everyone has a first job as a dev. Some of them are more forgiving than others. The amount of allowable errors is inversely related to how import the code is. Your working with and altering real people's medical history. When the data is this important, there is no "Oh, well this should work"

/end rant

Thank you,

-Sometimes the answer to your question is the hack that works

 

[simsima]

Thanks for your suggestions.

 

[PRPhx]

And I'll add my 2 cents here, fwiw. You should be working in a TEST environment, not in production. You screw up someone's vital data and your leaving yourself and whomever your working for wide open to lawsuits, at the very least.