An SQLDataSource On 2 Other DataSources? Show In A GridView
May 19, 2008
Hi,I have two SQLDataSources called "LeagueTableHome" an "LeagueTableAway" on my page.
I want to create another SQLDataSource called "LeagueTableTotal" on my page which adds up all the totals from each of the other two sources.
The datasource looks like this:
Team, Pld, W, D, L, F, A, Agg, Pts
my code for LeagueTableHome looks like this:
SELECT HomeTeam, 1 AS Pld, CASE WHEN HomeScore > AwayScore THEN 1 ELSE 0 END AS Won, CASE WHEN HomeScore = AwayScore THEN 1 ELSE 0 END AS Draw, CASE WHEN HomeScore < AwayScore THEN 1 ELSE 0 END AS Lost, HomeScore AS Scored, AwayScore AS Against, HomeScore - AwayScore AS Agg, CASE WHEN HomeScore > AwayScore THEN 3 ELSE 0 END AS Pts FROM tblFixtures WHERE (CompID = 1) AND (HomeScore IS NOT NULL)
I want then to show LeagueTableTotal in a GridView.
i have a gridview which is bound to sqldatasource. i have a delete button in the gridview. I have written code for "deleting" a record in app_code directory. now iam not using "deletecommand" of sqldatasource but on the click of "delete" link i want to call the procedure in the app_code. Any ideas on how to do it.??
I have created a GridView that uses a SqlDataSource. When I run the page it does not pull back any data. However when I test the query in the SqlDataSource dialog box it pulls back data. Here is my GridView and SqlDataSource: <asp:GridView ID="Results" runat="server" AllowPaging="True" AllowSorting="True" CellPadding="2" EmptyDataText="No records found." AutoGenerateColumns="False" Width="100%" CssClass="tableResults" PageSize="20" DataSourceID="SqlResults" > <Columns> <asp:BoundField DataField="DaCode" HeaderText="Sub-Station" SortExpression="DaCode" > <ItemStyle HorizontalAlign="Center" CssClass="tdResults" /> <HeaderStyle HorizontalAlign="Center" CssClass="tdHeaderResults" /> </asp:BoundField> <asp:BoundField DataField="DpInfo" HeaderText="Delivery Point" SortExpression="DpInfo" > <HeaderStyle HorizontalAlign="Left" CssClass="tdHeaderResults" /> <ItemStyle CssClass="tdResults" /> </asp:BoundField> <asp:HyperLinkField DataNavigateUrlFields="CuCode,OrderID" DataNavigateUrlFormatString="TCCustDetail.asp?CuCode={0}&OrderID={1}" DataTextField="OrderID" HeaderText="Order No" SortExpression="OrderID"> <ItemStyle CssClass="tdResults" HorizontalAlign="Center" /> <HeaderStyle CssClass="tdHeaderResults" HorizontalAlign="Center" /> </asp:HyperLinkField> <asp:BoundField HeaderText="Order Date" SortExpression="OrderDate" DataField="OrderDate" > <ItemStyle HorizontalAlign="Center" CssClass="tdResults" /> <HeaderStyle HorizontalAlign="Center" CssClass="tdHeaderResults" /> </asp:BoundField> <asp:BoundField DataField="ReqDeliveryDate" HeaderText="Req Delivery Date" SortExpression="ReqDeliveryDate" > <ItemStyle HorizontalAlign="Center" CssClass="tdResults" /> <HeaderStyle HorizontalAlign="Center" CssClass="tdHeaderResults" /> </asp:BoundField> <asp:BoundField DataField="StatusDate" HeaderText="Status Date" SortExpression="StatusDate"> <ItemStyle HorizontalAlign="Center" CssClass="tdResults" /> <HeaderStyle HorizontalAlign="Center" CssClass="tdHeaderResults" /> </asp:BoundField> <asp:BoundField DataField="ManifestNo" HeaderText="Manifest No" SortExpression="ManifestNo"> <ItemStyle HorizontalAlign="Center" CssClass="tdResults" /> <HeaderStyle HorizontalAlign="Center" CssClass="tdHeaderResults" /> </asp:BoundField> <asp:BoundField DataField="CustomerPO" HeaderText="P.O. No" SortExpression="CustomerPO"> <ItemStyle HorizontalAlign="Center" CssClass="tdResults" /> <HeaderStyle HorizontalAlign="Center" CssClass="tdHeaderResults" /> </asp:BoundField> <asp:BoundField DataField="Class" HeaderText="Class" SortExpression="Class"> <ItemStyle HorizontalAlign="Left" CssClass="tdResults" /> <HeaderStyle HorizontalAlign="Left" CssClass="tdHeaderResults" /> </asp:BoundField> <asp:BoundField DataField="OrderStatus" HeaderText="Order Status" SortExpression="StatusSort"> <ItemStyle HorizontalAlign="Left" CssClass="tdResults" /> <HeaderStyle HorizontalAlign="Left" CssClass="tdHeaderResults" /> </asp:BoundField> </Columns> <HeaderStyle ForeColor="White" HorizontalAlign="Left" /> <AlternatingRowStyle CssClass="tdResultsAltRowColor" /> </asp:GridView> <asp:SqlDataSource ID="SqlResults" runat="server" ConnectionString="<%$ ConnectionStrings:TransportationConnectionString %>" SelectCommand="GetOrderSummaryResults" SelectCommandType="StoredProcedure"> <SelectParameters> <asp:Parameter DefaultValue="10681" Name="CuCode" Type="String" /> <asp:Parameter DefaultValue="" Name="DaCode" Type="String" /> <asp:Parameter DefaultValue="" Name="DpCode" Type="String" /> <asp:Parameter DefaultValue="" Name="OrderID" Type="String" /> <asp:Parameter DefaultValue="" Name="ManifestNo" Type="String" /> <asp:Parameter DefaultValue="" Name="PONo" Type="String" /> </SelectParameters> </asp:SqlDataSource> I can get it to fill with data by manually filling the GridView without using a SqlDataSource but then I cannot get the sorting to work when I do it that way. Actually not sure if the sorting will work this way either as I cannot get it to fill with data. Any ideas would be much appreciated.
Dear people, When i test my page for uploading image too my sql database everthing goes ok (i think) en when i look into my database table i see 3 colums filled 1 column with: Image_title text 1 column with:Image_stream <binary data> 1 column with image_type image/pjpeg How can i show this image in a gridview column..... i have search for this problem but non of them i find i can use because its a too heavy script, or something i dont want. Is there a helping hand Below is the script for uploading the image.....and more
1 2 Imports System.Data 3 Imports System.Data.SqlClient 4 Imports System.IO 5 6 Partial Class Images_toevoegen 7 Inherits System.Web.UI.Page 8 9 10 Protected Sub btnUpload_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnUpload.Click 11 12 Dim imageSize As Int64 13 Dim imageType As String 14 Dim imageStream As Stream 15 16 ' kijkt wat de groote van de image is 17 imageSize = fileImgUpload.PostedFile.ContentLength 18 19 ' kijk welke type image het is 20 imageType = fileImgUpload.PostedFile.ContentType 21 22 ' Reads the Image stream 23 imageStream = fileImgUpload.PostedFile.InputStream 24 25 Dim imageContent(imageSize) As Byte 26 Dim intStatus As Integer 27 intStatus = imageStream.Read(imageContent, 0, imageSize) 28 29 ' connectie maken met de database 30 Dim myConnection As New SqlConnection(ConfigurationManager.ConnectionStrings("Personal").ConnectionString) 31 Dim myCommand As New SqlCommand("insert into tblMateriaal(Image_title,Image_stream,Image_type,ArtikelGroep,ArtikelMaat,Aantal,Vestiging,ArtikelNaam,ContactPersoon,DatumOnline) values(@Image_title,@Image_stream,@Image_type,@ArtikelGroep,@ArtikelMaat,@Aantal,@Vestiging,@ArtikelNaam,@ContactPersoon,@DatumOnline)", myConnection) 32 33 ' Mark the Command as a Text 34 myCommand.CommandType = CommandType.Text 35 36 ' geef alle parameters mee aan het command 37 Dim Image_title As New SqlParameter("@Image_title", SqlDbType.VarChar) 38 Image_title.Value = txtImgTitle.Text 39 myCommand.Parameters.Add(Image_title) 40 41 Dim Image_stream As New SqlParameter("@Image_stream", SqlDbType.Image) 42 Image_stream.Value = imageContent 43 myCommand.Parameters.Add(Image_stream) 44 45 Dim Image_type As New SqlParameter("@Image_type", SqlDbType.VarChar) 46 Image_type.Value = imageType 47 myCommand.Parameters.Add(Image_type) 48 49 Dim ArtikelGroep As New SqlParameter("@ArtikelGroep", System.Data.SqlDbType.NVarChar) 50 ArtikelGroep.Value = ddl1.SelectedValue 51 myCommand.Parameters.Add(ArtikelGroep) 52 53 Dim ArtikelMaat As New SqlParameter("@ArtikelMaat", System.Data.SqlDbType.NVarChar) 54 ArtikelMaat.Value = ddl2.SelectedValue 55 myCommand.Parameters.Add(ArtikelMaat) 56 57 58 Dim Aantal As New SqlParameter("@Aantal", System.Data.SqlDbType.NVarChar) 59 Aantal.Value = ddl3.SelectedValue 60 myCommand.Parameters.Add(Aantal) 61 62 Dim Vestiging As New SqlParameter("@Vestiging", System.Data.SqlDbType.NVarChar) 63 Vestiging.Value = ddl4.SelectedValue 64 myCommand.Parameters.Add(Vestiging) 65 66 Dim ArtikelNaam As New SqlParameter("@ArtikelNaam", System.Data.SqlDbType.NVarChar) 67 ArtikelNaam.Value = tb6.Text 68 myCommand.Parameters.Add(ArtikelNaam) 69 70 Dim ContactPersoon As New SqlParameter("@ContactPersoon", System.Data.SqlDbType.NVarChar) 71 ContactPersoon.Value = tb1.Text 72 myCommand.Parameters.Add(ContactPersoon) 73 74 Dim DatumOnline As New SqlParameter("@DatumOnline", System.Data.SqlDbType.NVarChar) 75 DatumOnline.Value = tb2.Text 76 myCommand.Parameters.Add(DatumOnline) 77 78 Try 79 myConnection.Open() 80 myCommand.ExecuteNonQuery() 81 myConnection.Close() 82 83 Response.Redirect("toevoegen.aspx") 84 Catch SQLexc As SqlException 85 Response.Write("Insert Failure. Error Details : " & SQLexc.ToString()) 86 End Try 87 88 89 End Sub 90 End class
Hi guys.Im using a gridview to show some training data on my website which is populated by:<asp:SqlDataSource ID="ARENATraining" runat="server" ConnectionString="<%$ ConnectionStrings:ARConnection %>" SelectCommand="SELECT [EventType], [CourseLink], [EventDate], [EventTitle], [EventLocation], [EventWebsite] FROM [qry_FutureEvents] WHERE ([EventPrivate] = @EventPrivate) ORDER BY [EventDate]"> <SelectParameters> <asp:Parameter DefaultValue="FALSE" Name="EventPrivate" Type="Boolean" /> </SelectParameters> So far so good..However I want to be able to filter the data.A) Show everythingB) Show individual EventType i.e. Seminar, Training etc etcIs it possible to generate the Select Command via a function? (im not using stored procedures for this part of the site, nor really want to at the moment). Id appreciate any help, otherwise ill be forced to curse and swear and use the good ol repeater and figureing out how to page it! Cheers guys
i am now writing a web page which can allow users to edit the field(display in the gridview->gridview source is from sqldatasource), there is a problem(it seems that i have followed all steps in the book): when the i edit the field and click update, in the web site, i can see those changes. however, i cant see any change in database table. i cant find solution on this, could anyone help me to solve this problem? thx!
Hello.When I create a user at the ASP.NET database, I need to insert more fields than the defaults, and I do it like this: Dim customProfile As ProfileCommon = ProfileCommon.Create(CreateUserWizard1.UserName, True) customProfile.telephone = (CType(CreateUserWizard1.CreateUserStep.ContentTemplateContainer.FindControl("TelephoneText"), TextBox)).Text(telephone example)Those data are inserted at the DB at the aspnet_Profile table, in the fields PropertyNames & PropertyValuesString, but they are saved together (see image above)
I want to separate those properties in the GridView as long as each property appears in a column, is it possible? Thank you very much, i'm expecting your answers.
hi .. i have a SqlDataSource, GridView & a Button .. i want to increase the condition in the "SelectCommand" below by one everytime i click the button .. i mean to be like that ID < 8 instead of ID < 7 so, the GridView will show 7 ID's instead of 6 .. and it will increase everytime i hit the button<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:connectionstring %>" SelectCommand="SELECT * FROM [table1] WHERE ID < 7"> </asp:SqlDataSource>
What is the easiest way to obtain number of records in SQLDataSource (using select statement)/GridView. All that I've found in forums seems to be very difficult for such trivial task... Thank you!
Hi I have a gridview which is using a SQLdatasource, to update the table. One of my parameters on the SP is username. At the moment on page load I am declaring a session variable Session("UserName") = me.user.identy.name I then amend the update parameter in the SQL Datasource to accept the username from the session variable. - works fine, I was however wondering if there is a way of returning the username directly into the sqldatasource without having to pass it through a session variable?
Hi guys! I have a simple windows form where a gridview is being populated by a database. I have checked the ability to update, insert delete. And everything is fine but when i want to delete a row it throws an exeption. The exeptions says: "Must declare the scalar variable "@id"."I haven't changed the delete, update and insert commands. They are as fallowsDeleteCommand="DELETE FROM [recepti] WHERE [id] = @id"InsertCommand="INSERT INTO [recepti] ([ime], [recepta], [snimka], [snimka2], [tip]) VALUES (@ime, @recepta, @snimka, @snimka2, @tip)" SelectCommand="SELECT * FROM [recepti]"UpdateCommand="UPDATE [recepti] SET [ime] = @ime, [recepta] = @recepta, [snimka] = @snimka, [snimka2] = @snimka2, [tip] = @tip WHERE [id] = @id" The id column is auto generated, unique, also it's a primery key.So any idea where is the problem?
I cant seen to change the Select command for a SQL Datasourcetry #1 SqlDataSourceProfilesThatMatch.SelectCommand = strSQLForSearch SqlDataSourceProfilesThatMatch.SelectParameters("ProfileID").DefaultValue = pProfileID SqlDataSourceProfilesThatMatch.SelectParameters("LoggedInUsersZipcode").DefaultValue = pUsersZipCode SqlDataSourceProfilesThatMatch.SelectParameters("ZipDistance").DefaultValue = pDistance NewProfilesThatMatchGridView.DataBind() try #2 SqlDataSourceProfilesToBeMatched.SelectParameters.Clear() SqlDataSourceProfilesThatMatch.SelectCommand = strSQLForSearch SqlDataSourceProfilesThatMatch.SelectParameters.Add("ProfileID", pProfileID) SqlDataSourceProfilesThatMatch.SelectParameters.Add("LoggedInUsersZipcode", pUsersZipCode) SqlDataSourceProfilesThatMatch.SelectParameters.Add("ZipDistance", pDistance) NewProfilesThatMatchGridView.DataBind() No errors but no rows show in the gridview. If I debug and get the value strSQLForSearch and paste it into a new SQL query window I get results. Any ideas???? Thanks
Hello I'm experiencing some problems, binding a SqlDataSource to a GridView. The following code creates the SqlDataSource: string strConn = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString; string strProvider = ConfigurationManager.ConnectionStrings["ConnectionString"].ProviderName; SqlDataSource ds = new SqlDataSource(strProvider, strConn); ds.SelectCommand = "SELECT * FROM rammekategori"; Then i bind the SqlDataSource to a GridView: GridView1.DataSource = ds; GridView1.DataBind();
ErrorMessage: Format of the initialization string does not conform to specification starting at index 0. Exception Details: System.ArgumentException: Format of the initialization string does not conform to specification starting at index 0.Line 24: GridView1.DataBind();Am i totally off target here? Can it be something about, that you have to set the datasource of the gridview, before the Page_Load event?
Help!I am trying to fill my datagrid using the SQLDataSource, using a stored procedure.The stored procedure expects a parameter which I can collect via the querystring, or a string. How can I pass the parameter through the SQLDatasSource?My SQLDataSource is SQLData1. I already have:SQLData1.SelectCommandType = SqlDataSourceCommandType.StoredProcedureSQLData1.SelectCommand = "dbo.get_players"Thanks in advance,Karls
Hi, I'm new in ASP 2.0. I need to incorporate edit and delete capability in GridView. Using the wizard, i've generated this code. When I delete a row, it gets deleted but update does not work. I've tried several ways. I got no error or exception. But row is not updated. I've checked database, and I think the update query is not executing at all. Please let me know, what I'm doing wrong? Here is the source code for reference. I'm using Visual Studio 2005 with SQL server 2005 Express Edition. Regards
I have a SqlDataSource that I need to remove the first 3 rows from before it is bound to a GridView. How would I go about doing this? (if I could remove them at the db level in the sproc I would, but right now that is not an option - so I need to do it once I've already received the data) Thanks.
Hi, I have a GridView connected to a sqldatasource control. Everything is working great, updates, paging and filtering with one exception. When the user enters in a name like O'Reilly (with a single quote), the page errors. The error returned is: Syntax error: Missing operand after 'Reilly' operator. Here is the definition of the sqldatasource: <asp:SqlDataSource ID="SqlDataSourcePersons" runat="server" ConnectionString="<%$ ConnectionStrings:database %>" SelectCommand="SELECT [Id], [FirstName], [LastName], , [PersonTypeId], [WorkerId], [_workerNTId], [Title], [City] FROM [Person]" FilterExpression="(FirstName like '{0}%') AND (LastName like '{1}%') AND (WorkerId like '{2}%') AND (City like '{3}%')" ProviderName="System.Data.SqlClient"> <FilterParameters> <asp:ControlParameter ControlID="TextBoxFirstName" Name="FirstName" DefaultValue="%" PropertyName="Text" Type="String" /> <asp:ControlParameter ControlID="TextBoxLastName" Name="LastName" DefaultValue="%" PropertyName="Text" Type="String" /> <asp:ControlParameter ControlID="TextBoxWorkerId" Name="WorkerId" DefaultValue="%" PropertyName="Text" Type="String" /> <asp:ControlParameter ControlID="TextBoxCity" Name="City" DefaultValue="%" PropertyName="Text" Type="String" /> </FilterParameters> </asp:SqlDataSource> Any suggestions would be appreciated. Thank you, Jim
Hi, say I have two Sqldatasources objects:SqlDataSource1 and SqlDataSource2.... Does anybody know how can I alter programmatically these two sqldatasources in a gridview? Thanks!!!
Hi all, first post, and I am desperate. I have a SqlDataSource with a Select, Update and Delete command. From what I understand, scalar variables should be read automatically from the GridView's BoundField columns when it executes a command on it. Here is my code:
I apologize for the way the code is put in, the code thing cut off a lot of the text! The problem I'm getting is, when the 'Update' button is hit, I get the error: Must declare the variable '@TeamId'.
I've tried putting "Update Parameters", that takes away the error, but the row does not update. I've browsed the internet and saw the same problems in lots of areas, but either a) none of the solutions work for me, or b) they don't really apply to my case. I'm using ASP .NET 2.0, and (obviously) C#. SQL Server database. Any help is greatly appreciated. Thanks in advance,
hello all 1) I was using SQLdataSource and bind it with the gridviw to show its data. so that is how is running when the page load, i see the data is posted on the page on the gridview. But what i need to do when I want a user to click a button and then the Gridview show the data.
As i was creating a SQLDatasource, it was click click and click but I want to put that sqldatasouce to load the data in gridview by a click event.
2) my second question is also related to the first one or just similar. I dont want to get all the data rather filter it. so once the page load, there is a button and a text box. In the text box user going to type the EmployeeID and click the "Employee Info" button and then the data would show up. Rather i use te DetaildView Control . What should I do to load the information on the Text boxes rather then on the GridView since it is going to be a single recrod. Thanks all
i am using 2 textbox to search name and instituition of some students. on button click need to display the result on gridview. but i dont get any result.. pl adviceSqlDataSource1.SelectCommand = "SELECT Sname, Address, Instituition, Role, Testgroup, Email FROM std_det WHERE (Sname LIKE '%'+@Param1+'%') AND (Instituition = 'RASET') AND (Role LIKE '%'+@Param2+'%') AND (Instituition = 'RASET')"; name.ControlID = "TextBox3"; name.DefaultValue = "%"; name.Name = "Param1"; name.PropertyName = "Text"; inst.ControlID = "TextBox4"; inst.DefaultValue = "%"; inst.Name = "Param2"; inst.PropertyName = "Text"; //SqlDataSource1.Select(DataSourceSelectArguments.Empty); SqlDataSource1.SelectParameters.Add(name); SqlDataSource1.SelectParameters.Add(inst); SqlDataSource1.DataBind(); //DataView dv = (DataView)this.SqlDataSource1.Select(DataSourceSelectArguments.Empty); GridView2.DataSourceID = "SqlDataSource1"; GridView2.DataBind();
I have a gridview that is tied to a SQLDataSource control which is inside an update panel. What I'm trying to accomplish is when the page is loaded the gridview is filled from the SQLDataSource. I have some textboxes also on the page so that the user can add new items to the gridview. The update panel trigger is tied to my code behind page through a button click which added the new data to the DB with an INSERT statement. Since the gridview was inside the update panel I thought when I placed the new data into the DB and the update panel caused the portion of the page that holds the gridview to refresh that the gridview would show the newly added data but it is not. I am missing something but I do not know what. I tried adding a gridview1.databind() and SQLDataSource1.DataBind() but niether caused the update of the gridview as I had hoped. Thanks for any of your help. Ty
Hi all I have a GridView on an aspx page, that is enabled for editing, deletion and sorting. In the Page_Load event of the aspx page, i add a SqlDataSource to the page, and bind the source to the GridView. When i click the update, or delete button, it makes a PostBack, but nothing is affected. I'm sure this has got something to do with the parameters. First, i tried having the GridView.AutoGenerateColumns set to True. I have also tried adding the columns manually, but no affect here either. The code for setting the commands, and adding the SqlDataSource to the page are as follows: string strConn = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString; string strProvider = ConfigurationManager.ConnectionStrings["ConnectionString"].ProviderName; string selectCommand = "SELECT * FROM rammekategori"; SqlDataSource ds = new SqlDataSource(strProvider, strConn, selectCommand); ds.ID = "RammeKategoriDS"; ds.UpdateCommand = "UPDATE rammekategori SET Kategoribeskrivelse = @Kategoribeskrivelse WHERE (Kategorinavn = @Kategorinavn)"; ds.DeleteCommand = "DELETE FROM rammekategori WHERE (Kategorinavn = @Kategorinavn)"; Parameter Kategorinavn = new Parameter("Kategorinavn", TypeCode.String); Parameter Kategoribeskrivelse = new Parameter("Kategoribeskrivelse", TypeCode.String); ds.UpdateParameters.Add(Kategorinavn); ds.UpdateParameters.Add(Kategoribeskrivelse); ds.DeleteParameters.Add(Kategorinavn); Page.Controls.Add(ds); SqlDataSource m_SqlDataSource = Page.FindControl("RammeKategoriDS") as SqlDataSource; if (m_SqlDataSource != null) { this.gvRammeKategorier.DataSourceID = m_SqlDataSource.ID; } As mentioned - no affect at all! Thanks in advance - MartinHN
There are so many ways to use database in asp.net/ado.net, I'm a bit confused about their difference from the performance point of view.So apparently SqlDataSource in DataReader mode is faster than DataSet mode, at a cost of losing some bolt-on builtin functions.What about SqlDataSource in DataReader mode vs manual binding in code? Say creating a SqlDataSource ds1 and set "DataSourceID" in Gridview, vs manually creating the SqlConnection, SqlCommand, SqlDataReader objects and mannually bind the myReader object to the gridview with the Bind() method.Also Gridview is a very convenient control for many basic tasks. But for more complex scenarios it requires lots of customization and modification. Now if I do not use gridview at all and build the entire thing from scratch with basic web controls such as table and label controls, and mannually read and display everything from a DataReader object, how's the performance would be like compared to the Gridview-databind route?
<!-- Trying to create a GridView Survey form: Hi [User] Do you have these assets? [Gridview] Tag Number Response 123 [Yes ] [No] [Comments textbox] 234 [Yes ] [No] [Comments textbox]
Goal: The desire is for the user to click Yes or No and have the database updated with the user's name, date, response and any comments.
So far, I have created SqlDataSource with Select and Update commands, created the gridview and response buttons, setup the RowCommand and On_Updating Functions.
Problems:1) If I call the update() function for SqlDataSource1 and misuse the Defaultvalue parameters to run the sql, no update is posted to the database.
2) If I use the On_Update function to set the parameter values, I get "Data type mismatch in criteria expression." (Additionally, the On_Update function runs twice which I don't understand)Can anyone tell me what/why? (and how to fix it?)Sorry for the deluge, but here is the code: -->
<%@ Page Language="VB" %> <html> <head id="Head1" runat="server"> <title>Asset Survey</title> </head> <SCRIPT runat="server"> Protected Sub SqlDataSource1_Selecting(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.SqlDataSourceSelectingEventArgs) Handles SqlDataSource1.Selecting e.Command.Parameters("Name1").Value = User.Identity.Name End Sub
Protected Sub SqlDataSource1_Updating(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.SqlDataSourceCommandEventArgs) Handles SqlDataSource1.Updating 'For some reason, this function executes twice '2nd execution gets error: "Data type mismatch in criteria expression." 'e.Command.Parameters("PrimaryKey1").Value = intPrimaryKey 'e.Command.Parameters("Responder1").Value = strUser 'e.Command.Parameters("ResponseDate1").Value = dtModDate 'e.Command.Parameters("Response1").Value = strResponse 'e.Command.Parameters("ResponseComments1").Value = strComments End Sub
Protected Sub GridView1_RowCommand(ByVal sender As Object, ByVal e As GridViewCommandEventArgs) _ Handles GridView1.RowCommand Dim GridRow1 As Integer = Convert.ToInt32(e.CommandArgument) If e.CommandName = "UpdateYes" Then 'code here to disable buttons and textbox '...
SqlDataSource1.UpdateParameters("Response1").DefaultValue = "Yes" End If
If e.CommandName = "UpdateNo" Then 'code here to disable buttons and textbox '...
SqlDataSource1.UpdateParameters("Response1").DefaultValue = "No" End If
'if I use the SqlDataSource1_Updating function, I get the mismatch error 'but if I (mis)use the DefaultValue parameter, no update occurs. SqlDataSource1.UpdateParameters("ResponseComments1").DefaultValue = _
SqlDataSource1.Update() 'Error Here if I use on_updating: "Data type mismatch in criteria expression." Catch except As Exception ' Handle the Exception. End Try End Sub
</SCRIPT> <body> <form id="formInv" runat="server"> <div> <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:LocalTestMDB %>" ProviderName="<%$ ConnectionStrings:LocalTestMDB.ProviderName %>" SelectCommand="SELECT [PrimaryKey], [Name], [AssetTag], [Response], [Responder], [ResponseDate],[ResponseComments] FROM [Tablename] WHERE ([Name] = ?) ORDER BY [Login Name], [AssetTag]" UpdateCommand="Update [Tablename] SET [Response]=@Response1, [Responder]=@Responder1, [ResponseDate]=ResponseDate1, [ResponseComments]=ResponseComments1 WHERE [PrimaryKey]=@PrimaryKey1" OnSelecting="SqlDataSource1_Selecting" OnUpdating="SqlDataSource1_Updating"> <SelectParameters>
Hi, I think the title explains it all really. I cannot configure the SqlDataSource to add Insert, Update and Delete statements - not through the wizard anyway. The view combines two tables, each containing 3 columns. I have tried using different combinations of columns, and ended up selecting all of them (making sure the foreign key column of the first table was given a different alias to the primary key column of the second table) but I still cannot enable editing. Cheers, Patrick
Using 3.5 If I have a sqldatasource on the page, is it run if it is not bound to a data object like a gridview? Seems like if i want to access the data (like set a label text) from the sqldatasource I have to use code to first create a dataview then pick throught it. This seems like I'm running it twice. I'm new at .net so I dont know how to tell. I don't want to write data select code programatically when I can just through an SDS on the page, but wondered it it ran just because it's on the page.
Hi, I got a page with 3 sqldatasources and 2 dropdownlists and 1 gridview. 2 sqldatasources may load when the page load adn fill in the 2 DDL's. But 1 sqldatasource and the gridview may only be loaded when I press a button. This is because otherwise every time I load the page more then 5000 records. (the 2 DDL's are filters for the 3th sqldatasource) Anyone an idea how to solve this problem? Best regards, Karel
Not sure if this is the correct forum, but I 'm having problems retrieving a sqldatasource's asp:control parameter values from a selected row (during edit) in a gridview to update a record thru a stored procedure. The stored procedure is pretty intense, so I'd like to keep it in SQL if possible instead of creating the generic "update table set ..." that I see in most examples. It seems as if I can't get the propertyname right or something because it keeps giving me a "Procedure or function XX has too many arguments specified error". Maybe the DataKeyNames is not right?? I've tried just passing one parameter (ProductID-same as DataKeyNames) using "SelectedValue" as propertyname and still get the same. It's got to be something very simple, but I'm at a loss. All parameters are spelled the same in the sp (with an added "@" at start) as in the asp:controlparameters. Here's the gridview (asp.net 2.0 connecting to SQL Server 2005): <asp:GridView ID="gvLoadEditProductPrices" runat="server" AutoGenerateColumns="False" AllowSorting="True" DataSourceID="SqlDataSource1" DataKeyNames="ProductID"> <Columns> <asp:CommandField ShowEditButton="True" /> <asp:BoundField DataField="ProductID" HeaderText="ProductID" HeaderStyle-BackColor="white" InsertVisible="False" ReadOnly="True" SortExpression="ProductID" /> <asp:BoundField DataField="Product" HeaderText="Product" SortExpression="Product" ReadOnly="True" /> <asp:BoundField DataField="ProductCat" HeaderText="ProductCat" SortExpression="ProductCat" ReadOnly="True" /> <asp:BoundField DataField="VarRate" HeaderText="VarRate" SortExpression="VarRate" /> <asp:BoundField DataField="loadid" HeaderText="loadid" InsertVisible="False" ReadOnly="True" SortExpression="loadid" /> <asp:BoundField DataField="loadamount" HeaderText="loadamount" SortExpression="loadamount" ReadOnly="True" /> <asp:BoundField DataField="ProductCol" HeaderText="ProductCol" SortExpression="ProductCol" ReadOnly="True" /> <asp:BoundField DataField="PageID" HeaderText="PageID" SortExpression="PageID" ReadOnly="True" /> </Columns> </asp:GridView> and the sqldatasource's info: <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:MARSProductEditor %>" ProviderName="System.Data.SqlClient" SelectCommand="spGetLoadEditProductPrices" SelectCommandType="StoredProcedure" UpdateCommand="spUpdateProductPrices" UpdateCommandType="StoredProcedure" > <UpdateParameters> <asp:ControlParameter Name="ProductID" Type="Int32" ControlID="gvLoadEditProductPrices" PropertyName=SelectedDataKey.Values("ProductID")></asp:ControlParameter> <asp:ControlParameter Name="LoadID" Type="Int32" ControlID="gvLoadEditProductPrices" PropertyName=SelectedDataKey.Values("LoadID")></asp:ControlParameter> <asp:ControlParameter Name="PageID" Type="Int32" ControlID="gvLoadEditProductPrices" PropertyName=SelectedDataKey.Values("PageID")></asp:ControlParameter> <asp:ControlParameter Name="ProductCol" Type="Int32" ControlID="gvLoadEditProductPrices" PropertyName=SelectedDataKey.Values("ProductCol")></asp:ControlParameter> <asp:ControlParameter Name="NewRate" Type="Double" ControlID="gvLoadEditProductPrices" PropertyName=SelectedDataKey.Values("NewRate")></asp:ControlParameter> </UpdateParameters> <SelectParameters> <asp:ControlParameter ControlID="ddlEstLoadsPerAcre" Name="LoadID" PropertyName="SelectedValue" Type="Int32" /> <asp:ControlParameter ControlID="txtEditType" Name="PageName" PropertyName="Text" Type="String" /> </SelectParameters> </asp:SqlDataSource>
Hi all, I have a gridview bound with a SQLDataSource. I am using the Update feature of the SQLDataSource to update a SQL Server database with values entered into the gridview. However I am not getting it to work. I believe this is due to the controls that contain the user entries are not the gridview itself, but rather child controls within the gridview. I have been using the names of the actual controls but nothing happens. Upon submit, the screen returns blank, and the database is not updated. Here is some code:
As seen above, controls such as txtPrimarySettlement are referenced but the update is not successful. The text boxes are within the GridEditSettlement gridview. In the .aspx code I cannot use FindControl (at least I don't think it will work). So the questions are: Is it possible to reference the child controls, if so - how? Is there another way to do this, such as in the vb code behind - in the either the gridview's RowUpdating event or the SQLDataSource's Updating event. What is the best approach? Anyone come up against this issue before? Thanks, KB
I have a gridview connected to a sqldatasource, and it works pretty good. It gives me the subsets of the information that I need. But, I really want to let them choose all the companies and/or any status. What's the best way to get all the values in the gridview...besides removing the filters :) I thought the company would be easy, I'd just set the selected value to blank "", and then it'd get them all....but that's not working. And, for the boolean, I have no idea to get the value without having a separate query. (tabs_done=@tabsdone) and (company like '%' + @company + '%')1 <asp:DropDownList ID="drpdwnProcessingStatus" runat="server"> 2 <asp:ListItem Value="0">Open</asp:ListItem> 3 <asp:ListItem Value="1">Completed</asp:ListItem> 4 </asp:DropDownList> 5 6 7 <asp:DropDownList ID="drpdwnCompany" runat="server"> 8 <asp:ListItem Value="">All</asp:ListItem> 9 <asp:ListItem Value="cur">Cur District</asp:ListItem> 10 <asp:ListItem Value="jho">Jho District</asp:ListItem> 11 <asp:ListItem Value="sea">Sea District</asp:ListItem> 12 <asp:ListItem Value="san">Net District</asp:ListItem> 13 <asp:ListItem Value="sr">Research District</asp:ListItem> 14 </asp:DropDownList> 15 16 17 <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:HRFormsConnectionString %>" 18 SelectCommand="SELECT DISTINCT [id], [lastname], [company] FROM [hr_term] hr where (tabs_done=@tabsdone) and (company like '%' + @company + '%')"> 19 <SelectParameters> 20 <asp:ControlParameter ControlID="drpdwnProcessingStatus" DefaultValue="0" Name="tabsdone" PropertyName="SelectedValue" /> 21 <asp:ControlParameter ControlID="drpdwnCompany" DefaultValue="" Name="company" PropertyName="SelectedValue" /> 22 </SelectParameters> 23 </asp:SqlDataSource> 24
Hi -- I'm starting an ASP.NET 2.0 application which contains a page with a checkbox and gridview control on it. In its default state the gridview displays all the records from a table pulled from a SQL Server database (via a SqlDataSource object). When the user checks the checkbox, I want the gridview to display only the records where one of the columns is not null. But I've been unable to construct the WHERE clause of the SQLDataSource object correctly. I see that I can hard-code the SqlDataSource object so that the column to be filtered is always NULL or always NOT NULL. But I want this filtering to be more dynamic such that the decision to show all or non-null records happens at run-time. Should I be using two SqlDataSource objects -- one for the NOT NULL condition and one for the "all records" condition? Then when the user checks the checkbox, the gridview would be configured to point to the appropriate SqlDataSource object. (???) Seems like a bit of overhead with that approach. I'm hoping there's a more elegant way to get this done. Please let me know if you need more information. Thanks in advance. Bill
I am using sqldatasource to make a connection to my access database. I have a table with a field called "last_activity" (Date/Time datatype).I want to make a query which shows users/players (the rows in the table) that have been active the last 14 days, so I have to use "last activity" as parameter.But how do I code this? <asp:SqlDataSource runat="server" id="race_a_car_Source" ProviderName="<%$ ConnectionStrings:speedracerConnectionString1.ProviderName %>" ConnectionString="<%$ ConnectionStrings:speedracerConnectionString1 %>" SelectCommand="SELECT * FROM [race_a_car] WHERE ([last_activity] >= ?)"> <SelectParameters> <asp:parameter DefaultValue=" what should I write here? " Name="last_activity" Type="DateTime" /> </SelectParameters> </asp:SqlDataSource>
