update data store via code only

[William]

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 OleDbDataAdapte r(Command)
myData = New DataSet()
myData = Me.DataGrid1.Da taSource

Dim Cno As OleDbConnection
Cno = New OleDbConnection (Me.strConnecti onString)

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

Adapter1.Update Command = New OleDbCommand()
'best way to get parameters and update string is to create them
'using an oledbdataadapte r 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.Update Command
..CommandText = strUpdate
..Connection = Cno

..Parameters.Ad d(New OleDbParameter( "Employee_Numbe r",
System.Data.Ole Db.OleDbType.Sm allInt, 0,
System.Data.Par ameterDirection .Input, False, CType(5, Byte), CType(0, Byte),
"Employee Number", System.Data.Dat aRowVersion.Cur rent, Nothing))

..Parameters.Ad d(New OleDbParameter( "First_Name ",
System.Data.Ole Db.OleDbType.Va rWChar, 25, "First Name"))

..Parameters.Ad d(New OleDbParameter( "Last_Name" ,
System.Data.Ole Db.OleDbType.Va rWChar, 25, "Last Name"))

..Parameters.Ad d(New OleDbParameter( "Original_Emplo yee_Number",
System.Data.Ole Db.OleDbType.Sm allInt, 0,
System.Data.Par ameterDirection .Input, False, CType(5, Byte), CType(0, Byte),
"Employee Number", System.Data.Dat aRowVersion.Ori ginal, Nothing))

..Parameters.Ad d(New OleDbParameter( "Original_First _Name",
System.Data.Ole Db.OleDbType.Va rWChar, 25,
System.Data.Par ameterDirection .Input, False, CType(0, Byte), CType(0, Byte),
"First Name", System.Data.Dat aRowVersion.Ori ginal, Nothing))

..Parameters.Ad d(New OleDbParameter( "Original_First _Name1",
System.Data.Ole Db.OleDbType.Va rWChar, 25,
System.Data.Par ameterDirection .Input, False, CType(0, Byte), CType(0, Byte),
"First Name", System.Data.Dat aRowVersion.Ori ginal, Nothing))

..Parameters.Ad d(New OleDbParameter( "Original_Last_ Name",
System.Data.Ole Db.OleDbType.Va rWChar, 25,
System.Data.Par ameterDirection .Input, False, CType(0, Byte), CType(0, Byte),
"Last Name", System.Data.Dat aRowVersion.Ori ginal, Nothing))

..Parameters.Ad d(New OleDbParameter( "Original_Last_ Name1",
System.Data.Ole Db.OleDbType.Va rWChar, 25,
System.Data.Par ameterDirection .Input, False, CType(0, Byte), CType(0, Byte),
"Last Name", System.Data.Dat aRowVersion.Ori ginal, Nothing))
End With

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

Try
Adapter1.Update (myData.Tables( 0))
Catch prob As OleDbException
MsgBox(prob.Mes sage)
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?

[William Ryan eMVP]

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/community/newsgroups

