locked
select string question RRS feed

  • Question

  • User-1290459327 posted

    Hello,I have a question about a select string,  why the selectstring works fine when i put hard written Furniture insite the select statement, but is it not working when i put a session var inside it. Maybe one of u can tels me what i do wrong!

    Here my script,

    protected void SubCatData()
        {
    
               ////session form page one
    
            string Furn1 = (string)Session["SubCat"];
    
            DataSet ds = new DataSet();
    
            /// this works hard written
    
           MySqlCommand cmd = new MySqlCommand("select * from prodcat3 where ProdCat2='furniture'", conn);
    
    
           /// Here the selectstatement with session var witch is not working??
    
         MySqlCommand cmd = new MySqlCommand("select * from prodcat3 where ProdCat2='" + Furn1 + "'", conn);
         
    
            conn.Open();
    
            
            MySqlDataAdapter adapter = new MySqlDataAdapter(cmd);
    
            adapter.Fill(ds);
    
            Repeater1.DataSource = ds;
            Repeater1.DataBind();
            conn.Close();
    
        }

    Sunday, September 6, 2015 2:12 PM

Answers

  • User-821857111 posted

    Maybe your session variable is null. Or it does not match an existing value in your database. You should use the debugger to investigate.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Sunday, September 6, 2015 2:44 PM
  • User269602965 posted

    show you code with ADD parameters

    and we shall work it from there.

    parameters is the standard for adding variable to WHERE clause and is one defense against SQL injection

    MySQL example.. in this case inserting data into a data reader

    using System;
    using System.Data;
    
    using MySql.Data;
    using MySql.Data.MySqlClient;
    
    public class Tutorial5
    {
        public static void Main()
        {
            string connStr = "server=localhost;user=root;database=world;port=3306;password=******;";
            MySqlConnection conn = new MySqlConnection(connStr);
            try
            {
                Console.WriteLine("Connecting to MySQL...");
                conn.Open();
    
                string sql = "SELECT Name, HeadOfState FROM Country WHERE Continent=@Continent";
                MySqlCommand cmd = new MySqlCommand(sql, conn);
    
                Console.WriteLine("Enter a continent e.g. 'North America', 'Europe': ");
                string user_input = Console.ReadLine();
    
                cmd.Parameters.AddWithValue("@Continent", user_input);
    
                MySqlDataReader rdr = cmd.ExecuteReader();
    
                while (rdr.Read())
                {
                    Console.WriteLine(rdr["Name"]+" --- "+rdr["HeadOfState"]);
                }
                rdr.Close();
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.ToString());
            }
    
            conn.Close();
            Console.WriteLine("Done.");
        }
    }
    

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Sunday, September 6, 2015 9:10 PM
  • User753101303 posted

    Hi,

    Could it be that this column is case sensitive for some reason? (furniture vs Furniture). Also do you know the value is correct because it should or because you have seen this in the debugger. See https://msdn.microsoft.com/en-us/library/sc65sadd.aspx

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, September 7, 2015 12:29 PM

