DataSet - Should Be Easy But I Don't Know How
Jan 6, 2004
I am working on a WebMatrix ASP project. I have a query that returns a System.Data.DataSet but I don't know how to assign the DataSet to a variable so that I can run some validation tests on it.
This is what I have so far...What I want to do is assign the dataset to a variable and to see if it is NULL or Not. I don't how??? Any help would be awesome.
Sub Button1_Click(sender As Object, e As EventArgs)
???? = MyQueryMethod(txtPhone.Text)
Thanks,
Matt
View 5 Replies
ADVERTISEMENT
Sep 14, 2004
I need to get the number of elements returned by an sql statement. i have the following code
I want to know how many rows were returned from the database?
SqlConnection conn = new SqlConnection(connectionString);
string query = null;
//Gather spec_names for dropdownlist
query = "SELECT spec_name FROM Menu WHERE use_it=1";
//Add Query to an SQL Command
SqlCommand cmd = new SqlCommand(query, conn);
SqlDataAdapter adp = new SqlDataAdapter();
//Add the command to the adapter
adp.SelectCommand = cmd;
//Create and fill a DataSet
DataSet ds = new DataSet();
adp.Fill(ds,"Menu");
View 1 Replies
View Related
May 26, 2015
I have a report with multiple datasets, the first of which pulls in data based on user entered parameters (sales date range and property use codes). Dataset1 pulls property id's and other sales data from a table (2014_COST) based on the user's parameters. I have set up another table (AUDITS) that I would like to use in dataset6. This table has 3 columns (Property ID's, Sales Price and Sales Date). I would like for dataset6 to pull the Property ID's that are NOT contained in the results from dataset1. In other words, I'd like the results of dataset6 to show me the property id's that are contained in the AUDITS table but which are not being pulled into dataset1. Both tables are in the same database.
View 0 Replies
View Related
Oct 1, 2015
I have a small number of rows in a dataset, Table 1. There is a CLOB on a large dataset, Table 2. They join on a PK. I would like to retrieve this CLOB and add it to the data flow for Table1. In short I want to emulate the following:
Table 1:Â Small table without CLOB, 10 rows.Â
Table 2: Large table with CLOB, 10,000,000 rows
select CLOB
from table2
where pk = (select pk from table1)
I want this to return the CLOBs for the small number of rows in Table 1. The PK is indexed obviously so it should be a fast look up.
Table 1 and Table 2 live on different Oracle databases. How do I perform this operation efficiently in SSIS? It seems the Lookup and Merge Join wont do this.
View 2 Replies
View Related
May 27, 2015
I have a report with multiple datasets, the first of which pulls in data based on user entered parameters (sales date range and property use codes). Dataset1 pulls property id's and other sales data from a table (2014_COST) based on the user's parameters.
I have set up another table (AUDITS) that I would like to use in dataset6. This table has 3 columns (Property ID's, Sales Price and Sales Date). I would like for dataset6 to pull the Property ID's that are NOT contained in the results from dataset1. In other words, I'd like the results of dataset6 to show me the property id's that are contained in the AUDITS table but which are not being pulled into dataset1. Both tables are in the same database.
View 3 Replies
View Related
May 21, 2007
I found out the data I need for my SQL Report is already defined in a dynamic dataset on another web service. Is there a way to use web services to call another web service to get the dataset I need to generate a report? Examples would help if you have any, thanks for looking
View 2 Replies
View Related
Oct 12, 2007
Is there any way to display this information in the report?
Thanks
View 3 Replies
View Related
May 7, 2008
Hi,
I have a stored procedure attached below. It returns 2 rows in the SQL Management studio when I execute MyStorProc 0,28. But in my program which uses ADOHelper, it returns a dataset with tables.count=0.
if I comment out the line --If @Status = 0 then it returns the rows. Obviously it does not stop in
if @Status=0 even if I pass @status=0. What am I doing wrong?
Any help is appreciated.
ALTER PROCEDURE [dbo].[MyStorProc]
(
@Status smallint,
@RowCount int = NULL,
@FacilityId numeric(10,0) = NULL,
@QueueID numeric (10,0)= NULL,
@VendorId numeric(10, 0) = NULL
)
AS
SET NOCOUNT ON
SET CONCAT_NULL_YIELDS_NULL OFF
If @Status = 0
BEGIN
SELECT ......
END
If @Status = 1
BEGIN
SELECT......
END
View 4 Replies
View Related
Apr 11, 2008
i have two datasets.one dataset have old data from some other database.second dataset have original data from sql server 2005 database.both database have same field having id as a primary key.i want to transfer all the data from first dataset to new dataset retaining the previous data but if old dataset have the same id(primary key) as in the new one then that row will not transfer.
but if the id(primary key) have changed values then the fields updated with that data.how can i do that.
View 4 Replies
View Related
Dec 19, 2006
Hi,
I have two datasets in my report, D1 and D2.
D1 is a list of classes with classid and title
D2 is a list of data. each row in D2 has a classid. D2 may or may not have all the classids in D1. all classids in D2 must be in D1.
I want to show fields in D2 and group the data with classids in D1 and show every group as a seperate table. If no data in D2 is available for a classid, It shows a empty table.
Is there any way to do this in RS2005?
View 2 Replies
View Related
Sep 3, 2015
Using this IIF statement:
=CountDistinct(IIF(Fields!Released_DT.Value = Fields!Date2.Value, Fields!Name.Value,
Nothing))
Released_DT = a date - 09/03/2015 or 09/02/2015
Date2 = returns another date value in this case 09/03/2015
What I'm trying to do is: count distinct number of people (Fields!Name.Value) if the Relased_DT = Date2.My IIF statement is returning a zero value.
View 4 Replies
View Related
Apr 5, 2007
Hi every body...
I have a probleme
I have a web Services which contains a method getValue(IDEq (int), idIndicator(int), startTime(dateTime), endTime(dateTime))
I need to call this method. But my problem is how pass parameter ?
I see the tab Param but it isn't work as I wait,... maybe I do a mistake...
I want that statTime and endTime are select by the user via a calendar for example...
now idIndicator and idEq was result of an other dataSet from a xml datasource...
But I don't how integrate dynamically... I try to enter a parameter via the param tab, and create and expression :
=First(Fields!idEq.Value, "EquipmentDataSet")
but when i execute the query, the promter display <NULL>...
So I don't know how to do and if it is possible !
I hope someone can help me !
Thank you !
View 3 Replies
View Related
Dec 3, 2007
Hi experts,
I'm not sure my design is normal or not. Please give me some advice.
I've a dataset and query by a field name 'companyid'.
select * from companyid where companyid = @icompany which @icompany is a input field.
if user select all, i'll send 0 to @icompany then I need to select all records.
question 1. How can I get all records? (i think about this query select * from companyid <> 0)
if user select for example companyid = 1, i'll send 1 to @icompany and the query work fine.
question 2. How can I change the query to adopt this 2 condition?
Thanks a lot,
Jeff
View 8 Replies
View Related
Jun 9, 2004
I'm banging my head against the wall. It's probably pretty simple so here goes...
I have a form that supposed to add records to a table on the database. I'm pretty sure I have the dataAdapter and Dataset set right but when I add a record, it complains that the primary key can’t be null. I’ve tried different things, including removing those fields and variables from the stored Proc, changing the Identity Properties of the Table, changing the AutoIncrement settings on the Dataset for that field, but all give me different errors. Nothing actually makes it work. This database has many records in it and the Primary key is a decimal field that starts at 5, then next was 10, then 28, then it started getting consecutive after 110. (111, 112, 113, etc.) (I'm porting over an old database to a new web site, so I'm somewhat contrained by the database.)
I guess my question is for adding records in to a database that has a primary unique key constraint. Certainly this is a pretty common thing. Obviously I do not want users to enter that in themselves, I thought it should simply autocreate/autonumber. So what is the best procedure and gotcha’s I need to watch out for?
View 7 Replies
View Related
Sep 16, 2004
if i have the following code, how do i know if the transaction was actually successful, so basically where in this code can i write TRANSACTION SUCCESFUL?
try
{
//Open up the connection
conn.Open();
//Setup the Transaction
trans = conn.BeginTransaction();
//First query in transaction
pre_query = "Delete from Menu where spec_name="
+ "'"
+ spec_name_array[i].Text.ToString()
+ "'" ;
//Second query in transaction
query = "INSERT into Menu VALUES ('"
+ Calendar1.SelectedDate
+"','"
+ spec_name_array[i].Text.ToString()
+"','"
+ spec_desc_array[i].Text.ToString()
+"','"
+spec_price_array[i].Text.ToString()
+"',1)";
SqlCommand comm = new SqlCommand(pre_query,conn);
//Setup the command to handle transaction
comm.Transaction = trans;
//Execute first query
comm.ExecuteNonQuery();
//Add in second query
comm.CommandText = query;
//Execute second query
comm.ExecuteNonQuery();
trans.Commit();
}
catch(SqlException ex)
{
com_label.Text = "Submission Not complete, did you forget the date or something else?";
//Undo all queries
trans.Rollback();
}
finally
{
conn.Close();
}
View 1 Replies
View Related
Aug 16, 2006
This may not be the right place for this post...but my head is hurting and I can't think right now.
I have a SQL table of users. Each record has a required 'Birthdate' field. I'd like to order this by the days left till the give birthday, keeping in mind that the year may be 1980 or their birthday might have been 2 weeks ago.
Can someone cure my headache?
View 1 Replies
View Related
Mar 3, 2006
I have written 2 custom connection mgr€™s.
One connects the data from an oracle source
One connects so I can put the data on a sql server.
These both work well and it makes it a lot easier across development, test, production.
But now I would like a good way to do this
I have 10 tables to copy from oracle. The only difference is just the name of the table.
static string[] g_tables =
{
"table_name_1",
"table_name_2",
"table_name_3",
€¦
};
foreach (string str_table in g_tables)
{
Oracle.DataAccess.Client.OracleCommand OracleCommand = new Oracle.DataAccess.Client.OracleCommand
(
"select * from " + str_table,
OracleConnection
);
Oracle.DataAccess.Client.OracleDataReader OracleDataReader =
OracleCommand.ExecuteReader();
// dump out to raw file destination
}
I understand there are some pieces left out but that is the basic idea. It seems like I€™m going to have write custom pieces for each step.
It does not seem like there is any advantage to using SSIS. Should I give up now and stick to writing windows services for stuff like this?
I could just write a specific piece for each table but the only difference is the table name.
View 13 Replies
View Related
Sep 5, 2006
I need to determine the top 20 most frequent occurences of a value in a specific field using SQL.
View 4 Replies
View Related
Oct 30, 2007
My table structure...
enddt datetime(8)
status varchar(20)
when enddt time equal to server time.
I want to change status field to expired.
What is the easy way to do this?.
enddt status
8/22/2005 7:00:00 PM expired
9/22/2005 8:30:00 PM expired
10/30/2005 7:30:00 PM live
11/22/2005 9:30:00 PM live
View 6 Replies
View Related
Sep 18, 2007
I completely forgot how to do this. I want one of my groups in the matrix to take its week number (1-7) as Monday-Sunday.
so it would kind of be like this:
=IIF(Fields!DayOfWeek.Value=2,"Mon",
ELSE(Fields!DayOfWeek.Value=3,"Tues",
Else(Fields!DayOfWeek.Value=4,"Wed",
etc etc
Am i doing this right, or am i off??
View 4 Replies
View Related
Sep 5, 2007
If I have a 64 bit version of SQL 2005 installed, will "select @@version" indicate it is a 64 bit version? If not, how do I determine such? Thanks in advance.
View 3 Replies
View Related
Oct 4, 2007
I have a column in my report that i want to show percentage change from the previous year to this year. So i enter all kinds of different type of formats for the formula, and keep getting the wrong answers or error messeges. Here's what i have tried recently:
=SUM((Fields!LYTD_Amount.Value - Fields!YTD_Amount.Value) (Fields!YTD_Amount.Value*100))
Its pretty much Last year to date Minues Year to Date , Divided by Year to Date, Multiplied by 100.
any suggestions? Everytime i try to put an aggregate in the box, its always tricky.
View 13 Replies
View Related
Dec 21, 2006
hi,
I have this simple sql query - it should be pretty obvious what I'm trying to achieve but this syntax isn't accepted on SQL 2005, any suggestions?
SELECT Name FROM Users WHERE UsersID IN (EXEC dbo.ReturnDataByModule 'Groups',1200)
View 6 Replies
View Related
Oct 19, 2007
What is the equivalent of Double for SQL Server?
View 1 Replies
View Related
Mar 16, 2008
Ok - I am still a bit weak on SQL. My understanding of FK relationships in a database are to reinforce the data integrity - Correct? For example, does it make sure that the id given for SiteID does indeed exists. Can it link tables like JOIN in select statements? If not, is there any gains by creating them, such as performance?
View 1 Replies
View Related
Mar 16, 2008
scenario I have 2 tables one is called "Site" which contains columns ID, Address1, Address2,City,StateID,ZipCode and the second table is called "State" which contains columns ID,Name,Abbreviation. I am looking to make a view that contains all the sites with the state inner joined on stateid to make my all my other queries easier. That is so I can just select from the view without having to do the inner join on the state. My question is when a new record is added to table Site do I have "recreate"/update the view?
View 3 Replies
View Related
Nov 21, 2004
I have a simple SQL table. The data looks like this:
products_ID----category----product_local
1----CORE----0
2----BASE----0
3----BRANCH----1
4----LEAF----3
I need to create a SQL statement that produces all category items where that ROW's products_ID is not found in ANY product_local in the table. So in the table above only the category BASE and LEAF would be printed because their products_ID are not found in any product_local in the table.
TIA as I am completely stuck!
View 1 Replies
View Related
Nov 30, 2004
OK, I have two cols of data:
COL1 COL2
test ing
tams ert
test pan
ted ted
hom jis
ted sam
tams dom
test ut
I need to simply pull one row of data from the given rows. Whats in COL2 is not relevant. So an example would be:
I want to retrieve a row(any row, but only one) where COL1 = 'test'
An ecceptable result woould be:
COL1 COL2
test ing
or
COL1 COL2
test pan
or
COL1 COL2
test ut
I would appreciate any help. This is driving me nuts. Seems like it would be easy.
TIA,
STue
View 4 Replies
View Related
Mar 1, 2001
Hi there,
I'm using bcp to import data into my database. the datetime format in the bcp file is ymd and my database has mdy datetime format. Is there any easy way to convert ymd into mdy when using bcp?
thanks a lot for your help!
Lena
View 2 Replies
View Related
Apr 5, 1999
Is there an easy way to update a Value field
for all records in One Table from a Value Field in another?
Both Tables have the same number of records.
View 3 Replies
View Related
Aug 2, 2002
Hi,
the following Insert statement is not acting like i would expect it. The final column (Average) divides two columns([Sum of Scores] / [Count Of Scores]) . The Average column is a numeric column with 5 decimal places. Yet, the result of the division is a whole number (no fractions). Why is this.
any help would be appreciated
Thanks,
Jim
Insert [Bankers_Scores_lead] ([ID], EqorMA, Region, Period,IBFirm, UndNeed ,QualIdea ,ProfAggr ,SpKnow ,negskill ,Perchem, RepPri, Repcomp, TranType, [Count Of Scores], [Sum of Scores], Average)
SELECT [ID],
EqorMA,
Region,
Period,
IBFirm,
UndNeed,
QualIdea,
ProfAggr,
SpKnow,
negskill,
Perchem,
RepPri,
Repcomp ,
TranType,
(Case when [UndNeed] >0 then 1 Else 0 END)
+(Case when [QualIdea] >0 then 1 Else 0 END)
+(Case when [ProfAggr] >0 then 1 Else 0 END)
+(Case when [SpKnow] >0 then 1 Else 0 END)
+(Case when [Perchem] >0 then 1 Else 0 END)
+(Case when [negskill] >0 then 1 Else 0 END)
,
(ISNULL([UndNeed],0))
+(ISNULL([QualIdea],0))
+(ISNULL([ProfAggr],0))
+(ISNULL([SpKnow],0))
+(ISNULL([negskill],0))
+ (ISNULL([Perchem],0))
,
((
(ISNULL([UndNeed],0))+
(ISNULL([QualIdea],0))+
(ISNULL([ProfAggr],0))+
(ISNULL([SpKnow],0))+
(ISNULL([Perchem],0)) +
(ISNULL([negskill],0)
))
/((Case when [UndNeed] >0 then 1 Else 0 END)
+(Case when [QualIdea] >0 then 1 Else 0 END)
+(Case when [ProfAggr] >0 then 1 Else 0 END)
+(Case when [SpKnow] >0 then 1 Else 0 END)
+(Case when [Perchem] >0 then 1 Else 0 END)
+(Case when [negskill] >0 then 1 Else 0 END)
))
FROM tmp_Bankers_Scores
Here is the create table statement
CREATE TABLE [dbo].[Bankers_Scores_lead] (
[ID] [int] NOT NULL ,
[EqorMA] [smallint] NOT NULL ,
[Region] [smallint] NOT NULL ,
[Period] [int] NULL ,
[IBFirm] [int] NULL ,
[UndNeed] numeric(8,5) NULL ,
[QualIdea] numeric(8,5) NULL ,
[ProfAggr] numeric(8,5) NULL ,
[SpKnow] numeric(8,5) NULL ,
[negskill] numeric(8,5) NULL ,
[Perchem] numeric(8,5) NULL ,
[RepPri] numeric(8,5) NULL ,
[Repcomp] numeric(8,5)NULL ,
[TranType] [smallint] NULL ,
[Count Of Scores] numeric(8,5) NULL ,
[Sum of Scores] numeric(8,5) NULL ,
[Average] numeric(8,5) NULL
) ON [PRIMARY]
here is the output
ID EqorMA Region Period IBFirm UndNeed QualIdea ProfAggr SpKnow negskill Perchem RepPri Repcomp TranType Count Of Scores Sum of Scores Average
----------- ------ ------ ----------- ----------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- -------- --------------- ------------- ----------
75659 5 2 9 504 9.00000 8.00000 7.00000 8.00000 8.00000 8.00000 1.00000 .00000 11 6.00000 48.00000 8.00000
75659 5 2 9 504 9.00000 8.00000 7.00000 8.00000 8.00000 8.00000 1.00000 .00000 11 6.00000 48.00000 8.00000
79360 6 5 9 501 9.00000 8.00000 9.00000 8.00000 8.00000 8.00000 1.00000 .00000 15 6.00000 50.00000 8.00000
79360 6 5 9 501 9.00000 8.00000 9.00000 8.00000 8.00000 8.00000 1.00000 .00000 15 6.00000 50.00000 8.00000
View 1 Replies
View Related
Aug 15, 2007
Hi I'm new to SQL and I'm stuggling with a simple query in MS-server 2005 and wondered if anyone can help me.
I'm trying to devide the ansewrs of two seperate queries, but both the queries use the same coloumn in the where clause to get the answer. Please can some one help!
eg.
select Sum(column1)
where column1 = 'x'
devided by
select Sum(column1)
where column1 = 'y'
View 2 Replies
View Related
Jul 1, 2004
hi.. guys this is simple for you guys how can i make ms access to display the current date in a col.... when i go to insert a new line i how can i make it so that its allready there? i dont have to type it??????
View 3 Replies
View Related
