update data store via code only

After much frustration I was able to update my data store via code only.
Using the data adapter was the only way I was able to set up all the objects
written in my code. Basically, I cheated by creating an adapter and then
copy, paste, modify it's code to suite my needs. This was the end result:

Private sub updateTable( )

Dim myData As DataSet
'Command is a form level oledbcommand object
Dim Adapter1 As New OleDbDataAdapter(Command)
myData = New DataSet()
myData = Me.DataGrid1.DataSource

Dim Cno As OleDbConnection
Cno = New OleDbConnection(Me.strConnectionString)

'************************************************* *
' here is where my copy, paste, modify code from
'adapter control starts
'*************************************************

Adapter1.UpdateCommand = New OleDbCommand()
'best way to get parameters and update string is to create them
'using an oledbdataadapter control

Dim strUpdate As String = "UPDATE employee SET [Employee Number] = ?, [First
Name] = ?, [Last Name] = ? WHER" & _
"E ([Employee Number] = ?) AND ([First Name] = ? OR ? IS NULL AND [First
Name] IS" & _
" NULL) AND ([Last Name] = ? OR ? IS NULL AND [Last Name] IS NULL)"

With Adapter1.UpdateCommand
..CommandText = strUpdate
..Connection = Cno

..Parameters.Add(New OleDbParameter("Employee_Number",
System.Data.OleDb.OleDbType.SmallInt, 0,
System.Data.ParameterDirection.Input, False, CType(5, Byte), CType(0, Byte),
"Employee Number", System.Data.DataRowVersion.Current, Nothing))

..Parameters.Add(New OleDbParameter("First_Name",
System.Data.OleDb.OleDbType.VarWChar, 25, "First Name"))

..Parameters.Add(New OleDbParameter("Last_Name",
System.Data.OleDb.OleDbType.VarWChar, 25, "Last Name"))

..Parameters.Add(New OleDbParameter("Original_Employee_Number",
System.Data.OleDb.OleDbType.SmallInt, 0,
System.Data.ParameterDirection.Input, False, CType(5, Byte), CType(0, Byte),
"Employee Number", System.Data.DataRowVersion.Original, Nothing))

..Parameters.Add(New OleDbParameter("Original_First_Name",
System.Data.OleDb.OleDbType.VarWChar, 25,
System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0, Byte),
"First Name", System.Data.DataRowVersion.Original, Nothing))

..Parameters.Add(New OleDbParameter("Original_First_Name1",
System.Data.OleDb.OleDbType.VarWChar, 25,
System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0, Byte),
"First Name", System.Data.DataRowVersion.Original, Nothing))

..Parameters.Add(New OleDbParameter("Original_Last_Name",
System.Data.OleDb.OleDbType.VarWChar, 25,
System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0, Byte),
"Last Name", System.Data.DataRowVersion.Original, Nothing))

..Parameters.Add(New OleDbParameter("Original_Last_Name1",
System.Data.OleDb.OleDbType.VarWChar, 25,
System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0, Byte),
"Last Name", System.Data.DataRowVersion.Original, Nothing))
End With

'***************************************
'here is where it ends
'***************************************

Try
Adapter1.Update(myData.Tables(0))
Catch prob As OleDbException
MsgBox(prob.Message)
End Try
end sub


I was never able to get the commandbuilder to work. I just think this is a
lot of coding where is previous version doing this in code was much simpler.
Have I just been spoiled by vb6 and it's simplicity?

re: update data store via code only

I haven't flipped through all of this code b/c it's working so I can't tell
what was wrong with the commandbuilder. Usually it's very easy to use - the
only catch is you must have a PK field in it and you can't control your
concurrency options. With that in mind, all you need is a valid Select
statement so it can infer the rest of the CRUD info and you're good to go.
If you want to post the code, I'll be glad to take a look at it.

--

W.G. Ryan, eMVP

