Recordset.update with SQL Native Client doesn't work

We recently migrated from SQL Server 7 to SQL Server 2005. Now there's a curious thing with some legacy applications. I have pasted some code below. Don't judge me, because like I said, it's legacy.

You can see, that I have two connection strings. One is commented and accessing via SQL Native Client. The other one is doing this through the old SQL Server driver. Funnily enough, when I use the new Native Client driver, the exception "Run-time error '-2147467259 (80004005)' [Microsoft][SQL Native Client]Invalid attribute value" is thrown in the rsPorder2.Update line. With the old driver, this works just alright.

Is this a bug? Is there a way, to make the code run, because we don't want to search the whole application for other occurances, if not necessary.

Any insights would be greatly appreciated.

Best regards,
DD

Dim ilinx As New ADODB.Connection
Dim rsPorder As New ADODB.Recordset
Dim rsPorder2 As New ADODB.Recordset
Dim cmdLinx As New ADODB.Command
Dim strConn As String

Dim lvIli_number As String

'strConn = "DRIVER=SQL Native Client;Server=10.0.14.7;Description=Test2k;UID=sa;PWD=asdf;APP=Microsoft? Access;WSID=DEHHC023;DATABASE=Linx;Network=DBMSSOCN;"

strConn = "DRIVER=SQL Server;Server=10.0.14.7;Description=Test2k;UID=sa;PWD=asdf;APP=Microsoft? Access;WSID=DEHHC023;DATABASE=Linx;Network=DBMSSOCN;"
ilinx.Open strConn
Set cmdLinx.ActiveConnection = ilinx

lvIli_number = "12345"
cmdLinx.CommandText = "SELECT * FROM porder WHERE previous_ili_no = '" & lvIli_number & "' "
cmdLinx.CommandType = 1

rsPorder.Open cmdLinx, , 1, 1

If rsPorder.EOF Then

cmdLinx.CommandText = "SELECT * FROM porder WHERE ili_no = '" & lvIli_number & "' "
cmdLinx.CommandType = 1
rsPorder2.Open cmdLinx, , 1, 3

If rsPorder2.RecordCount > 0 Then

rsPorder2("Locked") = 0
rsPorder2.Update
End If

End If
rsPorder.Close

Hi,

did you trace the SQL Server with the profiler to see which command arrives at the server ? This would be very interesting to see if come commands arrive at the SQL Server level or not. The connection string you provided work for me with a .NET application an a ODBCConnection, so there has to be another problem with your connection.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

|||Hi,

I just traced the server, but to be honest, I don't know, what to make out of it.
The last two steps are, which seem to represent the rsPorder2("Locked") = 0 bit.
declare @.p3 int
set @.p3=1
declare @.p4 int
set @.p4=1
exec sp_cursorfetch 180150003,256,@.p3 output,@.p4 output
select @.p3, @.p4

and

exec sp_cursor 180150003,32,1

I probably haven't been clear on this, but I can access the database with the Native Client just fine. Just when it comes to this particular update method, it's where I receive the exception described.

Best regards,
DD|||

Hi,

you should trace the information that is executed on the server while doing the Update method. Seems that the trace you posted here is something like a cursor move on the server. Otherwise I would open the post again for fellows which might already got in that problem.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

|||Just change the default CursorLocation into ilinx.CursorLocation = adUseClient and it will run correctly.|||

Thanks, I was having the very same problem and changing the Cursor Location to adUseClient works

Recordset.update with SQL Native Client doesn't work

We recently migrated from SQL Server 7 to SQL Server 2005. Now there's a curious thing with some legacy applications. I have pasted some code below. Don't judge me, because like I said, it's legacy.

You can see, that I have two connection strings. One is commented and accessing via SQL Native Client. The other one is doing this through the old SQL Server driver. Funnily enough, when I use the new Native Client driver, the exception "Run-time error '-2147467259 (80004005)' [Microsoft][SQL Native Client]Invalid attribute value" is thrown in the rsPorder2.Update line. With the old driver, this works just alright.

Is this a bug? Is there a way, to make the code run, because we don't want to search the whole application for other occurances, if not necessary.

Any insights would be greatly appreciated.

Best regards,
DD

Dim ilinx As New ADODB.Connection
Dim rsPorder As New ADODB.Recordset
Dim rsPorder2 As New ADODB.Recordset
Dim cmdLinx As New ADODB.Command
Dim strConn As String

Dim lvIli_number As String

'strConn = "DRIVER=SQL Native Client;Server=10.0.14.7;Description=Test2k;UID=sa;PWD=asdf;APP=Microsoft? Access;WSID=DEHHC023;DATABASE=Linx;Network=DBMSSOCN;"

strConn = "DRIVER=SQL Server;Server=10.0.14.7;Description=Test2k;UID=sa;PWD=asdf;APP=Microsoft? Access;WSID=DEHHC023;DATABASE=Linx;Network=DBMSSOCN;"
ilinx.Open strConn
Set cmdLinx.ActiveConnection = ilinx

lvIli_number = "12345"
cmdLinx.CommandText = "SELECT * FROM porder WHERE previous_ili_no = '" & lvIli_number & "' "
cmdLinx.CommandType = 1

rsPorder.Open cmdLinx, , 1, 1

If rsPorder.EOF Then

cmdLinx.CommandText = "SELECT * FROM porder WHERE ili_no = '" & lvIli_number & "' "
cmdLinx.CommandType = 1
rsPorder2.Open cmdLinx, , 1, 3

If rsPorder2.RecordCount > 0 Then

rsPorder2("Locked") = 0
rsPorder2.Update
End If

End If
rsPorder.Close

Hi,

did you trace the SQL Server with the profiler to see which command arrives at the server ? This would be very interesting to see if come commands arrive at the SQL Server level or not. The connection string you provided work for me with a .NET application an a ODBCConnection, so there has to be another problem with your connection.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

|||Hi,

I just traced the server, but to be honest, I don't know, what to make out of it.
The last two steps are, which seem to represent the rsPorder2("Locked") = 0 bit.
declare @.p3 int
set @.p3=1
declare @.p4 int
set @.p4=1
exec sp_cursorfetch 180150003,256,@.p3 output,@.p4 output
select @.p3, @.p4

and

exec sp_cursor 180150003,32,1

I probably haven't been clear on this, but I can access the database with the Native Client just fine. Just when it comes to this particular update method, it's where I receive the exception described.

Best regards,
DD|||

Hi,

you should trace the information that is executed on the server while doing the Update method. Seems that the trace you posted here is something like a cursor move on the server. Otherwise I would open the post again for fellows which might already got in that problem.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

|||Just change the default CursorLocation into ilinx.CursorLocation = adUseClient and it will run correctly.|||

Thanks, I was having the very same problem and changing the Cursor Location to adUseClient works

Recordset Problem

I have a Recordset as below which works

SELECT DISTINCT MemberID, RegStatus, ProfileDate, Title, PostTown, PostCode
FROM [Site Photos]
ORDER BY ProfileDate DESC

However as soon as I add a further field called "images" it all goes wrong

MemberID may have 3 images attached

How can I do the recordset so that only the first image record for the
members is displayed (as in the top records set)

so I only get 1 record displayed per memberID regardless of how many images
they have

Does that make sense ?

AndrewHi

Something like the following should work.

SELECT S.MemberID, S.RegStatus, S.ProfileDate, S.Title, S.PostTown,
S.PostCode, S.Images
FROM [Site Photos] S
JOIN ( SELECT MemberID, MIN(ProfileDate) AS ProfileDate
FROM [Site Photos] GROUP BY MemberID ) A ON A.MemberID = S.MemberID AND
A.ProfileDate = S.ProfileDate
ORDER BY S.ProfileDate DESC

OR

SELECT S.MemberID, S.RegStatus, S.ProfileDate, S.Title, S.PostTown,
S.PostCode, S.Images
FROM [Site Photos] S
WHERE S.ProfileDate IN ( SELECT MIN(ProfileDate)
FROM [Site Photos] A
WHERE A.MemberID = S.MemberID)
ORDER BY S.ProfileDate DESC

John

"Andrew" <andrew@.nospam.com> wrote in message
news:H_bdb.56$9m.6@.newsfep1-gui.server.ntli.net...
> I have a Recordset as below which works
> SELECT DISTINCT MemberID, RegStatus, ProfileDate, Title, PostTown,
PostCode
> FROM [Site Photos]
> ORDER BY ProfileDate DESC
> However as soon as I add a further field called "images" it all goes wrong
> MemberID may have 3 images attached
> How can I do the recordset so that only the first image record for the
> members is displayed (as in the top records set)
> so I only get 1 record displayed per memberID regardless of how many
images
> they have
> Does that make sense ?
> Andrew

Records comparsion

Please look at the code template below for my problem. I am comparing two tables. Table 99 is the current state of the table. I get in a fresh extract of Table 99 everyday from my source - DB2 and I insert that in Table 00. Then I execute the code template below so that Table 99 stays current with the source DB2 on a daily basis.
--
CREATE TABLE myTable99 (
Col1 char(1)
, Col2 char(1)
, Col3 char(1)
, Col4 char(1)
, Col5 char(1)
, CONSTRAINT myTable99_pk PRIMARY KEY (Col1, Col2))

CREATE TABLE myTable00 (
Col1 char(1)
, Col2 char(1)
, Col3 char(1)
, Col4 char(1)
, Col5 char(1)
, CONSTRAINT myTable00_pk PRIMARY KEY (Col1, Col2))
GO

INSERT INTO myTable99(Col1,Col2,Col3,Col4,Col5)
SELECT '1','1','a','b','c' UNION ALL
SELECT '1','2','d','e','f' UNION ALL
SELECT '1','3','g','h','i' UNION ALL
SELECT '1','4','j','k','l'
--DELETED

INSERT INTO myTable00(Col1,Col2,Col3,Col4,Col5)
SELECT '1','1','a','b','c' UNION ALL --NO CHANGE
SELECT '1','2','x','y','z' UNION ALL -- UPDATE
SELECT '1','3','g','h','i' UNION ALL --NO CHANGE
SELECT '2','3','j','b','c' --INSERT
GO

SELECT * FROM myTable99
SELECT * FROM myTable00
GO

--DO DELETES FIRST

DELETE FROM a
FROM myTable99 a
LEFT JOIN myTable00 b
ON a.Col1 = b.Col1
AND a.Col2 = b.Col2
WHERE b.Col1 IS NULL AND b.Col2 IS NULL

-- INSERT

INSERT INTO myTable99(Col1,Col2,Col3,Col4,Col5)
SELECT a.Col1, a.Col2, a.Col3, a.Col4, a.Col5
FROM myTable00 a
LEFT JOIN myTable99 b
ON a.Col1 = b.Col1
AND a.Col2 = b.Col2
WHERE b.Col1 IS NULL AND b.Col2 IS NULL

-- UPDATE

UPDATE a
SET Col3 = b.Col3
, Col4 = b.Col4
, Col5 = b.Col5
FROM myTable99 a
INNER JOIN myTable00 b
ON a.Col1 = b.Col1
AND a.Col2 = b.Col2
AND ( a.Col3 <> b.Col3
OR a.Col4 <> b.Col4
OR a.Col5 <> b.Col5)
GO

SELECT * FROM myTable99
SELECT * FROM myTable00
GO

DROP TABLE myTable00
DROP TABLE myTable99
GO

--
I want to process new and updated records.
Let me explain the problem -

The last row for myTable99 is - 1','4','j','k','l'

The last row for myTable00 is - '2','3','j','b','c'

Col1 & Col2 are the PK's. So the last row from Table 99 gets deleted and the one from Table00 gets inserted as a 'New' record.Now I want to do processing only on those records that are new. So I should process record '2','3','j','b','c' .

My processing depends on the Col3 (attribute). My problem is I do not want to process records if the attribute has been processed before. In this case Col3 has 'j' and it has been processed before in 1','4','j','k','l'. It's just that this record no longer exist. So when I get in '2','3','j','b','c' , I want to say that this record has been processed.

How do I do that with the Insert new records query? The same case is for updates as well.

Any help is appreciated.

Thank you.My processing depends on the Col3 (attribute). My problem is I do not want to process records if the attribute has been processed before. In this case Col3 has 'j' and it has been processed before in 1','4','j','k','l'. It's just that this record no longer exist. So when I get in '2','3','j','b','c' , I want to say that this record has been processed.

You lost me....

Huh?

Understand....Whatever is in Col3 attribute BELONGS to the composite key Col1+Col2...it does not transcend the row to some other key?

It belongs to that row...if it so happens that the same value is on another row that's fine...but it belongs to that other key...

Can you give the explanation another shot?|||Why so complicated?

Can you not flag the affected records after the import and use the EXISTS / NOT EXISTS clause to target those which have been actioned as you translate them from import source to stored data?|||I think he's saying, that if I messed with an "attribute" (bad choice of words here) of a value of 5 in column 3, then I don't want to touch ANY of the rows...across keys...right?|||Here is the processing deal. I get the value of col 3 and check to see if it exists in this other table Z.

So now when I get a new record and if I have already checked that col3 value in Table Z, then I don't really need to check it again?|||Clear as mud...what does that have to do with adding and updating rows?

And what are you checking col3 for?|||what does that have to do with adding and updating rows?
Well every row that is added/updated has the status of a new record. And all new records have to be processed.

And what are you checking col3 for?
I am checking if the col3 value of the new record exist in this other table called Table Z. If it exist then I spit out the contents of Table 99 and Table Z into another table say X. Now my client looks to see the degree of similarity in col3 and the column in Table Z. For instance - col3 value is 'j' and the column in Table Z is 'jo'

Now for a new record which has the same value of Col3 as an old/delted record that was spit out then I have many duplicate values of Col3 (but different primary key) in X. Now the client has to check the same value of Col3 again for degree of similarity with Table Z.

I know it's a bit crazy but that's how the data is. Let me know what you think.|||I;m thinking I'm glad I'm not you...

But a previous post by someone a good idea...

When you add or update a record, tag it with an indicator NEW_IND for example, the do you're comparison step where the ind = 'Y' or whatever...then when you're donme set them all bacl to N

What'chyou think?|||Well if I tag it with IND = 'Y' and do the comparison then isn't it possible that Col3 of the record with IND = 'Y' would have been compared before?|||You cannot delete the old records AND retain knowledge of the old record's values. You must retain a knowledge of what the old the old values were.

You probably need an intermediate table indicating which of the possible values you have already processed, or implement some ki9nd of flagging system such that you identify the records to be deleted, insert the new records, compare the col3 values and then delete the flagged records.|||I appologize for the tone of my previous message. I did not intend for it to sound aggressive.

I had to address a similar problem to create change files (LDIFs) for changes to an X500 directory. Our solution was to mirror the table structure and do a comparison of changes based off of the key values for the appropriate tables.

Prior to import (or live data manipulation) the two instances are identical. A series of EXISTS / NOT EXISTS comparisons returns the newly added or deleted records. These changses are passed to the ADD/DELETE tables.

At the field level we passed the field names with an INNER JOIN fo the PK to look for field level changes. These changes were passed to a MODIFY table.

I am sure that you can use a variation of this logic to solve your problem.

Records Between two date columns

Hi All,
I am having a problem getting records between two date columns. My table is shown below.

SAP_ID JoinDate LeaveDate
1 2006-01-23 00:00:00.000 2006-04-12 00:00:00.000
2 2006-09-04 00:00:00.000 2007-10-10 00:00:00.000
3 2006-10-03 00:00:00.000 2007-01-01 00:00:00.000
4 2007-12-04 00:00:00.000 2007-10-10 00:00:00.000

From asp page i am getting joindate and leavedate . I need to show records between joindate and leavedate.

For ex:
If
JoinDate=2006-01-01
LeaveDate=2007-01-01
then

report should show 1,2,3 SAP No's. But My Query showing only 1, 3. Here is the my query.

select sap_id from tblsap where joindate >= '2006-01-01' and leavedate <='2007-01-01'

I am not able to figure out the problem. Can some one please post suggestions to my problem.

