vb.net database(SQL Server 2005) problem - Database is not updating

[sppaladugu]

hi,

i am tring to add a new datarow to a table using the insert query, but for some reason its not working..(No exceptions thrown...) it is showing the effected rows in the database as '1' but nothing is added to the database.....if i use a select query all works fine so i guess nothing wrong with the connection.... what i am i doing wrong?????? .......

Dim DatabaseConnection As New SqlConnection("DataSource=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\Sample.mdf;Integrated Security=True;User Instance=True")
Dim DatabaseCommand As New SqlCommand

Dim InsertQuery As String
Dim DatabaseTransaction As SqlTransaction

InsertQuery = "insert into test values('shshd')"
DatabaseCommand.CommandTimeout = 60
DatabaseCommand.Connection = DatabaseConnection
DatabaseCommand.CommandType = CommandType.Text
Try
DatabaseConnection.Open()
DatabaseTransaction = DatabaseConnection.BeginTransaction()
DatabaseCommand.Transaction = DatabaseTransaction
DatabaseCommand.CommandText = InsertQuery
MsgBox(DatabaseCommand.ExecuteNonQuery())
DatabaseTransaction.Commit()
Catch ex As Exception
MessageBox.Show(ex.Message, "ERROR", MessageBoxButtons.OK, MessageBoxIcon.Error)
Finally
If DatabaseConnection.State <> ConnectionState.Closed Then
DatabaseConnection.Close()
End If
End Try


thanks a lot for your help in advance

 

[cjard]

but nothing is added to the database.....

and how are you determining this result? by restarting your project (so the blank development version of the database is copied over the top of the one you just updated) and looking to see the value hasnt been inserted?

this happens a lot with file databases...

 

[sppaladugu]

Even with out restarting the project there is nothing updated to database...

if i run the select query after insert the values are showing but if i exit and restart or have look in the databse while the project is running no values inserted to database...

any work arounds........

thanks a lot for your help...

 

[cjard]

You didnt read my post then.. (Or, sorry if i didnt make it very clear)

You edit the db
Without quitting the app you run a select and it shows that the values have changed
You restart the app and the changes are gone

ITS BECAUSE VISUAL STUDIO COPIED A NEW BLANK VERSION OF YOUR DATABASE OVER THE TOP OF THE ONE YOU CHANGED. THIS BEHAVIOUR IS BY DESIGN

Click the database file in solution explorer and set the copy property to "Copy if newer"

That will solve the "problem"

 

[cjard]

or have look in the databse while the project is running no values inserted to database...

Youre looking in the wrong database.. Search your hard disk for all database files called "Sample.dbf" and you will realise what's going on