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

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.first

edit retag close merge delete

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?

( 2018-09-29 18:35:02 +0200 )edit

Sort by » oldest newest most voted

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.

more

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).

( 2018-09-29 20:27:46 +0200 )edit

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.

( 2018-09-29 20:40:27 +0200 )edit

@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?

( 2018-09-29 22:36:10 +0200 )edit

This post is a wiki. Anyone with karma >75 is welcome to improve it.

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

more

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

( 2018-09-30 17:13:29 +0200 )edit

@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.

( 2018-09-30 17:26:02 +0200 )edit

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.

( 2018-09-30 17:38:46 +0200 )edit