Rajesh

The row 3 has leaveDate greater than 2007-01-01. You can AND logic in where clause and that row doesn't match where clause. So your query won't catch this row. You need understand requirement and make a little change your code.

SAP_ID JoinDate LeaveDate
3 2006-10-03 00:00:00.000 2007-01-01 00:00:00.000

select sap_id from tblsap where joindate >= '2006-01-01' and leavedate <='2007-01-01'

|||

As I understand the problem, you want rows where the 'active' period included any part of the year 2006.

I think that this should accomplish the task.

Code Snippet

SET NOCOUNT ON

DECLARE @.MyTable table
( SAP_ID int,
JoinDate datetime,
LeaveDate datetime
)

INSERT INTO @.MyTable VALUES ( 1,'2006-01-23', '2006-04-12' )
INSERT INTO @.MyTable VALUES ( 2,'2006-09-04', '2007-10-10' )
INSERT INTO @.MyTable VALUES ( 3,'2006-10-03', '2007-01-01' )
INSERT INTO @.MyTable VALUES ( 4,'2007-12-04', '2007-10-10' )
INSERT INTO @.MyTable VALUES ( 5,'2005-05-15', '2007-10-10' )
INSERT INTO @.MyTable VALUES ( 6,'2007-01-01', '2007-10-10' )
INSERT INTO @.MyTable VALUES ( 7,'2005-05-15', '2005-12-31' )

SELECT
Sap_ID,
JoinDate,
LeaveDate
FROM @.MyTable
WHERE ( JoinDate < '2007-01-01' --Join Prior 2007 and
AND LeaveDate >= '2006-01-01' --Leave 2006 or After
)

RecordCount -1

