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!
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.
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.
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)
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.
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?
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
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
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.
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?
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()
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).
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
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?
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....
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?
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!
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).
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)
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
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?
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.
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:
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.
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.
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
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
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.
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