Loading excel data column (alpha-numeric) using OLE DB adapter

Problem: One of the excel data column has alpha-numeric data stored. While loading the data using Ole DB adapter, adapter preset the data type to numeric and subsequently doesn't read the alpha numeric content.

Solution: While defining the connection string, define it as:
string connectionString = "Provider=Microsoft.Jet.OleDb.4.0;Extended Properties='Excel 8.0;HDR=Yes;IMEX=1';";
connectionString += "data source='C:\Temp\FileTobeProcessed.xls';";

we generally define the extended properties = 'Excel 8.0;' only and forget to add, if excel file has header, or treat the column data type as alpha-numeric (add IMEX=1 in extended properties).

It helped me a lot of places, mostly, when I used Dynamics GP eConnect to transfer Items from One bin location to another, and item number data was alpha-numeric.

MATSHITA DVD +- RW UJ 857G Driver Problem/Solution

I have DELL XPS 1330 with MATSHITA DVD UJ 857G DVD Drive. Because of nVidia problems, the mother board is replaced three times (thanks GOD that I took 3 years warranty on it), so I didnt pay a single penny so far on repairs.
Now I formatted it with Vista Ultimate 64bit and later I found that Vista is not detecting drivers for DVD.
I searched a lot but didnt find any suitable driver.
In device manager for the drive, I was getting error 32, and googled it based on the error description,and then I found the following posting:
http://www.pchell.com/hardware/cd_drive_error_code_39.shtml

It talks about error 39, still I did the same what the post says... and deleted entry LowerFilters - PxHlpa64. Restarted the computer, and noticed that drive is working just fine.

Read QueryString Parameter and use in CAML to display specific blog category Posts

Well, SharePoint is full of challenges, and one day before I was stuck on this problem. I modified Blog Category page, and hence the page became customized. Now when I click a specific blog category, the page was displaying all the posts having no category defined. I was confused what is happening. Apparently I looked the CAML Query in 'SelectCommand' attribute and found the CAML as:
<View><Query><OrderBy><FieldRef Name="PublishedDate" Ascending="FALSE"/><FieldRef Name="ID" Ascending="FALSE"/></OrderBy><Where><Eq><FieldRef Name="PostCategory"/><Value Type=""><GetVar Scope="Request" Name="Name"/></Value></Eq></Where></Query></View>"

Looked Ok to me! What is wrong in there. Noticed that how <GetVar> is working here. I thought of modifying the CAML query, but thought to investigate further using SharePoint Designer. BTW, here I am modifying WebPartPages:DataFormWebPart that has my custom formatting.

1. Now clicked this DataFormWebPart and a left arrow appears on the right of the same window, click it.
2. The window is titled as 'Common Data View Tasks'.
3. Click the top option - Filter.
4. When you click this 'Filter' option, 'Filter Criteria' window will open.
5. Click 'Click here to add a new clause...'.
6. Select the Field Name - In my case it was 'Category'.
7. Select Comparison value as 'Equals'
8. Select Value field and drop down option, click the last option...Create a new parameter. As soon as you click this new parameter option from drop down list, a new window will open titled as 'Data View Properties'.
9. Under Parameters, Type 'CategoryName' for 'Param1'. You can name anything here.
10. For Source Column, on the right side, select 'Query String' from the 'Parameter Source' drop down combo box.
11. As soon as you select 'Query String' from drop down, two new text boxes will appear. One is to define the query string variable name. Category page, by default, has 'Name' as Query String value. So Type 'Name' in Query String Variable Text Box.
12. Leave 'Default Value' text box empty.
13. Click Ok to close 'Data View Properties' window.
14. Click Ok to close 'Filter Criteria' window.

You are done here.

And finally when you look back your CAML query, it will look like the following:

<View><Query><OrderBy><FieldRef Name="PublishedDate" Ascending="FALSE"/><FieldRef Name="ID" Ascending="FALSE"/></OrderBy><Where><Eq><FieldRef Name="PostCategory"/><Value Type="Text">{CategoryName}</Value></Eq></Where></Query></View>

And when I ran my category page again for a specific category, filtering was happening correctly.

I am relieved!!

References that I used:

http://msdn.microsoft.com/en-us/library/aa218649.aspx - This I looked to understand the parameter binding relationship and from here I got the clue to look for 'Query String' option as one of the ParameterBindings Name is 'filterParam'. It is interesting article though.

Calendar: New Event - Hide 'Workspace' from NewForm or EditForm page

Somebody asked me about hiding a field called 'Workspace' from Calendar: New Event or Edit existing event.
I was wondering that there should be some decent way to hide any field available on 'New' or 'Edit' page.
I googled around, and I found couple of responses, forexample,
1. Modifying the NewForm.aspx page in SharePoint Designer 2007, hiding existing List Form Web Part, and then adding Custom List Form. Then delete the Workspace row from the form.

