Connection Manager

I am using a connection manager defined as a '.Net Providers\SqlClient Data Provider'

I choose the .net form since it has the ability to return multiple result sets.

However, when I set the Advanced 'MultipleActiveResultsSets' to True and check OK,

it doesn't save the setting.

This setting is available under the 'All' window, when you add the Connection Manager.

Any clues as to why this feature isn't functional? Or is this a known bug?

I am piping this into a DataConversion Task

SSIS Data Flow adapters will work only with single result sets.

Thanks,

Bob

|||

Thanks for the answer Bob.

I guess what SSIS appears to me to be is an updated DTS, some of the benefits are offset by the need to script advance functionality.

But isn't that what we did with DTS?

I just figured if you include .Net connections then you should support multiple result sets.

Is this a future option?

I am not completely disappointed with SSIS, but the learning curve doesn't appear to have that much of a reward when functionality like this is missing.

Again thanks for answering my question.

Connection Issue to SQL Express form VB Express

Anyone know how to fix this error:

System.Data.SqlClient.SqlException was unhandled
Class=16
ErrorCode=-2146232060
LineNumber=65536
Message="Directory lookup for the file "C:\Documents and Settings\zimmerman_e\My Documents\Visual Studio 2005\Projects\MGM_REPORTS\MGM_REPORTS\ImportFile.mdf" failed with the operating system error 5(Access is denied.).
Could not attach file 'C:\Documents and Settings\zimmerman_e\My Documents\Visual Studio 2005\Projects\MGM_REPORTS\MGM_REPORTS\ImportFile.mdf' as database 'dbname'."
Number=5133
Procedure=""
Server=".\SQLExpress"
Source=".Net SqlClient Data Provider"
State=1
StackTrace:
at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
at System.Data.SqlClient.SqlInternalConnectionTds.CompleteLogin(Boolean enlistOK)
at System.Data.SqlClient.SqlInternalConnectionTds.OpenLoginEnlist(SqlConnection owningObject, SqlConnectionString connectionOptions, String newPassword, Boolean redirectedUserInstance)
at System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, Object providerInfo, String newPassword, SqlConnection owningObject, Boolean redirectedUserInstance)
at System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection)
at System.Data.ProviderBase.DbConnectionFactory.CreatePooledConnection(DbConnection owningConnection, DbConnectionPool pool, DbConnectionOptions options)
at System.Data.ProviderBase.DbConnectionPool.CreateObject(DbConnection owningObject)
at System.Data.ProviderBase.DbConnectionPool.UserCreateRequest(DbConnection owningObject)
at System.Data.ProviderBase.DbConnectionPool.GetConnection(DbConnection owningObject)
at System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection)
at System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory)
at System.Data.SqlClient.SqlConnection.Open()
at MGM_REPORTS.DbConnect.InsertSQL(String[] Values) in C:\Documents and Settings\zimmerman_e\My Documents\Visual Studio 2005\Projects\MGM_REPORTS\MGM_REPORTS\DbConnect.vb:line 149
at MGM_REPORTS.DbConnect.btnConnectDB_Click(Object sender, EventArgs e) in C:\Documents and Settings\zimmerman_e\My Documents\Visual Studio 2005\Projects\MGM_REPORTS\MGM_REPORTS\DbConnect.vb:line 21
at System.Windows.Forms.Control.OnClick(EventArgs e)
at System.Windows.Forms.Button.OnClick(EventArgs e)
at System.Windows.Forms.Button.OnMouseUp(MouseEventArgs mevent)
at System.Windows.Forms.Control.WmMouseUp(Message& m, MouseButtons button, Int32 clicks)
at System.Windows.Forms.Control.WndProc(Message& m)
at System.Windows.Forms.ButtonBase.WndProc(Message& m)
at System.Windows.Forms.Button.WndProc(Message& m)
at System.Windows.Forms.Control.ControlNativeWindow.OnMessage(Message& m)
at System.Windows.Forms.Control.ControlNativeWindow.WndProc(Message& m)
at System.Windows.Forms.NativeWindow.DebuggableCallback(IntPtr hWnd, Int32 msg, IntPtr wparam, IntPtr lparam)
at System.Windows.Forms.UnsafeNativeMethods.DispatchMessageW(MSG& msg)
at System.Windows.Forms.Application.ComponentManager.System.Windows.Forms.UnsafeNativeMethods.IMsoComponentManager.FPushMessageLoop(Int32 dwComponentID, Int32 reason, Int32 pvLoopData)
at System.Windows.Forms.Application.ThreadContext.RunMessageLoopInner(Int32 reason, ApplicationContext context)
at System.Windows.Forms.Application.ThreadContext.RunMessageLoop(Int32 reason, ApplicationContext context)
at System.Windows.Forms.Application.Run(Form mainForm)
at MGM_REPORTS.DbConnect.Main() in C:\Documents and Settings\zimmerman_e\My Documents\Visual Studio 2005\Projects\MGM_REPORTS\MGM_REPORTS\DbConnect.vb:line 158
at System.AppDomain.nExecuteAssembly(Assembly assembly, String[] args)
at System.AppDomain.ExecuteAssembly(String assemblyFile, Evidence assemblySecurity, String[] args)
at Microsoft.VisualStudio.HostingProcess.HostProc.RunUsersAssembly()
at System.Threading.ThreadHelper.ThreadStart_Context(Object state)
at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state)
at System.Threading.ThreadHelper.ThreadStart()

