How do I editing a record in SQL Server 7 using ADO 2

[SouthwestNut]

I checked thru the past threads looking for this answer and couldn't find the answer to the question above. In VBA I would start my recordset with rs.Edit and when I had the fields changed I would use rs.Update. However, the Edit method is not recognized in ADO. Any suggestions would be appreciated.

 

[chiph]

I would use the SQL UPDATE command along with an ADO Command object to do that.

Chip H.

 

[Adam95]

rs.open "Select * from tblTable1",[valid connection],[option],[option]

rs.[field1] = Text1
rs.[field2] = Text2
rs.update
rs.close

simple as that

 

[rac2]

Hello Southwest,

The Microsoft Developer Network site has a programmers reference for ADODB

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/ado270/htm/mdobjodbrec.asp

To edit fields first set the cursor type and lock type for the recordset. There are several combinations that allow updating, the default values do not so you must specify values that do. Here is one possible set of values that allows updating.

Dim adOpenDynamic, adLockOptimistic
adOpenDynamic = 2
adLockOptimistic = 3

rs.CursorType = adOpenDynamic
rs.LockType = adLockOptimistic

Retrieve the records you might be updating.

rs.Open "SELECT * FROM employees", "DSN=human_resources"

Change the values

rs.Fields("annual_salary") = 100000

Then update

rs.Update

Hope this helps.

 

[SouthwestNut]

I appreciate everybody's help thus far, but I am still running into the same error (-2147217873, "Cannot insert duplicate key in object IncomeJournal)

I have put a portion of the code below. If anybody sees anything that looks like it might be the reason for the problem, please reply.

Set conn = New ADODB.Connection
conn.Open "Provider=sqloledb; Data Source=fine_nt_sql1; User ID =Data;Password =****;"

Set rsData = New ADODB.Recordset
With rsData
.CursorLocation = adUseServer
.CursorType = adOpenDyanmic
.LockType = adLockOptimistic
.Open "IncomeJournal", conn, , , adCmdTable
End With
dtmDate = CVDate(Worksheets("Revenue&quot.Cells(2, 3).Value)

rsData!smlHotelID = Worksheets("Configuration&quot.Cells(8, 7).Value
rsData!dtmHotelDate = dtmDate
rsData!smlYear = DatePart("yyyy", dtmDate) - 2000
rsData!smlMonth = DatePart("m", dtmDate)
rsData!smlDay = DatePart("d", dtmDate)
rsData!smlDayOfWeek = DatePart("w", dtmDate)
For intArrResultCnt = 0 To intArrLimit - 1 Step 1
strFieldName = Right(arrResultsRevenue(intArrResultCnt, 0), Len(arrResultsRevenue(intArrResultCnt, 0)) - 1)
rsData(strFieldName) = arrResultsRevenue(intArrResultCnt, 1)
Next intArrResultCnt
rsData.Update

 

[rac2]

Maybe updating an identity column, smlHotelID, is not allowed.

 

[Adam95]

Rac is exactly right, you are not allowed to update an identity field. Identity field is kind of a row identifier for the DB to use. NEVER allow the user to edit an identity field, and the programmer should NEVER update the identity field IMHO.

Adam