Item cannot be found in the collection corresponding to the requested name or ordinal.

I am retrieving the data using the below sql statement in VB 6.0 & it giving me the Error
Item cannot be found in the collection corresponding to the requested name or ordinal.
I have tried to run the same sql statement it ran fine
I am using a recordset to retrive a data & it looks like recorset do have a data init but when I am assigning it to a temp variable it ging me th eabove stated error
strsql = "select sum(isnull(Amount,0)) from Payments where Name ='" & Name & "'"
objRS.CursorLocation = enmCursorLocation
Call objRS.Open(vntSPNameOrSQLOrCmdObject, vntConn, enmCursorType, enmLockType, lngOptions) If (Not objRS Is Nothing) Then
If (Not objRS Is Nothing) Then
dblAmount = CDbl(Trim$(objRS.Fields.Item("Amount").Value))
end if
Please advice
Thanks
Your column is no longer called amount, since you are performing
aggregations against it. You can change your SQL statement to provide an
alias, e.g.
SELECT Amount = SUM(...) FROM ...
Or you can use a different, more meaningful name, e.g.
SELECT SumAmount = SUM(...) FROM ...
Or in your code you can refer to the ordinal position of the column, instead
of the name.
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"SAMAY" <anonymous@.discussions.microsoft.com> wrote in message
news:6C1456DD-FBC0-4ADC-8B99-F7BFA10821F1@.microsoft.com...
> I am retrieving the data using the below sql statement in VB 6.0 & it
giving me the Error
> Item cannot be found in the collection corresponding to the requested name
or ordinal.
> I have tried to run the same sql statement it ran fine
> I am using a recordset to retrive a data & it looks like recorset do have
a data init but when I am assigning it to a temp variable it ging me th
eabove stated error
> strsql = "select sum(isnull(Amount,0)) from Payments where Name ='" & Name
& "'"
> objRS.CursorLocation = enmCursorLocation
> Call objRS.Open(vntSPNameOrSQLOrCmdObject, vntConn, enmCursorType,
enmLockType, lngOptions) If (Not objRS Is Nothing) Then
> If (Not objRS Is Nothing) Then
> dblAmount = CDbl(Trim$(objRS.Fields.Item("Amount").Value))
> end if
> Please advice
> Thanks
|||Thanks for your help
it worked
Thanks again

Item cannot be found in the collection corresponding to the requested name or ordinal.

I am retrieving the data using the below sql statement in VB 6.0 & it giving
me the Error
Item cannot be found in the collection corresponding to the requested name o
r ordinal.
I have tried to run the same sql statement it ran fine
I am using a recordset to retrive a data & it looks like recorset do have a
data init but when I am assigning it to a temp variable it ging me th eabov
e stated error
strsql = "select sum(isnull(Amount,0)) from Payments where Name ='" & Name &
"'"
objRS.CursorLocation = enmCursorLocation
Call objRS.Open(vntSPNameOrSQLOrCmdObject, vntConn, enmCursorType, enmLockTy
pe, lngOptions) If (Not objRS Is Nothing) Then
If (Not objRS Is Nothing) Then
dblAmount = CDbl(Trim$(objRS.Fields.Item("Amount").Value))
end if
Please advice
ThanksYour column is no longer called amount, since you are performing
aggregations against it. You can change your SQL statement to provide an
alias, e.g.
SELECT Amount = SUM(...) FROM ...
Or you can use a different, more meaningful name, e.g.
SELECT SumAmount = SUM(...) FROM ...
Or in your code you can refer to the ordinal position of the column, instead
of the name.
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"SAMAY" <anonymous@.discussions.microsoft.com> wrote in message
news:6C1456DD-FBC0-4ADC-8B99-F7BFA10821F1@.microsoft.com...
> I am retrieving the data using the below sql statement in VB 6.0 & it
giving me the Error
> Item cannot be found in the collection corresponding to the requested name
or ordinal.
> I have tried to run the same sql statement it ran fine
> I am using a recordset to retrive a data & it looks like recorset do have
a data init but when I am assigning it to a temp variable it ging me th
eabove stated error
> strsql = "select sum(isnull(Amount,0)) from Payments where Name ='" & Name
& "'"
> objRS.CursorLocation = enmCursorLocation
> Call objRS.Open(vntSPNameOrSQLOrCmdObject, vntConn, enmCursorType,
enmLockType, lngOptions) If (Not objRS Is Nothing) Then
> If (Not objRS Is Nothing) Then
> dblAmount = CDbl(Trim$(objRS.Fields.Item("Amount").Value))
> end if
> Please advice
> Thanks|||Thanks for your help
it worked
Thanks again

