Operation must use an updateable query.

Hi everyone!
I'm trying to write a web application in school that utilizes a MS
Access database. I can read and everything fine from it but when I try
to add a new record I get an exception that states: "Operation must use
an updateable query" I know it has to do with the update command on the
OleDbDataAdapter. I've tried looking for the problem on the net and
newsgroups but I don't seem to have the same problems as everyone else.
Please, if anyone could help me or point me to a better solution I'd
greatly appreciate it!
Derek

<Begin Code
Public Shared Function addFile() As String

'Select Fields that will be affected by the add command
Dim query As String = "SELECT Filename, Modified, path," _
& "Department, Extension, " _
& " Project, Type, Software FROM Files"

'Get a dataset based upon the query statement defined above
Dim dsNewFile As DataSet = SealundDB.BindQuery(query, "Files")

'Create a datarow that will be added to the Table Files
Dim drNewRow As DataRow = dsNewFile.Tables("Files").NewRow

'Set the field values of the datarow to the information stored
'in the local data members

drNewRow("Filename") = fileName
drNewRow("Modified") = creationDate
drNewRow("Path") = filePath
drNewRow("Department") = department
drNewRow("Extension") = extension
drNewRow("Project") = "Unknown"
drNewRow("Software") = "Unknown"
drNewRow("Type") = fileType

Try
dsNewFile.Tables("Files").Rows.Add(drNewRow)
Return SealundDB.UpdateDatabase(dsNewFile, query)
Catch ex As Exception
Return ("1. " & ex.Message)
End Try
End Function

/************************************************** *******************/

Public Shared Function UpdateDatabase(ByVal newFile As DataSet, _
ByVal queryString As String) As String

Dim sourceTable As String = "Files"
Dim connection As OleDbConnection = New _
OleDbConnection(database)
Dim selectCommand As OleDbCommand = _
New OleDbCommand(queryString, connection)

Dim daQuery As OleDbDataAdapter = New OleDbDataAdapter
Dim returnEx As String

daQuery.SelectCommand = selectCommand
daQuery.MissingSchemaAction = MissingSchemaAction.AddWithKey
Dim cbQuery As OleDbCommandBuilder = New _
OleDbCommandBuilder(daQuery)

Try
daQuery.Update(newFile, sourceTable)
Catch ex As Exception
returnEx = "2. " & ex.Message
End Try

Return returnEx
End Function

/************************************************** *************************/

Public Shared Function BindQuery(ByVal queryString As String, _
ByVal queryName As String) As DataSet

Dim connection As OleDbConnection = New _
OleDbConnection(database)

Dim selectCommand As OleDbCommand = _
New OleDbCommand(queryString, connection)

Dim queryDA As OleDbDataAdapter = New OleDbDataAdapter
queryDA.SelectCommand = selectCommand

Dim queryDS As DataSet = New DataSet
queryDA.Fill(queryDS, queryName)

Return queryDS
End FunctionI've run into this problem before. It had to do with write permissions not
being enabled on the directory that my .mdb file was in. Make sure the
ASPNET account or whatever the identity is has write permissions on that
directory.

--Jason