Hello,
I am using the function below to get some values form database.
However for RecordCount it always returns -1.
Any clues hot to fix it?
Thx
YuriW
Function RunSQL (sConnection_String, sSQL_QUERY)
Dim db_object
Dim sSQL_string
'connection_string = "DSN=Magnetic;UID=laurel;PWD=uklever"
sSQL_string = sSQL_QUERY
' Create the conection object.
Set db_object = CreateObject("ADODB.Connection")
' or use this method if a DSN entry was created.
db_object.Open(sConnection_String)
' Get the recordset returned from a select query.
Set recordset = db_object.Execute(sSQL_string)
' Display the results of the query.
sOut = recordset.RecordCount
RunSQL = sOut
' Close the database connection.
db_object.Close
Set db_object = Nothing
End Function
http://www.aspfaq.com/2193
http://www.aspfaq.com/
(Reverse address to reply.)
"Yuri Weinstein (HotMail)" <yuriw_NOSPAM@.hotmail.com> wrote in message
news:7Mnxc.68469$yx3.34214@.newssvr29.news.prodigy. com...
Hello,
I am using the function below to get some values form database.
However for RecordCount it always returns -1.
Any clues hot to fix it?
Thx
YuriW
Function RunSQL (sConnection_String, sSQL_QUERY)
Dim db_object
Dim sSQL_string
'connection_string = "DSN=Magnetic;UID=laurel;PWD=uklever"
sSQL_string = sSQL_QUERY
' Create the conection object.
Set db_object = CreateObject("ADODB.Connection")
' or use this method if a DSN entry was created.
db_object.Open(sConnection_String)
' Get the recordset returned from a select query.
Set recordset = db_object.Execute(sSQL_string)
' Display the results of the query.
sOut = recordset.RecordCount
RunSQL = sOut
' Close the database connection.
db_object.Close
Set db_object = Nothing
End Function
|||Thanks you. I am still not clear how to change the code in order to fix it?
"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:eyU6tjYTEHA.3580@.TK2MSFTNGP09.phx.gbl...
> http://www.aspfaq.com/2193
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
>
>
> "Yuri Weinstein (HotMail)" <yuriw_NOSPAM@.hotmail.com> wrote in message
> news:7Mnxc.68469$yx3.34214@.newssvr29.news.prodigy. com...
> Hello,
> I am using the function below to get some values form database.
> However for RecordCount it always returns -1.
> Any clues hot to fix it?
> Thx
> YuriW
> Function RunSQL (sConnection_String, sSQL_QUERY)
> Dim db_object
> Dim sSQL_string
> 'connection_string = "DSN=Magnetic;UID=laurel;PWD=uklever"
> sSQL_string = sSQL_QUERY
> ' Create the conection object.
> Set db_object = CreateObject("ADODB.Connection")
> ' or use this method if a DSN entry was created.
> db_object.Open(sConnection_String)
> ' Get the recordset returned from a select query.
> Set recordset = db_object.Execute(sSQL_string)
> ' Display the results of the query.
> sOut = recordset.RecordCount
> RunSQL = sOut
> ' Close the database connection.
> db_object.Close
> Set db_object = Nothing
> End Function
>
|||Maybe you need to look at the article again?
http://www.aspfaq.com/
(Reverse address to reply.)
"Yuri Weinstein (HotMail)" <yuriw_NOSPAM@.hotmail.com> wrote in message
news:dnoxc.68491$iR3.59055@.newssvr29.news.prodigy. com...
> Thanks you. I am still not clear how to change the code in order to fix
it?
>
> "Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
> news:eyU6tjYTEHA.3580@.TK2MSFTNGP09.phx.gbl...
>
|||Recordcount is not supported with the default forward-only cursor. You can try to set the type of cursor before you excute your SQL code.
"Yuri Weinstein (HotMail)" <yuriw_NOSPAM@.hotmail.com> wrote in message news:7Mnxc.68469$yx3.34214@.newssvr29.news.prodigy. com...
Hello,
I am using the function below to get some values form database.
However for RecordCount it always returns -1.
Any clues hot to fix it?
Thx
YuriW
Function RunSQL (sConnection_String, sSQL_QUERY)
Dim db_object
Dim sSQL_string
'connection_string = "DSN=Magnetic;UID=laurel;PWD=uklever"
sSQL_string = sSQL_QUERY
' Create the conection object.
Set db_object = CreateObject("ADODB.Connection")
' or use this method if a DSN entry was created.
db_object.Open(sConnection_String)
' Get the recordset returned from a select query.
Set recordset = db_object.Execute(sSQL_string)
' Display the results of the query.
sOut = recordset.RecordCount
RunSQL = sOut
' Close the database connection.
db_object.Close
Set db_object = Nothing
End Function
|||Got it, thx!
"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:eyU6tjYTEHA.3580@.TK2MSFTNGP09.phx.gbl...
> http://www.aspfaq.com/2193
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
>
>
> "Yuri Weinstein (HotMail)" <yuriw_NOSPAM@.hotmail.com> wrote in message
> news:7Mnxc.68469$yx3.34214@.newssvr29.news.prodigy. com...
> Hello,
> I am using the function below to get some values form database.
> However for RecordCount it always returns -1.
> Any clues hot to fix it?
> Thx
> YuriW
> Function RunSQL (sConnection_String, sSQL_QUERY)
> Dim db_object
> Dim sSQL_string
> 'connection_string = "DSN=Magnetic;UID=laurel;PWD=uklever"
> sSQL_string = sSQL_QUERY
> ' Create the conection object.
> Set db_object = CreateObject("ADODB.Connection")
> ' or use this method if a DSN entry was created.
> db_object.Open(sConnection_String)
> ' Get the recordset returned from a select query.
> Set recordset = db_object.Execute(sSQL_string)
> ' Display the results of the query.
> sOut = recordset.RecordCount
> RunSQL = sOut
> ' Close the database connection.
> db_object.Close
> Set db_object = Nothing
> End Function
>
|||In "Display the results of the query" you should iterate through recordset
with MoveFirst/MoveNext. Add counter into this cycle
dim counter
counter = 0
rs.MoveFirst
while not rs.EOF
... display record
counter = counter + 1
rs.MoveNext
wend
'here counter contains number of records
//--
Regards,
Vassiliev V. V.
http://www-sharp.com -
Scripting/HTA/.Net Framework IDE
"Yuri Weinstein (HotMail)" <yuriw_NOSPAM@.hotmail.com> ??/?? ?
?? ??: news:7Mnxc.68469$yx3.34214@.newssvr29.news.prodigy. com...
Hello,
I am using the function below to get some values form database.
However for RecordCount it always returns -1.
Any clues hot to fix it?
Thx
YuriW
Function RunSQL (sConnection_String, sSQL_QUERY)
Dim db_object
Dim sSQL_string
'connection_string = "DSN=Magnetic;UID=laurel;PWD=uklever"
sSQL_string = sSQL_QUERY
' Create the conection object.
Set db_object = CreateObject("ADODB.Connection")
' or use this method if a DSN entry was created.
db_object.Open(sConnection_String)
' Get the recordset returned from a select query.
Set recordset = db_object.Execute(sSQL_string)
' Display the results of the query.
sOut = recordset.RecordCount
RunSQL = sOut
' Close the database connection.
db_object.Close
Set db_object = Nothing
End Function

