Click here to Skip to main content
15,860,859 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
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:

C#
//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
Updated 5-Mar-12 22:27pm
v2

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.
 
Share this answer
 
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...
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.
 
Share this answer
 
C#
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
C#
if (ds.Table.Count > 0)
{
TextBox1.Text = ds.Tables[0].Rows[0][1].ToString();

}
 
Share this answer
 
You are not opening the connection

C#
con.Open();
 
Share this answer
 

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



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900