UPDATE command is driving me crazy

  • Thread starter Thread starter scorpion53061
  • Start date Start date
S

scorpion53061

I can delete and add but why is this so hard?

Please tell me what I am doing wrong.........

Dim sConn1 As New OleDb.OleDbConnection
sConn1.ConnectionString = "Jet OLEDB:Global Partial Bulk Ops=2;Jet
OLEDB:Registry Path=;Jet OLEDB:Database L" & _
"ocking Mode=1;Jet OLEDB:Database Password=;Data Source=" &
databasepath & _
";Password=;Jet OLEDB:Engine T" & _
"ype=5;Jet OLEDB:Global Bulk
Transactions=1;Provider=""Microsoft.Jet.OLEDB.4.0"";Je" & _
"t OLEDB:System database=;Jet OLEDB:SFP=False;Extended
Properties=;Mode=Share Den" & _
"y None;Jet OLEDB:New Database Password=;Jet OLEDB:Create System
Database=False;J" & _
"et OLEDB:Don't Copy Locale on Compact=False;Jet OLEDB:Compact
Without Replica Re" & _
"pair=False;User ID=Admin;Jet OLEDB:Encrypt Database=False"

If TextBox1.Text = "" Or ComboBox1.Text = "" Or TextBox2.Text = ""
Then
MsgBox("All required fields do not have entries.",
MsgBoxStyle.Critical, "Neumann Plumbing and Heating Software")
Exit Sub
End If

Dslogin1.Clear()
Dslogin1.AcceptChanges()

sql2 = "select AlphaPagerNumber, EmployeeID, EmployeeName,
FaxNumber, [Group], LoggedIn, NumericPagerNumber, [Password] FROM cbEmployee
where employeename = '" & TextBox1.Text & "'"
OleDbDataAdapter1 = New OleDb.OleDbDataAdapter(sql2, sConn1)
OleDbDataAdapter1.Fill(Dslogin1.cbEmployee)
Dslogin1.AcceptChanges()

Dim itemcheck As Integer
Dim foundit As Boolean

For itemcheck = 0 To Dslogin1.Tables("cbEmployee").Rows.Count - 1
If TextBox1.Text =
Dslogin1.Tables("cbEmployee").Rows(itemcheck).Item("EMPLOYEENAME") Then
foundit = False
Exit For
End If
If TextBox1.Text <>
Dslogin1.Tables("cbEmployee").Rows(itemcheck).Item("EMPLOYEENAME") Then
foundit = True
End If
'Exit Sub
Next
If foundit = False Then

With Dslogin1.Tables("cbEmployee")
' Modify the first record (just append 3 asterisks to Name
field)

.Rows(0)("Employeeid") = .Rows(0)("Employeeid").ToString

.Rows(0)("EmployeeName") = TextBox1.Text.ToString

.Rows(0)("AlphaPagerNumber") = 0

If TextBox3.Text = "" Then
.Rows(0)("FaxNumber") = 0
Else
.Rows(0)("FaxNumber") = TextBox3.Text.ToString
End If

.Rows(0)("Group") = ComboBox1.Text.ToString

If TextBox5.Text = "" Then
.Rows(0)("NumericPagerNumber") = 0
Else
.Rows(0)("NumericPagerNumber") = TextBox5.Text.ToString
End If

.Rows(0)("Password") = TextBox2.Text

.Rows(0).AcceptChanges()
End With
Dslogin1.AcceptChanges()
CreateUpdateCommand()


Dslogin1.AcceptChanges()
OleDbDataAdapter1.Update(Dslogin1.cbEmployee)
OleDbDataAdapter1.AcceptChangesDuringFill = True