Have an opinion on the effectiveness of Microsoft Embedded newsgroups?
Let Microsoft know!
https://www.windowsembeddedeval.com/...ity/newsgroups
"William" <none@none.com> wrote in message
news:35yIc.274$cx5.250@bignews6.bellsouth.net...[color=blue]
> After much frustration I was able to update my data store via code only.
> Using the data adapter was the only way I was able to set up all the[/color]
objects[color=blue]
> written in my code. Basically, I cheated by creating an adapter and then
> copy, paste, modify it's code to suite my needs. This was the end result:
>
> Private sub updateTable( )
>
> Dim myData As DataSet
> 'Command is a form level oledbcommand object
> Dim Adapter1 As New OleDbDataAdapter(Command)
> myData = New DataSet()
> myData = Me.DataGrid1.DataSource
>
> Dim Cno As OleDbConnection
> Cno = New OleDbConnection(Me.strConnectionString)
>
> '************************************************* *
> ' here is where my copy, paste, modify code from
> 'adapter control starts
> '*************************************************
>
> Adapter1.UpdateCommand = New OleDbCommand()
> 'best way to get parameters and update string is to create them
> 'using an oledbdataadapter control
>
> Dim strUpdate As String = "UPDATE employee SET [Employee Number] = ?,[/color]
[First[color=blue]
> Name] = ?, [Last Name] = ? WHER" & _
> "E ([Employee Number] = ?) AND ([First Name] = ? OR ? IS NULL AND [First
> Name] IS" & _
> " NULL) AND ([Last Name] = ? OR ? IS NULL AND [Last Name] IS NULL)"
>
> With Adapter1.UpdateCommand
> .CommandText = strUpdate
> .Connection = Cno
>
> .Parameters.Add(New OleDbParameter("Employee_Number",
> System.Data.OleDb.OleDbType.SmallInt, 0,
> System.Data.ParameterDirection.Input, False, CType(5, Byte), CType(0,[/color]
Byte),[color=blue]
> "Employee Number", System.Data.DataRowVersion.Current, Nothing))
>
> .Parameters.Add(New OleDbParameter("First_Name",
> System.Data.OleDb.OleDbType.VarWChar, 25, "First Name"))
>
> .Parameters.Add(New OleDbParameter("Last_Name",
> System.Data.OleDb.OleDbType.VarWChar, 25, "Last Name"))
>
> .Parameters.Add(New OleDbParameter("Original_Employee_Number",
> System.Data.OleDb.OleDbType.SmallInt, 0,
> System.Data.ParameterDirection.Input, False, CType(5, Byte), CType(0,[/color]
Byte),[color=blue]
> "Employee Number", System.Data.DataRowVersion.Original, Nothing))
>
> .Parameters.Add(New OleDbParameter("Original_First_Name",
> System.Data.OleDb.OleDbType.VarWChar, 25,
> System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0,[/color]
Byte),[color=blue]
> "First Name", System.Data.DataRowVersion.Original, Nothing))
>
> .Parameters.Add(New OleDbParameter("Original_First_Name1",
> System.Data.OleDb.OleDbType.VarWChar, 25,
> System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0,[/color]
Byte),[color=blue]
> "First Name", System.Data.DataRowVersion.Original, Nothing))
>
> .Parameters.Add(New OleDbParameter("Original_Last_Name",
> System.Data.OleDb.OleDbType.VarWChar, 25,
> System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0,[/color]
Byte),[color=blue]
> "Last Name", System.Data.DataRowVersion.Original, Nothing))
>
> .Parameters.Add(New OleDbParameter("Original_Last_Name1",
> System.Data.OleDb.OleDbType.VarWChar, 25,
> System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0,[/color]
Byte),[color=blue]
> "Last Name", System.Data.DataRowVersion.Original, Nothing))
> End With
>
> '***************************************
> 'here is where it ends
> '***************************************
>
> Try
> Adapter1.Update(myData.Tables(0))
> Catch prob As OleDbException
> MsgBox(prob.Message)
> End Try
> end sub
>
>
> I was never able to get the commandbuilder to work. I just think this is[/color]
a[color=blue]
> lot of coding where is previous version doing this in code was much[/color]
simpler.[color=blue]
> Have I just been spoiled by vb6 and it's simplicity?
>
>[/color]

re: update data store via code only

IMHO Command Builder and Wizards are only good for quick tests when your
trying to look at functionality of something or to get a connection string
etc.

