How Do I Programmatically Inert Data Using A SqlDataSource Control?
May 19, 2007
I just want to insert a record into a table using a SqlDataSource control. But I'm having a hard time finding examples that don't use data bound controls
I have this so far (I deleted the parts not related to the insert):
<asp:SqlDataSource ID="SqlDataSource2" runat="server" ConnectionString="<%$ ConnectionStrings:UserPolls %>"
InsertCommand="INSERT INTO [PollAnswers] ([PollId], [AnswerText], [AnswerCount]) VALUES (@PollId, @AnswerText, @AnswerCount)"
<InsertParameters>
<asp:Parameter Name="PollId" Type="Int32" />
<asp:Parameter Name="AnswerText" Type="String" />
<asp:Parameter Name="AnswerCount" Type="Int32" />
</InsertParameters>
</asp:SqlDataSource>
This is the data source for a gridview control I have on the page. I could set up an SqlDataSource for this alone if I need to, but i don't know if it would help.
From what I could find, in the code behind I should have
SqlDataSource2.Insert()
and SqlDataSource2 will grab the parameters and insert the record. The problem is I need to set the Pollid (from a session variable) and AnswerText (from a text box) at run time. Can I do this?
Diane
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
Im ripping my hair out here.I need to access the field in a datasource control of use in non presentation layer code based actions.I know the I can use a code base connection and query but I dont see why i need to make two trips the the DB when the info is already available.The datasource is attached to a details view control and the details view control is nested in a loginview controlI've tried defining but all I can get in the header name of the field but not the dataitem, the dataitem causes an error help please jim
ello all Would someone be so kind as to save me from getting balder through pulling my hair out. My aim is to extract data from a database using SQLDataSource, then edit the data and update the database using the SQLDataSource. I have achieve the problem of retrieving the data from the sqlDataSource:DataView openRemindingSeats = (DataView)SqlDataSource2.Select(DataSourceSelectArguments.Empty); //Int32 openRemindingSeats = SqlDataSource2.Select(DataSourceSelectArguments.Empty), DataView; foreach (DataRowView rowProduct in openReminding) { //Output the name and price lbl_NumOfSeatsLeft.Text = rowProduct["Remaining"].ToString(); } Within the sqlDataSource the sql code is as follows:SELECT [refNumber], [refRemaining] FROM [refFlights] WHERE ([refNumber] = @Number) So at the moment my problems is being able to edit and update data to the same SELECTed data.Thank you for any help that you might have... SynDrome
Hi I think I've programmatically created a SqlDataSource - which is what I want to do; but I can't seem to access details from the source - row 1, column 1, for example???? If Not Page.IsPostBack Then 'Start by determining the connection string valueDim connString As New Data.SqlClient.SqlConnection(ConfigurationManager.ConnectionStrings("ConnectionString").ConnectionString) 'Create a SqlConnection instanceUsing connString 'Specify the SQL query Const sql As String = "SELECT eventID FROM viewEvents WHERE eventID=17" 'Create a SqlCommand instanceDim myCommand As New Data.SqlClient.SqlCommand(sql, connString) 'Get back a DataSetDim myDataSet As New Data.DataSet 'Create a SqlDataAdapter instanceDim myAdapter As New Data.SqlClient.SqlDataAdapter(myCommand) myAdapter.Fill(myDataSet)Label1.Text = myAdapter.Tables.Rows(0).Item("eventID").ToString() -?????????????? 'Close the connection connString.Close() End Using End IfThanks for any helpRichard
I am sending a GUID to a form via the query string. If it exists I use helper functions to load most of the form text boxes. However, if it does not then a blank form is presented and the GUID is stored in a hidden field. Regardless, I use this hidden field to populate a grid that is attached to a sqldatasource. If I then add new datarows to the backend database programmatically, I cannot 'requery' the datasource to include those row upon a postback. I cannot seem to find a simple way to force the sqldatasource to rerun the query. Can anyone help.
I have made a SQLDataSource control with the select command: SELECT COUNT(*) AS 'Antall' FROM Utgivelse WHERE (medieID = @medieID) I want to use the "Antall" result programmatically in C# code. I try the following statement: IDataReader MyReader;
MyReader = CType(SqlDataSource2.Select(DataSourceSelectArguments.Empty),IDataReader); but it doesnot work. Can somebody help me how to get the data from th control ? Tom
Hello:I forgot what to do in a case like this. I have a SqlDataSource control within a label and I want to allow auto sorting, but when I click on the sort link the page comes back with:The SqlDataSource control 'sqlViewIncompleteForms' does not have a naming container. Ensure that the control is added to the page before calling DataBind.[HttpException (0x80004005): The SqlDataSource control 'sqlViewIncompleteForms' does not have a naming container. Ensure that the control is added to the page before calling DataBind.] System.Web.UI.WebControls.DataBoundControlHelper.FindControl(Control control, String controlID) +1590679 System.Web.UI.WebControls.ControlParameter.Evaluate(HttpContext context, Control control) +76 System.Web.UI.WebControls.Parameter.UpdateValue(HttpContext context, Control control) +46 System.Web.UI.WebControls.ParameterCollection.UpdateValues(HttpContext context, Control control) +103 System.Web.UI.WebControls.SqlDataSource.LoadCompleteEventHandler(Object sender, EventArgs e) +40 System.EventHandler.Invoke(Object sender, EventArgs e) +0 System.Web.UI.Page.OnLoadComplete(EventArgs e) +2010392 System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +1209How do I over come this? Do I have to use the find control method on page_load? Here is the code portion .vb<% 'Label which contains the default.aspx home content %><asp:Label ID="recip_home_display" runat="server" Visible="false"> <div align="center" class="HeaderSmall">Incomplete Recip Submissions</div> <asp:GridView ID="RecipStatusGridView" runat="server" AutoGenerateColumns="False" BorderWidth="0px" DataKeyNames="queue_id" DataSourceID="sqlViewIncompleteForms" AllowPaging="True" AllowSorting="True" CellPadding="2" CellSpacing="2" CssClass="TextSmall" HorizontalAlign="Center" Width="500px" Visible="False"> <Columns> <asp:BoundField DataField="queue_id" HeaderText="Incomplete Listings" HtmlEncode="False" InsertVisible="False" ReadOnly="True" SortExpression="queue_id"> <ControlStyle CssClass="LinkNormal" /> <ItemStyle HorizontalAlign="Center" /> <HeaderStyle HorizontalAlign="Center" /> </asp:BoundField> <asp:BoundField DataField="form_type" HeaderText="Listing Type" SortExpression="form_type"> <ItemStyle HorizontalAlign="Center" /> <HeaderStyle HorizontalAlign="Center" /> </asp:BoundField> <asp:BoundField DataField="listing_address" HeaderText="Listing Address" ReadOnly="True" SortExpression="listing_address"> <ItemStyle HorizontalAlign="Center" /> <HeaderStyle HorizontalAlign="Center" /> </asp:BoundField> <asp:BoundField DataField="last_modified_date" DataFormatString="{0:d}" HeaderText="Last Modified" HtmlEncode="False" SortExpression="last_modified_date"> <ItemStyle HorizontalAlign="Center" /> <HeaderStyle HorizontalAlign="Center" /> </asp:BoundField> </Columns> <HeaderStyle BackColor="#5C6F8D" /> <AlternatingRowStyle BackColor="#e9eaf0" /> </asp:GridView> <asp:SqlDataSource ID="sqlViewIncompleteForms" runat="server" ProviderName="System.Data.SqlClient" ConnectionString="<%$ Appsettings:connectionstring %>" SelectCommandType="StoredProcedure" SelectCommand="spGetIncompleteForms"> <SelectParameters> <asp:ControlParameter ControlID="active_member_id" Name="AgentId" /> </SelectParameters></asp:SqlDataSource></asp:Label>
Let's say... the sqldatasource has 3 columns: TableKey(int), TableName(string) and StartDate(datetime) and i'm writing a method to return the data type based on the column name.. for example: GetDataType("StartDate") should return "datetime"...what should i do?
I have a form setup, the code of which is listed below, and I would like to be able to submit the data that is entered in the form to a SQL 2005 standard database via the SqlDataSource control and a button. I'm just having trouble doing so because of the coding. Any help or suggestions would be greata s I've already read through the tutorials on this site and they don't go in depth very much. Thanks! <%@ Page Language="VB" %> <%@ Register Assembly="BasicFrame.WebControls.BasicDatePicker" Namespace="BasicFrame.WebControls" TagPrefix="BDP" %> <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"> <%@ import Namespace="System.Data" %> <%@ import Namespace="System.Data.SqlClient" %> <Script runat="server"> Private Sub InsertData(ByVal Source As Object, ByVal e As EventArgs) SqlDataSource1.Insert() End Sub </Script>
<html xmlns="http://www.w3.org/1999/xhtml" > <head runat="server"> <title>Weston E-Vault - New Customer Sign-Up Form</title> </head> <body> <form id="form1" runat="server"> <div style="text-align: center"> <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="Data Source=C26819_24561;Initial Catalog=ebackups;Integrated Security=True;User Instance=False" ProviderName="System.Data.SqlClient" InsertCommand="INSERT INTO backup_info(cust_name, cust_phone, cust_contact, cust_analyst, storage_plan, install_date, backup_data, backupexec_option, backup_program, cust_email) VALUES (@cust_name, @cust_phone, @cust_contact, @cust_analyst, @storage_plan, @install_date, @backup_data, @backupexec_option, @backup_program, @cust_email)"> <InsertParameters> <asp:ControlParameter ControlID="DropDownList1" DefaultValue="" Name="@cust_name" PropertyName="SelectedValue" /> <asp:ControlParameter ControlID="TextBox1" Name="@cust_contact" PropertyName="Text" /> <asp:ControlParameter ControlID="TextBox2" Name="@cust_phone" PropertyName="Text" /> <asp:ControlParameter ControlID="DropDownList3" Name="@cust_analyst" PropertyName="SelectedValue" /> <asp:ControlParameter ControlID="BDPLite1" Name="@install_date" PropertyName="Controls" /> <asp:ControlParameter ControlID="TextBox3" Name="@cust_username" PropertyName="Text" /> <asp:ControlParameter ControlID="TextBox4" Name="@cust_password" PropertyName="Text" /> <asp:ControlParameter ControlID="DropDownList2" Name="@storage_plan" PropertyName="SelectedValue" /> <asp:ControlParameter ControlID="TextBox5" Name="@backup_data" PropertyName="Text" /> <asp:ControlParameter ControlID="RadioButtonList1" Name="@backupexec_option" PropertyName="SelectedValue" /> <asp:ControlParameter ControlID="TextBox6" Name="@backup_program" PropertyName="Text" /> <asp:ControlParameter ControlID="TextBox7" Name="@cust_email" PropertyName="Text" /> </InsertParameters> </asp:SqlDataSource> <br /> <br /> <br /> <table> <tr> <td style="width: 100px"> <img src="/images/westonevault_logo.jpg" /></td> <td style="width: 703px; text-align: right"> <asp:Button ID="Button1" runat="server" BackColor="White" BorderColor="Navy" BorderStyle="Solid" BorderWidth="1px" Font-Names="verdana" Font-Size="Small" ForeColor="Navy" PostBackUrl="~/secure/tech_home.aspx" Text="Back" /><span style="font-size: 8pt; color: #000099"> </span></td> </tr> </table> <br /> <br /> <img src="/images/div.jpg" style="font-size: 8pt; color: #000099" /><br /> <br /> <div style="text-align: center"> <table style="font-size: 8pt; width: 730px; color: #000099; font-family: Verdana"> <tr> <td colspan="3" style="font-weight: bold; font-family: Verdana; text-align: left"> <span style="color: #000099">Customer Information<br /> </span> <hr style="color: #000099" /> <span style="font-weight: normal; font-size: 8pt; color: #000099">Please enter all customer data as accurately as possible. Any incorrect information on this form may result in incorrect data being backed up, reports not reaching the customer or they may<span style="font-size: 12pt"><strong> </strong><span style="font-size: 8pt">not receive the need</span></span>ed amount of disk space.<br /> <br /> </span> </td> </tr> <tr style="font-size: 8pt"> <td style="width: 174px; text-align: left"> <span style="font-family: Verdana"><span style="color: navy">Customer Name:<br /> </span> <asp:DropDownList ID="DropDownList1" runat="server" DataValueField="cust_name" Font-Names="Verdana" Font-Size="X-Small" ForeColor="Navy" Width="178px"> <asp:ListItem Selected="True">Select Customer Name.....</asp:ListItem> <asp:ListItem>Alaska Road Boring</asp:ListItem> <asp:ListItem>Allen and Peterson</asp:ListItem> <asp:ListItem>AK Guns</asp:ListItem> <asp:ListItem>ATS Alaska</asp:ListItem> <asp:ListItem>BC Contractors</asp:ListItem> <asp:ListItem>Bek of Alaska</asp:ListItem> <asp:ListItem>Brokentop Community Assoc.</asp:ListItem> <asp:ListItem>COHRA</asp:ListItem> <asp:ListItem>Crisis Pregnancy Center</asp:ListItem> <asp:ListItem>Gaines and Co.</asp:ListItem> <asp:ListItem Value="Gil Damond">Gil Damond</asp:ListItem> <asp:ListItem>GMW Fire</asp:ListItem> <asp:ListItem>Integrity Funding</asp:ListItem> <asp:ListItem>La Pine Community Clinic</asp:ListItem> <asp:ListItem>La Pine Fire District</asp:ListItem> <asp:ListItem>Lumbermens Ins.</asp:ListItem> <asp:ListItem>Murray</asp:ListItem> <asp:ListItem>NEI</asp:ListItem> <asp:ListItem>Northstar Center</asp:ListItem> <asp:ListItem>Redi Electric</asp:ListItem> <asp:ListItem>Robberson Ford</asp:ListItem> <asp:ListItem>South Anchorage District Office</asp:ListItem> <asp:ListItem>St. George Tanaq</asp:ListItem> <asp:ListItem>Stinebaugh</asp:ListItem> <asp:ListItem>THT Electric</asp:ListItem> <asp:ListItem>Watterson</asp:ListItem> <asp:ListItem>United Auto</asp:ListItem> <asp:ListItem>Weston - ANC</asp:ListItem> <asp:ListItem>Weston - BND</asp:ListItem> <asp:ListItem></asp:ListItem> </asp:DropDownList><br /> </span> </td> <td style="width: 187px; text-align: left"> <span style="font-family: Verdana"><span style="color: #000099">Customer Contact:<br /> </span> <asp:TextBox ID="TextBox1" runat="server" Font-Names="Verdana" Font-Size="X-Small" Width="164px"></asp:TextBox><br /> </span> </td> <td style="width: 136px; text-align: left"> <span style="font-family: Verdana"><span style="color: #000099">Customer Phone:<br /> </span> <asp:TextBox ID="TextBox2" runat="server" Font-Names="verdana" Font-Size="X-Small"></asp:TextBox><br /> </span> </td> </tr> <tr style="font-size: 8pt"> <td style="width: 174px; text-align: left"> <span style="font-family: Verdana"><span style="color: #000099">Customer E-Vault Username:<br /> </span> <asp:TextBox ID="TextBox3" runat="server" Font-Names="Verdana" Font-Size="X-Small" Width="150px"></asp:TextBox><br /> </span> </td> <td style="width: 187px; text-align: left"> <span style="font-family: Verdana"><span style="color: #000099">Customer E-Vault Password:<br /> </span> <asp:TextBox ID="TextBox4" runat="server" Font-Names="Verdana" Font-Size="X-Small" TextMode="Password" Width="150px"></asp:TextBox><br /> </span> </td> <td style="width: 136px; text-align: left"> <span style="font-family: Verdana"><span style="color: #000099">E-Vault Storage Plan:<br /> </span> <asp:DropDownList ID="DropDownList2" runat="server" DataValueField="storage_plan" Font-Names="Verdana" Font-Size="X-Small" ForeColor="Navy"> <asp:ListItem Selected="True">Select Plan.....</asp:ListItem> <asp:ListItem>0-300MB</asp:ListItem> <asp:ListItem>300MB-2GB</asp:ListItem> <asp:ListItem>2GB-4GB</asp:ListItem> <asp:ListItem>4GB-6GB</asp:ListItem> <asp:ListItem>6GB-8GB</asp:ListItem> <asp:ListItem>8GB-15GB</asp:ListItem> <asp:ListItem>15GB-25GB</asp:ListItem> <asp:ListItem>25GB-35GB</asp:ListItem> <asp:ListItem>35GB-50GB</asp:ListItem> <asp:ListItem>50GB-100GB</asp:ListItem> <asp:ListItem>100GB-200GB</asp:ListItem> </asp:DropDownList><br /> </span> </td> </tr> <tr style="font-size: 8pt"> <td style="width: 174px; text-align: left"> <span style="font-family: Verdana"><span style="color: #000099">Analyst:<br /> </span> <asp:DropDownList ID="DropDownList3" runat="server" DataValueField="cust_analyst" Font-Names="Verdana" Font-Size="X-Small" ForeColor="Navy" Width="162px"> <asp:ListItem>Select Analyst.....</asp:ListItem> <asp:ListItem>Brock McFarlane</asp:ListItem> <asp:ListItem>Cameron Farrally</asp:ListItem> <asp:ListItem>Chad Huls</asp:ListItem> <asp:ListItem>Eric Spinney</asp:ListItem> <asp:ListItem>Greg Freeman</asp:ListItem> <asp:ListItem>Harald Smit</asp:ListItem> <asp:ListItem>Kevin Mark</asp:ListItem> <asp:ListItem>Mark Anderson</asp:ListItem> <asp:ListItem>Mike Murphy</asp:ListItem> <asp:ListItem>Tim Elder</asp:ListItem> </asp:DropDownList><br /> </span> </td> <td style="width: 187px; text-align: left"> <span style="font-family: Verdana"><span style="color: #000099">Today's Date: (mm/dd/yyy)<br /> </span> <bdp:bdplite id="BDPLite1" runat="server" borderstyle="None" cssclass="bdpLite" dateformat="ShortDate" font-bold="False" font-names="verdana" font-size="X-Small" forecolor="Navy" selecteddate=""></bdp:bdplite> </span> </td> <td style="width: 136px; text-align: left"> <br /> </td> </tr> <tr style="font-size: 8pt"> <td colspan="3"> </td> </tr> <tr style="font-size: 8pt"> <td colspan="3" style="text-align: left"> <span style="font-family: Verdana"><span style="color: #000099"><strong> <br /> Backup Data<br /> <hr style="color: #000099" /> </strong><span>Please enter all directories and/or files and folders the customer wishes to have backed up. Be thourough yet brief as this is for historical and record keeping purposes.<br /> <br /> </span></span></span> </td> </tr> <tr style="font-size: 8pt"> <td colspan="3" style="text-align: left"> <asp:TextBox ID="TextBox5" runat="server" ForeColor="#000000" Height="130px" MaxLength="200" TextMode="MultiLine" Width="503px"></asp:TextBox><br /> </td> </tr> <tr style="font-size: 8pt"> <td style="width: 174px"> </td> <td style="width: 187px"> </td> <td style="width: 136px"> </td> </tr> <tr style="font-size: 8pt"> <td colspan="3" style="text-align: left"> <span style="font-family: Verdana"><span style="color: #000099"><strong> <br /> BackupExec / NTBackup<br /> <hr style="color: #000099" /> </strong><span>Does the customer have another backup program running that backs up their files to tape or another physical media.<br /> <br /> </span></span></span> </td> </tr> <tr style="font-size: 8pt"> <td colspan="2" style="height: 41px; text-align: left"> <asp:RadioButtonList ID="RadioButtonList1" runat="server" Font-Names="Verdana" Font-Size="X-Small" ForeColor="Navy" RepeatDirection="Horizontal" Width="127px"> <asp:ListItem Value="Yes">Yes</asp:ListItem> <asp:ListItem Value="No">No</asp:ListItem> </asp:RadioButtonList><span style="font-family: Verdana"><span style="color: #000099">If "Yes", what program do they use?</span> <asp:TextBox ID="TextBox6" runat="server" Font-Names="Verdana" Font-Size="X-Small" Width="200px"></asp:TextBox><br /> </span> </td> <td style="width: 136px; height: 41px"> </td> </tr> <tr style="font-size: 8pt"> <td style="width: 174px"> </td> <td style="width: 187px"> </td> <td style="width: 136px"> </td> </tr> <tr style="font-size: 8pt"> <td colspan="3" style="text-align: left"> <span style="color: #000099"><span style="font-family: Verdana"><strong> <br /> E-Mail Notifications<br /> <hr style="color: #000099" /> </strong><span>Does the customer wish to receive weekly reports about their backups? (This includes what directories are being backed up, how much storage space they have used on their plan and how much storage space is remaining).If so enter their e-mail address below. If they don't want to receive notifications, you can skip this section.<br /> <br /> </span></span></span> </td> </tr> <tr style="font-size: 8pt"> <td style="width: 174px; text-align: left"> <asp:TextBox ID="TextBox7" runat="server" Font-Names="Verdana" Font-Size="XX-Small" ForeColor="Navy" Width="201px"> N/A</asp:TextBox></td> <td style="width: 187px"> </td> <td style="width: 136px"> </td> </tr> <tr style="font-size: 8pt"> <td style="width: 174px"> </td> <td style="width: 187px"> </td> <td style="width: 136px"> </td> </tr> <tr style="font-size: 8pt"> <td style="width: 174px; height: 26px"> </td> <td style="width: 187px; height: 26px; text-align: left"> <asp:Button ID="Button2" runat="server" BackColor="White" BorderColor="Navy" BorderStyle="Solid" BorderWidth="1px" Font-Names="verdana" Font-Size="Small" OnClick="InsertData" Text="Submit Form" /></td> <td style="width: 136px; height: 26px"> </td> </tr> </table> </div> <div style="text-align: center"> <img src="/images/div.jpg" /><br /> <br /> <span style="font-size: 7pt; font-family: Verdana">Copyright 2006 Weston Technology Solutions </span> </div>
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
Hi, I'm reasonably new to ASP.NET 2.0 I'm in my wizard_FinishButtonClick event, and from here, I want to take data from the form and some session variables and put it into my database via a stored procedure. I also want the stored procedure to return an output value. I do not need to perform a select or a delete. For the life of me, I can't find a single example online or in my reference books that tells me how to accomplish this task using a SqlDataSource control. I can find lots of examples on sqldatasources that have a select statements (I don't need one) and use insert and update sql statements instead of stored procedures (I use stored procedures). I desperately need the syntax to: a) create the SqlDataSource with the appropriate syntax for calling a stored procedure to update and/or insert (again, this design side of VS2005 won't let me configure this datasource without including a select statement...which I don't need). b) syntax on how to create the parameters that will be sent to the stored procedure for this sqldatasource (including output parameters). c) syntax on how to set the values for these parameters (again...coming from form controls and session variables) d) syntax on how to execute, in the code-behind, the stored procedure via the sqldatasource. If anybody has sample code or a link or two, I would be most appreciative. Thank you in advance for any help!
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.
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.
Background - I have a page that uses a numeric value stored in a Session object variable as the parameter for three different SQLDataSource objects, which provide data to two asp:Repeaters and an asp:DataList. Also, in the Page_Load, I use this value to to seed a stored procedure and an SQLDataReader to populate several unbound Labels. This works fine. In addition, I have a collection of 6 TextBoxes, an unbound Listbox, and two Buttons to allow the user to do searching and selection of potential matches. This basically identifies a new numeric value that I store in the Session variable and PostBack the page (via one of the buttons). This also works fine.Problem - I have been tasked with taking a different page and adding six textboxes to collect the search values, but to post over to this page, populate the existing search-oriented TextBoxes, adn programmatically triggering the search. Furthermore, I have to detect the number of matching records and, if only 1, have the Repeaters and DataList display the results based on the newly selected record's key numeric value, as well as populating the unbound Labels. I have managed to get all of this accomplished except for programmatically triggering the Repeaters and DataList "refresh". These controls only populate as expected if a button is clicked a subsequent time, which makes sense, since that would trigger a PostBack and the Page_Load uses the new saved numeric key value from the Session.My history in app development is largely from Windows Forms development (VB6), this is my second foray into Web Form dev with ASP.NET 2.0. I am willing to acceptthat what I am trying to do does not fit into the ASP environment, but I have to think that this is something that has been done before, and (hopefully) there is a way to do what I need. Any ideas, oh great and wise Forum readers? *smile*
Hi, All I'm using Gridview and SqlDataSource to dynamically display the contents in different tables, as followed: <% dataSource.ConnectionString = System.Configuration.ConfigurationSettings.AppSettings["connectionString"]; dataSource.SelectCommand = "SELECT * FROM " + tableName; gridView.DataBind(); dataSource.UpdateCommand = "";%> <asp:SqlDataSource ID="dataSource" runat="server"></asp:SqlDataSource><asp:GridView ID="gridView" runat="server" DataSourceID="dataSource" AllowPaging="True" AllowSorting="True" AutoGenerateEditButton="True" BorderColor="Silver" BorderStyle="Solid" BorderWidth="1px" CellPadding="3" PageSize="20" DataKeyNames="ID" OnRowDataBound="tableGridView_RowDataBound"> <HeaderStyle BackColor="#C0C0FF" /> <AlternatingRowStyle BackColor="#C0FFC0" /></asp:GridView> The datasource take the "tableName" as argument to determine which table to display. My problem is I can't figure out a way to programmatically construct the UpdataCommand for the SqlDataSource. I try to get the field names from the HeaderRow, but all the cells are empty in this row. Does anyone know what causes the problem or how to construct the UpdateCommand properly. Thanks!
I am using SqlDataSource programmatically in my data access layer - mainly for convenience but it does generally work fine with no obvious performance issues. The problem I have is with getting back an output parameter. I have an insert-type stored procedure (in Sql Server 2005) operating on a table with an identity column as the primary key: ALTER PROCEDURE [dbo].[InsertAlbum](@ArtistID int, @Title nvarchar(70), @NewID int OUTPUT)ASDECLARE @err intINSERT INTO dbo.ALBUMS (ARTISTID, TITLE)VALUES (@ArtistID, @Title)SELECT @err = @@error IF @err <> 0 RETURN @errSET @NewID = SCOPE_IDENTITY() This works fine when run from Sql Server Management Studio and @NewID has the correct value. My data access code is roughly as follows: dsrc = New SqlDataSource()dsrc.ConnectionString = ConnectionStringdsrc.InsertCommand = "InsertAlbum"dsrc.InsertCommandType = SqlDataSourceCommandType.StoredProcedureDim parms As ParameterCollection = dsrc.InsertParametersDim newid As IntegerAddParameter(parms, "ArtistID", TypeCode.Int32, ParameterDirection.Input, 0, album.ArtistID)AddParameter(parms, "Title", TypeCode.String, ParameterDirection.Input, 0, album.Title)Dim p As New Parameter("NewID", TypeCode.Int32)p.Direction = ParameterDirection.Outputparms.Add(p)Try Dim rv As Integer = dsrc.Insert() newid = parms("NewID") Return newidCatch ex As Exception Return -1End Try The row is inserted into the database, but however I try to define and add the NewID parameter it never has a value. Has anyone tried to do this and can tell me what I am doing wrong? Jon
Hey All for some reason I can not get this right and/or find what I am looking for. I have an SQLDataSource with a PartID set as the filtered value in the Datasource Query. I am trying to use code beside to set the value and I am failing...lol... Here is my attempt at it, SqlDataSource1.SelectParameters("PartID") = txtPartID.Text Any help would be great!
I've found example code of accessing an SQLDataSource and even have it working in my own code - an example would be Dim datastuff As DataView = CType(srcSoftwareSelected.Select(DataSourceSelectArguments.Empty), DataView) Dim row As DataRow = datastuff.Table.Rows(0) Dim installtype As Integer = row("InstallMethod") Dim install As String = row("Install").ToString Dim notes As String = row("Notes").ToString The above only works on a single row, of course. If I needed more, I know I can loop it.The query in srcSoftwareSelected is something like "SELECT InstallMethod, Install, Notes FROM Software"My problem lies in trying to access the data in a simliar way when I'm using a SELECT COUNT query. Dim datastuff As DataView = CType(srcSoftwareUsage.Select(DataSourceSelectArguments.Empty), DataView) Dim row As DataRow = datastuff.Table.Rows(0) Dim count As Integer = row("rowcnt") The query here is "SELECT COUNT(*) as rowcnt FROM Software"The variable count is 1 every time I query this, no matter what the actual count is. I know I've got to be accessing the incorrect data member in the 2nd query because a gridview tied to srcSoftwareUsage (the SQLDataSource) always displays the correct value. Where am I going wrong here?
Hello, I want to loop through the first 10 records that are showing in a gridview with several pages that is populated by a sqldatasource. I can loop through the sqldatasource and get the list of values, but I'm doing something wrong because the 10 records it prints out are not the same 10 records the user sees in the gridview...They can click a search button which changes the sort, and they can click on the column headings to change the sort order. Where's the best place to put the looping code? I need the result to be the same as what the users sees. 1 Protected Sub GridView1_Sorted(ByVal sender As Object, ByVal e As System.EventArgs) Handles GridView1.Sorted2 Dim i As Integer = -13 Dim sTest As String = ""4 Dim vwExpensiveItems As Data.DataView = CType(SqlDataSource1.Select(DataSourceSelectArguments.Empty), Data.DataView)5 6 'Loop through each record7 i = -18 For Each rowProduct As Data.DataRowView In vwExpensiveItems9 i = i + 110 'Output the name and price11 If i > 9 Then12 Exit For13 End If14 sTest = rowProduct("employeeid")15 Response.Write("RowSorting " & i.ToString & " [" & sTest & "]<br>")16 Next17 End Sub18
hi friends, I created sqldatasource control. In select command i written the query like this "select * form emp" and bounded in grid.How can I change the query for searching the details according the date wise when i click the search button.
Hello experts i have a SqlDataSource Control on my web form. Here is the source for the control <asp:SqlDataSource ID="sqlSearchDataSource" runat="server" ConnectionString="<%$ ConnectionStrings:DbefomsConnectionString %>" SelectCommand="SELECT [icon], [file_name], [path] FROM [tblfile] WHERE ([file_name] = @file_name)"> <SelectParameters> <asp:ControlParameter ControlID="txtSearch" DefaultValue="0" Name="file_name" PropertyName="Text" Type="String" /> </SelectParameters> </asp:SqlDataSource> This control displays icon, file_name, path in GridView Control The problem is How can i store the above field values in an asp.net variable. Thanks Regards Ali
Do you have to use the sqldatasource in conjuction with another control like gridview or dropdown list? I'd like to check to see if a record exists in one table before inserting data into another.
Hello.I'm new to ASP.NET and trying to write data to a Microsft SQL Server. I have created a SqlDataSource control, which is 'connected' to my SQL server.Now my question is how i can put data in the database throw the control, and read data from it. I've read the site, but it's still unclear for me..Greetings!
Hello: I am having a little issue with an error that is frustrating me. I have a SqlDataSource within an asp:label control and also have a two dropdownlists inside that asp:label control also. When I select from the first dropdownlist, it should enable the second dropdownlist with the populated data, but instead I receive. The SqlDataSource control 'sqlGetMLSByCity' does not have a naming container. Ensure that the control is added to the page before calling DataBind. Any ideas why this would be? Here is the web from portion: <asp:Label ID="recip_form_request" runat="server" Visible="false"> <ul> <li>Step one: Select listing type.</li> <li>Step two: Select the City that you are reciprocating to.</li> <li>Step three: Complete the Recip Form.</li> </ul> <asp:DropDownList ID="recip_form_type" runat="server" AutoPostBack="true" Visible="False"> <asp:ListItem Value="0" Text="(Select Form)"></asp:ListItem> <asp:ListItem Value="1" Text="Residential"></asp:ListItem> </asp:DropDownList> <br /> <asp:DropDownList ID="outside_assoc_cities" runat="server" AutoPostBack="True" Enabled="False" DataTextField="city" DataValueField="id" Visible="False"></asp:DropDownList><br /> <asp:Label ID="city_mls_desc" runat="server"></asp:Label><br /> <asp:Button ID="page1_btn" runat="server" Text="Next >>" Visible="False" /> <asp:SqlDataSource ID="sqlCities" runat="server" ProviderName="System.Data.SqlClient" ConnectionString="<%$ AppSettings:connectionstring %>" SelectCommandType="Text" SelectCommand="SELECT id, city FROM tCityMaster"></asp:SqlDataSource> <asp:SqlDataSource ID="sqlGetMLSByCity" runat="server" ProviderName="System.Data.SqlClient" ConnectionString="<%$ AppSettings:connectionstring %>"> <SelectParameters> <asp:ControlParameter ControlID="outside_assoc_cities" Name="City" /> </SelectParameters> </asp:SqlDataSource></asp:Label> then the code behind: Protected Sub outside_assoc_cities_SelectedIndexChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles outside_assoc_cities.SelectedIndexChanged If outside_assoc_cities.SelectedIndex > -1 Then Dim ItemId As Integer = Integer.Parse(outside_assoc_cities.SelectedValue) Dim MLSID As String = Nothing Dim cmd As New SqlCommand Dim con As New SqlConnection cmd.Connection = con Dim sqlGetMLSByCity_sql As SqlDataSource = CType(Page.FindControl("sqlGetMLSByCity"), SqlDataSource) 'using connectionstring from SqlDataSource Control con.ConnectionString = sqlGetMLSByCity_sql.ConnectionString 'con.ConnectionString = sqlGetMLSByCity.ConnectionString cmd.CommandType = CommandType.Text cmd.CommandText = "SELECT mls_id FROM pwaordev..tCityMaster WITH(NOLOCK) WHERE ID=@ID" cmd.Parameters.Add("@ID", SqlDbType.Int, 4).Value = ItemId Try con.Open() MLSID = cmd.ExecuteScalar con.Dispose() cmd.Dispose() Catch ex As Exception con.Dispose() cmd.Dispose() Response.Write(ex.ToString()) End Try 'Further logic to display button depending on MLS If Not String.IsNullOrEmpty(MLSID) Then If MLSID = "B" Then city_mls_desc.Text = "The MLS system that covers " & outside_assoc_cities.SelectedItem.Text & " is Greater South Bay MLS." page1_btn.Visible = True ElseIf MLSID = "A" Then city_mls_desc.Text = "The MLS system that covers " & outside_assoc_cities.SelectedItem.Text & " is Big Bear BOR, MLS, which is not covered by Pacific West Association of REALTOR®, please contact us for more information." page1_btn.Visible = False ElseIf MLSID = "C" Then city_mls_desc.Text = "The MLS system that covers " & outside_assoc_cities.SelectedItem.Text & " is Combined L.A./Westside MLS." page1_btn.Visible = True ElseIf MLSID = "F" Then city_mls_desc.Text = "The MLS system that covers " & outside_assoc_cities.SelectedItem.Text & " is Crisnet MLS, which is not covered by Pacific West Association of REALTOR®, please contact us for more information." page1_btn.Visible = False ElseIf MLSID = "Y" Then city_mls_desc.Text = "The MLS system that covers " & outside_assoc_cities.SelectedItem.Text & " is DCAoR - Yucca Valley Area, which is not covered by Pacific West Association of REALTOR®, please contact us for more information." page1_btn.Visible = False ElseIf MLSID = "D" Then city_mls_desc.Text = "The MLS system that covers " & outside_assoc_cities.SelectedItem.Text & " is Desert Area MLS." page1_btn.Visible = True ElseIf MLSID = "L" Then city_mls_desc.Text = "The MLS system that covers " & outside_assoc_cities.SelectedItem.Text & " is i-Tech Glendale/Pasadena." page1_btn.Visible = True ElseIf MLSID = "M" Then city_mls_desc.Text = "The MLS system that covers " & outside_assoc_cities.SelectedItem.Text & " is MRM - Multi-Regional MLS." page1_btn.Visible = True ElseIf MLSID = "R" Then city_mls_desc.Text = "The MLS system that covers " & outside_assoc_cities.SelectedItem.Text & " is RIM - Rim of the World MLS, which is not covered by Pacific West Association of REALTOR®, please contact us for more information." page1_btn.Visible = False ElseIf MLSID = "G" Then city_mls_desc.Text = "The MLS system that covers " & outside_assoc_cities.SelectedItem.Text & " is SDI - San Diego, which is not covered by Pacific West Association of REALTOR®, please contact us for more information." page1_btn.Visible = False ElseIf MLSID = "S" Then city_mls_desc.Text = "The MLS system that covers " & outside_assoc_cities.SelectedItem.Text & " is SOCAL MLS - Southern California MLS." page1_btn.Visible = True ElseIf MLSID = "T" Then city_mls_desc.Text = "The MLS system that covers " & outside_assoc_cities.SelectedItem.Text & " is Outside Area, which is not covered by Pacific West Association of REALTOR®, please contact us for more information." page1_btn.Visible = False End If 'test.Text = MLSID 'If test.Text = "L" Then 'mls_text.Text = "i-Tech" 'End If End If End If End Sub Any help would be great, thank you!
I have an SqlDatSource that I have fully setup at design time, but I don't want it to open and load as soon as the page loads, instead I want to open it based on a condition. How do you do that with a DataSource? I know that I can simply remove the Select query, and then set it at run time, but I'm looking for something better, that allows me to have the Select query set at design time, in part because I have a lot of parameters.
Hello: I have two SqlDataSource controls on two different pages: one is updating a table and the other is reading from the same table. Now is it possible that the reader SqlDataSource control can be refreshed immediately (to reflect updates) when the other control updates the table?
