CODEWITHSUNDEEP
c#sqldatabase-connectionresultset
Tim
Tim

Reputation: 331

Executing a data command query through ASP.NET

I'm trying to Execute a data command query against a local database but think I may have mixed up or left out some syntax...I have been trying to follow along with MSDN's tutorial but the portion of code they're showing isn't making too much sense to me. I have opened the connection with the DB, but I can't figure out how to pass a parameter from a user input textbox and use a select statement with that parameter which also uses a LIKE statement to return a resultset which I can then cycle through the records that match. 1) After hitting my search button, it's saying that I've got an invalid object name 'name' which is one issue. 2) Could you also take a look at my code and let me know if I'm going about trying to execute this select statement w/ a parameter and return a resultset wrong?

Here's what I have so far

 System.Data.SqlClient.SqlDataReader dReader;

    protected void  btnFirst_Click(object sender, EventArgs e)
 {
     using (var conn = new System.Data.SqlClient.SqlConnection(@"Server=LOCALHOST;           Database=Student;Trusted_Connection=True;"))
{
    try
    {
        conn.Open();
    }
    catch (Exception ex)
    {
        lblSearch.Text = "connection problem!";
    }
    using (var Sqlcmd = new System.Data.SqlClient.SqlCommand("Delete From Name", conn))
    {
        cmd.ExecuteNonQuery();
    }

    using (var Sqlcmd = new System.Data.SqlClient.SqlCommand("SELECT Name, Address from Student where Name LIKE '%@Name%'", conn))
    {
        cmd.Parameters["@Name"].Value = txtSearch.Text; 
    }
    lblFirst.Text = "@Name";

    String s = "";
    while (dReader.Read())
    {
        s+= dReader["@Name"].ToString();
    }
    txtSearch.Text = s;
    dReader.Close();


}
}

Upvotes: 0

Views: 923

Answers (1)

Steve
Steve

Reputation: 216343

There is no execution of the command, where do you get that reader initialized ? And the using around the SqlCommand is closed too early. Did you miss some code?.

Then I would remove the wildcards after the LIKE clause and add them to the parameter value.
Finally, the SqlDataReader works on the column names and not on the parameters.
So you need to address the column required with its real name

using (var Sqlcmd = new SqlCommand("SELECT Name, Address from Student " + 
                                   "where Name LIKE @Name", conn))
{
    cmd.Parameters.AddWithValue("@Name", "%" + txtSearch.Text "%");  
    using(SqlDataReader dReader = cmd.ExecuteReader())
    {
        StringBuilder s = new StringBuilder(1024);
        while (dReader.Read())
        {
            s.Append(dReader["Name"].ToString());
        }
        txtSearch.Text = s.ToString();
    }
 }

If you have a large amout of records returned I suggest to use a StringBuilder instead of a string to avoid the creation of a new copy of the string s at every loop

Another point that I suggest to change is the try/catch around the opening of the connection. If you do not signal what is the problem, you will have great difficulty to fix things. If you don't want, at this point build a log infrastructure, at least report, in the label, the exception message

OK, sorry if I am boring, but there is another piece of code that is not clear:

using (var Sqlcmd = new System.Data.SqlClient.SqlCommand("Delete From Name", conn))
{
    cmd.ExecuteNonQuery();
}

This code deletes EVERYTHING from a table named Name, it seems wrong given the context.

EDIT Following your comment below

using(SqlDataReader dReader = cmd.ExecuteReader())
{
     // First item displayed on the textbox
     if(dReader.HasRows)
          txtSearch.Text = dReader["Name"].ToString();

     // Continue looping on every record and copy the name field in a list
     List<string> names = new List<string>()
     while (dReader.Read())
     {
          names.Add(dReader["Name"].ToString());
     }

     // Here you have you list of names in memory to be used where you need it
}

Upvotes: 2

Related Questions