none
Reading SQL Rows RRS feed

  • Question

  • How do i get this to display the information contained within a Row and not a column? as of now if i enter information like my name into the search box it does nothing but if i enter the column name it returns information in within the database.
    DataRow[] returnRows = ds.Tables[0].Select("First_Name=" + searchOut);
                        returnRows = ds.Tables[0].Select("Last_Name=" + searchOut);

    Friday, May 19, 2017 9:40 PM

Answers

  • Then this should work for you ... and if it still doesn't, I can't think what might be wrong (this is, of course within your "using (SqlConnection..." block:

    if (tbSearch.Text.Length >= 1)
    {
        string sql = "SELECT * FROM clouddata WHERE First_Name = @parm";
        SqlCommand MyGetCommand = new SqlCommand(sql, conn);
        // I just had a thought, perhaps this value needs to be trimmed
        // so, perhaps the parameter should be set like this instead
        MyGetCommand.Parameters.AddWithValue("@parm", tbSearch.Text.Trim());
        da = new SqlDataAdapter(MyGetCommand);
        DataSet ds = new DataSet();
    
        da.Fill(ds, "MyDataBinding");
        dataGrid2.DataContext = ds.Tables[0];
    }
    
    conn.Close();
    

    Another thought is that the column in the database is a char(xxx), instead of a varchar(xxx), in which case that needs to be taken into account also (because a char() will pad the end of the value with spaces). If so, then your SELECT will need to be changed to this:

    string sql = "SELECT * FROM clouddata WHERE RTRIM(First_Name) = @parm";

    I bet that's the problem ...  having to do with non-trimmed spaces in one place or another.


    ~~Bonnie DeWitt [C# MVP]

    http://geek-goddess-bonnie.blogspot.com

    • Marked as answer by RattlerrFx Tuesday, May 23, 2017 4:59 PM
    Tuesday, May 23, 2017 4:52 PM

All replies

  •  private void button_Click(object sender, RoutedEventArgs e)
            {
                
                using (SqlConnection conn = new SqlConnection())
                {
                    int result = 0;
                    string searchOut = tbSearch.Text;
                    
                    
                    //using (SqlConnection con = new SqlConnection())
                    conn.ConnectionString = " Data Source=(LocalDB)\\MSSQLLocalDB;Database=cloudyhamdb.mdf;Trusted_Connection=True;AttachDbFilename =|DataDirectory|cloudyhamdb.mdf; Integrated Security = True;";
                    //conn.ConnectionString = "Server=tcp:uhc.database.windows.net,1433;Initial Catalog=universalhamcloudDB;Persist Security Info=False;User ID=gerry;Password=HuNt3r90@@_#;MultipleActiveResultSets=False;Encrypt=True;TrustServerCertificate=False;Connection Timeout=30;";
                    SqlCommand MyCommand = new SqlCommand("INSERT INTO clouddata " + " (First_Name, Last_Name, Grid_Square, Country, State, Call_Sign, Date_Time, Mode, Power)" + " Values (@First_Name, @Last_Name, @Grid_Square, @Country, @State, @Call_Sign, @Date_Time, @Mode, @Power)", conn);
                    MyCommand.Parameters.Add("@First_Name", System.Data.SqlDbType.Text);
                    MyCommand.Parameters.Add("@Last_Name", System.Data.SqlDbType.Text);
                    MyCommand.Parameters.Add("@Grid_Square", System.Data.SqlDbType.Text);
                    MyCommand.Parameters.Add("@Country", System.Data.SqlDbType.Text);
                    MyCommand.Parameters.Add("@State", System.Data.SqlDbType.Text);
                    MyCommand.Parameters.Add("@Call_Sign", System.Data.SqlDbType.Text);
                    MyCommand.Parameters.Add("@Date_Time", System.Data.SqlDbType.SmallDateTime);
                    MyCommand.Parameters.Add("@Power", System.Data.SqlDbType.Text);
                    MyCommand.Parameters.Add("@Mode", System.Data.SqlDbType.Text);
    
                    MyCommand.Parameters["@First_Name"].Value = Convert.ToString(tbFirstName.Text);
                    MyCommand.Parameters["@Last_Name"].Value = Convert.ToString(tbLastName.Text);
                    MyCommand.Parameters["@Grid_Square"].Value = Convert.ToString(tbGridSquare.Text);
                    MyCommand.Parameters["@Country"].Value = Convert.ToString(tbCountry.Text);
                    MyCommand.Parameters["@State"].Value = Convert.ToString(tbState.Text);
                    MyCommand.Parameters["@Call_Sign"].Value = Convert.ToString(tbCallSign.Text);
                    MyCommand.Parameters["@Date_Time"].Value = Convert.ToDateTime(DateTime.Now);
                    MyCommand.Parameters["@Power"].Value = Convert.ToString(tbPower.Text);
                    MyCommand.Parameters["@Mode"].Value = Convert.ToString(tbMode.Text);
    
                     conn.Open();
                    MyCommand.ExecuteNonQuery();
                    
    
                    SqlDataAdapter da = new SqlDataAdapter();
    
                    da = new SqlDataAdapter("Select * FROM clouddata", conn);
                    DataSet ds = new DataSet();
    
                    if (tbSearch.Text.Length >= 1)
                    {
                        da.Fill(ds, "MyDataBinding");
                        DataRow[] returnRows = ds.Tables[0].Select("First_Name=" + searchOut);
                        returnRows = ds.Tables[0].Select("Last_Name=" + searchOut);
                        result = returnRows.Length;
                        dataGrid2.DataContext = ds;
                        int results = MyCommand.ExecuteNonQuery();
    
                    }
                    
                    conn.Close();
                }
    
            }


    • Edited by RattlerrFx Friday, May 19, 2017 9:43 PM
    Friday, May 19, 2017 9:42 PM
  • I'm a little confused by your code examples and your explanation of what you want, but I'm assuming that you want to display the information for a specific person in a Grid? If so, then I think you want code that is more like this:

    if (tbSearch.Text.Length >= 1)
    {
        string sql = "SELECT * FROM clouddata WHERE First_Name = @parm OR Last_Name = @parm";
        SqlCommand MyGetCommand = new SqlCommand(sql, conn);
        da = new SqlDataAdapter(MyGetCommand);
        MyGetCommand.Parameters.AddWithValue("@parm", searchout);
        DataSet ds = new DataSet();
    
        da.Fill(ds, "MyDataBinding");
        dataGrid2.DataContext = ds;
    }
    
    conn.Close();
    


    ~~Bonnie DeWitt [C# MVP]

    http://geek-goddess-bonnie.blogspot.com

    Saturday, May 20, 2017 5:01 PM
  • Give this a try:

            private void button_Click(object sender, RoutedEventArgs e)
            {
                using (SqlConnection conn = new SqlConnection())
                {
                    int result = 0;
                    string searchOut = tbSearch.Text;
    
                    conn.ConnectionString = "Data Source=(LocalDB)\\MSSQLLocalDB;Database=cloudyhamdb.mdf;Trusted_Connection=True;AttachDbFilename =|DataDirectory|cloudyhamdb.mdf; Integrated Security = True;";
                    conn.Open();
    
                    SqlDataAdapter da = new SqlDataAdapter();
                    DataSet ds = new DataSet();
                    DataTable dt = new DataTable();
    
                    if (searchOut.Length >= 1)
                    {
                        da.SelectCommand = new SqlCommand($"SELECT * FROM clouddata WHERE First_Name = '{searchOut}' OR Last_Name = '{searchOut}'", conn);
                        da.Fill(ds, "clouddata");
                        dt = ds.Tables["clouddata"];
                        result = dt.Rows.Count;
                        dataGrid2.ItemSource = dt;
                    }
                    conn.Close();
                }
            }
    Also make sure your dataGrid2's AutoGenerateColumns="true"

    PS: Kindly close threads by marking answers where appropriate. Thank you.


    • Edited by Usman Mohammed Saturday, May 20, 2017 5:28 PM
    • Marked as answer by RattlerrFx Sunday, May 21, 2017 3:21 AM
    • Unmarked as answer by RattlerrFx Sunday, May 21, 2017 3:27 AM
    Saturday, May 20, 2017 5:24 PM
  • Yes, @Usman ... you caught the one thing I missed (and that was to use the DataTable for the grid's DataContext, not the DataSet).  Sorry, my bad. And you don't need a separate DataTable either. You could do it simply like this:

    dataGrid2.DataContext =ds.Tables[0];

    And you should use the Parameters, as I did, rather than inline them in the SqlCommand, as @Usman did. Parameters are almost *always* the preferred way!


    ~~Bonnie DeWitt [C# MVP]

    http://geek-goddess-bonnie.blogspot.com

    Saturday, May 20, 2017 11:37 PM
  • I am getting this error after trying the code above in these two ways:

    dataGrid2.DataContext = ds.Tables[0].Rows[0];
    or
    dataGrid2.DataContext = ds.Tables[0];

    System.Data.EvaluateException: 'Cannot find column [Gerry].' Any ideas on how to fix it is greatly appreciated :)

    Thank You,

    Gerry Carpinetti



    • Edited by RattlerrFx Sunday, May 21, 2017 3:38 AM added code
    Sunday, May 21, 2017 3:31 AM
  • Hi Gerry,

    I don't see why there would be a column called [Gerry], given that your column names are First_Name and Last_Name. Can you post the exact code you used that gives you that error?


    ~~Bonnie DeWitt [C# MVP]

    http://geek-goddess-bonnie.blogspot.com

    Sunday, May 21, 2017 5:01 AM
  • Hi RattlerrFx,

    Thank you for posting here.

    According to your question is more related to ADO, I will move it to ADO.NET DataSet forum for suitable support.

    The Visual C# discuss and ask the C# programming language, IDE, libraries, samples and tools.

    If you have some grammar or code errors, please feel free to contact us. We will try our best to give you a solution.

    Thanks for your understanding and cooperation.

    Best Regards,

    Wendy


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Monday, May 22, 2017 3:17 AM
  • Hi RattlerrFx,

    Please modify the code like this and check it works.

    SqlDataAdapter da = new SqlDataAdapter();
                    da = new SqlDataAdapter($"SELECT * FROM clouddata WHERE First_Name = '{searchOut}' OR Last_Name = '{searchOut}'", conn);
    
    DataSet ds = new DataSet();
    
    if (tbSearch.Text.Length >= 1)
    {
        da.Fill(ds, "MyDataBinding");
        dataGrid2.DataContext = ds.Tables[0];
    }

    If the issue still exists, could you please share a simple complete code, which could reproduce the issue.

    Best regards,

    Cole Wu


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Monday, May 22, 2017 8:33 AM
    Moderator
  • I tried the code above and it does not produce any results in the table and produces no errors..
    • Edited by RattlerrFx Monday, May 22, 2017 6:26 PM
    Monday, May 22, 2017 6:26 PM
  • Yeah, OK, but there have been several suggestions ... which one did you actually try? Humor me, please, and post the actual code *you* used.

    I assume that you are no longer getting the error "Cannot find column [Gerry]" ? I assume that meant that your searchout variable contained the value "Gerry"?

    Have you tried setting a breakpoint in the debugger to be sure that your searchout variable contains the value that you think it does?

    Did you check the database itself to see if there is actually data in the clouddata database table that has either the first name or last name contained in the searchout variable?


    ~~Bonnie DeWitt [C# MVP]

    http://geek-goddess-bonnie.blogspot.com

    Monday, May 22, 2017 8:46 PM
  • None of my colums are "Gerry" my columns are First_Name, Last_name etc I will repost my Original code.The code below does not populate the table with data from any of the columns. In addition, when i run a search using First_Name it populates my table with about 40 rows of data.

    SqlDataAdapter da = new SqlDataAdapter();
                    da = new SqlDataAdapter($"SELECT * FROM clouddata WHERE First_Name = '{searchOut}' OR Last_Name = '{searchOut}'", conn);
    
    DataSet ds = new DataSet();
    
    if (tbSearch.Text.Length >= 1)
    {
        da.Fill(ds, "MyDataBinding");
        dataGrid2.DataContext = ds.Tables[0];
    }

    Original Code Below::

    private void button_Click(object sender, RoutedEventArgs e)
            {
    
                using (SqlConnection conn = new SqlConnection())
                {
                    int result = 0;
                    string searchOut = tbSearch.Text;
    
                    //using (SqlConnection con = new SqlConnection())
                    conn.ConnectionString = " Data Source=(LocalDB)\\MSSQLLocalDB;Database=cloudyhamdb.mdf;Trusted_Connection=True;AttachDbFilename =|DataDirectory|cloudyhamdb.mdf; Integrated Security = True;";
                    //conn.ConnectionString = "Server=tcp:uhc.database.windows.net,1433;Initial Catalog=universalhamcloudDB;Persist Security Info=False;User ID=gerry;Password=HuNt3r90@@_#;MultipleActiveResultSets=False;Encrypt=True;TrustServerCertificate=False;Connection Timeout=30;";
                    SqlCommand MyCommand = new SqlCommand("INSERT INTO clouddata " + " (First_Name, Last_Name, Grid_Square, Country, State, Call_Sign, Date_Time, Mode, Power)" + " Values (@First_Name, @Last_Name, @Grid_Square, @Country, @State, @Call_Sign, @Date_Time, @Mode, @Power)", conn);
                    MyCommand.Parameters.Add("@First_Name", System.Data.SqlDbType.Text);
                    MyCommand.Parameters.Add("@Last_Name", System.Data.SqlDbType.Text);
                    MyCommand.Parameters.Add("@Grid_Square", System.Data.SqlDbType.Text);
                    MyCommand.Parameters.Add("@Country", System.Data.SqlDbType.Text);
                    MyCommand.Parameters.Add("@State", System.Data.SqlDbType.Text);
                    MyCommand.Parameters.Add("@Call_Sign", System.Data.SqlDbType.Text);
                    MyCommand.Parameters.Add("@Date_Time", System.Data.SqlDbType.SmallDateTime);
                    MyCommand.Parameters.Add("@Power", System.Data.SqlDbType.Text);
                    MyCommand.Parameters.Add("@Mode", System.Data.SqlDbType.Text);
    
                    MyCommand.Parameters["@First_Name"].Value = Convert.ToString(tbFirstName.Text);
                    MyCommand.Parameters["@Last_Name"].Value = Convert.ToString(tbLastName.Text);
                    MyCommand.Parameters["@Grid_Square"].Value = Convert.ToString(tbGridSquare.Text);
                    MyCommand.Parameters["@Country"].Value = Convert.ToString(tbCountry.Text);
                    MyCommand.Parameters["@State"].Value = Convert.ToString(tbState.Text);
                    MyCommand.Parameters["@Call_Sign"].Value = Convert.ToString(tbCallSign.Text);
                    MyCommand.Parameters["@Date_Time"].Value = Convert.ToDateTime(DateTime.Now);
                    MyCommand.Parameters["@Power"].Value = Convert.ToString(tbPower.Text);
                    MyCommand.Parameters["@Mode"].Value = Convert.ToString(tbMode.Text);
    
                    conn.Open();
                    MyCommand.ExecuteNonQuery();
    
    
                    SqlDataAdapter da = new SqlDataAdapter();
    
                    da = new SqlDataAdapter("Select * FROM clouddata", conn);
                    DataSet ds = new DataSet();
    
                    if (tbSearch.Text.Length >= 1)
                    {
                        da.Fill(ds, "MyDataBinding");
                        DataRow[] returnRows = ds.Tables[0].Select("First_Name=" + searchOut);
                        returnRows = ds.Tables[0].Select("Last_Name=" + searchOut);
                        result = returnRows.Length;
                        dataGrid2.DataContext = ds;
                        int results = MyCommand.ExecuteNonQuery();
    
                    }
    
                    conn.Close();
                }
            }

    Tuesday, May 23, 2017 1:46 AM
  • I have tried this code also which returns all the rows but i only want the rows that contain the name "Gerry" to appear.
     conn.Open();
                    MyCommand.ExecuteNonQuery();
    
    
                    SqlDataAdapter da = new SqlDataAdapter();
    
                    da = new SqlDataAdapter("Select * FROM clouddata", conn);
                    DataSet ds = new DataSet();
    
                    if (tbSearch.Text.Length >= 1)
                    {
                        da.Fill(ds, "MyDataBinding");
                        DataTable dt = ds.Tables[0];
                        dataGrid2.DataContext = ds;
                        int results = MyCommand.ExecuteNonQuery();
    
                    }
    
                    conn.Close();
                }

    Tuesday, May 23, 2017 2:48 AM
  • From the screenshot of your screen, it looks like you have entered "First_Name" in your tbSearch, so searchOut (which you have set equal tbSearch.Text) will not be the value you are searching for, but will be the column name. If that's the way that you wanted it to work, then you'll have to do something like this:

    if (tbSearch.Text.Length >= 1)
    {
        string sql = "SELECT * FROM clouddata WHERE " + tbSearch.Text;
        SqlCommand MyGetCommand = new SqlCommand(sql, conn);
        da = new SqlDataAdapter(MyGetCommand);
        DataSet ds = new DataSet();
    
        da.Fill(ds, "MyDataBinding");
        dataGrid2.DataContext = ds.Tables[0];
    }
    
    conn.Close();

    And the text entered into the tbSearch TextBox would be: First_Name = 'Gerry'

    This is probably not the best methodology though, because of being vulnerable to SQL Injection. Google it for a definition, if you need it, or take a look at http://bobby-tables.com/


    ~~Bonnie DeWitt [C# MVP]

    http://geek-goddess-bonnie.blogspot.com


    • Edited by BonnieBMVP Tuesday, May 23, 2017 2:00 PM fixed link
    Tuesday, May 23, 2017 5:22 AM
  • I appreciate your help and want to say thanks. You are correct it is not a good methodology. I am not wanting to search via column, I am wanting to search the database rows for the data using the tbSearch textbox. For example i want to enter "Gerry" into the tbSearch and only bring up the related information.
    Tuesday, May 23, 2017 4:27 PM
  • Then this should work for you ... and if it still doesn't, I can't think what might be wrong (this is, of course within your "using (SqlConnection..." block:

    if (tbSearch.Text.Length >= 1)
    {
        string sql = "SELECT * FROM clouddata WHERE First_Name = @parm";
        SqlCommand MyGetCommand = new SqlCommand(sql, conn);
        // I just had a thought, perhaps this value needs to be trimmed
        // so, perhaps the parameter should be set like this instead
        MyGetCommand.Parameters.AddWithValue("@parm", tbSearch.Text.Trim());
        da = new SqlDataAdapter(MyGetCommand);
        DataSet ds = new DataSet();
    
        da.Fill(ds, "MyDataBinding");
        dataGrid2.DataContext = ds.Tables[0];
    }
    
    conn.Close();
    

    Another thought is that the column in the database is a char(xxx), instead of a varchar(xxx), in which case that needs to be taken into account also (because a char() will pad the end of the value with spaces). If so, then your SELECT will need to be changed to this:

    string sql = "SELECT * FROM clouddata WHERE RTRIM(First_Name) = @parm";

    I bet that's the problem ...  having to do with non-trimmed spaces in one place or another.


    ~~Bonnie DeWitt [C# MVP]

    http://geek-goddess-bonnie.blogspot.com

    • Marked as answer by RattlerrFx Tuesday, May 23, 2017 4:59 PM
    Tuesday, May 23, 2017 4:52 PM
  • I guess the "Marked as answer" means that solved your problem, right Gerry?   =0)

    ~~Bonnie DeWitt [C# MVP]

    http://geek-goddess-bonnie.blogspot.com

    Tuesday, May 23, 2017 5:36 PM
  • Yes it did. Thanks
    Tuesday, May 23, 2017 7:43 PM
  • You're welcome, Gerry! Glad I could help! 

    I just got back from vacation ... =0)


    ~~Bonnie DeWitt [C# MVP]

    http://geek-goddess-bonnie.blogspot.com

    Friday, June 2, 2017 6:13 PM