Reading Results Into .NET

May 20, 2008

Hi. I want to write a function to retrieve all records from a "Parts" table so that I can read these records in .NET.

SqlDataReader reader = sqlCmd.ExecuteReader()

while( reader.Read() )
{
int x = reader["id"];
string partName = reader["name"];

// Do stuff with data here //
}


My question: How should the function be written?

Should I create a PROCEDURE and just call a SELECT statement?

CREATE PROCEDURE getPartsList()
AS
SELECT part_id as 'id', part_name as 'name' FROM parts


Or should I create a FUNCTION that returns a TABLE? If so, is this the correct syntax?

CREATE FUNCTION getPartsList()
RETURNS TABLE
AS
RETURN (SELECT part_id as 'id', part_name as 'name' FROM parts)


Thanks for reading

View 3 Replies


ADVERTISEMENT

Reading Results From Sys.dm_db_index_physical_stat

Mar 6, 2007

Could you help me understand the results of running sys.dm_db_index_physical_stats? select top 10 * from sys.dm_db_index_physical_stats(db_id('web_db1'), OBJECT_ID('tblUser'), NULL, NULL, 'DETAILED');

I see this table. Two HEAP indexes seem to have issues. Shall I simply rebuild the index for this table. How may I convert a table to have a clustered index if it has lots of transactions?


obj_id | avgFrag% | frag_cnt | avgFragPages | avgPageSpace% | recCnt | index_type
2157511501119.2518HEAP
215751150113.3118NONCLUSTERED INDEX
4733923368.211229.0694.6222716HEAP
4733923301926.1699.63222716NONCLUSTERED INDEX
4733923301273.66497NONCLUSTERED INDEX
473392330110.572NONCLUSTERED INDEX
4733923302124.999.94222716NONCLUSTERED INDEX
4733923301280.74523NONCLUSTERED INDEX
473392330110.592NONCLUSTERED INDEX
5357522933.33519.298.9623306HEAP

View 2 Replies View Related

Is There A Way To Hold The Results Of A Select Query Then Operate On The Results And Changes Will Be Reflected On The Actual Data?

Apr 1, 2007

hi,  like, if i need to do delete some items with the id = 10000 then also need to update on the remaining items on the with the same idthen i will need to go through all the records to fetch the items with the same id right?  so, is there something that i can use to hold those records so that i can do the delete and update just on those records  and don't need to query twice? or is there a way to do that in one go ?thanks in advance! 

View 1 Replies View Related

SQL Server 2008 :: Elegant Way For Returning All Results When Subquery Returns No Results?

Mar 25, 2015

I have four tables: Customer (CustomerId INT, CountyId INT), County (CountyId INT), Search(SearchId INT), and SearchCriteria (SearchCriteriaId INT, SearchId INT, CountyId INT, [others not related to this]).

I want to search Customer based off of the Search record, which could have multiple SearchCriteria records. However, if there aren't any SearchCriteria records with CountyId populated for a given Search, I want it to assume to get all Customer records, regardless of CountyId.

Right now, I'm doing it this way.

