OpenOffice.org Forum at OOoForum.orgThe OpenOffice.org Forum
 
 [Home]   [FAQ]   [Search]   [Memberlist]   [Usergroups]   [Register
 [Profile]   [Log in to check your private messages]   [Log in

[Solved] SQL Macros and those darn runtime errors

 
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Base
View previous topic :: View next topic  
Author Message
aftershock
Newbie
Newbie


Joined: 06 May 2012
Posts: 3

PostPosted: Sun May 06, 2012 6:43 am    Post subject: [Solved] SQL Macros and those darn runtime errors Reply with quote

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
View user's profile Send private message
Villeroy
Super User
Super User


Joined: 04 Oct 2004
Posts: 10106
Location: Germany

PostPosted: Sun May 06, 2012 7:02 am    Post subject: Reply with quote

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 https://forum.openoffice.org
Back to top
View user's profile Send private message
Sliderule
Super User
Super User


Joined: 29 May 2004
Posts: 2499
Location: 3rd Rock From The Sun

PostPosted: Sun May 06, 2012 7:37 am    Post subject: Reply with quote

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 Smile

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
View user's profile Send private message
aftershock
Newbie
Newbie


Joined: 06 May 2012
Posts: 3

PostPosted: Mon May 07, 2012 2:40 am    Post subject: Reply with quote

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
View user's profile Send private message
aftershock
Newbie
Newbie


Joined: 06 May 2012
Posts: 3

PostPosted: Mon May 07, 2012 2:42 am    Post subject: Reply with quote

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 Smile

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
View user's profile Send private message
Display posts from previous:   
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Base All times are GMT - 8 Hours
Page 1 of 1

 
Jump to:  
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