Select Case Does Not Evaluate The Value Returned By Datareader Or Dataset

Sep 13, 2006

I am working on a company sign out sheet for our Intranet. The application accesses a sql database.  I tried using a datareader as well as a dataset to access a small piece of information regarding the group that each employee belongs to in the company.  My last attempt was to assign the value to the text property of a text box and evaluate the text from the text box with the Select Case but that did not work either.The application is supposed to generate an email to our receptionist as well as to the group technical assistant responsible for each group according to the value that is passed into the Select Case statement.  With the datareader as well as the dataset and even now with creating a text box and accessing the text property, I was able to assign the group value to a variable (I can see the value in the subject of the email), but when the variable is supposed to be evaluated by the select case statement it skips right through all of the cases to case else and uses only the receptionist's address in the email as if it doesn't even see the value of the variable.  
 I have searched for a possible answer to the problem I am but so far have had no luck.  Any ideas? 
 
I included the part of the code that I am having trouble with:
 
'********************************************************************************************
' New DataSet is created to determine the group the employee belongs to so that the appropriate
' group technical assistant is emailed when employee signs out
'********************************************************************************************
 
' declare variables
Dim sqlGroup As String ' string variable to store sql statement for dataset
Dim BLGroup As String ' string variable to store the bl group of the employee
Dim emailAddress As String ' string variable to store resulting email address
 
Dim groupDS As DataSet ' dataset variable
Dim groupAdapter As SqlDataAdapter ' sql data adapter variable
' sql statement to access group value from the database
sqlGroup = "SELECT BLGroup FROM BLGroupsView WHERE (RTRIM(fname) + ' ' + LTRIM(lname)='" & employee & "')"
 
' repopen the connection (leftover from working with the datareader)
Connection.Open()
' create new SqlDataAdapter applying sql statement and connection
groupAdapter = New SqlDataAdapter(sqlGroup, Connection)
' create new dataset 
groupDS = New DataSet()
' populate the dataset
groupAdapter.Fill(groupDS, "BLGroupsView")
 
' get the value stored in the BLGroup column (only one row is returned at a time) 
BLGroup = groupDS!BLGroup
' Create GroupTextBox TextBox control.
Dim GroupTextBox As New TextBox()
' Set options for the UserTextBox TextBox control.
GroupTextBox.ID = "GroupTextBox"
GroupTextBox.Visible = "False"
GroupTextBox.Text = BLGroup
' just trying anything with this next line, even when i didn't set this the select case statement did not seem to see the value returned by the variable
GroupTextBox.runat = "server"
 
' use a select case statement to evaluate the value of the group (didn't work when I passed in the BLGroup variable either
Select Case GroupTextBox.Text
Case "bh"
emailAddress = "receptionist@myaddress.com; bhassistant@myaddress.com"
Case "env"
emailAddress = "receptionist@myaddress.com; envassistant@myaddress.com"
Case "sw"
emailAddress = "receptionist@myaddress.com; swassistant@myaddress.com"
Case "fac"
emailAddress = "receptionist@myaddress.com; facassistant@myaddress.com"
Case "www"
emailAddress = "receptionist@myaddress.com; wwwassistant@myaddress.com"
Case Else
emailAddress = "receptionist@myaddress.com"
End Select
 
'*************************************************************************************************
' set new message object
Dim myMessage As System.Web.Mail.MailMessage = New System.Web.Mail.MailMessage()
'**********************************************************
' set message properties
'**********************************************************
myMessage.From = "me@myaddress.com"
myMessage.To = emailAddress
myMessage.Subject = employee & " Signing Out at " & TimeOut.Text & " " & BLGroup
myMessage.Body = employee & " Signing Out at " & TimeOut.Text & " for " & Destination.Text & " will potentially return at " & EstTimeIn.Text
'***********************************************************
' set smtp server name
smtpMail.SmtpServer = "mailserver1"
' send email using smtp
smtpMail.Send(myMessage)
 It sends the message and returns the BLGroup value everytime but only sends to the receptionist.  Thanks for your time!
 

View 1 Replies


ADVERTISEMENT

Select A Subset Of Data From The Dataset Returned By A Union Query

Mar 30, 2004

hi all

Any day, another question. I love you guys.

I want to select a subset of data from a dataset returned by either another subquery or a union.

e.g. this is how i would do it in oracle, but i have no idea how this can be done in mssql or whether it's possible at all.

