cancel
Showing results for 
Search instead for 
Did you mean: 

How to pass Date to an SQL statement

Former Member
0 Kudos

Hi all

I have an edit text field in a form which is bounded to a date type database field using dbDataSource.

I want to pass this value to a SQL string to search for records from Table ORDR filed name DocDuedate.

my first edit box collects the "from" value which is as below

my edittet.string = "06/03/08"

I have another text box which collects the "to" value for search

When I pass these values to SQL the executs, but does not return any records eventhough records do exist.

Can some one help with this problem(Im using SQL 2000)

Thanks,

NB.

Accepted Solutions (1)

Accepted Solutions (1)

Former Member
0 Kudos

Hi Nirmala,

SBO dates are stored as YYYY-MM-DD in SQL so comparing to DD/MM/YY values will return no matches. Try getting the date value from the datasource rather than the screen item e.g. datasource.GetValue(field_id,0). This retrieves the value in YYYYMMDD format I think. You can then use something like the following to return records:

"SELECT whatever FROM ORDR WHERE DocDueDate >= '" & datasource.GetValue(fromfield_id,0) & "' AND DocDueDate <= '" & datasource.GetValue(tofield_id,0) & "'"

Note there are single quotation marks around the date values to treat them as strings.

Hope this is of some use.

Regards,

Andrew.

Former Member
0 Kudos

Hi,

Thanks for the reply.As you metioned I did change the value to the required fromat of "yyyy/MM/dd"

The query executes and returns proper results.

However I have to get the value from the text boxes only as the values are filled in by user and based on these values I fill the grid(based on Query).

Now, the problem is , if I enter value like "01/03/08" to 12/03/08 alls fine

The moment I enter value above 13 ie "13/03/08"

i get an error saying

"String was not recognized as a valid DateTime."

the text box is bound to a db which is date as per B1

this is the line that throws the error

DateTime DT = System.DateTime.Parse(edittext.String);

thanks

NB

Former Member
0 Kudos

Hi there,

I'm guessing you're getting the error because the first part of your date value (e.g. 13) is being treated as a month value rather than a day. What about using edittext.Value instead of .String? This should give you the value in YYYYMMDD format, you could then just use this string value in your SQL statement?

The following sample code worked fine for me:

Dim oForm As SAPbouiCOM.Form

Dim oEdit As SAPbouiCOM.EditText

Dim oRS As SAPbobsCOM.Recordset

Dim sSQL As String

'Get DocDate value from Sales Order screen to use as an example.

oForm = oApp.Forms.GetForm(pVal.FormTypeEx, pVal.FormTypeCount)

oEdit = oForm.Items.Item("10").Specific

oRS = oCmp.GetBusinessObject(SAPbobsCOM.BoObjectTypes.BoRecordset)

sSQL = "SELECT COUNT(DocNum) AS OrderCount FROM ORDR WHERE DocDate = '" & oEdit.Value & "'"

oRS.DoQuery(sSQL)

oApp.MessageBox("Count: " & oRS.Fields.Item("OrderCount").Value)

oRS = Nothing

If I had of used oEdit.String then the above returns a conversion error.

Regards,

Andrew.

Former Member
0 Kudos

Dear Andrew,

Your last post did the trick.

After spending around nearly two day converting and reconverting from string to date and vice versa, it was much easier just passing the string.

Thanks for your help, and your inputs

NB.

Edited by: Nirmala Bodhak on Apr 29, 2008 11:26 AM

Former Member
0 Kudos

Hi all,

we had the same problem and I just want you to know: I the user changed the displa of the date, you'll get error again. Normally, the date is displayed for example like 28.02.2008. In your query this will work fine, but if the user changes it, you'll get errors.

Use this function for converting:

        Public Shared Function ToDate(ByVal strValue As String) As Date
            Return DateTime.ParseExact(strValue, "yyyyMMdd", Nothing)
        End Function

Best regards,

Manuel Marhold

SoftChip EDV-Systeme GmbH

Edited by: Manuel Marhold on Apr 29, 2008 3:43 PM

Former Member
0 Kudos

Hi Manuel ,

What you suggest is absolutey right.

Thanks for bringing it to my notice, it was quite helpfull.

Thanks again

NB

Answers (0)