Hyperlink Search Results/SQL DB/BLOB Field/View PDF in Browser/Visual C#

[ESIS]

Hi,

I am trying to create a "simple" application, of which, one of the functionalities is to be able to view a PDF Image (Invoice) held in a BLOB field on an SQL Database in the web browser.

As it stands, I have successfully created a form that asks the user to enter an Invoice Number and it pulls up the PDF Image from the BLOB in the SQL DB and displays it in the browser.

However, now what i'm trying to do is to create a form that asks the user for a Client number and then pulls up all the Invoice numbers that client currently has from the SQL DB. Upon displaying the list of Invoice Numbers, I would like these to be hyperlinked and, upon being clicked, directed to the corresponding PDF Image... but i'm stuck!!!

Here is the coding I have to pull up the actual PDF by entering the invoice number:

   protected void Button1_Click1(object sender, EventArgs e)
    {
         int GetInvdID;
         GetInvdID = Convert.ToInt32(txtInvoice.Text);
         byte[] data = null;
 
         using (SqlConnection con = new SqlConnection("server=serername;database=database_name;user id=username;password=password;"))
         {
             con.Open();
 
             string SqlStatement = "SELECT Invd_PDF_Image FROM Invoice_Details WHERE Invoice_Details.Invd_ID=";
 
             SqlCommand cmd = new SqlCommand(SqlStatement + GetInvdID, con);
             SqlDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
 
             while (reader.Read())
             {
                  data = (byte[])reader[0];
 
             }
          }
 
          HttpContext.Current.Response.ContentType = "application/pdf";
          HttpContext.Current.Response.Clear();
          HttpContext.Current.Response.BufferOutput = true;
          HttpContext.Current.Response.BinaryWrite(data);
 
    }