"William" <none@none.co m> wrote in message
news:35yIc.274$ cx5.250@bignews 6.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 OleDbDataAdapte r(Command)
> myData = New DataSet()
> myData = Me.DataGrid1.Da taSource
>
> Dim Cno As OleDbConnection
> Cno = New OleDbConnection (Me.strConnecti onString)
>
> '************** *************** *************** ******
> ' here is where my copy, paste, modify code from
> 'adapter control starts
> '************** *************** *************** *****
>
> Adapter1.Update Command = New OleDbCommand()
> 'best way to get parameters and update string is to create them
> 'using an oledbdataadapte r 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.Update Command
> .CommandText = strUpdate
> .Connection = Cno
>
> .Parameters.Add (New OleDbParameter( "Employee_Numbe r",
> System.Data.Ole Db.OleDbType.Sm allInt, 0,
> System.Data.Par ameterDirection .Input, False, CType(5, Byte), CType(0,[/color]
Byte),[color=blue]
> "Employee Number", System.Data.Dat aRowVersion.Cur rent, Nothing))
>
> .Parameters.Add (New OleDbParameter( "First_Name ",
> System.Data.Ole Db.OleDbType.Va rWChar, 25, "First Name"))
>
> .Parameters.Add (New OleDbParameter( "Last_Name" ,
> System.Data.Ole Db.OleDbType.Va rWChar, 25, "Last Name"))
>
> .Parameters.Add (New OleDbParameter( "Original_Emplo yee_Number",
> System.Data.Ole Db.OleDbType.Sm allInt, 0,
> System.Data.Par ameterDirection .Input, False, CType(5, Byte), CType(0,[/color]
Byte),[color=blue]
> "Employee Number", System.Data.Dat aRowVersion.Ori ginal, Nothing))
>
> .Parameters.Add (New OleDbParameter( "Original_First _Name",
> System.Data.Ole Db.OleDbType.Va rWChar, 25,
> System.Data.Par ameterDirection .Input, False, CType(0, Byte), CType(0,[/color]
Byte),[color=blue]
> "First Name", System.Data.Dat aRowVersion.Ori ginal, Nothing))
>
> .Parameters.Add (New OleDbParameter( "Original_First _Name1",
> System.Data.Ole Db.OleDbType.Va rWChar, 25,
> System.Data.Par ameterDirection .Input, False, CType(0, Byte), CType(0,[/color]
Byte),[color=blue]
> "First Name", System.Data.Dat aRowVersion.Ori ginal, Nothing))
>
> .Parameters.Add (New OleDbParameter( "Original_Last_ Name",
> System.Data.Ole Db.OleDbType.Va rWChar, 25,
> System.Data.Par ameterDirection .Input, False, CType(0, Byte), CType(0,[/color]
Byte),[color=blue]
> "Last Name", System.Data.Dat aRowVersion.Ori ginal, Nothing))
>
> .Parameters.Add (New OleDbParameter( "Original_Last_ Name1",
> System.Data.Ole Db.OleDbType.Va rWChar, 25,
> System.Data.Par ameterDirection .Input, False, CType(0, Byte), CType(0,[/color]
Byte),[color=blue]
> "Last Name", System.Data.Dat aRowVersion.Ori ginal, Nothing))
> End With
>
> '************** *************** **********
> 'here is where it ends
> '************** *************** **********
>
> Try
> Adapter1.Update (myData.Tables( 0))
> Catch prob As OleDbException
> MsgBox(prob.Mes sage)
> 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]