Coudl you paste your connection string here ?

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de|||

Hi Jens, I think I got the connection to work but now I don't think its updating my database. Here is the entire code...

Imports System.Data

Imports System.Data.SqlClient

Public Class DbConnect

Dim objConnection As New SqlClient.SqlConnection("Data Source=.\SQLExpress;Integrated Security=true;AttachDbFilename=|DataDirectory|\ImportFile.mdf;User Instance=true;")

Dim objDataAdapter As New SqlDataAdapter()

Dim objDataSet As New DataSet()

Private Sub btnConnectDB_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnConnectDB.Click

'parse fixed width file

Using Reader As New Microsoft.VisualBasic.FileIO.TextFieldParser("\\netapp2\rs-common-d\AAA-MGM_990500024\Payrolls\Test.DAT")

Reader.TextFieldType = Microsoft.VisualBasic.FileIO.FieldType.FixedWidth

Reader.SetFieldWidths(9, 9, 35, 10, 35, 10, 10, 10, 10, 2, 8, 8, 5, 5, _

5, 8, 8, 30, 30, 3, 35, 2, 9, 9, 4, 10, 10, 3, 3, 10, 10, 10, 10, 10, _

1, 4, 5, 1, 1, 10, 4, 9, 15, 7, 1, 5, 7, 8, 2, 1, 2, 1, 8, 1, -1)

While Not Reader.EndOfData

Try

Dim Fields() As String = Reader.ReadFields

InsertSQL(Fields)

Catch ex As Microsoft.VisualBasic.FileIO.MalformedLineException

MsgBox("Line " & ex.Message & _

" is not valid and will be skipped.")

Exit While

End Try

End While

End Using

End Sub

Private Sub InsertSQL(ByVal Values() As String)

Dim cmd As New SqlClient.SqlCommand()

cmd.CommandText = "INSERT INTO MgmPayroll(PlanId,SSN,FirstName,MiddleName,LastName,Elec,Comp,GrossComp,Loans,Filler,PayrollDate,TermDate,Filler2,DivisionCode,Department,DOH,DOB,Address1,Address2,CountryCode,City,State,Zip,EmpNum,Location,Match,PayRate,PayFreq,Filler3,Elec2,PriorYearComp,YTDComp,YTDEligComp,Loan2,UnionMember,UnionCode,StatusCode,Filler4,MGMExecs,PhoneNum,LocationNew,EmpNumNew,LoanId,YTDHours,PayrollFreq,PensionCode,PeriodHours,RehireDate,Filler5,FilingStatus,Exemptions,OriginalStatus,SenorityDate,MaritalStatus,Roth)Values(@.PlanId,@.SSN,@.FirstName,@.MiddleName,@.LastName,@.Elec,@.Comp,@.GrossComp,@.Loans,@.Filler,@.PayrollDate,@.TermDate,@.Filler2,@.DivisionCode,@.Department,@.DOH,@.DOB,@.Address1,@.Address2,@.CountryCode,@.City,@.State,@.Zip,@.EmpNum,@.Location,@.Match,@.PayRate,@.PayFreq,@.Filler3,@.Elec2,@.PriorYearComp,@.YTDComp,@.YTDEligComp,@.Loan2,@.UnionMember,@.UnionCode,@.StatusCode,@.Filler4,@.MGMExecs,@.PhoneNum,@.LocationNew,@.EmpNumNew,@.LoanId,@.YTDHours,@.PayrollFreq,@.PensionCode,@.PeriodHours,@.RehireDate,@.Filler5,@.FilingStatus,@.Exemptions,@.OriginalStatus,@.SenorityDate,@.MaritalStatus,@.Roth)"

