I don't know if this topic should go in this forum category or in the SQLDataSource forum.
I need to be able to provide SQL which varies based on what the user enters on a page. However, this isn't as simple a providing parameters in the SQLDataSource control because the SQL changes depending on what the user selects. For example, in a customer search page, they choose to search by either first name or last name from a drop down list and then enter the name to search for in a text box. The dataview should display the matching customers.
How can I set this SQL based on the entered values while also keeping the paging and sorting capabilities of the dataview? You don't seem to be able to set the SQL for an SQLDataSource at run time.
Here is the code I am trying to use to set a update parameter at runtime. (Depending on what linkbutton a user clicks on the STATUS_ID value will change.) SqlDataSource1.UpdateParameters("STATUS_ID").DefaultValue = 33332Here are my parameters: <UpdateParameters> <asp:Parameter Name="CUSTOMER_ID" Type="Decimal" /> <asp:Parameter Name="RECEIVED_BY" Type="String" /> <asp:Parameter Name="CALL_DATETIME" Type="DateTime" /> <asp:Parameter Name="AREA_ID" Type="Decimal" /> <asp:Parameter Name="CLASS_ID" Type="Decimal" /> <asp:Parameter Name="STATUS_ID" Type="Decimal" /></UpdateParameters>Sqldatasource1 is the name of my datasource control. Any thoughts?
This may be a very easy question to answer, but I cannot figure out the answer for the life of me. I have set up a simple SSIS project based on a template that gathers information from several servers. I would like for this package to be almost completely automated and run nightly. Is there a setting I can add to make it run everyday?
I used to do this in DTS but wondered how to do it in SSIS. I have a For Each ADO recordset loop that reads these four columns into variables:
DataSource Name, Name(for a WHERE clause), user and password.
For each "Building" there is a new server to connect to, with identical tables. I have 10 tables in the loop to pump from one database to a SQL 2005 database (i.e. 10 dataflows). I want to set the source connection information with the variables. My questions:
1. How do I set the connection information for the source connections? Do I just set the properties for the connection manager and then it sets for all the sources? What property do I set--do I need a script task?
2. How do I use the values from the "Name" column in a WHERE clause in each data flow?
Hello, I am using a sqldatareader to get back a whole set of data. The columns are id, data_id and data_desc for example, the collection is id data_id data_desc0 100 this is name for id 00 101 this is address for id 00 102 this is gender for id 01 100 this is name for id 11 101 this is address for id 11 102 this is gender for id 12 100 this is name for id 22 101 this is address for id 22 102 this is gender for id 23 100 this is name for id 33 101 this is address for id 33 102 this is gender for id 3 I want to be able to go thru' this list and break up for each id and create a txt file. So, i will have four text files. txt0.txt will contain100, this is name for id 0 101, this is address for id 0102, this is gender for id 0 I would like to know, how do i use a dataview to break up the sqldatareader so i can then repeat the loop for each id and generate the text file. I don't have a problem generating the text file. Please let me know, how should i go about it. Thanks a bunch,-Sean
In the dataview rowfilter property, how can I say that I just wantTitles that start with 'A' to 'J' ??ex:dv.rowfilter = " Country = ' France ' "I wantdv.rowfilter = " Title = (from 'A' to 'J') "This doesn't work :dv.rowfilter = " Title like '[a-j]%' "Neither does :dv.rowfilter = " Title like 'a%'" AND "Title like 'b%'" AND..so onBut this does work just for the Titles starting with 'a'dv.rowfilter = " Title like 'a%' "any idea ?JMT
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
Hi, I use a filter on a dataview which has uniq rows. I need to bind several fields of the result filtered row to textboxes. Here the code : protected void Page_Load(object sender, EventArgs e) { string id = Request.QueryString["id"]; if (PreviousPage != null) { SqlDataSource dsPrev = (SqlDataSource)PreviousPage.FindControl("SqlDataSource1"); DataView dv = (DataView)(dsPrev.Select(DataSourceSelectArguments.Empty)); dv.RowFilter = "dr_id='" + id + "' "; dv.RowStateFilter = DataViewRowState.CurrentRows; txtFirstName.Text = // ??? FirstName Field txtLastName.Text= // ??? LastName Field txtAddress.Text= // ??? Address Field } } I don't know which cod to put in those lines. On the help of the dataView there is an example that use DataBindings. This example is in the DataView.RowFilter article: Text1.DataBindings.Add("Text",view,"CompanyName"); The big problem is that I don't have a method or event call 'DataBindings'. I just have 'DataBind' method or dataBinding event and they both don't support the 'Add' method. So how can I bound those controls to my filtered Dataview field value. Thanks, David
Question related to Visual Basic Video Lesson 09 (Databinding.wmv) by Bob Tabor.
Made a table and a UI form according to this lesson (table colums: CustomerID, FirstName, LastName).
Is it possible to use the same or an identical looking query form to find "Bob" by typing Bob in the Fist Name textbox or "Tabor" by typing Tabor in the Last Name textbox as in FileMaker?
Hi, I need to create filter which retrieves data for current moment. [time].[date].lastchild is not an option because the calender in database is generated till following year (2008).
I guess script goes like..
[time].[date].currentmember
but it does not work.
I'm green with these MDX scripts.. I'd be very pleased if some one of you gurus could help me.
I have the following SqlDataSource in my page:<asp:SqlDataSource ID="sqlds" runat="server" ConnectionString="<%$ ConnectionStrings:cs %>" SelectCommand="SELECT [Quest1], [Quest2], [Quest3], [Quest4], [Quest5] WHERE ([QuestID] = @QuestID)"> <SelectParameters> <asp:Parameter Name="QuestID" /> </SelectParameters> In my code I get the following error: "Cannot convert type 'int' to 'System.Data.DataView'", I don't understand why I am getting this error because I am casting my SqlDataSource as type DataView when passing it to my DataView type.Code: protected void Chart(string record) { System.Data.DataView dv = (System.Data.DataView)sqlds.SelectParameters.Add("QuestID", System.TypeCode.Int32, record); //Error line ... ... ...}If anyone could point me in the right direction I would be very grateful.
I need access to the underlying DataView associated with a declaratively defined SQLDataSource. Ideally, I need this access in the SQLDataSource's Selected event. I know that I can use the SQLDataSouce.Select() method to return the underlying DataView object. But, there is a GridView control bound to the datasource so I don't want to explicity call Select() since it will end up being called again implicity by the gridview itself. I have seen that this can be done with an ObjectDataSouce as follows: void odsDataSouce_Selected(object sender, ObjectDataSourceStatusEventArgs e) { DataView oDV = (DataView)e.ReturnValue;} Is there any way to accomplish the same thing with a SQLDataSouce? It seems hard to believe that the query results are not exposed to the Selected event. Thanks!
(Hope this isn't a "stupid" question, but I haven't been able to find a straight-forward answer anywhere)" I currently have code that iterates through a dataview's records, making a change to a field in some of the records. The way I have this coded, a conection has to opened & closed for each individual record that's updated: dsrcUserIae.UpdateCommand = "UPDATE UserIAE SET blnCorrect = @blnCorrect WHERE (ID = @ID)" dsrcUserIae.UpdateParameters.Add("blnCorrect", SqlDbType.Bit) dsrcUserIae.UpdateParameters.Add("ID", SqlDbType.Int) Dim myDataView As DataView = CType(dsrcUserIae.Select(DataSourceSelectArguments.Empty), DataView) For Each myRow As DataRowView In myDataView If myRow("FkUsersAnswerID") = myRow("AnswerID") Then intCorrect = 1 Else intCorrect = 0 End If dsrcUserIae.UpdateParameters.Item("blnCorrect").DefaultValue = intCorrect dsrcUserIae.UpdateParameters.Item("ID").DefaultValue = myRow("ID") intUpdateResult = dsrcUserIae.Update() Next It seems like I should be able to do something like this (call update once), but I'm not sure how... dsrcUserIae.UpdateCommand = "UPDATE UserIAE SET blnCorrect = @blnCorrect WHERE (ID = @ID)" dsrcUserIae.UpdateParameters.Add("blnCorrect", SqlDbType.Bit) dsrcUserIae.UpdateParameters.Add("ID", SqlDbType.Int) Dim myDataView As DataView = CType(dsrcUserIae.Select(DataSourceSelectArguments.Empty), DataView) For Each myRow As DataRowView In myDataView If myRow("FkUsersAnswerID") = myRow("AnswerID") Then myRow("blnCorrect") = 1 Else myRow("blnCorrect") = False End If Next intUpdateResult = dsrcUserIae.Update() 'Want all changed myRow("blnCorrect") to be updated to datasource Can anybody explain how to do the bulk update? I've seen some info about AcceptChanges and Merge, but I'm not sure if they apply here, or if they more for Transactions.
I am having a Data flow task in For each loop which will gets 100 sourcetable names and 100 target table names...
am having a simpleData flow task which trasferes from OLEDBSource to OLEDBDestination. I am repeating the Dataflow task which transfers from sourcetablename extracted from for loop to a destination table var.
The problem am gettting is for the first table it is able to transfer correcly because I did mapping for those tables at design time...but for the next coming sourcetable-desttable (which r having different no of cols,datatypes) its giving Validation failed...and...needs to refresh metadata....
is there any way to refresh the metadata of Data flow task (I set the property of OLEDBSource validate external meta to false then also same error is coming)
Hi all, In my VWD 2005 Express, I created a website "AverageTCE" that had Default.aspx, Default.aspx.vb and App_Code (see the attached code) for configurating a direct SqlDataSource connection to the dbo.Table "LabData" of my SQL Server 2005 Express "SQLEXPRESS" via SqlDataSource, DataView, CType Function and the Page_Load procedure. I executed the website "AverageTCE" and I got Compilation ErrorBC30451: Name 'SqlDataSource3' is not declared:
Server Error in '/AverageTCE' Application.
Compilation Error Description: An error occurred during the compilation of a resource required to service this request. Please review the following specific error details and modify your source code appropriately. Compiler Error Message: BC30451: Name 'SqlDataSource3' is not declared.Source Error:
Line 8: <DataObjectMethod(DataObjectMethodType.Select)> _ Line 9: Public Shared Function SelectedConcentration() As ConcDB Line 10: Dim dv As DataView = CType(SqlDataSource3.Select(DataSourceSelectArguments.Empty), DataView) Line 11: dvConcDB.RowFilter = "Concentration = '" & ddlLabData.SelectedValue & "'" Line 12: Source File: C:Documents and Settingse1enxshcMy DocumentsVisual Studio 2005WebSitesAverageTCEApp_CodeConcDB.vb Line: 10 //////////--Default.aspx--////////////////////////// <%@ Page Language="VB" AutoEventWireup="false" CodeFile="Default.aspx.vb" Inherits="_Default" %> <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"> <html xmlns="http://www.w3.org/1999/xhtml" > <head runat="server"> <title>SQL DataSource</title> </head> <body> <form id="form1" runat="server">
</div> </form> </body> </html> ///////////--Default.aspx.vb--//////////////////////////////// Partial Class _Default Inherits System.Web.UI.Page End Class ////////////////--App_Code/ConcDB.vb--////////////////////// Imports Microsoft.VisualBasic Imports System.ComponentModel Imports System.Data Imports System.Data.SqlClient Imports System.Data.SqlTypes <DataObject(True)> Public Class ConcDB <DataObjectMethod(DataObjectMethodType.Select)> _ Public Shared Function SelectedConcentration() As ConcDB Dim dv As DataView = CType(SqlDataSource3.Select(DataSourceSelectArguments.Empty), DataView) dvConcDB.RowFilter = "Concentration = '" & ddlLabData.SelectedValue & "'" Dim dvRow As DataRowView = dvConcDB(0) Dim ConcDB As New ConcDB ConcDB.SelectedConcentration = CDec(0)("Concentration") Return ConcDB End Function Call AverageValue (Conc1) Public Shared Function AverageValue(ByVal Conc1 As Decimal) Dim AverageConc As Decimal AverageConc = (Conc1 + 22.0) / 2 Return AverageConc End Function End Class ************************************************************** I have 2 questions to ask: 1) How can I fix this Compilation Error BC30451: Name 'SqlDataSource3' is not declared? 2) I just read MSDN Visual Studio 2005 Technical Article "Data Access in ASP.NET 2.0" and I saw the following thing: Types of Data Sources: SqlDataSouirce: The configuration of a SqlDataSoure is more complex then that of the AccessDataSource, and is intended for enterprise applications that require the features provided by a true database management system (DBMS). I am using the website application in VWD 2005 Express to do the task of extracting data values from the Tables of SQL Server 2005 Express via .NET Framwork, ASP.NET 2.0 and VB 2005 programming. Can VWD 2005 Express be configured to SQL Server 2005 Express (SQLEXPESS) for the SqlDataSource connection and do the data-extraction task via DataView, CType Function and the Page-Load procedure? Please help, respond and answer the above-mentiopned 2 questions. Many Thanks, Scott Chang
I am trying to put the data from a field in my database into a row in a table using the SQLDataSource.Select statement. I am using the following code: FileBase.SelectCommand = "SELECT Username FROM Files WHERE Filename = '" & myFileInfo.FullName & "'" myDataRow("Username") = CType(FileBase.Select(New DataSourceSelectArguments()), String)But when I run the code, I get the following error:Server Error in '/YorZap' Application. Unable to cast object of type 'System.Data.DataView' to type 'System.String'. Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. Exception Details: System.InvalidCastException: Unable to cast object of type 'System.Data.DataView' to type 'System.String'.Source Error: Line 54: FileBase.SelectCommand = "SELECT Username FROM Files WHERE Filename = '" & myFileInfo.FullName & "'" Line 55: 'myDataRow("Username") = CType(FileBase.Select(New DataSourceSelectArguments).GetEnumerator.Current, String) Line 56: myDataRow("Username") = CType(FileBase.Select(New DataSourceSelectArguments()), String) Line 57: Line 58: filesTable.Rows.Add(myDataRow)Source File: D:YorZapdir_list_sort.aspx Line: 56 Stack Trace: [InvalidCastException: Unable to cast object of type 'System.Data.DataView' to type 'System.String'.] ASP.dir_list_sort_aspx.BindFileDataToGrid(String strSortField) in D:YorZapdir_list_sort.aspx:56 ASP.dir_list_sort_aspx.Page_Load(Object sender, EventArgs e) in D:YorZapdir_list_sort.aspx:7 System.Web.Util.CalliHelper.EventArgFunctionCaller(IntPtr fp, Object o, Object t, EventArgs e) +13 System.Web.Util.CalliEventHandlerDelegateProxy.Callback(Object sender, EventArgs e) +45 System.Web.UI.Control.OnLoad(EventArgs e) +80 System.Web.UI.Control.LoadRecursive() +49 System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +3743 Version Information: Microsoft .NET Framework Version:2.0.50727.42; ASP.NET Version:2.0.50727.210 Please help me!
Which calls for me to import: Microsoft.SqlServer.Dts.Runtime However, I don't have this DST Runtime DLL installed on my machine. I have installed "everything" in SQL Server Developer Edition and Visual Studio. Any idea how I can get this?
I just ran a testbed of 4 types of SQL queries:1. inline SQL with a StringBuilder2. managed sql3. SQL text processing (@SQL as varchar(5000); SET @SQL = 'SELECT ' + @var...)4. a regular sproc that has the columns and table name hard coded1,2, and 4 always end up at about the same time given the averages.3 is always at last 1.5 times slower, and usually closed to 2 times.1 and 2 both use StringBuilders, the code is a direct copy, and 3 is a copy as well.My managed SQL is: [Microsoft.SqlServer.Server.SqlProcedure] public static void usp_Items_Select_Managed(SqlString table, SqlString name, SqlString value) { // sql StringBuilder stringBuilder = new StringBuilder(); stringBuilder.AppendFormat( "SELECT {0}.* FROM {0} WHERE {0}.{1} = {2}", table, col, value ); SqlConnection sqlConnection = new SqlConnection("context connection=true"); SqlCommand sqlCommand = new SqlCommand(stringBuilder.ToString(), sqlConnection); sqlConnection.Open(); SqlContext.Pipe.Send(sqlCommand.ExecuteReader()); sqlConnection.Close(); }Is there anything wrong with my Managed SQL, or is this just the way that it is?Thanks
I'm running a package on SQL Server 7, SP2 created in the same environment. When running a package from DTS Designer, I get an execution status of "Error Occurred" in the Package Execution Status dialog on one of the data pump tasks I'm running along with the number of rows inserted. When I double-click on the task, I get the following message: " The Connection is currently being used by a task. The connection cannot be closed or reused".
Both the exception log for the task and the package, specified for error output, indicate the task ran successfully and the clients confirm that the appropriate number of rows have been inserted in the table. Has anyone encountered this error that can explain the cause/cure?
When I'm using installshield to install sql runtime, I get the message "The Workstation service has not been started" during Configure SQL Server Agent. Does someone know what it means and how to solve it?
I've an DTS package, which has an Execute SQL Task. I'm doing the operations based on a parameter. I need to pass that in runtime. Is there any way to do that in Execute SQL Task? BOL says that the runtime parameter can be set by giving a question mark(?). But when I tried this, it says, value not passed.
I am Using Sql Server 2005 enterprise edition to develop an accounting system. now i want to distribute this software to other cleints. I have created a setup which includes all the components required for the run time of the software but i dont know how to include the sql server database in it. as i know that i have created access based software i used to include the database file in the setup and it would work but in the case of sql server i dont know how to do it . is there any runtime of sql server which needs to be downloaded or any other way out.
set @t = 'Test' set @chg22 = 'USE <%1> GO' set @chg22 = REPLACE(@chg22,'<%1>',@t)
exec(@chg22) SELECT * FROM Table_Name
but i am not getting it..in above example i am just took one line select * from table_name but actually in .sql file more that 300 lines are there so i m not able to concatenate it..
We run a regular query through Excel that is linked to a Microsoft SQL Server data source.
We have run this query routinely for the past few years. Suddenly this query has started returning an error message - “[Microsoft][ODBC SQL Server Driver] [SQL Server] Difference of two datetime columns caused overflow at runtime�. It seems to be a common error (as per Google search) however I don’t want to go messing around with something I don’t fully understand. Can anyone point me in the direction of what to look out for?
Currently im using SQL queries in combination with ASP.NET..
I have currently only one table('Requests'), which has all the user info of name,id,studyrequested etc.. A person can request for any number of studies.
Now the prob is that from now on whenever a user requests for a new study not only should his details be stored in the Request table but also in a new Users table. In the Users table that particular users details must only be loaded once.. i.e. if the same user enrolls for another study his details should only be updated in the requests table and not in the Users table, since his details have already been loaded once in the Users Table..
While this works fine for new users, but what bout the old users already stored in the Requests table before the Users table existed.
Is there any way i could run a sql script only once at runtime, so that all the old legacy users are loaded once in to the Users Table.
Hi!Can anybody tell me what I'm missing here?I'm trying to insert a new record into a sql-database from code atruntime (not stored procedure),but get the following error message:"The 'strUn' is not permitted in this context.Only constants, expressions or variables allowed here.Column names are not permitted."//Create sql connectionSqlConnection con = new SqlConnection("server=LocalHost;database=Users;uid=geir;pwd=geir");//Open database connectioncon.Open();//Create variables to hold values from textboxesstring strUn = txtUsername.Text;string strPw = txtPassword.Text;//Create a sqlCommand to insert textbox values into sql-databaseSqlCommand sqlcmd = new SqlCommand();sqlcmd.CommandText = "INSERT INTO tblUsers(Username,Password)VALUES(strUn, strPw)";sqlcmd.Connection = con;try{sqlcmd.ExecuteNonQuery();}catch(SqlException ex){lblInfo.Text = "ExecuteNonQuery failed because: " +"" +ex.Message;}finally{con.Close();}
My boss claims that there is a free SQL server runtime. Is thistrue? I was only aware of MSDE. But I believe the connections arecapped at 5 in MSDE. If there is a free SQL server runtime, are thereany limitations to using it (performance, connection count)? Thanks.
I want to combine a bunch of reports (which are stand alone reports on there own) as subreports in a parent or master report. We have done this in Crystal Reprots in order to get a group of reports out in one file.
The issue with Reporting Services is the header and footers of the subreports are not rendered, so I have to repeat the logo information for each subreport in the body of the parent report. I can do this in a table or list or rectangle, but I have to do this for each subreport. Is there any way that I can specify the name of the subreport at runtime? That way I can put everything into a List and drive it from the database. I noticed that the dropdown to select the report name does not allow you to create an expression for this field.
Does anyone have an idea on how to handle runtime parameters for a package? Ideally I'd like to have the package prompt the user for a date range and then store the 2 dates in package variables for use later in the package.
I am trying to create a report with unknown number of report items. (as I dont know how many columns the stored proc is going to return).Is there a way to create these type of reports using SSRS other than creating the rdl programatically using vb.net or c#?
