| View previous topic :: View next topic |
| Author |
Message |
aftershock
Newbie
Joined: 06 May 2012
Posts: 3
|
 Posted: Sun May 06, 2012 6:43 am Post subject: [Solved] SQL Macros and those darn runtime errors |
 |
|
Yes I'm new, to the forums and somewhat to Oo.
I have a form called Job. It has a listbox which is populated via sql. Listbox lists "LastName,FirstName". The sql pulls the information from the Customer table.
This Job form will create the job table entries, but I must first select a customer.
Once a customer is selected, I run (or am trying to run) an sql query in a macro to retrieve the CustomerID from the Customer table, using the LastName. (Once I can get this down I will actually check first and last name). Below is the code I am using
| Code: | Sub GetListBoxItem (oEvent As Object)
dim oListBox as object
dim srchString
srchString = ","
dim Form as object
dim MyPosition as integer
dim strLength as integer
dim newString as string
dim MyText as string
dim MyTextBox as object
Form = oEvent.Source.Model.Parent
MyTextBox = Form.GetByName("txtFirstName")
oListBox = Form.GetByName("CustomerListBox")
MyText = oListBox.CurrentValue
strLength = Len(MyText)
MyPosition = InStr(MyText,srchString)
newString = Right(MyText,strLength - MyPosition)
newString = Trim(newString)
MyTextBox.setString(newString)
MyTextBox = Form.GetByName("txtLastName")
newString = Left(MyText,MyPosition - 1)
newString = Trim(newString)
MyTextBox.setString(newString)
dim OStatement as object
dim searchCustomer as string
dim oResult
OStatement = Form.ActiveConnection.createStatement()
searchCustomer = "SELECT""CustomerID""FROM""Customer""WHERE""LastName"" = "& newString
oResult = OStatement.executeQuery(searchCustomer)
msgbox(searchCustomer)
End Sub |
I get "BASIC runtime error. An exception occured Type:com.sun.star.sdbc.SQLException
Message: Column not found: JENKINS in statement [SELECT"CustomerID"FROM"Customer"WHERE"LastName"="JENKINS"].
Last edited by aftershock on Mon May 07, 2012 2:44 am; edited 1 time in total |
|
| Back to top |
|
 |
