Just want to Delete a Record from Database

[Vetrijar]

I can't figure this out. I've been trying this for days and don't know what to do at all. I'm using

Visual Studio 2003, programming in Visual Basic.net side to make an ASP Application.

 

Also, I'm not using the straight coding that I find everywhere with databases. I'm using the feature

that the software says works which is dragging and dropping the database objects from the toolbox

to the webform. (I think it's called dao, but not sure)

 

The Update and Insert work fine, but the Delete sucks. what's odd is that it worked for one record,

but when I wanted it to delete any other record, it gives errors.

 

Here's the code used for Deleting

----------------------------------------------------------

Public Function DeleteAccount(ByVal Name As String) As Boolean

' Delete an account

If Name = "" Then Return False

Dim acct As Account ' This is an AccountSet from the AccountAdapter

acct = GetAccount(Name) ' This loads the current record from the database

If acct.account.Count > 0 Then

' account found

acct.account(0).Delete()

AccountSystem.Update(acct)

End If

Return True

End Function

----------------------------------------------------------

 

The above function worked for one record, but when I asked to do it again, it didn't work anymore.

 

Below is a sample Update function that works for updating but the procedure I used for it didn't even

work for the delete part.

----------------------------------------------------------

Private Function UpdateLastLogin(ByVal Username As String) As Boolean

Dim Data As String

 

Data = "UPDATE Account SET LastLogin = '" & Now & "' WHERE username = '" & Username & "'"

AccountSystem.InsertCommand.CommandText = Data

AccountSystem.InsertCommand.Connection.Open()

AccountSystem.InsertCommand.ExecuteNonQuery()

AccountSystem.InsertCommand.Connection.Close()

Return True

End Function

----------------------------------------------------------

 

For some reason, when I create the DataAdapter (drag and dropping it on the webform) I can do the

"Configure Data Adapter" to get the wizard. When done with it, the Update and Delete commands

come back with an error about not being created. Only the Insert and Select work. What's up with that??

 

anyways, here's the error message I get with the delete function

===========================

System.Web.Services.Protocols.SoapException: Server was unable to process request. ---> System.InvalidOperationException: ExecuteReader: CommandText property has not been initialized at System.Data.Common.DbDataAdapter.Update(DataRow[] dataRows, DataTableMapping tableMapping) at System.Data.Common.DbDataAdapter.Update(DataSet dataSet, String srcTable) at System.Data.Common.DbDataAdapter.Update(DataSet dataSet) at SecureXML.Service1.DeleteAccount(String Name, String Key) --- End of inner exception stack trace ---

===========================

 

Also, to explain the database connection I did,

I went to "Server Explorer" in vb.net, dragged a table to the webform (it made like an activeX control on the form)

and then access data through it like you would a textbox or label control. hope that helps.

Like I say, I don't know if this way I'm doing is called ADO, DAO, or what.. can't find any websites that show

what I'm doing. just the hard-code programming stuff.

 

Part of the initialization shows

Me.MDConn = New System.Data.Odbc.OdbcConnection

Me.AccountSystem = New System.Data.Odbc.OdbcDataAdapter

Me.OdbcInsertCommand1 = New System.Data.Odbc.OdbcCommand

Me.OdbcSelectCommand1 = New System.Data.Odbc.OdbcCommand

*that's just some of the long initialization*

 

Can anyone help me with this???? or if reluctant on advice, maybe just a website that shows the graphical

drag and drop creating of a database? I can't find ANYTHING.

 

Thx

Ryan

[bungpeng]

Did you try to delete a record base on SQL statement? like your "UpdateLastLogin" function? or try to run this delete record SQL statement in your database (Query Analyzer for SQL Server), make sure it run fine before jump to programming part.

[Vetrijar]

I tried an SQL statement with the delete also and it still had

the same problem.

 

What's odd to me is that with the SQL statement, it DID work

one time. for one delete. after that. it didn't work anymore. I don't think the server is blocking me from deleting in my database.

 

Do you have a sample format that I could try using that works with this?

 

Any other suggestions are welcomed too !! :-) thanks

[Robby]

As bungpeng mentioned use a Delete statement and an ExecuteNonQuery.

 

"Delete Account Where SomeColumnID = " & nIDVar

[Vetrijar]

It worked. wierd though because I tried that before.

here's the code that finally worked for me

 

Data = "DELETE FROM Account WHERE USERNAME='" & Name & "'"

AccountSystem.DeleteCommand.CommandText = Data

AccountSystem.DeleteCommand.Connection.Open()

AccountSystem.DeleteCommand.ExecuteNonQuery()

AccountSystem.DeleteCommand.Connection.Close()

 

 

I had the DELETE FROM to be DELETE * FROM like some

website told me to do. :-(

[Robby]

At least you got it to work. :)