RecordSets

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"

Recordet Problem

I'm creating a Visual Basic program linked to an SQL server DB. In one of my tables I have a field for phone number which is an nvarchar datatype that is stored in the following format (example: 617-782-6415).

I have a form where I want to view the contents of the following recordset:

SELECT d .ACCOUNT_CODE, d .DURATION, d .[DATE], d .[HOUR], d .NUMBER_DIALED, p.ACCOUNT_CODE AS Expr1, p.LAST_NAME, p.FIRST_NAME,
p.SUMMARY_GROUP, p.DIRECT_DIAL, p.TITLE, p.LOCATION, p.DIVISION, p.DEPARTMENT, p.DIRECTOR, p.OLD_CARD_NUM, p.REISSUE, p.CLASS,
p.PHONE
FROM DETAIL d INNER JOIN
PHONE_EMP_MAST p ON d .ACCOUNT_CODE = p.ACCOUNT_CODE
WHERE number_dialed = " & astring & "

The value for astring is pulled from a combobox that stores all the values for number_dialed. However when i run this program i get the error message

"syntax error converting the nvarchar value '401-781-7078' to a column of datatype int"

I dont need to convert it to a column of datatype int so I dont understand this error... can anyone help me?The only thing that can be forcing a convertion is

WHERE number_dialed = " & astring & "

What is this tryin to do?
Take one value frm teh combo box?
I assume as you have " as the string delimitter then you are trying to form the select in VB.
You would be better to create an SP and pass the parameter.
For your string here try

WHERE number_dialed = '" & astring & "'"

i.e. making astring into a string in the query.|||Yeah, I'm trying to form the select in VB because I'm more familiar with that way... I'd prefer to do it in VB if at all possible... Why is it converting it?|||WHERE number_dialed = " & astring & "

will give something like

WHERE number_dialed = 617-782-6415

it will evaluate 617-782-6415 giving a negative number then try to convert number_dialed to an integer for the compare.

you need
WHERE number_dialed = '617-782-6415'
to do a string compare
hence the quotes in my previous post.

Record size too big for table

Getting the subject error after creating fields in a SQL table. Total
fields is 810. I was told the total character count for all fields combined
in a SQL table couldn't exceed about 8000, but I cannot find any reference
to this.
See "Maximum Capacity Specifications" in BOL (Bytes per row).
http://msdn.microsoft.com/library/de...ar_ts_8dbn.asp
AMB
"Absolutely" wrote:

> Getting the subject error after creating fields in a SQL table. Total
> fields is 810. I was told the total character count for all fields combined
> in a SQL table couldn't exceed about 8000, but I cannot find any reference
> to this.
>
>
|||8060 bytes is the total size of a row in a sql server 2000 table
http://sqlservercode.blogspot.com/
"Absolutely" wrote:

> Getting the subject error after creating fields in a SQL table. Total
> fields is 810. I was told the total character count for all fields combined
> in a SQL table couldn't exceed about 8000, but I cannot find any reference
> to this.
>
>

Record size too big for table

Getting the subject error after creating fields in a SQL table. Total
fields is 810. I was told the total character count for all fields combined
in a SQL table couldn't exceed about 8000, but I cannot find any reference
to this.See "Maximum Capacity Specifications" in BOL (Bytes per row).
http://msdn.microsoft.com/library/d...br />
8dbn.asp
AMB
"Absolutely" wrote:

> Getting the subject error after creating fields in a SQL table. Total
> fields is 810. I was told the total character count for all fields combin
ed
> in a SQL table couldn't exceed about 8000, but I cannot find any reference
> to this.
>
>|||8060 bytes is the total size of a row in a sql server 2000 table
http://sqlservercode.blogspot.com/
"Absolutely" wrote:

> Getting the subject error after creating fields in a SQL table. Total
> fields is 810. I was told the total character count for all fields combin
ed
> in a SQL table couldn't exceed about 8000, but I cannot find any reference
> to this.
>
>