[One Handed Man \( OHM - Terry Burns \)]

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.co m> wrote in message
news:35yIc.274$ cx5.250@bignews 6.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 OleDbDataAdapte r(Command)
> myData = New DataSet()
> myData = Me.DataGrid1.Da taSource
>
> Dim Cno As OleDbConnection
> Cno = New OleDbConnection (Me.strConnecti onString)
>
> '************** *************** *************** ******
> ' here is where my copy, paste, modify code from
> 'adapter control starts
> '************** *************** *************** *****
>
> Adapter1.Update Command = New OleDbCommand()
> 'best way to get parameters and update string is to create them
> 'using an oledbdataadapte r 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.Update Command
> .CommandText = strUpdate
> .Connection = Cno
>
> .Parameters.Add (New OleDbParameter( "Employee_Numbe r",
> System.Data.Ole Db.OleDbType.Sm allInt, 0,
> System.Data.Par ameterDirection .Input, False, CType(5, Byte), CType(0,[/color]
Byte),[color=blue]
> "Employee Number", System.Data.Dat aRowVersion.Cur rent, Nothing))
>
> .Parameters.Add (New OleDbParameter( "First_Name ",
> System.Data.Ole Db.OleDbType.Va rWChar, 25, "First Name"))
>
> .Parameters.Add (New OleDbParameter( "Last_Name" ,
> System.Data.Ole Db.OleDbType.Va rWChar, 25, "Last Name"))
>
> .Parameters.Add (New OleDbParameter( "Original_Emplo yee_Number",
> System.Data.Ole Db.OleDbType.Sm allInt, 0,
> System.Data.Par ameterDirection .Input, False, CType(5, Byte), CType(0,[/color]
Byte),[color=blue]
> "Employee Number", System.Data.Dat aRowVersion.Ori ginal, Nothing))
>
> .Parameters.Add (New OleDbParameter( "Original_First _Name",
> System.Data.Ole Db.OleDbType.Va rWChar, 25,
> System.Data.Par ameterDirection .Input, False, CType(0, Byte), CType(0,[/color]
Byte),[color=blue]
> "First Name", System.Data.Dat aRowVersion.Ori ginal, Nothing))
>
> .Parameters.Add (New OleDbParameter( "Original_First _Name1",
> System.Data.Ole Db.OleDbType.Va rWChar, 25,
> System.Data.Par ameterDirection .Input, False, CType(0, Byte), CType(0,[/color]
Byte),[color=blue]
> "First Name", System.Data.Dat aRowVersion.Ori ginal, Nothing))
>
> .Parameters.Add (New OleDbParameter( "Original_Last_ Name",
> System.Data.Ole Db.OleDbType.Va rWChar, 25,
> System.Data.Par ameterDirection .Input, False, CType(0, Byte), CType(0,[/color]
Byte),[color=blue]
> "Last Name", System.Data.Dat aRowVersion.Ori ginal, Nothing))
>
> .Parameters.Add (New OleDbParameter( "Original_Last_ Name1",
> System.Data.Ole Db.OleDbType.Va rWChar, 25,
> System.Data.Par ameterDirection .Input, False, CType(0, Byte), CType(0,[/color]
Byte),[color=blue]
> "Last Name", System.Data.Dat aRowVersion.Ori ginal, Nothing))
> End With
>
> '************** *************** **********
> 'here is where it ends
> '************** *************** **********
>
> Try
> Adapter1.Update (myData.Tables( 0))
> Catch prob As OleDbException
> MsgBox(prob.Mes sage)
> 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]