DECLARE @SearchId INT = 100
SELECT * FROM Customer WHERE
CountyId IN
(
SELECT CASE WHEN EXISTS(SELECT CountyId FROM SearchCriteria WHERE SearchId = @SearchId)
THEN SearchCriteria.CountyId

[Code] .....

This works; it just seems cludgy. Is there a more elegant way to do this?

View 4 Replies View Related

Need To Display Results Of A Query, Then Use A Drop Down List To Filter The Results.

Feb 12, 2008

Hello. I currently have a website that has a table on one webpage. When a record is clicked, the primary key of that record is transfered in the query string to another page and fed into an sql statement. In this case its selecting a project on the first page, and displaying all the scripts for that project on another page. I also have an additional dropdownlist on the second page that i use to filter the scripts by an attribute called 'testdomain'. At present this works to an extent. When i click a project, i am navigated to the scripts page which is empty except for the dropdownlist. i then select a 'testdomain' from the dropdownlist and the page populates with scripts (formview) for the particular test domain. what i would like is for all the scripts to be displayed using the formview in the first instance when the user arrives at the second page. from there, they can then filter the scripts using the dropdownlist.
My current SQL statement is as follows.
SelectCommand="SELECT * FROM [TestScript] WHERE (([ProjectID] = @ProjectID) AND ([TestDomain] = @TestDomain))"
So what is happening is when testdomain = a null value, it does not select any scripts. Is there a way i can achieve the behaivour of the page as i outlined above? Any help would be appreciated.
Thanks,
James.

View 1 Replies View Related

Stored Proc Results Are Displaying In The Messages Tab Instead Of Results Tab- URGENT

May 14, 2008




Hi All,
I have a stored proc which is executing successfully...but the results of that stored proc are displaying in the Messages Tab instaed of results Tab. And in the Results Tab the results shows as 0..So, Any clue friends..it is very urgent..I am trying to call this stored proc in my Report in SSRS as well but the stored proc is not displaying there also...Please help me ASAP..

Thanks
dotnetdev1

View 4 Replies View Related

Mind-boggling Gridview Results! Different Results For Different Teams..

Jun 18, 2008

Hi all, I have the following SQLDataSource statement which connects to my Gridview:<asp:SqlDataSource ID="SqlDataSourceStandings" runat="server" ConnectionString="<%$ ConnectionStrings:ConnectionString %>"  SelectCommand="SELECT P.firstName, P.lastName, T.teamName, IsNull(P.gamesPlayed, 0) as gamesPlayed, IsNull(P.plateAppearances,0) as plateAppearances, IsNull( (P.plateAppearances - (P.sacrifices + P.walks)) ,0) as atbats, IsNull(P.hits,0) as hits, P.hits/(CONVERT(Decimal(5,2), IsNull(NullIF(P.atbats, 0), 1))) AS [average], (P.hits + P.walks)/(CONVERT(Decimal(5,2), IsNull(NullIF( (P.atbats + P.sacrifices + P.walks) , 0), 1)))  AS [OBP], (P.hits - (P.doubles + P.triples + P.homeRuns) + (2 * P.doubles) + (3 * P.triples) + (4 * P.homeRuns)) / (CONVERT(Decimal(5,2), IsNull(NullIF(P.atbats, 0), 1))) AS [SLG], P.singles, P.doubles, P.triples, P.homeRuns, P.walks, P.sacrifices, P.runs, P.rbis FROM Players P INNER JOIN Teams T ON P.team = T.teamID ORDER BY P.firstName, P.lastName"></asp:SqlDataSource>There are 8 teams in the database, and somehow the average and obp results are as expected for all teams except where T.teamID = 1.  This doesn't make sense to me at all!  For example, I get the following results with this same query: First NameLast NameTeamGPPAABHAVGOBPSLG1B2B3BHRBBSACRRBI

BrianAustinHope83432230.7187500.7352941.15625014612201221

GabrielHelbigSafe Haven62119141.0000000.9375002.1428576404111519

MarkusJavorSafe Haven82927200.8695650.8000001.21739114501021218

RobBennettMelville83029240.8275860.8333331.55172411904102117

AdamBiesenthalSafe Haven82929210.9130430.9130431.56521712631001015

ErikGalvezMelville82625180.7200000.7307691.24000011322101015 As you can see, all teams except for Safe Haven's have the correct AVG and OBP.  Since AVG is simply H/AB, it doesn't make sense for Gabriel Helbig's results to be 1.00000. Can anyone shed ANY light on this please?Thank you in advance,Markuu  ***As a side note, could anyone also let me know how I could format the output so that AVG and OBP are only 3 decimal places? (ex: 0.719 for the 1st result)*** 

View 2 Replies View Related

Removing Individual Results From A Paged Set Of Results.

Oct 19, 2007

Hi,
I have a web form that lets users search for people in my database they wish to contact. The database returns a paged set of results using a CTE, Top X, and Row_number().
I would like to give my users to option of removing individual people from this list but cannot find a way to do this.
I have tried creating a session variable with a comma delimited list of ID's that I pass to my sproc and use in a NOT IN() statement. But I keep getting a "Input string was not in a correct format." Error Message.
Is there any way to do this? I am still new to stored procedures so any advice would be helpful.
Thanks
 

View 3 Replies View Related

PASTE SQL RESULTS INTO EXCEL - Funny Results

Jan 30, 2008

Hi, when I copy and paste results from query analyzer into Excel it appears that values with zeroes at the end loose the zeroes. Example, if I copy and paste V128.0 into an Excel cell it comes out as V128 or if I copy 178.70 it displays as 178.7 - any ideas? I'm using SQL Enterprise Manager for 2000.

View 6 Replies View Related

Reading An Excel Doc

Jul 16, 2007

hi guys, Can anyone advise me how how to read an excel doc i have stored locally? I need to be able to start the read from say row number 6 and finish the read once i get to a row that contains a pre-determined  word signifying the end of processing. I intend to store the parsed data in an array that will be used as the data source for a gridview or repeater object on another asp page. I'm using ASP.net 2.0 and C# by the way. thanks in advance for any help!!! 

View 12 Replies View Related

DataReader Not Reading

Jan 13, 2004

Why won't this dataReader read?

Dim objCon2 As New SqlConnection()
objCon2.ConnectionString = "a standard connection string"
objCon2.Open()

Dim objCommand As SqlCommand
objCommand = New SqlCommand(strSQL, objCon2)
Dim objReader As SqlDataReader
objReader = objCommand.ExecuteReader()

Label1.Text = objReader("email")

strSQL is a select command which I've checked (using SQL Query analyzer) does return data. I know the connection string is valid (and I presume if it wasn't that it'd fail on objCon2.open, which it doesn't).

