wilsoada
asked on
SQL database in VS2005 windows application not allowing write operations
Question posed in https://www.experts-exchange.com/questions/22418035/SQL-database-in-VS2005-windows-application-not-allowing-write-operations.html
still exists re opening a new question to award points to someone
still exists re opening a new question to award points to someone
can you explain briefly about your application setup?
ASKER
create a windows application add new item SQL database
then add a new table named customers with CustID and CustomerName
using the code from the previous question and the generated My.Settings,DatabaseConnec tionString I can't save or update to the database
It's a very simple setup.
then add a new table named customers with CustID and CustomerName
using the code from the previous question and the generated My.Settings,DatabaseConnec
It's a very simple setup.
with the connection string
Data Source=.\SQLEXPRESS;Initia l Catalog=yourdbname;Persist Security Info=True;Integrated Security=True;
give your execute nonquery stuff inside the with block or do not use with itself and see how it works
in the mean while see whether full trust security mode is set in your project properties security tab
Data Source=.\SQLEXPRESS;Initia
give your execute nonquery stuff inside the with block or do not use with itself and see how it works
in the mean while see whether full trust security mode is set in your project properties security tab
ASKER
n Project Properties Security > the checkbox enable ClickOnce Security Settings is not checked. Therefore everything on that page is disabled.
ASKER
Data Source=.\SQLEXPRESS;Initia l Catalog=Database.mdf;Persi st Security Info=True;Integrated Security=True;
Generates the error:
System.Data.SqlClient.SqlE xception was unhandled
Class=11
ErrorCode=-2146232060
LineNumber=65536
Message="Cannot open database "Database.mdf" requested by the login. The login failed.
Login failed for user 'Adrenaline\Adam Wilson'."
Number=4060
Procedure=""
Server=".\SQLEXPRESS"
Source=".Net SqlClient Data Provider"
State=1
StackTrace:
at System.Data.SqlClient.SqlI nternalCon nection.On Error(SqlE xception exception, Boolean breakConnection)
at System.Data.SqlClient.TdsP arser.Thro wException AndWarning (TdsParser StateObjec t stateObj)
at System.Data.SqlClient.TdsP arser.Run( RunBehavio r runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
at System.Data.SqlClient.SqlI nternalCon nectionTds .CompleteL ogin(Boole an enlistOK)
at System.Data.SqlClient.SqlI nternalCon nectionTds .AttemptOn eLogin(Ser verInfo serverInfo, String newPassword, Boolean ignoreSniOpenTimeout, Int64 timerExpire, SqlConnection owningObject)
at System.Data.SqlClient.SqlI nternalCon nectionTds .LoginNoFa ilover(Str ing host, String newPassword, Boolean redirectedUserInstance, SqlConnection owningObject, SqlConnectionString connectionOptions, Int64 timerStart)
at System.Data.SqlClient.SqlI nternalCon nectionTds .OpenLogin Enlist(Sql Connection owningObject, SqlConnectionString connectionOptions, String newPassword, Boolean redirectedUserInstance)
at System.Data.SqlClient.SqlI nternalCon nectionTds ..ctor(DbC onnectionP oolIdentit y identity, SqlConnectionString connectionOptions, Object providerInfo, String newPassword, SqlConnection owningObject, Boolean redirectedUserInstance)
at System.Data.SqlClient.SqlC onnectionF actory.Cre ateConnect ion(DbConn ectionOpti ons options, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection)
at System.Data.ProviderBase.D bConnectio nFactory.C reatePoole dConnectio n(DbConnec tion owningConnection, DbConnectionPool pool, DbConnectionOptions options)
at System.Data.ProviderBase.D bConnectio nPool.Crea teObject(D bConnectio n owningObject)
at System.Data.ProviderBase.D bConnectio nPool.User CreateRequ est(DbConn ection owningObject)
at System.Data.ProviderBase.D bConnectio nPool.GetC onnection( DbConnecti on owningObject)
at System.Data.ProviderBase.D bConnectio nFactory.G etConnecti on(DbConne ction owningConnection)
at System.Data.ProviderBase.D bConnectio nClosed.Op enConnecti on(DbConne ction outerConnection, DbConnectionFactory connectionFactory)
at System.Data.SqlClient.SqlC onnection. Open()
at WindowsApplication1.frmMan ager.frmMa nager_Load (Object sender, EventArgs e) in C:\Users\Adam Wilson\Documents\Kent Oakley\Practical Gardening\Practical Gardening\frmManager.vb:li ne 87
at System.Windows.Forms.Form. OnLoad(Eve ntArgs e)
at System.Windows.Forms.Form. OnCreateCo ntrol()
at System.Windows.Forms.Contr ol.CreateC ontrol(Boo lean fIgnoreVisible)
at System.Windows.Forms.Contr ol.CreateC ontrol()
at System.Windows.Forms.Contr ol.WmShowW indow(Mess age& m)
at System.Windows.Forms.Contr ol.WndProc (Message& m)
at System.Windows.Forms.Scrol lableContr ol.WndProc (Message& m)
at System.Windows.Forms.Conta inerContro l.WndProc( Message& m)
at System.Windows.Forms.Form. WmShowWind ow(Message & m)
at System.Windows.Forms.Form. WndProc(Me ssage& m)
at System.Windows.Forms.Contr ol.Control NativeWind ow.OnMessa ge(Message & m)
at System.Windows.Forms.Contr ol.Control NativeWind ow.WndProc (Message& m)
at System.Windows.Forms.Nativ eWindow.De buggableCa llback(Int Ptr hWnd, Int32 msg, IntPtr wparam, IntPtr lparam)
at System.Windows.Forms.Unsaf eNativeMet hods.IntCr eateWindow Ex(Int32 dwExStyle, String lpszClassName, String lpszWindowName, Int32 style, Int32 x, Int32 y, Int32 width, Int32 height, HandleRef hWndParent, HandleRef hMenu, HandleRef hInst, Object pvParam)
at System.Windows.Forms.Unsaf eNativeMet hods.Creat eWindowEx( Int32 dwExStyle, String lpszClassName, String lpszWindowName, Int32 style, Int32 x, Int32 y, Int32 width, Int32 height, HandleRef hWndParent, HandleRef hMenu, HandleRef hInst, Object pvParam)
at System.Windows.Forms.Nativ eWindow.Cr eateHandle (CreatePar ams cp)
at System.Windows.Forms.Contr ol.CreateH andle()
at System.Windows.Forms.Form. CreateHand le()
at System.Windows.Forms.Contr ol.get_Han dle()
at System.Windows.Forms.Form. SetVisible Core(Boole an value)
at System.Windows.Forms.Contr ol.Show()
at WindowsApplication1.frmMai n.InvoiceM anagerTool StripMenuI tem_Click( Object sender, EventArgs e) in C:\Users\Adam Wilson\Documents\Kent Oakley\Practical Gardening\Practical Gardening\frmMain.vb:line 112
at System.Windows.Forms.ToolS tripItem.R aiseEvent( Object key, EventArgs e)
at System.Windows.Forms.ToolS tripMenuIt em.OnClick (EventArgs e)
at System.Windows.Forms.ToolS tripItem.H andleClick (EventArgs e)
at System.Windows.Forms.ToolS tripItem.H andleMouse Up(MouseEv entArgs e)
at System.Windows.Forms.ToolS tripItem.F ireEventIn teractive( EventArgs e, ToolStripItemEventType met)
at System.Windows.Forms.ToolS tripItem.F ireEvent(E ventArgs e, ToolStripItemEventType met)
at System.Windows.Forms.ToolS trip.OnMou seUp(Mouse EventArgs mea)
at System.Windows.Forms.ToolS tripDropDo wn.OnMouse Up(MouseEv entArgs mea)
at System.Windows.Forms.Contr ol.WmMouse Up(Message & m, MouseButtons button, Int32 clicks)
at System.Windows.Forms.Contr ol.WndProc (Message& m)
at System.Windows.Forms.Scrol lableContr ol.WndProc (Message& m)
at System.Windows.Forms.ToolS trip.WndPr oc(Message & m)
at System.Windows.Forms.ToolS tripDropDo wn.WndProc (Message& m)
at System.Windows.Forms.Contr ol.Control NativeWind ow.OnMessa ge(Message & m)
at System.Windows.Forms.Contr ol.Control NativeWind ow.WndProc (Message& m)
at System.Windows.Forms.Nativ eWindow.De buggableCa llback(Int Ptr hWnd, Int32 msg, IntPtr wparam, IntPtr lparam)
at System.Windows.Forms.Unsaf eNativeMet hods.Dispa tchMessage W(MSG& msg)
at System.Windows.Forms.Appli cation.Com ponentMana ger.System .Windows.F orms.Unsaf eNativeMet hods.IMsoC omponentMa nager.FPus hMessageLo op(Int32 dwComponentID, Int32 reason, Int32 pvLoopData)
at System.Windows.Forms.Appli cation.Thr eadContext .RunMessag eLoopInner (Int32 reason, ApplicationContext context)
at System.Windows.Forms.Appli cation.Thr eadContext .RunMessag eLoop(Int3 2 reason, ApplicationContext context)
at System.Windows.Forms.Appli cation.Run (Applicati onContext context)
at Microsoft.VisualBasic.Appl icationSer vices.Wind owsFormsAp plicationB ase.OnRun( )
at Microsoft.VisualBasic.Appl icationSer vices.Wind owsFormsAp plicationB ase.DoAppl icationMod el()
at Microsoft.VisualBasic.Appl icationSer vices.Wind owsFormsAp plicationB ase.Run(St ring[] commandLine)
at WindowsApplication1.My.MyA pplication .Main(Stri ng[] Args) in 17d14f5c-a337-4978-8281-53 493378c107 1.vb:line 81
at System.AppDomain.nExecuteA ssembly(As sembly assembly, String[] args)
at System.AppDomain.ExecuteAs sembly(Str ing assemblyFile, Evidence assemblySecurity, String[] args)
at Microsoft.VisualStudio.Hos tingProces s.HostProc .RunUsersA ssembly()
at System.Threading.ThreadHel per.Thread Start_Cont ext(Object state)
at System.Threading.Execution Context.Ru n(Executio nContext executionContext, ContextCallback callback, Object state)
at System.Threading.ThreadHel per.Thread Start()
Generates the error:
System.Data.SqlClient.SqlE
Class=11
ErrorCode=-2146232060
LineNumber=65536
Message="Cannot open database "Database.mdf" requested by the login. The login failed.
Login failed for user 'Adrenaline\Adam Wilson'."
Number=4060
Procedure=""
Server=".\SQLEXPRESS"
Source=".Net SqlClient Data Provider"
State=1
StackTrace:
at System.Data.SqlClient.SqlI
at System.Data.SqlClient.TdsP
at System.Data.SqlClient.TdsP
at System.Data.SqlClient.SqlI
at System.Data.SqlClient.SqlI
at System.Data.SqlClient.SqlI
at System.Data.SqlClient.SqlI
at System.Data.SqlClient.SqlI
at System.Data.SqlClient.SqlC
at System.Data.ProviderBase.D
at System.Data.ProviderBase.D
at System.Data.ProviderBase.D
at System.Data.ProviderBase.D
at System.Data.ProviderBase.D
at System.Data.ProviderBase.D
at System.Data.SqlClient.SqlC
at WindowsApplication1.frmMan
at System.Windows.Forms.Form.
at System.Windows.Forms.Form.
at System.Windows.Forms.Contr
at System.Windows.Forms.Contr
at System.Windows.Forms.Contr
at System.Windows.Forms.Contr
at System.Windows.Forms.Scrol
at System.Windows.Forms.Conta
at System.Windows.Forms.Form.
at System.Windows.Forms.Form.
at System.Windows.Forms.Contr
at System.Windows.Forms.Contr
at System.Windows.Forms.Nativ
at System.Windows.Forms.Unsaf
at System.Windows.Forms.Unsaf
at System.Windows.Forms.Nativ
at System.Windows.Forms.Contr
at System.Windows.Forms.Form.
at System.Windows.Forms.Contr
at System.Windows.Forms.Form.
at System.Windows.Forms.Contr
at WindowsApplication1.frmMai
at System.Windows.Forms.ToolS
at System.Windows.Forms.ToolS
at System.Windows.Forms.ToolS
at System.Windows.Forms.ToolS
at System.Windows.Forms.ToolS
at System.Windows.Forms.ToolS
at System.Windows.Forms.ToolS
at System.Windows.Forms.ToolS
at System.Windows.Forms.Contr
at System.Windows.Forms.Contr
at System.Windows.Forms.Scrol
at System.Windows.Forms.ToolS
at System.Windows.Forms.ToolS
at System.Windows.Forms.Contr
at System.Windows.Forms.Contr
at System.Windows.Forms.Nativ
at System.Windows.Forms.Unsaf
at System.Windows.Forms.Appli
at System.Windows.Forms.Appli
at System.Windows.Forms.Appli
at System.Windows.Forms.Appli
at Microsoft.VisualBasic.Appl
at Microsoft.VisualBasic.Appl
at Microsoft.VisualBasic.Appl
at WindowsApplication1.My.MyA
at System.AppDomain.nExecuteA
at System.AppDomain.ExecuteAs
at Microsoft.VisualStudio.Hos
at System.Threading.ThreadHel
at System.Threading.Execution
at System.Threading.ThreadHel
Now i got it, i checked it by creating as you explained.
data was updated. but the problem is i think you are trying to see the changes in the table from VS IDE server explorer. for some reason there the data is not refreshed.
i checked it from sqlserver management studio and the data is indeed updated.
may be disconnect and connect may get back the changes.
http://blogs.msdn.com/sqlexpress/archive/2006/11/22/connecting-to-sql-express-user-instances-in-management-studio.aspx
data was updated. but the problem is i think you are trying to see the changes in the table from VS IDE server explorer. for some reason there the data is not refreshed.
i checked it from sqlserver management studio and the data is indeed updated.
may be disconnect and connect may get back the changes.
http://blogs.msdn.com/sqlexpress/archive/2006/11/22/connecting-to-sql-express-user-instances-in-management-studio.aspx
you need to connect to the user instance.
how to connect to the user instance is explained in the link posted in my previous comment.
how to connect to the user instance is explained in the link posted in my previous comment.
ASKER
I don't understand the last comment. It definitely doesn't help the problem. Let me give you a replication example of exactly what I'm doing in a small example so you can re-create it.
1. Create windows application with Form1
2. On form put two textboxes and one button
3. On project add new item choose SQL database
4. Add table to database named Customer with two fields primary CustID and Name as string or text
5. Add one record to the table CustID = 1 and Name = "Test"
6. On Form1_Load use something like
cmd.CommandText = "Select * FROM Customers WHERE CustID = " & e.Node.Tag
cmd.CommandType = CommandType.Text
cmd.Connection = myConn
reader = cmd.ExecuteReader
If reader.HasRows Then
reader.Read()
Try
Me.TextBox1.Text = reader("CustID").ToString
Catch myErr As InvalidCastException
End Try
Try
Me.TextBox2.Text = reader("CustomerName")
Catch myErr As InvalidCastException
End Try
End If
reader.Close()
endif
7. In the button1_click set the update info as something like
Dim cmd As New SqlCommand
With cmd
.Connection = New SqlConnection(my.Settings. DatabaseCo nnectionSt ring)
.CommandText = "UPDATE Customers SET Name = '" & Me.TextBoxFirstNameView.Te xt & "' WHERE CustID = " & TreeView.SelectedNode.Tag
'set the parameter values
Try
cmd.Connection.Open()
cmd.ExecuteNonQuery()
Catch ex As Exception
MessageBox.Show(ex.Message )
Finally
If cmd.Connection.State = ConnectionState.Open Then
cmd.Connection.Close()
End If
End Try
End With
8 Then when you run the application change the name in textbox2 and hit the button to update
9. When you re-start the application it should load the named you changed the database to but it will always load the original information the database was instantiated with.
Maybe that will help you re-create the situation.
1. Create windows application with Form1
2. On form put two textboxes and one button
3. On project add new item choose SQL database
4. Add table to database named Customer with two fields primary CustID and Name as string or text
5. Add one record to the table CustID = 1 and Name = "Test"
6. On Form1_Load use something like
cmd.CommandText = "Select * FROM Customers WHERE CustID = " & e.Node.Tag
cmd.CommandType = CommandType.Text
cmd.Connection = myConn
reader = cmd.ExecuteReader
If reader.HasRows Then
reader.Read()
Try
Me.TextBox1.Text = reader("CustID").ToString
Catch myErr As InvalidCastException
End Try
Try
Me.TextBox2.Text = reader("CustomerName")
Catch myErr As InvalidCastException
End Try
End If
reader.Close()
endif
7. In the button1_click set the update info as something like
Dim cmd As New SqlCommand
With cmd
.Connection = New SqlConnection(my.Settings.
.CommandText = "UPDATE Customers SET Name = '" & Me.TextBoxFirstNameView.Te
'set the parameter values
Try
cmd.Connection.Open()
cmd.ExecuteNonQuery()
Catch ex As Exception
MessageBox.Show(ex.Message
Finally
If cmd.Connection.State = ConnectionState.Open Then
cmd.Connection.Close()
End If
End Try
End With
8 Then when you run the application change the name in textbox2 and hit the button to update
9. When you re-start the application it should load the named you changed the database to but it will always load the original information the database was instantiated with.
Maybe that will help you re-create the situation.
ASKER
The My.Settings.DatabaseConnec tionString is automatically generated when you create the project above so how is it that if the code runs correctly its not actually updating the database. Is it because each time you run the application it creates a new user instance of the original database to use?
ASKER
Found it search for something called the Copy to Output Directory Setting with SQL Express Databases in Visual Studio Applications
Yes i followed your steps to recreat the problem. it works fine.
the only change is instead of TreeView.SelectedNode.Tag i used hardcoded value, if that makes any difference.
connection string from my config file is as follows
<add name="WindowsApplication1. My.MySetti ngs.Databa se1Connect ionString"
connectionString="Data Source=.\SQLEXPRESS;Attach DbFilename =|DataDire ctory|\Dat abase1.mdf ;Integrate d Security=True;User Instance=True"
providerName="System.Data. SqlClient" />
the only change is instead of TreeView.SelectedNode.Tag i used hardcoded value, if that makes any difference.
connection string from my config file is as follows
<add name="WindowsApplication1.
connectionString="Data Source=.\SQLEXPRESS;Attach
providerName="System.Data.
i just noticed
in form load you are using
cmd.CommandText = "Select * FROM Customers WHERE CustID = " & e.Node.Tag
while updating using
.CommandText = "UPDATE Customers SET Name = '" & Me.TextBoxFirstNameView.Te xt & "' WHERE CustID = " & TreeView.SelectedNode.Tag
do you have same values in e.Node.Tag and TreeView.SelectedNode.Tag?
for testing instead of TreeView.SelectedNode.Tag and e.node.tag use some existing userid value and see
in form load you are using
cmd.CommandText = "Select * FROM Customers WHERE CustID = " & e.Node.Tag
while updating using
.CommandText = "UPDATE Customers SET Name = '" & Me.TextBoxFirstNameView.Te
do you have same values in e.Node.Tag and TreeView.SelectedNode.Tag?
for testing instead of TreeView.SelectedNode.Tag and e.node.tag use some existing userid value and see
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
It means when running this type of embedded database the write options will never be saved to the main version of the database. However, when you publish it the application works correctly. It is described in the MSDN. Basically, the main version fo the database when you run with F5 for debugging copies a copy of the db to the bin directory which the changes are saved to. Everytime you run the app it recopies a new copy of the database to the bin directory so it will seem like your changes aren't being saved. After publishing the database runs as a single entity and works correctly. I published my test application and can dave and modify with no problems. Thanks for your help guys.
ASKER
when you have instance already running why are you attaching the file and creating user instance again?
instead of attaching the db file try if you can connect to existing instance and see how it works.
try with the following connection string
Data Source=.\SQLEXPRESS;Initia
This doesn't work either the connection string I am using is the one generated by VS2005 when I created the application. If I run a test connection it tests fine but no matter what type of write operation I used as in previous question the application simply re-starts with the original database information