The above works fine. bu tthis is what I have so far for trying to create a hyperlink to each corresponding PDF after entering the client number:

   protected void btnClientRef_Click(object sender, EventArgs e)
    {
        string GetClientRef;
        GetClientRef = txtClientRef.Text;
        byte[] data = null;
 
        using (SqlConnection con = new SqlConnection("server=server_name;database=database_name;user id=username;password=password;"))
        {
            con.Open();
 
            string SqlStatement1 = "SELECT TOP 10 Invd_Client_Ref,Invd_ID,Link_to_Client,Invoice_Client FROM Invoice_Details,Calls WHERE Invoice_Details.Invd_Client_Ref='" + GetClientRef + "'";
 
            SqlCommand cmd = new SqlCommand(SqlStatement1, con);
            SqlDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
 
            while (reader.Read())
            {
                Response.Write("Invoice Number: " + "<a href=" + "http://www.google.co.uk" + ">" + reader["Invd_ID"] + "</a> <br>");
 
            }

Where i've put 'Google' as the hyperlink, i'd like that to be hyperlinked to the corresponding PDF Image for the Invoice number that is displayed.


Sorry for the long post!

Can anyone help?

Thanks in advance.

[kenobewan]

One way would be placing the link to where the image is displayed and using a querystring with the Invd_id to display the right image. HTH.

[ESIS]

One way would be placing the link to where the image is displayed and using a querystring with the Invd_id to display the right image. HTH.

Thanks for responding.

http://localhost:1053/PDF_Viewer/Default.aspx - that's the link that shows up in the browser when it is displayed...

how would i use a querystring in the context my coding? Could you possibly post some sample code for me? I'd really appreciate it.

Thanks again.

[ESIS]

Anyone? Please?

Thanks.

[ESIS]

One way would be placing the link to where the image is displayed and using a querystring with the Invd_id to display the right image. HTH.

Can anyone help with this, please?

I still have the same problem..

Thanks.

[kenobewan]

Have you tried:

Response.Write("Invoice Number: " + "<a href=" + "http://www.example.co.uk/default.aspx?link="+ reader["Invd_ID"]  + ">" + reader["Invd_ID"] + "</a> <br>");
 

[ESIS]

Have you tried:

Response.Write("Invoice Number: " + "<a href=" + "http://www.example.co.uk/default.aspx?link="+ reader["Invd_ID"]  + ">" + reader["Invd_ID"] + "</a> <br>");
 

Hi, yes. I already tried that, which worked fine in the sense that it assigns to each link it's own QueryString (Inv_ID) value.

However, I am still unable to use this value so that it directs the user to the corresponding PDF Image once the link is clicked...

Here is my code thus far:

 protected void btnClientRef_Click(object sender, EventArgs e)
    {
        // string GetClientRef;
        string NewInvdID = Request.QueryString["QInvdID"];
       // GetClientRef = txtClientRef.Text;
 
        using (SqlConnection con = new SqlConnection("server=server_name;database=database_name;user id=username;password=password;"))
        {
            con.Open();
 
            string SqlStatement1 = "SELECT TOP 10 Invd_Client_Ref,Invd_ID,Link_to_Client,Invoice_Client FROM Invoice_Details,Calls WHERE Invoice_Details.Invd_Client_Ref='" + GetClientRef + "'"; 
 
            SqlCommand cmd = new SqlCommand(SqlStatement1, con);
            SqlDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
 
            while (reader.Read())
            {
 
                Response.Write("Invoice Number: " + "<a href=" + "http://localhost:1053/PDF_Viewer_Test/Default.aspx?QinvdID=" + reader["Invd_ID"] + ">" + reader["Invd_ID"] + "</a> <br>");
 
 
            }

If you look at my initial post, the first chunk of code I posted allows the user to enter an Invoice Number and displays the corresponding PDF Image in the browser. This is what i'm trying to achieve; i.e. when the user clicks the hyperlinked invoice number, it should take it straight to the corresponding PDF Image.

Any ideas? I've been battling with it all afternoon, to no avail..

Thanks in advance.

[ESIS]

Still struggling over here...

[ESIS]

Anyone........................ ?

[ESIS]

Just to let you know, I have finally cracked after a whole day of battling with this morning and all day yesterday.

This is how I did it (code):

public partial class _Default : System.Web.UI.Page
{
 
    protected void Page_Load(object sender, EventArgs e)
    {
        if (this.Request.QueryString["QinvdID"] != null)
            this.ViewPDF(Convert.ToInt32(this.Request.QueryString["QinvdID"]));
    }
 
    protected void Button1_Click1(object sender, EventArgs e)
    {
        if (!string.IsNullOrEmpty(txtInvoice.Text))
            this.ViewPDF(Convert.ToInt32(txtInvoice.Text));
    }
 
    private void ViewPDF(int GetInvdID)
    {
 
         byte[] data = null;
 
         using (SqlConnection con = new SqlConnection("server=server_name;database=database_name;user id=username;password=password;"))
         {
             con.Open();
 
             string SqlStatement = "SELECT Invd_PDF_Image FROM Invoice_Details WHERE Invoice_Details.Invd_ID=";
 
             SqlCommand cmd = new SqlCommand(SqlStatement + GetInvdID, con);
             SqlDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
 
             if (reader.HasRows)
             {
                 reader.Read();
 
                 if (!reader.IsDBNull(0))
                 {
                    data = (byte[])reader[0];
 
                     HttpContext.Current.Response.ContentType = "application/pdf";
                     HttpContext.Current.Response.Clear();
                     HttpContext.Current.Response.BufferOutput = true;
                     HttpContext.Current.Response.BinaryWrite(data);
                 }
                 else
                 {
                     Response.Write ("<script language=javascript>alert('" + "No PDF Image exists for Invoice Number " + GetInvdID + "'" + ")</script>");
                 }
             }
        }
    }
 
    protected void btnClientRef_Click(object sender, EventArgs e)
    {
        string GetClientRef;
        GetClientRef = txtClientRef.Text;
 
        using (SqlConnection con = new SqlConnection("server=server_name;database=database_name;user id=username;password=password;"))
        {
            con.Open();
 
            string SqlStatement1 = "SELECT TOP 10 Invd_Client_Ref,Invd_ID,Link_to_Client,Invoice_Client FROM Invoice_Details,Calls WHERE Invoice_Details.Invd_Client_Ref='" + GetClientRef + "'";
 
            SqlCommand cmd = new SqlCommand(SqlStatement1, con);
            SqlDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
 
            while (reader.Read())
            {
                Response.Write("Invoice Number: " + "<a href=" + "http://localhost:1053/PDF_Viewer_Test/Default.aspx?QinvdID=" + reader["Invd_ID"] + ">" + reader["Invd_ID"] + "</a> <br>");
            }   
        }
    }
}

Thanks to all those who responded.

~ESIS

[kenobewan]

Well done! Programming can be like climbing a mountain, hours of pain and frustration for that brief exhiliration at the peak.

Respect to one of the best mountaineers of all time, Sir Edmund Hillary, who died on January 11th. Peace.