[Marina]

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.co m> wrote in message
news:wSyIc.4877 8$9t6.10240@big news3.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 btnCommandBuild er_Click(ByVal sender As System.Object, ByVal e
> As System.EventArg s) Handles btnCommandBuild er.Click
>
> Dim cb As OleDbCommandBui lder
> Dim con As New OleDbConnection (Me.strConnecti onString)
> Dim myDat As New DataSet()
>
> 'form level adapter
> Adapter.SelectC ommand = New OleDbCommand("s elect * from employee", con)
>
> cb = New OleDbCommandBui lder(Adapter)
>
> myDat = Me.DataGrid1.Da taSource
>
> Try
> Adapter.Update( myDat.Tables(0) )
>
> Catch prob As Exception
> MsgBox(prob.Mes sage)
>
> 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@com cast.nospam.net > wrote in message
> news:OKjUmaCaEH A.2388@TK2MSFTN GP11.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.co m> wrote in message
> > news:35yIc.274$ cx5.250@bignews 6.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 OleDbDataAdapte r(Command)
> > > myData = New DataSet()
> > > myData = Me.DataGrid1.Da taSource
> > >
> > > Dim Cno As OleDbConnection
> > > Cno = New OleDbConnection (Me.strConnecti onString)
> > >
> > > '************** *************** *************** ******
> > > ' here is where my copy, paste, modify code from
> > > 'adapter control starts
> > > '************** *************** *************** *****
> > >
> > > Adapter1.Update Command = New OleDbCommand()
> > > 'best way to get parameters and update string is to create them
> > > 'using an oledbdataadapte r 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.Update Command
> > > .CommandText = strUpdate
> > > .Connection = Cno
> > >
> > > .Parameters.Add (New OleDbParameter( "Employee_Numbe r",
> > > System.Data.Ole Db.OleDbType.Sm allInt, 0,
> > > System.Data.Par ameterDirection .Input, False, CType(5, Byte), CType(0,[/color]
> > Byte),[color=darkred]
> > > "Employee Number", System.Data.Dat aRowVersion.Cur rent, Nothing))
> > >
> > > .Parameters.Add (New OleDbParameter( "First_Name ",
> > > System.Data.Ole Db.OleDbType.Va rWChar, 25, "First Name"))
> > >
> > > .Parameters.Add (New OleDbParameter( "Last_Name" ,
> > > System.Data.Ole Db.OleDbType.Va rWChar, 25, "Last Name"))
> > >
> > > .Parameters.Add (New OleDbParameter( "Original_Emplo yee_Number",
> > > System.Data.Ole Db.OleDbType.Sm allInt, 0,
> > > System.Data.Par ameterDirection .Input, False, CType(5, Byte), CType(0,[/color]
> > Byte),[color=darkred]
> > > "Employee Number", System.Data.Dat aRowVersion.Ori ginal, Nothing))
> > >
> > > .Parameters.Add (New OleDbParameter( "Original_First _Name",
> > > System.Data.Ole Db.OleDbType.Va rWChar, 25,
> > > System.Data.Par ameterDirection .Input, False, CType(0, Byte), CType(0,[/color]
> > Byte),[color=darkred]
> > > "First Name", System.Data.Dat aRowVersion.Ori ginal, Nothing))
> > >
> > > .Parameters.Add (New OleDbParameter( "Original_First _Name1",
> > > System.Data.Ole Db.OleDbType.Va rWChar, 25,
> > > System.Data.Par ameterDirection .Input, False, CType(0, Byte), CType(0,[/color]
> > Byte),[color=darkred]
> > > "First Name", System.Data.Dat aRowVersion.Ori ginal, Nothing))
> > >
> > > .Parameters.Add (New OleDbParameter( "Original_Last_ Name",
> > > System.Data.Ole Db.OleDbType.Va rWChar, 25,
> > > System.Data.Par ameterDirection .Input, False, CType(0, Byte), CType(0,[/color]
> > Byte),[color=darkred]
> > > "Last Name", System.Data.Dat aRowVersion.Ori ginal, Nothing))
> > >
> > > .Parameters.Add (New OleDbParameter( "Original_Last_ Name1",
> > > System.Data.Ole Db.OleDbType.Va rWChar, 25,
> > > System.Data.Par ameterDirection .Input, False, CType(0, Byte), CType(0,[/color]
> > Byte),[color=darkred]
> > > "Last Name", System.Data.Dat aRowVersion.Ori ginal, Nothing))
> > > End With
> > >
> > > '************** *************** **********
> > > 'here is where it ends
> > > '************** *************** **********
> > >
> > > Try
> > > Adapter1.Update (myData.Tables( 0))
> > > Catch prob As OleDbException
> > > MsgBox(prob.Mes sage)
> > > 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]

