SQL Server Q&A

Symptoms
The System.Environment class has methods to read the environment variables. However, this class hasno method to set the environment variables for the current process.
Resolution
To work around this problem, use the interop services to set the environment variables. You can set an environment variable by using the Microsoft Platform Software Development Kit (SDK) SetEnvironmentVariable function.
To set an environment variable by calling the Platform SDK SetEnvironmentVariable function, follow these steps: Start Microsoft Visual Studio 2005 or Microsoft Visual Studio .NET. On the File menu, point to New, and then click New Project.In the New Project dialog box, click Visual C# Projects.
Note In Visual Studio 2005, Visual C# Projects is be changed to Visual C#.Under Templates, click Console Application, and then click OK. By default, the Class1.cs file is created.In the code view of the Class1.cs file, specify the using statement to declare the namespaces so that you do not have to qualify the declarations later in the code. Paste the following code in the Class1.cs file:

using System;using System.Runtime.InteropServices;using System.Security;using System.Security.Permissions;Declare the static method and the extern method. Use the DllImport attribute to import the Kernel32.dll file. This declaration indicates that the definition of the function is outside the code. To do this, paste the following code in the Class1.cs file:

// Import the Kernel32 dll file.[DllImport("kernel32.dll",CharSet=CharSet.Auto, SetLastError=true)][return:MarshalAs(UnmanagedType.Bool)]// The declaration is similar to the SDK functionpublic static extern bool SetEnvironmentVariable(string lpName, string lpValue);Paste the following code in the Class1.cs file to add a static method in the class that calls the SetEnvironmentVariable method and that sets the environment variable:

public static bool SetEnvironmentVariableEx(string environmentVariable, string variableValue){ try { // Get the write permission to set the environment variable. EnvironmentPermission environmentPermission = new EnvironmentPermission(EnvironmentPermissionAccess.Write,environmentVariable); environmentPermission.Demand(); return SetEnvironmentVariable(environmentVariable, variableValue); } catch( SecurityException e) { Console.WriteLine(“Exception:” + e.Message); } return false;}Paste the following code in the Main method to set an environment variable:

// Create a sample environment variable and set its value (for the current process).SampleSetEnvironmentVariable.SetEnvironmentVariableEx(“TESTENV”, “TestValue”);Paste the following code to display the environment variable value:

// Verify that environment variable is set correctly.Console.WriteLine(“The value of TESTENV is: ” + Environment.GetEnvironmentVariable(“TESTENV”));Complete Code Sample

using System;using System.Runtime.InteropServices;using System.Security;using System.Security.Permissions;namespace SetEnv{ /// <summary> /// Summary description for Class1. /// </summary> public class SampleSetEnvironmentVariable {// Import the kernel32 dll.[DllImport("kernel32.dll",CharSet=CharSet.Auto, SetLastError=true)][return:MarshalAs(UnmanagedType.Bool)]// The declaration is similar to the SDK functionpublic static extern bool SetEnvironmentVariable(string lpName, string lpValue); public SampleSetEnvironmentVariable() { } public static bool SetEnvironmentVariableEx(string environmentVariable, string variableValue) { try { // Get the write permission to set the environment variable. EnvironmentPermission environmentPermission = new EnvironmentPermission(EnvironmentPermissionAccess.Write,environmentVariable); environmentPermission.Demand(); return SetEnvironmentVariable(environmentVariable, variableValue); } catch( SecurityException e) { Console.WriteLine(“Exception:” + e.Message); } return false; } } class MyClass { /// <summary> /// The main entry point for the application. /// </summary> [STAThread] static void Main(string[] args) { // Create a sample environment variable and set its value (for the current process). SampleSetEnvironmentVariable.SetEnvironmentVariableEx(“TESTENV”, “TestValue”); // Verify that environment variable is set correctly. Console.WriteLine(“The value of TESTENV is: ” + Environment.GetEnvironmentVariable(“TESTENV”)); } }}

Symptoms
Adovb.exe is a self-extracting compressed file containing sample code thatdemonstrates how to use ActiveX Data Objects (ADO) within Visual Basic.
Resolution
The following file is available for download from the Microsoft Download Center:
http://download.microsoft.com/download/vb60pro/demo/1/WIN98/EN-US/AdoVB.EXE(http://download.microsoft.com/download/vb60pro/demo/1/win98/en-us/adovb.exe)For additional information about how to download Microsoft Support files, click the following article number to view the article in the Microsoft Knowledge Base:
119591?(http://support.microsoft.com/kb/119591/EN-US/) How to Obtain Microsoft Support Files from Online ServicesMicrosoft scanned this file for viruses. Microsoft used the most current virus-detection software that was available on the date that the file was posted. The file is stored on security-enhanced servers that help to prevent any unauthorized changes to the file.
Collapse this tableExpand this table
FileNameSizeDateAdoDemo.mdb332KB7/28/97AdoVB18KB8/5/97AdoVB.vbp1KB8/5/97AdoVbEx6KB7/28/97
What Adovb DemonstratesAdovb demonstrates proper use of output and return parameters (for SQLServer), and opening a parameterized recordset for both a Microsoft Access and SQL Server data sources. Also included is a generic template for error handling with ADO code:

Private Sub cmdTemplate_Click()Dim Conn1 As adodb.ConnectionOn Error GoTo VbError’ Trap (non-ADO) error/exceptions’ Create Connection Object (using early binding)Set Conn1 = new ADODB.ConnectionOn Error GoTo AdoError’ Trap any error/exceptionConn1.ConnectionString = AccessConnectConn1.Open’———————-’ YOUR CODE GOES HERE!’———————-’ Successful ShutdownConn1.CloseDone:’ Miscellaneous (graceful) CleanupOn Error Resume NextSet Conn1 = NothingExit Sub’ ADO Error/Exception HandlerAdoError:’ Save Error Information!ErrNumber = Err.NumberErrSource = Err.SourceErrDescription = Err.DescriptionAdoErrorEx List1, Conn1′ Non-ADO Native error/exception handlerVbError:VbErrorEx List1, ErrNumber, ErrSource, ErrDescriptionGoTo DoneEnd Sub NOTES:When using ADOVB with ADO 2.0 or later, you should remove the Reference to Microsoft OLE DB ActiveX Data Objects 1.0, and set a Reference to the latest version of Microsoft ActiveX Data Objects available.When using ADO 2.0 or later, referencing the OriginalValue property of an ADO Field object will give the following error, if the LockType of the ADO Recordset is Read Only:

Run-time error ‘3251′:The operation requested by the application is not supported by the provider.To avoid this error in ADOVB, comment out or remove the following line of code, in the Click event procedure for cmdAccess in the code window for frmADOVB:

List1.AddItem vbTab & “OriginalValue= ” & rs1.Fields(i).OriginalValue