select * from
(
select col1, col2, col3 from table1
union
select col1, col2, col3 from table 2
)
where col1 = 'blah'

in essence oracle treats the data returned by the subquery as a table that it would select from.


how would i do the same in mssql?

thank you


James :)

View 5 Replies View Related

Reporting Services :: Evaluate Two Values That Came From Different Dataset

Sep 17, 2015

I have an ssrs (report builder) with 2 dataset. the first dataset is a summary if records which the report has a column name qty and i put also a total qty summary in the last rows. the second dataset is a raw data and have a column name qty, also i put a total qty summary in the last row.  The requirements is to be able to evaluate or check the total qty under dataset1  from total qty of dataset2 if equal else if not equal i have to make the font as red so that the user will inform that the total qty has a discrepancy. the users will validate from raw data which are the one items that have a missing qty. How to work on this or is this appilcable in report builder.

View 4 Replies View Related

Deleting Records Returned By Datareader

May 8, 2005

I have a function that opens a connection to an SQL database, issues a
SELECT command, and reads the records with an OleDbDataReader. As the
records are read, any that match certain criteria are deleted with a
DELETE command. Simplified example code is shown below:

Dim dbCmd As OleDb.OleDbCommand = New OleDb.OleDbCommand()
dbCmd.Connection = New OleDb.OleDbConnection(UserDbConnString)
dbCmd.CommandText = "SELECT * FROM [User] ORDER BY UserID"
dbCmd.Connection.Open()
Dim reader as OleDb.OleDbDataReader = dbCmd.ExecuteReader(CommandBehavior.CloseConnection)
While reader.Read()
    If reader("SomeColumn") = SomeCalculatedValue Then
        Dim dbCmd2 As OleDb.OleDbCommand = New OleDb.OleDbCommand()
        dbCmd2.Connection = New OleDb.OleDbConnection(UserDbConnString)
        dbCmd2.CommandText = "DELETE FROM [User] WHERE UserID = " + reader("UserID")
        dbCmd2.Connection.Open()
        dbCmd2.ExecuteNonQuery()
        dbCmd2.Connection.Close()
    End If
End While
reader.Close()

This code worked well with an MS Access database, but when I changed to
SQL Server, I get a database timeout error when attempting to do the
DELETE. I suspect the reason is that the connection the reader has open
has the record locked so it cannot be deleted.

The SQL connection string I am using is something like this:

UserDbConnString = "Provider=SQLOLEDB; Server=(Local); User ID=userid; Password=password; Database=dbname"

The connection string I used for MS Access included the property
"Mode=Share Deny None". I wonder if there is some similar way to tell
SQL Server to allow editing of records that are open for reading with
an OleDbDataReader.

Any help would be appreciated.

View 3 Replies View Related

How Can I Group Data Returned By A Datareader By A Particular Field

Apr 23, 2007

How can i group the data returned by statement below by "Dept" field. I have already tried to group it like shown below but that doesn't seem to work for me. Any help.
cmd_select = New SqlCommand("SELECT Incident_id,Dept From Report_Incident Group By Dept,Incident_id", myconnection_string)

View 3 Replies View Related

Dataset Or Datareader?

Jun 20, 2007

i need help to know what is the best practice
i have a stored proc which returns 4 different resultselts
will that be easy to use dataset or datareader?
my purpose of using dataset/datareader is to load the data in a class
thanks.
 

View 5 Replies View Related

Datareader And Dataset

Nov 3, 2003

Hi

I am using a datareader to access data via a stored procedure. The reason for using the datareader is that the stored procedure is multi level depending on the variable sent to it. However I want to do two things with the data being returned.

The first is to poulate a datagrid - which I've done.
The second is to produce an Infragistic Web Graph. However according to the background reading I have done so far, I can only populate the graph from one of the following: datatable,dataview,dataset,Array or Ilist.

I don't want to make another call to the server for the same information, so how can I get the data out of a stored procedure into a dataset or dataview?

regards

Jim

View 1 Replies View Related

About Datareader And Dataset Combine Use

Dec 26, 2007

i have 2 database queries. One fetches data from a table and other according to first query result second query fetches record from another table both of these queries make use of datareader and while loop for inserting data to dropdownlist , but instead of that i want to add all my row fetched by second query store one by one in a dataset for some time .in simple words i want to insert datareader record one by one in dataset. i am using asp.net2.0,c#, Sql server 2000 

