Display Record from database based on Id from URL

Question

I have a url like this https://mydomain.com/page1.aspx?Id=287097645
And I have a select statement that selects data from database to display, based on the Id

using (SqlCommand cmd = new SqlCommand("SELECT Item,Rate,Fee, FROM Table WHERE (doc_no = '" + Session["record"] + "')", con))

OR

cmd.CommandText = "SELECT * FROM Table WHERE doc_no = '" + Session["record"] + "'";

When I navigate to the page (https://mydomain.com/page1.aspx?Id=287097645) to show the record, no record will be displayed. It just shows a blank web page.
So I tried to use the select statement, but this time, I added the Id (287097645) and the record showed.

using (SqlCommand cmd = new SqlCommand("SELECT Item,Qty,Rate,Amount FROM Table WHERE (doc_no = '" + 287097645 + "')", con))

OR

cmd.CommandText = "SELECT * FROM Table WHERE doc_no = 287097645";

How do I show records from database based on the condition (which is the Id that is in the URL)?

Answer 1

Hi @Donald Symmons ,
According to your codes, your problem is lossing the value of the session["record"]. Your database can't select that it don't know what num of your doc_no . I think,you don't set the value of the session["record"].
Another question is , you need get the url's parameter instead of the Session. I suggest you could get QueryString(). Just like this:

cmd.CommandText = "SELECT * FROM Table WHERE doc_no = '" + Request.QueryString["Id"] + "'";  

Best regards,
Yijing Sun

---

If the answer is helpful, please click "Accept Answer" and upvote it.

Note: Please follow the steps in our  documentation  to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

You should what the value is for Session["record"] using a breakpoint and use a parameter

using var con = new SqlConnection() { ConnectionString = ConnectionString };
var selectStatement = "SELECT * FROM Table WHERE doc_no = @doc_no;";
using var cmd = new SqlCommand() { Connection = con, CommandText = selectStatement };
cmd.Parameters.Add("@doc_no", SqlDbType.NVarChar).Value = Session["record"];

Answer 3

Ok, but when you pass a parameter(s) (often called url querydefs), it does not automatic go into session.

and for things like a database row PK id, for reasons of security, you probably don't want to show or have such values int the URL (unless everyone and anyone is allowed to view such records, else other users can change that value, and thus view other people's records.

But, the basic issue here seems to be the assumption that passing such values in the URL by magic goes into Session(). they have ZERO relationship to each other.

Now, if the calling page were to set session() (and that is a very good idea), then you would not require nor need the "ID" to be passed in the URL anymore, would you?

So, based on the above information, then your code would be something like this:

        protected void Page_Load(object sender, EventArgs e)  
        {  
            if (!IsPostBack)  
                LoadData();  
        }  
  
        void LoadData()  
        {  
            // Check for value in URL  
            string docnum = "";  
            if (!string.IsNullOrEmpty(Request.QueryString["ID"]))  
                // we have URL "?id=xxxxx"  
                docnum = Request.QueryString["ID"].ToString();  
            else  
                // no url id, try for session  
                if (Session["record"] != null)  
                   docnum = Session["record"].ToString();  
  
            // optional - we don't have ID or session - go somepalce else  
            if (docnum == "")  
            //{  
            //    // no URL, no session, we should jump back to previouis  
            //    Response.Redirect("myscan.aspx");  
            return;  
            //}  
  
            DataTable rstData = new DataTable();  
            using (SqlConnection conn = new SqlConnection(Properties.Settings.Default.TEST4))  
            {  
                string strSQL   
                    = "SELECT Item,Qty,Rate,Amount FROM Table WHERE doc_no = @docNum";  
                using (SqlCommand cmdSQL = new SqlCommand(strSQL, conn))  
                {  
                    cmdSQL.Parameters.Add("@docNum", SqlDbType.NVarChar).Value = docnum;  
                    conn.Open();  
                    rstData.Load(cmdSQL.ExecuteReader());  
                }  
            }  
            if (rstData.Rows.Count > 0)  
            {  
                // We have data - do whatever  
                General.FLoader(EditRecord, rstData.Rows[0]);  
            }                
        }  

So, above should quite much give you a close working bit of code

Regards,
Albert D. Kallal (Access MVP 2003-2017)
Edmonton, Alberta Canada