MsgBox(TextBox1.Text & " has had their record modified.",
MsgBoxStyle.Information, "Neumann Plumbing and Heating Software - User
Modified")
closeme = True
'Me.Hide()
Exit Sub
End If

If foundit = True Then
'rowdeletion = True
MsgBox("This user does not exist in the database.",
MsgBoxStyle.Critical, "Neumann Plumbing and Heating Software - User Does Not
Exist")
Exit Sub
End If

End Sub
Private Function CreateUpdateCommand() As OleDbCommand
Dim sConn45 As New OleDb.OleDbConnection
sConn45.ConnectionString = "Jet OLEDB:Global Partial Bulk Ops=2;Jet
OLEDB:Registry Path=;Jet OLEDB:Database L" & _
"ocking Mode=1;Jet OLEDB:Database Password=;Data Source=" &
databasepath & _
";Password=;Jet OLEDB:Engine T" & _
"ype=5;Jet OLEDB:Global Bulk
Transactions=1;Provider=""Microsoft.Jet.OLEDB.4.0"";Je" & _
"t OLEDB:System database=;Jet OLEDB:SFP=False;Extended
Properties=;Mode=Share Den" & _
"y None;Jet OLEDB:New Database Password=;Jet OLEDB:Create System
Database=False;J" & _
"et OLEDB:Don't Copy Locale on Compact=False;Jet OLEDB:Compact
Without Replica Re" & _
"pair=False;User ID=Admin;Jet OLEDB:Encrypt Database=False"
Dim strsql As String
strsql = "UPDATE cbEmployee SET AlphaPagerNumber = ?, EmployeeName =
?, FaxNumber = ?, [Gro" & _
"up] = ?, LoggedIn = ?, NumericPagerNumber = ?, [Password] = ? WHERE
(EmployeeN" & _
"ame = '" & TextBox1.Text & "') "

Dim cmd As New OleDbCommand(strsql, sConn45)
Dim pc As OleDbParameterCollection = cmd.Parameters

pc.Add("AlphaPagerNumber", System.Data.OleDb.OleDbType.VarWChar, 14,
"AlphaPagerNumber")
pc.Add("EmployeeName", System.Data.OleDb.OleDbType.VarWChar, 20,
"EmployeeName")
pc.Add("FaxNumber", System.Data.OleDb.OleDbType.VarWChar, 14,
"FaxNumber")
pc.Add("Group", System.Data.OleDb.OleDbType.VarWChar, 15, "Group")
'Me.OleDbUpdateCommand1.Parameters.Add(New
System.Data.OleDb.OleDbParameter("LoggedIn",
System.Data.OleDb.OleDbType.Boolean, 2, "LoggedIn"))
pc.Add("NumericPagerNumber", System.Data.OleDb.OleDbType.VarWChar,
14, "NumericPagerNumber")
pc.Add("Password", System.Data.OleDb.OleDbType.VarWChar, 10,
"Password")
'pc.Add("Original_EmployeeName",
System.Data.OleDb.OleDbType.VarWChar, 20,
System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0, Byte),
"EmployeeName", System.Data.DataRowVersion.Original, Nothing))
pc.Add("Original_EmployeeName",
System.Data.OleDb.OleDbType.VarWChar, 20, "EmployeeName")
pc.Add("Original_AlphaPagerNumber",
System.Data.OleDb.OleDbType.VarWChar, 14, "AlphaPagerNumber")
pc.Add("Original_AlphaPagerNumber1",
System.Data.OleDb.OleDbType.VarWChar, 14, "AlphaPagerNumber")
pc.Add("Original_FaxNumber", System.Data.OleDb.OleDbType.VarWChar,
14, "FaxNumber")
pc.Add("Original_FaxNumber1", System.Data.OleDb.OleDbType.VarWChar,
14, "FaxNumber")
pc.Add("Original_Group", System.Data.OleDb.OleDbType.VarWChar, 15,
"Group")
pc.Add("Original_Group1", System.Data.OleDb.OleDbType.VarWChar, 15,
"Group")
'Me.OleDbUpdateCommand1.Parameters.Add(New
System.Data.OleDb.OleDbParameter("Original_LoggedIn",
System.Data.OleDb.OleDbType.Boolean, 2,
System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0, Byte),
"LoggedIn", System.Data.DataRowVersion.Original, Nothing))
pc.Add("Original_NumericPagerNumber",
System.Data.OleDb.OleDbType.VarWChar, 14, "NumericPagerNumber")
pc.Add("Original_NumericPagerNumber1",
System.Data.OleDb.OleDbType.VarWChar, 14, "NumericPagerNumber")
pc.Add("Original_Password", System.Data.OleDb.OleDbType.VarWChar,
10, "Password")
pc.Add("Original_Password1", System.Data.OleDb.OleDbType.VarWChar,
10, "Password")
'cmd.Transaction.Commit()
Return cmd
End Function
 
Apparently, you don't know what AcceptChanges does--it clears the change
state so all changes made are ignored when you call the Update method. You
should never need to call AcceptChanges unless you are handling the physical
UPDATE yourself and not using the Update method.

