Hey,
I got it to work but I would really like it in this update statement if you
would explain what is happening here.
I basically used the database wizards statements but I would like it if you
or someone here would explain in a step by step way what each step of this
process is doing. I need to understand and right now I just feel lucky.
Function CreateUpdateCommand()
Try
cmd = New OleDbCommand("UPDATE cbEmployee SET EmployeeName = ?,
FaxNumber = ?, [Group] = ?, [Password] = " & _
"?, NumericPagerNumber = ?, AlphaPagerNumber = ? WHERE (EmployeeID =
?) AND (Alph" & _
"aPagerNumber = ? OR ? IS NULL AND AlphaPagerNumber IS NULL) AND
(EmployeeName = " & _
"? OR ? IS NULL AND EmployeeName IS NULL) AND (FaxNumber = ? OR ? IS
NULL AND Fax" & _
"Number IS NULL) AND ([Group] = ? OR ? IS NULL AND [Group] IS NULL)
AND (NumericP" & _
"agerNumber = ? OR ? IS NULL AND NumericPagerNumber IS NULL) AND
([Password] = ? " & _
"OR ? IS NULL AND [Password] IS NULL)")
Catch ex As Exception
MsgBox(ex.ToString)
End Try
Try
cmd.Connection = sConn45
cmd.Parameters.Add(New
System.Data.OleDb.OleDbParameter("EmployeeName",
System.Data.OleDb.OleDbType.VarWChar, 20, "EmployeeName"))
cmd.Parameters.Add(New
System.Data.OleDb.OleDbParameter("FaxNumber",
System.Data.OleDb.OleDbType.VarWChar, 14, "FaxNumber"))
cmd.Parameters.Add(New System.Data.OleDb.OleDbParameter("Group",
System.Data.OleDb.OleDbType.VarWChar, 15, "Group"))
cmd.Parameters.Add(New
System.Data.OleDb.OleDbParameter("Password",
System.Data.OleDb.OleDbType.VarWChar, 10, "Password"))
cmd.Parameters.Add(New
System.Data.OleDb.OleDbParameter("NumericPagerNumber",
System.Data.OleDb.OleDbType.VarWChar, 14, "NumericPagerNumber"))
cmd.Parameters.Add(New
System.Data.OleDb.OleDbParameter("AlphaPagerNumber",
System.Data.OleDb.OleDbType.VarWChar, 14, "AlphaPagerNumber"))
cmd.Parameters.Add(New
System.Data.OleDb.OleDbParameter("Original_EmployeeID",
System.Data.OleDb.OleDbType.Integer, 0,
System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0, Byte),
"EmployeeID", System.Data.DataRowVersion.Original, Nothing))
cmd.Parameters.Add(New
System.Data.OleDb.OleDbParameter("Original_AlphaPagerNumber",
System.Data.OleDb.OleDbType.VarWChar, 14,
System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0, Byte),
"AlphaPagerNumber", System.Data.DataRowVersion.Original, Nothing))
cmd.Parameters.Add(New
System.Data.OleDb.OleDbParameter("Original_AlphaPagerNumber1",
System.Data.OleDb.OleDbType.VarWChar, 14,
System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0, Byte),
"AlphaPagerNumber", System.Data.DataRowVersion.Original, Nothing))
cmd.Parameters.Add(New
System.Data.OleDb.OleDbParameter("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))
cmd.Parameters.Add(New
System.Data.OleDb.OleDbParameter("Original_EmployeeName1",
System.Data.OleDb.OleDbType.VarWChar, 20,
System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0, Byte),
"EmployeeName", System.Data.DataRowVersion.Original, Nothing))
cmd.Parameters.Add(New
System.Data.OleDb.OleDbParameter("Original_FaxNumber",
System.Data.OleDb.OleDbType.VarWChar, 14,
System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0, Byte),
"FaxNumber", System.Data.DataRowVersion.Original, Nothing))
cmd.Parameters.Add(New
System.Data.OleDb.OleDbParameter("Original_FaxNumber1",
System.Data.OleDb.OleDbType.VarWChar, 14,
System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0, Byte),
"FaxNumber", System.Data.DataRowVersion.Original, Nothing))
cmd.Parameters.Add(New
System.Data.OleDb.OleDbParameter("Original_Group",
System.Data.OleDb.OleDbType.VarWChar, 15,
System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0, Byte),
"Group", System.Data.DataRowVersion.Original, Nothing))
cmd.Parameters.Add(New
System.Data.OleDb.OleDbParameter("Original_Group1",
System.Data.OleDb.OleDbType.VarWChar, 15,
System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0, Byte),
"Group", System.Data.DataRowVersion.Original, Nothing))
cmd.Parameters.Add(New
System.Data.OleDb.OleDbParameter("Original_NumericPagerNumber",
System.Data.OleDb.OleDbType.VarWChar, 14,
System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0, Byte),
"NumericPagerNumber", System.Data.DataRowVersion.Original, Nothing))
cmd.Parameters.Add(New
System.Data.OleDb.OleDbParameter("Original_NumericPagerNumber1",
System.Data.OleDb.OleDbType.VarWChar, 14,
System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0, Byte),
"NumericPagerNumber", System.Data.DataRowVersion.Original, Nothing))
cmd.Parameters.Add(New
System.Data.OleDb.OleDbParameter("Original_Password",
System.Data.OleDb.OleDbType.VarWChar, 10,
System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0, Byte),
"Password", System.Data.DataRowVersion.Original, Nothing))
cmd.Parameters.Add(New
System.Data.OleDb.OleDbParameter("Original_Password1",
System.Data.OleDb.OleDbType.VarWChar, 10,
System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0, Byte),
"Password", System.Data.DataRowVersion.Original, Nothing))
'cmd.Parameters.Add("@AlphaPagerNumber", OleDbType.VarChar, 50,
"AlphaPagerNumber")
'cmd.Parameters.Add("@EmployeeName", OleDbType.VarChar, 50,
"EmployeeName")
'cmd.Parameters.Add("@LoggedIn", OleDbType.Boolean, 250,
"LoggedIn")
'cmd.Parameters.Add("@FaxNumber", OleDbType.VarChar, 50,
"FaxNumber")
'cmd.Parameters.Add("@Group", OleDbType.VarChar, 50, "Group")
'cmd.Parameters.Add("@NumericPagerNumber", OleDbType.VarChar,
50, "NumericPagerNumber")
'cmd.Parameters.Add("@Password", OleDbType.VarChar, 50,
"Password")
Catch ex As Exception
MsgBox(ex.ToString)
End Try
OleDbDataAdapter1.UpdateCommand = cmd
Try
Return cmd
Catch ex As Exception
MsgBox(ex.ToString)
End Try
End Function
Steve S said:
att eh very end of your SQL...... you have......
LoggedIn = @LoggedIn)where EmployeeName =" & TextBox1.Text,
Since this portion is stuff that you wrote... and the SQL doesn't persay
deal with a parameter for the textbox1.text... that specific portion should
be offset with tics..... as it needs to be denoted as a string.....
and remember to finish off that portion with tics... to close out the
string portion to the SQL...
as for the rest of the SQL.... you must be getting that from a datatable or
row somewhere.......
If your sure of the other portions.. thry this...
New OleDbCommand("UPDATE cbEmployee set AlphaPagerNumber
@AlphaPagerNumber, EmployeeName = @EmployeeName, FaxNumber = @FaxNumber,
[Group] = @Group, NumericPagerNumber = @NumbericPagerNumber, Password =
@Password, LoggedIn = @LoggedIn where EmployeeName = '" & TextBox1.Text &
"'",
sConn1)
AND...... thinking about the SQL.... even that probably will not
work.... as your looking for a NAME value.. that is equal to what you just
set it to....... so it won't find the record.... if you have changed the
name.....
HTH....... ALOHA...
scorpion53061 said:
Okay here is the syntax error generated in ex maybe this will help. I took
out the parenthesis like you said but no dice. Thanks for sticking with me
on this.
System.Data.OleDb.OleDbException: Syntax error in UPDATE statement.
at System.Data.Common.DbDataAdapter.Update(DataRow[] dataRows,
DataTableMapping tableMapping)
at System.Data.Common.DbDataAdapter.Update(DataTable dataTable)
at Neumann_Plumbing.fmadminemp.Button3_Click(Object sender, EventArgs e)
in C:\Documents and Settings\Administrator\My Documents\Visual Studio
Projects\Neumann Plumbing\fmadminemp.vb:line 476
Martin Robins said:
Take out the close bracket after @loggedIn and replace it with a space.
I changed it and now I am getting this error:
Syntax Error in Update Statement.
Any idea what I am doing wrong?
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
.Rows(0)("LoggedIn") = False
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
End With
Dim cmd As OleDbCommand
'Try
cmd = New OleDbCommand("UPDATE cbEmployee set AlphaPagerNumber
=
@AlphaPagerNumber, EmployeeName = @EmployeeName, FaxNumber = @FaxNumber,
[Group] = @Group, NumericPagerNumber = @NumbericPagerNumber,
Password
=
@Password, LoggedIn = @LoggedIn)where EmployeeName =" & TextBox1.Text,
sConn1)
cmd.Parameters.Add("@AlphaPagerNumber",
OleDbType.VarChar,
50,
"AlphaPagerNumber")
cmd.Parameters.Add("@EmployeeName", OleDbType.VarChar, 50,
"EmployeeName")
cmd.Parameters.Add("@LoggedIn", OleDbType.Boolean, 250,
"LoggedIn")
cmd.Parameters.Add("@FaxNumber", OleDbType.VarChar, 50,
"FaxNumber")
cmd.Parameters.Add("@Group", OleDbType.VarChar, 50, "Group")
cmd.Parameters.Add("@NumericPagerNumber", OleDbType.VarChar,
50,
"NumericPagerNumber")
cmd.Parameters.Add("@Password", OleDbType.VarChar, 50,
"Password")
OleDbDataAdapter1.UpdateCommand = cmd
Try
OleDbDataAdapter1.Update(Dslogin1.cbEmployee)
Catch ex As Exception
MsgBox(ex.ToString)
End Try
Your SQL is incorrect, you are mixing insert and update syntax.
The string should be ...
update cbEmployee set AlphaPagerNumber=@AlphaPagerNumber,
EmployeeName=@EmployeeName, ...
where EmployeeName='Martin'
Please let me know what I am doing wrong........
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
.Rows(0)("LoggedIn") = False
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
'Dslogin1.cbEmployee.
'.Rows(0).AcceptChanges()
End With
Dim cmd As OleDbCommand
'Try
cmd = New OleDbCommand("UPDATE cbEmployee(AlphaPagerNumber,
EmployeeName,
FaxNumber, [Group], NumericPagerNumber, [Password]) where employeename
=
'"
& TextBox1.Text & "'" & "VALUES (@AlphaPagerNumber, @EmployeeName,
@FaxNumber, @Group, @NumericPagerNumber, @Password)", sConn1)
cmd.Parameters.Add("@AlphaPagerNumber", OleDbType.VarChar, 50,
"AlphaPagerNumber")
cmd.Parameters.Add("@EmployeeName", OleDbType.VarChar, 50,
"EmployeeName")
'cmd.Parameters.Add("@LoggedIn", OleDbType.Boolean, 250, "LoggedIn")
cmd.Parameters.Add("@FaxNumber", OleDbType.VarChar, 50, "FaxNumber")
cmd.Parameters.Add("@Group", OleDbType.VarChar, 50, "Group")
cmd.Parameters.Add("@NumericPagerNumber", OleDbType.VarChar, 50,
"NumericPagerNumber")
cmd.Parameters.Add("@Password", OleDbType.VarChar, 50, "Password")
OleDbDataAdapter1.UpdateCommand = cmd
Try
OleDbDataAdapter1.Update(Dslogin1.cbEmployee)
Catch ex As Exception
MsgBox(ex.ToString)
End Try
OleDbDataAdapter1.AcceptChangesDuringFill = True
sConn1.Close()