Record set update

Hi

I am new to visual studio and I am attempting to edit records held in mysql, the code below runs and throws no errors "strAdd" is underlined and says that it is used before it has been given a value! But If I debug.print(strAdd) I get the expected string returned. What do I need to do to get the updated records saved?

' code

Dim cn As ADODB.Connection

Dim rs As ADODB.Recordset

Dim StrAdd as string

cn = New ADODB.Connection

cn.ConnectionString = "Provider=SQLNCLI;" _

& "Server=(local);" _

& "Database=customerlink;" _

& "Integrated Security=SSPI;" _

& "DataTypeCompatibility=80;" _

& "MARS Connection=True;"

Dim mySQL As String

mySQL = "SELECT*" & _

" FROM tblvsol" & _

" Where RevStatus = " & 0 & _

" And GeoCodeStatus = " & 1

cn.Open()

rs = New ADODB.Recordset

With rs

.ActiveConnection = cn

.CursorLocation = ADODB.CursorLocationEnum.adUseClient

.CursorType = ADODB.CursorTypeEnum.adOpenDynamic

.LockType = ADODB.LockTypeEnum.adLockBatchOptimistic

.Open(mySQL)

End With

Do While Not rs.EOF

Code here finds the value for the string variable ‘atrAdd’

strAdd = New Value

If Not strAdd Is Nothing Then

rs.Fields("location").Value = strAdd

rs.Fields("RevStatus").Value = 1

rs.Update()

else

end if

loop

Regards

Joe

Hi Experts

Have I posted in the wrong forum?

Regards

|||Can you add some code on how you're assigning new value to strAdd?|||Actually, yes you posted in the wrong forum, but nevertheless we will help you. Do you enter the Is Nothing part and the appropiate update or does the execution never touch this portion of the code ? Are you able to send a reproducable class to us ? as from the information you posted the code should be Ok. Did you start the profiler to see wheter the command is executed against the database or could you make sure through debugging that it never enters portion of the code ?

Jens K. Suessmeyer.

http://www.sqlserver2005.de
|||

Hi Experts

Thanks for your response, The variable StrAdd Is a street address which is returned from a mappoint recordset:

StrAdd = objResults.Item(1).Location.StreetAddress.Value

This is returned as a string.

The update portion of the code is only entered If a string value is returned from the mappoint recordset.

Regards,

joe

|||Ok, this is now a bit clearer to me, but did you check the value during debugging time or did you run the profiler ?

Jens K. Suessmeyer.

http://www.sqlserver2005.de
|||

Hi Jens K. Suessmeyer.

Thanks for your response, I had checked the value during debugging. I have had a reply posted on another forum which has provided me with the answer, I appreciate your efforts on my behalf and would not like to waste your time. I have reproduced the reply below, again..

Thank you,

Joe

instead of opening the recordset like so:

rcdSet.Open strSQL, ADO, adOpenForwardOnly, adLockReadOnly, -1

Use this method:

rcdSet.Open strSQL, ADO, adOpenDynamic, adLockBatchOptimistic, -1

Now, after looping through your recordset and changing your values, call:

rcdSet.UpdateBatch

|||Or as an alternative if you want to use Update method and not UpdateBatch - then you can use adLockOptimistic for lock type.