Issues in setDate of PreparedStatement

Hi,
I have recently started using the JDBC MS-SQL driver. I am facing a
problem with the setdate() function of the prepared statement. if we are
using the setDate function of the prepared statment then the date gets
updated with a timestamp.
i.e. pObjpreparedStmtIns.setDate(2, pdCurrentDate);
will add the date in the system as 2004-12-27 :12:00:000
instead of 2004-12-27 :00:00:000.
pdCurrentDate is java.sql.Date() set to todays date.
has anyone come acros this problem.
Thank you
"Shirish Nair" <Shirish Nair@.discussions.microsoft.com> schrieb im
Newsbeitrag news:4BAE3584-2220-4DF3-B66C-A75F3CAB3DF8@.microsoft.com...
> Hi,
> I have recently started using the JDBC MS-SQL driver. I am facing a
> problem with the setdate() function of the prepared statement. if we
are
> using the setDate function of the prepared statment then the date gets
> updated with a timestamp.
> i.e. pObjpreparedStmtIns.setDate(2, pdCurrentDate);
> will add the date in the system as 2004-12-27 :12:00:000
> instead of 2004-12-27 :00:00:000.
> pdCurrentDate is java.sql.Date() set to todays date.
> has anyone come acros this problem.
Two things come to mind:
- it's just a display problem (12am ~ 0:00 ~ 24:00)
- it's a time zone offset thingy (are you +/- 12h?)
- you could also try to use java.sql.Timestamp
Regards
robert

Issueing a CREATE TABLE statement within a transaction

Sorry new to the newsgroups so please point me to where I need to be if this is the wrong group. Below is a chunck of code that I have written to play around with transactions in Visual Studion 2005. I want to be able to create a table, the columns in it, and add rows of data and if any of those statements fail I woudl like to rollback the entire transaction. My testing has shown that when the CREATE TABLE sql is issued it automatcially commits. In my example below the VER_INFO table is immediately created when the ExecuteNonQuery() statement is invoked. The next 2 insert statements are held correctly in the transaction, however when the second table, WADES_TEST is created that causes the 2 rows to be inserted and the second table is immediately created. So my commit statement is useless. Is this normal? Is there a way to force the system to keep the CREATE TABLE statement in the transaction so I can roll it back if needed?

OracleCommand cmd = new OracleCommand();

OracleConnection wConnection = new OracleConnection(wConnString);

wConnection.Open();

OracleTransaction trans = wConnection.BeginTransaction();

string wQry = "CREATE TABLE VER_INFO (VER NVARCHAR2(25))";

cmd.CommandText = wQry;

cmd.Connection = wConnection;

cmd.Transaction = trans;

try

{

cmd.ExecuteNonQuery();

wQry = "INSERT INTO VER_INFO VALUES ('INITIALIZED')";

OracleCommand cmd4 = new OracleCommand(wQry, wConnection, trans);

cmd4.ExecuteNonQuery();

wQry = "INSERT INTO VER_INFO VALUES ('testss')";

OracleCommand cmd2 = new OracleCommand(wQry, wConnection, trans);

cmd2.ExecuteNonQuery();

wQry = "CREATE TABLE WADES_TEST (WADE NVARCHAR2(25))";

OracleCommand cmd3 = new OracleCommand(wQry, wConnection, trans);

cmd3.ExecuteNonQuery();

trans.Commit();

wConnection.Dispose();

}

catch

{

trans.Rollback();

wConnection.Close();

}

Thanks in advance,

Wade Sharp

This might be more appropriate to be posted to an Oracle forum or newsgroup. Or to an ADO.NET forum.

Issue with SELECT "IN" statement

