I am using a sqlDataSource to read my query and to put the rows into a gridview. Now I want to read just a couple of rows from my query based on a maximum number, also defined in the table. I have absolutely no idea how to do that..
for example: I want to view only the 10 most recent subsciptions into my gridview. The subscriptions are stored into tabel Subscriptions which has values id, userid,date. Table Event has a parameter "maximum subscriptions" (which is 10 in my example)
I hope it is clear enough?
Thank you in advance!
BlueiVeinz
I want to retrieve some data in my gridview using a sqldatasource. Here's the idea. My Gridview contains events. A user can subscribe into one of them. When he subscribes, the event must be removed in the gridview. So when there is a subscription from that certain user for that event, it may not appear. Here's my not working code..SELECT Event.EventID,Event.name, Event.LocationID, Event.Date, Event.StatusFROM Shift INNER JOIN Event ON Shift.EventID = Event.EventID INNER JOIN Subscription ON Shift.ShiftID = Subscription.ShiftIDWHERE (Subscription.UserID <> @UserID) Greetz
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, Using VS.NET 2008 Beta2, and SQL Server 2005. I have a gridview bound to a linq data source, and when trying to update a row, I get an exception that no rows were modified. The query generated is: UPDATE [dbo].[package] SET [owner_id] = @p5 WHERE ([package_id] = @p0) AND ([title] = @p1) AND ([directory] = @p2) AND ([owner_id] = @p3) AND ([creation_date] = @p4) -- @p0: Input Int32 (Size = 0; Prec = 0; Scale = 0) [20006] -- @p1: Input String (Size = 22; Prec = 0; Scale = 0) [Visual Studio.NET 2005] -- @p2: Input String (Size = 26; Prec = 0; Scale = 0) [MSI_Visual_Studio.NET_2005] -- @p3: Input Int32 (Size = 0; Prec = 0; Scale = 0) [10000] -- @p4: Input DateTime (Size = 0; Prec = 0; Scale = 0) [11/07/2007 12:00:00 a.m.] -- @p5: Input Int32 (Size = 0; Prec = 0; Scale = 0) [10001] -- Context: SqlProvider(Sql2005) Model: AttributedMetaModel Build: 3.5.20706.1
If I run it manually on sql server, it fails until the directory column is removed. The type is varchar(50), with a uniqueness constraint. However, this is same type as the title column, which doesn't have this problem. Thanks, Jessica
I have a form that has 4 fields to fill in. I have a button that can add these fields to a sql database. I also want to put a button next to the "add" button so you can fill out the same fields and search for those values. Here's what i have so far. I want to select based on the fields, but i'm having trouble with the syntax of the parameters. I also want to add something, so if nothing is filled out in one of those boxes, it retuns back all records for the default value. string strConnection = ConfigurationManager.ConnectionStrings["TimeAccountingConnectionString"].ConnectionString; SqlConnection myConnection = new SqlConnection(strConnection);
String selectCmd = "SELECT * FROM users WHERE firstname = @firstame or lastname = @lastname or office = @office or team = @team"; SqlDataAdapter myCommand = new SqlDataAdapter(selectCmd, myConnection);
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
Hi there,I am not sure how many of you have experienced this problem before but here is how to replicate it: Add a GridView, SqlDataSource and Button to a new projectSet the DataSourceID to the SqlDataSource objectSet AllowPaging to True on the GridViewWithin the Button1_Click sub, change the SqlDataSource.SelectCommand to a new Query e.g SELECT * FROM table WHERE ID > 1Run the project Click on another page, i.e. "3" on the GridView SqlDataSource.SelectCommand is now blank(!), thus Gridview displays nothing. Has anyone else found a solution to this annoying issue? Any help is appreciated.Regards
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.
I have built an Advanced Search page which allows users to select which columns to return (via checkbox) and to enter search criteria for any of the selected columns (into textboxes). I build the SQL statement from the properties of the controls. Works great. My problem is getting my gridview control to play nicely. At first I used a SqlDataReader and bound the gridview to it, thus giving me the ability to run new SQL statements through it (with different columns each time). Worked nicely. But, per Microsoft, sorting can only be done if the gridview is bound to a datasource control like the SqlDataSource. So I wrote the code to handle sorting. No big deal; worked nicely. But I could not adjust the column widths programmatically unless bound to a datasource control like the SqlDataSource. And could not figure out a work around. So, I decided to use the SqlDataSource. Works great. Except, I cannot figure out how to run a new SELECT statement through the SQLDataSource and have the gridview respond accordingly. If I try to return anything other than the exact same columns defined declaratively in the html, it pukes. But I need to be able to return a new selection of columns each time. For example, first time through the user selects columns 1,2,3,4 – the gridview should show those 4 columns. The second time the user selects columns 2,5,7 – the gridview should those 3 columns (and ONLY those 3 columns). Plus support selection and sorting. I am desperate on this. I've burned 2.5 days researching and testing. Does anyone have any suggestions? Thanks, Brad
Hello everyone, I have a view, NAS_vPosition that has a coloumn vLogin_Acting and I want to use the user.identity.name to select the row from this table that matches. So far i have tried: SelectCommand = "Select * FROM NAS_vPosition WHERE vLogin_Acting = ' <%=User.Identity.Name %> ' " with no success. Any help is appreciated
i have a case : i'm using a select statement with SqlCommand and save the results in the SQLDataAdapterand using the data table I post the result to the gridview and show it there....my question is what should I do to append string to the SQL Command??
Hi, I am seeking a hopefully easy solution to spit back an error message when a user receives no results from a SQL server db with no results. My code looks like this What is in bold is the relevant subroutine for this problem I'm having. Partial Class collegedb_Default Inherits System.Web.UI.Page Protected Sub submit_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles submit.Click SqlDataSource1.SelectCommand = "SELECT * FROM [college_db] WHERE [name] like '%" & textbox1.Text & "%'" SqlDataSource1.DataBind() If (SqlDataSource1 = System.DBNull) Then no_match.Text = "Your search returned no results, try looking manually." End If End Sub Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load SqlDataSource1.SelectCommand = "SELECT * FROM [college_db] ORDER BY [name]" SqlDataSource1.DataBind() End Sub Protected Sub reset_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles reset.Click SqlDataSource1.SelectCommand = "SELECT * FROM [college_db] ORDER BY [name]" SqlDataSource1.DataBind() End SubEnd Class I'm probably doing this completely wrong, I'm a .net newb. Any help would be appreciated. Basically I have GridView spitting out info from a db upon Page Load, but i also have a search bar above that. The search function works, but when it returns nothing, I want an error message to be displayed. I have a label setup called "no_match" but I'm getting compiler errors. Also, next to the submit button, I also have another button (Protected sub reset) that I was hoping to be able to return all results back on the page, similar to if a user is just loading the page fresh. I'd think that my logic would be OK, by just repeating the source code from page_load, but that doens't work.The button just does nothing. One final question, unrelated. After I set this default.aspx page up, sorting by number on the bottom of gridview, ie. 1,2,3,4, etc, worked fine. But now that paging feature, as long with the sorting headers, don't work! I do notice on the status bar in the browser, that I receive an error that says, "error on page...and it referers to javascript:_doPostBack('GridView1, etc etc)...I have no clue why this happened. Any help would be appreciated, thanks!
I have a GridView dispalying from a SQLServerDataSource that is using a SQL Select Union statement (like the following):
SELECT FirstName, LastNameFROM MasterUNION ALLSELECT FirstName, LastNameFROM CustomORDER BY LastName, FirstName I am wondering how to create Update and Insert statements for this SQLServerDataSource since the select is actually driving from two different tables (Master and Custom). Any ideas if or how this can be done? Specifically, I want the Custom table to be editable, but not the Master table. Any examples or ideas would be very much appreciated! Thanks, Randy
Dear Gurus,I have table with following entriesTable name = CustomerName Weight------------ -----------Sanjeev 85Sanjeev 75Rajeev 80Rajeev 45Sandy 35Sandy 30Harry 15Harry 45I need a output as followName Weight------------ -----------Sanjeev 85Rajeev 80Sandy 30Harry 45ORName Weight------------ -----------Sanjeev 75Rajeev 45Sandy 35Harry 15i.e. only distinct Name should display with only one value of Weight.I tried with 'group by' on Name column but it shows me all rows.Could anyone help me for above.Thanking in Advance.RegardsSanjeevJoin Bytes!
Copy an existing production database to a new database on the same server; this will be used for development.
Copy an existing table for backup purposes in preparation for deleting a couple fields from the table (precautionary process really).
I've been out of the database swing if you will for a while, just now getting the opportunity to get my feet wet so to speak. I can use either Enterprise Manager or connec to the database via ODBC and make the copies/deletes there, but I'm looking to do this via EM or SQL command.
Any help or direction would be greatly appreciated!
I have made a report in SRS and am not sure of how to do some of the functions I need.
In SQL I can use UPPER to convert to upper case, SRS doesn't seem to like this. The report runs but the line that is to be upper case is missing. If I remove the UPPER word from my query it works fine just displaying in lower case. Am I doing something wrong?
In Excel 2007 (I am converting my report from Excel to SRS) there is a function called NETWORKDAYS (Returns the number of whole working days between start_date and end_date. Working days exclude weekends and any dates identified in holidays. Use NETWORKDAYS to calculate employee benefits that accrue based on the number of days worked during a specific term.) Is there something similar I can use in SRS?
I have a table which is returning inconsistent results when I queryit!In query analyzer:If I do "SELECT * FROM TABLE_NAME" I get no rows returned.If I do "SELECT COL1, COL2 FROM TABLE_NAME" I get 4 rows returned.In Enterprise manager:If I do "return all rows" I get 4 rows returned, and the SQL is listedas being "SELECT * FROM dbo.TABLE_NAME".I've tried adding the "dbo." before my table name in QA, but it seemsto make no difference.I'm using SQL Server 2000, which is apparently 8.00534.Can anyone help me, or give me ideas about what to check?Thanks,Rowland.
Hello - I'm about to rebuild my website on a nice new windows 2k3 server. I was previously using SQL server 7 but I'd like to use the 2005 edition. Here's my first question - is the Express edition 2005 good enough to use on my live web server? Are there limits on the amount of connections at any one time? My second question is this - what's the best way to connect to SQL Server 2005 Express? At the moment I would use something like: Server=.SQLEXPRESS;Integrated Security=True;Database=myDB And then I would set up a COMPUTERNAMEASPNET account under SQL Server 2005 Express. Is that an ok way to connect?? Any ideas? Thanks.
Hello all, this is my first post, I have a couple of basic questions regarding the use of SSIS. I have used SSIS for a couple of years for on off table loads but never implemented production jobs with it so please bear with these questions.
1. When making scripts that connect to one or more databases, is there a way to store the userid and password somewhere besides embedded within the package, such as a web.config file, this would make routine password changes much more manageble.
I guess it was only one question but any help would be greatly appreciated.
I want to create a filtered view according to some information provides. for example I want to create in form 2 check boxes male and female and when either both or one of them is unchecked, the query filters out the unchecked content from view according to the other filtering conditions.
the exact thing I need is: a text box for string looking (in a specific column). male & female checkboxes. two datetime textboxes to specify a range to focus in.
it is very important to me, so if you misunderstood my question, please contact me.
I am using Merge Replication for my scenario (POS). I have made the publication (articles are a set of tables in DB ABC) and its subscription (in same DB and diff tables xyz1, xyz2). I have scheduled the push subscription as run continuously between Dates(e.g current date to 3 days ahead, just for testing). Now, Can I change this schedule? How can I edit/remove the artilce from the above publication?
Hello, I'm a little confused after installing a service pack for SQL 2005 Express. I see SP 1 & 2 for 2005 but I can't see where the SP stands alone for Express. The title says Express 2005 Service Pack 2. Does that mean that I download that version of Express and SP2 is already built in and I just load the new one over the old one or do I uninstall the old one first? I'm not real bold at this point since it took me a long time to get the Express working.
Another question - I downloaded the DTSWizard.exe 2006 msi package and installed it on my XP Pro machine. The add/remove programs window shows it to be installed but it doesn't show up in the folder it is supposed too. I've done searches for the DTSWizard.exe but it's not there, anywhere. Any ideas what I may be doing wrong? I was able to get the DTSWizard to work on my 2000 Pro machine before going to XP Pro this past week. Thanks for any help you may be able to give me. Riley
A couple questions about SQL Server Reporting Services: 1. Can it be used for other data sources besides SQL Server (Oracle db, XML files, via APIs, etc.)? 2. Can it do ad hoc reporting? I like the "universes" that the SAP Business Objects product allows. Does Reporting Services have something like this, to provide a simplified view that non-technical business users can use to create their own reports? Or is Reporting Services just the equivalent to Crystal Reports?
(I wanted to post this in the general MSDN forum for SQL Server, but I can never log in there for some reason.)
I've added a variable to my package but how do you specify its datatype? Is it purely determined by the type of the value that you pass in the Variables.Add(...) method? I've added task and a sequence container to my package - no problem there. My sequence container is referenced in a variable of type DTSContainer but DTSContainer doesn't have an executables collection. So, how do I add a task into my sequence container?
Does SQLServer go to sleep when it's been idle? How can I connect without an error?
I have SQLExpress on a server. Server Name is SqlExpress. I have 2 DBs on that server... Tuna & Guppy. I'm connecting to the DBs from a remote PC which runs my App... an exe created with Visual Studio 2008, Visual Basic.
If I run my App at say 9:30PM, after the server has been idle for several hours, I get a "Can't open connection to Guppy" error, which is my error message when the connection attempt fails. If I quit & relaunch the app, it connects fine, and will continue connecting fine unless you leave the server idle for serveral hours again.
If I'm developing a 2nd app all evening... which connects to a 2nd DB...Tuna, that activity seems to keep the SqlServer awake and then launching the 1st app at 9:30PM connects to Guppy just fine.
I tried adding a "Connection Timeout=30" to my connection string but that did not help.
My connection code is as follows (note the server's name is SQLExpress):
Dim strConnG As String = "Data Source=SQLEXPRESSSQLEXPRESS;Initial Catalog=Guppy;Integrated Security=True;Connection Timeout=30" Using ConnG As New SqlConnection(strConnG) 'open Guppy connection Try ConnG.Open() Catch ex As Exception MsgBox("Can't open connection to guppy") Return False End Try 'Code that does stuff End using
Can anyone tell me how to fix this so I can connect without error, even if the server has been idle?
Greetings! I am writing a proof of concept ASP.net application for my employer and I have run into a couple perplexing issues. First off, In my Page_Load event I have the following code. txOTHrs.Attributes["onBlur"] = "return calculateTotal(this)"; The onblur event handler is coded as follows function calculateTotal(tb) { var regHrs = document.getElementById ("txRegHrs").value; var otHrs = document.getElementById("txOtHrs").value; var rate = document.getElementById("txRate").value; var regAmt = rate * regHrs; var otAmt= rate * ( otHrs * 1.5 ); var total = otAmt + regAmt; document.getElementById("txTotal").value = total; } Everything seems to work fine. The onBlur event is handled and, as I expect the value in the txTotal text box is updated correctly. The problem occurs when I try to access that value in the codebehind page. When ever I look at txTotal.Text in the debugger it is always blank. This has left me scratching my head as I can clearly see a value on the web page. Have I missed some step where I have to notify the server that a value in the control has changed ?
My second question is, I hope a very easy one to answer. In WinForms when I have a dataset I can access the individual rows ( in an untyped dataset ) by speficying dataset.tables["tableName"].Rows[index]. Is there any similar mechanism for accessing the individual rows, and fields in the SqlDataSource object in ASP.net ?
Any help anyone can provide is greatly appreciated!!
I have a SQL Server 2005 database (called BDHSE) in a PC which i call PC1. I have a second PC (PC2) and both are within a network (a WLAN).
What i want is to have access to BDHSE from an application in VB6 (APP1) running in PC2. All the INSERT, DELETE, UPDATE records process is done through APP1.
APP1 ia currently running in PC1 and is to be installed on PC2.
I have these questions:
1. What do i need to install in PC2 since all the INSERT, DELETE, and UPDATE is done using APP1? I guess i only have to install the Microsoft SQL Native Client (with all the prerequisites of course) but i am not sure.
2. In the APP1 made in VB6, do i have to change the connectionstring since i am accesing the database which physically is at PC1 and the APP1 will be used in PC2?
3. Any advice you can give me on doing this will be well received.
Tell me please how to select from a table only rows with different value of one of fields (n_pr field). But I need also to select some other fields where the values can be different for one n_pr field's value and select only first value of them. For getting list of n_pr field's values I can use derived table of query with DISTINCT operator but I don't know how to join with other fields of the table to get only one row for each value of joinning field n_pr. I am using SQL Server 2000 and this DBMS can use TOP operator to select only one row but all the ways I've tryed affect all the query and I get one row at all instead of one for each n_pr field's value. Also I need in this query to connect one more table. So for efficiency it better to do it in one query or in one stored procedure that return a recodset as it needs.