Let me talk about the dis-advantages of this option:
a. Attach File will NOT work. You need to investigate further to make it work. Still painful process.
b. Try to create a 'Recurring' event, it will not work as expected.

Considering these advantages, I thought that this is very tedious process, and one has to make the changes to all calendar lists. And then this change is required to be done on NewForm.aspx and EditForm.aspx. How about that? So it is really very painful process.

2. Another option is again, modifying the NewForm.aspx or EditForm.aspx, and add following script (However, it didnt work for me though), but it is worth adding it here, just in case, if people are trying to solve the problemin this manner.
<script language='javascript' type='text/javascript' >
var toolbar = document.getElementByName("<name attribute value>"); toolbar.parentNode.parentNode.parentNode.style.display = 'none'; toolbar.parentNode.parentNode.style.display = 'none';
<script>

This can be added in asp:content ContentPlaceHolderId="PlaceHolderTitleAreaClass".
Once again as I said that it didnt work for me, but it can be done this way too.

3. Recommended way. First thing is that it is a feature, and can be found (12 Hive folder) at C:\Program Files\Common Files\Microsoft Shared\web server extensions\12\Template\Features\EventsList. Yes, it is event list feature. Now the modifications are required in schema.xml that can be found under Events folder (folder location, once again, is ..\12\TEMPLATE\FEATURES\EventsList\Events). This file can be modified in notepad.exe.
While looking into the Calendar List Settings in Internet Explorer, I noticed that this field 'Workspace' is of type 'Cross Project Link'. This is important to remember, and search 'CrossProjectLink' in schema.xml file. I noticed there is only one instance of it.
Secondly, the field is SharePoint field so looked for the corresponding class which is SPField. I looked into SPField Class Members on MSDN and found a couple of interesting properties (and used two of them), for example, ShowInDisplayForm, ShowInEditForm, ShowInListSettings, ShowInNewForm, ShowInVersionHistory, ShowInViewForms. I was interested in ShowInEditForm and ShowInNewForm properties only. So in schema.xml file, for Field of Type="CrossProjectLink", added these two properties as attributes, and set the value="FALSE".
IMPORTANT: attribute value should be set as ShowInNewForm="FALSE" (case sensitive). If you set the value as ShowInNewForm="false", it will not work, so be careful here.
My final <Field> is looked like:
<Field ID="{08fc65f9-48eb-4e99-bd61-5946c439e691}" Type="CrossProjectLink" Name="WorkspaceLink" Format="EventList" DisplayName="$Resources:core,Workspace;" DisplayImage="mtgicon.gif" HeaderImage="mtgicnhd.gif" ClassInfo="Icon" Title="$Resources:core,Meeting_Workspace;" Filterable="TRUE" Sealed="TRUE" SourceID="http://schemas.microsoft.com/sharepoint/v3" StaticName="WorkspaceLink" ShowInEditForm="FALSE" ShowInNewForm="FALSE">
As mentioned, I added only two attributes.
Save the schema.xml file.

Now the task is to publish the feature with changes. And since the feature is already installed and activated, so it is mandatory to de-activate it and then un-install it.

To smoothen the process I wrote two batch files one each for de-activate, un-install and install, activate:
script for uninstallfeature.bat

path="C:\Program Files\Common Files\Microsoft Shared\web server extensions\12\BIN"
cd\
cd "C:\Program Files\Common Files\Microsoft Shared\web server extensions\12\BIN"
stsadm.exe -o deactivatefeature -filename EventsList\Feature.xml -url http://moss2007:8551/sites/Demo1
stsadm.exe -o uninstallfeature -filename EventsList\Feature.xml -force
cd\

It is straight forward to understand, however, if you want instructions for the same, can send me request.

script for installfeature.bat

path="C:\Program Files\Common Files\Microsoft Shared\web server extensions\12\BIN"
cd\
cd "C:\Program Files\Common Files\Microsoft Shared\web server extensions\12\BIN"
stsadm.exe -o installfeature -filename EventsList\Feature.xml
stsadm.exe -o activatefeature -filename EventsList\Feature.xml -url http://moss2007:8551/sites/demo1
cd\

URL is required to install the feature, in this script, I am deploying the feature for a specific website.
Dont forget to do IISRESET

I am done, and when I tried to create or edit a calendar event, I don't see 'Workspace' option in there. I double checked that attach file is working, and I am able to create a 'Recurring' event too.
It is really very useful tip for me, so thought let me add it here for future reference too.

Update as on 01/13/2009:
1. The approach described here is applicable for a website. For example, if there are different calendars for different locations or divisions for an organization, then this approach can be used to hide a field in all calendars in that website. However, if the requirement is to show/hide 'Workspace' field for a specific list, there is a tool available in http://www.codeplex.com/spm [Thanks for Boris].

SQL Express 2005 - changing sa password from osql command

I installed MOSS 2007 on stand alone system, and by default it installed with SQL Express 2005. Now I wanted to connect to it from osql command using 'sa' account, I tried to connect to it using sa and 'null' password but failed with error 'Login failed for user 'sa''.
I googled and found very interesting support article here.

