I am using <asp:SqlDataSource ID and for the Select Command, the following, where the WHERE clause ... for an exact match (=) works correctly:
SelectCommand="SELECT [PatientID], [MedRecord] , [Accession], [FirstName], [LastName], [Address1] FROM [ClinicalPatient] WHERE (LastName = @LastName) ORDER BY [LastName]DESC">
I would like to do a "LIKE" search where the LastName Parameter is matched using "LIKE". In this situation how would the syntax be written.... I tried:
I have a SqlDataSource object that is bound to a GridView control. I have configured the SqlDataSource with a default select command. Under certain values of query strings on the URL for this page (Default.aspx), I want to change the select command. So I put the statements in the Page_Load method for Default.aspx to define SqlDataSource1.SelectCommand. The changed SelectCommand works fine for the first page of GridView data and shows 5 GridView pages, but if I switch to one of the other pages, it seems to revert to the default SelectCommand (which generates 19 GridView pages). I assume I should put my code to change the SelectCommand somewhere else. Can someone help me with where to put it? Thanks!
My compiler says that the line in bold below is illegal. The error msg I'm getting is: No overload for method 'select' takes '0' arguments. How can I correct this error and execute a SELECT? protected void Button1_Click(object sender, EventArgs e) { SqlDataSource2.Select (); } protected void SqlDataSource2_Selected(object sender, SqlDataSourceStatusEventArgs e) {string strReadyFirstName = e.Command.Parameters["@FirstName"].Value.ToString();string strReadyLastName = e.Command.Parameters["@LastName"].Value.ToString(); } <asp:SqlDataSource ID="SqlDataSource2" runat="server" ConnectionString="<%$ ConnectionStrings:ConnectionString %>" SelectCommand="SELECT [User_ID], [User_Name], [FirstName], [LastName], [Company_Name], [Department_Name] FROM [CompanyDepartment] WHERE ([User_Name] = @User_Name)" OnSelected="SqlDataSource2_Selected"> <selectparameters> <asp:sessionparameter DefaultValue="TheirUserName" Name="User_Name" SessionField="TheirUserName" Type="String" /> </selectparameters> </asp:SqlDataSource>
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 %>"
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?
Hi, Hope you guys won't mind this rather newbie question. I'm writing a simple blog page for my website and have created a SqlDataSource which queries the database for a list of blog post titles (from the web.Blog table) and the number of comments (from the web.BlogComments table). The SqlDataSource control is: <asp:SqlDataSource ID="sourceBlogArticles" ProviderName="System.Data.SqlClient" connectionString="<%$ ConnectionStrings:myDatabase %>" runat="server" SelectCommand="SELECT gb.blogID, gb.title, gb.description, gb.tags, gb.dateAdded, COUNT(gbc.blogID) AS noOfComments FROM web.Blog gb LEFT OUTER JOIN web.BlogComments gbc ON gb.blogID = gbc.blogID GROUP BY gb.blogID, gb.title, gb.description, gb.tags, gb.dateAdded ORDER BY gb.dateAdded"></asp:SqlDataSource> This works perfectly well if each blog entry in the web.Blog table has associated comments in the web.BlogComments table. However, if there are no comments yet defined in the web.BlogComments table for that blogID then no row is returned in ASP.Net (as checked with a GridView control or similar linked to the data source to view what I get) HOWEVER, I think the SELECT command IS correct: if I use the select command as a query in SQL Server Managment Studio Express, I do get the rows returned, with 0 for the number of comments which is what I would expect for that query: blogID, title, description, tags, dateAdded, noOfComments 1, title 1, description for title 1, tag1, 2007-09-27 06:49:03.810, 32, title 2, description for title 2, tag2, 2007-09-27 06:49:37.513, 03, title 3, description for title3, tag3, 2007-10-02 18:21:30.467, 0 Can anyone help? The result from the SSMSE query is what I want, yet when I use the very same SELECT statement in my SqlDataSource I don't get any rows returned if the BlogComment count is zero (in the above example I get only the first row). Many thanks for any suggestions!
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.
i am using visual web developer 2005 and SQL 2005 with VB as the code behindi am using INSERT command like this Dim test As New SqlDataSource() test.ConnectionString = ConfigurationManager.ConnectionStrings("DatabaseConnectionString1").ToString() test.InsertCommandType = SqlDataSourceCommandType.Text test.InsertCommand = "INSERT INTO try (roll,name, age, email) VALUES (@roll,@name, @age, @email) " test.InsertParameters.Add("roll", TextBox1.Text) test.InsertParameters.Add("name", TextBox2.Text) test.InsertParameters.Add("age", TextBox3.Text) test.InsertParameters.Add("email", TextBox4.Text) test.Insert() i am using UPDATE command like this Dim test As New SqlDataSource() test.ConnectionString = ConfigurationManager.ConnectionStrings("DatabaseConnectionString").ToString() test.UpdateCommandType = SqlDataSourceCommandType.Text test.UpdateCommand = "UPDATE try SET name = '" + myname + "' , age = '" + myage + "' , email = '" + myemail + "' WHERE roll 123 " test.Update()but i have to use the SELECT command like this which is completely different from INSERT and UPDATE commands Dim tblData As New Data.DataTable() Dim conn As New Data.SqlClient.SqlConnection("Data Source=.SQLEXPRESS;AttachDbFilename=|DataDirectory|Database.mdf;Integrated Security=True;User Instance=True") Dim Command As New Data.SqlClient.SqlCommand("SELECT * FROM try WHERE age = '100' ", conn) Dim da As New Data.SqlClient.SqlDataAdapter(Command) da.Fill(tblData) conn.Close() TextBox4.Text = tblData.Rows(1).Item("name").ToString() TextBox5.Text = tblData.Rows(1).Item("age").ToString() TextBox6.Text = tblData.Rows(1).Item("email").ToString() for INSERT and UPDATE commands defining the command,commandtype and connectionstring is samebut for the SELECT command it is completely different. why ?can i define the command,commandtype and connectionstring for SELECT command similar to INSERT and UPDATE ?if its possible how to do ?please help me
Hello all, I am having problem to insert the record into database from sqldatasource control. my code is listed below, and i can't find anything why it cause the exception... <asp:SqlDataSource ID="SqlDataSource1" runat="server" DataSourceMode="DataSet" ConnectionString="<%$ ConnectionStrings:WebsiteDataConnection %>" SelectCommand="SELECT * FROM [ServiceAgents]" InsertCommand="INSERT INTO ServiceAgents VALUES(@Ser_STATE, @Ser_CITY, @Ser_AGENT, @Ser_PHONE, @Ser_EQUIPMENT)"> <InsertParameters> <asp:FormParameter Name="Ser_STATE" FormField="TextBox_state"/> <asp:FormParameter Name="Ser_CITY" FormField="TextBox_city"/> <asp:FormParameter Name="Ser_AGENT" FormField="TextBox_agent"/> <asp:FormParameter Name="Ser_PHONE" FormField="TextBox_phone"/> <asp:FormParameter Name="Ser_EQUIPMENT" FormField="TextBox_equip" /> </InsertParameters> </asp:SqlDataSource> The table has got the fields that needed for insert command, for example: <table> <tr> <td>State:</td> <td> <asp:TextBox ID="TextBox_state" runat="server"></asp:TextBox> <asp:RequiredFieldValidator id="RequiredFieldValidator1" runat="server" ControlToValidate="TextBox_state" Display="Static" ErrorMessage="Please enter a state." /> </td> </tr> <tr> <td>City:</td> <td> <asp:TextBox ID="TextBox_city" runat="server"></asp:TextBox> <asp:RequiredFieldValidator id="RequiredFieldValidator2" runat="server" ControlToValidate="TextBox_city" Display="Static" ErrorMessage="Please enter a city." /> </td> </tr> <tr> <td>Agent:</td> <td> <asp:TextBox ID="TextBox_agent" runat="server"></asp:TextBox> <asp:RequiredFieldValidator id="RequiredFieldValidator3" runat="server" ControlToValidate="TextBox_agent" Display="Static" ErrorMessage="Please enter a agent." /> </td> </tr> <tr> <td>Phone:</td> <td> <asp:TextBox ID="TextBox_phone" runat="server"></asp:TextBox> <asp:RequiredFieldValidator id="RequiredFieldValidator4" runat="server" ControlToValidate="TextBox_phone" Display="Static" ErrorMessage="Please enter a phone No." /> </td> </tr> <tr> <td>Equipment:</td> <td> <asp:TextBox ID="TextBox_equip" runat="server"></asp:TextBox> <asp:RequiredFieldValidator id="RequiredFieldValidator5" runat="server" ControlToValidate="TextBox_equip" Display="Static" ErrorMessage="Please enter a equipment." /> </td> </tr> <tr> <td></td> <td> <asp:Button ID="Button2" runat="server" Text="Add" OnClick="addEntry" BackColor="#FFFBFF" BorderColor="#CCCCCC" BorderStyle="Solid" BorderWidth="1px" Font-Names="Verdana" Font-Size="1.0em" ForeColor="#284775"/> </td> </tr> </table> And in the code i just called: SqlDataSource1.Insert(); Then the page gives me the exception like: Cannot insert the value NULL into column 'STATE', table 'WebsiteData.dbo.ServiceAgents'; column does not allow nulls. INSERT fails. The statement has been terminated. But i actually input all the required text in the textbox.... Any idea guys? Thanks
hi i have an sqldatasource which has an insert command - a stored procedue is used.I have a text box with a button next to it . it is not in a datagrid.on the onclick event I would like to pass the value of the text box to the sqldatasource insert parameter ( it only expects this one parameter , and use the sqldatasource to do the insert basically doing a manual insert using the sqldatasource.does anyone know if this is possible thanks
i need to dyanamically generate my SQL commands so to do that i am generating sqldatasource commands programmatically rather declaratively. SELECT commands seems to work fine but DELETE isnt doing anything, here is my code:
SqlDataSource sdsConsultant = new SqlDataSource(); protected void Page_Load(object sender, EventArgs e) { if (!IsPostBack) { loadDataSet(); //it just loads dataset used by formview initializeSDS(); }
//SELECT sdsConsultant.SelectCommand = "SELECT * FROM Consultants WHERE (id=@id)"; QueryStringParameter id = new QueryStringParameter("id", "id"); sdsConsultant.SelectParameters.Add(id);
//DELETE sdsConsultant.DeleteCommand = "DELETE * FROM Consultants WHERE (id=@id2)"; QueryStringParameter id2 = new QueryStringParameter("id2", "id"); sdsConsultant.DeleteParameters.Add(id2);
hello everybody, i have a question to ask, suppose i have a sqldatasource, can i use it in a method??this is my case, i need to make a new method to count the rows in a datagrid, so i will have to read the sqldatasource. the problem is, how to retrieve it?? usually i use the built in sqldatasource_selected to count the rows.... is there any other way??
I have an SQLDataSource. The SQL is SELECT UserName, Category, ItemDescription, Size, Price, Reduce, Donate, Sold, ItemNumber, SoldDate, SoldPrice, PrintedFROM Tags WHERE (Printed = @Printed1 OR Printed = @Printed2 OR Printed = @Printed3) ORDER BY ItemNumber DESCThe bit field "printed" can be NULL, True or False.In the Selecting event of the SQLDataSource I have the following to show ALL records. But it does not work. If I remove these parameters it show ALL records. e.Command.Parameters("@Printed1").Value = Nothing 'ASP.NET 2.0 using Visual basice.Command.Parameters("@Printed2").Value = Truee.Command.Parameters("@Printed3").Value = False What am I doing wrong??? Thanks Craig
I apologise if i have not posted this in the correct Topic before i start. But was uncertain where to post this query. This is my first project in ASP.NET, MS Visual Web Developer 2005 Express and SQL Server 2005 Express. I have relatively little experience, so please bare with me. I have managed to create a form that inserts data into a table and then inserts the Automatically Created Primary Key(as a foreign key) in another table. I have done this by inserting what is highlighted in red in the code of my InsertCommand below (Please scroll across to the end of the code):-InsertCommand="INSERT INTO [PrinterModel] ([Model], [PrinterMakeID], [CartridgeCode], [PartCode], [Duplex], [NIC], [Wireless], [Parallel], [USB], [Colour], [PrinterTypeID]) VALUES (@Model, @PrinterMakeID, @CartridgeCode, @PartCode, @Duplex, @NIC, @Wireless, @Parallel, @USB, @Colour, @PrinterTypeID) INSERT INTO [Model] ([PrinterModelID],[TypeID]) VALUES (@@IDENTITY, 3)" Can you see any problems that may arise from using this method. This project is an Asset Management System and will be used by no more than a handful of users. My Concern is the use of the @@IDENTITY (As it only stores the last Key used). Should I be using it here? If there is more than one user inserting into tables (Chances of this happening are very low), will the correct Primary key be insert to the table in the above code?Thank you for your comments.
Hi - I'm using .net2, and have a gridview, populated by a SQL Datasource (Edit, Insert, Delete, Select). Like we all used to do with the datagrid, I've added text boxes into the footer, and a link button, which I'd like to use to fire the Update command. How do I get the link button to trigger the update command? Thanks, Mark
Hello all,I'm writing a site with one page that uses the session variable (User ID) to pick one user ID out of a comma separated list in the field Faculty. The default parameterized query designed in the SqlDataSource wizard only returns lines that contain an exact match:SELECT * FROM tStudents WHERE ([faculty] = @faculty) The query: SELECT * FROM tStudents WHERE ([faculty] LIKE '%userID%') works as I need when I hard code the query with a specific user ID into the SqlDataSource in the aspx page. It will not work if I leave the @faculty parameter in it:SELECT * FROM tStudents WHERE ([faculty] LIKE '%@faculty%') e.Command.Parameters works to replace the @Faculty with a user ID, but again, adding the single quote and percentage sign either causes errors or returns no results. I've tried several variations of: string strEraiderID = "'%" + Session["eRaiderID"].ToString() + "%'"; e.Command.Parameters["@faculty"].Value = strEraiderID;no results are returned, not even the lines returned with the default select query.How do generate the equivalent of SELECT * FROM tStudents WHERE ([faculty] LIKE '%userID%') into the SqlDataSource? Thanks much!
Is there any way I can use a variable from my code behind file in the UpdateCommand of a sqlDataSource? I have tried <%$ strUserGuid %>and<% strUserGuid %> any help appreciated.Thanks Dave
I have a gridview with a sqlDataSource with the SelectCommand as "SELECT Movie.Title, Movie.Category, Movie.ReleaseDate, ItemForSale.Quantity, ItemForSale.HasUnLimitedQuantity FROM ItemForSale INNER JOIN Movie ON ItemForSale.ID = Movie.ID"
what kinda 'UpdateCommand' do I set so that ItemForSale is also updated from the grid? I tried two update statements seperated with a semicolon but that wouldn't work, any suggestions...
HiI'm using a simple SqlDataSource to connect to a stored proc to delete a number of rows from different tables.In my SQL im using: DECLARE @table1Count int
DELETE FROM Table1 WHERE id = @new_id
SET @table1Count=@@rowcount
SELECT @table1Count I'm then using an input box and linking it to the delete control parameter. Then on a button click event i'm running SqlDataSource1.Delete() which all works fine. But how do i get the @table1Count back into my aspx page? Thanks
Hello all, Ok, I finally got my SqlDataSource working with Oracle once I found out what Oracle was looking for. My next hurdle is to try and set the Update Command and Parameters dynamically from a variable or radiobutton list. What I'm trying to accomplish is creating a hardware database for our computers by querying WMI and sending the info to textboxes for insertion and updating. I got that part all working nicely. Now I want to send the Computer name info to a different table column depending on if it is a laptop or desktop. I have been tossing around 2 ideas. A radiobutton list to select what it is and change the SQL parameters or do it by computer name since we have a unique identifier as the first letter ("W" for workstation, "L" for Laptop). I'm not sure what would be easiest but I'm still stuck on how this can be done. I posted this same question in here a few days ago, but I didn't have my SqlDataSources setup like I do now, I was using Dreamweaver 8, it is now ported to VS 2005. Below is my code, in bold is what I think needs to be changed dynamically, basically i need to change DESKTOP to LAPTOP...Thanks for all the help I've gotten from this forum already, I'm very new to ASP.NET and I couldn't do this without all the help. Thanks again!
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
I would like to use the value returned from my SqlDataSource SELECT method, in the INSERT method for the same SqlDataSource. Any ideas how this is done?
(Newbie). I'm trying to: 1) check if the SELECT command has returned any records, and 2) put a msg box on the screen if there are no records returned from the SELECT query. The type of code I'm heading towards is: (but it's wrong). Thank you in advance for your C# code suggestion. protected void SqlDataSource1_Selected(object sender, SqlDataSourceStatusEventArgs e) {if (SqlDataSource1.SelectParameters.Contains is "") then MessageBox.show ("There are no records available") ; }
I'm sure that is really simple, but how do I pass a parameter with multiple value to a SQLdatasource? ex: SELECT field1 from tblTableA where idTableA IN ( @Param1) Let's say I want to pass 1,2,3,4 as Param1 (SELECT field1 from tblTableA where idTableA IN ( 1,2,3,4)) How I am supposed tu use the .SelectParameters.Add() to pass a list of integers instead of a single value?? Thanks in advance.
Is it possible to use a WHERE-IN statement with a SqlDataSource control. For instance: <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:myConnectionString %>" SelectCommand="SELECT [Id], [Name], [Phone] FROM [Table1] WHERE ([Id] IN @Id)"> <SelectParameters> <asp:Parameter DefaultValue="( 1, 3, 5, 7, 11 )" Name="Id" Type="Int32" /> </SelectParameters></asp:SqlDataSource> I'm hoping the gridview would then display a table with rows for records 1,3,5,7,11. Thanks for any help...
I'm using the following Select Command:SELECT MAX(Document) AS DOC FROM dbo.Communicator WHERE (ReleaseDate <= { fn NOW() })It shows the most current date in the ReleaseDate column - even if the date is in the future. I don't want it to show future dates. If I change the command to WHERE (ReleaseDate >= { fn NOW() }) it doesn't work at all. I only want it to return one row - the latest releases date that is equal to or less than now.Any ideas?
I have a web form that has textbox1, textbox2 and DropDownList1. Let’s say textbox one is first name, textbox2 is last name and dropdownlist1 is age. How do I write a query that will select from database table dbo.emoployee where last name = dbo.employee.lastname and all other fields are blank, I want it to return all employees with that last name. If someone types in the last name and selects the age from the drop down list then I want to return all employees where last name = dbo.employee.lastname and age = dbo.employee.age. If someone types in just the first name then I want to return all employees where first name = pub.employees.firstname? I have been trying to do this using the SQLDatasource but cannot seem to figure it out.
I have a web form that has textbox1, textbox2 and DropDownList1. Let’s say textbox one is first name, textbox2 is last name and dropdownlist1 is age. How do I write a query that will select from database table dbo.emoployee where last name = dbo.employee.lastname and all other fields are blank, I want it to return all employees with that last name. If someone types in the last name and selects the age from the drop down list then I want to return all employees where last name = dbo.employee.lastname and age = dbo.employee.age. If someone types in just the first name then I want to return all employees where first name = pub.employees.firstname? I have been trying to do this using the SQLDatasource but cannot seem to figure it out.
I currently have a webpage that allows visitors to post links to their own website. As a spam filter I want to create a scheduled task that selects repeat entries in the database under the column name domain. I already made a filter to take everything out of the link the provide and leave it with just the domain name. I tested it and it works. Now I need a SQL command to select all the rows with repeats of the domain. Look at the following table example to better understand what I mean uid x y domain1 100 110 www.spam.com2 100 120 www.spam.com3 110 130 www.homepage.com4 210 220 www.myaspspam.com5 510 560 www.myaspspam.com in this example I would like 1 and 2 to be selected as well as 4 and 5 into a dataadapter so that I can delete them accordingly.
im trying to write a select command that gets info from 1 table and counts 11 differnt things in it im not sure if this is even posiable but if it is could someone help this is what i got for counting all of them SELECT owner, COUNT(*) AS TotalPots FROM Items WHERE (Name = 'Holy Potion') OR (Name = N'Arcane Potion') OR (Name = N'Shadow Potion') OR (Name = N'Fire Potion') OR (Name = N'Kinetic Potion') OR (Name = N'Potion of Holy Resistance') OR (Name = N'Potion of Arcane Resistance') OR (Name = N'Potion of Shodow Resistance') OR (Name = N'Potion of Fire Resistance') OR (Name = N'Potion of Kinetic Resistance') GROUP BY owner ORDER BY COUNT(*) DESC the 11 coloums i want are Holy, Arcane, Shadow, Fire, Kinetic, Holy Resist, Arcane Resist, Shadow resist, Fire Resist, Kinetic Resist, And Total Pots Also would like it on my Asp.net page at the bottom of the grid view to have a total row that counts all the colums up
