[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 192
  • Last Modified:

Opening an Excel TEMPLATE that is Stored as a BLOB in SQL Server

I have a table that stores EXCEL templates.  :

Here is what I have:

set cn=SERVER.CreateObject("ADODB.CONNECTION")
cn.ConnectionString="Provider=SQLOLEDB.1;Persist Security Info=False;" & _
    "Initial Catalog=dbAS_FAC_CENSUS;Data Source=ASDEV" & _
    ";User Id=" & Request.Form("txtUserID") & _
    ";Password=" & Request.Form("txtPwd")
cn.Open
set rs=server.CreateObject("ADODB.RECORDSET")
set rs.ActiveConnection=cn
rs.Open("SELECT * FROM vwMART_REPORTS WHERE strUserID='" & Request.Form("txtUserID") & "'")

THE rs("Report") field has the actual Template.  How do I open the contents of that field in Excel?
0
clangl
Asked:
clangl
  • 8
  • 5
1 Solution
 
Richie_SimonettiIT OperationsCommented:
you have to create a temporary local file and open it.
0
 
clanglAuthor Commented:
But how do I get it from the table to a local file?
0
 
Richie_SimonettiIT OperationsCommented:
OK. You need to use ADDB.stream object (more on this later)
0
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
Richie_SimonettiIT OperationsCommented:
0
 
Richie_SimonettiIT OperationsCommented:
0
 
clanglAuthor Commented:
with the ADo stream method....
What my goal is, is to have them click on this link on a web browser that points to that Field in the table and then have excel open right up......
0
 
Richie_SimonettiIT OperationsCommented:
But that's a complete diferent scenario!!!
0
 
Richie_SimonettiIT OperationsCommented:
You need to create/extract the file at server side after user does the click and navigate to resulting xls file. It should be opened in web browser window.
0
 
clanglAuthor Commented:
This will work but I need to be able to do this with out saving it to a file.  I will post another question.  Thank you for your help.
0
 
Richie_SimonettiIT OperationsCommented:
Hummm, i think you can't but i maybe wrong.
Thanks for "A" grade.
0
 
clanglAuthor Commented:
I ended up not using a stream.
Just in case you ever need something similiar here is what I ended up doing:

<%
Response.buffer = TRUE
'on error resume next
dim strReport
strReport=left(Request.Form.Item,instr(1,Request.Form.Item,"=")-1)
'Response.Write strReport

dim cn, cmd,rs
set cn=SERVER.CreateObject("ADODB.CONNECTION")
set rs=server.CreateObject("ADODB.Recordset")
cn.ConnectionString="Provider=SQLOLEDB.1;Persist Security Info=False;" & _
    "Initial Catalog=dbAS_FAC_CENSUS;Data Source=ASDEV" & _
    ";User Id=" &  Session("User") & _
    ";Password=" & Session("Pwd")
cn.Open
set cmd=server.CreateObject("ADODB.Command")
set cmd.ActiveConnection=cn
cmd.CommandType=4
cmd.CommandText="sp_MART_GetReport"
set rs=cmd.Execute(,strReport)
set fld=rs.Fields(0)
cBytes = fld.ActualSize
Response.Clear
'Response.ContentType = "application/vnd-ms-excel"
      Response.ContentType = rs.Fields(1).Value
      Response.AddHeader "Content-Disposition", "filename=a.xlt;"
      Response.BinaryWrite (fld.GetChunk(cBytes))
rs.Close
set fld=nothing
set rs=nothing
set cmd=nothing
cn.Close
set cn=nothing

%>
0
 
Richie_SimonettiIT OperationsCommented:
excellent!!, that's for sure far ago from my knowledge. excellent work again.
(I really think you accepted my comment too soon :)
0
 
clanglAuthor Commented:
I ended up using your answer to back in all the Templates to the SQL Database.  
0

Featured Post

Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

  • 8
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now