I also don't see why this entire operation can't be done on the server with
a correlated UPDATE statement.

UPDATE XXX SET XXX WHERE <some condition> = True

hth

--
____________________________________
Bill Vaughn
MVP, hRD
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________

scorpion53061 said:
I can delete and add but why is this so hard?

Please tell me what I am doing wrong.........

Dim sConn1 As New OleDb.OleDbConnection
sConn1.ConnectionString = "Jet OLEDB:Global Partial Bulk Ops=2;Jet
OLEDB:Registry Path=;Jet OLEDB:Database L" & _
"ocking Mode=1;Jet OLEDB:Database Password=;Data Source=" &
databasepath & _
";Password=;Jet OLEDB:Engine T" & _
"ype=5;Jet OLEDB:Global Bulk
Transactions=1;Provider=""Microsoft.Jet.OLEDB.4.0"";Je" & _
"t OLEDB:System database=;Jet OLEDB:SFP=False;Extended
Properties=;Mode=Share Den" & _
"y None;Jet OLEDB:New Database Password=;Jet OLEDB:Create System
Database=False;J" & _
"et OLEDB:Don't Copy Locale on Compact=False;Jet OLEDB:Compact
Without Replica Re" & _
"pair=False;User ID=Admin;Jet OLEDB:Encrypt Database=False"

If TextBox1.Text = "" Or ComboBox1.Text = "" Or TextBox2.Text = ""
Then
MsgBox("All required fields do not have entries.",
MsgBoxStyle.Critical, "Neumann Plumbing and Heating Software")
Exit Sub
End If

Dslogin1.Clear()
Dslogin1.AcceptChanges()

sql2 = "select AlphaPagerNumber, EmployeeID, EmployeeName,
FaxNumber, [Group], LoggedIn, NumericPagerNumber, [Password] FROM cbEmployee
where employeename = '" & TextBox1.Text & "'"
OleDbDataAdapter1 = New OleDb.OleDbDataAdapter(sql2, sConn1)
OleDbDataAdapter1.Fill(Dslogin1.cbEmployee)
Dslogin1.AcceptChanges()

Dim itemcheck As Integer
Dim foundit As Boolean

For itemcheck = 0 To Dslogin1.Tables("cbEmployee").Rows.Count - 1
If TextBox1.Text =
Dslogin1.Tables("cbEmployee").Rows(itemcheck).Item("EMPLOYEENAME") Then
foundit = False
Exit For
End If
If TextBox1.Text <>
Dslogin1.Tables("cbEmployee").Rows(itemcheck).Item("EMPLOYEENAME") Then
foundit = True
End If
'Exit Sub
Next
If foundit = False Then

With Dslogin1.Tables("cbEmployee")
' Modify the first record (just append 3 asterisks to Name
field)

.Rows(0)("Employeeid") = .Rows(0)("Employeeid").ToString

.Rows(0)("EmployeeName") = TextBox1.Text.ToString

.Rows(0)("AlphaPagerNumber") = 0

If TextBox3.Text = "" Then
.Rows(0)("FaxNumber") = 0
Else
.Rows(0)("FaxNumber") = TextBox3.Text.ToString
End If

.Rows(0)("Group") = ComboBox1.Text.ToString

If TextBox5.Text = "" Then
.Rows(0)("NumericPagerNumber") = 0
Else
.Rows(0)("NumericPagerNumber") = TextBox5.Text.ToString
End If

.Rows(0)("Password") = TextBox2.Text

.Rows(0).AcceptChanges()
End With
Dslogin1.AcceptChanges()
CreateUpdateCommand()


Dslogin1.AcceptChanges()
OleDbDataAdapter1.Update(Dslogin1.cbEmployee)
OleDbDataAdapter1.AcceptChangesDuringFill = True

