oracle sql parameter is not working in vb.net

Question

I Have select satament in vb.net for populating some controls and also a treeview with date value the date value of treeview is in this formate DD MON YYYY and also the column format to which paramter is passes is in this format .I am trying to pass the parameter in this manner

cmd.CommandText = "SELECT 'At CFO Approval ' || count(*) ||' Cheques' || ' of Rs.' ||nvl(to_char(sum(bpv_amt),'9,999,999,999'),0) CFOApp from check_data where sta_flg = 0 and bpv_date='" & TreeView1.SelectedValue & "'"

But I am worried this is not working in any way what may b the isuue please help

score 0 · Answer 1 · edited May 23 '17 at 10:24

0

As usual, don't use string concatenation to build a sql text.
Use always parametrized queries. Why? See Little Bobby Tables

cmd.CommandText = "SELECT 'At CFO Approval ' || count(*) ||' Cheques' || " & _
                  "' of Rs.' ||nvl(to_char(sum(bpv_amt),'9,999,999,999'),0) CFOApp " & _
                  "from check_data where sta_flg = 0 and bpv_date=:bpvdate"

cmd.Parameters.AddWithValue(":bpvdate", Convert.ToDateTime(TreeView1.SelectedValue))

I assume that the treeview contains valid dates

If your format is DD MMM YYYY then you could try with this conversion (note three letters months)

Dim d as DateTime = DateTime.ParseExact(TreeView1.SelectedValue.ToString(), _
                    "dd MMM yyyy", CultureInfo.CurrentCulture);
cmd.Parameters.AddWithValue(":bpvdate", d)

If instead you have full month names then the conversion should be

Dim d as DateTime = DateTime.ParseExact(TreeView1.SelectedValue.ToString(), _
                    "dd MMMM yyyy", CultureInfo.CurrentCulture);

edited May 23 '17 at 10:24

Community

1
1

answered Aug 28 '12 at 08:13

Steve

213,761
22
232
286

`String was not recognized as a valid DateTime.` here is the error after your suggestion – user1583775 Aug 28 '12 at 09:01
As I have said, I assume that the treeview contains valid dates. So what is the value of SelectedValue? It is a datetime? – Steve Aug 28 '12 at 09:09
No I Already Wirte In my Question Format of Both is `DD MON YYYY` but error is showing – user1583775 Aug 28 '12 at 09:12
`Select to_char(BPV_DTE,'DD MON YYYY') BPV_DTE from DATE_BPV` this is populating treeview – user1583775 Aug 28 '12 at 09:13
sir what u say now what should i do – user1583775 Aug 28 '12 at 09:23
Added some variations on the date conversion, please check. – Steve Aug 28 '12 at 09:54
`String was not recognized as a valid DateTime.` Still this error – user1583775 Aug 28 '12 at 11:41
Can you give me an example data present in your treeview node? – Steve Aug 28 '12 at 12:43
For Example `23 AUG 2012` is the TreeNode Value – user1583775 Aug 29 '12 at 03:55
So AUG is August in "us-US" culture. CultureInfo.CurrentCulture should be also "us-US" to parse correctly this date. In alternative try with CultureInfo.InvariantCulture. Or `CulterInfo provider = new CultureInfo("us-US")` and pass the provider at ParseExact – Steve Aug 29 '12 at 08:05

Bob Jarvis - Слава Україні · Answer 2 · 2012-08-29T10:47:34.127

While I agree wholeheartedly with @Steve's comment about using parameterized queries, in this case I'll just suggest adding an appropriate TO_DATE call to try and get you going. Try this:

cmd.CommandText = "SELECT 'At CFO Approval ' || count(*) || " &
                  "' Cheques' || ' of Rs.' || " &
                  "nvl(to_char(sum(bpv_amt),'9,999,999,999'),0) CFOApp " &
                  "from check_data where sta_flg = 0 and " &
                  "TRUNC(bpv_date)=TO_DATE('" & TreeView1.SelectedValue & "', 'DD MON YYYY')"

EDIT

Added TRUNC() call to date comparison.

Tested as follows:

CREATE TABLE CHECK_DATA
  (BPV_AMT   NUMBER,
   STA_FLG   NUMBER,
   BPV_DATE  DATE);

INSERT INTO CHECK_DATA (BPV_AMT, STA_FLG, BPV_DATE) VALUES (100, 0, SYSDATE);
INSERT INTO CHECK_DATA (BPV_AMT, STA_FLG, BPV_DATE) VALUES (200, 0, SYSDATE-1);
INSERT INTO CHECK_DATA (BPV_AMT, STA_FLG, BPV_DATE) VALUES (300, 1, SYSDATE);
INSERT INTO CHECK_DATA (BPV_AMT, STA_FLG, BPV_DATE) VALUES (200, 0, SYSDATE);

Now, executing the query:

SELECT 'At CFO Approval ' || count(*) ||
       ' Cheques' || ' of Rs.' ||
       nvl(to_char(sum(bpv_amt),'9,999,999,999'),0) CFOApp
  from check_data
  where sta_flg = 0 AND 
        TRUNC(bpv_date) = TO_DATE('29 Aug 2012', 'DD MON YYYY');

results in

At CFO Approval 2 Cheques of Rs.           300

Share and enjoy.

@user1583775 - I changed the query, adding a TRUNC() call for the date comparison. Testing indicates this should work. Give it a try. — Bob Jarvis - Слава Україні, Aug 29 '12 at 10:43
`"SELECT 'At CFO Approval ' || count(*) ||' Cheques' || ' of Rs.' ||nvl(to_char(sum(bpv_amt),'9,999,999,999'),0) CFOApp from check_data where sta_flg = 0 and bpv_dte='23 AUG 2012'"` Sir this works but when i repalce the Date with "& TreeView1.SelectedVlaue &" then No Data Found — user1583775, Aug 29 '12 at 12:03

oracle sql parameter is not working in vb.net

2 Answers2