|
-
Oct 6th, 2011, 07:46 AM
#1
Re: Help Resolving Syntax Error
Change
Code:
& "PostCode = ?," _
To
Code:
& "PostCode = ? " _
-
Oct 6th, 2011, 07:52 AM
#2
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
#3
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
#4
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
#5
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
#6
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:14 AM
#7
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
#8
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
#9
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:07 AM
#10
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
#11
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
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