Last post Jan 17, 2011 04:08 AM by hemant.yadav
Jan 15, 2011 05:35 PM|help527|LINK
Hope u all are doing well.
I am a beginner. I am try to develop a dynamic web page which fetches data from 10g database and display data in grid view.
So i have added a dataset and connected to database on remote server through ODBC Connection.
I want to display data based on from_date and to_date on a table .
table have one date field
query is like "select column1,column2 from table where collectionDate >=:FromDate and collectionDate<=:ToDate"
when i have added this query to querybuilder in vs 2005 it is giving error as ':' Format not maching or not supported.
I want to display fetched data in gridview using querystring from control(Textbox).
But I was not able to filter the table . Please help me regarding this.
oracle Query for filtering data in query builder
Jan 17, 2011 02:37 AM|hemant.yadav|LINK
Try following query
select column1,column2 from table where collectionDate >=FromDate and collectionDate<=ToDate
Colon is not supporting sql server
Jan 17, 2011 03:28 AM|help527|LINK
Thank you for replying .
When i tried without Colon
it is telling that "Invalid Identifier"
Jan 17, 2011 03:36 AM|hemant.yadav|LINK
Can you post your code???
Or can you post your original query which you writing to fetch record from database?
Jan 17, 2011 03:42 AM|help527|LINK
Following is the query.When iam excuting this query in TOAD it works but in query bulider it giving error .
Select rpl_CONTAINER_BICCODE as ContainerNo,TOH_SO_NUMBER as SO#,TOH_SO_ITEMNO as SO_Item ,CONTAINER_SLCODE as Shipping_Line ,CONTAINER_TYPECODE as Container_Size,PO_NUMBER as PO_Number,CNTMAT_MATERIAL AS Material,sum(CNTMAT_QTY) as Quantity,COLLECTION_DATE
as Container_Collection_Date,CNT_IN_TBL.CMTO_TIMESTAMP AS Empty_IN_Date,CNT_EMPTYOUT_TBL.CMTO_TIMESTAMP AS Empty_Out_Date, CONTAINER_LINEARDATE as Liner_Out_Date,CNT_EMPTYBOUT_TBL.CMTO_TIMESTAMP AS Liner_In_Date,TOH_TONO as Stuffed_in_TONO,CNT_STUFFIN_TBL.CMTO_TIMESTAMP
AS Stuffed_In_Date, CNT_STUFFOUT_TBL.CMTO_TIMESTAMP AS Shipped_Out_Date,max(GR_DATE) as GI_DATA,min(GI_DATE) as GR_DATA,FOB_DATE from TBL_CONTAINER left JOIN RPL_COLLECTIONDATA ON RPL_CONTAINER_BICCODE=CONTAINER_BICCODE LEFT join tbl_containerassignment on
container_id=ca_container_id LEFT JOIN TBL_CONTAINERMATERIAL ON CA_ID=CNTMAT_CAID LEFT join tbl_shippingdocktansferorders on tbl_containerassignment.CA_SDTOID=tbl_shippingdocktansferorders.SDTO_ID LEFT join tbl_transferorderheader on tbl_shippingdocktansferorders.SDTO_TOHID=toh_id
LEFT join SAP_DATA ON (TOH_SO_NUMBER=SAP_DATA.GR_SO_NUMBER AND TOH_SO_ITEMNO=GR_SO_ITEM) LEFT JOIN TBL_CONTAINERMOVEMENT CNT_IN_TBL ON CONTAINER_ID = CNT_IN_TBL.CMTO_CONTAINERID AND CNT_IN_TBL.CMTO_MOVEMENTTYPE = 1 LEFT JOIN TBL_CONTAINERMOVEMENT CNT_EMPTYBOUT_TBL
ON CONTAINER_ID = CNT_EMPTYBOUT_TBL.CMTO_CONTAINERID AND CNT_EMPTYBOUT_TBL.CMTO_SRCLOCATIONID = 3600 LEFT JOIN TBL_CONTAINERMOVEMENT CNT_EMPTYOUT_TBL ON CONTAINER_ID = CNT_EMPTYOUT_TBL.CMTO_CONTAINERID AND CNT_EMPTYOUT_TBL.CMTO_MOVEMENTTYPE = 3 LEFT JOIN TBL_CONTAINERMOVEMENT
CNT_STUFFIN_TBL ON CONTAINER_ID = CNT_STUFFIN_TBL.CMTO_CONTAINERID AND CNT_STUFFIN_TBL.CMTO_MOVEMENTTYPE = 5 LEFT JOIN TBL_CONTAINERMOVEMENT CNT_STUFFOUT_TBL ON CONTAINER_ID = CNT_STUFFOUT_TBL.CMTO_CONTAINERID AND CNT_STUFFOUT_TBL.CMTO_MOVEMENTTYPE = 6 Where
COLLECTION_DATE>=:FROMdate AND COLLECTION_DATE<=:TODATE group by rpl_container_biccode,toh_so_number,toh_so_itemno,container_SLcode,CONTAINER_TYPECODE,PO_NUMBER,CNTMAT_MATERIAL,COLLECTION_DATE,CNT_IN_TBL.CMTO_TIMESTAMP ,CNT_EMPTYOUT_TBL.CMTO_TIMESTAMP , CONTAINER_LINEARDATE,CNT_EMPTYBOUT_TBL.CMTO_TIMESTAMP
,TOH_TONO,CNT_STUFFIN_TBL.CMTO_TIMESTAMP , CNT_STUFFOUT_TBL.CMTO_TIMESTAMP ,GI_DATE,GR_DATE,FOB_DATE ORDER BY COLLECTION_DATE ASC
Jan 17, 2011 03:51 AM|hemant.yadav|LINK
Have you tried this query into Sql server?? If not than check it on Sql server.
Jan 17, 2011 03:56 AM|help527|LINK
Yes I have tried in SQL server it works fine.
i used objectdatasource and i was able to fetch records based on dates . When i am trying to do same using oracle database it gives error.
Jan 17, 2011 04:08 AM|hemant.yadav|LINK
Oracle database has different syntex that why its giving you error.
Sql Server and Oracle are totally different. Oracle uses pure Sql syntex so you have to convert it to that.