Im unsing VB6 SP6 and SQl 2000
Im creating a recordset using the following codsample
Dim ContraRS as ADODB.Recordset
Dim SQLString as String
Set ContraRS = New ADODB.Recordset
SQL String = "Select * from Table1 where Id = 2
ContraRS.CursorLocation = adUseClient
ContraRS.Open SQLString, UserDBConnect, adOpenForwardOnly, adLockReadOnly
This returns a recordset. Is there a way i can repeat theis process and
append to the data. The reason for this is im trying to obtain a list of
Banking transactions and there may be multiple id's to search for, non are
fixed as we have no way of knowing which clients (id's) will be submitting
data in on any days. I could build a dynamic SQl query with lots of 'AND Id
=
'' but im looking to see if there is a simplier wayWhere do these ID numbers come from? Maybe you can use a subquery:
SELECT *
FROM Table1
WHERE id IN
(SELECT id
FROM SomeOtherTable
WHERE some_date = @.date
/* @.date = the date of the data you are searching for */)
Best not to use SELECT * in production code - list the required column
names instead.
Make use of stored procedures where you can because that approach has
many advantages over executing SELECT statements directly from VB. If
you use a stored proc you can pass a list of IDs as parameters and use
them in an IN clause:
...WHERE id IN (@.id1, @.id2, @.id3, ...)
Finally, why are you still developing VB6, which was officially retired
as of yesterday ;-).
David Portas
SQL Server MVP
--|||Peter Newman wrote:
> Im unsing VB6 SP6 and SQl 2000
> Im creating a recordset using the following codsample
> Dim ContraRS as ADODB.Recordset
> Dim SQLString as String
> Set ContraRS = New ADODB.Recordset
> SQL String = "Select * from Table1 where Id = 2
> ContraRS.CursorLocation = adUseClient
> ContraRS.Open SQLString, UserDBConnect, adOpenForwardOnly,
> adLockReadOnly
> This returns a recordset. Is there a way i can repeat theis process
> and append to the data. The reason for this is im trying to obtain a
> list of Banking transactions and there may be multiple id's to search
> for, non are fixed as we have no way of knowing which clients (id's)
> will be submitting data in on any days. I could build a dynamic SQl
> query with lots of 'AND Id = '' but im looking to see if there is a
> simplier way
No. I would consider moving this processing into a stored procedure which
returns only the final recordset needed by the client.
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"|||Peter Newman wrote:
> Im unsing VB6 SP6 and SQl 2000
> Im creating a recordset using the following codsample
> Dim ContraRS as ADODB.Recordset
> Dim SQLString as String
> Set ContraRS = New ADODB.Recordset
> SQL String = "Select * from Table1 where Id = 2
> ContraRS.CursorLocation = adUseClient
> ContraRS.Open SQLString, UserDBConnect, adOpenForwardOnly,
> adLockReadOnly
> This returns a recordset. Is there a way i can repeat theis process
> and append to the data. The reason for this is im trying to obtain a
> list of Banking transactions and there may be multiple id's to search
> for, non are fixed as we have no way of knowing which clients (id's)
> will be submitting data in on any days. I could build a dynamic SQl
> query with lots of 'AND Id = '' but im looking to see if there is a
> simplier way
How are you deciding what id's to search for?
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"
Showing posts with label recordset. Show all posts
Showing posts with label recordset. Show all posts
Wednesday, March 7, 2012
Recordset with SQL and VB
Hi,
I am trying to cycle through a table and trigger an event based on some critera. I am not sure how to do it. I am a classic VBA guy, so I might be way off:
Dim myConnectionAs SqlConnectionDim myCommandAs SqlCommand myConnection =New SqlConnection("MY SQL DATA SOURCE") myConnection.Open() myCommand =New SqlCommand("SELECT * FROM history", myConnection)Dim dr = myCommand.ExecuteReader()Dim iAs Integer = 1While dr.read() i = i + 1' HOW DO I CYCLE THROUGH THE ROWS AND ASK IF A FIELD EQUALS A VALUE ' field name = "Tail"If dr(i) = ?Then MsgBox("ok")End If End While dr.Close() myConnection.Close()Dim myConnectionAs SqlConnection
Dim myCommandAs SqlCommand
myConnection =New SqlConnection("MY SQL DATA SOURCE")
myConnection.Open()myCommand =New SqlCommand("SELECT * FROM history", myConnection)
Dim dr As SqlDataReader = myCommand.ExecuteReader()
While dr.read()
If dr("Tail").ToString() = somestringvalue Then'do your thang
End While
dr.Close()
myConnection.Close()
|||
Have a look at the two examples fromhere on how to loop through data.
|||
Perfect..thanks to both of you!
Recordset to Array
Using Visual Basic can you assign a resultant recordset to an array
without looping through the number or returned records and fields.
Dim strArray() as String
Dim rs As ADODB.Recordset
rs.Open SQLStatement, Connection
strArray = rs
Many thanks for any assistance.
See if this helps you
http://www.sommarskog.se/arrays-in-sql.html
Madhivanan
|||There are no arrays in SQL Server and nor are they needed. What is your
question?
David Portas
SQL Server MVP
|||String and array are distinct data types in VB. One workaround is to use a
variant array and use GetRows method. See your VB/ADO manual for details and
examples on how to use this method.
Anith
without looping through the number or returned records and fields.
Dim strArray() as String
Dim rs As ADODB.Recordset
rs.Open SQLStatement, Connection
strArray = rs
Many thanks for any assistance.
See if this helps you
http://www.sommarskog.se/arrays-in-sql.html
Madhivanan
|||There are no arrays in SQL Server and nor are they needed. What is your
question?
David Portas
SQL Server MVP
|||String and array are distinct data types in VB. One workaround is to use a
variant array and use GetRows method. See your VB/ADO manual for details and
examples on how to use this method.
Anith
Recordset to Array
Using Visual Basic can you assign a resultant recordset to an array
without looping through the number or returned records and fields.
Dim strArray() as String
Dim rs As ADODB.Recordset
rs.Open SQLStatement, Connection
strArray = rs
Many thanks for any assistance.See if this helps you
http://www.sommarskog.se/arrays-in-sql.html
Madhivanan|||There are no arrays in SQL Server and nor are they needed. What is your
question?
David Portas
SQL Server MVP
--|||String and array are distinct data types in VB. One workaround is to use a
variant array and use GetRows method. See your VB/ADO manual for details and
examples on how to use this method.
Anith
without looping through the number or returned records and fields.
Dim strArray() as String
Dim rs As ADODB.Recordset
rs.Open SQLStatement, Connection
strArray = rs
Many thanks for any assistance.See if this helps you
http://www.sommarskog.se/arrays-in-sql.html
Madhivanan|||There are no arrays in SQL Server and nor are they needed. What is your
question?
David Portas
SQL Server MVP
--|||String and array are distinct data types in VB. One workaround is to use a
variant array and use GetRows method. See your VB/ADO manual for details and
examples on how to use this method.
Anith
Recordset to Array
Using Visual Basic can you assign a resultant recordset to an array
without looping through the number or returned records and fields.
Dim strArray() as String
Dim rs As ADODB.Recordset
rs.Open SQLStatement, Connection
strArray = rs
Many thanks for any assistance.See if this helps you
http://www.sommarskog.se/arrays-in-sql.html
Madhivanan|||There are no arrays in SQL Server and nor are they needed. What is your
question?
David Portas
SQL Server MVP
--|||String and array are distinct data types in VB. One workaround is to use a
variant array and use GetRows method. See your VB/ADO manual for details and
examples on how to use this method.
Anith
without looping through the number or returned records and fields.
Dim strArray() as String
Dim rs As ADODB.Recordset
rs.Open SQLStatement, Connection
strArray = rs
Many thanks for any assistance.See if this helps you
http://www.sommarskog.se/arrays-in-sql.html
Madhivanan|||There are no arrays in SQL Server and nor are they needed. What is your
question?
David Portas
SQL Server MVP
--|||String and array are distinct data types in VB. One workaround is to use a
variant array and use GetRows method. See your VB/ADO manual for details and
examples on how to use this method.
Anith
Recordset solution is sought. Please help.
-- First of all, thank you for your effort and your time.
--
-- I am looking for a record set solution to the following problem.
--
-- We have feature 1 2 and 3 and users define certain combinations of them.
These can be 2-way combinations where feature3 is null or 3 way combinations
-- For simplicity, let us assume we have values A B and C for these three
features.
-- Users can fill them in redundant ways as
-- ACB, CBA, BCA, CAB, ABC, BAC.
-- I would like to select a single occurrence, ABC, or one out of six,
always one distinct combination.
--
-- I have made an attempt however I remove all duplicate occurances. I just
don't see how I can resolve this. Please help.
--
-- Thanks again for your help.
--
set nocount on
declare @.tmp table (Feature varchar(10) not null, Feature1 varchar(10) not
null, Feature2 varchar(10) null)
insert @.tmp values ('A','C','B') -- one of the folowing 6 to return
insert @.tmp values ('C','B','A')
insert @.tmp values ('B','C','A')
insert @.tmp values ('C','A','B')
insert @.tmp values ('A','B','C')
insert @.tmp values ('B','A','C')
insert @.tmp values ('D','A','C') -- return either DAC or ACD
insert @.tmp values ('P','A','C')
insert @.tmp values ('N','B','D')
insert @.tmp values ('A','C','D')
SELECT *
FROM @.tmp t
WHERE NOT EXISTS
(
SELECT *
FROM @.tmp t2
WHERE t2.feature1 = t.feature AND t2.feature = t.feature1
AND t2.feature2 IS NULL AND t.feature2 IS NULL -- two way
)
AND NOT EXISTS
(
SELECT *
FROM @.tmp t2
WHERE -- 3 way
t2.feature = t.feature AND t2.feature1 = t.feature2 AND t2.feature2 =
t.feature1
OR
t2.feature1 = t.feature1 AND t2.feature = t.feature2 AND t2.feature2 =
t.feature
OR
t2.feature2 = t.feature2 AND t2.feature = t.feature1 AND t2.feature1 =
t.feature
)
-- Expected outcome:
A,B,C
D,A,C
P,A,C
N,B,DYou can add an Identity column to the temp table and check for Identity
column not equal in your sub query.
Perayu
"Farmer" wrote:
> -- First of all, thank you for your effort and your time.
> --
> -- I am looking for a record set solution to the following problem.
> --
> -- We have feature 1 2 and 3 and users define certain combinations of them
.
> These can be 2-way combinations where feature3 is null or 3 way combinatio
ns
> -- For simplicity, let us assume we have values A B and C for these three
> features.
> -- Users can fill them in redundant ways as
> -- ACB, CBA, BCA, CAB, ABC, BAC.
> -- I would like to select a single occurrence, ABC, or one out of six,
> always one distinct combination.
> --
> -- I have made an attempt however I remove all duplicate occurances. I jus
t
> don't see how I can resolve this. Please help.
> --
> -- Thanks again for your help.
> --
> set nocount on
> declare @.tmp table (Feature varchar(10) not null, Feature1 varchar(10) not
> null, Feature2 varchar(10) null)
> insert @.tmp values ('A','C','B') -- one of the folowing 6 to return
> insert @.tmp values ('C','B','A')
> insert @.tmp values ('B','C','A')
> insert @.tmp values ('C','A','B')
> insert @.tmp values ('A','B','C')
> insert @.tmp values ('B','A','C')
> insert @.tmp values ('D','A','C') -- return either DAC or ACD
> insert @.tmp values ('P','A','C')
> insert @.tmp values ('N','B','D')
> insert @.tmp values ('A','C','D')
>
> SELECT *
> FROM @.tmp t
> WHERE NOT EXISTS
> (
> SELECT *
> FROM @.tmp t2
> WHERE t2.feature1 = t.feature AND t2.feature = t.feature1
> AND t2.feature2 IS NULL AND t.feature2 IS NULL -- two way
> )
> AND NOT EXISTS
> (
> SELECT *
> FROM @.tmp t2
> WHERE -- 3 way
> t2.feature = t.feature AND t2.feature1 = t.feature2 AND t2.feature2 =
> t.feature1
> OR
> t2.feature1 = t.feature1 AND t2.feature = t.feature2 AND t2.feature2 =
> t.feature
> OR
> t2.feature2 = t.feature2 AND t2.feature = t.feature1 AND t2.feature1 =
> t.feature
> )
> -- Expected outcome:
> A,B,C
> D,A,C
> P,A,C
> N,B,D
>
>|||Thank you for replying.
How do you suggest I do it? Please post what you think I can use.
"Perayu" <Perayu@.discussions.microsoft.com> wrote in message
news:B06C8780-1282-4F0E-AC17-EFBF72703991@.microsoft.com...
> You can add an Identity column to the temp table and check for Identity
> column not equal in your sub query.
> Perayu
> "Farmer" wrote:
>|||Farmer
Why not perfom such reports on the client side?
"Farmer" <someone@.somewhere.com> wrote in message
news:ukeQ0UzpFHA.3180@.TK2MSFTNGP15.phx.gbl...
> Thank you for replying.
> How do you suggest I do it? Please post what you think I can use.
>
> "Perayu" <Perayu@.discussions.microsoft.com> wrote in message
> news:B06C8780-1282-4F0E-AC17-EFBF72703991@.microsoft.com...
>
--
-- I am looking for a record set solution to the following problem.
--
-- We have feature 1 2 and 3 and users define certain combinations of them.
These can be 2-way combinations where feature3 is null or 3 way combinations
-- For simplicity, let us assume we have values A B and C for these three
features.
-- Users can fill them in redundant ways as
-- ACB, CBA, BCA, CAB, ABC, BAC.
-- I would like to select a single occurrence, ABC, or one out of six,
always one distinct combination.
--
-- I have made an attempt however I remove all duplicate occurances. I just
don't see how I can resolve this. Please help.
--
-- Thanks again for your help.
--
set nocount on
declare @.tmp table (Feature varchar(10) not null, Feature1 varchar(10) not
null, Feature2 varchar(10) null)
insert @.tmp values ('A','C','B') -- one of the folowing 6 to return
insert @.tmp values ('C','B','A')
insert @.tmp values ('B','C','A')
insert @.tmp values ('C','A','B')
insert @.tmp values ('A','B','C')
insert @.tmp values ('B','A','C')
insert @.tmp values ('D','A','C') -- return either DAC or ACD
insert @.tmp values ('P','A','C')
insert @.tmp values ('N','B','D')
insert @.tmp values ('A','C','D')
SELECT *
FROM @.tmp t
WHERE NOT EXISTS
(
SELECT *
FROM @.tmp t2
WHERE t2.feature1 = t.feature AND t2.feature = t.feature1
AND t2.feature2 IS NULL AND t.feature2 IS NULL -- two way
)
AND NOT EXISTS
(
SELECT *
FROM @.tmp t2
WHERE -- 3 way
t2.feature = t.feature AND t2.feature1 = t.feature2 AND t2.feature2 =
t.feature1
OR
t2.feature1 = t.feature1 AND t2.feature = t.feature2 AND t2.feature2 =
t.feature
OR
t2.feature2 = t.feature2 AND t2.feature = t.feature1 AND t2.feature1 =
t.feature
)
-- Expected outcome:
A,B,C
D,A,C
P,A,C
N,B,DYou can add an Identity column to the temp table and check for Identity
column not equal in your sub query.
Perayu
"Farmer" wrote:
> -- First of all, thank you for your effort and your time.
> --
> -- I am looking for a record set solution to the following problem.
> --
> -- We have feature 1 2 and 3 and users define certain combinations of them
.
> These can be 2-way combinations where feature3 is null or 3 way combinatio
ns
> -- For simplicity, let us assume we have values A B and C for these three
> features.
> -- Users can fill them in redundant ways as
> -- ACB, CBA, BCA, CAB, ABC, BAC.
> -- I would like to select a single occurrence, ABC, or one out of six,
> always one distinct combination.
> --
> -- I have made an attempt however I remove all duplicate occurances. I jus
t
> don't see how I can resolve this. Please help.
> --
> -- Thanks again for your help.
> --
> set nocount on
> declare @.tmp table (Feature varchar(10) not null, Feature1 varchar(10) not
> null, Feature2 varchar(10) null)
> insert @.tmp values ('A','C','B') -- one of the folowing 6 to return
> insert @.tmp values ('C','B','A')
> insert @.tmp values ('B','C','A')
> insert @.tmp values ('C','A','B')
> insert @.tmp values ('A','B','C')
> insert @.tmp values ('B','A','C')
> insert @.tmp values ('D','A','C') -- return either DAC or ACD
> insert @.tmp values ('P','A','C')
> insert @.tmp values ('N','B','D')
> insert @.tmp values ('A','C','D')
>
> SELECT *
> FROM @.tmp t
> WHERE NOT EXISTS
> (
> SELECT *
> FROM @.tmp t2
> WHERE t2.feature1 = t.feature AND t2.feature = t.feature1
> AND t2.feature2 IS NULL AND t.feature2 IS NULL -- two way
> )
> AND NOT EXISTS
> (
> SELECT *
> FROM @.tmp t2
> WHERE -- 3 way
> t2.feature = t.feature AND t2.feature1 = t.feature2 AND t2.feature2 =
> t.feature1
> OR
> t2.feature1 = t.feature1 AND t2.feature = t.feature2 AND t2.feature2 =
> t.feature
> OR
> t2.feature2 = t.feature2 AND t2.feature = t.feature1 AND t2.feature1 =
> t.feature
> )
> -- Expected outcome:
> A,B,C
> D,A,C
> P,A,C
> N,B,D
>
>|||Thank you for replying.
How do you suggest I do it? Please post what you think I can use.
"Perayu" <Perayu@.discussions.microsoft.com> wrote in message
news:B06C8780-1282-4F0E-AC17-EFBF72703991@.microsoft.com...
> You can add an Identity column to the temp table and check for Identity
> column not equal in your sub query.
> Perayu
> "Farmer" wrote:
>|||Farmer
Why not perfom such reports on the client side?
"Farmer" <someone@.somewhere.com> wrote in message
news:ukeQ0UzpFHA.3180@.TK2MSFTNGP15.phx.gbl...
> Thank you for replying.
> How do you suggest I do it? Please post what you think I can use.
>
> "Perayu" <Perayu@.discussions.microsoft.com> wrote in message
> news:B06C8780-1282-4F0E-AC17-EFBF72703991@.microsoft.com...
>
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
Recordset opens as read-only -- WHY?
Hello,
I have a C++ / MFC app which uses CDatabase and CRecordset to connect to a
ODBC data source for a SQL Server 7 database. The application worked
properly with a MySQL database, but after swapping it for SQL Server
problems emerged. In particular, any calls to CRecordset::AddNew() and
CRecordset::Edit() cause an exception to be thrown with the error message
"Recordset is read-only". Stepping through the code for CRecordset::Open(),
I can see that it indeeds fail to open the recordset with SQL_CONCUR_ROWVER
and SQL_CONCUR_LOCK, finally succeeding with SQL_CONCUR_READ_ONLY. I need to
be able to write to the db!! What is going on here?
I call CRecordset::Open() with no SQL string and no options (which defaults
to full access and not read-only).
I am not a SQL Server expert, but I tried playing with the config a little
bit:
- The database is NOT set for read-only.
- The database option "Restrict Access" is NOT selected.
- I checkmarked all permissions (SELECT, INSERT, UPDATE, DELETE, DRI)
for my app's user on all tables in the db.
- My connection string uses the user 'sa', which is the login name for
the user 'dbo'.
In the ODBC data source config, I set authentication to SQL Server
authentication, and accepted the default for all the other options.
I haven't tried using a different class (say, CDaoRecordset) nor do I plan
on doing that. I really need to get this to work with CRecordset. Any help
is appreciated.
Thanks,
SL
"Silvio Lopes de Oliveira" <silviol@.aaesys.com> wrote in message
news:s91wc.11$5Z4.18584@.news.uswest.net...
> Hello,
> I have a C++ / MFC app which uses CDatabase and CRecordset to connect to a
> ODBC data source for a SQL Server 7 database. The application worked
> properly with a MySQL database, but after swapping it for SQL Server
> problems emerged. In particular, any calls to CRecordset::AddNew() and
> CRecordset::Edit() cause an exception to be thrown with the error message
> "Recordset is read-only". Stepping through the code for
CRecordset::Open(),
> I can see that it indeeds fail to open the recordset with
SQL_CONCUR_ROWVER
> and SQL_CONCUR_LOCK, finally succeeding with SQL_CONCUR_READ_ONLY. I need
to
> be able to write to the db!! What is going on here?
> I call CRecordset::Open() with no SQL string and no options (which
defaults
> to full access and not read-only).
> I am not a SQL Server expert, but I tried playing with the config a little
> bit:
> - The database is NOT set for read-only.
> - The database option "Restrict Access" is NOT selected.
> - I checkmarked all permissions (SELECT, INSERT, UPDATE, DELETE, DRI)
> for my app's user on all tables in the db.
> - My connection string uses the user 'sa', which is the login name for
> the user 'dbo'.
> In the ODBC data source config, I set authentication to SQL Server
> authentication, and accepted the default for all the other options.
> I haven't tried using a different class (say, CDaoRecordset) nor do I plan
> on doing that. I really need to get this to work with CRecordset. Any help
> is appreciated.
> Thanks,
> SL
>
This is a guess, but does the table you're working with have a primary key?
Many client tools will not update a table unless there is a primary key
present, as without a key there is no way to identify the rows you want to
update. Or if your recordset is returned by a stored procedure you might be
seeing an issue like this:
http://support.microsoft.com/default...b;en-us;246636
However, I don't do much client-side coding, so I might be on completely the
wrong track here.
Simon
|||Silvio Lopes de Oliveira (silviol@.aaesys.com) writes:
> I have a C++ / MFC app which uses CDatabase and CRecordset to connect to
> a ODBC data source for a SQL Server 7 database. The application worked
> properly with a MySQL database, but after swapping it for SQL Server
> problems emerged. In particular, any calls to CRecordset::AddNew() and
> CRecordset::Edit() cause an exception to be thrown with the error
> message "Recordset is read-only". Stepping through the code for
> CRecordset::Open(), I can see that it indeeds fail to open the recordset
> with SQL_CONCUR_ROWVER and SQL_CONCUR_LOCK, finally succeeding with
> SQL_CONCUR_READ_ONLY. I need to be able to write to the db!! What is
> going on here?
It would have helped if you had posted the query.
I would guess that the reason is that the query is such that SQL
Server will not be able to determine which row to update. This
could be because, as Simon pointed out, of a missing primary key.
But it can also happen if you use GROUP BY, expressions or a whole
load number of things.
When this happens to people in their stored procedures, I recommend
them to not use WHERE CURRENT OF, but use a regular WHERE clause
instead. In your case, this would mean that you would submit a
regular UPDATE statement. But I guess that would be through a
different connection, because the one where you have the cursor
may be busy. And depending on which locking scheme you have on your
cursor, you may look yourself.
The remedy for this would be to get all data into the client, and
the close the cursor, and then you can update from the regular
connection. Unless the amount of data is huge, this is likely to
be more effecient. (And if the amount of data is huge, then using a
stored procedure with all the update logic and never get the data
to he client, would be a big winner.)
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
|||As a matter of fact, it was the lack of a primary key field that caused the
problem. When I imported the tables and data from my MySQL database, the
primary key fields were not marked as such in the SQL Server tables.
Problem fixed. Thanks!
Silvio
"Simon Hayes" <sql@.hayes.ch> wrote in message
news:40c0aeb6$1_3@.news.bluewin.ch...[vbcol=seagreen]
> "Silvio Lopes de Oliveira" <silviol@.aaesys.com> wrote in message
> news:s91wc.11$5Z4.18584@.news.uswest.net...
a[vbcol=seagreen]
message[vbcol=seagreen]
> CRecordset::Open(),
> SQL_CONCUR_ROWVER
need[vbcol=seagreen]
> to
> defaults
little[vbcol=seagreen]
DRI)[vbcol=seagreen]
for[vbcol=seagreen]
plan[vbcol=seagreen]
help
> This is a guess, but does the table you're working with have a primary
key?
> Many client tools will not update a table unless there is a primary key
> present, as without a key there is no way to identify the rows you want to
> update. Or if your recordset is returned by a stored procedure you might
be
> seeing an issue like this:
> http://support.microsoft.com/default...b;en-us;246636
> However, I don't do much client-side coding, so I might be on completely
the
> wrong track here.
> Simon
>
I have a C++ / MFC app which uses CDatabase and CRecordset to connect to a
ODBC data source for a SQL Server 7 database. The application worked
properly with a MySQL database, but after swapping it for SQL Server
problems emerged. In particular, any calls to CRecordset::AddNew() and
CRecordset::Edit() cause an exception to be thrown with the error message
"Recordset is read-only". Stepping through the code for CRecordset::Open(),
I can see that it indeeds fail to open the recordset with SQL_CONCUR_ROWVER
and SQL_CONCUR_LOCK, finally succeeding with SQL_CONCUR_READ_ONLY. I need to
be able to write to the db!! What is going on here?
I call CRecordset::Open() with no SQL string and no options (which defaults
to full access and not read-only).
I am not a SQL Server expert, but I tried playing with the config a little
bit:
- The database is NOT set for read-only.
- The database option "Restrict Access" is NOT selected.
- I checkmarked all permissions (SELECT, INSERT, UPDATE, DELETE, DRI)
for my app's user on all tables in the db.
- My connection string uses the user 'sa', which is the login name for
the user 'dbo'.
In the ODBC data source config, I set authentication to SQL Server
authentication, and accepted the default for all the other options.
I haven't tried using a different class (say, CDaoRecordset) nor do I plan
on doing that. I really need to get this to work with CRecordset. Any help
is appreciated.
Thanks,
SL
"Silvio Lopes de Oliveira" <silviol@.aaesys.com> wrote in message
news:s91wc.11$5Z4.18584@.news.uswest.net...
> Hello,
> I have a C++ / MFC app which uses CDatabase and CRecordset to connect to a
> ODBC data source for a SQL Server 7 database. The application worked
> properly with a MySQL database, but after swapping it for SQL Server
> problems emerged. In particular, any calls to CRecordset::AddNew() and
> CRecordset::Edit() cause an exception to be thrown with the error message
> "Recordset is read-only". Stepping through the code for
CRecordset::Open(),
> I can see that it indeeds fail to open the recordset with
SQL_CONCUR_ROWVER
> and SQL_CONCUR_LOCK, finally succeeding with SQL_CONCUR_READ_ONLY. I need
to
> be able to write to the db!! What is going on here?
> I call CRecordset::Open() with no SQL string and no options (which
defaults
> to full access and not read-only).
> I am not a SQL Server expert, but I tried playing with the config a little
> bit:
> - The database is NOT set for read-only.
> - The database option "Restrict Access" is NOT selected.
> - I checkmarked all permissions (SELECT, INSERT, UPDATE, DELETE, DRI)
> for my app's user on all tables in the db.
> - My connection string uses the user 'sa', which is the login name for
> the user 'dbo'.
> In the ODBC data source config, I set authentication to SQL Server
> authentication, and accepted the default for all the other options.
> I haven't tried using a different class (say, CDaoRecordset) nor do I plan
> on doing that. I really need to get this to work with CRecordset. Any help
> is appreciated.
> Thanks,
> SL
>
This is a guess, but does the table you're working with have a primary key?
Many client tools will not update a table unless there is a primary key
present, as without a key there is no way to identify the rows you want to
update. Or if your recordset is returned by a stored procedure you might be
seeing an issue like this:
http://support.microsoft.com/default...b;en-us;246636
However, I don't do much client-side coding, so I might be on completely the
wrong track here.
Simon
|||Silvio Lopes de Oliveira (silviol@.aaesys.com) writes:
> I have a C++ / MFC app which uses CDatabase and CRecordset to connect to
> a ODBC data source for a SQL Server 7 database. The application worked
> properly with a MySQL database, but after swapping it for SQL Server
> problems emerged. In particular, any calls to CRecordset::AddNew() and
> CRecordset::Edit() cause an exception to be thrown with the error
> message "Recordset is read-only". Stepping through the code for
> CRecordset::Open(), I can see that it indeeds fail to open the recordset
> with SQL_CONCUR_ROWVER and SQL_CONCUR_LOCK, finally succeeding with
> SQL_CONCUR_READ_ONLY. I need to be able to write to the db!! What is
> going on here?
It would have helped if you had posted the query.
I would guess that the reason is that the query is such that SQL
Server will not be able to determine which row to update. This
could be because, as Simon pointed out, of a missing primary key.
But it can also happen if you use GROUP BY, expressions or a whole
load number of things.
When this happens to people in their stored procedures, I recommend
them to not use WHERE CURRENT OF, but use a regular WHERE clause
instead. In your case, this would mean that you would submit a
regular UPDATE statement. But I guess that would be through a
different connection, because the one where you have the cursor
may be busy. And depending on which locking scheme you have on your
cursor, you may look yourself.
The remedy for this would be to get all data into the client, and
the close the cursor, and then you can update from the regular
connection. Unless the amount of data is huge, this is likely to
be more effecient. (And if the amount of data is huge, then using a
stored procedure with all the update logic and never get the data
to he client, would be a big winner.)
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
|||As a matter of fact, it was the lack of a primary key field that caused the
problem. When I imported the tables and data from my MySQL database, the
primary key fields were not marked as such in the SQL Server tables.
Problem fixed. Thanks!
Silvio
"Simon Hayes" <sql@.hayes.ch> wrote in message
news:40c0aeb6$1_3@.news.bluewin.ch...[vbcol=seagreen]
> "Silvio Lopes de Oliveira" <silviol@.aaesys.com> wrote in message
> news:s91wc.11$5Z4.18584@.news.uswest.net...
a[vbcol=seagreen]
message[vbcol=seagreen]
> CRecordset::Open(),
> SQL_CONCUR_ROWVER
need[vbcol=seagreen]
> to
> defaults
little[vbcol=seagreen]
DRI)[vbcol=seagreen]
for[vbcol=seagreen]
plan[vbcol=seagreen]
help
> This is a guess, but does the table you're working with have a primary
key?
> Many client tools will not update a table unless there is a primary key
> present, as without a key there is no way to identify the rows you want to
> update. Or if your recordset is returned by a stored procedure you might
be
> seeing an issue like this:
> http://support.microsoft.com/default...b;en-us;246636
> However, I don't do much client-side coding, so I might be on completely
the
> wrong track here.
> Simon
>
Recordset opens as read-only -- WHY?
Hello,
I have a C++ / MFC app which uses CDatabase and CRecordset to connect to a
ODBC data source for a SQL Server 7 database. The application worked
properly with a MySQL database, but after swapping it for SQL Server
problems emerged. In particular, any calls to CRecordset::AddNew() and
CRecordset::Edit() cause an exception to be thrown with the error message
"Recordset is read-only". Stepping through the code for CRecordset::Open(),
I can see that it indeeds fail to open the recordset with SQL_CONCUR_ROWVER
and SQL_CONCUR_LOCK, finally succeeding with SQL_CONCUR_READ_ONLY. I need to
be able to write to the db!! What is going on here??
I call CRecordset::Open() with no SQL string and no options (which defaults
to full access and not read-only).
I am not a SQL Server expert, but I tried playing with the config a little
bit:
- The database is NOT set for read-only.
- The database option "Restrict Access" is NOT selected.
- I checkmarked all permissions (SELECT, INSERT, UPDATE, DELETE, DRI)
for my app's user on all tables in the db.
- My connection string uses the user 'sa', which is the login name for
the user 'dbo'.
In the ODBC data source config, I set authentication to SQL Server
authentication, and accepted the default for all the other options.
I haven't tried using a different class (say, CDaoRecordset) nor do I plan
on doing that. I really need to get this to work with CRecordset. Any help
is appreciated.
Thanks,
SL"Silvio Lopes de Oliveira" <silviol@.aaesys.com> wrote in message
news:s91wc.11$5Z4.18584@.news.uswest.net...
> Hello,
> I have a C++ / MFC app which uses CDatabase and CRecordset to connect to a
> ODBC data source for a SQL Server 7 database. The application worked
> properly with a MySQL database, but after swapping it for SQL Server
> problems emerged. In particular, any calls to CRecordset::AddNew() and
> CRecordset::Edit() cause an exception to be thrown with the error message
> "Recordset is read-only". Stepping through the code for
CRecordset::Open(),
> I can see that it indeeds fail to open the recordset with
SQL_CONCUR_ROWVER
> and SQL_CONCUR_LOCK, finally succeeding with SQL_CONCUR_READ_ONLY. I need
to
> be able to write to the db!! What is going on here??
> I call CRecordset::Open() with no SQL string and no options (which
defaults
> to full access and not read-only).
> I am not a SQL Server expert, but I tried playing with the config a little
> bit:
> - The database is NOT set for read-only.
> - The database option "Restrict Access" is NOT selected.
> - I checkmarked all permissions (SELECT, INSERT, UPDATE, DELETE, DRI)
> for my app's user on all tables in the db.
> - My connection string uses the user 'sa', which is the login name for
> the user 'dbo'.
> In the ODBC data source config, I set authentication to SQL Server
> authentication, and accepted the default for all the other options.
> I haven't tried using a different class (say, CDaoRecordset) nor do I plan
> on doing that. I really need to get this to work with CRecordset. Any help
> is appreciated.
> Thanks,
> SL
This is a guess, but does the table you're working with have a primary key?
Many client tools will not update a table unless there is a primary key
present, as without a key there is no way to identify the rows you want to
update. Or if your recordset is returned by a stored procedure you might be
seeing an issue like this:
http://support.microsoft.com/defaul...kb;en-us;246636
However, I don't do much client-side coding, so I might be on completely the
wrong track here.
Simon|||Silvio Lopes de Oliveira (silviol@.aaesys.com) writes:
> I have a C++ / MFC app which uses CDatabase and CRecordset to connect to
> a ODBC data source for a SQL Server 7 database. The application worked
> properly with a MySQL database, but after swapping it for SQL Server
> problems emerged. In particular, any calls to CRecordset::AddNew() and
> CRecordset::Edit() cause an exception to be thrown with the error
> message "Recordset is read-only". Stepping through the code for
> CRecordset::Open(), I can see that it indeeds fail to open the recordset
> with SQL_CONCUR_ROWVER and SQL_CONCUR_LOCK, finally succeeding with
> SQL_CONCUR_READ_ONLY. I need to be able to write to the db!! What is
> going on here??
It would have helped if you had posted the query.
I would guess that the reason is that the query is such that SQL
Server will not be able to determine which row to update. This
could be because, as Simon pointed out, of a missing primary key.
But it can also happen if you use GROUP BY, expressions or a whole
load number of things.
When this happens to people in their stored procedures, I recommend
them to not use WHERE CURRENT OF, but use a regular WHERE clause
instead. In your case, this would mean that you would submit a
regular UPDATE statement. But I guess that would be through a
different connection, because the one where you have the cursor
may be busy. And depending on which locking scheme you have on your
cursor, you may look yourself.
The remedy for this would be to get all data into the client, and
the close the cursor, and then you can update from the regular
connection. Unless the amount of data is huge, this is likely to
be more effecient. (And if the amount of data is huge, then using a
stored procedure with all the update logic and never get the data
to he client, would be a big winner.)
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||As a matter of fact, it was the lack of a primary key field that caused the
problem. When I imported the tables and data from my MySQL database, the
primary key fields were not marked as such in the SQL Server tables.
Problem fixed. Thanks!
Silvio
"Simon Hayes" <sql@.hayes.ch> wrote in message
news:40c0aeb6$1_3@.news.bluewin.ch...
> "Silvio Lopes de Oliveira" <silviol@.aaesys.com> wrote in message
> news:s91wc.11$5Z4.18584@.news.uswest.net...
> > Hello,
> > I have a C++ / MFC app which uses CDatabase and CRecordset to connect to
a
> > ODBC data source for a SQL Server 7 database. The application worked
> > properly with a MySQL database, but after swapping it for SQL Server
> > problems emerged. In particular, any calls to CRecordset::AddNew() and
> > CRecordset::Edit() cause an exception to be thrown with the error
message
> > "Recordset is read-only". Stepping through the code for
> CRecordset::Open(),
> > I can see that it indeeds fail to open the recordset with
> SQL_CONCUR_ROWVER
> > and SQL_CONCUR_LOCK, finally succeeding with SQL_CONCUR_READ_ONLY. I
need
> to
> > be able to write to the db!! What is going on here??
> > I call CRecordset::Open() with no SQL string and no options (which
> defaults
> > to full access and not read-only).
> > I am not a SQL Server expert, but I tried playing with the config a
little
> > bit:
> > - The database is NOT set for read-only.
> > - The database option "Restrict Access" is NOT selected.
> > - I checkmarked all permissions (SELECT, INSERT, UPDATE, DELETE,
DRI)
> > for my app's user on all tables in the db.
> > - My connection string uses the user 'sa', which is the login name
for
> > the user 'dbo'.
> > In the ODBC data source config, I set authentication to SQL Server
> > authentication, and accepted the default for all the other options.
> > I haven't tried using a different class (say, CDaoRecordset) nor do I
plan
> > on doing that. I really need to get this to work with CRecordset. Any
help
> > is appreciated.
> > Thanks,
> > SL
> This is a guess, but does the table you're working with have a primary
key?
> Many client tools will not update a table unless there is a primary key
> present, as without a key there is no way to identify the rows you want to
> update. Or if your recordset is returned by a stored procedure you might
be
> seeing an issue like this:
> http://support.microsoft.com/defaul...kb;en-us;246636
> However, I don't do much client-side coding, so I might be on completely
the
> wrong track here.
> Simon
Recordset opens as read-only -- WHY?
Hello,
I have a C++ / MFC app which uses CDatabase and CRecordset to connect to a
ODBC data source for a SQL Server 7 database. The application worked
properly with a mysql database, but after swapping it for SQL Server
problems emerged. In particular, any calls to CRecordset::AddNew() and
CRecordset::Edit() cause an exception to be thrown with the error message
"Recordset is read-only". Stepping through the code for CRecordset::Open(),
I can see that it indeeds fail to open the recordset with SQL_CONCUR_ROWVER
and SQL_CONCUR_LOCK, finally succeeding with SQL_CONCUR_READ_ONLY. I need to
be able to write to the db!! What is going on here'
I call CRecordset::Open() with no SQL string and no options (which defaults
to full access and not read-only).
I am not a SQL Server expert, but I tried playing with the config a little
bit:
- The database is NOT set for read-only.
- The database option "Restrict Access" is NOT selected.
- I checkmarked all permissions (SELECT, INSERT, UPDATE, DELETE, DRI)
for my app's user on all tables in the db.
- My connection string uses the user 'sa', which is the login name for
the user 'dbo'.
In the ODBC data source config, I set authentication to SQL Server
authentication, and accepted the default for all the other options.
I haven't tried using a different class (say, CDaoRecordset) nor do I plan
on doing that. I really need to get this to work with CRecordset. Any help
is appreciated.
Thanks,
SL"Silvio Lopes de Oliveira" <silviol@.aaesys.com> wrote in message
news:s91wc.11$5Z4.18584@.news.uswest.net...
> Hello,
> I have a C++ / MFC app which uses CDatabase and CRecordset to connect to a
> ODBC data source for a SQL Server 7 database. The application worked
> properly with a mysql database, but after swapping it for SQL Server
> problems emerged. In particular, any calls to CRecordset::AddNew() and
> CRecordset::Edit() cause an exception to be thrown with the error message
> "Recordset is read-only". Stepping through the code for
CRecordset::Open(),
> I can see that it indeeds fail to open the recordset with
SQL_CONCUR_ROWVER
> and SQL_CONCUR_LOCK, finally succeeding with SQL_CONCUR_READ_ONLY. I need
to
> be able to write to the db!! What is going on here'
> I call CRecordset::Open() with no SQL string and no options (which
defaults
> to full access and not read-only).
> I am not a SQL Server expert, but I tried playing with the config a little
> bit:
> - The database is NOT set for read-only.
> - The database option "Restrict Access" is NOT selected.
> - I checkmarked all permissions (SELECT, INSERT, UPDATE, DELETE, DRI)
> for my app's user on all tables in the db.
> - My connection string uses the user 'sa', which is the login name for
> the user 'dbo'.
> In the ODBC data source config, I set authentication to SQL Server
> authentication, and accepted the default for all the other options.
> I haven't tried using a different class (say, CDaoRecordset) nor do I plan
> on doing that. I really need to get this to work with CRecordset. Any help
> is appreciated.
> Thanks,
> SL
>
This is a guess, but does the table you're working with have a primary key?
Many client tools will not update a table unless there is a primary key
present, as without a key there is no way to identify the rows you want to
update. Or if your recordset is returned by a stored procedure you might be
seeing an issue like this:
http://support.microsoft.com/defaul...kb;en-us;246636
However, I don't do much client-side coding, so I might be on completely the
wrong track here.
Simon|||Silvio Lopes de Oliveira (silviol@.aaesys.com) writes:
> I have a C++ / MFC app which uses CDatabase and CRecordset to connect to
> a ODBC data source for a SQL Server 7 database. The application worked
> properly with a mysql database, but after swapping it for SQL Server
> problems emerged. In particular, any calls to CRecordset::AddNew() and
> CRecordset::Edit() cause an exception to be thrown with the error
> message "Recordset is read-only". Stepping through the code for
> CRecordset::Open(), I can see that it indeeds fail to open the recordset
> with SQL_CONCUR_ROWVER and SQL_CONCUR_LOCK, finally succeeding with
> SQL_CONCUR_READ_ONLY. I need to be able to write to the db!! What is
> going on here'
It would have helped if you had posted the query.
I would guess that the reason is that the query is such that SQL
Server will not be able to determine which row to update. This
could be because, as Simon pointed out, of a missing primary key.
But it can also happen if you use GROUP BY, expressions or a whole
load number of things.
When this happens to people in their stored procedures, I recommend
them to not use WHERE CURRENT OF, but use a regular WHERE clause
instead. In your case, this would mean that you would submit a
regular UPDATE statement. But I guess that would be through a
different connection, because the one where you have the cursor
may be busy. And depending on which locking scheme you have on your
cursor, you may look yourself.
The remedy for this would be to get all data into the client, and
the close the cursor, and then you can update from the regular
connection. Unless the amount of data is huge, this is likely to
be more effecient. (And if the amount of data is huge, then using a
stored procedure with all the update logic and never get the data
to he client, would be a big winner.)
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||As a matter of fact, it was the lack of a primary key field that caused the
problem. When I imported the tables and data from my mysql database, the
primary key fields were not marked as such in the SQL Server tables.
Problem fixed. Thanks!
Silvio
"Simon Hayes" <sql@.hayes.ch> wrote in message
news:40c0aeb6$1_3@.news.bluewin.ch...
> "Silvio Lopes de Oliveira" <silviol@.aaesys.com> wrote in message
> news:s91wc.11$5Z4.18584@.news.uswest.net...
a[vbcol=seagreen]
message[vbcol=seagreen]
> CRecordset::Open(),
> SQL_CONCUR_ROWVER
need[vbcol=seagreen]
> to
> defaults
little[vbcol=seagreen]
DRI)[vbcol=seagreen]
for[vbcol=seagreen]
plan[vbcol=seagreen]
help[vbcol=seagreen]
> This is a guess, but does the table you're working with have a primary
key?
> Many client tools will not update a table unless there is a primary key
> present, as without a key there is no way to identify the rows you want to
> update. Or if your recordset is returned by a stored procedure you might
be
> seeing an issue like this:
> http://support.microsoft.com/defaul...kb;en-us;246636
> However, I don't do much client-side coding, so I might be on completely
the
> wrong track here.
> Simon
>
I have a C++ / MFC app which uses CDatabase and CRecordset to connect to a
ODBC data source for a SQL Server 7 database. The application worked
properly with a mysql database, but after swapping it for SQL Server
problems emerged. In particular, any calls to CRecordset::AddNew() and
CRecordset::Edit() cause an exception to be thrown with the error message
"Recordset is read-only". Stepping through the code for CRecordset::Open(),
I can see that it indeeds fail to open the recordset with SQL_CONCUR_ROWVER
and SQL_CONCUR_LOCK, finally succeeding with SQL_CONCUR_READ_ONLY. I need to
be able to write to the db!! What is going on here'
I call CRecordset::Open() with no SQL string and no options (which defaults
to full access and not read-only).
I am not a SQL Server expert, but I tried playing with the config a little
bit:
- The database is NOT set for read-only.
- The database option "Restrict Access" is NOT selected.
- I checkmarked all permissions (SELECT, INSERT, UPDATE, DELETE, DRI)
for my app's user on all tables in the db.
- My connection string uses the user 'sa', which is the login name for
the user 'dbo'.
In the ODBC data source config, I set authentication to SQL Server
authentication, and accepted the default for all the other options.
I haven't tried using a different class (say, CDaoRecordset) nor do I plan
on doing that. I really need to get this to work with CRecordset. Any help
is appreciated.
Thanks,
SL"Silvio Lopes de Oliveira" <silviol@.aaesys.com> wrote in message
news:s91wc.11$5Z4.18584@.news.uswest.net...
> Hello,
> I have a C++ / MFC app which uses CDatabase and CRecordset to connect to a
> ODBC data source for a SQL Server 7 database. The application worked
> properly with a mysql database, but after swapping it for SQL Server
> problems emerged. In particular, any calls to CRecordset::AddNew() and
> CRecordset::Edit() cause an exception to be thrown with the error message
> "Recordset is read-only". Stepping through the code for
CRecordset::Open(),
> I can see that it indeeds fail to open the recordset with
SQL_CONCUR_ROWVER
> and SQL_CONCUR_LOCK, finally succeeding with SQL_CONCUR_READ_ONLY. I need
to
> be able to write to the db!! What is going on here'
> I call CRecordset::Open() with no SQL string and no options (which
defaults
> to full access and not read-only).
> I am not a SQL Server expert, but I tried playing with the config a little
> bit:
> - The database is NOT set for read-only.
> - The database option "Restrict Access" is NOT selected.
> - I checkmarked all permissions (SELECT, INSERT, UPDATE, DELETE, DRI)
> for my app's user on all tables in the db.
> - My connection string uses the user 'sa', which is the login name for
> the user 'dbo'.
> In the ODBC data source config, I set authentication to SQL Server
> authentication, and accepted the default for all the other options.
> I haven't tried using a different class (say, CDaoRecordset) nor do I plan
> on doing that. I really need to get this to work with CRecordset. Any help
> is appreciated.
> Thanks,
> SL
>
This is a guess, but does the table you're working with have a primary key?
Many client tools will not update a table unless there is a primary key
present, as without a key there is no way to identify the rows you want to
update. Or if your recordset is returned by a stored procedure you might be
seeing an issue like this:
http://support.microsoft.com/defaul...kb;en-us;246636
However, I don't do much client-side coding, so I might be on completely the
wrong track here.
Simon|||Silvio Lopes de Oliveira (silviol@.aaesys.com) writes:
> I have a C++ / MFC app which uses CDatabase and CRecordset to connect to
> a ODBC data source for a SQL Server 7 database. The application worked
> properly with a mysql database, but after swapping it for SQL Server
> problems emerged. In particular, any calls to CRecordset::AddNew() and
> CRecordset::Edit() cause an exception to be thrown with the error
> message "Recordset is read-only". Stepping through the code for
> CRecordset::Open(), I can see that it indeeds fail to open the recordset
> with SQL_CONCUR_ROWVER and SQL_CONCUR_LOCK, finally succeeding with
> SQL_CONCUR_READ_ONLY. I need to be able to write to the db!! What is
> going on here'
It would have helped if you had posted the query.
I would guess that the reason is that the query is such that SQL
Server will not be able to determine which row to update. This
could be because, as Simon pointed out, of a missing primary key.
But it can also happen if you use GROUP BY, expressions or a whole
load number of things.
When this happens to people in their stored procedures, I recommend
them to not use WHERE CURRENT OF, but use a regular WHERE clause
instead. In your case, this would mean that you would submit a
regular UPDATE statement. But I guess that would be through a
different connection, because the one where you have the cursor
may be busy. And depending on which locking scheme you have on your
cursor, you may look yourself.
The remedy for this would be to get all data into the client, and
the close the cursor, and then you can update from the regular
connection. Unless the amount of data is huge, this is likely to
be more effecient. (And if the amount of data is huge, then using a
stored procedure with all the update logic and never get the data
to he client, would be a big winner.)
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||As a matter of fact, it was the lack of a primary key field that caused the
problem. When I imported the tables and data from my mysql database, the
primary key fields were not marked as such in the SQL Server tables.
Problem fixed. Thanks!
Silvio
"Simon Hayes" <sql@.hayes.ch> wrote in message
news:40c0aeb6$1_3@.news.bluewin.ch...
> "Silvio Lopes de Oliveira" <silviol@.aaesys.com> wrote in message
> news:s91wc.11$5Z4.18584@.news.uswest.net...
a[vbcol=seagreen]
message[vbcol=seagreen]
> CRecordset::Open(),
> SQL_CONCUR_ROWVER
need[vbcol=seagreen]
> to
> defaults
little[vbcol=seagreen]
DRI)[vbcol=seagreen]
for[vbcol=seagreen]
plan[vbcol=seagreen]
help[vbcol=seagreen]
> This is a guess, but does the table you're working with have a primary
key?
> Many client tools will not update a table unless there is a primary key
> present, as without a key there is no way to identify the rows you want to
> update. Or if your recordset is returned by a stored procedure you might
be
> seeing an issue like this:
> http://support.microsoft.com/defaul...kb;en-us;246636
> However, I don't do much client-side coding, so I might be on completely
the
> wrong track here.
> Simon
>
recordset open?
How do I programmatically tell if my recordset is open?Les Stockton wrote:
> How do I programmatically tell if my recordset is open?
By checking its State property (assuming you are talking about an ADO
recordset ... )
Bob Barrows
--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.|||For ADO, check the .status method, for instance:
If Not ladoRs.State = adStateClosed Then
' -- add code --'
Anith
> How do I programmatically tell if my recordset is open?
By checking its State property (assuming you are talking about an ADO
recordset ... )
Bob Barrows
--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.|||For ADO, check the .status method, for instance:
If Not ladoRs.State = adStateClosed Then
' -- add code --'
Anith
Recordset not updatable
I use MS Access Project to connect to MS-SQL 2000. Even though I login as owner of the database, I can't update my table with message 'Recordset is not updatable'
I have no problem updating through Enterprise Manager. Someone out there. Please help meAccess needs unique index to update a table. When linking SQL tables Access may ask you to select index columns. Select the number of columns needed for an "unique index"
I have no problem updating through Enterprise Manager. Someone out there. Please help meAccess needs unique index to update a table. When linking SQL tables Access may ask you to select index columns. Select the number of columns needed for an "unique index"
Recordset not updatable
I use MS Access Project to connect to MS-SQL 2000. Even though I login as owner of the database, I can't update my table with message 'Recordset is not updatable'.
I have no problem updating through Enterprise Manager. Someone out there. Please help me
Access needs unique index to update a table. When linking SQL tables Access may ask you to select index columns. Select the number of columns needed for an "unique index"
I have no problem updating through Enterprise Manager. Someone out there. Please help me
Access needs unique index to update a table. When linking SQL tables Access may ask you to select index columns. Select the number of columns needed for an "unique index"
Recordset not updatable
I use MS Access Project to connect to MS-SQL 2000. Even though I login as ow
ner of the database, I can't update my table with message 'Recordset is not
updatable'.
I have no problem updating through Enterprise Manager. Someone out there. Pl
ease help meAccess needs unique index to update a table. When linking SQL tables Access
may ask you to select index columns. Select the number of columns needed for
an "unique index"
ner of the database, I can't update my table with message 'Recordset is not
updatable'.
I have no problem updating through Enterprise Manager. Someone out there. Pl
ease help meAccess needs unique index to update a table. When linking SQL tables Access
may ask you to select index columns. Select the number of columns needed for
an "unique index"
Recordset Modification and Copy
Please Help.
I have written a custom DTS task in an ActiveX Script. My script creates three recordsets from three existing tables in an SQL Server 2k DB by copying all the data in these tables.
My script then modifies the data in these recordsets including dropping several records, creates a new table and inserts the modified data into the new table.
Unfortunately, I need to keep my original data unmodified...and currently my code changes my original data when it modifies the record sets.
Does anyone have a method for modifying a recordset without modifying the source data? I am writing my ActiveX Script through the VB scripting language.
From what I have read on the internet some people suggest creating a new recordset, copying the data from the original recordset to the new recordset, and then modify and insert the new recordset. This is supposed to keep my source data unchanged. Unfortunately, I have not been successful with this method mainly because I am having trouble creating a new recordset and copying my original recordset data into this new object.
Thank you in advance for any help or input you might have!!!
-TRocheI assume you are using ADO? Disconnect your recordset by setting your ActiveConnection property to nothing.|||I don't think this will work. You see I have to manipulate the data through several DO Until loops. The constraint on my DO loop is
"DO UNTIL rs.EOF"
Before I set the recordset ActiveConnection to nothing It tells me I have to first close the recordset. Unfortunately, I can not run the syntax of the above loop if the recrordset is closed, and if I open the recordset with a new connection it again changes my source data.
Does anyone have any additional insight?
Thanks!
-TRoche|||create a function READ UNCOMMITTED transaction isolation level (you can do it with nolock optimizer hint) and do your DO UNTIL against the rs that points to it.|||Wow...I am just a beginner at this stuff...I will look into it, but do you have a sample code for this by chance?
thanks,
TRoche|||create function dbo.fn_select_star_from_your_table (
@.parm1 <data type>,
@.parm2 <data type>,
@.parm3 <data type> ) returns table
as return (
select * from your_table (nolock)
where field1 = @.parm1
and field2 = @.parm2
and @.field3 = parm3)
go|||Hmmmmm....ok to start I am writing a custom DTS Task, using an ActiveX Script in the DTS Designer, coding in VBScript
Well I am having a few problems with the previous comments. First off I can't get the code to drop the function ms_sql_dba advised. Here is my DROP code for a function called FUNSTUFF:
dim Dropx
set Dropx = CreateObject("ADODB.Command")
Dropx = "Drop Function [dbo].[FUNSTUFF]"
connection.execute Dropx
I am only trying to drop the function because if I run the code multiple times it errors saying the the object "FunStuff" already exists in the database...and I looked...and it does...dang Does anyone see something wrong with the above DROP code?
Also, ms_sql_dba, I am not sure I understand what your function does. Here is how I thought to use your advice.
Write a function called ADVICE as follows:
dim ADVICE
set ADVICE = CreateObject("ADODB.Command")
ADVICE = "CREATE FUNCTION dbo.FUNSTUFF (@.time float, @.Position float ) RETURNS Table AS RETURN (select * from GPSy (nolock) where ty = time and GPS_y = Position)"
then:
Open.recordset ADVICE, Connection, adOpenKeyset
Then write my do loop:
DO UNTIL recordset.EOF
blah, blah, blah
Loop
Unfortunately, I run into all kinds of errors like saying the operation can not be performed if the recordset is closed. But if I write an open command like:
open.recordset.connection
I get the error that the function FUNSTUFF alreadyexists in the database.
Hmmmmmmmm...
Does anyone have any advice? Do I have to write the Function code before every loop or a drop code after every loop...or...hmmm...I am just confused about how the function works.
A million thanks to anyone who has any input!!
-TRoche
I have written a custom DTS task in an ActiveX Script. My script creates three recordsets from three existing tables in an SQL Server 2k DB by copying all the data in these tables.
My script then modifies the data in these recordsets including dropping several records, creates a new table and inserts the modified data into the new table.
Unfortunately, I need to keep my original data unmodified...and currently my code changes my original data when it modifies the record sets.
Does anyone have a method for modifying a recordset without modifying the source data? I am writing my ActiveX Script through the VB scripting language.
From what I have read on the internet some people suggest creating a new recordset, copying the data from the original recordset to the new recordset, and then modify and insert the new recordset. This is supposed to keep my source data unchanged. Unfortunately, I have not been successful with this method mainly because I am having trouble creating a new recordset and copying my original recordset data into this new object.
Thank you in advance for any help or input you might have!!!
-TRocheI assume you are using ADO? Disconnect your recordset by setting your ActiveConnection property to nothing.|||I don't think this will work. You see I have to manipulate the data through several DO Until loops. The constraint on my DO loop is
"DO UNTIL rs.EOF"
Before I set the recordset ActiveConnection to nothing It tells me I have to first close the recordset. Unfortunately, I can not run the syntax of the above loop if the recrordset is closed, and if I open the recordset with a new connection it again changes my source data.
Does anyone have any additional insight?
Thanks!
-TRoche|||create a function READ UNCOMMITTED transaction isolation level (you can do it with nolock optimizer hint) and do your DO UNTIL against the rs that points to it.|||Wow...I am just a beginner at this stuff...I will look into it, but do you have a sample code for this by chance?
thanks,
TRoche|||create function dbo.fn_select_star_from_your_table (
@.parm1 <data type>,
@.parm2 <data type>,
@.parm3 <data type> ) returns table
as return (
select * from your_table (nolock)
where field1 = @.parm1
and field2 = @.parm2
and @.field3 = parm3)
go|||Hmmmmm....ok to start I am writing a custom DTS Task, using an ActiveX Script in the DTS Designer, coding in VBScript
Well I am having a few problems with the previous comments. First off I can't get the code to drop the function ms_sql_dba advised. Here is my DROP code for a function called FUNSTUFF:
dim Dropx
set Dropx = CreateObject("ADODB.Command")
Dropx = "Drop Function [dbo].[FUNSTUFF]"
connection.execute Dropx
I am only trying to drop the function because if I run the code multiple times it errors saying the the object "FunStuff" already exists in the database...and I looked...and it does...dang Does anyone see something wrong with the above DROP code?
Also, ms_sql_dba, I am not sure I understand what your function does. Here is how I thought to use your advice.
Write a function called ADVICE as follows:
dim ADVICE
set ADVICE = CreateObject("ADODB.Command")
ADVICE = "CREATE FUNCTION dbo.FUNSTUFF (@.time float, @.Position float ) RETURNS Table AS RETURN (select * from GPSy (nolock) where ty = time and GPS_y = Position)"
then:
Open.recordset ADVICE, Connection, adOpenKeyset
Then write my do loop:
DO UNTIL recordset.EOF
blah, blah, blah
Loop
Unfortunately, I run into all kinds of errors like saying the operation can not be performed if the recordset is closed. But if I write an open command like:
open.recordset.connection
I get the error that the function FUNSTUFF alreadyexists in the database.
Hmmmmmmmm...
Does anyone have any advice? Do I have to write the Function code before every loop or a drop code after every loop...or...hmmm...I am just confused about how the function works.
A million thanks to anyone who has any input!!
-TRoche
Recordset limit per connection?
Hi all,
I have recently moved over from SQL Server 2000 to 2005 and am now having an issue with my application with what appears to be, the number of recordsets that I can open/close on a single connection.
Here is a snippet of what I'm doing, in VB 2005 using ADO... (assuming the connection is already open and working)
Dim RS1 As RecordSet.
DIM RS2 As RecordSet
RS1.Open("SELECT...")
Do While Not RS1.EOF
count = count + 1
Console.Writeline(count)
RS2.Open("SELECT...")
..some processing...
RS2.Close
Loop
RS2 = Nothing
RS1.Close
RS1 = Nothing
Now as I said this all works fine when I connect to an SQL 2000 server but on SQL 2005 it bombs out when 'count' is approximately 1940 with an exception saying that the login failed. If I have Server Management Studio open, that connection will then freeze and throw up an error about how only one usage of each socket address is normally permitted - I think thats more a red herring though.
Any ideas? I've been through all the server settings and can not seem to find anything about recordset limits or timeouts. The only way I've been able to get around this problem at present is to open a new connection object for each iteration to be used by RS2.
Thanks everyone. Any pointers would be much appreciated.
For each RecordSet.open there can be a TCP socket open for communicating with the remote server and each socket will consume system resource and can only be cleaned up after a configurable timeout. If you open too freqently, your system resource for sockets can be exhausted and so the error message.
I recommend you to turn on connection pooling on your client.
-HTH
|||Hi, and thank you for your response.
I am opening one connection, via a connection object and connection string that is then used by the rest of the application and passed as a parameter to the RecordSet.Open call so I thought it would only be using the one connection and not using any more ports?
As that is the case I didn't think another TCP port would be opened for each RecordSet. When viewing the active connections in SQL Server Management Studio there is only ever one open too..
|||What if you set RS2 to Nothing inside the loop, right after close. Could you try and see if this changes the behavior?|||Another way to confirm whether it is too many socket issue, you can run the following command in a console,
"netstat -ano"
If you see lots timewait, then using pool would be good.
|||Just FYI. Connection pool is on by default it ADO.|||Alas moving the RS2 = Nothing inside the loop doesn't help and checking the TCP/IP ports, only one connection is being open. It's very strange. I am getting around it at present by opening a new connection for each iteration. Not ideal at all.
Thanks for all your input though. I *will* get to the bottom of this!
|||Just in case connection pooling might have been turned off using the registry, you might want to refactor your code slightly to open a connection object before the while loop then use that in the recordset open inside the while loop so for example,
Dim RS1 As RecordSet.
DIM RS2 As RecordSet
Dim cn as ADODB.Connection
set cn = new ADODB.Connection
cn.open <myconnectionstring> 'where <myconnectionstring> is your connection string
RS1.Open("SELECT...", cn)
Do While Not RS1.EOF
count = count + 1
Console.Writeline(count)
RS2.Open("SELECT...", cn)
..some processing...
RS2.Close
Loop
RS2 = Nothing
RS1.Close
RS1 = Nothing
|||Yeah that's what I am doing so I can't understand why it would fail after a certain time.. unless their is network trouble and the connection is getting dropped? I think a solution might be to try the failed login error and re-login.Recordset limit per connection?
Hi all,
I have recently moved over from SQL Server 2000 to 2005 and am now having an issue with my application with what appears to be, the number of recordsets that I can open/close on a single connection.
Here is a snippet of what I'm doing, in VB 2005 using ADO... (assuming the connection is already open and working)
Dim RS1 As RecordSet.
DIM RS2 As RecordSet
RS1.Open("SELECT...")
Do While Not RS1.EOF
count = count + 1
Console.Writeline(count)
RS2.Open("SELECT...")
..some processing...
RS2.Close
Loop
RS2 = Nothing
RS1.Close
RS1 = Nothing
Now as I said this all works fine when I connect to an SQL 2000 server but on SQL 2005 it bombs out when 'count' is approximately 1940 with an exception saying that the login failed. If I have Server Management Studio open, that connection will then freeze and throw up an error about how only one usage of each socket address is normally permitted - I think thats more a red herring though.
Any ideas? I've been through all the server settings and can not seem to find anything about recordset limits or timeouts. The only way I've been able to get around this problem at present is to open a new connection object for each iteration to be used by RS2.
Thanks everyone. Any pointers would be much appreciated.
For each RecordSet.open there can be a TCP socket open for communicating with the remote server and each socket will consume system resource and can only be cleaned up after a configurable timeout. If you open too freqently, your system resource for sockets can be exhausted and so the error message.
I recommend you to turn on connection pooling on your client.
-HTH
|||Hi, and thank you for your response.
I am opening one connection, via a connection object and connection string that is then used by the rest of the application and passed as a parameter to the RecordSet.Open call so I thought it would only be using the one connection and not using any more ports?
As that is the case I didn't think another TCP port would be opened for each RecordSet. When viewing the active connections in SQL Server Management Studio there is only ever one open too..
|||What if you set RS2 to Nothing inside the loop, right after close. Could you try and see if this changes the behavior?|||Another way to confirm whether it is too many socket issue, you can run the following command in a console,
"netstat -ano"
If you see lots timewait, then using pool would be good.
|||Just FYI. Connection pool is on by default it ADO.|||Alas moving the RS2 = Nothing inside the loop doesn't help and checking the TCP/IP ports, only one connection is being open. It's very strange. I am getting around it at present by opening a new connection for each iteration. Not ideal at all.
Thanks for all your input though. I *will* get to the bottom of this!
|||Just in case connection pooling might have been turned off using the registry, you might want to refactor your code slightly to open a connection object before the while loop then use that in the recordset open inside the while loop so for example,
Dim RS1 As RecordSet.
DIM RS2 As RecordSet
Dim cn as ADODB.Connection
set cn = new ADODB.Connection
cn.open <myconnectionstring> 'where <myconnectionstring> is your connection string
RS1.Open("SELECT...", cn)
Do While Not RS1.EOF
count = count + 1
Console.Writeline(count)
RS2.Open("SELECT...", cn)
..some processing...
RS2.Close
Loop
RS2 = Nothing
RS1.Close
RS1 = Nothing
|||Yeah that's what I am doing so I can't understand why it would fail after a certain time.. unless their is network trouble and the connection is getting dropped? I think a solution might be to try the failed login error and re-login.Recordset is read-only
Hey,
I'm using CRecordSet to add new rows to some table.
The Sequence of operations is as following:
m_recSet->Open();
m_recSet->AddNew();
m_recSet->Update();
In the AddNew() function i'm getting an Exception Saying "Recordset is read-only".
I saw in previous posts that this problem is caused when there is no Primary Key, but, my table
does not have a Primary Key and i don't want to set one.
How can i add rows to a table that doesn't have Primary Key ?
Thank
Shahar
On http://www.thescripts.com/forum/thread82452.html I see the same problem being discussed. Is there a reason why you don't want a primary key on the table?
Thanks
Waseem
|||The reason is as simple as there is no single primary key, i don't want to disable duplicate rows, and even if i'll compromise on that
i'll need to create Primary Key that will include 5 Fields, which as i see it won't be very efficient.
|||Perhaps you 'could' add an IDENTITY column to the table.
Waseem Basheer - MSFT wrote:
On http://www.thescripts.com/forum/thread82452.html I see the same problem being discussed. Is there a reason why you don't want a primary key on the table?
Thanks
Waseem
RecordSet into a DataFlow Task
In the control flow I have an "Execute SQL Task" that executes a stored procedure. The stored procedure returns a result set of about 2000 rows of data into a package variable that has been typed as Object to contain the data.
What I have not been able to figure out is how to access the rows of data (in the package variable) from within a data flow task. There does not seem to be a data flow source task to perform that operation.
What am I missing that would make this easy?
...cordell...
if you need to have control on every row of the result set you may be on the need of using a data flow task(source, transforms and destination components). Would you give an example of what you are trying to accomplish after getting the 2000 rows?
|||In the data flow task...I need to parse through the data to generate 3 unique result sets of data. One of the result sets will go to a text file, the 2nd result set go back into a staging table, and the 3rd result set goes to a excel spreadsheet.
|||Cordell Swannack wrote:
In the control flow I have an "Execute SQL Task" that executes a stored procedure. The stored procedure returns a result set of about 2000 rows of data into a package variable that has been typed as Object to contain the data.
What I have not been able to figure out is how to access the rows of data (in the package variable) from within a data flow task. There does not seem to be a data flow source task to perform that operation.
What am I missing that would make this easy?
...cordell...
This shows you how to do it:
Recordsets instead of raw files
(http://blogs.conchango.com/jamiethomson/archive/2006/01/04/2540.aspx)
I'm not sure if this is relevant though. If you are using a SQL statement to get the data why not just put the SQL statement into an OLE DB Source adapter? Why bother with the rigmarole of a variable and then having to write code?
-Jamie
|||
In the data flow task...I need to parse through the data to generate 3 unique result sets of data. One of the result sets will go to a text file, the 2nd result set go back into a staging table, and the 3rd result set goes to a excel spreadsheet.
I would wager you can accomplish all of this with a combination of the conditional split, multicast and/or derived column components.
Even if you can't, you would be better off employing a script component to parse through the data - there is still no need to put it into a variable first.
-Jamie
|||
Cordell Swannack wrote: In the control flow I have an "Execute SQL Task" that executes a stored procedure. The stored procedure returns a result set of about 2000 rows of data into a package variable that has been typed as Object to contain the data.
What I have not been able to figure out is how to access the rows of data (in the package variable) from within a data flow task. There does not seem to be a data flow source task to perform that operation.
What am I missing that would make this easy?
...cordell...
This shows you how to do it:
Recordsets instead of raw files
(http://blogs.conchango.com/jamiethomson/archive/2006/01/04/2540.aspx)
I'm not sure if this is relevant though. If you are using a SQL statement to get the data why not just put the SQL statement into an OLE DB Source adapter? Why bother with the rigmarole of a variable and then having to write code?
-Jamie
I was going to suggest something similar. In general you would use data flows to perform row by row operations; like transformations, splits, sorts, etc. However the control flow may be better place to row set based operations; like an update, select into, etc.
In you particular case, use a dataflow task and then drop a OLE DB source component, any required transform and the detination component.
|||Helpful suggestions....
While you can use the OLE DB Source adapater inside of the Data Flow task...I am executing a stored procedure that returns back a large result set. When you click on the 'Preview' button...the data and all of the column names are returned. (Just proving that everything works.)
However I haven't figure out how to set the column names typically set by OLE DB Source component. Right now they are blank. Usually at design time when you reference a table or use simple select statement the OLE DB Source task can map the columns specified by the table name or select statement to create a list of columns that are used for the output and thus by other components in the data flow task.
But when executing a stored procedure...the column names are not returned until completion (i.e. runtime) of the stored procedure. This is the problem.
...cordell...
|||Helpful suggestions....
While you can use the OLE DB Source adapater inside of the Data Flow task...I am executing a stored procedure that returns back a large result set. When you click on the 'Preview' button...the data and all of the column names are returned. (Just proving that everything works.)
However I haven't figure out how to set the column names typically set by OLE DB Source component. Right now they are blank. Usually at design time when you reference a table or use simple select statement the OLE DB Source task can map the columns specified by the table name or select statement to create a list of columns that are used for the output and thus by other components in the data flow task.
But when executing a stored procedure...the column names are not returned until completion (i.e. runtime) of the stored procedure. This is the problem.
...cordell...
You're absolutely right. This IS a problem with sprocs. And I agree that this sounds like a good justification for using the Execute SQL Task route. However, I suggest another workaround here:
Using stored procedures inside an OLE DB Source component
(http://blogs.conchango.com/jamiethomson/archive/2006/12/20/SSIS_3A00_-Using-stored-procedures-inside-an-OLE-DB-Source-component.aspx)
that you may wish to employ that will enable you to use the OLE DB Source component.
-Jamie
|||
I found your blog entry on the subject just as I was receiving your email.
The reason why a UDF doesn't work for my solution is the amount of transformation processing that must be performed upon the data. Basically I have 10,000's of data records that are processed and then randomized into a laboratory trial groups which are then distributed out to various computer systems for analysis.
I ended up changing around the stored procedure to create a table in the database...and then once the SSIS work has completed...I drop the table. A less than perfect solution...but it gets the job done and it is time to move on.
Thank you Jamie for your time to answer my questions and your patience.
...cordell...
Recordset insert into a table in a SP
Hi,
I need to insert a recordset into a single columns in a table in a Stored procedure.
I have dine the following:
Insert into Staging_Table Values(Recordset)
I get an error that only constants are allowed.
How can I insert the recordset values into that table?RE:
Hi,
I need to insert a recordset into a single columns in a table in a Stored procedure. I have dine the following:
Insert into Staging_Table Values(Recordset)
I get an error that only constants are allowed.
Q1 How can I insert the recordset values into that table?
A1 Insert values inserts (explicit) values. (Try posting the applicable ddl and some sample statements if this is what you are doing.)
For Example:
Use TempDB
Go
CREATE TABLE TestTable ( column_1 varchar(32))
Go
INSERT TestTable VALUES ('Row #1 Value')
INSERT TestTable VALUES ('Row #2 Value')
SELECT * From TestTable|||How do you want to store the recordset in the column and how would you extract information from it once is has been stored in the column ? Please post your existing code.|||Originally posted by rnealejr
How do you want to store the recordset in the column and how would you extract information from it once is has been stored in the column ? Please post your existing code.
Hi,
My existing code is:
if @.col2 is null
Begin
set @.SQL = 'create table LanTable ( ' + @.col1 + ' nvarchar(60))'
exec (@.SQL)
Insert into LanTable SELECT Data1= substring ( Record_Line , @.Pos1,@.Len1) FROM Staging_Table
End
else if @.col3 is null
Begin
set @.SQL = 'create table LanTable ( ' + @.col1 + ' nvarchar(60),' + @.col2 + ' nvarchar(60))'
exec (@.SQL)
Insert into LanTable SELECT Data1= substring ( Record_Line , @.Pos1,@.Len1), Data2= substring ( Record_Line , @.Pos2,@.Len2) FROM Staging_Table
End
@.Pos1,@.Len1,@.col2... all are input parameters of the SP.
Staging_Table consists of one column (Record_Line) that contains the data (ex. 03 Bank 2222 -etc)
Data1 and Data2 are recordsets that each contain their values from the Staging_Table and i want it to be inserted into LanTable.
Can it be done?
Thanks|||Originally posted by garfild
Hi,
My existing code is:
if @.col2 is null
Begin
set @.SQL = 'create table LanTable ( ' + @.col1 + ' nvarchar(60))'
exec (@.SQL)
Insert into LanTable SELECT Data1= substring ( Record_Line , @.Pos1,@.Len1) FROM Staging_Table
End
else if @.col3 is null
Begin
set @.SQL = 'create table LanTable ( ' + @.col1 + ' nvarchar(60),' + @.col2 + ' nvarchar(60))'
exec (@.SQL)
Insert into LanTable SELECT Data1= substring ( Record_Line , @.Pos1,@.Len1), Data2= substring ( Record_Line , @.Pos2,@.Len2) FROM Staging_Table
End
@.Pos1,@.Len1,@.col2... all are input parameters of the SP.
Staging_Table consists of one column (Record_Line) that contains the data (ex. 03 Bank 2222 -etc)
Data1 and Data2 are recordsets that each contain their values from the Staging_Table and i want it to be inserted into LanTable.
Can it be done?
Thanks
Hi again,
Try to use this SP and tell me what i did wrong:
CREATE PROCEDURE Lan_BuildTable
@.col1 nvarchar(60), @.Pos1 int=null, @.Len1 int=null,@.col2 nvarchar(60) = null,@.Pos2 int = null, @.Len2 int=null
AS
declare @.SQL as varchar(3000)
if @.col2 is null
Begin
set @.SQL = 'create table LanTable ( ' + @.col1 + ' nvarchar(60))'
exec (@.SQL)
Insert into LanTable SELECT Data1= substring ( Record_Line , @.Pos1,@.Len1) FROM Staging_Table
End
else if @.col3 is null
Begin
set @.SQL = 'create table LanTable ( ' + @.col1 + ' nvarchar(60),' + @.col2 + ' nvarchar(60))'
exec (@.SQL)
Insert into LanTable SELECT Data1= substring ( Record_Line , @.Pos1,@.Len1), Data2= substring ( Record_Line , @.Pos2,@.Len2) FROM Staging_Table
End
In the VB code I used:
exec Lan_BuildTable OpCode,1,2,Product,4,5
Thanks
Yossi
I need to insert a recordset into a single columns in a table in a Stored procedure.
I have dine the following:
Insert into Staging_Table Values(Recordset)
I get an error that only constants are allowed.
How can I insert the recordset values into that table?RE:
Hi,
I need to insert a recordset into a single columns in a table in a Stored procedure. I have dine the following:
Insert into Staging_Table Values(Recordset)
I get an error that only constants are allowed.
Q1 How can I insert the recordset values into that table?
A1 Insert values inserts (explicit) values. (Try posting the applicable ddl and some sample statements if this is what you are doing.)
For Example:
Use TempDB
Go
CREATE TABLE TestTable ( column_1 varchar(32))
Go
INSERT TestTable VALUES ('Row #1 Value')
INSERT TestTable VALUES ('Row #2 Value')
SELECT * From TestTable|||How do you want to store the recordset in the column and how would you extract information from it once is has been stored in the column ? Please post your existing code.|||Originally posted by rnealejr
How do you want to store the recordset in the column and how would you extract information from it once is has been stored in the column ? Please post your existing code.
Hi,
My existing code is:
if @.col2 is null
Begin
set @.SQL = 'create table LanTable ( ' + @.col1 + ' nvarchar(60))'
exec (@.SQL)
Insert into LanTable SELECT Data1= substring ( Record_Line , @.Pos1,@.Len1) FROM Staging_Table
End
else if @.col3 is null
Begin
set @.SQL = 'create table LanTable ( ' + @.col1 + ' nvarchar(60),' + @.col2 + ' nvarchar(60))'
exec (@.SQL)
Insert into LanTable SELECT Data1= substring ( Record_Line , @.Pos1,@.Len1), Data2= substring ( Record_Line , @.Pos2,@.Len2) FROM Staging_Table
End
@.Pos1,@.Len1,@.col2... all are input parameters of the SP.
Staging_Table consists of one column (Record_Line) that contains the data (ex. 03 Bank 2222 -etc)
Data1 and Data2 are recordsets that each contain their values from the Staging_Table and i want it to be inserted into LanTable.
Can it be done?
Thanks|||Originally posted by garfild
Hi,
My existing code is:
if @.col2 is null
Begin
set @.SQL = 'create table LanTable ( ' + @.col1 + ' nvarchar(60))'
exec (@.SQL)
Insert into LanTable SELECT Data1= substring ( Record_Line , @.Pos1,@.Len1) FROM Staging_Table
End
else if @.col3 is null
Begin
set @.SQL = 'create table LanTable ( ' + @.col1 + ' nvarchar(60),' + @.col2 + ' nvarchar(60))'
exec (@.SQL)
Insert into LanTable SELECT Data1= substring ( Record_Line , @.Pos1,@.Len1), Data2= substring ( Record_Line , @.Pos2,@.Len2) FROM Staging_Table
End
@.Pos1,@.Len1,@.col2... all are input parameters of the SP.
Staging_Table consists of one column (Record_Line) that contains the data (ex. 03 Bank 2222 -etc)
Data1 and Data2 are recordsets that each contain their values from the Staging_Table and i want it to be inserted into LanTable.
Can it be done?
Thanks
Hi again,
Try to use this SP and tell me what i did wrong:
CREATE PROCEDURE Lan_BuildTable
@.col1 nvarchar(60), @.Pos1 int=null, @.Len1 int=null,@.col2 nvarchar(60) = null,@.Pos2 int = null, @.Len2 int=null
AS
declare @.SQL as varchar(3000)
if @.col2 is null
Begin
set @.SQL = 'create table LanTable ( ' + @.col1 + ' nvarchar(60))'
exec (@.SQL)
Insert into LanTable SELECT Data1= substring ( Record_Line , @.Pos1,@.Len1) FROM Staging_Table
End
else if @.col3 is null
Begin
set @.SQL = 'create table LanTable ( ' + @.col1 + ' nvarchar(60),' + @.col2 + ' nvarchar(60))'
exec (@.SQL)
Insert into LanTable SELECT Data1= substring ( Record_Line , @.Pos1,@.Len1), Data2= substring ( Record_Line , @.Pos2,@.Len2) FROM Staging_Table
End
In the VB code I used:
exec Lan_BuildTable OpCode,1,2,Product,4,5
Thanks
Yossi
Subscribe to:
Posts (Atom)