View 2 Replies View Related

Datareader Insted Of Dataset Sored Procedure

Jan 10, 2007

i m writing a stored procudrue to update my data that is onther
table.and i pass the parameter in my vb code,when i pass the data that
is insert only first record of data but second record insert the eroor
will come is data reader is colsed. now insted of data reade i have to
use data set how can i use that and update my data is ontehr
table.?below i written my vb.net2005 code.      Dim con As New SqlConnection(ConfigurationManager.ConnectionStrings("Project1connectionString").ToString())            '        con.Open()            '        Dim ggrnid As String            '        Dim acceptqty As String            '        Dim itemid As String            '        Dim grnid As TextBox = CType(GRNDetailsView.FindControl("fldgrnid"), TextBox)            '        ggrnid = grnid.Text            '        Dim sWhere As String = grnid.Text            '        If (Not String.IsNullOrEmpty(sWhere)) Then            '            For Each s As String In sWhere '            '                'Dim iRowIndex As Integer = Convert.ToInt32(s)            '                Dim sqldtr As SqlDataReader            '                sqlcmd = New SqlCommand            '                sqlcmd.Connection = con            '                sqlcmd.CommandType = CommandType.Text           
'                sqlcmd.CommandText = "select acceptqty,itemid from
grndetail  where grnid='" & Trim(ggrnid) & "'"            '                datacommand = CommandType.StoredProcedure            '                'datacommand("aaceptqtygrn", con)            '                Dim cmd As New SqlCommand("aaceptqtygrn", con)            '                sqldtr = sqlcmd.ExecuteReader()            '                'dataset = datacommand.            '                'sqldtr = sqlcmd.ExecuteScalar            '                If sqldtr.HasRows = True Then            '                        While sqldtr.Read()            '                        acceptqty = sqldtr.Item("acceptqty")            '                        itemid = sqldtr.Item("itemid")            '                        cmd.CommandType = CommandType.StoredProcedure            '                        cmd.Parameters.AddWithValue("@acceptqty", acceptqty)            '                        cmd.Parameters.AddWithValue("@itemid", itemid)            '                        sqldtr.Close()            '                        cmd.ExecuteNonQuery()            '                    End While            '                    'sqldtr.Close()            '                    'cmd.ExecuteNonQuery()            '                    'Next sqldtr.HasRows            '                End If            '            Next s            '            sqldtr.Close()            '            con.Close()            '        End If            '    End If        Catch ex As Exception            MsgBox(ex.Message)        End Try

View 2 Replies View Related

Size Of Returned Dataset?

Jan 10, 2008

I'm using a SqlDataSource with a GridView to look at the contents of a table. I'd like to display the number of rows returned by the query in a label above the grid. How do I get a hold of this value? I can't find where it's exposed through the DataSource or the Grid. I can get the number of rows in the currently displayed page, but that's not the value I'm after.

View 6 Replies View Related

Missing Column In Returned Dataset.

Nov 21, 2006

I have a web page that has been in production for over a year.  On occasion the web app users report sporadic errors which last a few minutes then go away.  The page loads up a generic DataSet, and then that DataSet is assigned to a datagrid on the page.  The error is that a column that is expected to be in the dataset is not found.  I know this column must exist in the database because its a required field.  After we reset IIS, then all returns to normal and the errors go away.  Below is the stack trace.
 Exception: System.IndexOutOfRangeException  Message: Cannot find column LastName.  Source: System.Data     at System.Data.DataTable.ParseSortString(String sortString)     at System.Data.DataView.UpdateIndex(Boolean force)     at System.Data.DataView.SetIndex(String newSort, DataViewRowState newRowStates, DataFilter newRowFilter)     at System.Data.DataView..ctor(DataTable table, String RowFilter, String Sort, DataViewRowState RowState)     at MySystem.Web.GuestSearch.ShowResults(DataSet ds, Int32 currentPageIndex)     at MySystem.Web.GuestSearch.LoadResults(Int32 currentPageIndex)     at MySystem.GuestSearch.btnSearch_Click(Object sender, EventArgs e)     at System.Web.UI.WebControls.Button.OnClick(EventArgs e)     at System.Web.UI.WebControls.Button.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument)     at System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument)     at System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData)     at System.Web.UI.Page.ProcessRequestMain()
