January 9, 2008 at 1:44 pm
Hi,
I have a visual basic 6.0 application running on Windows XP which accesses sql 2000 database. We tried to migrate our sql server 2000 database to sql server 2005 last week and encountered errors when trying to add a new record via VB app. The app works fine with SQL 2000 for last 7 years. Now as we are migrating to 2005 server we have problems.
Here is my function to open a connection.
Public Function OpenDatabaseConnection1()
On Error GoTo ErrHandler
Dim ADOERR As ADODB.Error
Set objConn1 = New ADODB.Connection
If objConn1.State = adStateClosed Then
objConn1.Open DSNConnection
End If
Set objRs1 = New ADODB.Recordset
objRs1.CursorType = adOpenDynamic
objRs1.LockType = adLockOptimistic
objRs1.Open "CheckMaster", objConn1, , , adCmdTable
If objRs1.State = adStateClosed Then
MsgBox "The SQL Server is not responding!."
ServerStatus = False
blnConnection = False
Else
blnConnection = True
End If
Set ADOERR = Nothing
Exit Function
ErrHandler:
If objConn1.Errors.Count > 0 Then
Set ADOERR = objConn1.Errors(0)
MsgBox ("The sql server not responding:" & ADOERR.Description & ADOERR.Source)
Else
MsgBox ("An error occurred:" & Err.Description & Err.Source)
End If
End Function
Here is my function to add new record:
Function AddRecord
If objConn1.State = adStateOpen Then
If objRs1.State = adStateOpen Then
objRs1.AddNew
objRs1!Company = Company
objRs1!CheckDate = recDate
objRs1!ABANumber = ABANumber
objRs1!CheckNumber = CheckNo
objRs1!CheckingAcctNumber = CheckingAccount
objRs1!CheckAmount = Amount
objRs1!PayType = PayType
objRs1!paydate = WireDate
objRs1.Update
tempCheckId = objRs1!CheckId
Else
' 'else display errors
MsgBox "Connection to SQL recordset is not open.Please Close this batch and Try again."
blnConnection = False
End If
Else
blnConnection = False
MsgBox "Connection to SQL database is not open. Please Close this batch and Try Again."
End If
End Function
The first error I was getting on line where it says objRS.Addnew, is this:
"-2147467259 Server "ServerName" is not configured for DATA ACCESS."
I looked up on google and found that there is a flag that we can turn on in SQL 2005 :
sp_serverOption 'Servername', 'Data Access', 'True';
After I did that I did not get that error but I started getting error on line where it tries to update 'OBjRS.Update'. Here is the error:
"-2147217885: The cursor does not include the table being modified or the table is not updatable through the cursor"
I looked up on the google and found on one of the websites that SQL 2005 does not support OBJRS.Update method.
I am not sure if that's true. BUt I did test it by replacing it with Insert command and that worked.
My question is first, to verify if that's true and I have to use Insert instead of Update.
Second How can I get the latest inserted ID if I use the Insert command.
Thanks for your help.
Muhammad.
January 9, 2008 at 8:32 pm
I've been developing VB apps since around '98, and we have never really used ADOs built in record creating/editing capabilities. If you can, a better route is to create an insert stored procedure, and return the SCOPE_IDENTITY() through an output parameter. It's a little more code this way (create connection, create command, assign parameters to a command, set output type of the output parameter) but it is quite well documented. We ported a classic ASP application using ADO through VBScript from SQL 2000 to SQL 2005 using the above approach, and we had no issues.
January 9, 2008 at 10:26 pm
There are changes in DBEngine features from SQL2K to SQL2K5. You can try to change the cusrsor type as per the table given in below link.
http://msdn2.microsoft.com/en-us/library/ms143359.aspx
Using a correct cursor should solve your update problem.
Happy Debuging 🙂
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply