|
-
Oct 6th, 2011, 05:39 AM
#1
Thread Starter
Addicted Member
Help Resolving Syntax Error
Hey All,
Need some help resolving a Syntax error as title says, been a long day pretty sure it's a simple fix but i can't find the error.
Code:
If bStudentNumberExists Then
sSaveSql = "update CUSTOMER set " _
& "Surname = '" & oThisFormA.txtCusSurname.Text.Trim & "'," _
& "Given = '" & oThisFormA.txtCustGiven.Text.Trim & "'," _
& "DateOfBirth = '" & oThisFormA.txtCustDOB.Text.Trim & "', " _
& "Sex = '" & oThisFormA.txtCustSex.Text.Trim & "'," _
& "Phone = '" & oThisFormA.txtCustPhone.Text.Trim & "'," _
& "Address = '" & oThisFormA.txtCustAddress.Text.Trim & "'," _
& "Suburb = '" & oThisFormA.txtCustSuburb.Text.Trim & "'," _
& "State = '" & oThisFormA.txtCustState.Text.Trim & "'," _
& "PostCode = '" & oThisFormA.txtCustPostCode.Text.Trim & "'," _
& "where ID ='" & oThisFormA.txtCustID.Text.Trim & "';"
End If
-
Oct 6th, 2011, 06:05 AM
#2
Re: Help Resolving Syntax Error
One of the best way to avoid syntax errors is to use a parameterized command. Below is an example doing an INSERT You can do the same for SELECT, UPDATE, DELETE etc.
Code:
Using cmd As OleDbCommand = New OleDbCommand With {.Connection = cn}
cn.Open()
Try
cmd.CommandText = _
<SQL>
INSERT INTO Customers
(
Identifier,
FirstName,
LastName
)
VALUES (@Identifier,@FirstName, @LastName)
</SQL>.Value
cmd.Parameters.AddWithValue("@Identifier", CurrentID)
cmd.Parameters.AddWithValue("@FirstName", txtFirstName.Text)
cmd.Parameters.AddWithValue("@LastName", txtLastName.Text)
cmd.ExecuteNonQuery()
Catch OleDbExceptionErr As OleDbException
MessageBox.Show(OleDbExceptionErr.Message)
End Try
End Using
-
Oct 6th, 2011, 06:13 AM
#3
Thread Starter
Addicted Member
Re: Help Resolving Syntax Error
 Originally Posted by kevininstructor