So why oh why do I get this error on the last line (and yes, there is an "email" field in the contents of the reader)

System.InvalidOperationException: Invalid attempt to read when no data is present.

View 1 Replies View Related

Help With Reading Database.

Jun 29, 2005

I have this code that I hacked together from someone else's example.  I kind of understand how it works.  I just don't know if it will and i am not in a location right now to check.  I was wondering if I did this correctly first, second how can it improve and should i do something different.  Basically i just want to check the password in a database. I am passing the username and password to this function from another functioprivate bool authUser(string UserName, string Password){ string connectionString = ConfigurationSettings.AppSettings["DBConnectionString"];  SqlConnection DBConnection = new SqlConnection(connectionString);  bool result = false;  DBConnection.open() SqlCommand checkCommand = new SqlCommand("SELECT password FROM Users WHERE userName='" + Password + "', DBConnection) SqlDataReader checkDataReader = checkCommand.ExecuteReader();
 if(checkDataReader.GetString(0) == Password) {  result = true; } else {  result = false; } checkDataReader.Close(); DBConnection.Close();
 return result;}Thank you Buddy Lindsey

View 6 Replies View Related

Reading Transaction Log

May 30, 2001

Does anyone know how to read transaction log besides trace and profiler. The current situation is some one in our org. deleted an item and I'm trying to find out who and when.

View 2 Replies View Related

Reading Log File

Feb 9, 2000

I have some records that have been deleted. I need to find out who did it and to do that I need to read the logs. Are there any utilities that will allow me to read login 7.0? How about 6.5?

Chris

View 2 Replies View Related

Reading The Transaction Log In SQL 7.0

Dec 22, 1999

Here is I think an interesting question
is there a way to read or access the transaction log of a
database in SQL server 7.0

Hoping someone has a solution :-)

View 5 Replies View Related

Reading The Transaction Log

Nov 2, 1999

We are having continual problems with our transaction log filling up on one of our major applications.
Does anyone know of a way or tool to read the transaction log? We want to determine what is causing this problem.

Thanks

View 3 Replies View Related

Reading The Transaction Log

Jul 12, 1999

Is there a way, in SQL 7.0 to print out or view what's in the Transaction Log? In 6.5 you could view the table syslogs, but I don't see any documentation anywhere on how to do this in 7.0.

View 1 Replies View Related

Reading Xml In A Sql Table

Apr 8, 2005

I receive in a table a field which is an xml string
like below

<NewOrder><SiteID>CJC</SiteID><patID>458887</patID><LName>Cronin</LName><FName>tim</FName><EntryID>{7B1A4946-CEC8-4F23-AE89-5C70A6A0F9B2}</NewOrder>

Is there a way read this field with a select statement? I need to strip out the entryid value in a trigger

View 4 Replies View Related

Reading SQL 6.5 Databases

Aug 23, 2001

I received a SQL Server 6.5 database (.dat file) on a removeable drive. I'm currently running 7.0. Is there any way to read/import/link to this data without installing 6.5. And if I have to install 6.5 how do I get the system to see the database?

View 1 Replies View Related

Reading MS-SQL Transaction Log

May 24, 2002

Hello people;


Somebody knows some utility where I can read the Transaction Log Sql 2000 ?

Thanks;


Navlig®

View 1 Replies View Related

Reading A Ini File In DTS

Jun 10, 2002

Hi

How can I read an ini file entry and pass this parameter to a stored procedure which will be run in a DTS Package?


Thanks for the input

mipo

View 2 Replies View Related

Reading DTS Packages

Sep 19, 2001

Anyone know a way to read DTS packages? I have inherited a DTS package, saved in SQL, and am trying to find a way to read the steps without having to view every single step through the GUI.

Thanks in advance.

View 3 Replies View Related

Reading ClientProcessID

Sep 1, 2006

Hi,

I need to know that how can we read ClientProcessID (älso use used in SQL Profiler).

Regards,
Shabber.

View 2 Replies View Related

Reading XML Element

Jun 9, 2014

The requirement is to read XML element from database column.The column looks like

