By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,165 Members | 853 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,165 IT Pros & Developers. It's quick & easy.

update data store via code only

P: n/a
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?
Nov 20 '05 #1
Share this Question
Share on Google+
4 Replies


P: n/a
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" <no**@none.com> wrote in message
news:35***************@bignews6.bellsouth.net...
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?

Nov 20 '05 #2

P: n/a
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" <no**@none.com> wrote in message
news:35***************@bignews6.bellsouth.net...
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?

Nov 20 '05 #3

P: n/a
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" <no**@none.com> wrote in message
news:wS*******************@bignews3.bellsouth.net. ..
William, thanks for the help. Here is the last version of my commandbuilder 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 curious as to the PK field since I never implemented it in my commandbuilder. It is implemented in the data store however. I am thinking this might be part if not all the problem

"William Ryan eMVP" <do********@comcast.nospam.net> wrote in message
news:OK**************@TK2MSFTNGP11.phx.gbl...
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" <no**@none.com> wrote in message
news:35***************@bignews6.bellsouth.net...
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?



Nov 20 '05 #4

P: n/a
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" <so*****@nospam.com> wrote in message
news:OD**************@TK2MSFTNGP10.phx.gbl...
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" <no**@none.com> wrote in message
news:wS*******************@bignews3.bellsouth.net. ..
William, thanks for the help. Here is the last version of my commandbuilder
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

curious
as to the PK field since I never implemented it in my commandbuilder. It is
implemented in the data store however. I am thinking this might be
part if
not all the problem

"William Ryan eMVP" <do********@comcast.nospam.net> wrote in message
news:OK**************@TK2MSFTNGP11.phx.gbl...
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" <no**@none.com> wrote in message
news:35***************@bignews6.bellsouth.net...
> 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?
>
>



Nov 20 '05 #5

This discussion thread is closed

Replies have been disabled for this discussion.