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

Connection

Hi all,

I was on SQL 2000 and now changed over to SQL 2005 Ex Ed. When I run my application I get the following error Mssg @. Conn.Open
An unhandled exception of type 'System.Data.SqlClient.SqlException' occurred in system.data.dll
Additional information: System error.
Code:
Imports System.Data.SqlClient

Public Conn As SqlConnection = New SqlConnection
Dim da As New SqlDataAdapter

Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
Conn.ConnectionString = "Data Source=(local);" & _
"Initial Catalog=Rsch1;" & _
"Integrated Security=SSPI"
Conn.Open()

Did you added the ASPNET Account to the database security section|||Actually this is not a ASP.NET project.

Basically VB.NET is not accepting the Server name as (Local)
If I hardcode my servername as in 072-Rsch1 then it works fine.

I would like to have the server name as local as I was working with
SQL 2K.

Tnx
|||If you are using ado.net1.0 against SQL2K5, you need enable named pipe on both server and client to make (local) work. SQL2k5's local sharememory connection is not compatible with SQL2k's and ado.net1.0. If the SQL2k5 is default instaince, and you're using ado.net1.0, (local) is a known issue. You can upgraded to ado.net2.0 or use alias to work around if you don't want to change your connection string.