update data store via code only

W

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 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?
 
W

William Ryan eMVP

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 said:
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?
Click to expand...
 
O

One Handed Man \( OHM - Terry Burns \)

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 said:
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?
Click to expand...
 
W

William

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 said:
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 said:
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
Click to expand...
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?
Click to expand...
Click to expand...
 
M

Marina

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 said:
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 said:
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 said:
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
Click to expand...
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?
Click to expand...
Click to expand...
Click to expand...
 
W

William

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 said:
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 said:
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.
Click to expand...
It
is
implemented in the data store however. I am thinking this might be
Click to expand...
part
if
not all the problem



William Ryan eMVP said:
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
Click to expand...
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
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
Click to expand...
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?
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Database wont update but getting no errors.... 7
Cant update my database. 5
SqlDataAdapter1.update for datagrid1 does not work 1
What are OleDbParameter names with 'Original_' prefix? 5
sqladapter caching data? 5
Concurrency Troubles using Access as Database '?' Appear in SQL 1
DBConcurrencyException when using OleDBTypes DBDate and Double 1
VB.NET DataAdapter.Update 2

Top