delete a record and update the dataset

Hi

There must be something wrong with the command that you are using to update the database.

Why don't you try creating a commandbuilder, it will create the commands automaticallly for you.

something like this:

If reponse_del = DialogResult.Yes Then
 OleDAPass.SelectCommand.Parameters ("UserName").Value = txtSearch.Text
 'delete row
 DS_Pass1.Tables("PwordStore").Rows(0).Delete()
 Dim cmdbuilder As OleDbCommandBuilder = New oleDbCommandBuilder(OleDAPass)
 OleDAPass.update(DS_Pass1)

End If

hope it helps

Hi

There must be something wrong with the command that you are using to update the database.

Why don't you try creating a commandbuilder, it will create the commands automaticallly for you.

something like this:

If reponse_del = DialogResult.Yes Then
 OleDAPass.SelectCommand.Parameters ("UserName").Value = txtSearch.Text
 'delete row
 DS_Pass1.Tables("PwordStore").Rows(0).Delete()
 Dim cmdbuilder As OleDbCommandBuilder = New oleDbCommandBuilder(OleDAPass)
 OleDAPass.update(DS_Pass1)

End If

hope it helps

Thanks for your advice William. I tried adding the commandbuilder as you suggested but it made no difference whatsoever. I have only one simple table in access and i used the wizard to create the Sql commands. the delete command i am using is DELETE FROM PwordStore WHERE (UserName = ? ). Anymore advice please?
Thanks in advance

Hi

Try to enclose the database update in a try - catch statement and see exacly what the problem is.

Also try adding to the update statement the name of the table.

Try
    OleDAPass.update(DS_Pass1, "PwordStore")
Catch x As Exception
    ' Error during Update, add code to locate error, reconcile 
    ' and try to update again.
End Try

other than that, I can not think of anything else, everything seems to be fine.

hi William
I tried your 'try catch' suggestion but it doesn't throw up any exceptions. The problem is the code runs and seems to be fine, it just doesn't update the database.
Thanks for your help, I guess I'm going to have to keep at it - unless anyone else can suggest something.

Hi again - I'm still stuck
i was wondering if my update statement could be wrong. It was generated by the access wizard :

UPDATE PwordStore SET UserName = ?, Pword = ? WHERE (PwordNo = ?) AND (Pword = ? OR ? IS NULL AND Pword IS NULL) AND (UserName = ? OR ? IS NULL AND UserName IS NULL)

It is also used to update the database when a new row is added and works fine for that. Does it need changing for the delete statement?
Please help me my deadline is getting very near.
Thanks

Hi

The delete command and the update commad have to be different.
The delete command will be:

DELETE FROM  Table_name WHERE ( ((? = 1 AND Rep IS NULL) OR (Rep = ?)) AND (ID = ?) AND ((? = 1 AND SN IS NULL) OR (SN = ?)) AND ((? = 1 AND Veh IS NULL) OR (Veh = ?)) AND ((? = 1 AND Hm IS NULL) OR (Hm = ?)) AND ((? = 1 AND Dt IS NULL) OR (Dt = ?)) )

This is what my command builder gives me to delete records from my table. My table has the fields Rep, ID, SN, etc.

Another thing. When you use the command builder, did you declare the variables for the oleDBdataadapter, oleDBconnection and oleDbcommand outside the procedure?

regards

how do I add data into a dataset because currently my software just stores it on the ram until the computer is shutdown

DS_Pass1.Tables("PwordStore").Rows(0).Delete()
 Dim cmdbuilder As OleDbCommandBuilder = New oleDbCommandBuilder(OleDAPass)
 OleDAPass.update(DS_Pass1)

OliverQ, here's the SQL syntax to insert record:

Dim objcommand As SqlCommand = New SqlCommand

            With objcommand
                .Connection = objConnection
                .CommandText = "INSERT INTO TableName" & _
               "(Columns separated with commas)" + _
                "VALUES (enter values separated with commas)"
              
            End With

            objConnection.Open()
            objcommand.ExecuteNonQuery()
            objConnection.Close()