Beginners SqlDataSource SelectCommand Question

Hi folks,

I'm having problems using the SqlDataSource to return certain values from a SQL database. I have two DropDownLists. When the first DDL's selected index is changed, I need to take the value (an int id) from the field, use the value to look up a string in a different colum in the table for that record id, which should then be used to automatically select a value from the second DDL. I think my problem is I'm not sure how to get the SelectCommand to actually return the value, let alone in string form!

Here is my code:

{
string selectedProject = ProjectDDL.SelectedValue;
SqlDataSource temp = new SqlDataSource();
temp.ConnectionString = rootWebConfig.ConnectionStrings.ConnectionStrings["DBConnectionString"].ToString();

temp.SelectParameters.Add("id", selectedProject);
temp.SelectCommand = "SELECT [username] FROM dbo.projects WHERE id = @.id";

//temp.Select(); ?

//AssigneeField.SelectedValue = string returned from Select Statement!
}

I'd appreciate it if somone could point me in the right direction?

Thanks,

Ally

Hi,

you can either use a command object with a dataadaptor or a datareader to retrieve data from your command
assuming you are only returning one String value, using a datareader would be better on your system:

String result = "";

SqlCommand command = New SqlCommand( _
"SELECT CategoryID, CategoryName FROM dbo.Categories;" & _
"SELECT EmployeeID, LastName FROM dbo.Employees", connection);
command.parameters.add(...);
connection.Open();

sqldatareader reader = command.executereader();
Do While reader.Read()
<<<<<Do your processing with the data here>>>>>
result = reader(<columnNum>);
Loop

reader.Close();
return result

i'm sorry about the syntax errors as im not c# trained...
Hope this helps...

|||

Excellent, exactly what I needed, thanks! I was having problems with SqlDataReader.ExecuteReader() constantly returning anException:

System.InvalidOperationException: Invalid attempt to read when no data is present."

I'd accidently omitted the inital SqlDataReader.Read() call, fought with it for ages until I realised the reader doesn't move on to the first record until SqlDataRead.Read() is called for the first time, makes sense I guess! All working now :)

Thanks again,

Ally