You are really better off buying an ADO.NET books and working through it,
yes its slow, yes it can be painfull, but ultimatley it is worth the effort
because at least then you know how it all hangs together.

CommandBuilder/Wizards are only any good for simple Select Statements.


Regards - OHM


--

OHM ( Terry Burns )
. . . One-Handed-Man . . .

Time flies when you don't know what you're doing

"William" <none@none.com> wrote in message
news:35yIc.274$cx5.250@bignews6.bellsouth.net...[color=blue]
> After much frustration I was able to update my data store via code only.
> Using the data adapter was the only way I was able to set up all the[/color]
objects[color=blue]
> written in my code. Basically, I cheated by creating an adapter and then
> copy, paste, modify it's code to suite my needs. This was the end result:
>
> Private sub updateTable( )
>
> Dim myData As DataSet
> 'Command is a form level oledbcommand object
> Dim Adapter1 As New OleDbDataAdapter(Command)
> myData = New DataSet()
> myData = Me.DataGrid1.DataSource
>
> Dim Cno As OleDbConnection
> Cno = New OleDbConnection(Me.strConnectionString)
>
> '************************************************* *
> ' here is where my copy, paste, modify code from
> 'adapter control starts
> '*************************************************
>
> Adapter1.UpdateCommand = New OleDbCommand()
> 'best way to get parameters and update string is to create them
> 'using an oledbdataadapter control
>
> Dim strUpdate As String = "UPDATE employee SET [Employee Number] = ?,[/color]
[First[color=blue]
> Name] = ?, [Last Name] = ? WHER" & _
> "E ([Employee Number] = ?) AND ([First Name] = ? OR ? IS NULL AND [First
> Name] IS" & _
> " NULL) AND ([Last Name] = ? OR ? IS NULL AND [Last Name] IS NULL)"
>
> With Adapter1.UpdateCommand
> .CommandText = strUpdate
> .Connection = Cno
>
> .Parameters.Add(New OleDbParameter("Employee_Number",
> System.Data.OleDb.OleDbType.SmallInt, 0,
> System.Data.ParameterDirection.Input, False, CType(5, Byte), CType(0,[/color]
Byte),[color=blue]
> "Employee Number", System.Data.DataRowVersion.Current, Nothing))
>
> .Parameters.Add(New OleDbParameter("First_Name",
> System.Data.OleDb.OleDbType.VarWChar, 25, "First Name"))
>
> .Parameters.Add(New OleDbParameter("Last_Name",
> System.Data.OleDb.OleDbType.VarWChar, 25, "Last Name"))
>
> .Parameters.Add(New OleDbParameter("Original_Employee_Number",
> System.Data.OleDb.OleDbType.SmallInt, 0,
> System.Data.ParameterDirection.Input, False, CType(5, Byte), CType(0,[/color]
Byte),[color=blue]
> "Employee Number", System.Data.DataRowVersion.Original, Nothing))
>
> .Parameters.Add(New OleDbParameter("Original_First_Name",
> System.Data.OleDb.OleDbType.VarWChar, 25,
> System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0,[/color]
Byte),[color=blue]
> "First Name", System.Data.DataRowVersion.Original, Nothing))
>
> .Parameters.Add(New OleDbParameter("Original_First_Name1",
> System.Data.OleDb.OleDbType.VarWChar, 25,
> System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0,[/color]
Byte),[color=blue]
> "First Name", System.Data.DataRowVersion.Original, Nothing))
>
> .Parameters.Add(New OleDbParameter("Original_Last_Name",
> System.Data.OleDb.OleDbType.VarWChar, 25,
> System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0,[/color]
Byte),[color=blue]
> "Last Name", System.Data.DataRowVersion.Original, Nothing))
>
> .Parameters.Add(New OleDbParameter("Original_Last_Name1",
> System.Data.OleDb.OleDbType.VarWChar, 25,
> System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0,[/color]
Byte),[color=blue]
> "Last Name", System.Data.DataRowVersion.Original, Nothing))
> End With
>
> '***************************************
> 'here is where it ends
> '***************************************
>
> Try
> Adapter1.Update(myData.Tables(0))
> Catch prob As OleDbException
> MsgBox(prob.Message)
> End Try
> end sub
>
>
> I was never able to get the commandbuilder to work. I just think this is[/color]
a[color=blue]
> lot of coding where is previous version doing this in code was much[/color]
simpler.[color=blue]
> Have I just been spoiled by vb6 and it's simplicity?
>
>[/color]

