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()
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
