Using SqlDataSource To Execute A Select Statement In The Code File???
Nov 27, 2006
Hi you all,
In abc.aspx, I use a GridView and a SqlDataSource with a SelectCommand. The GridView's DataSourceID is the SqlDataSource.
In abc.aspx.cs, I would like to use an IF statement in which if a criterion is not satistied then I will use the SqlDataSource with another SelectCommand string. Unfortunately, I have yet to know how to write code lines in order to do that with the SqlDataSource. Plz help me out!
View 1 Replies
ADVERTISEMENT
May 23, 2008
Hi,I've never used an SQLDataSource programatically before.Its not even executing the select command because I've got a trace on the database and theres no executing of the Sproc.Here's my code behind: Dim sql As New SqlDataSource sql.ConnectionString = ConfigurationManager.ConnectionStrings("My connection").ConnectionString sql.SelectCommand = "Select_All" sql.SelectCommandType = SqlDataSourceCommandType.StoredProcedure sql.SelectParameters.Add("@Param1", txtParam1.Text) sql.SelectParameters.Add("@Param2", txtParam2.Text) sql.DataSourceMode = SqlDataSourceMode.DataReader gvOrderItemsReport.DataSource = sql gvOrderItemsReport.DataBind() Any ideas? I must be missing something.
View 5 Replies
View Related
Jan 11, 2006
Hello!
I would like to set the SqlDataSource in the code behind file and not to have it in the aspx file. How can I do that?
Regards,
Gato Gris
View 1 Replies
View Related
Aug 15, 2007
hi, i have a .aspx.cs code page file and i wanna to send some parameters to sqldatasource in page_load event.i wrote this line of code but it errors that the System.Web.UI.Webcontrols.parameters is a type and not a namespace.here my code:</p><p> </p><pre class="alt2" dir="ltr" style="border: 1px inset ; margin: 0px; padding: 6px; overflow: auto; width: 640px; height: 226px; text-align: left;">if (Session["membartype"].tostring() == "siteadmin1") SqlDataSource1.SelectCommand ="SELECT DocumentID, DocumentCode, DocumentDate,RequestCode, Delivery, Expr1,CityCode, Title FROM (SELECT dbo.EnterDocument.DocumentID, dbo.EnterDocument.DocumentCode, dbo.EnterDocument.DocumentDate,dbo.EnterDocument.RequestCode, dbo.EnterDocument.Delivery, dbo.EnterDocument.EnterType AS Expr1,dbo.EnterDocument.codecity AS CityCode, dbo.Location.Title FrOM dbo.Location RIGHT OUTER JOIN dbo.EnterDocument ON dbo.Location.codecity = dbo.EnterDocument.codecity) AS T1";ParameterCollection parameter=new ParameterCollection[7]; parameter.Add(DocumentID,text,TextBox1.Text); parameter.Add(DocumentCode,text,TextBox4); parameter.Add(FromDocumentDate,text,TextBox6); parameter.Add(untilDocumentDate,text,TextBox5); parameter.Add(PersonID,SelectedValue,DropDownList2); parameter.Add(EnterTypeID,SelectedValue,DropDownList3); parameter.Add(usercode,String,usercode); SqlDataSource1.SelectParameters.Add(parameter); GridView1.DataBind();</pre><p> thanks,M.H.H
View 2 Replies
View Related
Mar 27, 2007
Hi all,I drag sqldatasource to my form, and then adding a button there. I want when clicking the button to be able to use the sqldatasource1.select statement . I found some parameters that this method used but still dont know how to figure it out, which was IEnumerable Select (DataSourceSelectArguments a)for example when the button it clicked I want to perform the select * from employee Thanks
View 1 Replies
View Related
May 16, 2006
I have multiple SqlDataSources on a page. I only want them to execute the select query when the user performs a certain action. Is this possible? Or do they all execute as soon as the page is loaded?
View 1 Replies
View Related
Mar 15, 2008
hi all
I have to execute the stored procedure from code file
string constr = ConfigurationSettings.AppSettings["ConnectionString"];SqlConnection con = new SqlConnection(constr);con.Open();SqlCommand cmd = new SqlCommand("GetTax",con);cmd.CommandType = CommandType.StoredProcedure;SqlParameter paramFrom = new SqlParameter("@from", SqlDbType.VarChar, 50);paramFrom.Value = "JFK";SqlParameter paramTo = new SqlParameter("@To", SqlDbType.VarChar,50);paramTo.Value = "HOU";SqlParameter paramAirline = new SqlParameter("@Airline", SqlDbType.VarChar,50);paramAirline.Value = "US";SqlParameter rpTax = new SqlParameter("@Tax",SqlDbType.Int);rpTax.Direction = ParameterDirection.Output;cmd.Parameters.Add(rpTax);
insted of this way can i execute stored procedure any other way
like
exec MystoredProc "param1"."param2","param3"
i appreciate u r help
View 2 Replies
View Related
Sep 21, 2007
Hello all,
I have been working with a DetailsView control for the past week and it is a great control, but also lacks on some departments. Anyhow I need to know what the best approach for this scenerio would be?
I have a SqlDataSource"
<asp:SqlDataSource ID="SqlUpsertAffiliateDetails" runat="server" ConnectionString="<%$ ConnectionStrings:connectionstring %>"
SelectCommand="SELECT am.affiliate_id AS AffiliateId, am.member_id AS MemberId, m.First_Name, m.Last_Name, am.category_id AS CategoryId, ac.category_name, am.profile_web_address AS WebAddress, am.profile_email_1 AS Email, am.comments AS Comments, am.date_modified FROM tAffiliateMaster AS am WITH (NOLOCK) INNER JOIN tAffiliateCategories AS ac WITH (NOLOCK) ON am.category_id = ac.category_id INNER JOIN rapdata..Member AS m WITH (NOLOCK) ON am.member_id = m.Member_Number WHERE (am.affiliate_id = @AffiliateId)"
UpdateCommand="spUpsertAffiliateProfile" UpdateCommandType="StoredProcedure">
<SelectParameters>
<asp:QueryStringParameter Name="AffiliateId" QueryStringField="affiliate_id" />
</SelectParameters>
<UpdateParameters>
<asp:Parameter Name="Action" Type="Byte" DefaultValue="2" />
</UpdateParameters>
</asp:SqlDataSource>
And my SP:/* 09-19-07 Used to update affiliate profile */
CREATE PROCEDURE spUpsertAffiliateProfile
@Action tinyint,
@AffiliateId int,
@MemberId int = -1,
@CategoryId int,
@WebAddress varchar(50),
@Email varchar(50),
@Comments varchar(1500)
AS
SET NOCOUNT ON
-- Find errors first, check is not needed if deleting
IF @Action <> 3
IF NOT EXISTS (SELECT Member_Number FROM rapdata..Member_Association WHERE Member_Number = @MemberId AND Status = 'A' AND Association_ID = 'TRI' AND Bill_Type_Code LIKE '%AF%')
BEGIN
SELECT retval = 'A qualified member ID was NOT found. Action Failed.', errorcount = 1, 0 AS affiliate_id
RETURN
END
IF @Action = 1
IF EXISTS (SELECT member_id FROM tAffiliateMaster WHERE member_id = @MemberId)
BEGIN
SELECT retval = 'This member has already been listed. Action Failed.', errorcount = 1, 0 AS affiliate_id
RETURN
END
IF @Action = 1 AND @AffiliateId = 0-- insert
BEGIN
INSERT INTO tAffiliateMaster
(member_id, category_id, profile_web_address, profile_email_1, comments)
VALUES
(@MemberId, @CategoryId, @WebAddress, @Email, @Comments)
SELECT retval = 'Record Entered', errorcount = 0, @@IDENTITY AS affiliate_id
RETURN
END
ELSE IF @Action = 2 AND @AffiliateId > 0-- update
BEGIN
UPDATE
tAffiliateMaster
SET
category_id= @CategoryId,
profile_web_address=@WebAddress,
profile_email_1=@Email,
comments=@Comments
WHERE
affiliate_id = @AffiliateId AND member_id = @MemberId
SELECT retval = 'Record Updated', errorcount = 0, @AffiliateId AS affiliate_id
RETURN
END
ELSE IF @Action = 3 AND @AffiliateId > 0-- delete
BEGIN
DELETE
tAffiliateMaster
WHERE
affiliate_id = @AffiliateId
SELECT retval = 'Record Deleted', errorcount = 0, 0 AS affiliate_id
RETURN
END
GO
My question is how will I be able to return the retval? Will I need to do it within the code behind of the SqlDataSource Updated Event?
Thanks!
View 3 Replies
View Related
Oct 28, 2007
Hi Everyone,
I am trying to change the select statement of an sqldatasource if a check box is checked.
I am using the SqlDataSourceSelectingEventArgs but i can't get it to work, anyone got any pointers?
Code BehindProtected Sub LocMan_Searching(ByVal sender As Object, ByVal e As SqlDataSourceSelectingEventArgs) Handles LocManSearch.Selecting
If cb_Today.Checked = True ThenLocManSearch.SelectCommand = "SELECT * FROM [LocMan_CV] WHERE ([area] LIKE '%' + " & dd_Area.SelectedValue.ToString() & "+ '%') AND [available] LIKE '%' + " & Date.Today & "+ '%')"
Else : LocManSearch.SelectCommand = "SELECT * FROM [LocMan_CV] WHERE ([area] LIKE '%' + " & dd_Area.SelectedValue.ToString() & " + '%')"
End If
End Sub
My SQLDATSOURCE
<asp:SqlDataSource ID="LocManSearch" runat="server" ConnectionString="<%$ ConnectionStrings:MYLOCDEVConnectionString %>" >
<SelectParameters>
<asp:ControlParameter ControlID="dd_Area" Name="area" PropertyName="SelectedValue"
Type="String" />
</SelectParameters>
</asp:SqlDataSource>
Thanks in advance
Chris
View 6 Replies
View Related
Jan 31, 2008
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
View 1 Replies
View Related
Jun 16, 2008
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
View 8 Replies
View Related
Apr 15, 1999
hi, I need to run a batch file in specific time of the week. can anyone show me the code to run a batch file with both ways: window nt schedualer and ms sql server task manger.... I do appreciate your help
Ali
View 5 Replies
View Related
May 1, 2007
Is there a way to retreive the SQL Statement with the values from the parameters merged together? I know how to retreive the SQL Select Statement and the parameters separately but I need to retreive the final SQL.
For example:
SELECT name FROM employee WHERE id = @id
I would like to retreive from SQLDataSource
SELECT name FROM employee WHERE id = 1
Thank you
View 4 Replies
View Related
Dec 4, 2006
I would like to put a select statement inside this execute statement
use [AnalysisReport]
GO
GRANT EXECUTE ON (select statement) TO [test]
GO
anyone?
View 5 Replies
View Related
Mar 20, 2014
I am a junior dba not a developer. So I'm just trying to get use to write code in T-SQL.
Anyways, I have a table which is dba.dbhakyedek.
Columns are
dbname, username, class_desc, object_name, permission_name, state_desc
I have statement
select case
when class_desc='OBJECT_OR_COLUMN' then 'GRANT '+permission_name+' ON '+'['+left(object_name,3)+'].'+'['+substring(object_name,5,len(object_name))+ '] TO '+username
WHEN class_desc='DATABASE_ROLE' THEN EXEC sp_addrolemember N'object_name', N'MC'
end
from dba.dbhakyedek
where username='MC'
This statement was running successfully until exec sp_addrolemember thing. I just learned that i can't call a sp in select case but i couldnt figure out how to do it.
View 6 Replies
View Related
Aug 25, 2006
I have following requirement. From OLE-DB source I am getting IDS. Then lookup with some master data. Now I have only matching IDs. Now I need find some filed(say Frequency from some table for each above id). I already write stored procedure for same where I am passing ID as parameter.Which is working fine when I run it SQL server management studio.
Query is sort of
Select field1,fiel2... from table 1 where id = @id
@id is each ID from lookup
Now I want to call this stored procedure in Data flow. I tried it using OLE DB command but it did not return output of stored procudre. I am getting output same what ever I am passing input.
Is there way to do this? In short my requirement is execute parametrized select statement using data flow trasformation component.
View 8 Replies
View Related
Apr 8, 2006
In Code Behind, What is proper select statement syntax to retrieve the @BName field from a table?Using Visual Studio 2003SQL Server DB
I created the following parameter:Dim strName As String Dim parameterBName As SqlParameter = New SqlParameter("@BName", SqlDbType.VarChar, 50) parameterBName.Value = strName myCommand.Parameters.Add(parameterBName)
I tried the following but get error:Dim strSql As String = "select @BName from Borrower where BName= DOROTHY V FOWLER "
error is:Line 1: Incorrect syntax near 'V'. 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.Data.SqlClient.SqlException: Line 1: Incorrect syntax near 'V'.
Source Error:
Line 59: Line 60: Line 61: myCommand.ExecuteNonQuery() 'Execute the query
View 2 Replies
View Related
Apr 19, 2007
I'm looking for a way to refer to a package variable within any
Transact-SQL code included in either an Execute SQL or Execute T-SQL
task. If this can be done, I need to know the technique to use -
whether it's something similar to a parameter placeholder question
mark or something else.
FYI - I've been able to successfully execute Transact-SQL statements
within the Execute SQL task, so I don't think the Execute T-SQL task
is even necessary for this purpose.
View 5 Replies
View Related
Jan 25, 2007
Hi,I need some help here. I have a SELECT sql statement that will query the table. How do I get the return value from the sql statement to be assigned to a label. Any article talk about this? Thanks geniuses.
View 2 Replies
View Related
Jul 23, 2007
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.
View 7 Replies
View Related
Dec 30, 2006
How do I programically (in the code-behind-file) assign a value from the database to a variable using a sqldatasource?...
I will do something like this:
Dim MyCity as string = SqlDataSource.MyCityColumnInTheDatabase
(The sqldatasource select everything in my "CityTable")
Hope you understand what I mean...
View 5 Replies
View Related
Mar 13, 2007
i have an sqldatasource which runs a select command. How does one access the results in the codebehind, that is on page load set Label1 to value returned from the datasource. Please note that there will only ever be one value returned by the datasource This is fairly urgent so any help is needed quick thanks in advanceIlan
View 2 Replies
View Related
Mar 3, 2006
Can you access a SQLDatasource in the code-behind?
Example: label1.text = sqldatasource.field
I am just migrating apps to ASP.NET 2.0...
Confused as to the best approach for paramaterized data access and binding to labels.
View 3 Replies
View Related
Jun 16, 2006
Hi,
I have a problem with the <asp:SqlDataSource. The thing that I try to do is to create a SQL statement. I am not sure if this can be done or not ( just started asp.net).
Something like this:
<%
Dim Test1 As String
Dim Test2 As String
Dim Test3 As String
Dim Test4 As String
Test1 = Request("xmbr")
Test2 = Request("xSEL")
Test3 = Request("xpro")
Test4 = "SELECT [jedan], [dva], [tri], [cetiri], [pet] FROM [pet1] where " & (Test1) & " " & (Test2) & "'" & (Test3) & "'"
%>
<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:FinGateConnectionString %>" SelectCommand="<%= Test4%>">
The problem is that is giving me an error that I can’t figure out.
-------------------------------------------------
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.Data.SqlClient.SqlException: Line 1: Incorrect syntax near '<'.Source Error:
An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below.
-------------------------------------------------
Thanks.
Nbdg_28
View 2 Replies
View Related
Nov 19, 2006
Hi,
I have the following Sql Data Source that I wish to bring back on my page:
<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:FrogConnectionString %>"
SelectCommand="ProposalsCheckNewCustomerData" SelectCommandType="StoredProcedure">
<SelectParameters>
<asp:FormParameter FormField="MaritalStatusID" Name="MartialStatusID" Type="Int32" />
<asp:FormParameter FormField="LivingStatusID" Name="LivingStatusID" Type="Int32" />
<asp:FormParameter FormField="DealerID" Name="DealerID" Type="Int32" />
<asp:FormParameter FormField="VehicleTypeID" Name="VehicleTypeID" Type="Int32" />
<asp:FormParameter FormField="FinanceTypeID" Name="FinanceTypeID" Type="Int32" />
<asp:FormParameter FormField="MediaSourceID" Name="MediaSourceID" Type="Int32" />
<asp:FormParameter FormField="DealerContactID" Name="DealerContactID" Type="Int32" />
</SelectParameters>
</asp:SqlDataSource>
The recordset brings back 7 values that I wish to populate into 7 Labels.
1. Should the SqlDataSource be on the page (between the Head tags) or within the Page_Load command of the Page Behind ?- If its on the page behind do any of you have any sample code to show me how to get the recordset up and running ?
2. How would I bind the results to a label ?- Within my old asp pages it was simple (I'm not saying its not simple here, I am still getting my head around vb.net)- eg <%=rsX("MaritalStatus")%> I guess its something very similar
Thanks for any pointersFizzystutter
View 4 Replies
View Related
Feb 18, 2007
My code behind file builds a select statement and I would like to fill an SqlDataSource control with it. Can some show me an example of how I might do that? Maybe something like this?
Me.SqlDataSourceSearchResult.ConnectionString = "ConnectStr"
Me.SqlDataSourceSearchResult.SelectCommand = "SelectStatement"
gvSearchResult.DataSource = Me.SqlDataSourceSearchResult
gvSearchResult.DataBind()
View 5 Replies
View Related
May 30, 2007
Hi guys,
I have following query in stored procedure Select ContractId, Version, CreateDate, body, IsCurrentFrom CRM_Contracts where ContractId=@ContractId I am trying to access the value of IsCurrent field in Code behind using sqlDataSource to hide a radiobutton list in Formview control's when it is in Edit Mode. How can i do this.
View 1 Replies
View Related
Dec 30, 2007
Hi All
It´s my DropDownList data source from my page1.aspx:
<asp:DropDownList ID="ddlChampionships" runat="server" DataSourceID="sdsSearchChampionships" DataTextField="name" DataValueField="id_championship"> </asp:DropDownList><asp:SqlDataSource ID="sdsSearchChampionships" runat="server" ConnectionString="<%$ ConnectionStrings:LocalSqlServer %>"
ProviderName="System.Data.SqlClient" SelectCommand="SELECT * FROM [Tupi_Campeonatos]"></asp:SqlDataSource>
When I put a new championship name in a TextBox and click on the button "ADD", this button event click is called:
Protected Sub btAddChampionship_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btAddChampionship.Click
insertNewChampionshipIntoDataBase()
End Sub
The Page then reloads but my DropDownList is not updated. The way I found to do it is to put the DDList update code after the calling of the insertNewChampionshipIntoDataBase() method, but how???
thnx
View 2 Replies
View Related
Apr 21, 2006
I have a sqldatasource in the markup. This is in turn connected to a Gridview. I use Eval to retrieve the database information in the markup but I also need the Eval information in code behind. How can I do this?
I got this far but how (if it's possible this way) can I retrive it?
Protected Sub Test(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.GridViewRowEventArgs) Handles GridView1.RowDataBound
If (e.Row.RowType = DataControlRowType.DataRow) Then
this is where i want to get the "MyData" value from the database.
End If
........
<asp:GridView ID="GridView1" runat="server" AllowPaging="True" AutoGenerateColumns="False"
DataKeyNames="test" DataSourceID="SqlDataSource1" UseAccessibleHeader="False" GridLines="None" ShowHeader="False">
<Columns>
<asp:TemplateField>
<ItemTemplate>
<asp:HiddenField ID="Hiddenvalue" runat=server Value='<%# Eval("MyData") %>' />
</ItemTemplate>
</asp:TemplateField>
</Columns>
</asp:GridView>
using VB..
View 1 Replies
View Related
May 3, 2007
hi everyone
i have a SqlDataSourceand i want execute two T-Sql (two InsertCommand)but not successHow did i do this?
Thanks
1 Protected Sub SqlDataSource1_Inserted(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.SqlDataSourceStatusEventArgs) Handles SqlDataSource1.Inserted2 Dim SDSTemp As SqlDataSource = Nothing3 Try4 SDSTemp = New SqlDataSource(CnStr, "")5 SDSTemp.InsertCommandType = SqlDataSourceCommandType.Text6 SDSTemp.InsertCommand = "INSERT INTO Table1 (id,t1,t2) VALUES (@id,@t1,@t2)"7 Dim id As String = e.Command.Parameters("@id").Value.ToString8 SDSTemp.InsertParameters.Add("id", id)9 SDSTemp.InsertParameters.Add("t1", CType(FormView1.FindControl("TextBox1"), TextBox).Text)10 SDSTemp.InsertParameters.Add("t2", CType(FormView1.FindControl("TextBox2"), TextBox).Text)11 SDSTemp.Insert()12 13 SDSTemp.InsertCommand = "INSERT INTO Table2 (id) VALUES (@id)"14 15 SDSTemp.InsertParameters.Add("id", id)16 17 SDSTemp.Insert()18 Catch ex As Exception19 Message.Text = ex.Message.ToString20 End Try21 End Sub22
View 1 Replies
View Related
Mar 4, 2008
Hi,
I have a SQLDataSource, button, textbox, and label. I take the text from the textbox, count it's occurance in the database, then assign the number to the label. The code works but I would like to provide a button that will execute/invoke the SQLDataSource.
I have this in the click event for the button:
Me.label1.Text = SqlDataSource1.Select(DataSourceSelectArguments.Empty)
How do I execute a SQLDataSource from the code-behind for the button click event?
Thanks.
View 2 Replies
View Related
Sep 3, 2007
Hello... im having a problem with my query optimization....
I have a query that looks like this:
SELECT * FROM table1
WHERE location_id IN (SELECT location_id from location_table WHERE account_id = 998)
it produces my desired data but it takes 3 minutes to run the query... is there any way to make this faster?... thank you so much...
View 3 Replies
View Related
May 25, 2007
Hi All, Maybe because it's Friday afternoon and I can't think clearly anymore... A really (I guess) simple problem: DataView with SqlDataSource <asp:DataList ID="DataList1" runat="server" DataSourceID="SqlDataSource1"> <ItemTemplate> <asp:Label ID="id" runat="Server" Text='<%# Eval("ID")%>' /> </ItemTemplate> </asp:DataList> <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:MyConnectionString %>" SelectCommand="GetItem" SelectCommandType="StoredProcedure"> </asp:SqlDataSource> Now, what I have to do is to pass two parameters to the stored procedure: 1) ProviderUserKey2) Int ValueBut the question becomes "how"?Even if I define: <SelectParameters> <asp:Parameter Name="I_GUID" /> <asp:Parameter Name="I_TYPE" Type="Int32" DefaultValue="1" /> </SelectParameters>I need to set the parameters from code behind.... Thanks for any suggestions Adam
View 8 Replies
View Related