MsgBox(TextBox1.Text & " has had their record modified.",
MsgBoxStyle.Information, "Neumann Plumbing and Heating Software - User
Modified")
closeme = True
'Me.Hide()
Exit Sub
End If

If foundit = True Then
'rowdeletion = True
MsgBox("This user does not exist in the database.",
MsgBoxStyle.Critical, "Neumann Plumbing and Heating Software - User Does Not
Exist")
Exit Sub
End If

End Sub
Private Function CreateUpdateCommand() As OleDbCommand
Dim sConn45 As New OleDb.OleDbConnection
sConn45.ConnectionString = "Jet OLEDB:Global Partial Bulk Ops=2;Jet
OLEDB:Registry Path=;Jet OLEDB:Database L" & _
"ocking Mode=1;Jet OLEDB:Database Password=;Data Source=" &
databasepath & _
";Password=;Jet OLEDB:Engine T" & _
"ype=5;Jet OLEDB:Global Bulk
Transactions=1;Provider=""Microsoft.Jet.OLEDB.4.0"";Je" & _
"t OLEDB:System database=;Jet OLEDB:SFP=False;Extended
Properties=;Mode=Share Den" & _
"y None;Jet OLEDB:New Database Password=;Jet OLEDB:Create System
Database=False;J" & _
"et OLEDB:Don't Copy Locale on Compact=False;Jet OLEDB:Compact
Without Replica Re" & _
"pair=False;User ID=Admin;Jet OLEDB:Encrypt Database=False"
Dim strsql As String
strsql = "UPDATE cbEmployee SET AlphaPagerNumber = ?, EmployeeName =
?, FaxNumber = ?, [Gro" & _
"up] = ?, LoggedIn = ?, NumericPagerNumber = ?, [Password] = ? WHERE
(EmployeeN" & _
"ame = '" & TextBox1.Text & "') "

Dim cmd As New OleDbCommand(strsql, sConn45)
Dim pc As OleDbParameterCollection = cmd.Parameters

pc.Add("AlphaPagerNumber", System.Data.OleDb.OleDbType.VarWChar, 14,
"AlphaPagerNumber")
pc.Add("EmployeeName", System.Data.OleDb.OleDbType.VarWChar, 20,
"EmployeeName")
pc.Add("FaxNumber", System.Data.OleDb.OleDbType.VarWChar, 14,
"FaxNumber")
pc.Add("Group", System.Data.OleDb.OleDbType.VarWChar, 15, "Group")
'Me.OleDbUpdateCommand1.Parameters.Add(New
System.Data.OleDb.OleDbParameter("LoggedIn",
System.Data.OleDb.OleDbType.Boolean, 2, "LoggedIn"))
pc.Add("NumericPagerNumber", System.Data.OleDb.OleDbType.VarWChar,
14, "NumericPagerNumber")
pc.Add("Password", System.Data.OleDb.OleDbType.VarWChar, 10,
"Password")
'pc.Add("Original_EmployeeName",
System.Data.OleDb.OleDbType.VarWChar, 20,
System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0, Byte),
"EmployeeName", System.Data.DataRowVersion.Original, Nothing))
pc.Add("Original_EmployeeName",
System.Data.OleDb.OleDbType.VarWChar, 20, "EmployeeName")
pc.Add("Original_AlphaPagerNumber",
System.Data.OleDb.OleDbType.VarWChar, 14, "AlphaPagerNumber")
pc.Add("Original_AlphaPagerNumber1",
System.Data.OleDb.OleDbType.VarWChar, 14, "AlphaPagerNumber")
pc.Add("Original_FaxNumber", System.Data.OleDb.OleDbType.VarWChar,
14, "FaxNumber")
pc.Add("Original_FaxNumber1", System.Data.OleDb.OleDbType.VarWChar,
14, "FaxNumber")
pc.Add("Original_Group", System.Data.OleDb.OleDbType.VarWChar, 15,
"Group")
pc.Add("Original_Group1", System.Data.OleDb.OleDbType.VarWChar, 15,
"Group")
'Me.OleDbUpdateCommand1.Parameters.Add(New
System.Data.OleDb.OleDbParameter("Original_LoggedIn",
System.Data.OleDb.OleDbType.Boolean, 2,
System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0, Byte),
"LoggedIn", System.Data.DataRowVersion.Original, Nothing))
pc.Add("Original_NumericPagerNumber",
System.Data.OleDb.OleDbType.VarWChar, 14, "NumericPagerNumber")
pc.Add("Original_NumericPagerNumber1",
System.Data.OleDb.OleDbType.VarWChar, 14, "NumericPagerNumber")
pc.Add("Original_Password", System.Data.OleDb.OleDbType.VarWChar,
10, "Password")
pc.Add("Original_Password1", System.Data.OleDb.OleDbType.VarWChar,
10, "Password")
'cmd.Transaction.Commit()
Return cmd
End Function
Click to expand...
 
Back
Top