D
Derek Van Cuyk
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'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