Read Single Piece Of Data From SqlDataSource To A Variable?
Jan 17, 2006
First I want to say thanks to the folks how have helped me over the past week or two.
I'm missing a crucial piece of insight. How do I read a single piece of data from a SqlDataSource which only hold one row of data into a declared variable?
string zUserName = SqlDataSource.(get the value in the row that is labeled UserName)
It really doesn't matter to me whether it is an SqlDataSource or some other data container that hold the data I've selected. My goal is to be able to read some data from some predefined datasource into a declared variable.
If I am approaching this problem in the wrong way any advice or article links you could give me would be grealy appreciate.
I have the following sqlDataSource. I wan't to display one piece of data, not a whole row, from it. I can find lots of information on putting it into a datagrid, but nothing on puttin one piece in a textbox. I cannot use a formview as we are embedding html in response.write and it breaks in a formview. Thanks.
<asp:SqlDataSource runat="server" ID="myEmpInfo" SelectCommand="Select di.EmpInfo,di.eth,gc.t_level From tblEmp di ,tblMisc gc Where di.empnum = @empnumand di.empnum = gc.empnum" DataSourceMode="DataReader" ConnectionString="<%$ ConnectionStrings : myConnectionString %>">
RE: XML Data source .. Expression? Variable? Connection? Error: unable to read the XML data.
I want my XML Data source to be an expression as i will be looping through a directory of xml files.
I don't see the expression property or the connection property??
I tried setting the XMLData property to @[User::filename], but that results in:
Information: 0x40043006 at Load XML Files, DTS.Pipeline: Prepare for Execute phase is beginning. Error: 0xC02090D0 at Load XML Files, XML Source [108]: The component "XML Source" (108) was unable to read the XML data. Error: 0xC0047019 at Load XML Files, DTS.Pipeline: component "XML Source" (108) failed the prepare phase and returned error code 0xC02090D0. Information: 0x4004300B at Load XML Files, DTS.Pipeline: "component "OLE DB Destination" (341)" wrote 0 rows. Task failed: Load XML Files Information: 0xC002F30E at Bad, File System Task: File or directory "d:jcpxmlLoadjcp2.xml.bad" was deleted. Warning: 0x80019002 at Package: The Execution method succeeded, but the number of errors raised (2) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors. SSIS package "Package.dtsx" finished: Failure. The program '[3312] Package.dtsx: DTS' has exited with code 0 (0x0).
I'm relatively new to SSIS and I've been tinkering with the XML and Flat File sources.
I noticed that in the XML source it is possible to tell SSIS to read the XML data from a variable. I didn't see a similar option for the Flat File source.
Does anyone know if it is possible to read flat file data from a variable when using the Flat File source?
What is the C# code I use to do this? I'm guessing it should be fairly simple, as there is only one row selected. I just need to pull out a specific field from that row and then insert that value into a different SqlDataSource.
All the column names in upper case are actually symptom names, and in those columns are values {NULL, 1, 2, 3, 4, 5} and they belong in a column, so the normalized structure should be like this:
CREATE TABLE Symptom ( PatientID INT NOT NULL, Cycle TINYINT NOT NULL, SymptomName VARCHAR(20) NOT NULL, -- from the source column *name* Grade TINYINT NOT NULL -- from the value in the column with the name in uppercase PRIMARY KEY (PatientID, Cycle, SymptomName));
I can untwist the repeating groups with the code I borrowed from Kenneth Fisher's article [ here ], but the part I'm having a harder time with is grabbing the information that's still left in the column name and integrating it into the solution...
I can retrieve all the column names that are in uppercase using this:
DECLARE @db_id int; DECLARE @object_id int; SET @db_id = DB_ID(N'SCRIDB'); SET @object_id = OBJECT_ID(N'SCRIDB.dbo.BadTox'); SELECT name AS column_name , column_id AS col_order FROM sys.all_columns WHERE name = UPPER(name) COLLATE SQL_Latin1_General_CP1_CS_AS AND object_id = @object_id;
but I can't figure out how to work it into this (that I built by mimicking Kenneth Fisher's article...):
ALTER PROC [dbo].[UnpivotMaxGradeUsingCrossApply] AS SELECT PatientID , Toxicity , MAX(Grade) AS MaxGrade
[code]....
The problem is that I need to extract the column names (where ToxicityName[n] would be). I can do that by querying the sys.all_columns view, but I can't figure out how to integrate the two pieces. About the only thing I have even dreamed up is to build the VALUES(...) statements dynamically from the values returned by the system view.
So how do I get both the value from the ToxicityName[n] column and the column name into my final data query?
The new databound controls are great, but somehow doing the simpler things has gotten harder.The code is in a function that is passed pollid. I want to use SqlDataSource to get the smallest value of answerid where pollid = the value passed for pollid. For example:"Select top from PollAsnwers where pollid=" & PollId & " order by AnswerId" I can set up the SqlDataSource for a data reader to fill a GridView control, but that isn't what I want to do. In fact, I can't find anything on SqlDataSource that doesn't also involve the GridView control.I have so far:Dim myConnection As SqlConnectionDim myCommand As SqlCommandDim myReader As SqlDataReadermyConnection = New SqlConnection()myConnection.ConnectionString = ConfigurationManager.ConnectionStrings("UserPolls").ConnectionStringmyCommand = New SqlCommand()myCommand.CommandText = "Select top from PollAsnwers where pollid=" & PollId & " order by AnswerId"myCommand.CommandType = CommandType.TextmyCommand.Connection = myConnectionmyCommand.Connection.Open() Now assuming I have a single row of data, how do I read the value of Answerid? If I have this wrong, what should I have? Diane
Hi, I have a SqlDataSource(named SQLDS1) which retrieves 4 value from database(ProductName,ProductCost,ProductID). I Have a DropDownList(DDL1) control and its DataSource SQLDS1. DDL1 Selected data field to display is ProductNameDDL1 Selected data field to value is ProductCost I did all this in Visual Part without any line of code. But in the code behind , When i select an item from DDL1 i need its ProductName,ProductCost and Also ProductID. It is simple to get first two. But how can i get the ProductID. Is there anyway to get ProductID from SQLDS1. Happy Coding
Hi all, first post, and I am desperate. I have a SqlDataSource with a Select, Update and Delete command. From what I understand, scalar variables should be read automatically from the GridView's BoundField columns when it executes a command on it. Here is my code:
I apologize for the way the code is put in, the code thing cut off a lot of the text! The problem I'm getting is, when the 'Update' button is hit, I get the error: Must declare the variable '@TeamId'.
I've tried putting "Update Parameters", that takes away the error, but the row does not update. I've browsed the internet and saw the same problems in lots of areas, but either a) none of the solutions work for me, or b) they don't really apply to my case. I'm using ASP .NET 2.0, and (obviously) C#. SQL Server database. Any help is greatly appreciated. Thanks in advance,
I'm using strongly typed datasets in my first ASP.NET 2.0 web application. I come from ASP Classic, not an earlier version of .NET, and feel like I'm in another world. I'm slowly getting my head around datasets, but one thing I can't find any information on is how to read the data in a single record?I'm not talking about for next loops, or accessing row information as a repeater or other control is filled. I'm talking about reading the data returned by a method that returns a table containing a single record. This is what I have: Dim BookAdapter As New BooksTableAdapters.BooksBBTableAdapter Dim organizations As Books.BooksBBDataTable Dim organization As Books.BooksBBRow organizations = BookAdapter.GetDataByOneBook(sBookID) Dim sDropOff As String Dim sOrganization As String Dim sContact For Each organization In organizations If organization.DropOff = 1 Then sDropOff = "True" Else sDropOff = "False" End If sOrganization = organization.Organization sContact = organization.ContactName Next sBody = "Books Listing" & Chr(10) & Chr(10) sBody = sBody & "Organization: " & sOrganization & Chr(10) sBody = sBody & "Contact Name: " & sContact & Chr(10) sBody = sBody & "Email: " & organization.Email & Chr(10) sBody = sBody & "Phone Number: " & organization.Phone & Chr(10) I'm using the FOR NEXT, but this is silly since I only have one record. GetDataByOneBook(sBookID) does exactly what is says, it returns a single book with a specific bookID.Not only is this silly, it doesn't work. Using sContact as an example, it's Dim'd as a string. In the FOR NEXT loop, organization.ContactName has the right value, and it appears to be assigned to sContact correctly, but when I try to use sContact in sBody, I get an error saying that sContact has been used before it is assigned a value. Maybe the variables lose their scope outside the loop? Maybe I could get around this by building sBody inside the loop, but there has to be a better way! Diane
My application supports a single database connection and in the app console I can produce reports. If I include the app database in an AlwaysOn availability group with a read-intent replica will SQL automatically route the “selects” to that second instance thus offloading my application’s reporting activities or I need a separate db connection (maybe from a reporting app or cli) with a connection specifying read-only intent?
Is me again,and now i facing problem to retrieve a single record from the database. here is my code:
Protected Sub Button1_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button1.Click Dim user As String user = TextBox1.Text Dim varpassword Dim mydata As SqlDataSource mydata.SelectCommand = "Select * from tbluser where uLogin = '" & user & "'" varpassword = mydata.SelectCommand.uPassword End SubEnd Class
but i get the error : 'uPassword' is not a member of 'String' i wan to retrieve the password of that user,can anyone help me? thanks
hello friends iam newlly joined in to company any one can help me how to bind individual controls to a single SqlDataSource Control.. my problem is "i have different text boxes and dropdown boxes and what i want to do is if i enter primary ker field(employeeno) in the text box automatically i need to get that particular record and i need to populate it to the Respective controls... is there any chance of doing this by using a single datasource control..
Hi Team, I have created on SSIS package which export data from excel to SQL server 2005.It's working fine.Now I am trying to make it dynamic using variable. It means I wil just change the path or name of the excel file How to access excel file using variables ? Need help any link,suggestion .....
I'm trying to read the filename from a file that I'm downloading by FTP (FTP Component) into a string variable so I can later use it for another part of the process, how can I do this?
I would like to ask if there will be a write and concurrency issue if i would create an online application with just one user connecting to the database, just like most open source php/mysql that can be downloaded
i.e Setup 1. I will grant a single user that will connect to a database and will be set to a config.inc.php file and then create a table users (userid, username, password) and this table will be used for the application authentication and access control.
Setup 2. or is it much better to grant users that will connect to the database and have the table users for access control of the application?
to further illustrate my query:
Setup 1 would be: ###################Table: Users user a@ipadd --> db1 --> userid mary --> myOnlineApp user a@ipadd --> db1 --> userid john --> myOnlineApp user a@ipadd --> db1 --> userid paul --> myOnlineApp
and Setup 2 would be: #####################Table: Users user mary@ipadd --> db1 --> userid mary --> myOnlineApp user john@ipadd --> db1 --> userid john --> myOnlineApp user paul@ipadd --> db1 --> userid paul --> myOnlineApp
will Setup 1 be enough if I will use SQL Server as database?
or Setup 2 is better user/database architecture?
This application will be online enrollment for a school with 16,000 students
very much appreciated for the feedbacks and suggestions
I've been getting the error below inconsistently when kicking SSIS packages off from a custom windows service. This same service works fine on our development and integration servers but not on our production servers. When I run the packages manually, it works great. Can someone provide any insight as to what is going on?
Error:
The variable "System::LocaleID" is already on the read list. A variable may only be added once to either the read lock list or the write lock list.
I have tired the soultions found in http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=211081&SiteID=1 and applied the hotfix http://support.microsoft.com/kb/918091
I'm running SQL Server 2005 on Windows Server 2003 R2 SP2.
I am using a SqlDataSource in my form page to query the DB and populate a GridView. If for example the value of Cell (3) is "1" I want to display the values of two other fields, Cells (6) & (7) in two ASP BoundFields 4 and 5. Then, When Cell(3) = "2" I want to compare two Cells (8) & (9) procede as above, placing those values in ASP BoundFields 4 and 5. Ive tried using the code below to do the comparisons of all the fields that I am using and then not displaying them with "Visible=False" as I dont want all the fields to be visible... But this method has become quite cumbersome to manage. Is there a better way to do this?? Thanks!
Protected Sub TestStatus_RowDataBound(ByVal sender As Object, ByVal e As GridViewRowEventArgs) If e.Row.RowType = DataControlRowType.DataRow Then If e.Row.Cells(3).Text = 1 Then e.Row.Cells(4).Text = "<font color=Green><b>" & e.Row.Cells(6).Text & "</b></font>" e.Row.Cells(5).Text = "<font color=Red><b>" & e.Row.Cells(7).Text & "</b></font>" e.Row.Cells(6).Visible = False e.Row.Cells(7).Visible = False e.Row.Cells(8).Visible = False e.Row.Cells(9).Visible = False End If If e.Row.Cells(3).Text = 2 Then e.Row.Cells(4).Text = "<font color=Orange><b>" & e.Row.Cells(8).Text & "</b></font>" e.Row.Cells(5).Text = "<font color=Blue><b>" & e.Row.Cells(9).Text & "</b></font>" e.Row.Cells(6).Visible = False e.Row.Cells(7).Visible = False e.Row.Cells(8).Visible = False e.Row.Cells(9).Visible = False End If End If End Sub
I'm having a real problem populating my SqlDataSource and was wondering if anyone could help. I'm sure it's something simple First off, I created a session variable on a previous page like so Dim NewSubscribeID As Integer = Convert.ToInt32(retSubcribeID.Value) Session("SubcribeID") = NewSubscribeID I now want to use this session variable on another page to populate a SqlDataSource like so <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:ConnectionXX %>" SelectCommand="spXX" SelectCommandType="StoredProcedure"> <SelectParameters> <asp:SessionParameter Name="SubscribeID" SessionField="SubscribeID" Type="Int32" DefaultValue="0" /> </SelectParameters> </asp:SqlDataSource> Here is the problem... Only the default value gets used. I've tried putting the session variable into a label to test that it does actually exist lblTest.Text = Session("SubcribeID")
Hello all,I'm writing a site with one page that uses the session variable (User ID) to pick one user ID out of a comma separated list in the field Faculty. The default parameterized query designed in the SqlDataSource wizard only returns lines that contain an exact match:SELECT * FROM tStudents WHERE ([faculty] = @faculty) The query: SELECT * FROM tStudents WHERE ([faculty] LIKE '%userID%') works as I need when I hard code the query with a specific user ID into the SqlDataSource in the aspx page. It will not work if I leave the @faculty parameter in it:SELECT * FROM tStudents WHERE ([faculty] LIKE '%@faculty%') e.Command.Parameters works to replace the @Faculty with a user ID, but again, adding the single quote and percentage sign either causes errors or returns no results. I've tried several variations of: string strEraiderID = "'%" + Session["eRaiderID"].ToString() + "%'"; e.Command.Parameters["@faculty"].Value = strEraiderID;no results are returned, not even the lines returned with the default select query.How do generate the equivalent of SELECT * FROM tStudents WHERE ([faculty] LIKE '%userID%') into the SqlDataSource? Thanks much!
I have been using a workaround because i think I don't understand something about sqldatasource select parameters. I am using control parameters as a workaround for when i want to use a variable at runtime. i assign the value to the control - then use that as my select parameter. is there another way? here's what i do: <asp:Literal runat="server" ID="litCurrentYear" Text="" Visible="False"></asp:Literal> <asp:SqlDataSource runat="server" ID="dsSummaryTable" ConnectionString="***"SelectCommand="spReportSummary" SelectCommandType="StoredProcedure"><SelectParameters><asp:ControlParameter Name="CurrentYear" ControlID="litCurrentYear" PropertyName="Text" /></SelectParameters></asp:SqlDataSource> On PageLoad I assign litCurrentYear.Text whatever value I want. It works fine. But there must be a more elegant way - I just don't know it yet :)
Can somebody please tell me whether the following syntax is supportedor whether it's a "feature" that will someday stop working. It works inboth SQL Server 2000 and 2005 at the moment.declare @var varchar(1000)set @var = ''select @var = @var + colx from some_table where col1 = some_valuecolx is a varchar or at least is cast to one as part of the selectstatement. If the where clause would normally return more than one row,all returned values for colx are concatenated into @var.I've not seen this syntax before but that doesn't make it wrong ;-)Malc.
SELECT * FROM sale WHERE id =1 AND caldate >='10/1/2015' AND caldate <='10/31/2015' and I don't know how to write this in SP CREATE PROCEDURE [dbo].[sale] @id int,
[Code] ...
Is this a correct way to enclose a variable in single quotes?
I'm getting a type mismatch error (currently string, when I think I need Int32 ?) with the code below. I'm having difficultly setting my session variable to type Int32 and having it take up the value of RawCoDeptRowID. When I "Watch" it, it's value remains as "snCoDeptRowID". Also, not sure if my Update command and it's snCoDeptRowID input parameter is well formed? Any advice would be greatly appreciated. Thank you. Web Page 1: Session["snCoDeptRowID"] = Convert.ToInt32 (RawCoDeptRowID);
Public Class ScriptMain Inherits UserComponent Dim smpid As String Dim Prdt As String Dim rcnt As Int64
[code]...
Using the Vb script above I am expecting to read the first row from a flat file source and transferring the data into two variable using script component.
I get the following errors one after the other:"The collection of variables locked for read and write access is not available outside of PostExecute." "Object reference not set to an instance of an object."
