Simulataneous Programmatic And Declarative Access To SQLDataSource Data
Apr 22, 2006
Hi,
Is there a way to programmatically access the results of an SQLDataSource control select at the time it is binding to a DetailView? There are some fields in the data results I do not want to render to the page, but I still need for other stuff.
I know I can programmatically invoke a select method, but since it is selecting anyway for binding to the DetailView it would be better to get all the data in one swoop.
Thanks in advance for any assistance you could provide.
I'm developing custom SSIS task (control flow component) which offers usert to choose ADO.NET connection.
I want to use this connection in my code and access SQL server.
There is no any problem unless connection is with IntegratedSecurity = true.
But, when user chooses to set username and password, I can not access password because it is not present in the connection string as sensitive information.
Package.DTSProtectionLevel is set to EncryptAllWithUserKey
I need to programmaticaly access to password, but I don't know which class to use from DTS (SSIS) object model.
I am creating an ad-hoc reporting module for my website. The user may select a list of columns and specify the WHERE for the report. I build the select statement and the where statement and pass the info to a stored procedure. The stored procedure accepts 3 select parameters and a 3 wherestmt parameters. I finally got the SQLDatasource to work by setting the parameter values in the Selecting method of the SQLDatasource. Binding it to a gridview shows me all my values. Now, I am trying to programmatically call the select method of the SQLDatasource so I can get a dataview or datareader to manipulate further. I am getting an null back when I call the select method. Any ideas why? or a workaround? <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:AssetMgmtRWConnectionString %>"SelectCommand="getAdhocRptData" SelectCommandType="StoredProcedure" DataSourceMode="DataReader"> <SelectParameters> <asp:Parameter Name="strSelect" Type="String" /> <asp:Parameter Name="strSelect2" Type="String" /> <asp:Parameter Name="strSelect3" Type="String" /> <asp:Parameter Name="strWhere" Type="String" /> <asp:Parameter Name="strWhere2" Type="String" /> <asp:Parameter Name="strWhere3" Type="String" /> <asp:Parameter Name="bitDebug" Type="Boolean" /> </SelectParameters> </asp:SqlDataSource>
<VB Code>Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load 'Getting an error that the object is null? why is the above code not returning any data? 'Tried using a datareader and datasource.Dim rptview As IDataReader = CType(SqlDataSource1.Select(DataSourceSelectArguments.Empty), IDataReader) If rptview.Read Then lblMsg.Text = "Got Rows" End If End Sub Protected Sub SqlDataSource1_Selecting(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.SqlDataSourceSelectingEventArgs) Handles SqlDataSource1.Selecting 'For this sp, need to set each paramater even if it is not being used. Setting it in the wizard 'does not work. Need to be set programmatically. e.Command.Parameters("@strSelect").Value = "p.field1, p.field2 " e.Command.Parameters("@strSelect2").Value = "s.field1, s.field2" e.Command.Parameters("@strSelect3").Value = "a.field1, a.field2, a.field4, a.field5" e.Command.Parameters("@strWhere").Value = "p.field3 is not null and a.field5 is null" e.Command.Parameters("@strWhere2").Value = "'" e.Command.Parameters("@strWhere").Value = "" e.Command.Parameters("@bitdebug").Value = False End Sub
I am trying building a package from code. I have been able to follow the SSIS samples and build my control flow with foreach loop and SQL Commands pretty easily.
The data flow has been a different story, I am struggling with input and output columns. I trying to read from a flatfile, convert data, perform a lookup, and update or insert based on the results of the lookup. I was able to build this package in the designer and it works just as I want, but I am having problems duplicating the data flow in the code.
I was able add the flatfile, data conversion, and insert controls on the data flow and linked them together. However, I cannot figure out how get the input columns in the data convert object to become selected and generate the converted output columns.
I have tried to refresh metadata and mappings column, but to no success. The only custom property for this component seems to be SourceInputColumnLineageId, but I cannot figure how to set it. Can someone give me nudge or push in the right direction?
// Get the design time instance of the component and initialize the component CManagedComponentWrapper instance = convert.Instantiate(); instance.ProvideComponentProperties();
// Reinitialize the metadata. instance.AcquireConnections(null); instance.ReinitializeMetaData(); instance.ReleaseConnections();
// Iterate through the inputs of the component. IDTSVirtualInput90 vInputLkUp = convert.InputCollection[0].GetVirtualInput(); foreach (IDTSVirtualInputColumn90 vColumn in vInputLkUp.VirtualInputColumnCollection) { IDTSInputColumn90 col = instance.SetUsageType(convert.InputCollection[0].ID, vInputLkUp, vColumn.LineageID, DTSUsageType.UT_READONLY); //instance.SetInputColumnProperty(convert.InputCollection[0].ID, col.ID, "SourceInputColumnLineageId", 1); }
Hello all,I have what I think should be a simple problem to solve but I just don't quite understand what to do (.NET Framework Developer Center confused me :( ).My code should load the highest SiteID, known as Last_ID into the siteIdBox when the form is in insert mode. My difficulty is in understanding how to use the sqlDataSource correctly. Dim dsLastId As New SqlDataSource() Dim siteIdBox As TextBox Dim dsmLastId As SqlDataSourceMode If FV_Site.CurrentMode = FormViewMode.Insert Then Try dsLastId.ConnectionString = ConfigurationManager.ConnectionStrings("AquaConnectionString1").ToString dsLastId.SelectCommandType = SqlDataSourceCommandType.Text dsLastId.SelectCommand = "SELECT MAX(Site_ID) AS Last_ID FROM Tbl_Site"
dsmLastId = SqlDataSourceMode.DataReader
siteIdBox.Text = dsmLastId.?????
Finally dsLastId = Nothing End Try End If If anyone could tell me the code for getting the value from the select statement into the textbox, I would be most grateful.Thank you, m00sie.
How do I access sqldatasource data values in code behind and bind them to strings or texboxes etc. as oposed to using Eval in the markup? I can create a new database connection, but I would like to use the data values from the autogenerated sqldatasource control Many thanks,
Hi every experts I have a exist Stored Procedure in SQL 2005 Server, the stored procedure contain few output parameter, I have no problem to get result from output parameter to display using label control by using SqlCommand in Visual Studio 2003. Now new in Visual Studio 2005, I can't use sqlcommand wizard anymore, therefore I try to use the new sqldatasource control. When I Configure Datasource in Sqldatasource wizard, I assign select field using exist stored procedure, the wizard control return all parameter in the list with auto assign the direction type(input/ouput....), after that, whatever I try, I click on Test Query Button at last part, I always get error message The Query did not return any data table. My Question is How can I setup sqldatasource to access Stored Procedure which contain output parameter, and after that How can I assign the output parameter value to bind to the label control's Text field to show on web? Thanks anyone, who can give me any advice. Satoshi
I can't figure out what is wrong with the code below. The first line prints out correctly when I am logged in If I substitute the actual username in the SQL, the Repeater works fine, printing out the item from the db. But using WHERE USERNAME='<%= User.Identity.Name%>' in the SelectCommand returns no rows. Can anyone help? Thanks, Michael <h3>News for <%=User.Identity.Name%></h3> <asp:SqlDataSource ID="SqlDataSource2" runat="server" ConnectionString="<%$ ConnectionStrings:NEWWEB %>" ProviderName="<%$ ConnectionStrings:NEWWEB.ProviderName %>" SelectCommand="SELECT USERNAME FROM ASPNET_MEMBERSHIP WHERE USERNAME='<%= User.Identity.Name%>' "> </asp:SqlDataSource> <asp:Repeater ID="FirmAlertsRepeater" runat="server" DataSourceID="SqlDataSource2"> <ItemTemplate> <b><%# DataBinder.Eval(Container.DataItem, "USERNAME")%></b> — <br />
I have a page with over 20 Textbox and DDL controls and an upload in various Divs and Panels (Ajax enabled) that are used for gathering user data. Some of the fields are mandatory and some optional. In the Code behind (VB- I am a complete novice) On the submit button click event, I iterate through the controls in the page and build an array with information from the controls that have data in them, (filtering out the non-filled textboxes, and DDLs). All this works well, and I get an array called 'myInfo' with the columns with the control ID, and control values 'rvalue' (as string), with the number of rows equal to the filled textboxes and DDLs. I then step through the array and build a string with 'name=values' of all the rows in the myinfo array and email this as a message: ThisMessage = "" NoOfControls = myInfo.GetLength(0) For i = 0 To NoOfControls - 1 ThisMessage = ThisMessage & myInfo(i).ID.ToString & "=" & myInfo(i).rvalue.ToString & "; " Next SendMail(email address, ThisMessage) I also want to add this information to a database, appended by the IP address and datetime.now. Dim evdoDataSource As New SqlDataSource() evdoDataSource.ConnectionString = ConfigurationManager.ConnectionStrings("SQLConnectionStringCK").ToString evdoDataSource.InsertCommandType = SqlDataSourceCommandType.Text Dim InsertMessage As String = """INSERT INTO evdoData (" NoOfControls = myInfo.GetLength(0) Dim k As Integer For k = 0 To NoOfControls - 1 InsertMessage = InsertMessage & myInfo(k).ID.ToString & ", " Next InsertMessage = InsertMessage & "IPNo, DateEntered) VALUES (" For k = 0 To NoOfControls - 1 InsertMessage = InsertMessage & "@" & myInfo(k).ID.ToString & ", " Next InsertMessage = InsertMessage & ", @IPNo, @DateEntered" & ")""" evdoDataSource.InsertCommand = InsertMessage I then similarly iterate through and do the insertparameters. Now here is the rub- (My all too often DUH moment!) When I look at the insertmessage in debug (and to be sure- I also show the insert string on a temporary debug label on the page), The insertmessage looks fine: "INSERT INTO EvdData (FirstName, Age, Email, Phone, Country, City, IPNo, DateEntered) VALUES (@FirstName, @Age, @Email, @Phone, @Country, @City, , @IPNo, @DateEntered)" However when the above code(evdoDataSource.InsertCommand = InsertMessage) is run, I get an error - the message with the error is: The identifier that starts with 'INSERT INTO ModelData (FirstName, Age, Email, Phone, Country, City, IPNo, DateEntered) VALUES (@FirsteName, @Age, @Email, @Phone,' is too long. Maximum length is 128 EH? When I actually copy the InsertMessage from the debug window and paste it manually after the command "evdoDataSource.InsertCommand = " It works, and I get the data inserted into the table.. -It would seem that I am probably missing something obvious in my complete "noviceness" . HELP! (oh and thanks a bunch in anticipation) _____________________________________________________________________________________________________________________ Its Easy --------------------------When you know How. Meanwhile Aaaaaaaaah .
I need to set up a job to allow users to restore their databases, on SQL Server 2000 SP3. The idea is that a user inserts a record into a table, identifying the dump they want to load. (They can only restore their own account.) A job picks up this record, restores the database, and notifies the user as appropriate.
My part of this is writing the procedure that the job executes, including the dump restore. Part of that is getting each dump's file groups (data, index, and log) into the proper locations for this server and this user.
Essentially, I need to be able to access the results of 'load filelistonly' from a cursor. How do I access the file list?
I'm trying to create an SSIS package that will do a straight data copy between databases. The problem is that the underlying schema of the origin may change and the requirement is that the transfer be table driven. i.e. the tables that are copied are listed in a table and there should be no human intervention when the schema changes.
I'm moving data between SQL Server and SQL Azure, so backup and restore doesn't work. Has to be an SSIS package.
What's the best way to deal with a changing schema in an SSIS package? Can I delete and rewrite the underlying XML for any tables that change? Do I need to do it programmatically with C#? Do I need to create the package from scratch each time?
Hello, Is it possible to access both my database and the ASPNETDB within the same SQLDATASOURCE using SQL Express ? I want to pull out some user details from the aspnet_* tables within the ASPNETDB In my database I have created the 'UserId' fields so the relevant joins can take place. In my web.config: <connectionStrings> <add name="mydb" connectionString="Data Source=.SQLEXPRESS;AttachDbFilename=|DataDirectory|mydb.mdf;Integrated Security=True;User Instance=True" providerName="System.Data.SqlClient" /> </connectionStrings> In my asp (showing only mydb): <asp:SqlDataSource ID="SqlDataSource2" runat="server" ConnectionString="<%$ ConnectionStrings:mydb %>" SelectCommand="SELECT [CreateDtTime], [UserId], [Comment] FROM [Log_Dtl] WHERE ([LogId] = @LogId)"> <SelectParameters> <asp:QueryStringParameter Name="LogId" QueryStringField="LogId" Type="Int32" /> </SelectParameters> </asp:SqlDataSource>
I am trying to acces an SQLDatasource in the code page, I have the following code but get the error as below, any one help please The SQLDataSource returns 1 value named [ShippingRegion], I think that has somethjing to do with it ??!! dsShippingRegion.Select(DataSourceSelectArguments.Empty)Dim myReader As Data.IDataReader = CType(dsShippingRegion.Select(DataSourceSelectArguments.Empty), Data.IDataReader)If myReader.Read Then If Convert.IsDBNull(myReader("ShippingRegion")) Then Beep() Else Beep() End IfEnd If System.InvalidCastException was unhandled by user code Message="Unable to cast object of type 'System.Data.DataView' to type 'System.Data.IDataReader'." Source="App_Web_rd5quiy1" StackTrace: at admin_administer_shop_productaddnew.Page_Load(Object sender, EventArgs e) in E:Web DevelopmentWebSitesAJAX_sirs2hersadminadminister_shopproductaddnew.aspx.vb:line 25 at System.Web.UI.Control.OnLoad(EventArgs e) at System.Web.UI.Control.LoadRecursive() at System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) dsShippingRegion.Select(DataSourceSelectArguments.Empty)
Hi all...I've set the DataSourceMode = SqlDataSourceMode.DataSet, and did a .Type return and found that it is actually returning a DataView. A component I am trying to avoid rewriting....requires a dataset that loops through the table, does some cool formatting to a datagrid and then rebinds.Here's the code that I'm trying to send the dataset to....maybe there's just a couple of changes that could make it work with a DataView? int i = 0; string prevsub = ""; while (i <= ds.Tables[0].Rows.Count - 1) { DataRow dr = ds.Tables[0].Rows[i]; string sub = dr["SubHeading"].ToString(); if (sub != prevsub) { prevsub = sub; DataRow newrow = ds.Tables[0].NewRow(); newrow["Title"] = "SubHeading"; newrow[columnName] = dr[columnName]; ds.Tables[0].Rows.InsertAt(newrow, i); i++; } i++; }
I have <asp:SqlDataSource> in aspx page that is being used by an updateable GridView. However, there are a couple fields in the dataset that contain filenames that I want to access directly in code and place them into some image tags on the page. This line should give me access to the dataset but how to I access the fields? Dim myDataSource As DataView = DirectCast(SqlDataSourceGridView.[Select](DataSourceSelectArguments.Empty), DataView) When the SqlDataSource is a DataReader I would access it with the code below but since this SqlDataSource is a DataSet I can't access it with this code.If myDataSource.Read Then If Convert.IsDBNull(myDataSource("CusAgentPhoto")) Then ImageAgent.ImageUrl = "/photos/nophoto.gif" Else ImageAgent.ImageUrl = AgentImagePath & myDataSource("CusAgentPhoto").ToString End If If Convert.IsDBNull(myDataSource("CusCompanyLogo")) Then ImageCompany.ImageUrl = "/photos/nophoto.gif" Else ImageCompany.ImageUrl = OfficeImagePath & myDataSource("CusCompanyLogo").ToString End IfEnd If What would be the correct way to get at the DataSet fields that contain the filenames?
I'm trying to re-write my database to de-couple the interface (MS Access) from the SQL Backend. Â As a result, I'm going to write a number of Stored Procedures to replace the MS Access code. Â My first attempt worked on a small sample, however, trying to move this on to a real table hasn't worked (I've amended the SP and code to try and get it to work on 2 fields, rather than the full 20 plus).It works in SQL Management console (supply a Client ID, it returns all the client details), but does not return anything (recordset closed) when trying to access via VBA code.The Stored procedure is:-
USE [VMSProd] GO /****** Object: StoredProcedure [Clients].[vms_Get_Specified_Client] Script Date: 22/09/2015 16:29:59 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON
I have a client who has SSMS installed on her laptop. She is able to connect to the SQL server via SSMS in the office and query data on the server.
She needs to be out of site often and doesn't have internet access. She asks if the data tables can be "backed up" or saved on her laptop, so she can look at them without worrying connecting to the server. I am not sure if this can be achieved, as SSMS is built for accessing a server, not a desktop. Myself never have this need. If I really need it, I would go to Microsoft Access and create an ODBC connection to the datatables. But this client thinks that Microsoft Access is beneath her.Â
I have reviewed the BOL documentation on how to configure Peer-to-Peer replication via T-SQL and how to use the Replication Wizard to implement replication.
What I would like to find out is how do I configure the peer-to-peer replication process to use an existing column on a table that contains a GUID instead of creating an extra column with a uniqueidentifier GUID value. When you use the Wizard each table article has this extra column added to it.
I don't seem to be able to find it in the books-on-line. Can some one point me to the correct article or BOL page.
I have recently upgraded to SQL2014 on Win2012. The Access front end program works fine.
But, previously created Excel reports with built in MS Queries now fail with the above error for users with MS 2013. The queries still work for users still using MS 2007.Â
I also cannot create any new queries and get the same error message. If I log on as myself on the domain to another PC with 2007 installed it works fine, so I don't think it is anything to do with AD groups or permissions.
We need to insert data/rows from a SQL Server 2014 database into MS Access database. The problem is, there are so many columns (100+) in the table and there are so many insert transactions of this kind (from different tables) that it is not very easy to write the code in VB.NET that lists all column names.
Both the Access and SQL Server tables have the same number of columns and the equivalent data types, so inserting is not really the problem. It's just that is there a way to do an insert statement in T-SQL that does not name all the columns?
I've been developing desktop client-server and web apps and have used Access and SQL Server Standard most of the time. I'm looking into using SQL CE, and had a few questions that I can't seem to get a clear picture on:
- The documentation for CE says that it supports 256 simultaneous connections and offers the Isolation levels, Transactions, Locking, etc with a 4GB DB. But most people say that CE is strictly a single-user DB and should not be used as a DB Server. Could CE be extended for use as a multi-user DB Server by creating a custom server such as a .NET Remoting Server hosted through a Windows Service (or any other custom host) on a machine whereby the CE DB would run in-process with this server on the machine which would then be accessed by multiple users from multiple machines?? Clients PCs -> Server PC hosting Remoting Service -> ADO.NET -> SQL CE
- and further more can we use Enterprise Services (Serviced Components) to connect to SQL CE and further extend this model to offer a pure high-quality DB Server? Clients PCs -> Server PC hosting Remoting Service -> Enterprise Services -> ADO.NET -> SQL CE
Seems quite doable to me, but I may be wrong..please let me know either ways
I have a page containing a FormView, which gets its data from a SqlDataSource control and displays details of a job. Two of the fields are location and job title. I want to re-use this data to create a dynamic page title. I know I can do this by setting the Page.Title to what I want to, but do I have access to the data outside of the FormView to which the data source is bound? If so how? Or will I have to perform an additional SELECT statement to get this data again?
and what this does is fills the dataset with the information from the select command in the dataadapter and then gets the value for the first row of the fieldname
It doesnt seem like this should be that big of a deal, but it has become very frustrating trying to find the answer, does anyone know how to do this? Please post some demo code if possible
Hi Everybody,I have an ordinary SqlDataSource on my page <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:P4SConnection %>" ProviderName="<%$ ConnectionStrings:P4SConnection.ProviderName %>" SelectCommand="SELECT CO_Id,CT_Name,CO_Username,CO_Password,CO_Company,CO_Email,CT_Id,CT_Position,CT_Admin, CO_Session,CO_LastLogin FROM company_contact LEFT JOIN company ON company_contact.CT_Company = company.CO_Id WHERE (CT_Username= & LCase(txtName.Text) & "> </asp:SqlDataSource>How do i use any of these values?I know I can display the values in a GridView or something but I dont want to display them.I originaly was using ODBC and just used the followingDim Row As DataRow = thisTable.Rows(0) Dim lP_ID As String = Row(0).ToString() Dim TheContact As String = Row(1).ToString() Dim theName As String = Row(2).ToString() Dim thePassword As String = Row(3).ToString() Dim TheCompany As String = Row(4).ToString() Dim TheEmail As String = Row(5).ToString() Dim lP_ContactID As String = Row(6).ToString() Dim ThePosition As String = Row(7).ToString() Dim bAdmin As Boolean = Row(8).ToString() Dim sP_Session As String = Row(9).ToString() Dim sP_Last As String = Row(10).ToString()Then I could use the values wherever I wanted.How do I do this with a SqlDataSource?Thanks,G
Okay, I used the SQLDataSource control to get my data from the database table. What or how do I retrieve individual data from the sqldatasource? I want to do some string comparison and manipulation before I display it to the browser. How can this be accomplish?
I have a process that inserts a new record using the InsertCommand of a SqlDataSource. As part of the process, I need to insert data the is available in a different SqlDataSource. I was trying this with the Insert Parameter:
