Field not found: 'System.Collections.Generic.KeyValuePair`2.Value'

I get a Field not found: 'System.Collections.Generic.KeyValuePair`2.Value' when i try to open up a table in SQL Server 2005 Managment Studio. I have the June CTP of SQL 2005 and I have the July CTP of Visual Studio 2005 Professional. Can anyone help me cause i'm bout to lose my mind. I'm hoping to find the real problem because i'm thinking that this error message does not lead to exactly the problem. I would appreciate any help that you all can give me.
Thanks,

MatthewHello Mulktide!
I encountered the same error. Did you manage to solve the problem?

Thanks.
pax

Field name in LIKE statement

Hi,

I have come across an SQL statement today that I actually thought was impossible. I always thought that a LIKE statement must use a literal value such as field1 like '%ABC'. But today I came across a subselect that used a like statement

select * from TABLE_A
where exists (select 'X' from TABLE_B where TABLE_B.f1 like '%'+TABLE_A.f2+'%')

As I said I didn't think you could use a field in a like statement. This works in SQL Server,,, could people tell me if this would function for other databases.

Thanks
DaveYes; no problem in Oracle.SQL> SELECT * FROM EMP e
2 WHERE EXISTS (SELECT NULL FROM DEPT d
3 WHERE d.deptno LIKE '%' || e.sal || '%');

EMPNO ENAME JOB MGR HIREDATE SAL DEPTNO
---- ---- --- ---- --- ---- ----
7839 KING PRESIDENT 17.11.81 50 10

SQL>|||also no problem in mysql -- where TABLE_B.f1 like concat('%', TABLE_A.f2, '%')|||In Informix it can be done the same way as previously described for Oracle... Looks like standard SQL to me.sql

Field name as a Parameter

Hi,

I'm trying to figure out how to pass a field name into a procedure so the procedure can selectively find out the value of different fields. The DB table I'm interested in has multiple bool fields in it and the field name is the parameter I want to pass into the procedure. With the example code I list below, there is only one row that exists in the table so the end result should be dependent upon the value in the field. The calling procedure is "attempting" to pass in the name of the field, and the called procedure should use the paramter passed in as the field to return in the table. If the field returned in the called procedure is true, the called procedure returns "0" to the calling procedure, otherwise it returns 1. The syntax I have doesn't seem to work in that the called procedure always returns true from the field and the calling procedure always gets 0 back from the called procedure.

In the calling procedure, I'm doing a select just to find out if a 0 or 1 was returned, but this will not be the final version of the code, its just for test purposes.

Any help on what I'm doing wrong greatly appriciated.

- Bruce

ALTER PROCEDURE [dbo].[caller]

AS

BEGIN

SET NOCOUNT ON;

DECLARE @.include int,

@.Committee bit

execute @.include = dbo.callee @.Committee

IF @.include = 0

BEGIN

select * from ProvCompensation

END

ELSE

BEGIN

select * from ProvCommittee

END

END

ALTER PROCEDURE [dbo].[callee]

(@.fName bit)

AS

BEGIN

DECLARE @.aBool bit

SET NOCOUNT ON;

select @.aBool = @.fName from ProvisionsPlanSum where PlanId = -99

IF @.aBool = 'true'

BEGIN

RETURN (0)

END

ELSE

BEGIN

RETURN (1)

END

END

Generally speaking, parameterizing a column (or table for that matter) is not considered the "right" way to build SQL Server objects. But, if you really need to, you can use dynamic sql, and just execute it like:

declare @.aBool bit
declare @.query nvarchar(max)
declare @.fName varchar(10)
set @.fName = 'someCol'

set @.query = 'select @.aBool = ' + @.fName + ' from ProvisionsPlanSum where PlanId = -99'

exec sp_executeSQL
@.query, N'@.aBool bit output', @.aBool= @.aBool output

select @.aBool

Not 100% sure if this is perfect. I do know that this will work, if you need an example:

declare @.objectId int

exec sp_executeSQL
N'select @.objectId = max(object_id) from sys.objects',
N'@.objectId int output', @.objectId=@.objectId output
select @.objectId

|||

This will also work :)

declare @.aBool bit
declare @.query nvarchar(1000)
declare @.fName varchar(30)
set @.fName = '1; DROP TABLE Test12; --'

set @.query = 'select @.aBool = ' + @.fName + ' from ProvisionsPlanSum where PlanId = -99'

EXEC sp_executeSQL
@.query, N'@.aBool bit output', @.aBool= @.aBool OUTPUT

SELECT @.aBool

|||

Hey guys,

Thanks for the help. I'll give it a go sometime today.

... worked like poop through a goose, thanks guys!

- Bruce

Field Default Value

I'm copying MyDbTable to TempdbTable. There are 12 fields and I'm ignoring 11 of them when copying the one table to another with DTSWizard.

Once the TempdbTable is there, I'm adding 2 fields along with the one that is there already.

1) field1 int, Nullable, Default value ((3))

2) field2 tinyint, Nullable, Default value ((1))

The reason why I'm creating these two fields and giving them a value so they will match up when I import TempdbTable to MyDb2ndTable.

The problem I'm having is... field1 and field2 have a Null value intead of the value I'm giving them.

I'm new at this, so maybe the cause of the problem is obvious, but if anyone can help me I'd really appreciate it.

Thanks,

Bill

When you add the new columns, you MUST make them NOT NULL if you wish to set a DEFAULT value and assign that default value to the existing rows.

ALTER TABLE #MyTable
ADD MyNewColumn varchar(30) NOT NULL DEFAULT 'N/A'

|||

Thanks for responding Arnie.

I Ran This...

ALTER TABLE #xlaANLsubscribers
ADD listid int NOT NULL DEFAULT '3'
ADD isconfirmed tinyint NOT NULL DEFAULT '1'

I Got this Error...

Msg 102, Level 15, State 1, Line 3
Incorrect syntax near 'isconfirmed'.

When I added one field only I got something like...

The table doesn't exist or you don't have permission.

Thanks,

Bill

|||

ALTER TABLE #xlaANLsubscribers
ADD listid int NOT NULL DEFAULT '3',
isconfirmed tinyint NOT NULL DEFAULT '1'

(without the additional ADD)

Jens K. Suessmeyer.

http://www.sqlserver2005.de

|||

Thanks Jens,

This it the error I'm getting when running that, and the table is definitely there.

Msg 4902, Level 16, State 1, Line 1

Cannot find the object "#xlaANLsubscribers" because it does not exist or you do not have permissions.

Thanks,

Bill

|||Was the #Temp table created by the same user in the same session/connection?|||

Thanks Arnie,

I imported the records into the table of the database with DTSWizard authenicated in Windows. I then copied this table to Temp with the same authenication, but I've been running your script and doing the editing of the Temp table in SQL authenication. So I tried running your script and editing in Windows authenication and the same error happened. How that's not to confusing.

Thanks,

Bill

|||Unless you specify the ## as a prefix instead of a single #, the table is only available to the session where it was created in. Even other connected session might have no access to the table.

Jens K. Suessmeyer

http://www.sqlserver2005.de
|||I'm getting the same error even when I specify ## as the prefix of the table.|||

Bill,

Does this code execute without error.

Code Snippet

SET NOCOUNT ON

CREATE TABLE #MyTable
( RowID int IDENTITY,
Name varchar(20)
)

INSERT INTO #MyTable VALUES ( 'Bill' )

SELECT *
FROM #MyTable

ALTER TABLE #MyTable
ADD Address varchar(30) NOT NULL DEFAULT 'N/A'

SELECT *
FROM #MyTable

DROP TABLE #MyTable

|||

Arnie,

No errors, it worked perfectly.

|||Then I assume that your executing user is another one than the one which was uses to execute Arnies script, right ? Try to create explicitly dbo.#Something and select also with the full qualified name.

Jens K. Suessmeyer

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

I can only suggest walking back throught the rest of your code (all code that touches the #xlaANLsubscribers table), and verify that there isn't a DELETE TABLE stuck in there somewhere...

If you are creating the #Temp table, and populating it in the same session, it 'should' be there. So I'm baffled at the moment and will have to give this some thought...

|||

Jens,

I thought that schema/owner was ignored in TempDb...

|||I'm using DTSWizard to copy the table from the Main Database to Temp... am I wrong in doing it that way?

Field attribute question

If I want to store day of the week (i.e. Mon), do I use datetime field or char? Also if I update #25/12/2004# to a datetime field. What is the value is the time port of the field? Thx.I would calculate the day of the week from a datetime, if you were also saving the datetime. If you are saving ONLY a day of week, then I would use a number, etc.

With only a date stored in a DateTime, the time would be 00:00:00.000|||I created two fields in a sql table, one for storing date and one for time. In my VB, I populated them with #3/8/2005# and #10:00:00 PM# respectively. The program crashed with message "sqldattime overflow. Must be between 1/1/1753 12:00:00 AM ...." Why did the program crash? Should I combine two fields into one?|||Show some code.|||In my aspx form I created one text field and one drop down list. The value in the text field is pulled from MS calendar control so it is like 3/10/2005 and the ddl is filled with time in every 15 min. e.g. 10:00, 10:15.
In my vb code, I coded as following:

...
reservation.requesteddate=cdate(txtbkdate.text)
reservation.requestedtime=ddlRequestedTime.SelectedItem.Text
...
reservation.insert()

I also did some experiment in which I created a temp table and a smalldatetime field and I entered only 10:00. When I debugged, I found it actually stored a date and time and the date value is one day (can't remember exactly) before the valid range.

Should I combine two fields into only one? Thanks.|||DateTime fields ALWAYS contains a date and a time. You can use code to split them for independent display, if you wish. I certainly would combine them in a single field if they refer to the same event.|||Thanks, Doug. Yes, they do refer to the same event. What I intended to do is a field for booked date and another for booked time. I though by splitting into two fields, when I create tree view for booking for a particular date will be comparatively easier. But still, apart from such reason, if I do have a need to only store a time value in a datatime field, what is the proper way?|||The reality is, you ALWAYS store both, and then use client side (in this case, ASP.NET) formatting to show the data you want. Alternately, you can use SQL code to get just the date or time (see CONVERT() in SQL Server Books Online).|||Doug, here is another real situation I have, can I have your comments.
I have a table to store my restaurant's info and I need to store the business hours. I created two fields to store from and to. Before I create a form to maintain the info of this table, I simply input data from vs2003 and it did accept my entry (only 10:00 entered to a smalldatetime field). Now if I create a form for data entry, apparently it makes no sense to ask user to input date and time, so I have to do something in the vb code. But which is a more sensible way to do so? Should I just concatenate any date with the time entered by user and update it to sql table? How do other developers normally do? Since I'm developer from other platform, may be my though is totally unlogically...|||I would likely concatentate the date and time, then parse it into a single DateTime (I generally do not use SmallDateTime's). The question is, is the date and time a single attribute? Meaning, are you talking about 3/13/2005 10:15 AM as a single thing, so that at some point, you might want to find a row based upon that date/time combination.|||Sorry I may have confused you. Put it in a simply way, what I need to store is just only a time, the date is meaningless to me. So really I don't need to store a date to the database but according to what you said, the date portion is required when popuplating the database. It means that I have to put in a meaningless date even I don't need it. The way I see is if documentation is not done properly or the field is not named meaningfully then other programmers may be misled by the date when examining data. I hope this is clear for you to understand my point.

Fetching Data from Table result

This may not be possible but if I don't ask...
I have a SQL query which returns something like :
Category Value1 Value2 Value3 Value 4
A 10 12 14 15
B 8 22 44 55
C 5 33 55 88
I have a table which displays the above data like:
Category Values
C 5
33
55
88
B 8
22
44
55
A 10
12
14
15
Note the ordering of the resultant table. In the Table Properties | Sorting
I have a function which returns the highest value given a set of values.
Hence the sorting is set to Code.HighestValue (Value1, Value2, Value3,
Value4). Sure there's a better way to do this and am open to suggestions.
Finally we come to my problem. I want to display 88 (highest value from
category C) in another part of the report. Is there a way to do this other
than changing the sorting in the SQL query so I can grab the first row?
MartinYeah as I see you have written code to find the highest value.
Just write another function with the same parameters as
HighestValue that returns the highest value.
In your code just compare value 1 ,value 2 ,value 3 ,value 4.
Then use the return value of the function whereever you want.
Hope this helps
>--Original Message--
>This may not be possible but if I don't ask...
>I have a SQL query which returns something like :
>Category Value1 Value2 Value3 Value 4
>A 10 12 14 15
>B 8 22 44 55
>C 5 33 55 88
>I have a table which displays the above data like:
>Category Values
>C 5
> 33
> 55
> 88
>B 8
> 22
> 44
> 55
>A 10
> 12
> 14
> 15
>Note the ordering of the resultant table. In the Table
Properties | Sorting
>I have a function which returns the highest value given a
set of values.
>Hence the sorting is set to Code.HighestValue (Value1,
Value2, Value3,
>Value4). Sure there's a better way to do this and am open
to suggestions.
>Finally we come to my problem. I want to display 88
(highest value from
>category C) in another part of the report. Is there a way
to do this other
>than changing the sorting in the SQL query so I can grab
the first row?
>
>Martin
>.
>

fetch the value from each cell into datatable

I'm trying this code but nothing is being displayed

SqlConnection conn =newSqlConnection(ConfigurationManager.AppSettings["STRING_CON"]);

SqlDataAdapter da =newSqlDataAdapter("my_sproc", conn);

DataTable dt =newDataTable();

DataRow dr;

//Adding the columns to the datatable

dt.Columns.Add("CategoryIdIn");

dt.Columns.Add("CategoryNameVc");

foreach (DataGridItem itemin gd_freq.Items)

{

dr = dt.NewRow();

dr[0] = item.Cells[0].Text;

dr[1] = item.Cells[1].Text;

dt.Rows.Add(dr);

}//ForEach

da.Fill(dt);

gd_freq.DataSource = dt;

gd_freq.DataBind();

I am not clear what you are trying to do here.

Do you want to populate the datagrid by binding it to the datatable returned by my_sproc?

"fetch the value from each cell into datatable"

Where did the values in the cells come from?

Can you explain a bit more?

|||

Hi Robert,

I'm also a little lost after reading your posted code. It seems that first you filled a table by fetching the data from a data grid and then you called a stored procedure and updated that table with data returned. Note here that all the data you got from the data grid will be overridden now. So, what exactly are you going to do?

If you just want to fetch the data from a data grid and then update the data source, you can try to use da.Update(dt) in your code (instead of da.Fill);

And if you want to fill the data grid with data from the database, first, you can filldt by executing da.Fill(dt), then you can set gd_freq.datasource=dt. Finally call gd_freq.databind and by now you will have your data grid well populated.

Hope my suggestion could help

Thanks.

Fetch a certain row

Hi.
I have a dataset with a two column table:
name | value
What I'm trying to do is to make a textbox in the report designer to fetch a
certain field (value) from the table based on the name field.
How to fetch that certain cell from the dataset.
Any help appreciated.
AlekWhere do you select the name for which you want the value
You could do this with a parameter which displays the name and returns the
value. Then in the Text box
=Parameters!myparam.value
You could maybe filter the where clause in the sql or in the data set..
You could write a funcion which accepts some value and returns the name of
the field in the data set... Then use
=Fields(Code.GetFieldname("mypassedinvalue")).Value in the textbox
Hope this helps... If I do not understand your question, please send more
details
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Alek Lynge" <alekl@.penguin.dk> wrote in message
news:OUSV$MI2FHA.3712@.TK2MSFTNGP09.phx.gbl...
> Hi.
> I have a dataset with a two column table:
> name | value
> What I'm trying to do is to make a textbox in the report designer to fetch
> a certain field (value) from the table based on the name field.
> How to fetch that certain cell from the dataset.
> Any help appreciated.
> Alek
>|||Hi, Wayne.
Thanx for your quick answer. However I'm still a bit lost in the VBScript
world, as I'm usually coding in c#.
It is easiest to explain with a sample data example:
I have a dataset with multiple tables. The one I need is called
InvoiceTemplate and has its tableadapter implemented to fetch data from the
sql server backend with a stored proc, which accepts a single parameter: an
integer determining the country.
When the dataset is filled I get the following data in my InvoiceTemplate
table:
Columns: name, value
Sample data (ntext, ntext):
'iName', 'Lene Espersen'
'iNumber', 'Faktura nr.'
'iUnits', 'Antal'
'iPrice', 'Pris'
Etc.
So I've got a textbox in the report designer which I need to fill with the
value from the row containing 'iUnits' (get the data 'Antal')?
How to make an elegant expression solution to this? Essentially I'm trying
to escape from table pivoting, as sql server 2000 has poor support for that.
Best regards
Alek
"Wayne Snyder" <wayne.nospam.snyder@.mariner-usa.com> wrote in message
news:O7y8AmI2FHA.2616@.tk2msftngp13.phx.gbl...
> Where do you select the name for which you want the value
> You could do this with a parameter which displays the name and returns
> the value. Then in the Text box
> =Parameters!myparam.value
> You could maybe filter the where clause in the sql or in the data set..
> You could write a funcion which accepts some value and returns the name of
> the field in the data set... Then use
> =Fields(Code.GetFieldname("mypassedinvalue")).Value in the textbox
> Hope this helps... If I do not understand your question, please send more
> details
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> www.mariner-usa.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
> "Alek Lynge" <alekl@.penguin.dk> wrote in message
> news:OUSV$MI2FHA.3712@.TK2MSFTNGP09.phx.gbl...
>> Hi.
>> I have a dataset with a two column table:
>> name | value
>> What I'm trying to do is to make a textbox in the report designer to
>> fetch a certain field (value) from the table based on the name field.
>> How to fetch that certain cell from the dataset.
>> Any help appreciated.
>> Alek
>

Fast Query

Hi,
What's the fastest way of checking if a value exists in a table? How is this
done in a production enviornment? Is it better ot return the result or a 1 o
r
0 as yes or no via an output parameter.
I am currently using
Create Proc test(
@.searchVal nvarchar(10))
as
select id from tablea where id = @.searchVal
ThanksIts better to select the key or something like this that I always choose
select 1 from tablea where id = @.searchVal
and you can assign it to an output parameter or type BIT. Better than
getting a dataset. And in the calling application (with respect to C#.NET
here) use ExecuteScalar() and get the value.
Hope this helps.|||You can also use exists
Create Proc test(
@.searchVal nvarchar(10))
as
if exists (select * from tablea where id = @.searchVal)
select 1
else
select 0
Denis the SQL Menace
http://sqlservercode.blogspot.com/