"Derek Van Cuyk" <dvancuyk@.tampabay.rr.com> wrote in message
news:O9E0e.207352$qB6.203595@.tornado.tampabay.rr.c om...
> Hi everyone!
> I'm trying to write a web application in school that utilizes a MS
> Access database. I can read and everything fine from it but when I try
> to add a new record I get an exception that states: "Operation must use
> an updateable query" I know it has to do with the update command on the
> OleDbDataAdapter. I've tried looking for the problem on the net and
> newsgroups but I don't seem to have the same problems as everyone else.
> Please, if anyone could help me or point me to a better solution I'd
> greatly appreciate it!
> Derek
> <Begin Code>
> Public Shared Function addFile() As String
> 'Select Fields that will be affected by the add command
> Dim query As String = "SELECT Filename, Modified, path," _
> & "Department, Extension, " _
> & " Project, Type, Software FROM Files"
> 'Get a dataset based upon the query statement defined above
> Dim dsNewFile As DataSet = SealundDB.BindQuery(query, "Files")
> 'Create a datarow that will be added to the Table Files
> Dim drNewRow As DataRow = dsNewFile.Tables("Files").NewRow
> 'Set the field values of the datarow to the information stored
> 'in the local data members
> drNewRow("Filename") = fileName
> drNewRow("Modified") = creationDate
> drNewRow("Path") = filePath
> drNewRow("Department") = department
> drNewRow("Extension") = extension
> drNewRow("Project") = "Unknown"
> drNewRow("Software") = "Unknown"
> drNewRow("Type") = fileType
> Try
> dsNewFile.Tables("Files").Rows.Add(drNewRow)
> Return SealundDB.UpdateDatabase(dsNewFile, query)
> Catch ex As Exception
> Return ("1. " & ex.Message)
> End Try
> End Function
> /************************************************** *******************/
> Public Shared Function UpdateDatabase(ByVal newFile As DataSet, _
> ByVal queryString As String) As String
> Dim sourceTable As String = "Files"
> Dim connection As OleDbConnection = New _
> OleDbConnection(database)
> Dim selectCommand As OleDbCommand = _
> New OleDbCommand(queryString, connection)
> Dim daQuery As OleDbDataAdapter = New OleDbDataAdapter
> Dim returnEx As String
> daQuery.SelectCommand = selectCommand
> daQuery.MissingSchemaAction = MissingSchemaAction.AddWithKey
> Dim cbQuery As OleDbCommandBuilder = New _
> OleDbCommandBuilder(daQuery)
> Try
> daQuery.Update(newFile, sourceTable)
> Catch ex As Exception
> returnEx = "2. " & ex.Message
> End Try
> Return returnEx
> End Function
>
/************************************************** *************************
/
> Public Shared Function BindQuery(ByVal queryString As String, _
> ByVal queryName As String) As DataSet
> Dim connection As OleDbConnection = New _
> OleDbConnection(database)
> Dim selectCommand As OleDbCommand = _
> New OleDbCommand(queryString, connection)
> Dim queryDA As OleDbDataAdapter = New OleDbDataAdapter
> queryDA.SelectCommand = selectCommand
> Dim queryDS As DataSet = New DataSet
> queryDA.Fill(queryDS, queryName)
> Return queryDS
> End Function
I've checked again and all he properties are set to share and write.
Doesn't seem to be that. I'm looking into another way of implementing it
using an actual INSERT command rather than using the commandbuilder to
supply one from my SELECT statement.
Derek

Jason Mauss <jason.mauss@. wrote:
> I've run into this problem before. It had to do with write permissions not
> being enabled on the directory that my .mdb file was in. Make sure the
> ASPNET account or whatever the identity is has write permissions on that
> directory.
> --Jason
> "Derek Van Cuyk" <dvancuyk@.tampabay.rr.com> wrote in message
> news:O9E0e.207352$qB6.203595@.tornado.tampabay.rr.c om...
>>Hi everyone!
>>I'm trying to write a web application in school that utilizes a MS
>>Access database. I can read and everything fine from it but when I try
>>to add a new record I get an exception that states: "Operation must use
>>an updateable query" I know it has to do with the update command on the
>>OleDbDataAdapter. I've tried looking for the problem on the net and
>>newsgroups but I don't seem to have the same problems as everyone else.
>>Please, if anyone could help me or point me to a better solution I'd
>>greatly appreciate it!
>>Derek
>>
>><Begin Code>
>>
>> Public Shared Function addFile() As String
>>
>> 'Select Fields that will be affected by the add command
>> Dim query As String = "SELECT Filename, Modified, path," _
>> & "Department, Extension, " _
>> & " Project, Type, Software FROM Files"
>>
>> 'Get a dataset based upon the query statement defined above
>> Dim dsNewFile As DataSet = SealundDB.BindQuery(query, "Files")
>>
>> 'Create a datarow that will be added to the Table Files
>> Dim drNewRow As DataRow = dsNewFile.Tables("Files").NewRow
>>
>> 'Set the field values of the datarow to the information stored
>> 'in the local data members
>>
>> drNewRow("Filename") = fileName
>> drNewRow("Modified") = creationDate
>> drNewRow("Path") = filePath
>> drNewRow("Department") = department
>> drNewRow("Extension") = extension
>> drNewRow("Project") = "Unknown"
>> drNewRow("Software") = "Unknown"
>> drNewRow("Type") = fileType
>>
>> Try
>> dsNewFile.Tables("Files").Rows.Add(drNewRow)
>> Return SealundDB.UpdateDatabase(dsNewFile, query)
>> Catch ex As Exception
>> Return ("1. " & ex.Message)
>> End Try
>> End Function
>>
>>/************************************************** *******************/
>>
>> Public Shared Function UpdateDatabase(ByVal newFile As DataSet, _
>>ByVal queryString As String) As String
>>
>> Dim sourceTable As String = "Files"
>> Dim connection As OleDbConnection = New _
>> OleDbConnection(database)
>> Dim selectCommand As OleDbCommand = _
>> New OleDbCommand(queryString, connection)
>>
>> Dim daQuery As OleDbDataAdapter = New OleDbDataAdapter
>> Dim returnEx As String
>>
>> daQuery.SelectCommand = selectCommand
>> daQuery.MissingSchemaAction = MissingSchemaAction.AddWithKey
>> Dim cbQuery As OleDbCommandBuilder = New _
>> OleDbCommandBuilder(daQuery)
>>
>> Try
>> daQuery.Update(newFile, sourceTable)
>> Catch ex As Exception
>> returnEx = "2. " & ex.Message
>> End Try
>>
>> Return returnEx
>> End Function
>>
>>
> /************************************************** *************************
> /
>>Public Shared Function BindQuery(ByVal queryString As String, _
>> ByVal queryName As String) As DataSet
>>
>> Dim connection As OleDbConnection = New _
>> OleDbConnection(database)
>>
>> Dim selectCommand As OleDbCommand = _
>> New OleDbCommand(queryString, connection)
>>
>> Dim queryDA As OleDbDataAdapter = New OleDbDataAdapter
>> queryDA.SelectCommand = selectCommand
>>
>> Dim queryDS As DataSet = New DataSet
>> queryDA.Fill(queryDS, queryName)
>>
>> Return queryDS
>> End Function
>
Derek,
Have you tried giving Full Permission instead of just share and write? I
would google the error message too. I seem to recall that being how I found
out about the answer.

Jason

"Derek Van Cuyk" <dvancuyk@.tampabay.rr.com> wrote in message
news:azN0e.1604$Pc.1319@.tornado.tampabay.rr.com...
> I've checked again and all he properties are set to share and write.
> Doesn't seem to be that. I'm looking into another way of implementing it
> using an actual INSERT command rather than using the commandbuilder to
> supply one from my SELECT statement.
> Derek
> Jason Mauss <jason.mauss@. wrote:
> > I've run into this problem before. It had to do with write permissions
not
> > being enabled on the directory that my .mdb file was in. Make sure the
> > ASPNET account or whatever the identity is has write permissions on that
> > directory.
> > --Jason
> > "Derek Van Cuyk" <dvancuyk@.tampabay.rr.com> wrote in message
> > news:O9E0e.207352$qB6.203595@.tornado.tampabay.rr.c om...
> >>Hi everyone!
> >>I'm trying to write a web application in school that utilizes a MS
> >>Access database. I can read and everything fine from it but when I try
> >>to add a new record I get an exception that states: "Operation must use
> >>an updateable query" I know it has to do with the update command on the
> >>OleDbDataAdapter. I've tried looking for the problem on the net and
> >>newsgroups but I don't seem to have the same problems as everyone else.
> >>Please, if anyone could help me or point me to a better solution I'd
> >>greatly appreciate it!
> >>Derek
> >>
> >><Begin Code>
> >>
> >> Public Shared Function addFile() As String
> >>
> >> 'Select Fields that will be affected by the add command
> >> Dim query As String = "SELECT Filename, Modified, path," _
> >> & "Department, Extension, " _
> >> & " Project, Type, Software FROM Files"
> >>
> >> 'Get a dataset based upon the query statement defined above
> >> Dim dsNewFile As DataSet = SealundDB.BindQuery(query, "Files")
> >>
> >> 'Create a datarow that will be added to the Table Files
> >> Dim drNewRow As DataRow = dsNewFile.Tables("Files").NewRow
> >>
> >> 'Set the field values of the datarow to the information stored
> >> 'in the local data members
> >>
> >> drNewRow("Filename") = fileName
> >> drNewRow("Modified") = creationDate
> >> drNewRow("Path") = filePath
> >> drNewRow("Department") = department
> >> drNewRow("Extension") = extension
> >> drNewRow("Project") = "Unknown"
> >> drNewRow("Software") = "Unknown"
> >> drNewRow("Type") = fileType
> >>
> >> Try
> >> dsNewFile.Tables("Files").Rows.Add(drNewRow)
> >> Return SealundDB.UpdateDatabase(dsNewFile, query)
> >> Catch ex As Exception
> >> Return ("1. " & ex.Message)
> >> End Try
> >> End Function
> >>
> >>/************************************************** *******************/
> >>
> >> Public Shared Function UpdateDatabase(ByVal newFile As DataSet, _
> >>ByVal queryString As String) As String
> >>
> >> Dim sourceTable As String = "Files"
> >> Dim connection As OleDbConnection = New _
> >> OleDbConnection(database)
> >> Dim selectCommand As OleDbCommand = _
> >> New OleDbCommand(queryString, connection)
> >>
> >> Dim daQuery As OleDbDataAdapter = New OleDbDataAdapter
> >> Dim returnEx As String
> >>
> >> daQuery.SelectCommand = selectCommand
> >> daQuery.MissingSchemaAction = MissingSchemaAction.AddWithKey
> >> Dim cbQuery As OleDbCommandBuilder = New _
> >> OleDbCommandBuilder(daQuery)
> >>
> >> Try
> >> daQuery.Update(newFile, sourceTable)
> >> Catch ex As Exception
> >> returnEx = "2. " & ex.Message
> >> End Try
> >>
> >> Return returnEx
> >> End Function
> >>
> >>
/************************************************** *************************
> > /
> >>Public Shared Function BindQuery(ByVal queryString As String, _
> >> ByVal queryName As String) As DataSet
> >>
> >> Dim connection As OleDbConnection = New _
> >> OleDbConnection(database)
> >>
> >> Dim selectCommand As OleDbCommand = _
> >> New OleDbCommand(queryString, connection)
> >>
> >> Dim queryDA As OleDbDataAdapter = New OleDbDataAdapter
> >> queryDA.SelectCommand = selectCommand
> >>
> >> Dim queryDS As DataSet = New DataSet
> >> queryDA.Fill(queryDS, queryName)
> >>
> >> Return queryDS
> >> End Function
Are the permissions Read/Write/Execute?

See if this thread at ASP.NEt Forums covers your case
http://www.asp.net/Forums/ShowPost...1&PostID=154273

--
Teemu Keiski
ASP.NET MVP, AspInsider
Finland, EU

"Derek Van Cuyk" <dvancuyk@.tampabay.rr.com> wrote in message
news:azN0e.1604$Pc.1319@.tornado.tampabay.rr.com...
> I've checked again and all he properties are set to share and write.
> Doesn't seem to be that. I'm looking into another way of implementing it
> using an actual INSERT command rather than using the commandbuilder to
> supply one from my SELECT statement.
> Derek
> Jason Mauss <jason.mauss@. wrote:
>> I've run into this problem before. It had to do with write permissions
>> not
>> being enabled on the directory that my .mdb file was in. Make sure the
>> ASPNET account or whatever the identity is has write permissions on that
>> directory.
>>
>> --Jason
>>
>> "Derek Van Cuyk" <dvancuyk@.tampabay.rr.com> wrote in message
>> news:O9E0e.207352$qB6.203595@.tornado.tampabay.rr.c om...
>>
>>>Hi everyone!
>>>I'm trying to write a web application in school that utilizes a MS
>>>Access database. I can read and everything fine from it but when I try
>>>to add a new record I get an exception that states: "Operation must use
>>>an updateable query" I know it has to do with the update command on the
>>>OleDbDataAdapter. I've tried looking for the problem on the net and
>>>newsgroups but I don't seem to have the same problems as everyone else.
>>>Please, if anyone could help me or point me to a better solution I'd
>>>greatly appreciate it!
>>>Derek
>>>
>>><Begin Code>
>>>
>>> Public Shared Function addFile() As String
>>>
>>> 'Select Fields that will be affected by the add command
>>> Dim query As String = "SELECT Filename, Modified, path," _
>>> & "Department, Extension, " _
>>> & " Project, Type, Software FROM Files"
>>>
>>> 'Get a dataset based upon the query statement defined above
>>> Dim dsNewFile As DataSet = SealundDB.BindQuery(query, "Files")
>>>
>>> 'Create a datarow that will be added to the Table Files
>>> Dim drNewRow As DataRow = dsNewFile.Tables("Files").NewRow
>>>
>>> 'Set the field values of the datarow to the information stored
>>> 'in the local data members
>>>
>>> drNewRow("Filename") = fileName
>>> drNewRow("Modified") = creationDate
>>> drNewRow("Path") = filePath
>>> drNewRow("Department") = department
>>> drNewRow("Extension") = extension
>>> drNewRow("Project") = "Unknown"
>>> drNewRow("Software") = "Unknown"
>>> drNewRow("Type") = fileType
>>>
>>> Try
>>> dsNewFile.Tables("Files").Rows.Add(drNewRow)
>>> Return SealundDB.UpdateDatabase(dsNewFile, query)
>>> Catch ex As Exception
>>> Return ("1. " & ex.Message)
>>> End Try
>>> End Function
>>>
>>>/************************************************** *******************/
>>>
>>> Public Shared Function UpdateDatabase(ByVal newFile As DataSet, _
>>>ByVal queryString As String) As String
>>>
>>> Dim sourceTable As String = "Files"
>>> Dim connection As OleDbConnection = New _
>>> OleDbConnection(database)
>>> Dim selectCommand As OleDbCommand = _
>>> New OleDbCommand(queryString, connection)
>>>
>>> Dim daQuery As OleDbDataAdapter = New OleDbDataAdapter
>>> Dim returnEx As String
>>>
>>> daQuery.SelectCommand = selectCommand
>>> daQuery.MissingSchemaAction = MissingSchemaAction.AddWithKey
>>> Dim cbQuery As OleDbCommandBuilder = New _
>>> OleDbCommandBuilder(daQuery)
>>>
>>> Try
>>> daQuery.Update(newFile, sourceTable)
>>> Catch ex As Exception
>>> returnEx = "2. " & ex.Message
>>> End Try
>>>
>>> Return returnEx
>>> End Function
>>>
>>>
>>
>> /************************************************** *************************
>> /
>>
>>>Public Shared Function BindQuery(ByVal queryString As String, _
>>> ByVal queryName As String) As DataSet
>>>
>>> Dim connection As OleDbConnection = New _
>>> OleDbConnection(database)
>>>
>>> Dim selectCommand As OleDbCommand = _
>>> New OleDbCommand(queryString, connection)
>>>
>>> Dim queryDA As OleDbDataAdapter = New OleDbDataAdapter
>>> queryDA.SelectCommand = selectCommand
>>>
>>> Dim queryDS As DataSet = New DataSet
>>> queryDA.Fill(queryDS, queryName)
>>>
>>> Return queryDS
>>> End Function
>>
>>
>