All replies

  • User-821857111 posted

    Maybe your session variable is null. Or it does not match an existing value in your database. You should use the debugger to investigate.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Sunday, September 6, 2015 2:44 PM
  • User269602965 posted

    It is best to use PARAMETERS to pass Bind Variables to a Select statement

    This example is for Oracle, but same principle with minor changes in syntax for other databases

    Public Shared Sub selectCustomerInfo(ByVal decCustomerSeq As Decimal)
      ' Select customer name and email using Customer table key passed from application to Oracle via ODP.NET'
      Dim connectionString As String = ConfigurationManager.ConnectionStrings("{YourConnectionStringName}").ConnectionString
      Try
        Dim SQL =
        <SQL>
        SELECT CUSTOMER_SEQ, NAME_LAST, NAME_FIRST, EMAIL
        FROM  {YOURSCHEMANAME}.CUSTOMER
        WHERE CUSTOMER_SEQ = :bindvarCUSTOMER_SEQ
        </SQL>
        Using conn As New OracleConnection(connectionString)
          Using cmd As New OracleCommand(SQL.Value, conn)
            cmd.Parameters.Clear()
            cmd.Parameters.Add("bindvarCUSTOMER_SEQ", OracleDbType.Decimal, decCustomerSeq, ParameterDirection.Input)
            conn.Open()
            cmd.ExecuteNonQuery()
          End Using
        End Using
      Catch ex As Exception
      End Try
    End Sub 

    Sunday, September 6, 2015 4:09 PM
  • User-1290459327 posted

    Dear Mikes,

    I use debugger and session is not 0, and session var is Furniture... thats why my question what i do wrong.

    Sunday, September 6, 2015 4:27 PM
  • User-1290459327 posted

    Dear lannie,

    I have try to use parameters ADD but its also not working...

    Sunday, September 6, 2015 4:42 PM
  • User-1290459327 posted

    Anyone else who has the answer?

    //Methode (1) working but i cannot use this one
    
     (1)   MySqlCommand cmd = new MySqlCommand("select * from prodcat3 where ProdCat2 ='Furniture'", conn); //<------- working repeater filled
    
    
     //Methode (2) Not working but i need this one
    
     string SubCat = (string)Session["SubCat"]; //<----- session is filled with furniture
    
    (2) MySqlCommand cmd = new MySqlCommand("select * from prodcat3 where ProdCat2 ='" + SubCat + "'", conn); //<----- repeater empty

    Sunday, September 6, 2015 5:31 PM
  • User269602965 posted

    show you code with ADD parameters

    and we shall work it from there.

    parameters is the standard for adding variable to WHERE clause and is one defense against SQL injection

    MySQL example.. in this case inserting data into a data reader

    using System;
    using System.Data;
    
    using MySql.Data;
    using MySql.Data.MySqlClient;
    
    public class Tutorial5
    {
        public static void Main()
        {
            string connStr = "server=localhost;user=root;database=world;port=3306;password=******;";
            MySqlConnection conn = new MySqlConnection(connStr);
            try
            {
                Console.WriteLine("Connecting to MySQL...");
                conn.Open();
    
                string sql = "SELECT Name, HeadOfState FROM Country WHERE Continent=@Continent";
                MySqlCommand cmd = new MySqlCommand(sql, conn);
    
                Console.WriteLine("Enter a continent e.g. 'North America', 'Europe': ");
                string user_input = Console.ReadLine();
    
                cmd.Parameters.AddWithValue("@Continent", user_input);
    
                MySqlDataReader rdr = cmd.ExecuteReader();
    
                while (rdr.Read())
                {
                    Console.WriteLine(rdr["Name"]+" --- "+rdr["HeadOfState"]);
                }
                rdr.Close();
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.ToString());
            }
    
            conn.Close();
            Console.WriteLine("Done.");
        }
    }
    

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Sunday, September 6, 2015 9:10 PM
  • User-1290459327 posted

    Hi lannie thanks for the help,

    I will show the steps from page to page,

    HTML First page linkbutton click,

    <div class="auto-style2">
    
                <asp:Repeater ID="Repeater2" runat="server">
                    <ItemTemplate>
                        <table>
                            <tr>
                                <td>
                                    <asp:LinkButton ID="LbMain" runat="server" Text='<%# Eval("ProdCat1") %>' OnCommand="LbMain_Command" CommandArgument='<%# Eval("ProdCat1") %>'></asp:LinkButton>
    
                                </td>
                            </tr>
                        </table>
                    </ItemTemplate>
                </asp:Repeater>
    
                </div>

    Firsrt page code behind wher session is created,

     protected void LbMain_Command(object sender, CommandEventArgs e)
        {
            foreach (RepeaterItem item in Repeater2.Items)
            {
    
                LinkButton LBId = (LinkButton)item.FindControl("LbMain");
                LBId.Text = e.CommandArgument.ToString();
                Session["SubCat"] = LBId.Text;
                Response.Redirect("~/Default3.aspx");
            }
        }

    HTML second page

     <div>
            <asp:Repeater ID="Rp1" runat="server">
    
                <ItemTemplate>
                    <table>
                            <tr>
                                <td>
                                    <
                                    <asp:LinkButton ID="LinkButton1" runat="server" Text='<%# Eval("prodcat3") %>'></asp:LinkButton>
                                </td>
    
                            </tr>
    
                        </table>
    
                </ItemTemplate>
            </asp:Repeater>
        </div>

    Code behind second page with read session variable and put it in select statement, to fill repeater.

     MySqlConnection conn = new MySqlConnection(ConfigurationManager.ConnectionStrings["boelenkaspotshopsConnectionString"].ConnectionString);
        
        protected void Page_Load(object sender, EventArgs e)
        {
            
            if (!IsPostBack)
            {
                BindLviewData();
              
            }
        }
    
        protected void BindLviewData()
        {
           
            DataSet ds = new DataSet();
            conn.Open();
            // MySqlCommand cmd = new MySqlCommand("select * from prodcat3 where ProdCat2 ='" + SubCat + "'", conn);
            MySqlCommand cmd = new MySqlCommand("select * from prodcat3 where ProdCat2 = @ProdCat2", conn);
            string SubCat = (string)Session["SubCat"];
    
            cmd.Parameters.AddWithValue("@ProdCat2", SubCat);
           
            MySqlDataAdapter adapter = new MySqlDataAdapter(cmd);
    
            adapter.Fill(ds);
    
            Rp1.DataSource = ds;
            Rp1.DataBind();
            conn.Close();
    
        }




    Monday, September 7, 2015 11:38 AM
  • User753101303 posted

    Hi,

    Could it be that this column is case sensitive for some reason? (furniture vs Furniture). Also do you know the value is correct because it should or because you have seen this in the debugger. See https://msdn.microsoft.com/en-us/library/sc65sadd.aspx

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, September 7, 2015 12:29 PM
  • User-1290459327 posted

    Hi all,

    I have to make excuses for my question, not long before i was test my script, we my co worker and i change the tables @ the database, I have changed al my pages but this one I 've overlooked, so al my script on this was linked to the wrong colums of the database. logical i do not get any data this way :,( , I am so ashamed. But al youre answers brought me to, to look better to my script.

    Manny thanks for youre patience,

    Michel

    Monday, September 7, 2015 2:58 PM
  • User-821857111 posted

    We've all done things like that and wasted hours trying to fix "mysterious" bugs ;)

    Monday, September 7, 2015 3:33 PM