In Reporting Services I have following query. For some reason query only
picks the FIRST choice for State or Product â?¦ even though user selects
multiple products or states â?¦ what is the issue here? Could you please help
me?
Thank you.
SELECT
DATENAME(MONTH, dbo.fnGetDSDate(ts_time)) AS SubmitMonth,
YEAR(dbo.fnGetDSDate(ts_time)) AS SubmitYear,
s.ts_name
FROM ShowAll INNER JOIN TS_STATE s on newstate = s.id
WHERE
(transitionlabel <> 'Update' OR transitionlabel <> '')
AND ts_productsubsystem IN (@.ProductId)
AND ts_newstate IN (@.StateId)On Jun 4, 10:43 am, ozcan <o...@.discussions.microsoft.com> wrote:
> In Reporting Services I have following query. For some reason query only
> picks the FIRST choice for State or Product ... even though user selects
> multiple products or states ... what is the issue here? Could you please help
> me?
> Thank you.
> SELECT
> DATENAME(MONTH, dbo.fnGetDSDate(ts_time)) AS SubmitMonth,
> YEAR(dbo.fnGetDSDate(ts_time)) AS SubmitYear,
> s.ts_name
> FROM ShowAll INNER JOIN TS_STATE s on newstate = s.id
> WHERE
> (transitionlabel <> 'Update' OR transitionlabel <> '')
> AND ts_productsubsystem IN (@.ProductId)
> AND ts_newstate IN (@.StateId)
You will most likely need to loop through the multi-select report
parameter and insert the values in a temp table (in a query/stored
procedure outside the report) and then use the above query to access
the values in the temp table. Here is a query that should get you
started in looping through the multi-select report parameter values
selected.
--CREATE PROC ParseRSMultiParameterList
DECLARE
@.STATES VARCHAR(MAX)
--AS
DECLARE @.STATEBUFFER VARCHAR(MAX),
@.END_POSITION INT;
--TEST DATA--
SET @.STATES = 'AL,TN,CA,OH';
--TEST DATA--
CREATE TABLE #STATELIST (State char(2));
SET @.STATEBUFFER = @.STATES;
WHILE (LEN(@.STATEBUFFER) > 0)
BEGIN
IF (CHARINDEX(',', @.STATEBUFFER) > 0)
BEGIN
SET @.END_POSITION = CHARINDEX(',', @.STATEBUFFER);
INSERT INTO #STATELIST VALUES (SUBSTRING(@.STATEBUFFER, 1,
(@.END_POSITION - 1)));
END
IF (CHARINDEX(',', @.STATEBUFFER) = 0)
BEGIN
SET @.END_POSITION = LEN(@.STATEBUFFER);
INSERT INTO #STATELIST VALUES (SUBSTRING(@.STATEBUFFER, 1,
(@.END_POSITION + 1)));
END
SET @.STATEBUFFER = RIGHT(@.STATEBUFFER, (LEN(@.STATEBUFFER) -
@.END_POSITION));
END
SELECT
DATENAME(MONTH, dbo.fnGetDSDate(ts_time)) AS SubmitMonth,
YEAR(dbo.fnGetDSDate(ts_time)) AS SubmitYear,
s.ts_name
FROM ShowAll INNER JOIN TS_STATE s on newstate = s.id
WHERE
(transitionlabel <> 'Update' OR transitionlabel <> '')
AND ts_productsubsystem IN (@.ProductId)
AND ts_newstate IN (select State from #STATELIST)
DROP TABLE #STATELIST;
Hope this helps.
Regards,
Enrique Martinez
Sr. Software Consultant|||You may want to look at the Report, Parameters setting (from layout view) and
see if that parameter has the "multi-value" checkbox selected. If it is not
checked, I would think that the end user would only be able to select one
single parameter, and this does not seem to be the issue - but may want to
check it.

Issue with Export to CSV

Dear Anyone,

Whenever we export reports to CSV, it seems that the column headers in the CSV fileb are the actual column names in the select statement we used in the data set and not the column names that are in the report.

Is there a way to reflect the column names in the report to the CSV file rather than the actual physical column name in the report?

Thanks,
JosephAs there is no explicit binding in RDL between the data columns and the header columns, CSV export uses the names of the controls in the export. You can either change the textbox name or you can override the name using the DataElementName property (on the data tab of the textbox properties dialog). This is just like the XML data output, described here: http://msdn2.microsoft.com/en-us/library/ms156020(en-US,SQL.90).aspx. You can also exclude them here (DataElementOutput).

I have asked the documentation people to update the CSV topic.

Issue with CONTAINSTABLE statement

I am using the following query to search all columns in the 'dashboard'
table for the value 'Meets':

SELECT * from dashboard AS FT_TBL INNER JOIN CONTAINSTABLE(dashboard,*,
'meets') AS KEY_TBL ON FT_TBL.employee = KEY_TBL.[KEY]

I have multiple records that contain the word 'Meets', but none are
showing up as a result of this query. Any ideas?

Also, anytime I use a space in my search condition (Meets Expectations
instead of Meets) I am getting an error:
Syntax error occurred near 'Expectations'. Expected '' in search
condition 'Meets Expectations'.

Any ideas?

Thanks in advance.GAH.. Had not run a start_full for the index. All is working now.
Thanks!

Nate wrote:

Quote:

Originally Posted by

I am using the following query to search all columns in the 'dashboard'
table for the value 'Meets':
>
SELECT * from dashboard AS FT_TBL INNER JOIN CONTAINSTABLE(dashboard,*,
'meets') AS KEY_TBL ON FT_TBL.employee = KEY_TBL.[KEY]
>
I have multiple records that contain the word 'Meets', but none are
showing up as a result of this query. Any ideas?
>
Also, anytime I use a space in my search condition (Meets Expectations
instead of Meets) I am getting an error:
Syntax error occurred near 'Expectations'. Expected '' in search
condition 'Meets Expectations'.
>
Any ideas?
>
Thanks in advance.

isql problem with null values

I am having a problem running a stored procedure using isql to launch the procedure.

My stored procedure contains a statement like:

select @.myvar = count(*) from mytable where mycolumn is null

when I run the procedure from query analyser a value is set in @.myvar showing the correct number of null values from mytable which is output to another table where I can see it.

Without making any changes, when I run the identical procedure using isql to execute the procedure @.myvar gets set to zero. The procedure cannot find the null values in mytable

I have tried setting ANSI_NULLS on and off inside the procedure but still get the same result.

Can someone please tell me what is going on, and how I might get isql to show me how many null values exist in the table?I don't seems to have anyproblem with this

USE Northwind
GO

CREATE TABLE myTable99(Col1 int)
GO

INSERT INTO myTable99(Col1)
SELECT 1 UNION ALL
SELECT 2 UNION ALL
SELECT 3 UNION ALL
SELECT null UNION ALL
SELECT null UNION ALL
SELECT null
GO

CREATE PROC mySproc99 @.Rows int OUTPUT AS
SELECT @.Rows = COUNT(*) FROM myTable99 WHERE Col1 IS NULL
GO

DECLARE @.Rows int
EXEC mySproc99 @.Rows OUTPUT
SELECT @.Rows

DECLARE @.cmd varchar(8000), @.sql varchar(8000)
SELECT @.sql = 'DECLARE @.Rows int EXEC mySproc99 @.Rows OUTPUT SELECT @.Rows'
SELECT @.cmd = 'osql -U <username> -P <password> -S <servername> -d Northwind -Q "' + @.sql + '"'
EXEC master..xp_cmdshell @.cmd
GO

DROP PROC mySproc99
DROP TABLE myTable99
GO|||Which SQL Server are you running? If SQL 2000, which campatibility level?|||Brett,

Thanks for your reply, I'll try out your script tomorrow morning when I get into the office. And let you know the result.

The reason I'm confused, is I set up my script using osql and it worked fine on our test server, It didn't run at all on production, then I found that it did run if I changed osql to isql, but with the quirk noted. I'm sure it is something to do with the OLE DB config, but I am not sure where to start looking for differences. Also the osql version might work if I could get ODBC set up on production, but I'm not sure how to go about doing that.

Anyway, thanks again for all your help and I'd be grateful for any further suggestions.|||I don't have tons of experience with xp_cmdshell but my results from the example script above look pretty strange:

www.sullivanstreet.us/query.jpg