Record size too big for table

Getting the subject error after creating fields in a SQL table. Total
fields is 810. I was told the total character count for all fields combined
in a SQL table couldn't exceed about 8000, but I cannot find any reference
to this.See "Maximum Capacity Specifications" in BOL (Bytes per row).
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/architec/8_ar_ts_8dbn.asp
AMB
"Absolutely" wrote:
> Getting the subject error after creating fields in a SQL table. Total
> fields is 810. I was told the total character count for all fields combined
> in a SQL table couldn't exceed about 8000, but I cannot find any reference
> to this.
>
>|||8060 bytes is the total size of a row in a sql server 2000 table
http://sqlservercode.blogspot.com/
"Absolutely" wrote:
> Getting the subject error after creating fields in a SQL table. Total
> fields is 810. I was told the total character count for all fields combined
> in a SQL table couldn't exceed about 8000, but I cannot find any reference
> to this.
>
>

Record size more than 8060B

Hi,

In MS SQL Server, while creating the table, I am getting a warning
message saying like "maximum row size can exceed allowed maximum size
of 8060 bytes".

Is there any way in SQL Server, to increase this allowed maximum row
size?

The setting like "set ANSI_WARNINGS OFF" is not suitable in my case. I
need creation of table with around 11000 bytes in the record (Summation
of precision of all the columns). And more over, I do not want to loose
any data (truncation) in inert operation to keep on the whole record
size to 8060B.

Thanks a lot in advance.
Ramakrishna.Hi,

SQL Server is limited to 8060 bytes of data stored in row. Depending on
your SQL Server version some data types can be stored in row or outta
row, but for SQL Server if you want to have more than the mentioned
limit you either have to do a 1:1 relation with pulling out some data
to another table, or use data types (But I wouldnt prefer that as a
solution) that are not stored in row, than stored as pointers (like
text/ntext)

HTH, jens Suessmeyer.

--
http://www.sqlserver2005.de
--|||Am 6 Jun 2006 10:46:41 -0700 schrieb RamaKrishna Narla:

> In MS SQL Server, while creating the table, I am getting a warning
> message saying like "maximum row size can exceed allowed maximum size
> of 8060 bytes".
> Is there any way in SQL Server, to increase this allowed maximum row
> size?

The perfect solution would be changing to SQL Server 2005 (or SQLExpress).
There is a new datatype called VARCHAR(MAX) which can grow up till 2 Gb!
I would avoid TEXT for strings in any case. Read BOL to see which functions
do not work with TEXT, how to change TEXT (you need pointers!), ...

bye, Helmut|||On 6 Jun 2006 10:46:41 -0700, RamaKrishna Narla wrote:

>Hi,
>In MS SQL Server, while creating the table, I am getting a warning
>message saying like "maximum row size can exceed allowed maximum size
>of 8060 bytes".
>Is there any way in SQL Server, to increase this allowed maximum row
>size?

Hi Ramakrishna,

Helmut's suggestion of upgrading to SQL Server 2005 is spot on. Not only
because of the new VARCHAR(max) datatype, but also because SQL Server
2005 will automatically store part of the data on overflow pages if your
row size exceeds the 8060 byte limit.

If you're stuck on SQL Server 2000, you'll have to work around the
limitation. For instance by creating a second table that holds some of
the data. E.g.

CREATE TABLE Reports
(ReportID int NOT NULL,
FirstBigColumn varchar(4000) NOT NULL,
SecondBigColumn varchar(4000) NOT NULL,
PRIMARY KEY (ReportID)
)
CREATE TABLE ReportExtensions
(ReportID int NOT NULL,
ThirdBigColumn varchar(4000) NOT NULL,
FourthBigColumn varchar(4000) NOT NULL,
PRIMARY KEY (ReportID),
FOREIGN KEY (ReportID) REFERENCES Reports(ReportID)
)

--
Hugo Kornelis, SQL Server MVP