Our DBA can find no problems on the database side and our web servers don't log any system event problems either.  I do not understand why resetting IIS helps the issue.  I need to find the real cause of the problems rather than just resetting IIS all the time.  Any ideas?
 

View 1 Replies View Related

How Do I Update A SQLCE SDF Table With A Dataset Returned From A Webservice?

Jun 14, 2007

I have an existing call to a webservice that updates an MDB with data from a dataset.
I was now moving to a process using SQL Compact Edition sdf instead of an mdb.

I belive I have to DROP/Truncate the table in the SDF file, then create a new table then somehow insert the results of the retrieved dataset into the new table. DOes anybody have any pointers to help me out?

Here is my old way of doing it, using access.

Thanks.

Private Sub retrieveData(ByVal intMode As Integer)
'This process will call a webservice passing a parameter which in turn executes a SQL stored procedure, returning a dataset
Dim MyObj As localhost.Service1 = New localhost.Service1
Dim ds As DataSet
ds = MyObj.RetrieveSQLdata(txtSecureString, intMode)
Dim myConnection As OleDbConnection
Dim cmd As OleDbCommand = New OleDbCommand
Dim Connstr As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Path & FileName & ";Persist Security Info=False"
myConnection = New OleDbConnection(Connstr)
myConnection.Open()
cmd.Connection = myConnection
Dim mystr As String = ""
For Each row As DataRow In ds.Tables(0).Rows
mystr = ""
mystr = mystr & "INSERT INTO CustMast ( "
mystr = mystr & "StoreNo, CustNo, ShipNo"
mystr = mystr & "values("
mystr = mystr & "'" & row("StoreNo").ToString & "', "
mystr = mystr & "'" & row("CustNo").ToString & "', "
mystr = mystr & "'" & row("ShipNo").ToString & "')"
cmd.CommandText = mystr
cmd.ExecuteNonQuery()
Next
System.Windows.Forms.Application.DoEvents()
cmd.Dispose()
myConnection.Close()
myConnection.Dispose()

End Sub

View 1 Replies View Related

How To Figure Out If The Dataset Returned Any Data Or Not While Rendering The Report Through The SSRS Web Service

Jan 12, 2007

In one of my projects, i have been using the SSRS web service to render a report as pdf and then manually emailing it out.

I need to know whether the data set for the report returned any data or not (so that i can avoid emailing the report with a blank report body).

Ofcource this can be done by executing the same query (same as the query for the report) from C# code and checking the count of the resultset, before calling the SSRS Render method. But it might not be an efficient method.

Was wondering the the SSRS Web Service provides an property or method that can let me figure out whether the resultset was empty or not (after the report execution).

Thanks.

View 1 Replies View Related

CASTing A Datatype Returned By CASE Statement

Apr 28, 2004

I realize that the CASE statement doesn't like different datatypes as return values but if I want to format the "0" in the second WHEN condition below to "000", how do I do that? I have a "Region" that is "000" and would like it to show up that way at the very top of my report. I have the GROUP BY and ORDER BY to work fine, it just shows up as "0" and I can't change it. I realize it is being read as an int but am having trouble with the CAST and where to place it. Thanks again, you guys are great.

ddave


SELECT Region =
CASE WHEN branch_num IN(48,53,78,173,186,198,208,212,257,286,287,317,35 3,398,440,
478,571,572,610,1069) THEN 44
WHEN branch_num IN(484,532,841,864,7001,7101,7102,7103,7104,9031) THEN 0
ELSE 999
END

View 7 Replies View Related

Using A Datareader In A SQL Helper Select Query

Jan 28, 2005

Hi,

I am trying to retrieve a value from a SQL Server table based on the results of a datareader.This is the string i am trying to create

Me.ViewState("Make") = SqlHelper.ExecuteScalar(MyConnectionString, "usp_GetMake", (dr("MakeID")).To String)

Im not sure of the syntax as I know that it is basically using two different ways of reading a database.Is there an equivalent for the datreader which can be used with SQL Helper?

Thank you for your help

Julie

View 1 Replies View Related

Get Value From SQL Server 2005 Select Statement With Datareader

Nov 6, 2006

