on 04-28-2008 7:22 AM
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.
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.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
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.
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
User | Count |
---|---|
88 | |
7 | |
6 | |
4 | |
3 | |
3 | |
3 | |
3 | |
3 | |
2 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.