can not delete record using delete query in VB!!!

[longmatch]

I am trying to delete some records based upon criteria. But it did not work for some reason. But the select clause works well. Don't know why.

Please me give a clue.

Thanks

haijun

Dim strSqlDelete As String
Dim admissionID As String

admissionID = txtAdmissionID.Text
strSqlDelete = "delete * from tblPtDiagnosis where admitID=0"

Adodc13.CommandType = adCmdText
Adodc13.RecordSource = strSqlDelete
Adodc13.Refresh

 

[Longia]

Try this if it works.

Dim cmd As ADODB.Command
strSqlDelete = "Delete from tblPtDiagnosis where admitID='0'"
Set cmd = New ADODB.Command
cmd.ActiveConnection = conn
cmd.CommandText = strSqlDelete
cmd.CommandType = 1
cmd.Prepared = True
cmd.Execute

I don't know how you connect to database but I do it this way.
for character field use ='0' in query and for integer field use just =0

 

[longmatch]

Dear SarPreet:
how to define conn? and what does "cmd.prepared = true" mean?

Thanks

 

[bjd4jc]

You are using a data controller to connect to your database. To the best of my knowledge I don't think you can do update, delete or insert statements using the data controller.

To define conn, you will need to have a reference to ADO 2.x in project References. I is probably already there since you are using the ADODC but check just to make sure.

The do this

Dim conn As ADODB.Connection

set conn = New ADODB.Connection

Conn.Provider="Microsoft.Jet.OLEDB.4.0"
Conn.Open "Your MDB Path here"

This is what it is for access,,, you will need to look up the provider in the help if you are using another database.

then you can do what sarpreet posted.

 

[longmatch]

Dear all:
I found the error in my code. When I deleted the * from my query clause, everything works fine. This is the first time I used the DELETE command. I thought it is the same as SELECT. But I understand right now that when a record is deleted, every field will be gone, so no "*" need in this case. Thanks to everybody who give me help.

Haijun