Problem with insert statement...

  • Thread starter Thread starter Paul Mason
  • Start date Start date
P

Paul Mason

Hi folks,

The following function generates the error "Operation must use an updateable
query". There is no identifiable or meaningful error code.

This system is using OLEDB to connect to an Access database. I've tried
applying the insert statement directly to the Access database and it works
fine.

I'm not sure whether this is because the connection isn't allowing updates
or whether there's something odd about the insert statement.

Cheers...P


Public Function AddMatchReport(ByVal lngTeamMatchID As Long, ByVal
lngMemberID As Long, ByVal strReport As String) As Boolean

Dim cm As OleDb.OleDbCommand

Dim adp As OleDb.OleDbDataAdapter

Dim ds As Data.DataSet

Dim rw As DataRow

Dim strRet As String

If Me.Connection.State = ConnectionState.Closed Then

Me.Connection.Open()

End If

cm = New OleDb.OleDbCommand

cm.Connection = Me.Connection

cm.CommandText = "INSERT INTO tbTeamMatchReport (TeamMatchID, MemberID,
Report) " & _

"VALUES (" & lngTeamMatchID & ", " & lngMemberID & ", " & _

Chr(34) & strReport & Chr(34) & ")"

'cm.CommandType = CommandType.Text

If cm.ExecuteNonQuery() = -1 Then

Return True

Else

Return False

End If



End Function
 
The first thing I'd check is that you have a primary key on the table. If
not, that's the likely culprit. The next thing is that you have read/Write
access to the db - if you can't modify the file, then this is a common
error.

As an aside, I'd highly suggest paramaterizing the query b/c you may have a
problem with the input (although with this particular error, I doubt that's
it). Paramaterizing the query is a defintely "should do" in this regard
irrespective of if its causing this particular problem b/c leaving it as is
may introduce a new problem. Just juse ...Value(?, ?, ?)

cmd.Paramaters.Add(lngTeamMatchId)
cmd.Parameters.Add(lngMemberID)
cmd.Parameters.Add(strReport)

between these, you should be good to go, let me know if not.

Cheers,

Bill
 
¤ Hi folks,
¤
¤ The following function generates the error "Operation must use an updateable
¤ query". There is no identifiable or meaningful error code.
¤

Are you attempting to perform the update from an ASP.NET app?

Whenever I see this message it usually indicates there is a user permissions issue for the folder
where the database is located.


Paul
~~~~
Microsoft MVP (Visual Basic)
 
Hi,

The table has a primary key and under normal circumstances I can edit the
file happily. The primary key is not listed in the insert statement
however....It is a autonumber field as there can be many Reports for each
member for each match. The two key fields (TeamMatchID and MemberID) are
foreign keys.

I am currently running this as an ASP.NET app off my local IIS server i.e.
my PC.

Whenever I use this app and I get an error (regardless of whether I trap it
and handle it or not) the Access database becomes read-only and I have to
reboot the machine to unlock it (annoying to say the least). All
connections are closed even if an error occurs (I trap it and handle it is
in the finally clause of a try statement).

I'm not sure if the above i.e. the status of the connection, is causing the
problem. The connection object is very simply put together, just setting a
connection string. The connection string is
"Provider=Microsoft.Jet.OLEDB.4.0;Data
Source=C:\Inetpub\wwwroot\SportSite\SSData.mdb;"

I have modified the function applying parameters. It wouldn't let me just
put the object values in, but required the full declaration. Unfortunately
I still get the same error about using an updateable query. Please note
this function is a method of a class clsTeam (see calling procedure below
this function).

Cheers...P

Public Function AddMatchReport(ByVal lngTeamMatchID As Long, ByVal
lngMemberID As Long, ByVal strReport As String) As Boolean

Dim cm As OleDb.OleDbCommand

Dim adp As OleDb.OleDbDataAdapter

Dim ds As Data.DataSet

Dim rw As DataRow

Dim strRet As String

If Me.Connection.State = ConnectionState.Closed Then

Me.Connection.Open()

End If

cm = New OleDb.OleDbCommand

cm.Connection = Me.Connection

'cm.CommandText = "INSERT INTO tbTeamMatchReport (TeamMatchID, MemberID,
Report) " & _

' "VALUES (" & lngTeamMatchID & ", " & lngMemberID & ", " & _

' Chr(34) & strReport & Chr(34) & ")"

cm.CommandText = "INSERT INTO tbTeamMatchReport (TeamMatchID, MemberID,
Report) VALUES (?,?,?)"

cm.CommandType = CommandType.Text

cm.Parameters.Add("TeamMatchID", OleDb.OleDbType.BigInt).Value =
lngTeamMatchID

cm.Parameters.Add("MemberID", OleDb.OleDbType.BigInt).Value = lngMemberID

cm.Parameters.Add("Report", OleDb.OleDbType.VarChar, 2000).Value = strReport



If cm.ExecuteNonQuery() = -1 Then

Return True

Else

Return False

End If

End Function



The calling routine is :



Private Sub cmdSave_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles cmdSave.Click

Dim objTeam As SportSiteClass.clsTeam

Dim lngMemberID As Long

Try

If Me.txtReport.Text.Length > 0 Then

objTeam = New SportSiteClass.clsTeam

lngMemberID = Session.Item("MemberID")

objTeam.AddMatchReport(Me.lngTeamMatchID, lngMemberID, Me.txtReport.Text)

End If

Catch ex As Exception

Me.lblError.Text = ex.Message

Finally

objTeam.Connection.Close()

End Try



'Me.Response.Redirect("MatchReport.aspx?ID=" & Me.lngTeamMatchID & "&Team="
& Me.lngTeamID)



End Sub
 
Hi,

Please ignore that last posting.

It turned out to be the fact that the ASPNET user didn't have write
permission on the Access database.....DOH! Thanks for the info on the
parameters anyway.

Cheers...P
 
that's ok you have resolved it :)
on the other hand, the problem with locking mdb file. did you try iisreset
commandline utility instead of rebooting? iis probably locks mdb file and
its reset should help

peter
 
Back
Top