I am trying to customize my update statement and this MUST happen in codebehind, otherwise I will be overwriting data. The following is updating the data that is should be. The problem is that eventhough my UpdateCommand is clear in my .aspx and there are no parameters set... It is STILL running it's own update and overwriting the information it isn't supposed to. From what I can tell, it is using a default. What can I do to prevent this?
SqlDataSource1.UpdateCommand = "UPDATE MyTable SET MyField1=@MyField1 WHERE MyField2=@MyField2 AND MyField3=@MyField3"SqlDataSource1.UpdateParameters.Add("MyField1", "CustomText")
I need to provide defaults and sometimes overrides for items in SQLDataSource's UpdateParameters. I am attempting to do this in a FormView's ItemUpdating and ItemInserting events as follows: //======================================================================== // FormView1_ItemUpdating: //======================================================================== protected void FormView1_ItemUpdating(object sender, FormViewUpdateEventArgs e) { // not sure if this is the bets place to put this or not? dsDataSource.UpdateParameters["UpdatedTS"].DefaultValue = DateTime.Now.ToString(); dsDataSource.UpdateParameters["UpdatedUserID"].DefaultValue = ((csi.UserInfo)Session["UserInfo"]).QuotaUserID; } In the example above I am attempting to set new values for the parameters which will replace the existing values. I have found that using the DefaultValue property works ONLY if there is no current value for the parameter. Otherwise the values I specify are ingnored.The parameters of an ObjectDataSource provide a Value property but SQLDataSource parameters do not.How can I provide an override value without needing to place the value in the visible bound form element???If you can answer this you will be the FIRST person ever to answer one of my questions here!!!Thanks,Tony
I've been working on an application that uploads an RDL to Reporting Services (through the SOAP webservice method CreateReport) programmatically. I'm having difficulty setting up the data source properties for my uploaded report. In particular the Data Source Credentials property.
The datasource for my report doesn't require credentials. By default after I upload the report to Reporting Services, the Data Source Credentials property is set to "Credentials supplied by the user running the report". How do I go about setting the Data Source Credentials property to "Credentials are not required" programmatically through the webservice?
I have a package which contains a foreach container. Can anyone help me in setting the properties for the enumerators programmatically??? I am trying to set the properties for the enumerator "ForEach File Enumerator"
I have a templatefield dropdownlist in a gridview. i'm trying to make the dropdownlist the controlID in updateparameters tag. But I get the following error when trying to update the datasource Thank you!. "Could not find control 'DropDownList3' in ControlParameter" <asp:TemplateField HeaderText="IT Member" SortExpression="txtenteredby"> <EditItemTemplate> <asp:DropDownList ID="DropDownList3" runat="server" DataSourceID="ObjectDataSource1" DataTextField="txtUserName"> </asp:DropDownList> </EditItemTemplate> <ItemTemplate> <asp:Label ID="Label3" runat="server" Text='<%# Bind("txtenteredby") %>'></asp:Label> </ItemTemplate> </asp:TemplateField> <asp:SqlDataSource id="CustomersSqlDataSource" runat="server" ConnectionString="<%$ ConnectionStrings:SPIdb %>"
UpdateCommand="UPDATE table SET txtBName=@txtBName,txtPType=@txtType,txtBrand=@txtBrand,txtCondition=@txtCondition,txtstatus=@txtstatus,txtenteredby=@txtenteredby,txtDescription=@txtDescription where intID=@intID" <UpdateParameters> <asp:ControlParameter Name="txtenteredby" ControlId="DropDownList3" PropertyName="SelectedValue"/> </UpdateParameters> </asp:SqlDataSource>
Hi there!I'm trying to populate automactly a record in a table. I've tried this: <asp:SqlDataSource ID="SqlDataSource2" runat="server" ConnectionString="<%$ ConnectionStrings:ProducaoLigacao %>" SelectCommand="UPDATE [Producao] SET [Prodas] = @Prodas WHERE ([ProdUserID] = @ProdUserID)"> <UpdateParameters> <asp:Parameter DefaultValue="1" Name="ProdUserID" Type="Int32" /> <asp:Parameter DefaultValue="999" Name="Prodas" Type="Int32" /> </UpdateParameters> </asp:SqlDataSource> Is this possible? Thanks in advance!
I need to programmatically backup a database in SQL Server Express. I actually also need to programmatically restore it from a backup file. How can I do this programmatically? I know how to do simple ADO commands for simple db operations, but backup and restore sound like "meta" commands to me, and I don't know where to begin from.
I'm building SSIS packages through code and I would like to set the properties of some custom tasks (not data flow tasks) to expressions. I've done some searches but turned up nothing. This is the only thing I'm hitting a brick wall on at the moment; Books Online has been excellent in detailing how to create packages via code up to this point.
For the sake of argument, let's say I want to set the SqlStatementSource property of an Execute SQL task to this value:
"INSERT INTO [SomeTable] VALUES (NEWID(), '" + @[User:omeStringVariable] + "')"
Ok. So I have this ASP.NET page and I've programmatically taken a report from the report server and rendered it in PDF. Now I would like to take this a step further and save the report as a pdf document on the local machine.
So at this point I have a byte array representing the document, now how would I save this as a pdf on the local machine? I'm unaware of an ASP Response method to allow this and I'm unaware of a SSRS ReportingService method, but as I said I'm unaware...
I've seen several post asking for that possibility, but all 've read, didn't help me.Some sing SQLDMO, other suggest to use SQLSMO, others only explaining to connect to a server and then do "CREATE DATABASE".I will do this within .Net. Connecting to a SQL Server 2005 and execute "CREATE DATABASE" I could realize.But how doing this with SQLExpress? Trying to do SqlConnection.Open with a non existing DB does not work., says "file not exists".Or do I only have the wrong connection string? Can someone post here an excample connection string, which works with a non existing database?Some hints I've read make me considering to use SQLSMO. But I don't have it on my computer. Where do I get it from? Any links would be nice.
Hi Guys, I have this SqlDataSource, that counts some records and sets it in "NotStartedBugs". How do I retrive "NotStartedBugs" programmatically? <asp:SqlDataSource ID="SqlDataSource3" runat="server" ConnectionString="<%$ ConnectionStrings:ConnectionString %>" SelectCommand="SELECT (SELECT COUNT(*) AS Expr1 FROM tickets WHERE (TicketType = 'Bug') AND (TicketStatus = 'Not Started')) AS NotStartedBugs"></asp:SqlDataSource>
Hi, I am using Visual Web Developer 2005 Express Edition. I am trying to SELECT three information fields from a table when the Page_Load take place (so I select the info on the fly). The refering page, sends the spesific record id as "Articleid", that looks typically like this: "http://localhost:1424/BelaBela/accom_Contents.aspx?Articleid=2". I need to extract the "Article=2" so that I can access record 2 (in this example). How do I define the SelectParameters or QueryStingField on the fly so that I can define the WHERE part of my query (see code below). If I remove the WHERE portion, then it works, but it seem to return the very last record in the database, and if I include it, then I get an error "Must declare the scalar variable @resortid". How do I programatically set it up so that @resortid contains the value that is associated with "Articleid"? My code is below. Thank you for your advise! RegardsJan/******************************************************************************* * RETRIEVE INFORMATION FROM DATABASE *******************************************************************************/ // specify the data source string connContStr = ConfigurationManager.ConnectionStrings["tourism_connect1"].ConnectionString; SqlConnection myConn = new SqlConnection(connContStr);
// define the command query String query = "SELECT resortid, TourismGrading, resortHits FROM Resorts WHERE ([resortid] = @resortid)"; SqlCommand myCommand = new SqlCommand(query, myConn);
// open the connection and instantiate a datareader myConn.Open(); SqlDataReader myReader = myCommand.ExecuteReader();
// loop thru the reader while (myReader.Read()) { Label5.Text = myReader.GetInt32(0).ToString(); Label6.Text = myReader.GetInt32(1).ToString(); Label7.Text = myReader.GetInt32(2).ToString(); }
// close the reader and the connection myReader.Close(); myConn.Close();
Hello. Im trying to create an SQLDataSource control programmatically. I need to do this because I want to do some stuff on my MasterPage's 'Page_Init' event. heres my code (Master.master.vb): Protected Sub Page_Init(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Init lblUser.Text = Page.User.Identity.Name
Dim PUser As New ControlParameter PUser.ControlID = "lblUser" PUser.Name = "LoginName" PUser.PropertyName = "Text" PUser.Type = TypeCode.String PUser.DefaultValue = Page.User.Identity.Name
Dim SQLDS_Login As New SqlDataSource SQLDS_Login.ID = "SQLDS_Login" SQLDS_Login.ConnectionString = "I put conection string here. How do I use the one on my web.config?" SQLDS_Login.SelectCommand = "SELECT [LoginID], [LoginName], [Role], [Status] FROM [myLogin] WHERE ([LoginName] = @LoginName)" SQLDS_Login.SelectParameters.Add(PUser) SQLDS_Login.SelectCommandType = SqlDataSourceCommandType.Text
When i run, i get this error message: The SqlDataSource control 'SQLDS_Login' does not have a naming container. Ensure that the control is added to the page before calling DataBind. I never had any problem with Inserts, Updates and Deleting, but I have never made it work for Select when doing it programmatically. Can you help me with this?
I am trying to add a number of dates into a Sql database. Basically I want the user to add the start and end date and then all the dates in between are are added to a database in unique records. I can create an ArrayList but I don't know how to bind it to an SqlDataSource Dim startdate As DateTime = tbstartdate.Text Dim enddate As DateTime = tbenddate.Text Dim datediff As TimeSpan = enddate.Subtract(startdate) Dim noofdays As Integer = datediff.Days Dim ar As New ArrayList Dim i For i = 0 To noofdays ar.Add(startdate.AddDays(i)) Next Sorry if this is a total noob question....
I have a GridView bound to a SqlDataSource. On page load I would like to programmatically specify a SelectParameter value based on the role of the user. This SelectParameter will be used in my WHERE clause. The closest post I can find is http://forums.asp.net/thread/1233258.aspx but no answer was posted. What code would I use to modify a SelectParameters value? Is it possible to reference a parameter by name (SqlDataSource1.SelectParameters["usertype"]) or does it have to be by index? (SqlDataSource1.SelectParameters[0]) Alternatively, perhaps I'm going about this in the wrong way, is there a better way to have dynamic GridView content based on the role of the user? Thank you very much for your help.
I have a system that processes inserts that originate from automatic data collection subsystems on manufacuturing cells. The system processes about 2500 records a day. The system is isolated with no ready support or attention. My goal is to automate any and every reasonable admin task. My present activity centers on re-indexing the main table (receives the data from the inserts, supplies the data for web based reporting).
The table - tb_production_log - receives inserts that are time stamped and bear a Machine_id. The table has a clustered index built on the Machine_id (int) and Date_time (time of data's acquisition). The table only receives Inserts, the records are never Updated. No inserts are out of time sequence (no older records ever have to be 'wedged' in amongst existing records). Ulitmately, the table is tested daily for records with age > 365 days. Such records are Deleted.
For the past week, I have been running a monitoring stored procedure on my test box to track the fragmentation of the tb_production_log table. It's based on DBCC SHOWCONTIG with some extra tests. After capturing the SHOWCONTIG data, the sp runs a test query against the table to emulate a typical User report. I track the time this query takes. The query covers records over the last 7 days. (approx. 17,500 records involved). In addition, I track the time it takes Inserts to run. Inserts are done in batches from an external app. I get a RecordsPerSecond data point for each batch.
I have a system that processes inserts that originate from automatic data collection subsystems on manufacuturing cells. The system processes about 2500 records a day. The system is isolated with no ready support or attention. My goal is to automate any and every reasonable admin task. My present activity centers on re-indexing the main table (receives the data from the inserts, supplies the data for web based reporting).
The table - tb_production_log - receives inserts that are time stamped and bear a Machine_id. The table has a clustered index built on the Machine_id (int) and Date_time (time of data's acquisition). The table only receives Inserts, the records are never Updated. No inserts are out of time sequence (no older records ever have to be 'wedged' in amongst existing records). Ulitmately, the table is tested daily for records with age > 365 days. Such records are Deleted.
For the past week, I have been running a monitoring stored procedure on my test box to track the fragmentation of the tb_production_log table. It's based on DBCC SHOWCONTIG with some extra tests. After capturing the SHOWCONTIG data, the sp runs a test query against the table to emulate a typical User report. I track the time this query takes. The query covers records over the last 7 days. (approx. 17,500 records involved). In addition, I track the time it takes Inserts to run. Inserts are done in batches from an external app. I get a RecordsPerSecond data point for each batch.
Is the any easy way to clone a database programatically? I've searched the web but did not come up with anything.
At the moment I create a new blank database (based on the model database) and create tables, indexes, etc. via asp. (The databases are always exactly the same)
It seems to me that it would be a better option to create one database with tables, indexes, etc and copy it to a new database (on the same server).
One of the options I'm considering would be to create a database, fill it with tables and indexes, etc. and then detach it. Whenever I need a new database all I would have to do is use the file system object to copy and rename the mdf file and then reattach the new mdf file.
I realise that I could also make the changes to the model database so that all new databases have the same structure but that would be my last resort.
Is there any easier way to do this? A stored procedure perhaps?
I recently had to find ways to programmatically backup and restore anSQL Server Express database.For backup, I found a one-liner stored procedure that does the job(BACKUP DATABASE Toy to disk = 'c:ProjectsToy.bak'.My question is, is there something as simple for a restore operation?Thanks in Advance-Kostas
Hi,The following script does not return any resultset against a test dbwhile I know for a fact tables with letter "aaa" has columns thatcontains "ccc".What's wrong? the the inner cursor?Thanks.-- get all tbls with letter aaadeclare @tbl varchar(8000)declare tblCursor cursor forSELECT nameFROM sysobjectsWHERE xtype = 'U'AND name LIKE '%aaa%'open tblCursorfetch next from tblCursorinto @tblwhile (@@fetch_status = 0)begin-- get all columns with letter ccc and replace it with nothing /remove itdeclare @tbuffer varchar(4000)declare @cbuffer varchar(8000)declare abnormal_cols cursor forSELECT o.name, c.nameFROM sysobjects oJOIN syscolumns c ON o.id = c.idWHERE o.xtype = 'U'AND c.name LIKE '%ccc%'and o.id = object_id('+@tbl')-- ORDER BY c.nameopen abnormal_colsfetch next from abnormal_colsinto @tbuffer,@cbufferwhile (@@fetch_status = 0)begin-- EXEC sp_rename '+@tbuffer+'.['+@cbuffer+']','+Replace(+@cbuffer+','%ccc%','')','COLUMN';-- testprint @tbuffer + ', ' + @cbuffer;fetch next from abnormal_colsinto @tbuffer,@cbufferendclose abnormal_colsdeallocate abnormal_cols;fetch next from tblCursorinto @tblendclose tblCursordeallocate tblCursor;
Does anybody know can you programmatically instantiate a control at run-time? I need to make a dynamic number of images appear on a report and programmatically grab an image name based on data in the report.
In the old DTS, we can use the ActiveX Script to change any task's property programmatically.
Can we still do it in SSIS? Using the Script task? It seems changing the value of variables then use a expression can do some of the work, but what if a task has no expression defined?
Say, I want to change the Fuzzy look up reference table name.
I wrote a custom IIS log reader windows service that reads and parses log data and periodically uploads it to a SQL server table which I run my reports from later. Currently I read the file by giving the absolute physical path name for the log directory followed by the log file name I anticipate depending on date/time stamps.
My question is: Is there a programmatic way to get the directory path which is configured in IIS to store the IIS log files to? We use non default location e.g. D:DATALOGSIISW3SVC1 or something similar, which might change from server to server. Also, I am not worried about the individual log file names but the directory. I would prefer a C# code example.
I got a user who is requesting a weekly report to be exported in csv (comma delimited) format. But this process will run weekly using schedule job and he wants the file to save to a certain directory on the network. Two part questions...
1. Is there a way to create a .csv file programmatically after runing the query?
2. How would I save the .csv file to a specified directory on the network?