cmd.Connection = objConnection

Dim p1 As New SqlClient.SqlParameter("@.PlanId", Values(0))

Dim p2 As New SqlClient.SqlParameter("@.SSN", Values(1))

Dim p3 As New SqlClient.SqlParameter("@.FirstName", Values(2))

Dim p4 As New SqlClient.SqlParameter("@.MiddleName", Values(3))

Dim p5 As New SqlClient.SqlParameter("@.LastName", Values(4))

Dim p6 As New SqlClient.SqlParameter("@.Elec", Values(5))

Dim p7 As New SqlClient.SqlParameter("@.Comp", Values(6))

Dim p8 As New SqlClient.SqlParameter("@.GrossComp", Values(7))

Dim p9 As New SqlClient.SqlParameter("@.Loans", Values(8))

Dim p10 As New SqlClient.SqlParameter("@.Filler", Values(9))

Dim p11 As New SqlClient.SqlParameter("@.PayrollDate", Values(10))

Dim p12 As New SqlClient.SqlParameter("@.TermDate", Values(11))

Dim p13 As New SqlClient.SqlParameter("@.Filler2", Values(12))

Dim p14 As New SqlClient.SqlParameter("@.DivisionCode", Values(13))

Dim p15 As New SqlClient.SqlParameter("@.Department", Values(14))

Dim p16 As New SqlClient.SqlParameter("@.DOH", Values(15))

Dim p17 As New SqlClient.SqlParameter("@.DOB", Values(16))

Dim p18 As New SqlClient.SqlParameter("@.Address1", Values(17))

Dim p19 As New SqlClient.SqlParameter("@.Address2", Values(18))

Dim p20 As New SqlClient.SqlParameter("@.CountryCode", Values(19))

Dim p21 As New SqlClient.SqlParameter("@.City", Values(20))

Dim p22 As New SqlClient.SqlParameter("@.State", Values(21))

Dim p23 As New SqlClient.SqlParameter("@.Zip", Values(22))

Dim p24 As New SqlClient.SqlParameter("@.EmpNum", Values(23))

Dim p25 As New SqlClient.SqlParameter("@.Location", Values(24))

Dim p26 As New SqlClient.SqlParameter("@.Match", Values(25))

Dim p27 As New SqlClient.SqlParameter("@.PayRate", Values(26))

Dim p28 As New SqlClient.SqlParameter("@.PayFreq", Values(27))

Dim p29 As New SqlClient.SqlParameter("@.Filler3", Values(28))

Dim p30 As New SqlClient.SqlParameter("@.Elec2", Values(29))

Dim p31 As New SqlClient.SqlParameter("@.PriorYearComp", Values(30))

Dim p32 As New SqlClient.SqlParameter("@.YTDComp", Values(31))

Dim p33 As New SqlClient.SqlParameter("@.YTDEligComp", Values(32))

Dim p34 As New SqlClient.SqlParameter("@.Loan2", Values(33))

Dim p35 As New SqlClient.SqlParameter("@.UnionMember", Values(34))

Dim p36 As New SqlClient.SqlParameter("@.UnionCode", Values(35))

Dim p37 As New SqlClient.SqlParameter("@.StatusCode", Values(36))

Dim p38 As New SqlClient.SqlParameter("@.Filler4", Values(37))

Dim p39 As New SqlClient.SqlParameter("@.MGMExecs", Values(38))

Dim p40 As New SqlClient.SqlParameter("@.PhoneNum", Values(39))

Dim p41 As New SqlClient.SqlParameter("@.LocationNew", Values(40))

Dim p42 As New SqlClient.SqlParameter("@.EmpNumNew", Values(41))

Dim p43 As New SqlClient.SqlParameter("@.LoanId", Values(42))

Dim p44 As New SqlClient.SqlParameter("@.YTDHours", Values(43))

Dim p45 As New SqlClient.SqlParameter("@.PayrollFreq", Values(44))

Dim p46 As New SqlClient.SqlParameter("@.PensionCode", Values(45))

Dim p47 As New SqlClient.SqlParameter("@.PeriodHours", Values(46))

