Hi, how I do create SqlDataSource in the code-behind,setting Select SQL along with its parameters and, finally automically generating Del, Update, Insert SQL.
Is there a way to achieve this goal as it would in VS designer?
Thanks,
Ricky.
Hi
I have generated a ssis package that creates a text file based on a query retrieving records from the table.
Now i want the file name to be appended with the date and time of package execution. So the filename will be something like ; Filename_MMDDYYYY.txt.
How can i do this with the help of SSIS?
Anyone having an idea would be of great help.
Regards,
Salman Shehbaz.
Hi,
There is one manual process done by my friend. The process is " In query analyser, she runs a sql query and the resultset of Sql query, she copies and pastes into a excel workbook".
I wanted to write a SSIS package to automate it and providing a UI, in which she can give connection details and paste the SQL query and the package will in turn generate excel workbook in the target path". I thought of trying ExecuteSQL task, but the output resultset of ExecuteSQL task cannot be mapped dynamically to excel source(correct me if I am wrong). If I use Dataflow task, then also whether dynamic mapping of SQL output to Excel destination input is possible?
Can you please tell me the best approach for achieving the above functionality?
Thanks,
Venkataraman R
I have to display three months name as column name in ssrs reports. These month column will be dynamically. So i have to display the column dynamically.
View 3 Replies View RelatedHello
Following is my code which is not working.
<asp:SqlDataSource ID=events runat=serverSelectCommand="SELECT * FROM TBL_EVENT WHERE SECTION_ID=<%= Session("ID")%>"ConnectionString = "<%$ appSettings:SQLConnectionString %>"</asp:SqlDataSource>
I want to pass the value of Session("ID") into that query. How can I do that?
I have several Sql Tables, each containing more than 100 fields, on which I need to perform display operation through GridView, Insert and Update through an array of dynamically created and databound TextBoxes.
I would like to use a single page to manage all Sql Tables on the basis of the name of the specific one invoked by the user.
In order to achieve that, I need to dynamically configure the SqlDataSource Web Control present on the page for each Sql Table. In particular I need to generate at runtime the InsertCommand and the UpdateCommand.
On how to do it I have no idea.
Federico
Hi guys.Im using a gridview to show some training data on my website which is populated by:<asp:SqlDataSource ID="ARENATraining" runat="server" ConnectionString="<%$ ConnectionStrings:ARConnection %>" SelectCommand="SELECT [EventType], [CourseLink], [EventDate], [EventTitle], [EventLocation], [EventWebsite] FROM [qry_FutureEvents] WHERE ([EventPrivate] = @EventPrivate) ORDER BY [EventDate]"> <SelectParameters> <asp:Parameter DefaultValue="FALSE" Name="EventPrivate" Type="Boolean" /> </SelectParameters> So far so good..However I want to be able to filter the data.A) Show everythingB) Show individual EventType i.e. Seminar, Training etc etcIs it possible to generate the Select Command via a function? (im not using stored procedures for this part of the site, nor really want to at the moment). Id appreciate any help, otherwise ill be forced to curse and swear and use the good ol repeater and figureing out how to page it! Cheers guys
View 6 Replies View RelatedI need to generate a csv file from another csv file, seems to be simple but let's go the trick thing:
Needs to have maximum 1000 lines, if I reach to this, I need to create another csv and fill that new one.
Exemplifying:
I have a csv file called fileA and this has 2000 lines and another csv called fileB with 1500 lines.
I need to loop a folder and get the fileA, create an output called FileAOutput and start to fill that, if I reach to 1000 lines, I need to create a FileAOutput_2 and fill the other 1000 lines...so I'll go to fileB and do the same thing, but in the second case, I'll have 500 lines in the second output.
CancelSelectOnNullParameterHi,Suppose I have a SQLDataSource with a
SelectCommand="SELECT * FROM STOCKS WHERE USER=@USER ORDER BY STOCK_NAME"
and @USER is set as a session parameter. No problem till this part, but in some postbacks, I wish to add a new WHERE criteria to my SQL, so I do:DSSQL.SelectParameters.Add("@STOCK_NAME", "ABC123");
DSSQL.SelectCommand = DSgvStoklar.SelectCommand.Replace(" ORDER BY ", " AND STOCK_NAME LIKE @STOCK_NAME+'%' ORDER BY ");
gridViewDS.DataBind();
and wish that my SelectCommand is modified and a new parameter is added.
The SQL is modified, however -I guess- new parameter is not added, so I get "Must declare the scalar variable "@STOCK_NAME" error.
How can I solve, any ideas?
Hi,
say I have two Sqldatasources objects:SqlDataSource1 and SqlDataSource2....
Does anybody know how can I alter programmatically these two sqldatasources in a gridview?
Thanks!!!
Can you dynamically set the name of the table in the SelectCommand section of the SqlDataSource? (If it is relevant, I code in C#)
For example,
<asp:SqlDataSource runat="server" ID="SqlDataSource1" ConnectionString="<%$ ConnectionStrings:Test-MySQL %>" ProviderName="<%$ ConnectionStrings:Test-MySQL.ProviderName %>" SelectCommand="SELECT * FROM TestTable">
I would like to replace 'TestTable' with the name of a table that is extracted from a string array in the code-behind.
Appreciatively,Peter
I wish to dynamically assign Select Statement to SqlDataSource. Anyone has any idea?? Is it possiable at all?
I am coding using C# under Framwork 2.0
How can i assign select statement to a sqldatasource
I mean if the user clicks button 1
sqldatasource=-"select * from customers where status='ClOSED' "
or else if the user clicks button2
sqldatasource="Select * from Customers Where Status='OPEN' "
I want to bind the sqldatasource to a gridview finally .
Is this possible ?
I tried sqldatasource filters But am reallly confused
Any suggestions/solutions are appreciated
thankyou
Hi all
I have a cms-page where i want to display various entry-categories like news etc....I want to define which kind of entries should be shown by a parameter in the URL (e.g. cms.aspx?category=news). So far everything is OK.
To display the entries actually I'm using the following SqlDataSource:
<asp:SqlDataSource ID="SqlDataSourceCMS" runat="server" ConnectionString="......."SelectCommand="SELECT * FROM [cms] where category = news"></asp:SqlDataSource>
What i need, is to set the category which i want to show dinamically, like with a variable.
Does anybody know how i can set something like a variable in the SelectCommand property of the SqlDataSource?
Hello all,
Ok, I finally got my SqlDataSource working with Oracle once I found out what Oracle was looking for. My next hurdle is to try and set the Update Command and Parameters dynamically from a variable or radiobutton list. What I'm trying to accomplish is creating a hardware database for our computers by querying WMI and sending the info to textboxes for insertion and updating. I got that part all working nicely. Now I want to send the Computer name info to a different table column depending on if it is a laptop or desktop. I have been tossing around 2 ideas. A radiobutton list to select what it is and change the SQL parameters or do it by computer name since we have a unique identifier as the first letter ("W" for workstation, "L" for Laptop). I'm not sure what would be easiest but I'm still stuck on how this can be done. I posted this same question in here a few days ago, but I didn't have my SqlDataSources setup like I do now, I was using Dreamweaver 8, it is now ported to VS 2005. Below is my code, in bold is what I think needs to be changed dynamically, basically i need to change DESKTOP to LAPTOP...Thanks for all the help I've gotten from this forum already, I'm very new to ASP.NET and I couldn't do this without all the help. Thanks again!
<asp:SqlDataSource ID="SqlDataSource2" runat="server" ConnectionString="<%$ ConnectionStrings:CAT %>"ProviderName="<%$ ConnectionStrings:CAT.ProviderName %>" SelectCommand='SELECT * FROM "COMPUTER"' UpdateCommand="UPDATE COMPUTER SET DESKTOP = :DESKTOP, TECH = :TECH, SERVICE_TAG = :SERVICE_TAG WHERE USERNAME=:USERNAME">
<UpdateParameters>
<asp:ControlParameter Name="USERNAME" ControlId="txtUserName" PropertyName="Text"/>
<asp:ControlParameter Name="SERVICE_TAG" ControlId="txtServiceTag" PropertyName="Text"/>
<asp:ControlParameter Name="TECH" ControlId="txtTech" PropertyName="Text"/>
<asp:ControlParameter Name="DESKTOP" ControlId="txtComputerName" PropertyName="Text"/>
</UpdateParameters>
</asp:SqlDataSource>
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.
Hello everybody,I was configuring a SqlDataSource control using SQL Authentication mode.I first added a database file (testdb.mdf) through Solution Explorer-Add New Items. Then through Database Explorer I created a table named "info"Then while configuring the SqlDataSource control I used the SQL Authentication mode and attached the "testdb.mdf" database file.Test Connection showed success. But when I hit the Ok button of the wizard it displayed the following error message:Failed to generate a user instance of SQL Server. Only an integrated connection can generate a user instance.While configuring the SqlDataSource control I clicked "New Connection". Under Data Source section I tried both Microsoft SQL Server and Microsoft SQL Server Database File. And in both the cases I attached a databese file(testdb.mdf). Plz enlighten me on this.Thanks and Regards,Sankar.
View 1 Replies View Relatedi am using visual web developer 2005 with SQL Express 2005 with VB as the code behindi have one database and three tables in itfor manipulating each table i am using separate SqlDataSource() is it sufficient to use one SqlDataSource() for manipulating all the three tables ? i am manipulating all the tables in the same page only please help me
View 1 Replies View RelatedHi!
When MS published starter kits there were files .sql in App_Data. This files contained some sample data for a DB. How to create such files when I have database with data ?
Jarod
how generate an xml file from data in sqlserver2005
and read it in sql again
One of my first tasks on my current project was to generate all of the SQL scripts needed to rebuild the database. Of course, one of the catches was that everything needed to be separated out (table columns in different script from PKs different from FKs different from DEFAULTs, etc., etc.). And we wanted our own comment block inserted. So, I couldn't just use the Generate SQL Script from Enterprise Manager and be done with it. I was going to need to do some more work. Well, here's one of the scripts I wrote to build the FK scripts. Execute this script, then copy & paste the results into a new window. Break apart into separate .sql files as desired.
NOTE: Be sure to show results in Text (not grid) and change the display options in QA to return more than just 256 characters.
/***************************************************************************************************
Author: Mark Caldwell
Date: 11/15/2002
Descrip: Generate scipt commands to ADD FOREIGN KEY CONSTRAINTS for constraints already in DB.
NOTE: Be sure to set your Tools/Options/Results/Maximum Characters per column to a
large enough number (such as 2000) to display the entire command.
***************************************************************************************************/
SELECT
'----------------------------------------------------------------------------------------------------
-- ' + so2.name + ' to ' + so3.name + '
----------------------------------------------------------------------------------------------------
IF OBJECTPROPERTY(OBJECT_ID(N''' + so1.name + '''), ''IsForeignKey'') = 1 BEGIN
ALTER TABLE ' + so2.name + ' DROP CONSTRAINT ' + so1.name + '
PRINT '' -- DRP - ' + so1.name + '''
END
G' + 'O
ALTER TABLE ' + so2.name + '
ADD CONSTRAINT ' + so1.name + '
FOREIGN KEY (' + ISC1.COLUMN_NAME + ')
REFERENCES ' + so3.name + '(' + ISC2.COLUMN_NAME + ')
PRINT '' -- ADD - ' + so1.name + '''
G' + 'O
'
FROM sysforeignkeys sfk
JOIN sysobjects so1 on sfk.constid = so1.id
JOIN sysobjects so2 on sfk.fkeyid = so2.id
JOIN sysobjects so3 on sfk.rkeyid = so3.id
JOIN INFORMATION_SCHEMA.COLUMNS ISC1 on so2.name = ISC1.TABLE_NAME AND sfk.fkey = ISC1.ORDINAL_POSITION
JOIN INFORMATION_SCHEMA.COLUMNS ISC2 on so3.name = ISC2.TABLE_NAME AND sfk.rkey = ISC2.ORDINAL_POSITION
ORDER BY so2.name
Edited by - AjarnMark on 11/20/2002 02:46:11
Hi,I have 30 Table with same structure but it will accept different value at RunTime according to AirCraftType. I wand to create a stored prosedure that will accept TableName, FlightDate, FlightNo. whenever i wnat to execute this code in the database I received error :Must declare the variable '@TableName'create proc InsertDataForAirBus330 @FlightNo varchar(5),@TableName varchr(5),@FlightDate datetime
as
insert into @TableName values(@JourneyDate,@FlightNo,18,42,280,3,7,35)plz suggest me how to solve this pboblem...Jasim...
How to create a sql statement that dynamically matches a database name in a stored proc. Somthing like this:
Select Table_name
From Information_schema.Tables
Where Table_type = 'BASE TABLE' and Objectproperty (Object_id(Table_name), 'IsMsShipped') = 0
and table_name like 'Item%Master'
and (DATABASENAME=????????????????)
Hi....I am fresh to asp.net .... i want to get the Request.QueryString Value Dynamically in SQL Query ... I have attached the coding of two files please gothrough and give the solution..First File : -actyearwise.aspx <%@ Page Language="C#" AutoEventWireup="true" CodeFile="actyearwise.aspx.cs" Inherits="acts_actyearwise" MasterPageFile="~/MasterPage.master" %><asp:Content ID="Content1" ContentPlaceHolderID="ContentPlaceHolder1" runat="server"> <asp:FormView ID="FormView1" AllowPaging="false" DataSourceID="AccessDataSource1" Width="80%" align="center" runat="server"> <ItemTemplate> <tr style="font-family: Arial; font-size: small;"> <td align="left" width="7%"> <a id="href1" href="actcheck.aspx?2007" target="_self">2007</a> </td> <td align="left" width="7%"> <a id="A1" href="actcheck.aspx?2006" target="_self">2006 </a> </td> <td align="left" width="7%"> <a id="A2" href="actcheck.aspx?2005" target="_self">2005 </a> </td> <td align="left" width="7%"> <a id="A3" href="actcheck.aspx?2004" target="_self">2004 </a> </td> </tr> </ItemTemplate> </asp:FormView> <asp:SqlDataSource ID="AccessDataSource1" runat="server" OnSelecting="AccessDataSource1_Selecting" ConnectionString="Data Source=.SQLEXPRESS;AttachDbFilename=C:InetpubwwwrooteLawApp_DatasampleDB.mdf;Integrated Security=True;User Instance=True" ProviderName="System.Data.SqlClient" SelectCommand="SELECT * FROM actindex "></asp:SqlDataSource></asp:Content><asp:Content ContentPlaceHolderID="FooterPlaceHolder1" ID="Content2" runat="server"></asp:Content> actyearwise.aspx.csusing System;using System.Data;using System.Configuration;using System.Collections;using System.Web;using System.Web.Security;using System.Web.UI;using System.Web.UI.WebControls;using System.Web.UI.WebControls.WebParts;using System.Web.UI.HtmlControls;public partial class acts_actyearwise : System.Web.UI.Page{ protected void Page_Load(object sender, EventArgs e) { } protected void AccessDataSource1_Selecting(object sender, SqlDataSourceSelectingEventArgs e) { }}// This page works Properly... In the actyearwise.aspx ..just some years with link... i have entered ...the href page is actcheck.aspx(below).. In this page i want the years dynamically.. please go throughSecond file : -actcheck. aspx <%@ Page Language="C#" AutoEventWireup="true" CodeFile="actcheck.aspx.cs" Inherits="acts_actfiles_actcheck" MasterPageFile="~/MasterPage.master" %><asp:Content ID="Content1" ContentPlaceHolderID="ContentPlaceHolder1" runat="server"> <asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" CellPadding="3" DataKeyNames="actno" DataSourceID="AccessDataSource1" Width="100%" AllowSorting="true"> <Columns> <asp:BoundField HeaderText="actno" Visible="False"/> <asp:TemplateField SortExpression="actno"> <ItemTemplate> <table width="80%" align="center"> <tr> <td align="left" width="3%" colspan="3"> <%# Eval("actno") %> </td> <td align="left" width="75%"> <a id="href1" href="actshorttitle.aspx?<%# Eval("actid") %>" target="_self"> <%# Eval("actname") %> </a> </td> </tr> </table> </ItemTemplate> </asp:TemplateField> </Columns> </asp:GridView> <asp:SqlDataSource ID="AccessDataSource1" runat="server" OnSelecting="AccessDataSource1_Selecting" ConnectionString="Data Source=.SQLEXPRESS;AttachDbFilename=C:InetpubwwwrooteLawApp_DatasampleDB.mdf;Integrated Security=True;User Instance=True" ProviderName="System.Data.SqlClient" SelectCommand="SELECT * FROM actindex where actyear = 'check' "> </asp:SqlDataSource></asp:Content><asp:Content ContentPlaceHolderID="FooterPlaceHolder1" ID="Content2" runat="server"></asp:Content> actcheck. aspx.csusing System;using System.Data;using System.Configuration;using System.Collections;using System.Web;using System.Web.Security;using System.Web.UI;using System.Web.UI.WebControls;using System.Web.UI.WebControls.WebParts;using System.Web.UI.HtmlControls;public partial class acts_actfiles_actcheck : System.Web.UI.Page{ protected void Page_Load(object sender, EventArgs e) { String check = Request.QueryString.ToString(); } protected void AccessDataSource1_Selecting(object sender, SqlDataSourceSelectingEventArgs e) { }} The text i had bold and italics in the actcheck.aspx... is the problem..... From the actyearwise.aspx .. i was passing the actyear value ..... In actcheck,aspx .. i want to get it dynamically in SQL Query...... Please give the solution .. I am using the SQL database.Table name actindexfields nameactnoactnameactidactyear In the actyear field i have different years .... Thanks in advance
View 5 Replies View RelatedI'm trying to dynamically set the TOP value in a query, but it doesn't work. Any other way it can be done?
Doesn't work:
declare @sometopvalue int
set @sometopvalue = 100
select top @sometopvalue
field1,
field2,
field3
from table1 ORDER BY BRDCST_DT_TM field1 desc
Hi Guys: I am trying to get a top parameter dynamically but it does not seem to work
SELECT TOP
(SELECT COUNT(DISTINCT RegionID) AS COUNT
FROM Sales)
*
FROM Sales
but when I hard code the value it works
SELECT TOP
2
*
FROM Sales
any ideas...Thanks
I have a query that pulls many values and I want to loop through each value dynamically using a query.
SELECT COL1 FROM TABLE1
Results:
A
B
C
D
I want those results to loop and run this:
SELECT * FROM TABLE2 WHERE COL2 = A
SELECT * FROM TABLE2 WHERE COL2 = B
SELECT * FROM TABLE2 WHERE COL2 = C
SELECT * FROM TABLE2 WHERE COL2 = D
Hello,
I am trying to create a sp dynamically. However each time, I run my sp, which creates the dynamic SQL for the sp, then it automatically changes:
CREATE PROCEDURE .....
to
Alter PROCEDURE ....
Then I am obvoiusly getting the error msg:
Invalid object name.
How can I get rid of that?
HI chaps
my scenario is that, i have a table in which i have column such as col01,col02,col03....col31
i want to retrieve the records related to that column by specifying the name dynamically (by using loop) as fist three character are same ('col??') and i have to do integer increment in last two character (???01...???31). Is it possible that I can use variable for column name?, if yes how? or is there any other way to achieve this task ?
waiting for your reply
regards
Hi,
Using a ssis package I have several tasks.
A variable is also created called FileName.
There is a flat file connection called InputFileName.
initially when I designed the package, I hardcoded the pass of one of the .csv files into the path of the flatfile connection.
There is a dataflow which has flatfile source and oledb destination. These two basically pickup the file and import into the database using mapping.
So therefore the package works and data gets imported into the database.
Then I used the variable to make the filename dynamic. So I removed the hardcoded connectionstring in the InputFileName.
Is this the right thing to do to make the package dynamic? I would like to pass in any .csv filename and so the package runs.
My question is now that the connectionstring is deleted but instead placed an expression for the filename, then how does the system know which file to use?
Thanks
Hi, there;
I am doing a SSIS project, my target is:
I have a source folder which have some *.dbf files (the number of
files will change as time goes on) exported from another application. I want my SSIS package to load data from these dbf
files in the source folder to my SQL2005 database no mather how many
dbf files will be.If the destination table already exists in the database,
the logic logic will do update (delete and import again), if not, create a new table on th fly and then
pump the data.
What I did here is that I have a foreach loop container which contains Script
task, Execute SQL task and a data flow. The Foreach container now can
connect to the *.dbf files successfully, the Script task can read
schema information from each dbf file and create script "Create table ...."
successfully and Execute SQL task can create table in my new empty
database successfully! But I have problem in the data flow task: my OLEDB Source points to the dbf file folder and set Data Access mode to "Table name or view name variable",but when I set Variable Name to a package level variable ("tableName"), I get error: Error at Data Flow Task_Pumping data from Admar [OLE DB Source [2737]]: A destination table name has not been provided.
Exception from HRESULT: 0xC0202042 (Microsoft.SqlServer.DTSPipelineWrap)
Does anybody know what to do to create table and pumping data on the fly? (Using variable as table name)
Big Thanks here.
I need an sql statement that dynamically matches a database name in a stored proc. Here is my attempt
Select Table_name
From Information_schema.Tables
Where Table_type = 'BASE TABLE' and Objectproperty (Object_id(Table_name), 'IsMsShipped') = 0
and table_name like 'Item%'
and (DATABASENAME=????????????????)
Can someone help me out?
Thanks.
I have an FTP Task where I have to set the server password at run-time. I have the ftp connection manager set up. What I normally do is configure the ftp server connection manager using a dtsConfig file but I can't do that in this case. I won't know the serverpassword until I get a parameter from the user.
View 3 Replies View Related