How do I use a date value from a textbox in my insert query?

dfoutsindy · September 29, 2018, 4:14pm

Sorry if I need to post in a special forum - I’m been using open office for a little while & just found LibreOffice. I have a fairly complex data entry form & I want to copy a date from a textbox and include the date in my insert query to another table. Can’t get it to accept the date. The numeric data inserts successfully.

Thanks

DrawPageObj = ThisComponent.DrawPage	
DrawPageFrmFrom = DrawPageObj.Forms.getByName("frmTrips")	
iTripID = DrawPageFrmFrom.GetByName("numTripID")
iTripDate = DrawPageFrmFrom.GetByName("txtTripDate")

DrawPageFrmFacility = DrawPageObj.Forms.getByName("frmFacilities")	
iFacilityID = DrawPageFrmFacility.GetByName("numFacilityLnk")

oForm.updateRow
oForm.moveToInsertRow
oStatement = oForm.ActiveConnection.createStatement()
sColumns = ""
sColumns = sColumns & """TripID"""
sColumns = sColumns & ", ""FacilityID"""
'sColumns = sColumns & ", ""VisitDate"""
'sSQL = "INSERT INTO ""Visits"" (" & sColumns & ") VALUES ( " & Str(iTripID.Value) & " , " & Str(iFacilityID.Value) & " , ""# & (iTripDate.Text) & "#" )"	
sSQL = "INSERT INTO ""Visits"" (" & sColumns & ") VALUES ( " & Str(iTripID.Value) & " , " & Str(iFacilityID.Value) & " )"	
oStatement.executeUpdate( sSQL) 
oForm.reload
oForm.first

Ratslinger · September 29, 2018, 4:35pm

Hello,

Please be aware you are missing some important info. It always helps to include LO version(specific), OS & in the case of Base what DB you are using.

In this particular case, you are also missing what the field types are of the fields in the Visits table. I suspect it is a format problem you are having but need to know - Is VisitDate a Date type field? If not, what type is it set to?

Ratslinger · September 29, 2018, 5:55pm

Hello,

Notwithstanding the missing info, the most likely cause of your problem is the format of the field in your input to the Insert.

Now, if you are including all fields in Visits, you do not need to specify the column names; just the values you are inserting.

The “date” data you are using is text. If the VisitDate field in the Visits table is of type Date, the Insert statement expects the data to be in the format of yyyy-mm-dd. You can convert the data currently obtained from the text box with a Format command:

Format(iTripDate.Text, "'YYYY-MM-DD'")

Your naming conventions need attention. The routine seems to be only a part of the routine as it contains many lines of code which don’t make sense. Also many lines can be combined into less code.

dfoutsindy · September 29, 2018, 6:27pm

Thank you - not sure why I didn’t pick this out of the docs/posts I’ve found. Works great!
I just downloaded LibreOffice today - version 6.1.2.1. I will have a few more questions to build a small business app for my wife’s harp therapy business.
To clarify my code - I have three “forms” on one page - each with a table grid & a couple textboxes that track the currently selected record in the grid. A button pulls data from two of the forms and inserts a record in the third table (visits).

Ratslinger · September 29, 2018, 6:40pm

As you have been helped, please help others to know the question has been answered by clicking on the in upper left area of answer which satisfied the question.

Ratslinger · September 29, 2018, 8:36pm

@dfoutsindy After re-reading your form description in the comment, it reveals some confusion. Based upon the code presented, I would have guessed you had a form with a sub form. This is a very typical relational form. But you state you have three forms? And this third table seems to duplicate data - typically a relational DB no-no. Why duplicate data?

dfoutsindy · September 30, 2018, 3:07pm

The reason I’ve got some duplicated columns is purely convenience to enter data while I’m working on the automation. If it’s OK to continue the conversation here, I will ask a followup question. If I had my visits table linked to the facilities table thru the foreign key as a query in my visits form grid, would I still be able to make this insert into the visits table in my add-a-visit button code behind sub?

I previously created a form & grid with that query, but it won’t let me insert a new row in the grid.

(As I mentioned, I have just downloaded LibreOffice base & am using the native database - no remote database links as yet)
Thanks,
Dave

dfoutsindy · September 30, 2018, 3:13pm

Sorry - I noticed the request for more info include OS - I am using this on windows 10.

Ratslinger · September 30, 2018, 3:26pm

@dfoutsindy Please do not post this type of information as an answer - use a comment or add additional info to original question by editing & note edited info. Also do not check post as wiki when asking/answering questions. It helps no one.

This forum is designed to ask a question and get an answer. It is not meant for ‘conversations’ . Please ask as a new question.

Again, if original question has been answered, click on the in upper left area of answer which satisfied the question.

Ratslinger · September 30, 2018, 3:38pm

Also, please be clear with your question. The above seems to be asking about different items - linked tables & using macro; a form with a grid & query not being able to insert rows.

The macro depends upon the fields. The inserting rows is probably the manner in which the form is set up.

Be specific with information - a sample always helps. People answering questions have no idea what you are looking at if you don’t explain it.