Adding/Editing and Deleting records from SQL DB

[VRIT]

Dear all,

Im sure this is so simple and I am just missing the point. I have created a form that contains a list of items in a listbox. I have been able to write the code to add new items to the listbox and update the list box - Simple.

However Im having trouble deleting the item from the SQL database. I am able to find the correct record in the list box and then I dont seem to be able to remove it from the SQL db. I have:

Do While Not CatagoryRS.EOF
If CatagoryRS![Catagory] = LstCatagories.Text Then
CatagoryRS.Delete
CatagoryRS.MoveLast
Else
CatagoryRS.MoveNext
End If
Loop
CatagoryRS.Update
CatagoryRS.Close

Can someone please point of the stupid mistake.

Many thanks

 

[nigelrivett]

Try sending a delete statement instead

sql = "delete categories where category = '" & LstCatagories.Text & "'"

You can do the insert in the same way rather than keeping a bound recordset.


======================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

 

[VRIT]

Ah that sounds better, please excuse my lack of ability but could you give me an example. The last time I did any coding was with VB3 and Access!!

I take it I need to give the SQL statement back to the database.....

Thanks for your help and patience

VRIT

 

[nigelrivett]

I prefer to implement a data access layer in the client - then it's easy to change and put in tracing/logging.

see

http://www.nigelrivett.net/VB6DataAccessLayer.html

Thats for sql server and restricted to SP calls only but you can amend it to pass a string if you wish.

======================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

 

[VRIT]

Thank you.

 

[VRIT]

Hello again,

I still seem to be having a problem, can someone review the code below (dont laugh) and let me know what is wrong and why it does not update the SQL db, thanks:

Dim cnConn As ADODB.Connection
Dim CatagoryRS As ADODB.Recordset
Dim AlreadyExists As Boolean

Set cnConn = New ADODB.Connection
Set CatagoryRS = New ADODB.Recordset

With cnConn
.Provider = "SQLOLEDB.1"
.Properties("Data Source&quot.Value = "BIC03"
.Properties("User ID&quot.Value = "sa"
.Properties("Password&quot.Value = ""
.Properties("Initial Catalog&quot.Value = "VR_Support"
.CursorLocation = adUseClient
.ConnectionTimeout = 0
.Open
End With

If TxtCatagory.Text <> &quot;&quot; Then
CatagorySQL = &quot;SELECT * FROM Catagory&quot;
CatagoryRS.Open CatagorySQL, cnConn, adOpenStatic, adLockPessimistic
CatagoryRS.MoveFirst
AlreadyExists = False
Do While Not CatagoryRS.EOF
If LCase(Trim(TxtCatagory)) = LCase(Trim(CatagoryRS![Catagory])) Then
AlreadyExists = True
CatagoryRS.MoveLast
Else
CatagoryRS.MoveNext
End If
Loop
If AlreadyExists = True Then
MsgBox &quot;The catagory entered already exists&quot;
ElseIf AlreadyExists = False Then
CatagoryRS.AddNew
CatagoryAdd = &quot;INSERT Catagory WHERE Catagory = '&quot; & TxtCatagory.Text & &quot;'&quot;
End If
Else
MsgBox &quot;Blank&quot;
End If
'CatagoryRS.Close
cnConn.Close