|
Subject:
|
updating Access 2002 using ADO.NET
|
|
Posted By:
|
howardz
|
Post Date:
|
12/12/2003 9:36:20 AM
|
Hi I'm just trying to get to grips with ASP .NET for the very first time. I Set up a simple test login screen (using vs.net) which puts away the user input to an Access 2002 mdb parked on my laptop. I lifted the routine
sub UpdateDB(....) but changed the namespace and references to embrace OLE DB processing. Everything goes fine whilst I'm only reading data from the mdb. As soon as I push a simple update (of the form (UPDATE [table name] SET [field] = [value] WHERE [primary key id] = [value]) onto the UpdateDB routine it dies every time on the
command.ExecuteNonQuery()
method with the arcane message 'operation must use updateable query'. Is this a known (or common) problem for beginners. I've checked the Access help for Updateable queries and as far as I can see a singleton update should have worked. The only time I've come across this problem in the past is if a taregt SQL Server table did not sport a primary key. I'll post the code details if the discussion needs to develop. I ran the SQL as a normal Access update query and it worked fine (of course). I would be grateful if someone can stop me from ripping my hair out as I've come to a grinding halt on this one.
The connection string is:
Provider=Microsoft.JET.OLEDB.4.0; Data Source=C:\entde2000\test.mdb
The update sub was
Protected Sub UpdateDB(ByVal sCmd As String)
Dim connection As OleDbConnection = New OleDbConnection(<connect string defined above>)
connection.Open()
Dim command As New OleDbCommand()
command.Connection = connection
command.CommandText = sCmd
command.ExecuteNonQuery()
connection.Close()
Return
End Sub
the access table has a primary key defined.
Thanks for any support on this one.
|
|
Reply By:
|
stu9820
|
Reply Date:
|
12/16/2003 1:31:00 PM
|
It should look similart to this:
Imports system.data.oledb
Sub updateIt()
'declare all variables
'oledb connection here
strSQLQuery = "UPDATE [Fall] SET [event_date]=@EventDate, [event]=@Event, [event_time]=@EventTime, [location]=@Location WHERE ([Fall].[id] = @ID)"
objCommand = New OleDbCommand(strSQLQuery, objConn)
' Add parameters that our SQL update command needs:
objCommand.Parameters.Add(New OleDbParameter("@EventDate", OleDbType.Date, 25))
objCommand.Parameters("@EventDate").Value = txtEventDate.Text
objCommand.Parameters.Add(New OleDbParameter("@Event", OleDbType.VarChar, 100))
objCommand.Parameters("@Event").Value = txtEvent.Text
objCommand.Parameters.Add(New OleDbParameter("@EventTime", OleDbType.VarChar, 49))
objCommand.Parameters("@EventTime").Value = txtEventTime.Text
objCommand.Parameters.Add(New OleDbParameter("@Location", OleDbType.VarChar, 49))
objCommand.Parameters("@Location").Value = txtLocation.Text
intID = dgDates.DataKeys(e.Item.ItemIndex)
objCommand.Parameters.Add(New OleDbParameter("@ID", OleDbType.Integer))
objCommand.Parameters("@ID").Value = intID
' Open the connection, execute the command, and close the connection.
objConn.Open()
objCommand.ExecuteNonQuery()
objConn.Close()
End Sub
Notice that it is the parameters are in the exact order as the strSQL and the id field is the last parameter. If that doesn't do it you need to give write permissions to the aspnet user account on the folder that the db is in.
|
