locked
Problem with data refreshing after insert or update statement RRS feed

  • Question

  • Dear All,

     

    Im using VS2008, visual basic and SQL Server express.

     

    General questions please: Is it necessary, to issue some sort of command, to 'commit' data to a SQL express database after a INSERT or UPDATE sql command?

     

    I'm getting strange behavior where the data is not refreshed unless i exit my app and re-enter. In other words, i can run a sql command , the data is apparantly saved (because i get no errors) then if i refresh a data set or do a sql select query the data that i expect to return is not there.

     

    Im fairly new to SQL express (and SQL server generally) so i dont know if its my coding or i need to switch some 'feature'

    on/off or not.

     

    I hope thats clear

     

    Also, could someone point me to documentation that explains each parameter in the connection string

     

    Many Thanks

     

    Chris Anderson

     

     

    My code is:

     

    ConnectionString = "Data Source=.\SQLEXPRESS;AttachDbFilename=C:\myfile.mdf;Integrated Security=True;Connect Timeout=30;User Instance=True"

    Connection = New SqlConnection

    Connection.ConnectionString = ConnectionString

    Connection.Open()

    '''''''''''''''''the above code is done at the start of my application

     

     

    '''''''this code below called during application many times

     

     

    dim sql as string = "my sql string here"

    Dim cmd As SqlCommand = Nothing

    cmd = New SqlCommand(sql, Connection)

     

     

     

     

    Try

    cmd.ExecuteNonQuery()

    Catch err As SqlException

    MessageBox.Show(err.Message.ToString())

    MessageBox.Show(sql, "SQL ERROR: ", MessageBoxButtons.OK, MessageBoxIcon.Error)

    End Try

     

    cmd.Dispose()

    Tuesday, March 18, 2008 10:07 PM

Answers

  • hi,

     chrisanderson wrote:

    ...

    General questions please: Is it necessary, to issue some sort of command, to 'commit' data to a SQL express database after a INSERT or UPDATE sql command?

    ...

    >dim sql as string = "my sql string here"

    assuming that sql string variable is filled with an INSERT/UPDATE/DELETE statement, you are ok as regard the DML statement.. you still have to re-fetch the modification from the base table(s) you just posted..

     

    ConnectionString = "Data Source=.\SQLEXPRESS;AttachDbFilename=C:\myfile.mdf;Integrated Security=True;Connect Timeout=30;User Instance=True"

    I just have a thought, here... you work against a User Instance, and the "typical" behaviour of this is, if you set the "file" property in the VS IDE "copy to output directory" to "copy always" and not to "copy if newer", the "original" database will overwrite the modified database you are using when running your app in the VS ide, and this can be solved modifying that property value of the database file, as reported by Mke.. but you report an "alternative" behaviour.. you do save and can not refresh the saved data until next application run.. to me this can mean you are connecting to a wrong instance at update time or at refresh time.. that's to say you are querying a different database from the one you are updating.. can you please check this?

     

    Also, could someone point me to documentation that explains each parameter in the connection string

    a "good" list of samples about connection strings can be found here.. and you can have a look here about all related properties..

     

    Wednesday, March 19, 2008 3:36 PM

All replies

  • hi,

     chrisanderson wrote:

    ...

    General questions please: Is it necessary, to issue some sort of command, to 'commit' data to a SQL express database after a INSERT or UPDATE sql command?

    ...

    >dim sql as string = "my sql string here"

    assuming that sql string variable is filled with an INSERT/UPDATE/DELETE statement, you are ok as regard the DML statement.. you still have to re-fetch the modification from the base table(s) you just posted..

     

    ConnectionString = "Data Source=.\SQLEXPRESS;AttachDbFilename=C:\myfile.mdf;Integrated Security=True;Connect Timeout=30;User Instance=True"

    I just have a thought, here... you work against a User Instance, and the "typical" behaviour of this is, if you set the "file" property in the VS IDE "copy to output directory" to "copy always" and not to "copy if newer", the "original" database will overwrite the modified database you are using when running your app in the VS ide, and this can be solved modifying that property value of the database file, as reported by Mke.. but you report an "alternative" behaviour.. you do save and can not refresh the saved data until next application run.. to me this can mean you are connecting to a wrong instance at update time or at refresh time.. that's to say you are querying a different database from the one you are updating.. can you please check this?

     

    Also, could someone point me to documentation that explains each parameter in the connection string

    a "good" list of samples about connection strings can be found here.. and you can have a look here about all related properties..

     

    Wednesday, March 19, 2008 3:36 PM
  • Andrea,

     

    Many thanks for your reply.

     

    >you still have to re-fetch the modification from the base table(s) you just posted..

     

    Not sure what you mean here ??

     

    As regards, the 'user instance' - im not sure whta this means, as i took the connection string from a example somewhere.

    I do hard code the path to the mdf file in my connection string so am i write to assume it wouldnt matter what the setting is regarding copying the file ? The file i am querying and the file i am updating to use the same connection string.

     

    Thanks for the links re properties etc, will take a look at those.

     

    regards

     

    Chris

     

     

     

     

     

     

    Wednesday, March 19, 2008 6:10 PM
  • hi Chris,

     chrisanderson wrote:

     

    >you still have to re-fetch the modification from the base table(s) you just posted..

     

    Not sure what you mean here ??

    the code you posted just perform the 1 part of the whole requirements.. it probably perform and INSERT INTO ... or UPDATE .... SET ...

    this is ok, but you have to re-execute a SELECT .. FROM.. in order to re-fetch the updated data from the database you are accessing..

     

    As regards, the 'user instance' - im not sure whta this means, as i took the connection string from a example somewhere.

    I do hard code the path to the mdf file in my connection string so am i write to assume it wouldnt matter what the setting is regarding copying the file ? The file i am querying and the file i am updating to use the same connection string.

    if the connection strings are the same, then you are accessing the same database.. so please check you perform:

    - update the row you like to (as per the code you submitted)

    - re-fetch the updated data from the database (performing a SELECT col1, col2 FROM table WHERE ...) as you do at the application init..

    regards

    Friday, March 21, 2008 12:09 AM
  •  

    Andrea,

     

    Many thanks for your replies.

     

    Problem solved, i was actually using 2 databases without knowing it. The datasets that auto generated for me used a connection string in app.config which changed if you were debugging or not (it used the datadirectory variable.

     

    Ive know changed all connections to my prefered connection string and all seems fine.

     

    Many thanls for your hel[

     

    Chris

     

    Saturday, March 22, 2008 2:36 PM