re: update data store via code only

Try setting the QuotePrefix and QuoteSuffix properties to '[' and ']',
respectively. I believe the problem is that some of your column's have
spaces in the name, and without these symbols around the names, there is a
syntax error.

"William" <none@none.com> wrote in message
news:wSyIc.48778$9t6.10240@bignews3.bellsouth.net. ..[color=blue]
> William, thanks for the help. Here is the last version of my[/color]
commandbuilder[color=blue]
> code I used after many revision that never worked.
>
> Private Sub btnCommandBuilder_Click(ByVal sender As System.Object, ByVal e
> As System.EventArgs) Handles btnCommandBuilder.Click
>
> Dim cb As OleDbCommandBuilder
> Dim con As New OleDbConnection(Me.strConnectionString)
> Dim myDat As New DataSet()
>
> 'form level adapter
> Adapter.SelectCommand = New OleDbCommand("select * from employee", con)
>
> cb = New OleDbCommandBuilder(Adapter)
>
> myDat = Me.DataGrid1.DataSource
>
> Try
> Adapter.Update(myDat.Tables(0))
>
> Catch prob As Exception
> MsgBox(prob.Message)
>
> End Try
>
> End Sub
>
>
> This one keeps barking about Syntax error in update statement. I am[/color]
curious[color=blue]
> as to the PK field since I never implemented it in my commandbuilder. It[/color]
is[color=blue]
> implemented in the data store however. I am thinking this might be part[/color]
if[color=blue]
> not all the problem
>
>
>
> "William Ryan eMVP" <dotnetguru@comcast.nospam.net> wrote in message
> news:OKjUmaCaEHA.2388@TK2MSFTNGP11.phx.gbl...[color=green]
> > I haven't flipped through all of this code b/c it's working so I can't[/color]
> tell[color=green]
> > what was wrong with the commandbuilder. Usually it's very easy to use -[/color]
> the[color=green]
> > only catch is you must have a PK field in it and you can't control your
> > concurrency options. With that in mind, all you need is a valid Select
> > statement so it can infer the rest of the CRUD info and you're good to[/color][/color]
go.[color=blue][color=green]
> > If you want to post the code, I'll be glad to take a look at it.
> >
> > --
> >
> > W.G. Ryan, eMVP
> >
> > Have an opinion on the effectiveness of Microsoft Embedded newsgroups?
> > Let Microsoft know!
> > https://www.windowsembeddedeval.com/...ity/newsgroups
> > "William" <none@none.com> wrote in message
> > news:35yIc.274$cx5.250@bignews6.bellsouth.net...[color=darkred]
> > > After much frustration I was able to update my data store via code[/color][/color][/color]
only.[color=blue][color=green][color=darkred]
> > > Using the data adapter was the only way I was able to set up all the[/color]
> > objects[color=darkred]
> > > written in my code. Basically, I cheated by creating an adapter and[/color][/color]
> then[color=green][color=darkred]
> > > copy, paste, modify it's code to suite my needs. This was the end[/color][/color]
> result:[color=green][color=darkred]
> > >
> > > Private sub updateTable( )
> > >
> > > Dim myData As DataSet
> > > 'Command is a form level oledbcommand object
> > > Dim Adapter1 As New OleDbDataAdapter(Command)
> > > myData = New DataSet()
> > > myData = Me.DataGrid1.DataSource
> > >
> > > Dim Cno As OleDbConnection
> > > Cno = New OleDbConnection(Me.strConnectionString)
> > >
> > > '************************************************* *
> > > ' here is where my copy, paste, modify code from
> > > 'adapter control starts
> > > '*************************************************
> > >
> > > Adapter1.UpdateCommand = New OleDbCommand()
> > > 'best way to get parameters and update string is to create them
> > > 'using an oledbdataadapter control
> > >
> > > Dim strUpdate As String = "UPDATE employee SET [Employee Number] = ?,[/color]
> > [First[color=darkred]
> > > Name] = ?, [Last Name] = ? WHER" & _
> > > "E ([Employee Number] = ?) AND ([First Name] = ? OR ? IS NULL AND[/color][/color][/color]
[First[color=blue][color=green][color=darkred]
> > > Name] IS" & _
> > > " NULL) AND ([Last Name] = ? OR ? IS NULL AND [Last Name] IS NULL)"
> > >
> > > With Adapter1.UpdateCommand
> > > .CommandText = strUpdate
> > > .Connection = Cno
> > >
> > > .Parameters.Add(New OleDbParameter("Employee_Number",
> > > System.Data.OleDb.OleDbType.SmallInt, 0,
> > > System.Data.ParameterDirection.Input, False, CType(5, Byte), CType(0,[/color]
> > Byte),[color=darkred]
> > > "Employee Number", System.Data.DataRowVersion.Current, Nothing))
> > >
> > > .Parameters.Add(New OleDbParameter("First_Name",
> > > System.Data.OleDb.OleDbType.VarWChar, 25, "First Name"))
> > >
> > > .Parameters.Add(New OleDbParameter("Last_Name",
> > > System.Data.OleDb.OleDbType.VarWChar, 25, "Last Name"))
> > >
> > > .Parameters.Add(New OleDbParameter("Original_Employee_Number",
> > > System.Data.OleDb.OleDbType.SmallInt, 0,
> > > System.Data.ParameterDirection.Input, False, CType(5, Byte), CType(0,[/color]
> > Byte),[color=darkred]
> > > "Employee Number", System.Data.DataRowVersion.Original, Nothing))
> > >
> > > .Parameters.Add(New OleDbParameter("Original_First_Name",
> > > System.Data.OleDb.OleDbType.VarWChar, 25,
> > > System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0,[/color]
> > Byte),[color=darkred]
> > > "First Name", System.Data.DataRowVersion.Original, Nothing))
> > >
> > > .Parameters.Add(New OleDbParameter("Original_First_Name1",
> > > System.Data.OleDb.OleDbType.VarWChar, 25,
> > > System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0,[/color]
> > Byte),[color=darkred]
> > > "First Name", System.Data.DataRowVersion.Original, Nothing))
> > >
> > > .Parameters.Add(New OleDbParameter("Original_Last_Name",
> > > System.Data.OleDb.OleDbType.VarWChar, 25,
> > > System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0,[/color]
> > Byte),[color=darkred]
> > > "Last Name", System.Data.DataRowVersion.Original, Nothing))
> > >
> > > .Parameters.Add(New OleDbParameter("Original_Last_Name1",
> > > System.Data.OleDb.OleDbType.VarWChar, 25,
> > > System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0,[/color]
> > Byte),[color=darkred]
> > > "Last Name", System.Data.DataRowVersion.Original, Nothing))
> > > End With
> > >
> > > '***************************************
> > > 'here is where it ends
> > > '***************************************
> > >
> > > Try
> > > Adapter1.Update(myData.Tables(0))
> > > Catch prob As OleDbException
> > > MsgBox(prob.Message)
> > > End Try
> > > end sub
> > >
> > >
> > > I was never able to get the commandbuilder to work. I just think this[/color][/color]
> is[color=green]
> > a[color=darkred]
> > > lot of coding where is previous version doing this in code was much[/color]
> > simpler.[color=darkred]
> > > Have I just been spoiled by vb6 and it's simplicity?
> > >
> > >[/color]
> >
> >[/color]
>
>
>[/color]

