11/26/2024 2:24:38 AM
|
| |
| slxdeveloper.com Community Forums |
|
| |
|
|
The Forums on slxdeveloper.com are now retired. The forum archive will remain available for the time being. Thank you for your participation on slxdeveloper.com!
Forum to discuss ADO specific questions for use with SalesLogix. View the code of conduct for posting guidelines.
|
|
|
|
Open recordset in VB and it's readOnly
Posted: 21 Aug 06 8:02 AM
|
fiogf49gjkf0d Ok very simple question, I am trying to move some code from inside of SalesLogix to run outside of SalesLogix.
Taking data from another source populating tables in SLX.. simple task..
When I open the recordset now in my new VS2005.. it is telling me the recordset is "Read-Only", I am not doing anything differently than I have done before.. Cursor is to client, LockBatchOptimistic, Open Static...
any ideas how to make the rs not readonly..? |
|
|
| |
|
Re: Open recordset in VB and it's readOnly
Posted: 21 Aug 06 10:30 AM
|
fiogf49gjkf0d No I am on v6.2.3 na dwhen I created thes examples Ryan gave in his artical, I am getting a different RWpassword each time I run it. I don't think we have one setup
|
|
|
|
Re: Open recordset in VB and it's readOnly
Posted: 21 Aug 06 10:55 AM
|
fiogf49gjkf0d First of all, I would suggest *not* using a Recordset if you're developing in VS2005. Use the better (ease of use, performance, etc) data objects in .NET (OleDbCommand, OleDbDataReader, OleDbDataAdapter etc) instead of using interop around ADO.
However, as long as you're setting the appropriate lock-types *and* your query you're opening (and want to write to) doesn't have any joins in it then you should be OK.
Is there a section of code you could post for us to look at? |
|
|
|
Re: Open recordset in VB and it's readOnly
Posted: 21 Aug 06 11:05 AM
|
fiogf49gjkf0d Ryan, Here is the entire situation.. I am reading data from one database... dumping that in a table in SLX then running an update to the products table from there.. the entire process works inside of SLX .. I'm trying to move it out so it can run on an automated task rather than from inside the application.
Here is some code:
******************************
Try
Dim oConn As ADODB.Connection
Dim oConCln As ADODB.Connection
Dim oRs As ADODB.Recordset
Dim sCN As String
Dim sCNSLX As String
Dim sSQL As String
Dim sProdId As String
Dim oRsProgramUp As ADODB.Recordset
Dim oRsUpdate As ADODB.Recordset
Dim oRsPriceUp As ADODB.Recordset
Dim fld, col, sCnet As String
Dim x As Integer
Dim sTot As Integer
Dim sNew, sDel, sId As Integer
'Zero counts for e-mail
sTot = "0"
sNew = "0"
sDel = "0"
'Connection strings to Data warehouse -- Current is test db
sCN = "Provider = sqloledb; Data Source = Bastonia; Initial Catalog = CAI_APR06; User Id = sysdba; Password = masterkey;"
'Connection to SalesLogix
sCNSLX = "Provider=SLXOLEDB.1 assword=ersist Security Info=True;User ID=Admin;Initial Catalog=CAI_MAY;Data Source=Bastonia; Extended Properties= PORT=1706;LOG=ON;CASEINSENSITIVEFIND=ON;"
oConn = New ADODB.Connection
oConn.Open(sCN)
sSQL = "Select FundName From C_Funds"
'Truncate the table of old data
oConCln = New ADODB.Connection
oConCln.Open(sCNSLX)
oConCln.Execute("Delete From C_Funds")
oConCln.Close()
oRs = New ADODB.Recordset
oRs.CursorLocation = ADODB.CursorLocationEnum.adUseClient
oRs.Open(sSQL, oConn, ADODB.CursorTypeEnum.adOpenStatic, ADODB.LockTypeEnum.adLockBatchOptimistic, 1)
For x = 0 To oRs.RecordCount - 1
sSQL = "Select * From C_Funds Where 1 = 2"
oRsUpdate = New ADODB.Recordset
oRsUpdate.CursorLocation = ADODB.CursorLocationEnum.adUseClient
oRsUpdate.Open(sSQL, oConCln, ADODB.CursorTypeEnum.adOpenStatic, ADODB.LockTypeEnum.adLockBatchOptimistic, 1)
sTot = x
With oRsUpdate
If .RecordCount = 0 Then
.AddNew()
.Fields("C_FundsId") = x
.Fields("CreateDate") = Now()
.Fields("CreateUser") = "ADMIN"
.Fields("ModifyDate") = Now()
.Fields("FUNDID") = oRs.Fields("SEC_ID").Value
.Fields("FUNDNAME") = oRs.Fields("SEC_NAME").Value
.Fields("ASSETCLASS") = oRs.Fields("ASSET_CLASS_CODE").Value
.Fields("FUNDTYPE") = oRs.Fields("ASSET_CLASS_CODE").Value
.Fields("MININVESTMENT") = oRs.Fields("MIN_INV").Value
.Fields("CURRENCY") = oRs.Fields("CCY_CODE").Value
.Fields("STATUS") = oRs.Fields("SEC_STATUS").Value
.Fields("THIRDPARTYIND") = (oRs.Fields("THIRD_PARTY_IND").Value)
.Fields("LAUNCHDATE") = oRs.Fields("LAUNCH_DT").Value
.Fields("INVESTMENTMGR") = (oRs.Fields("SEC_OWNER").Value)
.Fields("OPSMGR") = (oRs.Fields("OPERATIONAL_MANAGER").Value)
.Fields("ADMINOWNER") = (oRs.Fields("SEC_ADMIN_OWNER").Value)
.Fields("IRRADMIN") = (oRs.Fields("SEC_IRR_OWNER").Value)
.UpdateBatch()
.Close()
oRs.MoveNext()
End If
End With
Next
x = 0
oRs.Close()
oConn.Close()
oConn = Nothing
***********************************************************
Thanks |
|
|
|
Re: Open recordset in VB and it's readOnly
Posted: 21 Aug 06 11:17 AM
|
fiogf49gjkf0d Originally posted by RJ Eaton
Ryan, Here is the entire situation.. I am reading data from one database... dumping that in a table in SLX then running an update to the products table from there.. the entire process works inside of SLX .. I'm trying to move it out so it can run on an automated task rather than from inside the application.
Here is some code:
******************************
Try
Dim oConn As ADODB.Connection
Dim oConCln As ADODB.Connection
Dim oRs As ADODB.Recordset
Dim sCN As String
Dim sCNSLX As String
Dim sSQL As String
Dim sProdId As String
Dim oRsProgramUp As ADODB.Recordset
Dim oRsUpdate As ADODB.Recordset
Dim oRsPriceUp As ADODB.Recordset
Dim fld, col, sCnet As String
Dim x As Integer
Dim sTot As Integer
Dim sNew, sDel, sId As Integer
'Zero counts for e-mail
sTot = "0"
sNew = "0"
sDel = "0"
'Connection strings to Data warehouse -- Current is test db
sCN = "Provider = sqloledb; Data Source = Bastonia; Initial Catalog = CAI_APR06; User Id = sysdba; Password = masterkey;"
'Connection to SalesLogix
sCNSLX = "Provider=SLXOLEDB.1assword=ersist Security Info=True;User ID=Admin;Initial Catalog=CAI_MAY;Data Source=Bastonia; Extended Properties= PORT=1706;LOG=ON;CASEINSENSITIVEFIND=ON;"
oConn = New ADODB.Connection
oConn.Open(sCN)
sSQL = "Select FundName From C_Funds"
'Truncate the table of old data
oConCln = New ADODB.Connection
oConCln.Open(sCNSLX)
oConCln.Execute("Delete From C_Funds")
oConCln.Close()
oRs = New ADODB.Recordset
oRs.CursorLocation = ADODB.CursorLocationEnum.adUseClient
oRs.Open(sSQL, oConn, ADODB.CursorTypeEnum.adOpenStatic, ADODB.LockTypeEnum.adLockBatchOptimistic, 1)
For x = 0 To oRs.RecordCount - 1
sSQL = "Select * From C_Funds Where 1 = 2"
oRsUpdate = New ADODB.Recordset
oRsUpdate.CursorLocation = ADODB.CursorLocationEnum.adUseClient
oRsUpdate.Open(sSQL, oConCln, ADODB.CursorTypeEnum.adOpenStatic, ADODB.LockTypeEnum.adLockBatchOptimistic, 1)
sTot = x
With oRsUpdate
If .RecordCount = 0 Then
.AddNew()
.Fields("C_FundsId") = x
.Fields("CreateDate") = Now()
.Fields("CreateUser") = "ADMIN"
.Fields("ModifyDate") = Now()
.Fields("FUNDID") = oRs.Fields("SEC_ID").Value
.Fields("FUNDNAME") = oRs.Fields("SEC_NAME").Value
.Fields("ASSETCLASS") = oRs.Fields("ASSET_CLASS_CODE").Value
.Fields("FUNDTYPE") = oRs.Fields("ASSET_CLASS_CODE").Value
.Fields("MININVESTMENT") = oRs.Fields("MIN_INV").Value
.Fields("CURRENCY") = oRs.Fields("CCY_CODE").Value
.Fields("STATUS") = oRs.Fields("SEC_STATUS").Value
.Fields("THIRDPARTYIND") = (oRs.Fields("THIRD_PARTY_IND").Value)
.Fields("LAUNCHDATE") = oRs.Fields("LAUNCH_DT").Value
.Fields("INVESTMENTMGR") = (oRs.Fields("SEC_OWNER").Value)
.Fields("OPSMGR") = (oRs.Fields("OPERATIONAL_MANAGER").Value)
.Fields("ADMINOWNER") = (oRs.Fields("SEC_ADMIN_OWNER").Value)
.Fields("IRRADMIN") = (oRs.Fields("SEC_IRR_OWNER").Value)
.UpdateBatch()
.Close()
oRs.MoveNext()
End If
End With
Next
x = 0
oRs.Close()
oConn.Close()
oConn = Nothing
***********************************************************
Thanks |
|
'Truncate the table of old data
oConCln = New ADODB.Connection
oConCln.Open(sCNSLX)
oConCln.Execute("Delete From C_Funds")
oConCln.Close()
The above is most likely your problem. You close oConCln but never re-open it before you call the following line..
oRsUpdate.Open(sSQL, oConCln, ADODB.CursorTypeEnum.adOpenStatic, ADODB.LockTypeEnum.adLockBatchOptimistic, 1)
You might also want to remove your .UpdateBatch and .Close outside of your loop to speed up inserts, and maybe use truncate instead of delete unless you need sql to log the delete statement. |
|
|
|
Re: Open recordset in VB and it's readOnly
Posted: 21 Aug 06 11:23 AM
|
fiogf49gjkf0d Doug,
Thanks for the help.. Sorry even removing the oConCln.close didn't change the recordset from being readonly.. I am channging the flow also so I will be moving the Update but thanks |
|
|
| |
|
You can
subscribe to receive a daily forum digest in your
user profile. View the site code
of conduct for posting guidelines.
 Forum RSS Feed - Subscribe to the forum RSS feed to keep on top of the latest forum activity!
|
|
|
|
|
|
|
|