Problems with SqlDataSource Parameters

Hello!

I am pulling my hair out over this latest problem. I'm trying to do a simple SQL SELECT statement by taking values from textboxes and inserting them into the WHERE part of the statement. I'm using MySQL and have to use ? prefix for all inserts, that I have working by the way. Now for some reason I cannot get the values from the text boxes to replace the prefixes! Here is my code:

private void submitLogin(object source, EventArgs e) {if (isPassword(password.Text.ToString()) && isEmail(username.Text.ToString())) { ValidateCustomer.SelectParameters["c_email"].DefaultValue ="fish"; ValidateCustomer.SelectParameters["c_password"].DefaultValue ="cakes"; test_label.Text = ValidateCustomer.SelectCommand.ToString(); }else { test_label.Text ="Username or Password Incorrect"; }} 
<asp:SqlDataSource runat="server" ID="ValidateCustomer" ProviderName="System.Data.Odbc" ConnectionString ="<%$ connectionStrings:MySQLDatabase%>" SelectCommand ="SELECT * FROM customer_details WHERE (Customer_Email = ? AND Customer_Password = ?)"> <selectparameters> <asp:parameter name="c_email" type="String" /> <asp:parameter name="c_password" type="String" /> </selectparameters></asp:SqlDataSource>
 

I have omitted obvious code. The test label, when updated, shows the SQL statement but with the ?. I also use controlparameters that took the values directly from the textboxes but again this did not work. I'm really stuck with this one. Your help is much needed!

Cheers

Dan

i have to be honest with you i never write my sql in the little wizard because it exposes that to the user, make a stored procedure, moreover i dont know what (?) do. When you use a stored procedure the sqldatasource takes care of all the params for you

|||

MySQL supports both parameter syntaxes (@.name and ?) if you use the managed .NET connector (I believe 5.1.4 is the latest version). You can continue to use the ? syntax, but I would recommend against it. ? parameters depend upon position and order of declaration and use and don't allow reuse of a single parameter within a query.

That said, pulling the SelectCommand should never show the query with the parameters "replaced". Parameterized queries are not simple search/replaces, and even if they were, it should pull back the value you set the SelectCommand to, not the interally post-replaced value.

|||

Cheers for the replies folks!

I'm actually only doing this for a University Project so it doesn't really need to be water tight, but I get you point.

So does this mean then that I shouldn't actually see the values I think I should be seeing? Then the only way I know it works is if the select statement returns any values?

|||

You can put a breakpoint on the SqlDatasource_Selecting event, and look at the e.Command.Parameters collection if you want.

|||

So when I have the default values of the parameters set does the dataview update itself? If not then how do I enforce an update? At the moment it looks like it doesn't as I have the default values changed and when I try to return rows from the dataview I get none.

Cheers

Dan

|||

In VB this is how you would do it:

private void submitLogin(object source, EventArgs e) {
if (isPassword(password.Text.ToString()) && isEmail(username.Text.ToString()))
{

dim conn as new SqlConnection(System.Configuration.ConfigurationManager("MySQLDatabase").ToString)

dim cmd as new SqlCommand("SELECT COUNT(*) FROM customer_details WHERE (Customer_Email = @.email AND Customer_Password = @.password)",conn)

cmd.Parameters.Add("@.email",SqlDbType.NVarchar).Value=username.Text

cmd.Parameters.Add("@.password",SqlDbType.NVarchar).value=password.Text

conn.open

text_label.Text=cmd.ExecuteScalar.ToString

conn.close

}
else {
test_label.Text ="Username or Password Incorrect";
}
}

test_label should then either be a "0" if no records were found, or >=1 if 1 or more customer_detail records had a username/password that matched what was typed.

Running that through a VB -> C# converter gives:

SqlConnection conn = new SqlConnection(System.Configuration.ConfigurationManager("MySQLDatabase").ToString);

SqlCommand cmd = new SqlCommand("SELECT COUNT(*) FROM customer_details WHERE (Customer_Email = @.email AND Customer_Password = @.password)", conn);

cmd.Parameters.Add("@.email", SqlDbType.NVarchar).Value = username.Text;

cmd.Parameters.Add("@.password", SqlDbType.NVarchar).value = password.Text;

conn.open();

text_label.Text = cmd.ExecuteScalar.ToString;

conn.close();

|||

Thanks for the reply.

I'm using Web Controls so this solution would mean changing a lot of code. Is there anyway I could accomplish this but within the limits of my SqlDataSource and DataView controls? When I try to do the SqlDataSource.Select() method I get errors.

Cheers

Dan

sql