SqlDataSource And Dynamic SelectCommand
Jan 16, 2006
Good morning.
I'm writing an application that allows users to generate Personal Leave requests and route them to their managers.
I'm using the integrated Windows Authentication, so I'm able to get the user's username from HttpContext.Current.User.Identity.Name.
How do I dynamically generate the SelectCommand? Right now, I do:
Dim strQuery As String = "SELECT * FROM [tblPLRequest] WHERE employee = '" & strUsername & "'"
to get the query I want, but when I set my SelectCommand to that, it thinks it's a stored procedure (and can't find it).
Am I going about this in the completely wrong way?
Thanks.
View 3 Replies
ADVERTISEMENT
Apr 27, 2007
Hello everyone,
I would like to know if there is a way to know if a sqldatasource.selectcommand returns tables or not? I'm trying to make a If statement so that if the selectcommand isn't returning anything it will disable a checkbox.
Thank you.
View 2 Replies
View Related
Nov 19, 2007
Hello, I'm new to ASP.NET (past 10 months) and am a little stuck. I will try to explain the best I can. I have a dropdown (ddNames) with a list of peoples names from my database and the value is their (staffID). When I select a name from the dropdown I want to get that Staff members details from my database. Everything works perfectly until I get to the
[[ WHERE staffID = "dropdown value??" ]] Can you help?
View 6 Replies
View Related
Dec 12, 2007
I am trying to set a select command in a VB script which is activated by a button and to display the data in a gridview
The script code is
SqlDataSource1.SelectCommand = "SELECT MemberID, Title, FirstName, LastName, EmailAddress, Password, DateTimeStamp, IPAddress, Description, Location, Position FROM Members, Careers WHERE " + Search.ToString() + " AND MemberID = CareerID"
and the grid view is<asp:GridView ID="GridView1" runat="server" AllowPaging="True" AllowSorting="True"AutoGenerateColumns="False" DataKeyNames="MemberID" ataSourceID="SqlDataSource1"BorderWidth="0" BorderColor="White"EmptyDataText="There are no data records to display." GridLines="None" ShowHeader="False" >
this works fine until the next page button is pressed then the message "No data records" comes up even though there are plenty of records still to come. It works fine if I set AllowPaging to be False.
Can anyone help?
View 4 Replies
View Related
Apr 12, 2008
Hi
Do you guy's know if there is an upper limit on the length of the string that can go into the selectcommand of the sqldatasource? I'm getting strange behavior from the sqldatasource. I'm dynamically generating select strings in the business layer and then programmatically using them in a sqldatasource up in the website. I sometimes get really large select statements (whole pages of joins) and was wondering if there was a character limit to the selectcommand property.
Nuwanda
View 4 Replies
View Related
May 25, 2006
Greetings all,
I am new to ASP, .NET, and VWD2005. My experience lies primarily with PHP.
I was impressed with how simple it was to create a datagrid view of a table in VWD, however, now I'm stuck and need some assistance.
I have an Access database, and have configured an SQL Data Source to it. (forgive me if my terminology is bad)
The code is:
<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:ConnectionString %>" ProviderName="<%$ ConnectionStrings:ConnectionString.ProviderName %>" SelectCommand="SELECT phones.FirstName, phones.LastName, phones.Phone, Departments.DepartmentName FROM (Departments INNER JOIN phones ON Departments.DEPT_ID = phones.DEPTID) WHERE (phones.FirstName BETWEEN 'A' AND 'AZZZ') ORDER BY phones.FirstName"></asp:SqlDataSource>
It works fine and returns all the names/phone numbers of people beginning with 'A' as anticipated.
My original intent was to create a sting variable "strQry" which contained the actualy Select statement above. The strQry would be programatically created to alter the WHERE statement accordingly for B,C,D,E - Z. My code would then be
<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:ConnectionString %>" ProviderName="<%$ ConnectionStrings:ConnectionString.ProviderName %>" SelectCommand=strQry></asp:SqlDataSource>
This did not work. What am I doing wrong?
I also tried programatically testing the condition of the desired letter (A-Z) and having it use a select case statement to call multiple occurences of the asp:SqlDataSource control. The problem I ran into there is that the ID "SqlDataSource1" is already in use on my subsequent occurences. (regardless, this would produce a huge bloated section of code having 26 instances of the control)
I need some help please. Any advice would be greatly appreciated.
View 5 Replies
View Related
Jun 27, 2006
Hallo,Why doesnt SelectCommand have problems with angle brackates if I place = insted of <> then it works fine??? Also do I have to convert a Session datetime dtdate like this to select from SQL2000?SelectCommand = "SELECT (SELECT PICTUREID FROM PICTURE WHERE TYPE = 1 AND HOTELID = HOTEL.HOTELID) AS PictureID, dbo.Hotel.HotelID, dbo.Hotel.Name AS HName, dbo.Hotel.Star, dbo.Loc.GLoc, dbo.Loc.NameCZ, dbo.Loc.NameCZ2, MIN(dbo.HSRSeason.Price) AS LowestPrice FROM dbo.Hotel INNER JOIN dbo.HSR ON dbo.Hotel.HotelID = dbo.HSR.HotelID INNER JOIN dbo.HSRSeason ON dbo.HSR.HSRID = dbo.HSRSeason.HSRID INNER JOIN dbo.Room ON dbo.HSR.RoomID = dbo.Room.RoomID INNER JOIN dbo.HSeason ON dbo.Hotel.HotelID = dbo.HSeason.HotelID INNER JOIN dbo.Loc ON dbo.Hotel.LocID = dbo.Loc.LocID WHERE (dbo.Room.Name <> 'Extra Bed') AND (dbo.HSeason.[From] <= CONVERT(DATETIME, '" + @dtdate1 + "', 104)) AND (dbo.HSeason.[To] >= CONVERT(DATETIME, '" + @dtdate1 + "', 104)) GROUP BY dbo.Hotel.HotelID, dbo.Hotel.Name, dbo.Hotel.Star, dbo.Loc.GLoc, dbo.Loc.NameCZ, dbo.Loc.NameCZ2, dbo.Hotel.Com ORDER BY Com DESC"
View 4 Replies
View Related
Nov 17, 2006
This is my SQLDataSource for my gridview
<asp:SQLDataSource ConnectionString="<%$ ConnectionStrings:Pubs %>" id="sqlProducts" Runat="Server" SelectCommand=' "Select * from adressen INNER JOIN Adressoort ON adressen.AD_ID = Adressoort.AD_ID where " & lblsql.Text ' ></asp:SQLDataSource>
the label is something like : adressoort.AD_soort = '3' or adressoort.AD_soort = '1'
I keep on getting the error sever tag not well formed
View 3 Replies
View Related
Nov 10, 2007
I'm try to achieve the following ;
<asp:SqlDataSource ID="CardDataSource" runat="server" ConnectionString = "my connection SelectCommand = "String.Format("Select * from cards where active not like '0' or active is NULL and (card like ' {0}%' or card like '{0}%')", Request.QueryString("db"))" />
I'm trying to pass a value into my query from the sqldatasource, but I'm having trouble properly using the string. This code used to be in a vb code, but theres a considerable performance difference when I have it load from my vb file and when I front load it here.
Anyone know what i would have to add or remove to make the above script work? Do I have my single/double quotes mixed up?
View 5 Replies
View Related
Mar 6, 2008
Hi,
I would like to set the selectcommand property of the sqldatasource at runtime.
how to do that?
can somebody help me out?
View 1 Replies
View Related
Feb 28, 2007
Hi folks,I'm having problems using the SqlDataSource to return certain values from a SQL database. I have two DropDownLists. When the first DDL's selected index is changed, I need to take the value (an int id) from the field, use the value to look up a string in a different colum in the table for that record id, which should then be used to automatically select a value from the second DDL. I think my problem is I'm not sure how to get the SelectCommand to actually return the value, let alone in string form!Here is my code:{ string selectedProject = ProjectDDL.SelectedValue; SqlDataSource temp = new SqlDataSource(); temp.ConnectionString = rootWebConfig.ConnectionStrings.ConnectionStrings["DBConnectionString"].ToString(); temp.SelectParameters.Add("id", selectedProject); temp.SelectCommand = "SELECT [username] FROM dbo.projects WHERE id = @id"; //temp.Select(); ? //AssigneeField.SelectedValue = string returned from Select Statement!} I'd appreciate it if somone could point me in the right direction? Thanks,Ally
View 2 Replies
View Related
Mar 12, 2008
Hi Everyone,
I set the session("MyTable") with the user's name as contents when the user logs in, then I copy a new table name with session("MyTable") , I set PK and this all works, but I can't seem to figure out how to use the session var as table name SQLDataSource Selectcommand? I try a bunch of different stuff, putting it into a key in the web.config. Seems like this should be a piece of cake.
Im getting ~ "Session variable is not a valid datasource item" etc. Below is one of the things I have tried.<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:Connectio String %>" SelectCommand="Select * From [" + Session("MyTable") + "] Where ID = @ID">
TIA
Cebo
View 2 Replies
View Related
May 7, 2007
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
View 4 Replies
View Related
Feb 24, 2008
Hi all,
I've been trying to rack my brains over this one; When I use this select statement in SqlDataAdapter, it runs fine; it takes about 4 to 5 seconds to run. In SqlDataSource, its very erratic; taking between 8 to 71 seconds to run. I've finally narrowed down the issue to something that resolves the symptoms to something in my query;
SELECT f.UnitID, f.FacilityName, SUM(CASE WHEN t .sData_Type = 'CLINICAL' THEN t .nWrite_Off_Amt ELSE 0 END) AS Sum_Clinical, SUM(CASE WHEN t .sData_Type = 'OTHERS' THEN t .nWrite_Off_Amt ELSE 0 END) AS Sum_Others, SUM(t.nWrite_Off_Amt) AS Sum_Total, SUM(t.nCashAmount) AS Sum_Cash, t.iYearFROM t LEFT OUTER JOIN d ON t.sUnitID = d.sUnitID AND t.sPat_Num = d.sPat_Num AND t.iMonth = d.iMonth AND t.iYear = d.iYear LEFT OUTER JOIN tblFac AS f ON t.sUnitID = f.UnitID_1WHERE (t.iYear = @year) AND (t.iMonth = @month) AND (@UnitID IN ('08948', RIGHT('0' + f.Division, 5)))GROUP BY f.UnitID, t.iYear, f.FacilityName, f.Division
If I change my WHERE clause to the following
WHERE (@year = t.iYear) AND (@month = t.iMonth) AND (@UnitID IN ('08948', RIGHT('0' + f.Division, 5)))
performance is MUCH better. How could such a little change make such a big difference in speed?
Thanks.
-e
View 3 Replies
View Related
Jun 6, 2008
I am setting the SelectCommand of the SqlDataSource in my page_load. This is the WHERE part of my SelectCommand:WHERE ProductName LIKE '%' ? '%' OR ProductRef LIKE '%' ? '%'The above uses two select parameters. But the two select parameters will have the same value. So is it possible to re-write my select command so it just uses one parameter? I have tried the following, but this doesn't work. WHERE (ProductName OR ProductRef) LIKE '%' ? '%'Is there similar syntax I could use that works?
View 2 Replies
View Related
Dec 12, 2006
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?
View 1 Replies
View Related
Sep 18, 2007
Although writing a parameterized SQL statement has been simplified using the asp:parameter options, it still may benefit to use the old fashioned method of writing a sql statement using an input string. I have noticed this for wanting to make a parameter to select which table I want to pull from. Here is some code I wrote to pull information from a database based on input from a search box and write it to a gridview.
Partial Class Private_SearchResultsInherits System.Web.UI.Page
Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
Dim Table As String
Dim SearchString As StringSearchString = Request.QueryString("SearchString")
Table = Request.QueryString("Table")
If Len(Trim(SearchString)) > 0 ThenSelect Case Table
Case "Plant"
SqlDataSource1.SelectCommand = "SELECT PlantName as 'Plant',PlantAddr as 'Address',PlantCity as 'City',PlantState as 'State',PlantCountry as 'Country',PlantZip as 'ZIP' FROM Plant WHERE PlantName LIKE '%" & SearchString & "%' OR PlantCity LIKE '%" & SearchString & "%' OR PlantState LIKE '%" & SearchString & "%' OR PlantCountry LIKE '%" & SearchString & "%' OR PlantZip LIKE '%" & SearchString & "%' ORDER BY PlantName"
Case "Contacts"
SqlDataSource1.SelectCommand = "SELECT ContactPosition as'Position',ContactTitle as 'Title',ContactLast as 'Last Name',ContactFirst as 'First Name',ContactPhone as 'Phone No' FROM Contacts WHERE ContactLast LIKE '%" & SearchString & "%' OR ContactFirst LIKE '%" & SearchString & "%' OR ContactPosition LIKE '%" & SearchString & "%' OR ContactPhone LIKE '%" & SearchString & "%' OR ContactTitle LIKE '%" & SearchString & "%' ORDER BY ContactLast"
Case "Events"
SqlDataSource1.SelectCommand = "SELECT EventName as 'Event',CONVERT(varchar,EventStartDate,101) as'Start Date',CONVERT(varchar,EventEndDate,101) as 'End Date',EventNotes as 'Notes',EventNoAttendees as 'Attendees',EventType as 'Event Type' FROM Events WHERE EventName LIKE '%" & SearchString & "%' OR EventStartDate LIKE '%" & SearchString & "%' OR EventEndDate LIKE '%" & SearchString & "%' OR EventNotes LIKE '%" & SearchString & "%' OR EventNoAttendees LIKE '%" & SearchString & "%' OR EventType LIKE '%" & SearchString & "%' ORDER BY EventName"
Case ""
Label1.Text = "Nothing Selected in Drop Down Box"
End Select
Else
Label1.Text = "No Search Parameters Entered"
End If
GridView1.DataBind()End Sub
End Class
Here is also my code to the front end of the page...
<%@ Page Language="VB" MasterPageFile="~/MasterPage.master" AutoEventWireup="false" CodeFile="SearchResults.aspx.vb" Inherits="Private_SearchResults" title="Search Results" %>
<asp:Content ID="Content1" ContentPlaceHolderID="ContentPlaceHolder1" Runat="Server">
<br />
<strong><span style="font-family: Tahoma">Search Results<br />
<br /></span></strong>
</asp:Content>
<asp:Content ID="Content2" ContentPlaceHolderID="ContentPlaceHolder2" Runat="Server">
<asp:Label ID="Label1" runat="server" Font-Names="Tahoma" Font-Size="10pt"></asp:Label><br />
<asp:GridView ID="GridView1" runat="server" CellPadding="4" ForeColor="#333333" GridLines="None" DataSourceID="SqlDataSource1" Width="1020px">
<FooterStyle BackColor="#5D7B9D" Font-Bold="True" ForeColor="White" />
<RowStyle BackColor="#F7F6F3" ForeColor="#333333" Font-Names="tahoma" Font-Size="Small" />
<EditRowStyle BackColor="#999999" />
<SelectedRowStyle BackColor="#E2DED6" Font-Bold="True" ForeColor="#333333" />
<PagerStyle BackColor="#284775" ForeColor="White" HorizontalAlign="Center" />
<HeaderStyle BackColor="Indigo" Font-Bold="True" ForeColor="White" Font-Names="tahoma" Font-Size="Small" />
<AlternatingRowStyle BackColor="White" ForeColor="#284775" /></asp:GridView>
<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:CRMConnectionString %>"></asp:SqlDataSource>
</asp:Content>
View 2 Replies
View Related
Feb 1, 2007
Hey,
I changed the database name in the initial cataloge in the web.config conncetion string so that it now connects to other databas that contains same tables as the old one,but now i am getting that error at the update stmt !
thank u in advance
Hiba
View 1 Replies
View Related
Nov 14, 2006
Hello,
Has anyone used/fiqured out how to set one of the properties below to a variable with a dynamic database.
<asp:SqlDataSource ID="Sql_Imports" runat="server" selectcommand='EXEC <%# Session("CompanyDB") %>..IMPORT_S' ConnectionString=""></asp:SqlDataSource>
You would have thought that Microsoft would have allowed catalog/database on this command.
Thanks.
View 2 Replies
View Related
May 14, 2007
Ok, here's my situation.
I have a dynamic page that accepts a "type" query string. This type query string is the name of a table I want to display on the page in the GridView. Creating a different SqlDataSource/Strongly typed class for every type isn't possible because I need to make this flexible for future updates. So therefor I basically need to be able to Sort, Page, Edit and Delete with the Grid View without knowing the table name at compile-time.
Any help is appreciated,
Thanks!
View 3 Replies
View Related
Sep 20, 2007
OK so I need to use a dynamic SQL statement in a SqlDataSource object because I need to pass in the column name. I'm having trouble accounting for the apostophes I have to interpret literals within the statement. This is connecting to an Oracle database.
This is what I originally tried...
<asp:SqlDataSource ID="SqlDataSourceMine" runat="server" ConnectionString="<%$ ConnectionStrings:My_Connection_String %>" ProviderName="<%$ ConnectionStrings:My_Connection_String.ProviderName %>" SelectCommand="SELECT m.YIE, :selecteditem, m.ENDTIME FROM MyMAP.MAP_M_SUM m WHERE (m.GROUPID LIKE 'YC%' AND m.GROUPID NOT LIKE 'YCX%' AND m.ENDTIME >= SYSDATE-14)"> <SelectParameters> <asp:ControlParameter Name="selecteditem" ControlID="itemlabel" PropertyName="Text" Type="String" /> </SelectParameters></asp:SqlDataSource>
And the second column returned as :selecteditem for the column name and the value of itemlabel.text (what I wanted to be the column that was queried) as the value in each of the rows of that column. So I tried dynamic SQL to put the value of itemlabel.txt as the column to be queried, but I'm not sure how to get the query to read the literals. How can I accomplish this?
<asp:SqlDataSource ID="SqlDataSourceMine" runat="server" ConnectionString="<%$ ConnectionStrings:My_Connection_String %>" ProviderName="<%$ ConnectionStrings:MY_Connection_String.ProviderName %>" SelectCommand="EXEC('SELECT m.YIE, '+selectedbin+', m.ENDTIME FROM MMAP.MAP_M_SUM m WHERE (m.GROUPID LIKE '+paramlike+' AND m.GROUPID NOT LIKE '+paramnotlike+' AND m.ENDTIME >= SYSDATE-14)')"> <SelectParameters> <asp:ControlParameter Name="selectedbin" ControlID="binlabel" PropertyName="Text" Type="String" /> <asp:ControlParameter Name="paramlike" ControlID="Label1" PropertyName="Text" Type="String" /> <asp:ControlParameter Name="paramnotlike" ControlID="Label2" PropertyName="Text" Type="String" /> </SelectParameters></asp:SqlDataSource>
This errors to "illegal variable name"
Can someone help me out please? Thanks a lot.
-steve
View 3 Replies
View Related
Mar 20, 2008
I have a datasource on my ASP.NET 2.0 control I want to make dynamic. I have 6 different connection strings in my web.config file and want to change the connection with the selection in dropdownbox.
Here is my VB code:Protected Sub GetDatabase(ByVal intDb As Integer)
Select Case intDb
Case 1 ' Americas
srcCustomers.ConnectionString = "RWSqlServer"
Exit Sub
Case 2 ' Asia
srcCustomers.ConnectionString = "SGSqlServer"
Exit Sub
Case 3 ' Australia
srcCustomers.ConnectionString = "SYSqlServer"
Exit Sub
Case 4 ' Canada
srcCustomers.ConnectionString = "MSSqlServer"
Exit Sub
Case 5 ' EMEA
srcCustomers.ConnectionString = "NCSqlServer"
Exit Sub
Case 6 ' NE
srcCustomers.ConnectionString = "RUSqlServer"
Exit Sub
End Select
End SubProtected Sub ddlBusinessUnit_SelectedIndexChanged(ByVal sender As Object, ByVal e As System.EventArgs)
If (ddlBusinessUnit.SelectedIndex = 0) Then
panUser.Visible = False
panDistributor.Visible = False
panEndUser.Visible = False
GetDatabase(ddlBusinessUnit.SelectedIndex)
lblDb.Text = ddlBusinessUnit.SelectedIndex.ToString
Else
panUser.Visible = True
End If
End Sub
Here is my ASP.NET code:<asp:SqlDataSource ID="srcCustomers" runat="server" SelectCommand="SELECT cm_addr + ' - ' + cm_sort AS [name], cm_addr, cm_sort FROM cm_mstr WHERE cm_type <> 'I'">
</asp:SqlDataSource><asp:DropDownList ID="ddlBusinessUnit"
runat="server"AutoPostBack="True"
OnSelectedIndexChanged="ddlBusinessUnit_SelectedIndexChanged">
<asp:ListItem Value="" Text="Select One"></asp:ListItem>
<asp:ListItem Value="1" Text="Americas"></asp:ListItem>
<asp:ListItem Value="2" Text="Asia"></asp:ListItem>
<asp:ListItem Value="3" Text="Australia"></asp:ListItem>
<asp:ListItem Value="4" Text="Canada"></asp:ListItem>
<asp:ListItem Value="5" Text="EMEA"></asp:ListItem><asp:ListItem Value="6" Text="NE"></asp:ListItem>
</asp:DropDownList>
What am I missing? Do I need to reference the whole connection string or is there a way I can reference the "NAME" id in the web.config file for the connection string?
View 2 Replies
View Related
Jul 20, 2007
I have a GridView (that uses SqlDataSource1) and a Dropdownlist. Depending upon the value selected on the DropDownList I need to select different stored procedures for the gridview. The problem is that I can do it without taking SqlDataSource1 by using DataSet or DataTable. But, I need to Use SQLDataSource1 for easy way of Header SORTING. So, is there any way to change the SQLDatasource1.SELECT Command dynamically. So that, I can use different queries for the Single DataGrid. I have attached the sample code of the SqlDataSource1 I'm using. I need to change the Command i.e. SelectCommand="usp_reports_shortages" to "usp_reports_shortagesbyID" and "usp_reports_shortagesbyDate" depending on the value selected in the dropdownlist. So, is there any way to do this????<asp:SqlDataSource ID="SqlDataSource1"
runat="server"
ConnectionString="<%$
ConnectionStrings:TESTDrivercommunication %>"
SelectCommand="usp_reports_shortages" SelectCommandType="StoredProcedure">
<SelectParameters>
<asp:ControlParameter ControlID="lblDriver" Name="date1" PropertyName="Text" Type="DateTime" />
<asp:ControlParameter ControlID="lblTODate" Name="date2" PropertyName="Text" Type="DateTime" />
<asp:ControlParameter ControlID="DDlDriver" Name="driver" PropertyName="SelectedValue"
Type="Int32" />
<asp:SessionParameter Name="week" SessionField="s_week" Type="DateTime" />
</SelectParameters>
</asp:SqlDataSource>
View 1 Replies
View Related
Apr 25, 2008
I am trying to implement an "advanced search" feature on my ASP.NET 2.0 web form. I have a GridView control and a SqlDataSource. The SqlDataSource control successfully retrieves data when the SelectCommand attribute is set in the aspx page. I need to make it so when a user clicks on a button, it can take a value from a text box and use it in the WHERE clause. I have tried setting the SelectCommand programmatically and then DataBinding but it never accepts the new SelectCommand. What can I do to fix this?
View 5 Replies
View Related
Mar 29, 2006
Hey, I have a search form with a selectbox. This selectbox contains the columnnames.I want when I put a text in a textbox and select a value in the selectbox and click submit that it search database.The Columnnames I put in a session.
If you see I have put in the querystring as columnname @sescolumn which I have initialised as asp:sessionparameter.But it gives no results. When I put @sescolumn between [] like normal columnnames are it doesn't work also.
Can someon put my on the right path?
<asp:SqlDataSource ID="Database_ecars" runat="server" ConnectionString="<%$ ConnectionStrings:connectionstring %>"
SelectCommand="SELECT [AutoID], [Merk], [Kleur], [Type], [Autotype], [prijs], [Zitplaatsen], [Afbeelding1], [Afbeelding2], [Afbeelding3], [Afbeelding4] FROM [Auto] where @sescolumn like @seskeyword and [AutoID] not in (select [AutoID] from [verhuring] where [StartVerhuur] >= @sesdatefrom and [Eindeverhuur] <= @sesdatetill)" >
<SelectParameters>
<asp:SessionParameter Name="sesdatefrom" SessionField="datefrom" Type="Decimal" />
<asp:SessionParameter Name="sesdatetill" SessionField="datetill" Type="Decimal" />
<asp:SessionParameter Name="seskeyword" SessionField="keyword" Type="string" />
<asp:SessionParameter Name="sescolumn" SessionField="columnname" Type="string" />
</SelectParameters>
</asp:SqlDataSource>
View 3 Replies
View Related
May 12, 2007
Hi all,
I want to do something like this, but I cannot get it working:
<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:AFS_WAWI_DBConnectionString1 %>"<%If Session("my") = "123" thenSelectCommand="SELECT [nr1] FROM [Artikel]"ElseSelectCommand="SELECT [nr2] FROM [Artikel]"End if %></asp:SqlDataSource>
Is that possible and how can I manage that?
Thanks for your answers, Chris.
View 4 Replies
View Related
Dec 7, 2006
for some reason the following code fails. I want to present all of the information of the CD (from table B)that inlcudes the song name the user types in the text box(table A).
Meaning the user types the name, it's located in table A, for each CD id in table A I want to present all the info from table B. Do I have to use DataRelation?:
private void Button1_Click(object sender, System.EventArgs e)
{
System.Data.SqlClient.SqlDataAdapter da=new SqlDataAdapter();
string str=TextBox1.Text;
System.Data.SqlClient.SqlParameter sparam=new SqlParameter("@song",SqlDbType.NVarChar);
sparam.Direction=ParameterDirection.Input;
sparam.Value=str;
da.SelectCommand=new SqlCommand();
da.SelectCommand.Connection=sqlConnection1;
da.SelectCommand.CommandText="CdInfoSP";
da.SelectCommand.CommandType=CommandType.StoredProcedure;
da.SelectCommand.Parameters.Add(sparam);
da.Fill(dataSet11,"CdInfoSP");
DataGrid1.DataSource=dataSet11;
DataGrid1.DataBind();
DataGrid1.Visible=true;
}
My Stored procedure is:
CREATE PROCEDURE dbo.CdInfoSP (@song nvarchar)AS select cd_id,cd_name,year from song_cd_artist,cd_info where song_name like '%@song%' and cdofsong_id=cd_idGO
I hope someone can help me!
Thanks in advance!
View 1 Replies
View Related
Jun 19, 2007
If I hard code the select statement everything works fine.
<asp:SqlDataSource ID="SqlDataSourceZip" runat="server" ConnectionString="<%$ ConnectionStrings:MyConnectionString %>"
SelectCommand="SELECT * FROM [Station] WHERE ([ZipCode] = '80523' OR [ZipCode] = '80521' OR [ZipCode] = '80553' OR [ZipCode] = '80522' OR [ZipCode] = '80526' OR [ZipCode] = '80527' OR [ZipCode] = '80525' OR [ZipCode] = '80524' OR [ZipCode] = '80547' OR [ZipCode] = '80535' OR [ZipCode] = '80538' OR [ZipCode] = '80551' OR [ZipCode] = '80549' OR [ZipCode] = '80550' OR [ZipCode] = '80546' OR [ZipCode] = '80539' OR [ZipCode] = '80512' OR [ZipCode] = '80537' OR [ZipCode] = '80541' OR [ZipCode] = '80650' OR [ZipCode] = '80515' OR [ZipCode] = '80513' OR [ZipCode] = '80610' OR [ZipCode] = '80534' OR [ZipCode] = '80536' OR [ZipCode] = '80634' OR [ZipCode] = '80543' OR [ZipCode] = '80532' OR [ZipCode] = '80638' OR [ZipCode] = '80615' OR [ZipCode] = '80646' OR [ZipCode] = '80648' OR [ZipCode] = '80612' OR [ZipCode] = '80631' OR [ZipCode] = '80528')"> </asp:SqlDataSource>
Want to use a label control to return data, but can't find anything that works.
<asp:Label ID="zipLabel" runat="server"></asp:Label>
[ZipCode] = '80523' OR [ZipCode] = '80521' OR [ZipCode] = '80553' OR [ZipCode] = '80522' OR [ZipCode] = '80526' OR [ZipCode] = '80527' OR [ZipCode] = '80525' OR [ZipCode] = '80524' OR [ZipCode] = '80547' OR [ZipCode] = '80535' OR [ZipCode] = '80538' OR [ZipCode] = '80551' OR [ZipCode] = '80549' OR [ZipCode] = '80550' OR [ZipCode] = '80546' OR [ZipCode] = '80539' OR [ZipCode] = '80512' OR [ZipCode] = '80537' OR [ZipCode] = '80541' OR [ZipCode] = '80650' OR [ZipCode] = '80515' OR [ZipCode] = '80513' OR [ZipCode] = '80610' OR [ZipCode] = '80534' OR [ZipCode] = '80536' OR [ZipCode] = '80634' OR [ZipCode] = '80543' OR [ZipCode] = '80532' OR [ZipCode] = '80638' OR [ZipCode] = '80615' OR [ZipCode] = '80646' OR [ZipCode] = '80648' OR [ZipCode] = '80612' OR [ZipCode] = '80631' OR [ZipCode] = '80528'
something like this, but it don't work.
SelectCommand="SELECT * FROM [Station] WHERE (<%=zipLabel.Text%>)"> </asp:SqlDataSource>
View 3 Replies
View Related
Jan 15, 2008
Hi, I try to do a simple search:
<SelectCommand="SELECT color FROM mytable where color LIKE @PARAMETER1 ">
<SelectParameters>
<asp:Parameter Name="PARAMETER1 " DefaultValue="%" Type="string"/>
</SelectParameters>
have a code with CASE
Dim pColor As String = "%"
Case "All" I put to parameter "%"Case "Equals to" I put to parameter Me.TextBoxColor.Text
Case "Begins with" I put to parameter Me.TextBoxColor.Text + "%"
Case "Contains" I put to parameter "%" + Me.TextBoxColor.Text + "%"
Me.SqlDataSource1.SelectParameters("PARAMETER1 ").DefaultValue = pColor
Everything work find except my "Equal to case" and I try SELECT color FROM mytable where color LIKE blue and is don't work (return nothing)??? and I don't want %color% because is return me blue sky, not just blue.
Thank for help me
View 5 Replies
View Related
May 14, 2008
Hi,
I use the Count function to get the total from my SelectCommand. However, I need to get my selectcommands dynamically, so is there a way of writing like this somehow:
"SELECT COUNT(1) FROM (MypagesSqlDataSource.SelectCommand)
Thanks in advance,
Pettrer
View 4 Replies
View Related
Feb 3, 2006
How do you programmatically change the SQL select statement for the SelectCommand in a SqlDataSource?For instance, if I had two buttons on a page, and for one button, I'd like it to change the select command to be "SELECT * FROM authors WHERE au_id > 100". Then for the second button, I'd like it to show "SELECT * FROM authors WHERE au_lname like S%".Anybody know the code to do this? I was trying to type the code, but the intellisense didn't give me any options to pick from after I typed SqlDataSouce1.SelectCommand.
View 3 Replies
View Related
Sep 6, 2007
I'm trying to create a simple search against a SQL Database. I setup a TextBox and a GridView, I simply need the contents of the textbox to be used on databind() in my SQL select statement. Seems like it should be a simple thing to do, any guidance is appreciated! Here is my code:
<%@ Page Language="VB" MasterPageFile="~/MasterPage.master" Title="IP Tracker - Search" EnableSessionState="True" %>
<script runat=server>Protected Sub Button1_Click(ByVal sender As Object, ByVal e As System.EventArgs)
Search_GridView.DataBind()
End Sub
</script>
<asp:Content ID="Content1" ContentPlaceHolderID="MasterContent" Runat="Server">
<br />
<strong>Search for IP</strong><br />
<br />
<input id="SearchBox" type="text" /><br />
<asp:Button ID="Button1" runat="server" Text="Please Work..." onclick="Button1_Click"/><br />
<br />
<asp:GridView ID="Search_GridView" runat="server" DataSourceID="SqlDataSource1" EmptyDataText="No Records Match">
</asp:GridView>
<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:IP_TrackerConnectionString %>"
SelectCommand="SELECT * FROM [AddressTable] WHERE ([IPAddress] LIKE '%' + SearchBox.text + '%')">
<SelectParameters>
<asp:FormParameter FormField="SearchBox" Name="IPAddress" Type="String" />
</SelectParameters></asp:SqlDataSource>
</asp:Content>
View 7 Replies
View Related
Oct 9, 2007
why i type the select command like below the disctinct doesnt work??? the query stil show all the Category i hav so how do i fix it??
<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:ConnectionString %>"
SelectCommand="SELECT DISTINCT Category, ID FROM Notes WHERE (UserName LIKE '%' + @UserName + '%') ORDER BY ID DESC">
<SelectParameters>
<asp:SessionParameter Name="UserName" SessionField="UserName" Type="String" />
</SelectParameters>
</asp:SqlDataSource>
View 3 Replies
View Related