The steps to change the 'sa' password from command prompt is given here.
1. Goto Command Prompt.
2. Type: osql -E -S servername\instancename [and press Enter]. This option will work only if you are logged on as administrator on the machine or using the account that you used while installing sharepoint. Otherwise this option will not work. It worked for me because I am the administrator on my machine and same account was used to install MOSS.
3. After successfully connected to SQL instance you will see sql prompt as ">".
4. Type:
1>sp_password @old=null, @new='pass123#$', @loginame='sa'
2>go
1>
Important: @loginame (this is correct, there is no double "n" in login name... )
You must see 1> once password is changed successfully. Quit from windows authentication, and try connecting using the new 'sa' password.
1>quit
C:\>
C:\>osql -S servername\OfficeServers -U sa -P pass123#$ [and Press Enter]. You should be able to see the SQL Prompt which is:
1>

Hoping it is useful for someone as it was for me.

Who is connected to a server using Remote Desktop (command line command)

I was trying to connect to a machine, and then I get 'terminal server connection exceeded...' notification.
I was wondering if there is a way to find out who is connected to the machine so that i can send an email who is in 'Disconnected' mode or something.
I took the help of a network administrator, and he gave me one simple command that I ran from command line and it gave me the list of all sessions connected/disconnected to the machine.
Here you go:
C:\>qwinsta /server:urservername [press enter]

If you want to remove any instance/session, then run the following:
C:\>rwinsta /server:urservername ID# [press enter]

I like to mention it here though.

Read AppSettings Section from MyApp.dll.config

Its been long time, when I was struggling to read DLL Configuration file settings, and finally I found the solution. My app.config content is as :


<configuration>
<configSections>
<section name="log4net" type="log4net.Config.Log4NetConfigurationSectionHandler"/>
<sectionGroup name="applicationSettings" type="System.Configuration.ApplicationSettingsGroup, System, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" ><section name="Lab3.Properties.Settings" type="System.Configuration.ClientSettingsSection, System, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" requirePermission="false" />
</sectionGroup>
</configSections>
<log4net debug="true">
<appender name="RollingLogFileAppender" type="log4net.Appender.RollingFileAppender,log4net">
<param name="File" value="C:\\AppConfig\\Log.txt"/>
<param name="AppendToFile" value="true"/>
<param name="RollingStyle" value="Composite"/>
<param name="DatePattern" value="yyyyMMdd"/>
<param name="MaxSizeRollBackups" value="10"/>
<param name="MaximumFileSize" value="50KB"/>
<layout type="log4net.Layout.PatternLayout,log4net">
<param name="ConversionPattern" value="%d %-5p %c [%x] - %m%n"/>
</layout>
</appender>
<root>
<priority value="ALL"/>
<appender-ref ref="RollingLogFileAppender"/>
</root>
</log4net>
<appSettings>
<add key="UserName" value="MyUserName"/>
</appSettings>
<applicationSettings>
<MyApp.Properties.Settings>
<setting name="MyApp_SampleWS_Service1" serializeAs="String">
<value>http://localhost/SampleWebService/Service1.asmx</value>
</setting>
</Lab3.Properties.Settings>
</applicationSettings>
</configuration>


The assembly (MyApp.dll) is deployed in GAC, and the once compiled, I copied MyApp.dll.config from build folder to C:\AppConfig. This path is hardcoded in the code so that it can be picked up easily by the code.
This app.config has three major parts:
1. Log4Net - to write log details in a text file.
2. AppSettings
3. WebService Reference.

The piece of code that reads this config file from C:\AppConfig location:


ExeConfigurationFileMap fileMap = new ExeConfigurationFileMap();
fileMap.ExeConfigFilename = @"C:\AppConfig" + @"\MyApp.dll.config";
if (!File.Exists(fileMap.ExeConfigFilename))
{
logger.Info("File " + fileMap.ExeConfigFilename + " does not found.");
return;
}
assemblyConfig = ConfigurationManager.OpenMappedExeConfiguration(fileMap, ConfigurationUserLevel.None);
AppSettingsSection appSettings = assemblyConfig.AppSettings;
logger.Info("App Settings found");
logger.Info("Key=UserName, Value=" + appSettings.Settings["UserName"].Value);
logger.Info("End of Information");

And two static variable declarations:

private static ILog logger = LogManager.GetLogger("Atul.Sample.ConfigSample");
private static Configuration assemblyConfig;

Add following line of code in AssemblyInfo.cs too to make log4net working correctly.

[assembly: log4net.Config.XmlConfigurator(ConfigFile = @"C:\AppConfig\MyApp.dll.config", Watch = true)]

Once again, make sure to copy 'MyApp.dll.config' in the C:\AppConfig location to make it work correctly.

Assumption is that your assembly is 'strongly named' as this is required for deployment in GAC.