Dear friends,

I am using Vb.net as front end and Sqldatabase as my database file. In my application actual data is not updaing in to the database. But it shows the data while running. Once if clsoe the eecution and if i check the table the data is not saved in the table. i am not able to find the error. Please help me ...


Thanking you,
Deepak

I am having the same problem with an Access database. I have attached my code of the button event if anyone could help I would appreciate it.

Private Sub btnSaveData_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSaveData.Click


Dim ds As New DataSet
Dim strConnectionString As String = "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source= C:\Documents and Settings\Robert\My Documents\Chapter 10\flower.mdb"
Dim objConnection = New OleDbConnection(strConnectionString)
Dim strSQL As String = "SELECT * FROM flower"
Dim Connection As New SqlConnection
Dim objCommand As New OleDb.OleDbCommand(strSQL, objConnection)
Dim Command As New SqlCommand("INSERT INTO flower(Number, Name, Meaning) VALUES (FlowerNumber, FlowerName, FlowerMeaning)", Connection)
Dim objDataAdapter As New OleDb.OleDbDataAdapter(objCommand)
Dim objDataTable As New Data.DataTable("flower")
Dim objDataRow As DataRow

Try
'Open the database connection
objConnection.Open()

'Fill the DataTable object
objDataAdapter.Fill(objDataTable)

'Load the list box on the form

Dim dsNewRow As DataRow
For Each dsNewRow In objDataTable.Rows
Console.WriteLine(dsNewRow.Item("FlowerNumber") & " " & _
dsNewRow.Item("FlowerName") & " " & dsNewRow.Item("FlowerMeaning") & " ")
Next
Catch OleDbExceptionErr As OleDb.OleDbException
'Write the exception
Console.WriteLine(OleDbExceptionErr.Message)
Catch InvalidOperationExceptionErr As InvalidOperationException
'Write the exception
Console.WriteLine(InvalidOperationExceptionErr.Message)
End Try

'Close the database connection
objConnection.Close()


'Clean up
objDataRow = Nothing
objDataTable.Dispose()
objDataTable = Nothing
objDataAdapter.Dispose()
objDataAdapter = Nothing
objCommand.Dispose()
objCommand = Nothing
objConnection.Dispose()
objConnection = Nothing


btnNewFlower.Enabled = True
btnSaveData.Enabled = False
btnDelete.Enabled = True

You must either add an Update Command to the Adapter-just as you have done for the SELECT and INSERT Command or use the Commandbuilder to do it. Then use the Adapters Update method (just like Fill) to update the dataset to the database. Besides this, you dont need to open and close the Connection when using an Adapter. The Adapter does that automatically when you call the Fill method and closes the Connection Automatically after the Update.

Thank you very much for the reply. But my problem is something different. in my code snippet sometimes i use only sql commands to insert, update and delete command. that time data is is not updating in my database. pls heil me....


thanking you ,
Deepak

If you are using only commands to Insert,Update, or Delete-you should use the objCommand.ExecuteNonQuery method to update the database.

You will also have to use Parameters to pass data to the Insert,update, delete commands.

Thank you for the useful information..
Here is one more problem when i am using update command with data adapter, i getting error msg:"Update requires a valid UpdateCommand when passed DataRow collection with modified Rows ". I have checked the code and database. i am not able to find the error. please help me out to find the error,

Thanking you,

I posted the solution a few hrs ago. You have to create a Command Object for Update-just like you have done for Select and Insert. Add the Update code (Update table -- set--where==etc) and Add Parameters for the Update. Specify values for the Parameters.

Then use the Adapter.Update()
to update the records
You will find all you need in this topic in MSDN - SqlDataAdapter Class

This article has been dead for over six months. Start a new discussion instead.