Gerty Engrie - Undefined

Reading an Excel file on a 64 bit server!

Thursday, 23. April 2009, 19:52:18

install this (MSDASQL) on the server

adjust your code

SqlConnection objConn = new SqlConnection();
            DataSet ds = new DataSet();
            string m_strConnection = @"server=.\SQLEXPRESS;Initial Catalog=[your database];user id=[your user];password=[your password];";

            objConn.ConnectionString = m_strConnection;
            objConn.Open();

            string sSQL;
            sSQL = @"Select * from openrowset('MSDASQL','driver={Microsoft Excel Driver (*.xls)}; DriverId=790;Dbq=[filename.xls]; DefaultDir=[path, everything but the filename]', 'Select * from [[fill in worksheetname]$]')";
            //You must use the $ after the object you reference in the spreadsheet

            SqlDataAdapter myCommand = new SqlDataAdapter(sSQL, objConn);
 
            DataSet myDataSet = new DataSet();
            myCommand.Fill(myDataSet, "ExcelInfo");

            DataTable dt = myDataSet.Tables["ExcelInfo"];

note: [..] means you need to replace it with your own values, Select * from [[fill in worksheetname]$] will be Select * from [Sheet1$] if your worksheet is called Sheet1.
frontend of my page:

<asp:DataGrid ID="dgExcel" runat="server" />

go to your sql server surface area configurationtool; choose "Surface Area Configuration for Features" click on "Ad Hoc Remote Queries" and enable it
give your user (the one from the connectionstring) the rights of a sysadmin
put the code on the server and execute! tadaaaa

Many thanks to Wybren one of our system admins who helped solving this

oooh and if you have a better way, feel free to share

No comments

updated jCaroussel

Sunday, 19. April 2009, 15:17:48

jCaroussel is now updated to v1.4 :-)
and the first datatype made by Netcentric is also available separatly (it was/is included in the jCaroussel too).

No comments

New to umbraco?

Wednesday, 25. March 2009, 20:33:35

umbraco cws

Be sure to try out the CWS starter package! Made with a lot of patience and dedication by Warren Buckley!

No comments

Maps in umbraco

Tuesday, 17. February 2009, 14:52:17

Check out Darren Ferguson's screencast!

Download link

No comments

Umbraco modules for Runway

Sunday, 8. February 2009, 20:38:09

umbraco, modules

Sander and I launched 2 new packages a few weeks ago. Thought I should mention it here too :-) We're pretty happy with the result so... a FAQ module and a jCarousel module. Where you can find the new modules: http://packages.netcentric.be/ Hopefully there will be coming more soon :-)

No comments

Google Sitemap

Tuesday, 30. December 2008, 19:36:13

sitemap, google, umbraco

I actually just adjusted the standard sitemap xslt

-- you should install umbraco utilities for this, to change the mime-type or use <xsl:value-of select="umbraco.library:ChangeContentType('text/xml')"/> in umbraco v4 (thanks to bootnumlock) -- don't forget to change the url in the xslt

<?xml version="1.0" encoding="UTF-8"?><!DOCTYPE xsl:stylesheet [ <!ENTITY nbsp " "> ]>
<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform" xmlns:msxml="urn:schemas-microsoft-com:xslt" xmlns:umbraco.library="urn:umbraco.library" exclude-result-prefixes="msxml umbraco.library">
<xsl:output method="xml" omit-xml-declaration="yes"/>
<xsl:param name="currentPage"/>
<!-- update this variable on how deep your site map should be -->
<xsl:variable name="maxLevelForSitemap" select="10"/>

<xsl:template match="/">
<urlset xmlns="http://www.sitemaps.org/schemas/sitemap/0.9">
<xsl:call-template name="drawNodes">  
<xsl:with-param name="parent" select="$currentPage/ancestor-or-self::node [@level=1]"/>  
</xsl:call-template>
</urlset>
</xsl:template>

<xsl:template name="drawNodes">
<xsl:param name="parent"/> 
<xsl:if test="umbraco.library:IsProtected($parent/@id, $parent/@path) = 0 or (umbraco.library:IsProtected($parent/@id, $parent/@path) = 1 and umbraco.library:IsLoggedOn() = 1)">
<xsl:for-each select="$parent/node [string(./data [@alias='umbracoNaviHide']) != '1' and @level <= $maxLevelForSitemap]"> 
<url xmlns="http://www.sitemaps.org/schemas/sitemap/0.9">
<loc>http://www.corazine.com<xsl:value-of select="umbraco.library:NiceUrl(@id)"/></loc>
<lastmod><xsl:value-of select="umbraco.library:FormatDateTime(@updateDate, 'yyyy-MM-dd')"/></lastmod>
<changefreq>weekly</changefreq>
</url>
<xsl:if test="count(./node [string(./data [@alias='umbracoNaviHide']) != '1' and @level <= $maxLevelForSitemap]) > 0">   
<xsl:call-template name="drawNodes">    
<xsl:with-param name="parent" select="."/>    
</xsl:call-template>  
</xsl:if> 
</xsl:for-each>
</xsl:if>
</xsl:template>
</xsl:stylesheet>

and the template:

<%@ Master Language="C#" MasterPageFile="/umbraco/masterpages/default.master" AutoEventWireup="true" %>
<asp:Content ContentPlaceHolderID="ContentPlaceHolderDefault" runat="server">
<umbraco:Macro ContentType="text/xml" Alias="uuContentType" runat="server"></umbraco:Macro><?xml version="1.0" encoding="UTF-8"?><umbraco:Macro Alias="SitemapXml" runat="server"></umbraco:Macro>
</asp:Content>

et voila

6 comments

umbraco Base

Thursday, 25. December 2008, 20:07:09

umbraco, base

note to myself, if you want to use Base and pass a variable:

make a C# method you want to use
put dll in bin folder

add it to restExtensions.config in the config file

<ext assembly="/bin/namespace" type="namespace.classname" alias="alias">
    <permission method="methodname" allowAll="true" />
  </ext>

hah! now you can access it through domainname/Base/alias/methodname/variable.aspx, when not using a variable you can just access it through domainname/Base/alias/methodname.aspx

No comments