Access Database Update Problem

[bernie321]

Hi

I am using the below code to write to an access database but when I run it the page just seems to hang. If i cancel the page i cannot access the database properly until windows XP gives me a message some time after saying there has been a delay in the process and it cannot recover the data.

The database is physically located in the

http://wwwroot

folder:

Dim objItem As DataGridItem
Dim strID As String
Dim strErrors As String
Dim strSQL As String
Dim cn As New ADODB.Connection
cn.ConnectionString "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=\\localhost\lettersdb\letters.mdb;"

strErrors = ""
For Each objItem In myDataGrid.Items

' Ignore invalid items
If objItem.ItemType <> ListItemType.Header And objItem.ItemType <> ListItemType.Footer And objItem.ItemType <> ListItemType.Pager Then

' Retrieve the value of the check box
Dim blnPS As Boolean
blnPS = CType(objItem.Cells(0).FindControl("chkPS"), CheckBox).Checked
strID = objItem.Cells(1).Text

If blnPS = True Then
strSQL = "UPDATE tblC SET Response = 'PS' WHERE C_ID = '" & Trim(strID) & "'"
cn.Open()
cn.Execute(strSQL)
cn.Close()
End If
End If

Next

cn = Nothing

Thanks B

 

[jzelhart]

Try using ExecuteNonQuery instead of the Excute. You are not returning a dataset. Also, is the ID a text field? if not take out the single quotes around that item.

Hope everyone is having a great day!

Thanks - Jennifer

 

[bernie321]

Hi Jennifer

Executenonquery does not seem to be a command for ADODB.connection.

Thanks B

 

[jzelhart]

Here is what I use.
Imports System.Data
Imports System.Data.SqlClient

Public Class SQLBean
Dim ds As New DataSet
Dim strConn As String = "server=ServerName;uid=Login;pwd=Password;Initial Catalog=Database"
Dim conn As SqlConnection
...




Public Function updateExpenseDetail(ByVal strID As String, ByVal strDate As String) As Integer
Dim intUpdate As Integer = 0

Try
Dim strSQL As String = "UPDATE dbo.Detail_T Set dbo.Detail_T.Date_DT = @strDate WHERE dbo.Detail_T.Detail_ID = @strID"

conn = New SqlConnection(strConn)
Dim cmd As SqlCommand = New SqlCommand(strSQL, conn)
cmd.Parameters.Add(New SqlParameter("@strID", SqlDbType.Int))
cmd.Parameters("@strID").Value = strID
cmd.Parameters.Add(New SqlParameter("@strDate", SqlDbType.DateTime))
cmd.Parameters("@strDate").Value = strDate

conn.Open()
intUpdate = cmd.ExecuteNonQuery()
Catch ex As Exception
intUpdate = 0
Finally
If Not conn Is Nothing Then
conn.Close()
End If
End Try

Return intUpdate
End Function
...
End Class

Hope this helps.

Hope everyone is having a great day!

Thanks - Jennifer

 

[jzelhart]

That is for SQL but should be similar for Access.

Hope everyone is having a great day!

Thanks - Jennifer

 

[yu217171]

Hi Bernie,

If you are performing database interactions using Access, you should do so with the System.Data.OleDb namespace and its objects.

Keith

 

[bernie321]

Hi Guys

I now have this code but still have the same problem, it hangs.

Thanks B

Dim objItem As DataGridItem
Dim strID As String
Dim strErrors As String
Dim strSQL As String
Dim cn As New OleDb.OleDbConnection
Dim conn As OleDb.OleDbCommand
cn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=\\localhost\lettersdb\letters.mdb;"

For Each objItem In myDataGrid.Items

' Ignore invalid items
If objItem.ItemType <> ListItemType.Header And objItem.ItemType <> ListItemType.Footer And objItem.ItemType <> ListItemType.Pager Then
' Retrieve the value of the check box
Dim blnPS As Boolean
blnPFS = CType(objItem.Cells(0).FindControl("chkPS"), CheckBox).Checked
strID = objItem.Cells(1).Text

If blnPFS = True Then
strSQL = "UPDATE tblC SET Response = 'PS' WHERE C_ID = '" & Trim(strID) & "'"
conn = New OleDbCommand(strSQL, cn)
cn.Open()
conn.ExecuteNonQuery()
cn.Close()
conn = Nothing

End If
End If
Next

 

[bernie321]

Hi Guys

I now have this code but still have the same problem, it hangs.

Thanks B

Dim objItem As DataGridItem
Dim strID As String
Dim strErrors As String
Dim strSQL As String
Dim cn As New OleDb.OleDbConnection
Dim conn As OleDb.OleDbCommand
cn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=\\localhost\lettersdb\letters.mdb;"

For Each objItem In myDataGrid.Items

' Ignore invalid items
If objItem.ItemType <> ListItemType.Header And objItem.ItemType <> ListItemType.Footer And objItem.ItemType <> ListItemType.Pager Then
' Retrieve the value of the check box
Dim blnPS As Boolean
blnPFS = CType(objItem.Cells(0).FindControl("chkPS"), CheckBox).Checked
strID = objItem.Cells(1).Text

If blnPS = True Then
strSQL = "UPDATE tblC SET Response = 'PS' WHERE C_ID = '" & Trim(strID) & "'"
conn = New OleDbCommand(strSQL, cn)
cn.Open()
conn.ExecuteNonQuery()
cn.Close()
conn = Nothing

End If
End If
Next

 

[bernie321]

Hi

After a little more investigation I have realised that it updates the database but freezes on cn.close()

The access locking file is left even after the webpage & visual studio are closed.

Thanks B

 

[bernie321]

I have found out why - its because the database is on a network share.

Now I have coded it locally.

The only problem is that i get this error: Operation must use an updateable query.

When it attempts the update. I have given the approprate write privilages to the IIS user.

Any ideas?

Thanks B

 

[jzelhart]

Quick question - is there a reason that you are opening the connection and closing it inside of the For statement? Will it work if you place it outside?

Just to confirm on your last post, the IIS Anonymous User has rights to the directory that the Access Database is in - correct?

Hope everyone is having a great day!

Thanks - Jennifer

 

[bernie321]

Hi Jennifer

Just before you posted I changed the IIS user to full control permissions, instead of just write permissions.

THis solved the problem - i think it could not delete the locking file when closing the connection.

Thanks for all the help

B