Assigning Values from a dropdown list.

[evanguiton]

I am currently doing a college project using asp and c#. I have the sql database connected and I am able to write to it but i cant read from the database the way I want.

So I have a dropdown list populated by dates from my database and I want to populate text boxes relating to the selected date.

I have attached an image of my GUI to give you a better understanding and I have put the code below.

using System;
using System.Collections;
using System.Configuration;
using System.Data;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;
using System.Web.Configuration;
using System.Data.Common;
using System.Data.SqlClient;

public partial class Staff_Home : System.Web.UI.Page
{

    string sun1;
    string sun2;
    string WeekEnd;
        

    protected void Page_Load(object sender, EventArgs e)
    {

    }
    protected void DropDownList1_SelectedIndexChanged(object sender, EventArgs e)
    {
        WeekEnd = (DropDownList1.Text);
        AssignValues();
        TextBox1.Text = sun1;
    }

    
    private void AssignValues()
    {

        SqlConnection conn = new SqlConnection(WebConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString);

        //Execute SQL Statement
        SqlCommand comm = new SqlCommand("SELECT * FROM Schedule1 WHERE [WeekEnd] = @[WeekEnd]", conn);

        //Set Parameters
       
        
       // comm.Parameters.Add("@[WeekEnd]", SqlDbType.Int);
        comm.Parameters.Add("@[WeekEnd]", SqlDbType.Text);


        comm.Parameters["@[WeekEnd]"].Value = WeekEnd;



        //Open Connection
        conn.Open();

        //Start SQL Reader
        SqlDataReader reader = comm.ExecuteReader();
        //SqlDataReader reader = comm.BeginExecuteReader();
        while (reader.Read())
        {

            sun1 = (string)reader["sun1"];
            sun2 = (string)reader["sun2"];
        }
        //Close Reader
        reader.Close();

        //Close Connection
        conn.Close();
    }
    protected void SqlDataSource2_Selecting(object sender, SqlDataSourceSelectingEventArgs e)
    {

    }
}

[apegram]

Are you getting an exception or just no data at all? In a hastily constructed scenario, I got an exception when using brackets the way you did around my parameter name. If you are getting an exception, consider changing @[WeekEnd] to @WeekEnd throughout the function. I cannot find the documentation of it, but it could be that SqlParameter names do not support non-alphanumeric characters (possibly excluding underscores). The other possibility is that I got a random, unrelated exception, except that the program executed without difficulty without the square brackets.

Also check that your SqlDbType argument matches the data. For instance, I doubt WeekEnd is a Text field, it may be something more along the lines of VarChar.

[evanguiton]

the error is highlighting this line of code

SqlDataReader reader = comm.ExecuteReader();

and the error is this

Incorrect syntax near 'text'.
Must declare the scalar variable "@".

when i take out the [] brackets I get this exception on the same line of code

The data types nchar and text are incompatible in the equal to operator.

probably a simple error but its really bugging me.

[apegram]

That matches my results. Remove the brackets on the parameter as I suggested and test it again.

SqlCommand comm = new SqlCommand("SELECT * FROM Schedule1 WHERE [WeekEnd] = @WeekEnd", conn);   
        comm.Parameters.Add("@WeekEnd", SqlDbType.Text);
        comm.Parameters["@WeekEnd"].Value = WeekEnd;

Edit: Ah, I see now you're getting a second error. Go back to the second thing I mentioned, verify WeekEnd is actually a Text field in the database. It is more likely to be nchar, varchar or some other such storage format. And, to be honest, if it is Text, it probably shouldn't be.

[evanguiton]

That matches my results. Remove the brackets on the parameter as I suggested and test it again.

SqlCommand comm = new SqlCommand("SELECT * FROM Schedule1 WHERE [WeekEnd] = @WeekEnd", conn);   
        comm.Parameters.Add("@WeekEnd", SqlDbType.Text);
        comm.Parameters["@WeekEnd"].Value = WeekEnd;

Edit: Ah, I see now you're getting a second error. Go back to the second thing I mentioned, verify WeekEnd is actually a Text field in the database. It is more likely to be nchar, varchar or some other such storage format. And, to be honest, if it is Text, it probably shouldn't be.

It is stored as a varchar field in the database it saves in the format and populates the dropdownlist like that to.

[apegram]

If it is a varchar in the database, change your code from SqlDbType.Text to SqlDbType.VarChar.

[evanguiton]

If it is a varchar in the database, change your code from SqlDbType.Text to SqlDbType.VarChar.

It worked:) thanks so much for everything.