Dim p48 As New SqlClient.SqlParameter("@.RehireDate", Values(47))

Dim p49 As New SqlClient.SqlParameter("@.Filler5", Values(48))

Dim p50 As New SqlClient.SqlParameter("@.FilingStatus", Values(49))

Dim p51 As New SqlClient.SqlParameter("@.Exemptions", Values(50))

Dim p52 As New SqlClient.SqlParameter("@.OriginalStatus", Values(51))

Dim p53 As New SqlClient.SqlParameter("@.SenorityDate", Values(52))

Dim p54 As New SqlClient.SqlParameter("@.MaritalStatus", Values(53))

Dim p55 As New SqlClient.SqlParameter("@.Roth", Values(54))

cmd.Parameters.Add(p1)

cmd.Parameters.Add(p2)

cmd.Parameters.Add(p3)

cmd.Parameters.Add(p4)

cmd.Parameters.Add(p5)

cmd.Parameters.Add(p6)

cmd.Parameters.Add(p7)

cmd.Parameters.Add(p8)

cmd.Parameters.Add(p9)

cmd.Parameters.Add(p10)

cmd.Parameters.Add(p11)

cmd.Parameters.Add(p12)

cmd.Parameters.Add(p13)

cmd.Parameters.Add(p14)

cmd.Parameters.Add(p15)

cmd.Parameters.Add(p16)

cmd.Parameters.Add(p17)

cmd.Parameters.Add(p18)

cmd.Parameters.Add(p19)

cmd.Parameters.Add(p20)

cmd.Parameters.Add(p21)

cmd.Parameters.Add(p22)

cmd.Parameters.Add(p23)

cmd.Parameters.Add(p24)

cmd.Parameters.Add(p25)

cmd.Parameters.Add(p26)

cmd.Parameters.Add(p27)

cmd.Parameters.Add(p28)

cmd.Parameters.Add(p29)

cmd.Parameters.Add(p30)

cmd.Parameters.Add(p31)

cmd.Parameters.Add(p32)

cmd.Parameters.Add(p33)

cmd.Parameters.Add(p34)

cmd.Parameters.Add(p35)

cmd.Parameters.Add(p36)

cmd.Parameters.Add(p37)

cmd.Parameters.Add(p38)

cmd.Parameters.Add(p39)

cmd.Parameters.Add(p40)

cmd.Parameters.Add(p41)

cmd.Parameters.Add(p42)

cmd.Parameters.Add(p43)

cmd.Parameters.Add(p44)

cmd.Parameters.Add(p45)

cmd.Parameters.Add(p46)

cmd.Parameters.Add(p47)

cmd.Parameters.Add(p48)

cmd.Parameters.Add(p49)

cmd.Parameters.Add(p50)

cmd.Parameters.Add(p51)

cmd.Parameters.Add(p52)

cmd.Parameters.Add(p53)

cmd.Parameters.Add(p54)

cmd.Parameters.Add(p55)

objConnection.Open()

cmd.ExecuteNonQuery()

objConnection.Close()

End Sub

<STAThread()> _

Shared Sub Main()

Application.EnableVisualStyles()

Application.Run(New DBConnect)

End Sub

End Class

|||Use proper exception handling around the database call to encapsulate and catch the error message.

Try
objConnection.Open()

cmd.ExecuteNonQuery()

Catch e As Exception
Response.Write(e.Message.toString)
End Try

(Hope this is right as I am not a VB guy :-) )

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

Connecting to SQL Server Express in VS2003

Hi,

Please assist with the following.

How do I connect to SQL Server Express in VS2003? "Do I still use using System.Data.SqlClient;"?

What would the connection string be in the code? The following does not work.

try
{
string connectionString = "server=localhost;uid=webserver;pwd=wordpass;database=dbMV;";
// create a new SqlConnection object with the appropriate connection string

SqlConnection sqlConn = new SqlConnection(connectionString);

// open the connection
sqlConn.Open();
// do some operations ...
// close the connection
sqlConn.Close();
}
catch(Exception err)
{
Response.Write(err);
}

Try to write you connection string as following:

string connectionString = "server=localhost;User ID=webserver;pwd=wordpass;database=dbMV;";

For more information about connection string, please refer to:

http://msdn2.microsoft.com/en-us/library/system.data.sqlclient.sqlconnection.connectionstring(d=ide).aspx