Please help with SQL problem

[jfarrell]

Can someone help me with this code. The sql code has bugs in it I have tried to figure out for a week
without success. When I delete out a record the navigation buttons do not work right.
And when I insert the record the record doesn't show up until I exit out and then come
back into the program. With an initial insert it gives the message 'The option requested is not allowed in this context.
I apologize for the code looking like crap but I am new to ADO so please forgive.
John
Option Explicit
Dim cnAP As Connection
Dim rsSchool As Recordset
Dim sbAddNew As Boolean
Dim cmdSchool As Command

Private Sub Form_Load()
Set cnAP = New Connection
Set rsSchool = New Recordset
Set cmdSchool = New Command
cnAP.Open "Provider=Microsoft.Jet.OLEDB.3.51;Persist Security Info=False;........
rsSchool.Open "SELECT * FROM SCHOOL ORDER BY KEY", _
cnAP, adOpenKeyset, adLockOptimistic, adCmdText
SetNavigationButtons True
End Sub

Private Sub cmdInsert_Click()
On Error GoTo errorhandler
Dim dr As Long
Dim cnn As ADODB.Connection
Dim SQL As String
Dim cmdSchool As Command
Set cnn = New Connection
Set cmdSchool = New Command
cnAP.CursorLocation = adUseClient
cnAP.BeginTrans
cnn.Open "Provider=Microsoft.Jet.OLEDB.3.51;Persist Security Info=False;Data Source=C:\project\Student.mdb"
cmdSchool.ActiveConnection = cnAP
cmdSchool.CommandType = adCmdText
SQL = "INSERT INTO School(Key,Schoolid,Name,Principal,Telephone) " & _
"VALUES('26','100','Private School','Jones','N/A')"
cmdSchool.CommandText = SQL
cmdSchool.Execute
cnAP.CommitTrans

Private Sub cmdDelete_Click()
Dim dr As Long
Dim cnn As ADODB.Connection
Dim constr As String
Dim SQL As String
Dim errloop As ADODB.Error
constr = "Provider=Microsoft.Jet.OLEDB.3.51;Persist Security Info=False;Data Source=C:\project\Student.mdb"
Set cnn = New ADODB.Connection
cnn.Open constr
cnn.BeginTrans
If cnn.Errors.Count > 0 Then
MsgBox "An error has occured"
For Each errloop In cnn.Errors
MsgBox "Error #: " & errloop.Number & vbCr & _
errloop.Description
Next
cnn.RollbackTrans
Exit Sub
End If
dr = InputBox("Enter the Schoolid you want to delete", "Delete School&quot
SQL = "DELETE FROM SCHOOL WHERE SCHOOL!KEY = " & dr & ""
cnn.Execute SQL
cnn.CommitTrans
End Sub

 

[olichap]

John,

The SQL query tested fine when I put brackets around Key (I also put them around Name but don't think it too necessary).

INSERT INTO School([Key],Schoolid,[Name],Principal,Telephone)
VALUES('26','100','Private School','Jones','N/A')

Whether this runs or not I don't know, but try it.

Oliver

 

[jfarrell]

Thanks for the suggestion Oliver I gave it a try but
still come up with the error
'The operation requested by the application is not
allowed in this context.'
When I look for the record after this message its not
there. If I Exit out and come back in the record is
there along with a blank record.
I hope I can find an answer as this is a pain.
John

 

[olichap]

Also, in your string concantenations you need to make sure the format is valid. In your insert statement you are treating Key as a string variable (based on VALUES('26',). Variable "dr" is declared as a number so are you expecting to delete a string or numeric value in the statement below?

SQL = "DELETE FROM SCHOOL WHERE SCHOOL!KEY = " & dr & ""

If "dr is supposed to be a string then your concatenation format must be = '" & dr & "'".

Also, your delete statement does not parse. Try this:
"Delete from School where [Key] = " & dr & ""
or
"Delete from School where School.[Key] = " & dr & ""

The "!" is not valid. I forgot to mention it before, but just like in VB certain words are reserved in SQL. Key, Name, UniqueIdentifier, and Procedure are just a few. Normally SQL Server (I'm not sure about Access) will recognize that you are not using the word as a reserved word, but as a field name. When I parsed your query earlier it seemed to be a bit more strict about the Key word.

O.

 

[olichap]

When the error occurs, where in the code does it happen? Is it the Insert or Delete?

The best what to find this out is to REM out the OnError Goto statement by putting a ' in front of it. Then when it bombs you will know exactly where it occurs.

 

[jfarrell]

The Delete seems to work ok except the navigation buttons
have trouble navigating after a delete.
The insert statement does not appear to insert a record at first but after exiting out of program and reentry the record is there along with a blank record.
I also tried this code:
Private Sub cmdInsert_Click()
Dim strSQL As String
Dim cnAP As Connection
Dim cmdAP As Command
Set cnAP = New Connection
Set cmdAP = New Command
cnAP.CommitTrans
cnAP.CursorLocation = adUseClient
cnAP.Open "Provider=Microsoft.Jet.OLEDB.3.51;Persist Security Info=False;Data Source=C:\project\Student.mdb"
cmdAP.ActiveConnection = cnAP
cmdAP.CommandType = adCmdText
strSQL = "INSERT INTO School(Key,Schoolid,Name,Principal,Telephone) " & _
"VALUES('26','100','Private School','Parent','N/A')"
cmdAP.CommandText = strSQL
cmdAP.Execute
cnAP.CommitTrans
Exit Sub
End Sub
With this I get the error 'Run time error 3704'
The operation requested by the application is not allowed
if the object is closed'.
cnAP.CommitTrans is highlighted
This is working with an access table. Any help will be greatly appreciated.
John

 

[olichap]

OK, I think I see part of the problem now.

I believe your problem with the INSERT (in cmdInsert_Click) is the use of .BeginTrans and .CommitTrans.

If all you really need to do is insert a record into the table, that task can be accomplished like this:

Private Sub cmdInsert_Click()
Dim strSQL As String
Dim cnAP As new adodb.Connection

cnAP.CursorLocation = adUseClient
cnAP.Open "Provider=Microsoft.Jet.OLEDB.3.51;Persist Security Info=False;Data Source=C:\project\Student.mdb"

strSQL = "INSERT INTO School(Key,Schoolid,Name,Principal,Telephone) " & _
"VALUES('26','100','Private School','Parent','N/A')"
cnAP.Excecute strSQL

If you really want to use BeginTrans and CommitTrans, the format is more like this:

Dim rs As New ADODB.Recordset
Dim cn As New ADODB.Connection
cn.CursorLocation = adUseServer 'or clientside if you wish
cn.ConnectionString = "your connection data here"
cn.Open

cn.BeginTrans
With rs
.Open "tempTable", cn, adOpenDynamic, adLockBatchOptimistic
.AddNew
!key= 26 'see note below
!SchoolID = "100" 'again, make sure of your data types-is this supposed to be string or #
!Name = "Private School"
!Principal = "Parent"
!Telephone = "N/A"
.UpdateBatch
End With
cn.CommitTrans

You don't want to do an .Execute command with .CommitTrans.

Hope this helps,
Oliver

 

[jfarrell]

Oliver, many thanks for your help. It seems to be working
with your help.
May You Live Long and Prosper
John
PS. I cannot thank you enough!