Villeroy
Super User
Joined: 04 Oct 2004
Posts: 10065
Location: Germany
|
| Posted: Sun May 06, 2012 7:02 am Post subject: |
|
|
| Quote: | | Once a customer is selected, I run (or am trying to run) an sql query in a macro to retrieve the CustomerID from the Customer table, using the LastName. |
And what makes you believe that anybody needs a macro for this? This is the first and foremost job of a list box. Avoid all macros. Macros are evil especially when using a rotten procedural language in an extremely object oriented environment.
http://user.services.openoffice.org/en/forum/viewtopic.php?f=100&t=40444
_________________
Rest in peace, oooforum.org
Get help on http://forum.openoffice.org |
|
| Back to top |
|
|
Sliderule
Super User
Joined: 29 May 2004
Posts: 2477
Location: 3rd Rock From The Sun
|
| Posted: Sun May 06, 2012 7:37 am Post subject: |
|
|
Just as an FYI . . . IF you want to do this with a macro . . . the following may help.
| Code: | Sub GetListBoxItem (oEvent As Object)
dim oListBox as object
dim srchString
srchString = ","
dim Form as object
dim MyPosition as integer
dim strLength as integer
dim newString as string
dim MyText as string
dim MyTextBox as object
' Added by Sliderule 2012-05-06
dim sSINGLEQUOTE as string ' Used to define a single quote
sSINGLEQUOTE = CHR(39) ' Basic CHR function for a single quote
Form = oEvent.Source.Model.Parent
MyTextBox = Form.GetByName("txtFirstName")
oListBox = Form.GetByName("CustomerListBox")
MyText = oListBox.CurrentValue
strLength = Len(MyText)
MyPosition = InStr(MyText,srchString)
newString = Right(MyText,strLength - MyPosition)
newString = Trim(newString)
MyTextBox.setString(newString)
MyTextBox = Form.GetByName("txtLastName")
newString = Left(MyText,MyPosition - 1)
' Changed by Sliderule 2012-05-06 so variable will be surrounded by single quotes
newString = sSINGLEQUOTE & Trim(newString) & sSINGLEQUOTE
MyTextBox.setString(newString)
dim OStatement as object
dim searchCustomer as string
dim oResult
OStatement = Form.ActiveConnection.createStatement()
searchCustomer = "SELECT ""CustomerID"" FROM ""Customer"" WHERE ""LastName"" = " & newString
msgbox(searchCustomer)
oResult = OStatement.executeQuery(searchCustomer)
' Added by Sliderule 2012-05-06
' as a demonstration of retrieving values from Object oResult
' Loop through all Rows returned in the event more than one row returned
While oResult.next
' MsgBox oResult.GetString(1) 'Since first column returned use number 1
' Since first column returned use number 1 in GetString
MsgBox(oResult.GetString(1),0,"CustomerID field from DB")
Wend '<<< End of While Loop for oResultSet
End Sub |
Explanation: When using a Query . . . text strings MUST be surrounded by single quotes, for example, 'Smith' . . . however . . . StarBasic uses a single quote to indicate a comment on a line of code.
Therefore, I 'assigned' a variable ( sSINGLEQUOTE ) using the StarBasic function CHR and, surrounded the variable newString with the single quotes 
I hope this helps, please be sure to let me / us know.
Sliderule
Thanks to add [Solved] in your first post Title ( edit button ) if your issue has been fixed / resolved. |
|
| Back to top |
|
|
aftershock
Newbie
Joined: 06 May 2012
Posts: 3
|
| Posted: Mon May 07, 2012 2:40 am Post subject: |
|
|
| Villeroy wrote: | | Quote: | | Once a customer is selected, I run (or am trying to run) an sql query in a macro to retrieve the CustomerID from the Customer table, using the LastName. |
And what makes you believe that anybody needs a macro for this? This is the first and foremost job of a list box. Avoid all macros. Macros are evil especially when using a rotten procedural language in an extremely object oriented environment.
http://user.services.openoffice.org/en/forum/viewtopic.php?f=100&t=40444 |
I am used to programming behind the GUI as in VB. I will check out the DB example thanx |
|
| Back to top |
|
|
aftershock
Newbie
Joined: 06 May 2012
Posts: 3
|
| Posted: Mon May 07, 2012 2:42 am Post subject: |
|
|
| Sliderule wrote: | Just as an FYI . . . IF you want to do this with a macro . . . the following may help.
| Code: | Sub GetListBoxItem (oEvent As Object)
dim oListBox as object
dim srchString
srchString = ","
dim Form as object
dim MyPosition as integer
dim strLength as integer
dim newString as string
dim MyText as string
dim MyTextBox as object
' Added by Sliderule 2012-05-06
dim sSINGLEQUOTE as string ' Used to define a single quote
sSINGLEQUOTE = CHR(39) ' Basic CHR function for a single quote
Form = oEvent.Source.Model.Parent
MyTextBox = Form.GetByName("txtFirstName")
oListBox = Form.GetByName("CustomerListBox")
MyText = oListBox.CurrentValue
strLength = Len(MyText)
MyPosition = InStr(MyText,srchString)
newString = Right(MyText,strLength - MyPosition)
newString = Trim(newString)
MyTextBox.setString(newString)
MyTextBox = Form.GetByName("txtLastName")
newString = Left(MyText,MyPosition - 1)
' Changed by Sliderule 2012-05-06 so variable will be surrounded by single quotes
newString = sSINGLEQUOTE & Trim(newString) & sSINGLEQUOTE
MyTextBox.setString(newString)
dim OStatement as object
dim searchCustomer as string
dim oResult
OStatement = Form.ActiveConnection.createStatement()
searchCustomer = "SELECT ""CustomerID"" FROM ""Customer"" WHERE ""LastName"" = " & newString
msgbox(searchCustomer)
oResult = OStatement.executeQuery(searchCustomer)
' Added by Sliderule 2012-05-06
' as a demonstration of retrieving values from Object oResult
' Loop through all Rows returned in the event more than one row returned
While oResult.next
' MsgBox oResult.GetString(1) 'Since first column returned use number 1
' Since first column returned use number 1 in GetString
MsgBox(oResult.GetString(1),0,"CustomerID field from DB")
Wend '<<< End of While Loop for oResultSet
End Sub |
Explanation: When using a Query . . . text strings MUST be surrounded by single quotes, for example, 'Smith' . . . however . . . StarBasic uses a single quote to indicate a comment on a line of code.
Therefore, I 'assigned' a variable ( sSINGLEQUOTE ) using the StarBasic function CHR and, surrounded the variable newString with the single quotes
I hope this helps, please be sure to let me / us know.
Sliderule
Thanks to add [Solved] in your first post Title ( edit button ) if your issue has been fixed / resolved. |
That did it (with an edit) Thanx |
|
| Back to top |
|
|
|
|
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum
|
Powered by phpBB © 2001, 2005 phpBB Group
|
