Click here to Skip to main content
13,198,727 members (41,250 online)
Rate this:
 
Please Sign up or sign in to vote.
See more:
Hi Im getting this error when i add a WHERE Clause in my Query? if i remove the where clause it selects the data correctly but obviously not the record i want.

Here's the code:

//fill data
        string connectionString = System.Configuration.ConfigurationManager.ConnectionStrings["ConnectionString2"].ConnectionString;
        string selectSQL = "Select a.CIS FROM ptCIS_Person a, ptCIS_Tel b, ptCIS_ProductMember c where a.CIS = b.CIS and b.CIS = c.CIS and c.MPAcc = '70046498'";
 
        OdbcConnection con = new OdbcConnection(connectionString);
        OdbcCommand cmd = new OdbcCommand(selectSQL, con);
        OdbcDataAdapter adapter = new OdbcDataAdapter(cmd);
        DataSet ds = new DataSet();
 
        adapter.Fill(ds, "member");
        TextBox1.Text = ds.Tables[0].Rows[0][1].ToString();
 
        con.Close();
        con.Dispose();
        //end


If i add if (ds.Tables[0].Rows.Count > 0) i avoid the error but that doesn't solve my problem because when i run the same query in SQL i get the correct record...

Any help plz
Posted 5-Mar-12 22:20pm
Updated 5-Mar-12 22:27pm
member6016.1K
v2
Rate this: bad
 
good
Please Sign up or sign in to vote.

Solution 1

You should carefully compare 'the same query in SQL' with the exact content of the selectSQL string.
It is very likely they are actually different, that is your problem.
  Permalink  
Comments
man_in_marak 6-Mar-12 4:47am
   
Thanks bro,

That was my original thought as well, I just ran it again no luck still getting "There is no row at position 0." copied and pasted the query code exactly, tried everything?

Can't explain it myself...
Rate this: bad
 
good
Please Sign up or sign in to vote.

Solution 2

This 'error' means that you are returning 0 rows, i.e. the query doesn't match. It's almost certain that you have made a mistake and the where clause is not catching anything. Check it really carefully, and try running it by copy-and-paste in an immediate SQL window. (On the same database!) For example, I'd expect the key you're looking up to be in table 'a', not 'c'.

If that doesn't expose a mistake then check the permissions of the user you're using from your application, though a permissions violation should throw an exception instead of just returning zero rows.

You should probably also rewrite that query as an inner join or a left join, and not to use one character aliases, for clarity.
  Permalink  
Rate this: bad
 
good
Please Sign up or sign in to vote.

Solution 3

        string selectSQL = "Select a.CIS FROM ptCIS_Person a, ptCIS_Tel b, ptCIS_ProductMember c where a.CIS = b.CIS and b.CIS = c.CIS and c.MPAcc = '"+70046498+"'";
 


and then check
if (ds.Table.Count > 0)
{
TextBox1.Text = ds.Tables[0].Rows[0][1].ToString();
 
}
  Permalink  
Rate this: bad
 
good
Please Sign up or sign in to vote.

Solution 4

You are not opening the connection

con.Open();
  Permalink  

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
Top Experts
Last 24hrsThis month


Advertise | Privacy |
Web02 | 2.8.171020.1 | Last Updated 6 Mar 2012
Copyright © CodeProject, 1999-2017
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100