One of the best way to avoid syntax errors is to use a parameterized command. Below is an example doing an INSERT You can do the same for SELECT, UPDATE, DELETE etc.
Code:
Using cmd As OleDbCommand = New OleDbCommand With {.Connection = cn}
cn.Open()
Try
cmd.CommandText = _
<SQL>
INSERT INTO Customers
(
Identifier,
FirstName,
LastName
)
VALUES (@Identifier,@FirstName, @LastName)
</SQL>.Value
cmd.Parameters.AddWithValue("@Identifier", CurrentID)
cmd.Parameters.AddWithValue("@FirstName", txtFirstName.Text)
cmd.Parameters.AddWithValue("@LastName", txtLastName.Text)
cmd.ExecuteNonQuery()
Catch OleDbExceptionErr As OleDbException
MessageBox.Show(OleDbExceptionErr.Message)
End Try
End Using
I dont Understand how the cn Stuff works mate..
Currently i Have:
Code:
Dim oConn As OleDbConnection 'To reference a Connection obj.
Dim oCmd_Select As OleDbCommand 'To Instantiate a Command obj used to execute the Select SQL.
Dim oDataReader As OleDbDataReader 'To instantiate a DataReader obj.
Dim oCmd_Update As OleDbCommand 'To Instantiate a Command obj used to execute the Insert/Update SQL.
'sErrorMessage = ""
oConn = Nothing
oCmd_Select = Nothing
oDataReader = Nothing
oCmd_Update = Nothing
' --- create a database command object and set the SQL statement
' --- it will execute, and the database connection object associated to it
sConnection = "Provider=Microsoft.Jet.OLEDB.4.0;"
sConnection = sConnection & "User ID=Admin;"
sConnection = sConnection & "Data Source="
sConnection = sConnection & Application.StartupPath & "\" & sDBNameA
oConn = New OleDbConnection(sConnection)
oConn.Open()
oCmd_Select = oConn.CreateCommand()
sSQL = "select ID from CUSTOMER where ID= " & sCusID & ""
oCmd_Select.CommandText = sSQL
' --- execute SQL command and place results into a datareader object
oDataReader = oCmd_Select.ExecuteReader()
bStudentNumberExists = (oDataReader.Read() = True)
'MessageBox.Show("bStudentNumberExists=" & bStudentNumberExists)
' --- Build Insert or Update Sql depending if record retrieved with select query is found
If bStudentNumberExists Then
sSaveSql = "update CUSTOMER set " _
& "Surname = '" & oThisFormA.txtCusSurname.Text.Trim & "'," _
& "Given = '" & oThisFormA.txtCustGiven.Text.Trim & "'," _
& "DateOfBirth = '" & oThisFormA.txtCustDOB.Text.Trim & "', " _
& "Sex = '" & oThisFormA.txtCustSex.Text.Trim & "'," _
& "Phone = '" & oThisFormA.txtCustPhone.Text.Trim & "'," _
& "Address = '" & oThisFormA.txtCustAddress.Text.Trim & "'," _
& "Suburb = '" & oThisFormA.txtCustSuburb.Text.Trim & "'," _
& "State = '" & oThisFormA.txtCustState.Text.Trim & "'," _
& "PostCode = '" & oThisFormA.txtCustPostCode.Text.Trim & "'," _
& "where ID ='" & oThisFormA.txtCustID.Text.Trim & "';"
End If
-
Oct 6th, 2011, 07:04 AM
#4
Re: Help Resolving Syntax Error
cn is your oConn
This is known as cn is being set as the connection to the command
Code:
Using cmd As OleDbCommand = New OleDbCommand With {.Connection = cn}
-
Oct 6th, 2011, 07:07 AM
#5
Re: Help Resolving Syntax Error
only because I'm feeling generous today....
Code:
'sSaveSql = "update CUSTOMER set " _
' & "Surname = '" & oThisFormA.txtCusSurname.Text.Trim & "'," _
' & "Given = '" & oThisFormA.txtCustGiven.Text.Trim & "'," _
' & "DateOfBirth = '" & oThisFormA.txtCustDOB.Text.Trim & "', " _
' & "Sex = '" & oThisFormA.txtCustSex.Text.Trim & "'," _
' & "Phone = '" & oThisFormA.txtCustPhone.Text.Trim & "'," _
' & "Address = '" & oThisFormA.txtCustAddress.Text.Trim & "'," _
' & "Suburb = '" & oThisFormA.txtCustSuburb.Text.Trim & "'," _
' & "State = '" & oThisFormA.txtCustState.Text.Trim & "'," _
' & "PostCode = '" & oThisFormA.txtCustPostCode.Text.Trim & "'," _
' & "where ID ='" & oThisFormA.txtCustID.Text.Trim & "';"
sSaveSql = "update CUSTOMER set " _
& "Surname = ?," _
& "Given = ?," _
& "DateOfBirth = ?, " _
& "Sex = ?," _
& "Phone = ?," _
& "Address = ?," _
& "Suburb = ?," _
& "State = ?," _
& "PostCode = ?," _
& "where ID = ?;"
cmd.Parameters.AddWithValue("@Surname", oThisFormA.txtCusSurname.Text.Trim)
cmd.Parameters.AddWithValue("@Given", oThisFormA.txtCustGiven.Text.Trim)
cmd.Parameters.AddWithValue("@DateOfBirth", CDate(oThisFormA.txtCustDOB.Text.Trim))
cmd.Parameters.AddWithValue("@Sex", oThisFormA.txtCustSex.Text.Trim)
cmd.Parameters.AddWithValue("@Phone",oThisFormA.txtCustPhone.Text.Trim)
cmd.Parameters.AddWithValue("@Address", oThisFormA.txtCustAddress.Text.Trim)
cmd.Parameters.AddWithValue("@Suburb", oThisFormA.txtCustSuburb.Text.Trim)
cmd.Parameters.AddWithValue("@State", oThisFormA.txtCustState.Text.Trim)
cmd.Parameters.AddWithValue("@PostCode", oThisFormA.txtCustPostCode.Text.Trim)
cmd.Parameters.AddWithValue("@ID", CInt(oThisFormA.txtCustID.Text.Trim))
I made some assumptions: 1) Your DOB field is a date type. 2) Your ID field is an integer type.
-tg
-
Oct 6th, 2011, 07:13 AM
#6
Re: Help Resolving Syntax Error
You can clean up your connection as follows so not to worry about string concatenation. Note the use of IO.Path.Combine which ensures the blackslash will be added in this case.
Code:
Dim builder As New OleDbConnectionStringBuilder With _
{ _
.DataSource = IO.Path.Combine(Application.StartupPath, sDBNameA), _
.Provider = "Microsoft.Jet.OLEDB.4.0" _
}
builder.Add("User ID", "Admin")
Dim oConn As New OleDbConnection With {.ConnectionString = builder.ConnectionString}
-
Oct 6th, 2011, 07:27 AM
#7
Re: Help Resolving Syntax Error
I'd also like to point out that I wouldn't use the Reader if all you are doing is checking to see if a record exists... use the ExecuteScalar method instead. Using the reader will lock the connection to the reader, preventing you from using it anywhere else until the reader is closed. By using ExecuteScalar you free up your connection, allowing you to use it for the update or what ever else command you want.
-tg
-
Oct 6th, 2011, 07:31 AM
#8
Thread Starter
Addicted Member
Re: Help Resolving Syntax Error
vb Code:
If bStudentNumberExists Then
Using cmd As OleDbCommand = New OleDbCommand
sSaveSql = "update CUSTOMER set " _
& "Surname = ?," _
& "Given = ?," _
& "DateOfBirth = ?, " _
& "Sex = ?," _
& "Phone = ?," _
& "Address = ?," _
& "Suburb = ?," _
& "State = ?," _
& "PostCode = ?," _
& "where ID = ?;"
cmd.Parameters.AddWithValue("@Surname", oThisFormA.txtCusSurname.Text.Trim)
cmd.Parameters.AddWithValue("@Given", oThisFormA.txtCustGiven.Text.Trim)
cmd.Parameters.AddWithValue("@DateOfBirth", CDate(oThisFormA.txtCustDOB.Text.Trim))
cmd.Parameters.AddWithValue("@Sex", oThisFormA.txtCustSex.Text.Trim)
cmd.Parameters.AddWithValue("@Phone", oThisFormA.txtCustPhone.Text.Trim)
cmd.Parameters.AddWithValue("@Address", oThisFormA.txtCustAddress.Text.Trim)
cmd.Parameters.AddWithValue("@Suburb", oThisFormA.txtCustSuburb.Text.Trim)
cmd.Parameters.AddWithValue("@State", oThisFormA.txtCustState.Text.Trim)
cmd.Parameters.AddWithValue("@PostCode", oThisFormA.txtCustPostCode.Text.Trim)
cmd.Parameters.AddWithValue("@ID", CInt(oThisFormA.txtCustID.Text.Trim))
End Using
'sSaveSql = "update CUSTOMER set " _
' & "Surname = '" & oThisFormA.txtCusSurname.Text.Trim & "'," _
' & "Given = '" & oThisFormA.txtCustGiven.Text.Trim & "'," _
' & "DateOfBirth = '" & oThisFormA.txtCustDOB.Text.Trim & "', " _
' & "Sex = '" & oThisFormA.txtCustSex.Text.Trim & "'," _
' & "Phone = '" & oThisFormA.txtCustPhone.Text.Trim & "'," _
' & "Address = '" & oThisFormA.txtCustAddress.Text.Trim & "'," _
' & "Suburb = '" & oThisFormA.txtCustSuburb.Text.Trim & "'," _
' & "State = '" & oThisFormA.txtCustState.Text.Trim & "'," _
' & "PostCode = '" & oThisFormA.txtCustPostCode.Text.Trim & "'," _
' & "where ID ='" & oThisFormA.txtCustID.Text.Trim & "';"
Else
sSaveSql = "insert into CUSTOMER(ID, Surname, Given, DateOfBirth,Sex,Phone,Address,Suburb,State,PostCode) " _
& " values('" & oThisFormA.txtCustID.Text & "'," _
& "'" & oThisFormA.txtCusSurname.Text.Trim & "'," _
& "'" & oThisFormA.txtCustGiven.Text.Trim & "'," _
& "'" & oThisFormA.txtCustDOB.Text.Trim & "'," _
& "'" & oThisFormA.txtCustSex.Text.Trim & "'," _
& "'" & oThisFormA.txtCustPhone.Text.Trim & "'," _
& "'" & oThisFormA.txtCustAddress.Text.Trim & "'," _
& "'" & oThisFormA.txtCustSuburb.Text.Trim & "'," _
& "'" & oThisFormA.txtCustState.Text.Trim & "'," _
& "'" & oThisFormA.txtCustPostCode.Text.Trim & "' )"
MsgBox("ID:" & sCusID & " has been Added to the table")
' Be careful however with the commas and the closing bracket
End If
With that as my Update code is still get SYNTAX ERROR in UPDATE clause
Thanks for your help so far, but still not working.
-
Oct 6th, 2011, 07:35 AM
#9
Thread Starter
Addicted Member
Re: Help Resolving Syntax Error
Originally Posted by techgnome
I'd also like to point out that I wouldn't use the Reader if all you are doing is checking to see if a record exists... use the ExecuteScalar method instead. Using the reader will lock the connection to the reader, preventing you from using it anywhere else until the reader is closed. By using ExecuteScalar you free up your connection, allowing you to use it for the update or what ever else command you want.
-tg
Ok thanks but it's the way i have been asked to do it 
-
Oct 6th, 2011, 07:46 AM
#10
Re: Help Resolving Syntax Error
Change
Code:
& "PostCode = ?," _
To
Code:
& "PostCode = ? " _
-
Oct 6th, 2011, 07:52 AM
#11
Thread Starter
Addicted Member
Re: Help Resolving Syntax Error
Originally Posted by kevininstructor
Change
Code:
& "PostCode = ?," _
To
Code:
& "PostCode = ? " _
That fixed that error, thanks a lot! But now with this full code:
vb Code:
Public Sub g_sSaveCustomerRecord(ByVal oThisFormA As frmCaptureCustomer, _
ByVal sCusID As String, _
ByVal sDBNameA As String)
' ---------------------------------------------------------------------
' Incoming Parameters:
' oThisFormA - a reference to the current form
' of type frmDataCapture
' sStudentNumberA - The student number
' sDBNameA - The Database File Name
' Return value :
' if save successfull (no validation issues) return empty string, ""
' otherwise return error message
'------------------------------------------------------------
Dim sSaveSql As String
Dim sConnection As String
Dim sErrorMessage As String
Dim sSQL As String
Dim bStudentNumberExists As Boolean
Dim oConn As OleDbConnection 'To reference a Connection obj.
Dim oCmd_Select As OleDbCommand 'To Instantiate a Command obj used to execute the Select SQL.
Dim oDataReader As OleDbDataReader 'To instantiate a DataReader obj.
Dim oCmd_Update As OleDbCommand 'To Instantiate a Command obj used to execute the Insert/Update SQL.
'sErrorMessage = ""
oConn = Nothing
oCmd_Select = Nothing
oDataReader = Nothing
oCmd_Update = Nothing
' --- create a database command object and set the SQL statement
' --- it will execute, and the database connection object associated to it
sConnection = "Provider=Microsoft.Jet.OLEDB.4.0;"
sConnection = sConnection & "User ID=Admin;"
sConnection = sConnection & "Data Source="
sConnection = sConnection & Application.StartupPath & "\" & sDBNameA
oConn = New OleDbConnection(sConnection)
oConn.Open()
oCmd_Select = oConn.CreateCommand()
sSQL = "select ID from CUSTOMER where ID= " & sCusID & ""
oCmd_Select.CommandText = sSQL
' --- execute SQL command and place results into a datareader object
oDataReader = oCmd_Select.ExecuteReader()
bStudentNumberExists = (oDataReader.Read() = True)
'MessageBox.Show("bStudentNumberExists=" & bStudentNumberExists)
' --- Build Insert or Update Sql depending if record retrieved with select query is found
If bStudentNumberExists Then
Using cmd As OleDbCommand = New OleDbCommand
sSaveSql = "update CUSTOMER set " _
& "Surname = ?," _
& "Given = ?," _
& "DateOfBirth = ?, " _
& "Sex = ?," _
& "Phone = ?," _
& "Address = ?," _
& "Suburb = ?," _
& "State = ?," _
& "PostCode = ? " _
& "where ID = ?;"
cmd.Parameters.AddWithValue("@Surname", oThisFormA.txtCusSurname.Text.Trim)
cmd.Parameters.AddWithValue("@Given", oThisFormA.txtCustGiven.Text.Trim)
cmd.Parameters.AddWithValue("@DateOfBirth", CDate(oThisFormA.txtCustDOB.Text.Trim))
cmd.Parameters.AddWithValue("@Sex", oThisFormA.txtCustSex.Text.Trim)
cmd.Parameters.AddWithValue("@Phone", oThisFormA.txtCustPhone.Text.Trim)
cmd.Parameters.AddWithValue("@Address", oThisFormA.txtCustAddress.Text.Trim)
cmd.Parameters.AddWithValue("@Suburb", oThisFormA.txtCustSuburb.Text.Trim)
cmd.Parameters.AddWithValue("@State", oThisFormA.txtCustState.Text.Trim)
cmd.Parameters.AddWithValue("@PostCode", oThisFormA.txtCustPostCode.Text.Trim)
cmd.Parameters.AddWithValue("@ID", CInt(oThisFormA.txtCustID.Text.Trim))
End Using
'sSaveSql = "update CUSTOMER set " _
' & "Surname = '" & oThisFormA.txtCusSurname.Text.Trim & "'," _
' & "Given = '" & oThisFormA.txtCustGiven.Text.Trim & "'," _
' & "DateOfBirth = '" & oThisFormA.txtCustDOB.Text.Trim & "', " _
' & "Sex = '" & oThisFormA.txtCustSex.Text.Trim & "'," _
' & "Phone = '" & oThisFormA.txtCustPhone.Text.Trim & "'," _
' & "Address = '" & oThisFormA.txtCustAddress.Text.Trim & "'," _
' & "Suburb = '" & oThisFormA.txtCustSuburb.Text.Trim & "'," _
' & "State = '" & oThisFormA.txtCustState.Text.Trim & "'," _
' & "PostCode = '" & oThisFormA.txtCustPostCode.Text.Trim & "'," _
' & "where ID ='" & oThisFormA.txtCustID.Text.Trim & "';"
'Else
' sSaveSql = "insert into CUSTOMER(ID, Surname, Given, DateOfBirth,Sex,Phone,Address,Suburb,State,PostCode) " _
' & " values('" & oThisFormA.txtCustID.Text & "'," _
' & "'" & oThisFormA.txtCusSurname.Text.Trim & "'," _
' & "'" & oThisFormA.txtCustGiven.Text.Trim & "'," _
' & "'" & oThisFormA.txtCustDOB.Text.Trim & "'," _
' & "'" & oThisFormA.txtCustSex.Text.Trim & "'," _
' & "'" & oThisFormA.txtCustPhone.Text.Trim & "'," _
' & "'" & oThisFormA.txtCustAddress.Text.Trim & "'," _
' & "'" & oThisFormA.txtCustSuburb.Text.Trim & "'," _
' & "'" & oThisFormA.txtCustState.Text.Trim & "'," _
' & "'" & oThisFormA.txtCustPostCode.Text.Trim & "' )"
' MsgBox("ID:" & sCusID & " has been Added to the table")
' ' Be careful however with the commas and the closing bracket
End If
oDataReader.Close()
oCmd_Select.Dispose()
' Execute sql update or insert query:
oCmd_Update = oConn.CreateCommand()
oCmd_Update.CommandText = sSaveSql
oCmd_Update.ExecuteNonQuery()
End Sub
I get Erro:
Code:
No value given for one or more required parameters.
Any Idea why?
I have done a debug and every txt box had a vale assigned to it.
-
Oct 6th, 2011, 07:54 AM
#12
Re: Help Resolving Syntax Error
Good to hear that fixed the first error, using parameters along with you statement w/o string concatenation allows you to examine said statement easier to spot issues.
For example the following returns a string which is easy to locate issues.
Code:
Dim UpdateStatement = _
<SQL>
UPDATE CUSTOMER set
Surname = ?,
Given = ?,
DateOfBirth = ?,
Sex = ?,
Phone = ?,
Address = ?,
Suburb = ?,
State = ?,
PostCode = ?
WHERE ID = ?
</SQL>.Value
-
Oct 6th, 2011, 07:57 AM
#13
Thread Starter
Addicted Member
Re: Help Resolving Syntax Error
Originally Posted by kevininstructor
Good to hear that fixed the first error, using parameters along with you statement w/o string concatenation allows you to examine said statement easier to spot issues.
For example the following returns a string which is easy to locate issues.
Code:
Dim UpdateStatement = _
<SQL>
UPDATE CUSTOMER set
Surname = ?,
Given = ?,
DateOfBirth = ?,
Sex = ?,
Phone = ?,
Address = ?,
Suburb = ?,
State = ?,
PostCode = ?
WHERE ID = ?
</SQL>.Value
Sorry to bug ya mate, im new to vb haha so not to sure what you want me to do with that piece of code
-
Oct 6th, 2011, 08:02 AM
#14
Re: Help Resolving Syntax Error
Originally Posted by coccoster
Sorry to bug ya mate, im new to vb haha so not to sure what you want me to do with that piece of code
Lets simplify it by taking the variable out, this is your SQL statement assigned directly to the commmand's command text.
Code:
Using cmd As OleDbCommand = New OleDbCommand
cmd.CommandText = _
<SQL>
UPDATE CUSTOMER set
Surname = ?,
Given = ?,
DateOfBirth = ?,
Sex = ?,
Phone = ?,
Address = ?,
Suburb = ?,
State = ?,
PostCode = ?
WHERE ID = ?
</SQL>.Value
End Using
-
Oct 6th, 2011, 08:06 AM
#15
Thread Starter
Addicted Member
Re: Help Resolving Syntax Error
Originally Posted by kevininstructor
Lets simplify it by taking the variable out, this is your SQL statement assigned directly to the commmand's command text.
Code:
Using cmd As OleDbCommand = New OleDbCommand
cmd.CommandText = _
<SQL>
UPDATE CUSTOMER set
Surname = ?,
Given = ?,
DateOfBirth = ?,
Sex = ?,
Phone = ?,
Address = ?,
Suburb = ?,
State = ?,
PostCode = ?
WHERE ID = ?
</SQL>.Value
End Using
I commented out the other code you gave me and pasted that code instead.. The only error i got was
Code:
"Command text was not set for the command object."
Not sure im doing the right thing though.
-
Oct 6th, 2011, 08:07 AM
#16
Re: Help Resolving Syntax Error
Here is another example
Original
Code:
sSQL = "select ID from CUSTOMER where ID= " & sCusID & ""
Alternate
Code:
Dim sSQL = <SQL>select ID from CUSTOMER where ID= <%= sCusID %></SQL>.Value
Both do the same thing but the second one w/o concatenation
-
Oct 6th, 2011, 08:13 AM
#17
Thread Starter
Addicted Member
Re: Help Resolving Syntax Error
Originally Posted by kevininstructor
Here is another example
Original
Code:
sSQL = "select ID from CUSTOMER where ID= " & sCusID & ""
Alternate
Code:
Dim sSQL = <SQL>select ID from CUSTOMER where ID= <%= sCusID %></SQL>.Value
Both do the same thing but the second one w/o concatenation
Ohh ok..
I un commented out everything, and did what you just posted..
Still get the same error as above:
Code:
"Command text was not set for the command object."
Sp full code looks like:
vb Code:
Public Sub g_sSaveCustomerRecord(ByVal oThisFormA As frmCaptureCustomer, _
ByVal sCusID As String, _
ByVal sDBNameA As String)
' ---------------------------------------------------------------------
' Incoming Parameters:
' oThisFormA - a reference to the current form
' of type frmDataCapture
' sStudentNumberA - The student number
' sDBNameA - The Database File Name
' Return value :
' if save successfull (no validation issues) return empty string, ""
' otherwise return error message
'------------------------------------------------------------
Dim sSaveSql As String
Dim sConnection As String
Dim sErrorMessage As String
Dim sSQL As String
Dim bStudentNumberExists As Boolean
Dim oConn As OleDbConnection 'To reference a Connection obj.
Dim oCmd_Select As OleDbCommand 'To Instantiate a Command obj used to execute the Select SQL.
Dim oDataReader As OleDbDataReader 'To instantiate a DataReader obj.
Dim oCmd_Update As OleDbCommand 'To Instantiate a Command obj used to execute the Insert/Update SQL.
'sErrorMessage = ""
oConn = Nothing
oCmd_Select = Nothing
oDataReader = Nothing
oCmd_Update = Nothing
' --- create a database command object and set the SQL statement
' --- it will execute, and the database connection object associated to it
sConnection = "Provider=Microsoft.Jet.OLEDB.4.0;"
sConnection = sConnection & "User ID=Admin;"
sConnection = sConnection & "Data Source="
sConnection = sConnection & Application.StartupPath & "\" & sDBNameA
oConn = New OleDbConnection(sConnection)
oConn.Open()
oCmd_Select = oConn.CreateCommand()
sSQL = <SQL>select ID from CUSTOMER where ID= <%= sCusID %></SQL>.Value
'sSQL = "select ID from CUSTOMER where ID= " & sCusID & ""
oCmd_Select.CommandText = sSQL
' --- execute SQL command and place results into a datareader object
oDataReader = oCmd_Select.ExecuteReader()
bStudentNumberExists = (oDataReader.Read() = True)
'MessageBox.Show("bStudentNumberExists=" & bStudentNumberExists)
' --- Build Insert or Update Sql depending if record retrieved with select query is found
If bStudentNumberExists Then
Using cmd As OleDbCommand = New OleDbCommand
sSaveSql = "update CUSTOMER set " _
& "Surname = ?," _
& "Given = ?," _
& "DateOfBirth = ?, " _
& "Sex = ?," _
& "Phone = ?," _
& "Address = ?," _
& "Suburb = ?," _
& "State = ?," _
& "PostCode = ? " _
& "where ID = ?;"
cmd.Parameters.AddWithValue("@Surname", oThisFormA.txtCusSurname.Text.Trim)
cmd.Parameters.AddWithValue("@Given", oThisFormA.txtCustGiven.Text.Trim)
cmd.Parameters.AddWithValue("@DateOfBirth", CDate(oThisFormA.txtCustDOB.Text.Trim))
cmd.Parameters.AddWithValue("@Sex", oThisFormA.txtCustSex.Text.Trim)
cmd.Parameters.AddWithValue("@Phone", oThisFormA.txtCustPhone.Text.Trim)
cmd.Parameters.AddWithValue("@Address", oThisFormA.txtCustAddress.Text.Trim)
cmd.Parameters.AddWithValue("@Suburb", oThisFormA.txtCustSuburb.Text.Trim)
cmd.Parameters.AddWithValue("@State", oThisFormA.txtCustState.Text.Trim)
cmd.Parameters.AddWithValue("@PostCode", oThisFormA.txtCustPostCode.Text.Trim)
cmd.Parameters.AddWithValue("@ID", CInt(oThisFormA.txtCustID.Text.Trim))
End Using
'sSaveSql = "update CUSTOMER set " _
' & "Surname = '" & oThisFormA.txtCusSurname.Text.Trim & "'," _
' & "Given = '" & oThisFormA.txtCustGiven.Text.Trim & "'," _
' & "DateOfBirth = '" & oThisFormA.txtCustDOB.Text.Trim & "', " _
' & "Sex = '" & oThisFormA.txtCustSex.Text.Trim & "'," _
' & "Phone = '" & oThisFormA.txtCustPhone.Text.Trim & "'," _
' & "Address = '" & oThisFormA.txtCustAddress.Text.Trim & "'," _
' & "Suburb = '" & oThisFormA.txtCustSuburb.Text.Trim & "'," _
' & "State = '" & oThisFormA.txtCustState.Text.Trim & "'," _
' & "PostCode = '" & oThisFormA.txtCustPostCode.Text.Trim & "'," _
' & "where ID ='" & oThisFormA.txtCustID.Text.Trim & "';"
'Else
' sSaveSql = "insert into CUSTOMER(ID, Surname, Given, DateOfBirth,Sex,Phone,Address,Suburb,State,PostCode) " _
' & " values('" & oThisFormA.txtCustID.Text & "'," _
' & "'" & oThisFormA.txtCusSurname.Text.Trim & "'," _
' & "'" & oThisFormA.txtCustGiven.Text.Trim & "'," _
' & "'" & oThisFormA.txtCustDOB.Text.Trim & "'," _
' & "'" & oThisFormA.txtCustSex.Text.Trim & "'," _
' & "'" & oThisFormA.txtCustPhone.Text.Trim & "'," _
' & "'" & oThisFormA.txtCustAddress.Text.Trim & "'," _
' & "'" & oThisFormA.txtCustSuburb.Text.Trim & "'," _
' & "'" & oThisFormA.txtCustState.Text.Trim & "'," _
' & "'" & oThisFormA.txtCustPostCode.Text.Trim & "' )"
' MsgBox("ID:" & sCusID & " has been Added to the table")
' ' Be careful however with the commas and the closing bracket
End If
oDataReader.Close()
oCmd_Select.Dispose()
' Execute sql update or insert query:
oCmd_Update = oConn.CreateCommand()
oCmd_Update.CommandText = sSaveSql
oCmd_Update.ExecuteNonQuery()
End Sub
-
Oct 6th, 2011, 08:14 AM
#18
Re: Help Resolving Syntax Error
Originally Posted by coccoster
I commented out the other code you gave me and pasted that code instead.. The only error i got was
Code:
"Command text was not set for the command object."
Not sure im doing the right thing though.
Not sure either as the code is clearly setting the CommandText ie
Code:
Using cmd As OleDbCommand = New OleDbCommand
cmd.CommandText = _
<SQL>
UPDATE CUSTOMER set
Surname = ?,
Given = ?,
DateOfBirth = ?,
Sex = ?,
Phone = ?,
Address = ?,
Suburb = ?,
State = ?,
PostCode = ?
WHERE ID = ?
</SQL>.Value
MsgBox(cmd.CommandText)
End Using
Or
Code:
Using cmd As OleDbCommand = New OleDbCommand
cmd.CommandText = _
<SQL>
UPDATE CUSTOMER set
Surname = ?,
Given = ?,
DateOfBirth = ?,
Sex = ?,
Phone = ?,
Address = ?,
Suburb = ?,
State = ?,
PostCode = ?
WHERE ID = ?
</SQL>.Value
Console.WriteLine(cmd.CommandText)
End Using
Result from console write line
Code:
UPDATE CUSTOMER set
Surname = ?,
Given = ?,
DateOfBirth = ?,
Sex = ?,
Phone = ?,
Address = ?,
Suburb = ?,
State = ?,
PostCode = ?
WHERE ID = ?
-
Oct 6th, 2011, 08:18 AM
#19
Thread Starter
Addicted Member
Re: Help Resolving Syntax Error
Originally Posted by kevininstructor
Not sure either as the code is clearly setting the CommandText ie
Code:
Using cmd As OleDbCommand = New OleDbCommand
cmd.CommandText = _
<SQL>
UPDATE CUSTOMER set
Surname = ?,
Given = ?,
DateOfBirth = ?,
Sex = ?,
Phone = ?,
Address = ?,
Suburb = ?,
State = ?,
PostCode = ?
WHERE ID = ?
</SQL>.Value
MsgBox(cmd.CommandText)
End Using
Or
Code:
Using cmd As OleDbCommand = New OleDbCommand
cmd.CommandText = _
<SQL>
UPDATE CUSTOMER set
Surname = ?,
Given = ?,
DateOfBirth = ?,
Sex = ?,
Phone = ?,
Address = ?,
Suburb = ?,
State = ?,
PostCode = ?
WHERE ID = ?
</SQL>.Value
Console.WriteLine(cmd.CommandText)
End Using
Result from console write line
Code:
UPDATE CUSTOMER set
Surname = ?,
Given = ?,
DateOfBirth = ?,
Sex = ?,
Phone = ?,
Address = ?,
Suburb = ?,
State = ?,
PostCode = ?
WHERE ID = ?
Does the code i posted before look right to you?
-
Oct 6th, 2011, 08:19 AM
#20
Re: Help Resolving Syntax Error
The following will work if you have Option Infer On and using Framework 3.5 or higher (VS2008 or VS2010)
I highly suggest turning Option Strict On
Code:
If bStudentNumberExists Then
Using cmd As OleDbCommand = New OleDbCommand
cmd.CommandText = _
<SQL>
UPDATE CUSTOMER set
Surname = ?,
Given = ?,
DateOfBirth = ?,
Sex = ?,
Phone = ?,
Address = ?,
Suburb = ?,
State = ?,
PostCode = ?
WHERE ID = ?
</SQL>.Value
cmd.Parameters.AddWithValue("@Surname", oThisFormA.txtCusSurname.Text.Trim)
cmd.Parameters.AddWithValue("@Given", oThisFormA.txtCustGiven.Text.Trim)
cmd.Parameters.AddWithValue("@DateOfBirth", CDate(oThisFormA.txtCustDOB.Text.Trim))
cmd.Parameters.AddWithValue("@Sex", oThisFormA.txtCustSex.Text.Trim)
cmd.Parameters.AddWithValue("@Phone", oThisFormA.txtCustPhone.Text.Trim)
cmd.Parameters.AddWithValue("@Address", oThisFormA.txtCustAddress.Text.Trim)
cmd.Parameters.AddWithValue("@Suburb", oThisFormA.txtCustSuburb.Text.Trim)
cmd.Parameters.AddWithValue("@State", oThisFormA.txtCustState.Text.Trim)
cmd.Parameters.AddWithValue("@PostCode", oThisFormA.txtCustPostCode.Text.Trim)
cmd.Parameters.AddWithValue("@ID", CInt(oThisFormA.txtCustID.Text.Trim))
End Using
End If
-
Oct 6th, 2011, 08:25 AM
#21
Thread Starter
Addicted Member
Re: Help Resolving Syntax Error
Yep ive had Option Strict on the whole time.
I'm currently using vs2010.
Well with my code looking like:
vb Code:
Public Sub g_sSaveCustomerRecord(ByVal oThisFormA As frmCaptureCustomer, _
ByVal sCusID As String, _
ByVal sDBNameA As String)
' ---------------------------------------------------------------------
' Incoming Parameters:
' oThisFormA - a reference to the current form
' of type frmDataCapture
' sStudentNumberA - The student number
' sDBNameA - The Database File Name
' Return value :
' if save successfull (no validation issues) return empty string, ""
' otherwise return error message
'------------------------------------------------------------
Dim sSaveSql As String
Dim sConnection As String
Dim sErrorMessage As String
Dim sSQL As String
Dim bStudentNumberExists As Boolean
Dim oConn As OleDbConnection 'To reference a Connection obj.
Dim oCmd_Select As OleDbCommand 'To Instantiate a Command obj used to execute the Select SQL.
Dim oDataReader As OleDbDataReader 'To instantiate a DataReader obj.
Dim oCmd_Update As OleDbCommand 'To Instantiate a Command obj used to execute the Insert/Update SQL.
'sErrorMessage = ""
oConn = Nothing
oCmd_Select = Nothing
oDataReader = Nothing
oCmd_Update = Nothing
' --- create a database command object and set the SQL statement
' --- it will execute, and the database connection object associated to it
sConnection = "Provider=Microsoft.Jet.OLEDB.4.0;"
sConnection = sConnection & "User ID=Admin;"
sConnection = sConnection & "Data Source="
sConnection = sConnection & Application.StartupPath & "\" & sDBNameA
oConn = New OleDbConnection(sConnection)
oConn.Open()
oCmd_Select = oConn.CreateCommand()
sSQL = <SQL>select ID from CUSTOMER where ID= <%= sCusID %></SQL>.Value
'sSQL = "select ID from CUSTOMER where ID= " & sCusID & ""
oCmd_Select.CommandText = sSQL
' --- execute SQL command and place results into a datareader object
oDataReader = oCmd_Select.ExecuteReader()
bStudentNumberExists = (oDataReader.Read() = True)
' --- Build Insert or Update Sql depending if record retrieved with select query is found
If bStudentNumberExists Then
If bStudentNumberExists Then
Using cmd As OleDbCommand = New OleDbCommand
cmd.CommandText = _
<SQL>
UPDATE CUSTOMER set
Surname = ?,
Given = ?,
DateOfBirth = ?,
Sex = ?,
Phone = ?,
Address = ?,
Suburb = ?,
State = ?,
PostCode = ?
WHERE ID = ?
</SQL>.Value
cmd.Parameters.AddWithValue("@Surname", oThisFormA.txtCusSurname.Text.Trim)
cmd.Parameters.AddWithValue("@Given", oThisFormA.txtCustGiven.Text.Trim)
cmd.Parameters.AddWithValue("@DateOfBirth", CDate(oThisFormA.txtCustDOB.Text.Trim))
cmd.Parameters.AddWithValue("@Sex", oThisFormA.txtCustSex.Text.Trim)
cmd.Parameters.AddWithValue("@Phone", oThisFormA.txtCustPhone.Text.Trim)
cmd.Parameters.AddWithValue("@Address", oThisFormA.txtCustAddress.Text.Trim)
cmd.Parameters.AddWithValue("@Suburb", oThisFormA.txtCustSuburb.Text.Trim)
cmd.Parameters.AddWithValue("@State", oThisFormA.txtCustState.Text.Trim)
cmd.Parameters.AddWithValue("@PostCode", oThisFormA.txtCustPostCode.Text.Trim)
cmd.Parameters.AddWithValue("@ID", CInt(oThisFormA.txtCustID.Text.Trim))
End Using
End If
' ' Be careful however with the commas and the closing bracket
End If
oDataReader.Close()
oCmd_Select.Dispose()
' Execute sql update or insert query:
oCmd_Update = oConn.CreateCommand()
oCmd_Update.CommandText = sSaveSql
oCmd_Update.ExecuteNonQuery()
End Sub
I am getting the same
Code:
"Command text was not set for the command object."
error 
I put Option Infer On right at the top aswell.
-
Oct 6th, 2011, 08:58 AM
#22
Re: Help Resolving Syntax Error
Originally Posted by coccoster
Yep ive had Option Strict on the whole time.
I'm currently using vs2010.
Well with my code looking like:
vb Code:
Public Sub g_sSaveCustomerRecord(ByVal oThisFormA As frmCaptureCustomer, _
ByVal sCusID As String, _
ByVal sDBNameA As String)
' ---------------------------------------------------------------------
' Incoming Parameters:
' oThisFormA - a reference to the current form
' of type frmDataCapture
' sStudentNumberA - The student number
' sDBNameA - The Database File Name
' Return value :
' if save successfull (no validation issues) return empty string, ""
' otherwise return error message
'------------------------------------------------------------
Dim sSaveSql As String
Dim sConnection As String
Dim sErrorMessage As String
Dim sSQL As String
Dim bStudentNumberExists As Boolean
Dim oConn As OleDbConnection 'To reference a Connection obj.
Dim oCmd_Select As OleDbCommand 'To Instantiate a Command obj used to execute the Select SQL.
Dim oDataReader As OleDbDataReader 'To instantiate a DataReader obj.
Dim oCmd_Update As OleDbCommand 'To Instantiate a Command obj used to execute the Insert/Update SQL.
'sErrorMessage = ""
oConn = Nothing
oCmd_Select = Nothing
oDataReader = Nothing
oCmd_Update = Nothing
' --- create a database command object and set the SQL statement
' --- it will execute, and the database connection object associated to it
sConnection = "Provider=Microsoft.Jet.OLEDB.4.0;"
sConnection = sConnection & "User ID=Admin;"
sConnection = sConnection & "Data Source="
sConnection = sConnection & Application.StartupPath & "\" & sDBNameA
oConn = New OleDbConnection(sConnection)
oConn.Open()
oCmd_Select = oConn.CreateCommand()
sSQL = <SQL>select ID from CUSTOMER where ID= <%= sCusID %></SQL>.Value
'sSQL = "select ID from CUSTOMER where ID= " & sCusID & ""
oCmd_Select.CommandText = sSQL
' --- execute SQL command and place results into a datareader object
oDataReader = oCmd_Select.ExecuteReader()
bStudentNumberExists = (oDataReader.Read() = True)
' --- Build Insert or Update Sql depending if record retrieved with select query is found
If bStudentNumberExists Then
If bStudentNumberExists Then
Using cmd As OleDbCommand = New OleDbCommand
cmd.CommandText = _
<SQL>
UPDATE CUSTOMER set
Surname = ?,
Given = ?,
DateOfBirth = ?,
Sex = ?,
Phone = ?,
Address = ?,
Suburb = ?,
State = ?,
PostCode = ?
WHERE ID = ?
</SQL>.Value
cmd.Parameters.AddWithValue("@Surname", oThisFormA.txtCusSurname.Text.Trim)
cmd.Parameters.AddWithValue("@Given", oThisFormA.txtCustGiven.Text.Trim)
cmd.Parameters.AddWithValue("@DateOfBirth", CDate(oThisFormA.txtCustDOB.Text.Trim))
cmd.Parameters.AddWithValue("@Sex", oThisFormA.txtCustSex.Text.Trim)
cmd.Parameters.AddWithValue("@Phone", oThisFormA.txtCustPhone.Text.Trim)
cmd.Parameters.AddWithValue("@Address", oThisFormA.txtCustAddress.Text.Trim)
cmd.Parameters.AddWithValue("@Suburb", oThisFormA.txtCustSuburb.Text.Trim)
cmd.Parameters.AddWithValue("@State", oThisFormA.txtCustState.Text.Trim)
cmd.Parameters.AddWithValue("@PostCode", oThisFormA.txtCustPostCode.Text.Trim)
cmd.Parameters.AddWithValue("@ID", CInt(oThisFormA.txtCustID.Text.Trim))
End Using
End If
' ' Be careful however with the commas and the closing bracket
End If
oDataReader.Close()
oCmd_Select.Dispose()
' Execute sql update or insert query:
oCmd_Update = oConn.CreateCommand()
oCmd_Update.CommandText = sSaveSql
oCmd_Update.ExecuteNonQuery()
End Sub
I am getting the same
Code:
"Command text was not set for the command object."
error
I put Option Infer On right at the top aswell.
Which line number in regards to the code above is giving you this error message.
-
Oct 6th, 2011, 09:01 AM
#23
Thread Starter
Addicted Member
Re: Help Resolving Syntax Error
Originally Posted by kevininstructor
Which line number in regards to the code above is giving you this error message.
101. oCmd_Update.ExecuteNonQuery()
-
Oct 6th, 2011, 09:07 AM
#24
Re: Help Resolving Syntax Error
Originally Posted by coccoster
101. oCmd_Update.ExecuteNonQuery()
That is because you have not set the CommandText for oCmd_Update
You have set the following only
Code:
oCmd_Select.CommandText = sSQL
and
Code:
Using cmd As OleDbCommand = New OleDbCommand
cmd.CommandText = _
<SQL>
UPDATE CUSTOMER set
Surname = ?,
Given = ?,
DateOfBirth = ?,
Sex = ?,
Phone = ?,
Address = ?,
Suburb = ?,
State = ?,
PostCode = ?
WHERE ID = ?
</SQL>.Value
-
Oct 6th, 2011, 09:15 AM
#25
Thread Starter
Addicted Member
Re: Help Resolving Syntax Error
Originally Posted by kevininstructor
That is because you have not set the CommandText for oCmd_Update
You have set the following only
Code:
oCmd_Select.CommandText = sSQL
and
Code:
Using cmd As OleDbCommand = New OleDbCommand
cmd.CommandText = _
<SQL>
UPDATE CUSTOMER set
Surname = ?,
Given = ?,
DateOfBirth = ?,
Sex = ?,
Phone = ?,
Address = ?,
Suburb = ?,
State = ?,
PostCode = ?
WHERE ID = ?
</SQL>.Value
I'm not sure what i have to do now, sorry mate.
-
Oct 6th, 2011, 09:31 AM
#26
Re: Help Resolving Syntax Error
Originally Posted by coccoster
I'm not sure what i have to do now, sorry mate.
You need to set the CommandText, simple as that.
-
Oct 6th, 2011, 09:35 AM
#27
Thread Starter
Addicted Member
Re: Help Resolving Syntax Error
Originally Posted by kevininstructor
You need to set the CommandText, simple as that.
Im sorry but how do i do that??
Im not quite sure i understand entirely what i need to edit/add
-
Oct 6th, 2011, 09:38 AM
#28
Re: Help Resolving Syntax Error
Originally Posted by coccoster
Im sorry but how do i do that??
Im not quite sure i understand entirely what i need to edit/add
You set the CommandText
i.e.
MyCommand.CommandText = "Your command text"
or as I have shown using
Code:
<SQL>
UPDATE CUSTOMER set
Surname = ?,
Given = ?,
DateOfBirth = ?,
Sex = ?,
Phone = ?,
Address = ?,
Suburb = ?,
State = ?,
PostCode = ?
WHERE ID = ?
</SQL>.Value
It is that simple.
-
Oct 6th, 2011, 09:45 AM
#29
Thread Starter
Addicted Member
Re: Help Resolving Syntax Error
Code:
<SQL>
UPDATE CUSTOMER set
Surname = ?,
Given = ?,
DateOfBirth = ?,
Sex = ?,
Phone = ?,
Address = ?,
Suburb = ?,
State = ?,
PostCode = ?
WHERE ID = ?
</SQL>.Value
So i have to remove the "?" and add txtHardGiven.Text etc??
-
Oct 6th, 2011, 10:05 AM
#30
Re: Help Resolving Syntax Error
no... you assign theabove as t he command text... then you need to add the parameters in the manner in which I showed using command.parameters.addwithvalue ...
-tg
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|
Reply With Quote