[William]

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:ODe5d5CaEH A.4052@TK2MSFTN GP10.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.co m> wrote in message
> news:wSyIc.4877 8$9t6.10240@big news3.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 btnCommandBuild er_Click(ByVal sender As System.Object, ByVal[/color][/color]
e[color=blue][color=green]
> > As System.EventArg s) Handles btnCommandBuild er.Click
> >
> > Dim cb As OleDbCommandBui lder
> > Dim con As New OleDbConnection (Me.strConnecti onString)
> > Dim myDat As New DataSet()
> >
> > 'form level adapter
> > Adapter.SelectC ommand = New OleDbCommand("s elect * from employee", con)
> >
> > cb = New OleDbCommandBui lder(Adapter)
> >
> > myDat = Me.DataGrid1.Da taSource
> >
> > Try
> > Adapter.Update( myDat.Tables(0) )
> >
> > Catch prob As Exception
> > MsgBox(prob.Mes sage)
> >
> > 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@com cast.nospam.net > wrote in message
> > news:OKjUmaCaEH A.2388@TK2MSFTN GP11.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.co m> wrote in message
> > > news:35yIc.274$ cx5.250@bignews 6.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 OleDbDataAdapte r(Command)
> > > > myData = New DataSet()
> > > > myData = Me.DataGrid1.Da taSource
> > > >
> > > > Dim Cno As OleDbConnection
> > > > Cno = New OleDbConnection (Me.strConnecti onString)
> > > >
> > > > '************** *************** *************** ******
> > > > ' here is where my copy, paste, modify code from
> > > > 'adapter control starts
> > > > '************** *************** *************** *****
> > > >
> > > > Adapter1.Update Command = New OleDbCommand()
> > > > 'best way to get parameters and update string is to create them
> > > > 'using an oledbdataadapte r 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.Update Command
> > > > .CommandText = strUpdate
> > > > .Connection = Cno
> > > >
> > > > .Parameters.Add (New OleDbParameter( "Employee_Numbe r",
> > > > System.Data.Ole Db.OleDbType.Sm allInt, 0,
> > > > System.Data.Par ameterDirection .Input, False, CType(5, Byte),[/color][/color][/color]
CType(0,[color=blue][color=green][color=darkred]
> > > Byte),
> > > > "Employee Number", System.Data.Dat aRowVersion.Cur rent, Nothing))
> > > >
> > > > .Parameters.Add (New OleDbParameter( "First_Name ",
> > > > System.Data.Ole Db.OleDbType.Va rWChar, 25, "First Name"))
> > > >
> > > > .Parameters.Add (New OleDbParameter( "Last_Name" ,
> > > > System.Data.Ole Db.OleDbType.Va rWChar, 25, "Last Name"))
> > > >
> > > > .Parameters.Add (New OleDbParameter( "Original_Emplo yee_Number",
> > > > System.Data.Ole Db.OleDbType.Sm allInt, 0,
> > > > System.Data.Par ameterDirection .Input, False, CType(5, Byte),[/color][/color][/color]
CType(0,[color=blue][color=green][color=darkred]
> > > Byte),
> > > > "Employee Number", System.Data.Dat aRowVersion.Ori ginal, Nothing))
> > > >
> > > > .Parameters.Add (New OleDbParameter( "Original_First _Name",
> > > > System.Data.Ole Db.OleDbType.Va rWChar, 25,
> > > > System.Data.Par ameterDirection .Input, False, CType(0, Byte),[/color][/color][/color]
CType(0,[color=blue][color=green][color=darkred]
> > > Byte),
> > > > "First Name", System.Data.Dat aRowVersion.Ori ginal, Nothing))
> > > >
> > > > .Parameters.Add (New OleDbParameter( "Original_First _Name1",
> > > > System.Data.Ole Db.OleDbType.Va rWChar, 25,
> > > > System.Data.Par ameterDirection .Input, False, CType(0, Byte),[/color][/color][/color]
CType(0,[color=blue][color=green][color=darkred]
> > > Byte),
> > > > "First Name", System.Data.Dat aRowVersion.Ori ginal, Nothing))
> > > >
> > > > .Parameters.Add (New OleDbParameter( "Original_Last_ Name",
> > > > System.Data.Ole Db.OleDbType.Va rWChar, 25,
> > > > System.Data.Par ameterDirection .Input, False, CType(0, Byte),[/color][/color][/color]
CType(0,[color=blue][color=green][color=darkred]
> > > Byte),
> > > > "Last Name", System.Data.Dat aRowVersion.Ori ginal, Nothing))
> > > >
> > > > .Parameters.Add (New OleDbParameter( "Original_Last_ Name1",
> > > > System.Data.Ole Db.OleDbType.Va rWChar, 25,
> > > > System.Data.Par ameterDirection .Input, False, CType(0, Byte),[/color][/color][/color]
CType(0,[color=blue][color=green][color=darkred]
> > > Byte),
> > > > "Last Name", System.Data.Dat aRowVersion.Ori ginal, Nothing))
> > > > End With
> > > >
> > > > '************** *************** **********
> > > > 'here is where it ends
> > > > '************** *************** **********
> > > >
> > > > Try
> > > > Adapter1.Update (myData.Tables( 0))
> > > > Catch prob As OleDbException
> > > > MsgBox(prob.Mes sage)
> > > > 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]