I just want a simple datareader, that i can read the value returned from a select statement executed on a SQL server 2005 db.  The code below should work in, but email[calc]= rdr[0].ToString(); when i want to read some data a get a exception saying:System.InvalidOperationException was unhandled by user code  Message="Invalid attempt to read when no data is present."  Source="System.Data"  StackTrace:       at System.Data.SqlClient.SqlDataReader.GetValue(Int32 i)       at System.Data.SqlClient.SqlDataReader.get_Item(Int32 i)       at _Default.Login_Click(Object sender, EventArgs e) in d:My DocumentsVisual Studio 2005WebSitesWebSite1Default.aspx.cs:line 47       at System.Web.UI.WebControls.Button.OnClick(EventArgs e)       at System.Web.UI.WebControls.Button.RaisePostBackEvent(String eventArgument)       at System.Web.UI.WebControls.Button.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument)       at System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument)       at System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData)       at System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) If anybody could  advise me where my stupid mistake is then i would highly appreciate it!         SqlConnection conn = new SqlConnection(getConnection());        SqlDataReader rdr = null;        SqlCommand cmd = new SqlCommand();        cmd.CommandText = "SELECT * FROM Customer";        cmd.CommandType = CommandType.Text;        cmd.Connection = conn;        try        {            conn.Open();                           rdr = cmd.ExecuteReader();                        int calc = 0;            Boolean login = false;            string[] email = new string[100];            object[] password = new object[100];            while (rdr.HasRows) // or rdr.Read();             {                rdr.Read();                email[calc]= rdr[0].ToString();                password[calc] = rdr["Password"].ToString();                if (UserName.Text.Equals(email[calc]) && Password.Text.Equals(password[calc]))                {                    login = true;                }                calc++;            }        }        finally        {            rdr.Close();        }  thanks.... 

View 1 Replies View Related

Reporting Services :: Select Text Field Dataset Based On User Select Option?

Aug 4, 2015

I have a report that uses different datasets based on the year selected by a user.

I have a year_id parameter that sets a report variable named dataset_chosen. I have varified that these are working correctly together.

I have attempted populating table cell data to display from the chosen dataset. As yet to no avail.

How could I display data from the dataset a user selects via the year_id options?

View 4 Replies View Related

How Do I Use A Value Returned From SQLDataSource Select?

Mar 28, 2007

I would like to use the value returned from my SqlDataSource SELECT method, in the INSERT method for the same SqlDataSource.
 Any ideas how this is done?

View 1 Replies View Related

Select Subquery Returned More Than 1 Value

Dec 24, 2007

select t1.a, (select t2.b from t2 where t1.c = t2.c) b from t1
I need to write that kind of sql to return me single value a and multiple values b on each of lines, like
 a            b
----------------------------
tom        small,big,hugh
But if I execute that sql, I would get error like 'select Subquery returned more than 1 value'.  Please help me find a solution, thanks!

View 4 Replies View Related

Selective Rows Returned From Select

Jan 25, 2006

Hi,

I have a problem that I would like help with.

I have to write an SQL Server 2000 stored procedure that returns rows from a table (a SELECT with an ORDER BY). A front end system calls the stored procedure and displays the returned rows. If there is more than one screen's worth of data (ie more than 20 rows returned from the table) then there is a requirement that the stored procedure only returns the rows for the screen ie, for screen 1 I need to return rows 1-20, for screen2 I need to return rows 21-40, screen 3 = rows 41-60.

The screen number will be passed into the stored procedure so I can work out what rows to return, the only problem is how can I tell SQL Server to only select the required rows (ie, say rows 21 to 40 from the returned rows). I can't see any arguments that allow selective rows to be returned (apart from TOP).

Any ideas??

Regards,

Chris

View 3 Replies View Related

Saving Value Returned By Select In Variable

Jul 16, 2007

What is the syntax for saving a single value returned by a Select statement in a variable? I have tried the following, but its telling me I "Must declare the variable '@TempCount'." which is already declared!


SET @SQL = 'Select @TempCount = count(*) FROM ' + @TblName + ' WHERE ' + .......
exec(@SQL)

View 1 Replies View Related

Take Id-s From Returned Rows, And Make A New SELECT

Jun 14, 2006

Hello, I have a question on sql stored procedures.
I have such a procedure, which returnes me rows with ID-s.
Then in my asp.net page I make from that Id-s a string like

SELECT * FROM [eai.Documents] WHERE CategoryId=11 OR CategoryId=16 OR CategoryId=18.

My question is: Can I do the same in my stored procedure? (without sending it to page)
Here is it:

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

ALTER PROCEDURE [dbo].[eai.GetSubCategoriesById]
(
@Id int
)
AS
declare @path varchar(100);
SELECT @path=Path FROM [eai.FileCategories] WHERE Id = @Id;
SELECT Id, ParentCategoryId, Name, NumActiveAds FROM [eai.FileCategories]
WHERE Path LIKE @Path + '%'
ORDER BY Path

fileCategories table: (for information)Here is the screenshot of the table (10 kb )http://eai.w2o.ru/screen1.gif

Thank you
Artashes

View 12 Replies View Related

No Rows Returned From Select Satement

Jul 31, 2007

How is the best way to check for No rows returned from a Select statement in a Stored Proc? I have see this:






Code Snippet

Declare @res int
set @res = NULL
Select @res = dbKeyColumn
From aTable

If @res is null


then no rows returned.



Surely there is a better way??

Thanks.

View 3 Replies View Related

Listbox To Only Appear If There Are Records Returned From The SQL Select Query

Oct 19, 2006

I would like to make a listbox only appear if there are results returned by the SQL select statement. I want this to be assessed on a click event of a button before the listbox is rendered.I obviously use the ".visible" property, but how do I assess the returned records is zero before it is rendered?

View 3 Replies View Related

No Results Returned By SELECT Against Datetime Field

Jan 8, 2004

I am trying to pull results from an SQL Server DB into an dataset where a particular field (SMALLDATETIME) is within a particular date range. The code I was using worked fine when the database was in Access. I have made several changes already but am still getting 0 results returned when there is data that should be returned.

I was using in Access:
Dim StrSQL = "SELECT ID FROM myTable WHERE myDateField>=#" & startDate & "# AND myDateField<=#" & stopDate & "# ORDER BY ID"
I have changed this for SQL Server to:
Dim StrSQL = "SELECT ID FROM myTable WHERE myDateField>='01/01/2003 00:00:01' AND myDateField<='01/01/2004 23:59:59' ORDER BY ID"
But I am always returned 0 results even if the date range should return plenty of data. I have also tried using the BETWEEN structure with the same result.

Is there a particular format for the date I am comparing with?
Am I missing something else in my query?

The connection / permissions and everything else are correct as I can read and write data to the database in numerous other pages. It is just this date comparison that is not working.

Many thanks for any help or comments you can provide.

View 2 Replies View Related

Row Count Returned By A Select Query In Result

Jul 30, 2013

I want to show the number of rows returned by a select query in the result.

e.g. select policy,policynumber,datecreated,Firstname, '-' as recordcount from policy

If it returns 5 rows, then the 'recordcount' need to show '5' in all row in the result

OutPut

0y96788,HGYG564,29/07/2013,SAM,5
FJUFBN7,JLPIO67,29/07/2013,Test,5
...
..
..

How can i get this number in the result.

View 3 Replies View Related

SELECT Query (rows Returned In One Column)?

Aug 17, 2005

Hi there,

I¡¦ve got a table with the following as well as other info:

User ID
DirectoryTypeID (int)
Region ID (int)

I need to run a query where I could get the region ID, then, in the second column, I¡¦d get all distinct directory types within that region. For example, if I run the query:

subRegionAreaID directoryTypeID
--------------- ---------------
3 1
3 2
3 3
3 9

If need these results to be:

subRegionAreaID directoryTypeID
--------------- ---------------
3 1, 2, 3, 9

Is this possible?

Many Thanks!! ļ


Grazi

View 1 Replies View Related

How To Read The Xml Returned By Select Query With For XML Auto,Elements

Dec 13, 2007

Hi all,
 I am writing a select query which produces huge xml data.Now i want to read that data from my web application a save it as xml file.
How can i do that.
I am using asp.net vb.net.

View 1 Replies View Related

Determining If A SqlCeDataReader Has Returned Data From A SELECT Statement

Sep 26, 2006

Hi all,

is there no a way to determine if a SqlCeDataReader has managed to return data (rows) from an executed SQL SELECT statement? I ask this because I have the following problem where I first need to determine if data (rows) are returned, if so, cycle through them and get the data out. But if I do the test to determine if data is returned (like I have in my code below) then the Reader.Read() is classed as reading a row, so when I do my 'while (Reader.Read())' this will then only work with data in the 2nd row.
if (Reader.Read()){ while (Reader.Read()) { //Get data from rows }}

I recall having this issue before as there doesn't seem to be a standard way of testing for data returned from a SELECT statement. I hope you can shed some light on this for me.

Thanks

View 8 Replies View Related

Excluding Part Of Select Statement If No Data Is Returned In Results

Aug 22, 2006

I have a query that returns results based on information in several tables.  The problem I am having is that is there are no records in the one table it doesn't return any information at all.  This table may not have any information initially for the employees so I need to show results whether or not there is anything in this one table.
Here is my select statement:
  SELECT employee.emp_id, DATEDIFF(mm, employee.emp_begin_accrual, GETDATE()) * employee.emp_accrual_rate -
(SELECT SUM(request_duration) AS daystaken
FROM request) AS daysleft, employee.emp_lname + ', ' + employee.emp_fname + ' ' + employee.emp_minitial + '.' AS emp_name,
department.department_name, location.location_name
FROM employee INNER JOIN
request AS request_1 ON employee.emp_id = request_1.emp_id INNER JOIN
department ON employee.emp_department = department.department_id INNER JOIN
location ON department.department_location = location.location_id
GROUP BY employee.emp_id, employee.emp_begin_accrual, employee.emp_accrual_rate, employee.emp_fname, employee.emp_minitial,
employee.emp_lname, department.department_name, location.location_name
ORDER BY location.location_name, department.department_name, employee.emp_lname
 
The section below is the part that may or may not contain information:
  SELECT (SELECT SUM(request_duration) AS daystaken
FROM request) AS daysleft

 
So I need it to return results whether this sub query has results or not.  Any help would be greatly appreciated!!!
TIA

View 3 Replies View Related

Can I Select * From DataSet?

Apr 16, 2008

I used codes below:
sqlA = "select * from order where orderby = 'Mike'"
myDataset = makeDataset(sqlA) // I created a function to return a dataset
I knew the code below is working:
select * from Product where OrderID in ( "select * from order where orderby = 'Mike'")
Can I do the way below?
select * from Product where OrderID in myDataset

View 2 Replies View Related

Assigning A Select Value To Each Row Of A Dataset

Oct 4, 2007

I have the following problem:
in a data flow, if inserting new records, there are columns that take some default values. These default values are kept in a table in case the user wants to change them some day. Def. values could not be assigned at a table level because there's another dataflow that populates the same table, but the rules for the default values are different.

Since I want to extract these values only if there is at least one new row, I'm not fond of the idea to use Execute SQL Task (to save the default values in a variable) before the actual Data Flow. What are my options in getting these values in a Data Flow right before inserting? Thank you for the help.

View 5 Replies View Related

Problem With Dates In Dataset Select Statement

Mar 14, 2007

I'm using the designer to create/modify strongly typed datasets.  I have one select statement that I'm having considerable trouble with.  The user selects search parameters from a form.  These search parameters are used to filter the data sent to a gridview control.  Three of these parameters are almost enough to make me abandon the dataset in this case.The first two are minimum and maximum age.  I have a birth date field, but not an age field - ages have a habit of changing without user intervention <grin> and can be calculated.  In ASP I'd do something like this:strSQL = "SELECT [DATE OF BIRTH], [FIRST NAME], [LAST NAME], [STATE], [MALE OR FEMALE] FROM members WHERE (DateDiff(yyyy, [DATE OF BIRTH], '" & date & "') >= " & strLowerAge & ") AND (DateDiff(yyyy, [DATE OF BIRTH], '" & date & "') <= " & strUpperAge & ")" I can't figure out how to get datediff working in the designer much less testing against upper and lower age limits.The third parameter is astrological sign.  Again, I calculate it based on the birth date.  I don't have a field for it.  I could, but I would have to modify all the pages that add or edit records to the database to insure all the records that have birth dates also have the right sign.  I'm leaning in that direction, but is it possible to accept a sign as a parameter and calculate, based on the birth date, which records qualify?  I need to get the age issue fixed.  The sign is a nice to have, since I can do it another way if I have to. BTW:  I did decide to abandon the dataset in favor of a SqlDataSource control.  This allowed me to build my select string in the code behind the way I would have in ASP.  This resulted in paging and sorting not working properly.  Sorting would be nice, paging is necessary.  I'm pretty sure going back to the dataset or using a stored procedure would fix the paging problem, and I've yet to work with stored procedures (it's on my list of things to learn).   Any comments or feedback on this would be avidly read and appreciated.Diane 

View 8 Replies View Related







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