<ClMetadataDataContract xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns="http://www.cch.com/pfx.net/psi/">
<EntityType>1</EntityType>
<NameLine1>David Jones</NameLine1>

[code]....

I have also tries OPENXML but no luck

View 2 Replies View Related

Reading Log File

Jun 22, 2008

How do I view the SQL 2005 transaction log file(.ldf)?

Is there a built-in utility?

Thank you

View 3 Replies View Related

Reading A File

Jun 24, 2008

I am storing one text file on the server.This text file contains some mobile numbers.The file look like
009198XXXXXXXX
009198XXXXXXXX
009198XXXXXXXX
009198XXXXXXXX
etc

Here I need to read this text file row by row mobile number and then insert these mobile numbers into a table by using sql procedure or sql trigger or any other method or coding. Is it is possible or not?
If so then anybody can help me!

regards
Shaji

View 4 Replies View Related

Reading ID From XML File???

Jul 13, 2006

Hello friends...

We have sql server 2000 and i need to read one perticular xml and from that i want ID field stored in one table...is it possible by query analyzer to read xml and stored ID in table...I have only path of that xml file...



like



select ID from ("D:XMLFolder*.xml)



that i want

View 2 Replies View Related

SP Is Not Reading The Table Name?

Aug 23, 2006

-- drop proc SP_Trio_Popul_Stg_Tbls1
CREATE PROC [dbo].[SP_Trio_Popul_Stg_Tbls1] @tableName varchar(20) AS
-- alter PROC [dbo].[SP_Trio_Popul_Stg_Tbls1] @tableName varchar(20) AS


declare @sql varchar (20)

set @sql = 'INSERT INTO dbo.Name_Pharse_Stg_Tbl1 (nm_last)
SELECT nm_name FROM dbo.' + quotename(@tableName)

print @sql
exec (@sql)

----------------------------------------------------------------------
exec SP_Trio_Popul_Stg_Tbls1 '00485'
----------------------------------------------------------------------
INSERT INTO dbo.Name
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near 'Name'.


im trying to run this SP but im getting an error. Can you help me to fix it?

-------------------------
gongxia649
-------------------------

View 20 Replies View Related

Reading From A Xl Sheet

Jul 17, 2007

Hi All,

I would like to know if i can read data from xl sheet using a stored procedure?

Thanks in advance

vishu

View 17 Replies View Related

Recommend Some Reading

Jul 25, 2007

Hi,
I've been a developer for 20 some years, but never learned the design steps of a complex database. Can you give me a book or 2 to bring me up to speed with the latest database design and data modeling techniques?

thanks.....

View 3 Replies View Related

Reading A Directory

Feb 28, 2007

I do a lot of file processing and I usually run a little script I copyand paste to read directory information to see if a new file it thereand then process the file if it is. So, I decided to wise up and makea stored procedure to automate a lot of that.The pivotal step in this is that i run a command that looks like:CREATE TABLE #DIR (FileName varchar(100))DECLARE @Cmd varchar(1050)SET@Cmd = 'DIR "' + @Path + CASE WHEN RIGHT(@Path, 1) = '' THEN ''ELSE '' END + @WildCard + '"'INSERT INTO #DIREXEC master..xp_CmdShell @CmdWhen I run the stored procedure I get back the files and folders inthere that match the wildcard and all is good!!!!....Until I try to put that information into a table while calling thatstored procedure:CREATE TABLE #Files (Path varchar(100),FileName varchar(100),PathAndFileName varchar(150),FileDateTime SmallDateTime,FileLength int,FileType Varchar(10))INSERT INTO #FilesEXEC sp_GetFileNames @Path = '\isoft2ftpLegacyBilling', @Wildcard= '*.txt'When I run this I get:Server: Msg 8164, Level 16, State 1, Procedure sp_GetFileNames, Line53An INSERT EXEC statement cannot be nested.Because I use an INSERT EXEC to with the results from the @Cmd.Anybody have any ideas how I can get that information into a table?I did try to just copy the data to c: empdir.txt and then bulkimport it in. But when it runs the @Cmd to create the file it comesback with a NULL value and my stored procedure returns two sets ofvalues... which I can't do.So, I would appreciate anybody who can help.Thanks!-utah

View 4 Replies View Related

Reading Transaction Log.

Jul 20, 2005

Hi,How can i read information in Transaction Log in SQL Server 2000?Thanks

View 1 Replies View Related

Reading SQL Data

Jul 6, 2005

I posted this in the Windows Forms Data Binding section, and they directed me to the .Net data section.  I posted it there and waited a week with no replies.  I'm hoping someone here can at least give me an idea of what the problem might be.

View 1 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved