Link to home
Start Free TrialLog in
Avatar of wilsoada
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
Avatar of wilsoada
wilsoada

ASKER

IN REGARDS TO THIS STATEMENT BY APARI IN PREVIOUS QUESTION

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;Initial Catalog=yourdbname;Persist Security Info=True;Integrated Security=True;

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

can you explain briefly about your application setup?
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,DatabaseConnectionString I can't save or update to the database

It's a very simple setup.
with the connection string

Data Source=.\SQLEXPRESS;Initial 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
n Project Properties Security > the checkbox enable ClickOnce Security Settings is not checked. Therefore everything on that page is disabled.
Data Source=.\SQLEXPRESS;Initial Catalog=Database.mdf;Persist Security Info=True;Integrated Security=True;

Generates the error:

System.Data.SqlClient.SqlException 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.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.AttemptOneLogin(ServerInfo serverInfo, String newPassword, Boolean ignoreSniOpenTimeout, Int64 timerExpire, SqlConnection owningObject)
       at System.Data.SqlClient.SqlInternalConnectionTds.LoginNoFailover(String host, String newPassword, Boolean redirectedUserInstance, SqlConnection owningObject, SqlConnectionString connectionOptions, Int64 timerStart)
       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 WindowsApplication1.frmManager.frmManager_Load(Object sender, EventArgs e) in C:\Users\Adam Wilson\Documents\Kent Oakley\Practical Gardening\Practical Gardening\frmManager.vb:line 87
       at System.Windows.Forms.Form.OnLoad(EventArgs e)
       at System.Windows.Forms.Form.OnCreateControl()
       at System.Windows.Forms.Control.CreateControl(Boolean fIgnoreVisible)
       at System.Windows.Forms.Control.CreateControl()
       at System.Windows.Forms.Control.WmShowWindow(Message& m)
       at System.Windows.Forms.Control.WndProc(Message& m)
       at System.Windows.Forms.ScrollableControl.WndProc(Message& m)
       at System.Windows.Forms.ContainerControl.WndProc(Message& m)
       at System.Windows.Forms.Form.WmShowWindow(Message& m)
       at System.Windows.Forms.Form.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.IntCreateWindowEx(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.UnsafeNativeMethods.CreateWindowEx(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.NativeWindow.CreateHandle(CreateParams cp)
       at System.Windows.Forms.Control.CreateHandle()
       at System.Windows.Forms.Form.CreateHandle()
       at System.Windows.Forms.Control.get_Handle()
       at System.Windows.Forms.Form.SetVisibleCore(Boolean value)
       at System.Windows.Forms.Control.Show()
       at WindowsApplication1.frmMain.InvoiceManagerToolStripMenuItem_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.ToolStripItem.RaiseEvent(Object key, EventArgs e)
       at System.Windows.Forms.ToolStripMenuItem.OnClick(EventArgs e)
       at System.Windows.Forms.ToolStripItem.HandleClick(EventArgs e)
       at System.Windows.Forms.ToolStripItem.HandleMouseUp(MouseEventArgs e)
       at System.Windows.Forms.ToolStripItem.FireEventInteractive(EventArgs e, ToolStripItemEventType met)
       at System.Windows.Forms.ToolStripItem.FireEvent(EventArgs e, ToolStripItemEventType met)
       at System.Windows.Forms.ToolStrip.OnMouseUp(MouseEventArgs mea)
       at System.Windows.Forms.ToolStripDropDown.OnMouseUp(MouseEventArgs mea)
       at System.Windows.Forms.Control.WmMouseUp(Message& m, MouseButtons button, Int32 clicks)
       at System.Windows.Forms.Control.WndProc(Message& m)
       at System.Windows.Forms.ScrollableControl.WndProc(Message& m)
       at System.Windows.Forms.ToolStrip.WndProc(Message& m)
       at System.Windows.Forms.ToolStripDropDown.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(ApplicationContext context)
       at Microsoft.VisualBasic.ApplicationServices.WindowsFormsApplicationBase.OnRun()
       at Microsoft.VisualBasic.ApplicationServices.WindowsFormsApplicationBase.DoApplicationModel()
       at Microsoft.VisualBasic.ApplicationServices.WindowsFormsApplicationBase.Run(String[] commandLine)
       at WindowsApplication1.My.MyApplication.Main(String[] Args) in 17d14f5c-a337-4978-8281-53493378c1071.vb:line 81
       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()
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
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.
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.DatabaseConnectionString)
            .CommandText = "UPDATE Customers SET Name = '" & Me.TextBoxFirstNameView.Text & "' 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.
The My.Settings.DatabaseConnectionString 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?
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.MySettings.Database1ConnectionString"
            connectionString="Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\Database1.mdf;Integrated Security=True;User Instance=True"
            providerName="System.Data.SqlClient" />
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.Text & "' 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
ASKER CERTIFIED SOLUTION
Avatar of appari
appari
Flag of India image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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.