re: update data store via code only

Mariana you are a genass (genious). You know how when you get so entrenched
in a problem that you start to believe that the problem is so complex that
the only solution is a complex solution. Now it all seems so much simpler.
That first step is a doozy.


Thanks again


"Marina" <someone@nospam.com> wrote in message
news:ODe5d5CaEHA.4052@TK2MSFTNGP10.phx.gbl...[color=blue]
> Try setting the QuotePrefix and QuoteSuffix properties to '[' and ']',
> respectively. I believe the problem is that some of your column's have
> spaces in the name, and without these symbols around the names, there is a
> syntax error.
>
> "William" <none@none.com> wrote in message
> news:wSyIc.48778$9t6.10240@bignews3.bellsouth.net. ..[color=green]
> > William, thanks for the help. Here is the last version of my[/color]
> commandbuilder[color=green]
> > code I used after many revision that never worked.
> >
> > Private Sub btnCommandBuilder_Click(ByVal sender As System.Object, ByVal[/color][/color]
e[color=blue][color=green]
> > As System.EventArgs) Handles btnCommandBuilder.Click
> >
> > Dim cb As OleDbCommandBuilder
> > Dim con As New OleDbConnection(Me.strConnectionString)
> > Dim myDat As New DataSet()
> >
> > 'form level adapter
> > Adapter.SelectCommand = New OleDbCommand("select * from employee", con)
> >
> > cb = New OleDbCommandBuilder(Adapter)
> >
> > myDat = Me.DataGrid1.DataSource
> >
> > Try
> > Adapter.Update(myDat.Tables(0))
> >
> > Catch prob As Exception
> > MsgBox(prob.Message)
> >
> > End Try
> >
> > End Sub
> >
> >
> > This one keeps barking about Syntax error in update statement. I am[/color]
> curious[color=green]
> > as to the PK field since I never implemented it in my commandbuilder.[/color][/color]
It[color=blue]
> is[color=green]
> > implemented in the data store however. I am thinking this might be[/color][/color]
part[color=blue]
> if[color=green]
> > not all the problem
> >
> >
> >
> > "William Ryan eMVP" <dotnetguru@comcast.nospam.net> wrote in message
> > news:OKjUmaCaEHA.2388@TK2MSFTNGP11.phx.gbl...[color=darkred]
> > > I haven't flipped through all of this code b/c it's working so I can't[/color]
> > tell[color=darkred]
> > > what was wrong with the commandbuilder. Usually it's very easy to[/color][/color][/color]
use -[color=blue][color=green]
> > the[color=darkred]
> > > only catch is you must have a PK field in it and you can't control[/color][/color][/color]
your[color=blue][color=green][color=darkred]
> > > concurrency options. With that in mind, all you need is a valid[/color][/color][/color]
Select[color=blue][color=green][color=darkred]
> > > statement so it can infer the rest of the CRUD info and you're good to[/color][/color]
> go.[color=green][color=darkred]
> > > If you want to post the code, I'll be glad to take a look at it.
> > >
> > > --
> > >
> > > W.G. Ryan, eMVP
> > >
> > > Have an opinion on the effectiveness of Microsoft Embedded newsgroups?
> > > Let Microsoft know!
> > > https://www.windowsembeddedeval.com/...ity/newsgroups
> > > "William" <none@none.com> wrote in message
> > > news:35yIc.274$cx5.250@bignews6.bellsouth.net...
> > > > After much frustration I was able to update my data store via code[/color][/color]
> only.[color=green][color=darkred]
> > > > Using the data adapter was the only way I was able to set up all the
> > > objects
> > > > written in my code. Basically, I cheated by creating an adapter and[/color]
> > then[color=darkred]
> > > > copy, paste, modify it's code to suite my needs. This was the end[/color]
> > result:[color=darkred]
> > > >
> > > > Private sub updateTable( )
> > > >
> > > > Dim myData As DataSet
> > > > 'Command is a form level oledbcommand object
> > > > Dim Adapter1 As New OleDbDataAdapter(Command)
> > > > myData = New DataSet()
> > > > myData = Me.DataGrid1.DataSource
> > > >
> > > > Dim Cno As OleDbConnection
> > > > Cno = New OleDbConnection(Me.strConnectionString)
> > > >
> > > > '************************************************* *
> > > > ' here is where my copy, paste, modify code from
> > > > 'adapter control starts
> > > > '*************************************************
> > > >
> > > > Adapter1.UpdateCommand = New OleDbCommand()
> > > > 'best way to get parameters and update string is to create them
> > > > 'using an oledbdataadapter control
> > > >
> > > > Dim strUpdate As String = "UPDATE employee SET [Employee Number] =[/color][/color][/color]
?,[color=blue][color=green][color=darkred]
> > > [First
> > > > Name] = ?, [Last Name] = ? WHER" & _
> > > > "E ([Employee Number] = ?) AND ([First Name] = ? OR ? IS NULL AND[/color][/color]
> [First[color=green][color=darkred]
> > > > Name] IS" & _
> > > > " NULL) AND ([Last Name] = ? OR ? IS NULL AND [Last Name] IS NULL)"
> > > >
> > > > With Adapter1.UpdateCommand
> > > > .CommandText = strUpdate
> > > > .Connection = Cno
> > > >
> > > > .Parameters.Add(New OleDbParameter("Employee_Number",
> > > > System.Data.OleDb.OleDbType.SmallInt, 0,
> > > > System.Data.ParameterDirection.Input, False, CType(5, Byte),[/color][/color][/color]
CType(0,[color=blue][color=green][color=darkred]
> > > Byte),
> > > > "Employee Number", System.Data.DataRowVersion.Current, Nothing))
> > > >
> > > > .Parameters.Add(New OleDbParameter("First_Name",
> > > > System.Data.OleDb.OleDbType.VarWChar, 25, "First Name"))
> > > >
> > > > .Parameters.Add(New OleDbParameter("Last_Name",
> > > > System.Data.OleDb.OleDbType.VarWChar, 25, "Last Name"))
> > > >
> > > > .Parameters.Add(New OleDbParameter("Original_Employee_Number",
> > > > System.Data.OleDb.OleDbType.SmallInt, 0,
> > > > System.Data.ParameterDirection.Input, False, CType(5, Byte),[/color][/color][/color]
CType(0,[color=blue][color=green][color=darkred]
> > > Byte),
> > > > "Employee Number", System.Data.DataRowVersion.Original, Nothing))
> > > >
> > > > .Parameters.Add(New OleDbParameter("Original_First_Name",
> > > > System.Data.OleDb.OleDbType.VarWChar, 25,
> > > > System.Data.ParameterDirection.Input, False, CType(0, Byte),[/color][/color][/color]
CType(0,[color=blue][color=green][color=darkred]
> > > Byte),
> > > > "First Name", System.Data.DataRowVersion.Original, Nothing))
> > > >
> > > > .Parameters.Add(New OleDbParameter("Original_First_Name1",
> > > > System.Data.OleDb.OleDbType.VarWChar, 25,
> > > > System.Data.ParameterDirection.Input, False, CType(0, Byte),[/color][/color][/color]
CType(0,[color=blue][color=green][color=darkred]
> > > Byte),
> > > > "First Name", System.Data.DataRowVersion.Original, Nothing))
> > > >
> > > > .Parameters.Add(New OleDbParameter("Original_Last_Name",
> > > > System.Data.OleDb.OleDbType.VarWChar, 25,
> > > > System.Data.ParameterDirection.Input, False, CType(0, Byte),[/color][/color][/color]
CType(0,[color=blue][color=green][color=darkred]
> > > Byte),
> > > > "Last Name", System.Data.DataRowVersion.Original, Nothing))
> > > >
> > > > .Parameters.Add(New OleDbParameter("Original_Last_Name1",
> > > > System.Data.OleDb.OleDbType.VarWChar, 25,
> > > > System.Data.ParameterDirection.Input, False, CType(0, Byte),[/color][/color][/color]
CType(0,[color=blue][color=green][color=darkred]
> > > Byte),
> > > > "Last Name", System.Data.DataRowVersion.Original, Nothing))
> > > > End With
> > > >
> > > > '***************************************
> > > > 'here is where it ends
> > > > '***************************************
> > > >
> > > > Try
> > > > Adapter1.Update(myData.Tables(0))
> > > > Catch prob As OleDbException
> > > > MsgBox(prob.Message)
> > > > End Try
> > > > end sub
> > > >
> > > >
> > > > I was never able to get the commandbuilder to work. I just think[/color][/color][/color]
this[color=blue][color=green]
> > is[color=darkred]
> > > a
> > > > lot of coding where is previous version doing this in code was much
> > > simpler.
> > > > Have I just been spoiled by vb6 and it's simplicity?
> > > >
> > > >
> > >
> > >[/color]
> >
> >
> >[/color]
>
>[/color]