In a web site the user has to choose one out of several sql tables to deal with.
He will then be allowed to view the selected table data through a GridView, to insert a new row into and to update a row in the selected table by means of an array of TextBoxes created dynamically on the base of the selected table.
I think it is possible to solve the problem fully by properly configuring at run time an SqlDataSource.
I have solved the issue of data display by declaring in the code behind within the OnPageLoad sub the following:
SqlDataSource1.SelectCommand = "Select * FROM " & selectedTable
While for the GridView1 I have added the selected table columns to the columns collection as follows:
for i=0 to ColumnCount-1 Dim cac As BoundField = New BoundField
I have several Sql Tables, each containing more than 100 fields, on which I need to perform display operation through GridView, Insert and Update through an array of dynamically created and databound TextBoxes. I would like to use a single page to manage all Sql Tables on the basis of the name of the specific one invoked by the user. In order to achieve that, I need to dynamically configure the SqlDataSource Web Control present on the page for each Sql Table. In particular I need to generate at runtime the InsertCommand and the UpdateCommand. On how to do it I have no idea. Federico
When I configure a SQLDataSource control I want the following where clause... WHERE (RegionID = @RegionID AND DistrictID IS NULL AND CampusID IS NULL) OR (DistrictID = @DistrictID AND CampusID IS NULL ) OR(CampusID = @CampusID AND UserRole = 'CampusAdmin') OR (CampusID = @CampusID)but it gets reformated as.... WHERE (RegionID = 'Region10') AND (DistrictID IS NULL) AND (CampusID IS NULL) OR (DistrictID = 2) AND (CampusID IS NULL) OR (CampusID = '999999103') AND (UserRole = 'CampusAdmin') OR (CampusID = '999999103')I don't think these are functionaly equivelant, are they?If not how do I stop the 'wizard' from reformatting the SQL where clause?Thanks
Hello, I have specified a default database in my web.config like this: <dataConfiguration defaultDatabase="scsLocal"/> <connectionStrings> <add name="scsLocal" connectionString="server=DRLSWARTEBRV;database=SCS;uid=******;password=******;" providerName="System.Data.SqlClient"/> </connectionStrings> Now I would like to configure a SQLDataSource control to use this default database, yet it prompts me for a connectionstring. I know I can program it in the code behind file (if I ommit connection string, it will use the default), but then I can make no use of the wizard for configuring the SQLDataSource which would safe me a lot of coding. Is there a way to configure the SQLDataSource to use the default database that is specified in the web.config? Thanks! Veerle
I've just finished configuring the SELECT command for the SqlDataSource in my ASP.NET 2.0 web app. It works fine and runs against a SQL Server 2005 database, using a stored procedure that I've written. So, then I went to configure the SqlDataSource for the UPDATE and INSERT commands, and I've written two SP's for those as well. In the designer the second form of the wizard asks for the Select statement. I've already given that for the SELECT statement in the third form, and I also select the INSERT tab to specify the SP I want to use for inserting data and the UPDATE tab to specify the SP I want to use for updating data. However, there appears to be no way that I can specify what the parameters are supposed to be for anything other than the SELECT command, through the designer. Is that correct, or have I missed something?
Hi! My question is exactly the subject. My Web Form has only a GridView and a DetailsView, there is no SqlDataSource at project time, i create the SqlDataSource at runtime using code like this in the Page_Load event: (I NEED IT TO BE CREATED DYNAMICALLY)1 Dim SQLDS As SqlDataSource = New SqlDataSource() 2 3 SQLDS.ID = "CustomerDataSource" 4 SQLDS.ConnectionString = ConfigurationManager.ConnectionStrings("connectionstring").ConnectionString 5 SQLDS.SelectCommand = "select customerid,companyname,contactname,country from customers" 6 SQLDS.InsertCommand = "insert into customers(customerid,companyname,contactname,country) values(@customerid,@companyname,@contactname,@country)" 7 SQLDS.UpdateCommand = "update customers set companyname=@companyname,contactname=@contactname,country=@country where customerid=@customerid" 8 SQLDS.DeleteCommand = "delete from customers where customerid=@customerid" 9 10 SQLDS.UpdateParameters.Add(New Parameter("companyname")) 11 SQLDS.UpdateParameters.Add(New Parameter("contactname")) 12 SQLDS.UpdateParameters.Add(New Parameter("country")) 13 SQLDS.UpdateParameters.Add(New Parameter("customerid")) 14 15 Page.Controls.Add(SQLDS) 16 17 If Not Page.IsPostBack Then 18 GridView1.DataKeyNames = New String() {"customerid"} 19 GridView1.DataSourceID = SQLDS.ID 20 21 ' ... and so on The DetailsView1 uses the same SqlDataSource to show data, but i could not find a way to synchronize the DetailsView1 with the GridView1 when a record is selected in the GridView1.How can I synchronize the DetailsView?I played with the ControlParameter but i can't find either how to add a ControlParameter in code, is there a way? Every place talking about ControlParameter shows something like this: 1 <asp:SqlDataSource ID="SqlDataSource1" runat="server" 2 ConnectionString="<%$ ConnectionStrings:Pubs %>" 3 SelectCommand="SELECT [au_id], [au_lname], [au_fname], [state] FROM [authors] WHERE [state] = @state"> 4 <SelectParameters> 5 <asp:ControlParameter Name="state" ControlID="DropDownList1" PropertyName="SelectedValue" /> 6 </SelectParameters> 7 </asp:SqlDataSource> 8 Ok. OK. But my SqlDataSource is created dynamically. Any ideas on how to solve this problem?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);
Is there any way to view the queries that a SqlDataSource executes against your chosen data source at runtime? i.e. after all the parameters have been substituted with their values.I'm assigning a number of parameters at runtime to its selectcommand, and i'm getting some exceptions thrown when attempting to update or delete records from a gridview using it (it's a really old Synergy database, which can be quite particular about the structure of queries). It would be a massive help if i could actually see the exact queries the datasource is attempting to use.Cheers for any help
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?
I've got a page that uses an SqlDataSource to populate a simple DataList of inventory. It has been working just fine until yesterday. All of the sudden when I run the page from Visual Studio, it fails to connect to the database with an SqlException: An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server) If I choose to configure the SqlDataSource in the designer, I select the stored procedure and on the last page of the wizard click the Test button and it brings back the results correctly. It turns out, all of the pages in this project are unable to connect at runtime. It's like there's a runtime firewall...
I am having a Data flow task in For each loop which will gets 100 sourcetable names and 100 target table names...
am having a simpleData flow task which trasferes from OLEDBSource to OLEDBDestination. I am repeating the Dataflow task which transfers from sourcetablename extracted from for loop to a destination table var.
The problem am gettting is for the first table it is able to transfer correcly because I did mapping for those tables at design time...but for the next coming sourcetable-desttable (which r having different no of cols,datatypes) its giving Validation failed...and...needs to refresh metadata....
is there any way to refresh the metadata of Data flow task (I set the property of OLEDBSource validate external meta to false then also same error is coming)
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 with SQL Express 2005 with VB as the code behindi have one database and three tables in itfor manipulating each table i am using separate SqlDataSource() is it sufficient to use one SqlDataSource() for manipulating all the three tables ? i am manipulating all the tables in the same page only please help me
We have recently added 1GB Ram to our systems and now we are trying to optimally configure 2GB of RAM for SS6.5 SP4 and we are getting the fllowing error INFREQUENTLY
"Not enough memory for pss allocation"
We have to restart the SQL server/ (recover databases) to get out of this. Since it is a heavily loaded production environment you can imagine the impact!!.
There is a recommendation to reduce the memory or stack size parameters ref:Q160683!!
Question
1. I would like to know if anyone has got around a similar problem, and how. 2. As in NT Virtual Memory Manager AUTOMATICALLY brokers the allocation of physical memory between the requesting applications (e.g. sqlsrvr.exe) and the page file and no application can write directly to RAM, what does the memory parameter in SQL config control? Also then how is the above KB article relevant?
Hi all, I have a question regarding SQL caching. I want to use SQL caching for my pages.I've gone through http://msdn2.microsoft.com/en-us/library/e3w8402y(VS.80).aspx article on msdn, where in step by step procedure is explained for this. But I have a slightly different situation. In the article, we need to configure our application from web.config by following... <!-- caching section group --><caching> <sqlCacheDependency enabled = "true" pollTime = "1000" > <databases> <add name="Northwind" connectionStringName="NorthwindConnectionString1" pollTime = "1000" /> </databases> </sqlCacheDependency></caching>
Where in "NorthwindConnectionString1" is the SQLdatasource connection string. But I'm not using SQLDatasource, and have my own classes to build a connection datasource and get a dataset, and then bind it to my control.So in this case what should I write for the above "connectionstring" attribute. My connection string is in web.config only with a section defined. and in my application i'm reading from web.fing only.The connection section i'm using is as follows... <ConnSettings> <add key="ConnString" value="server=serverName;database=dbName;uid=dbuser;Pwd=password"/> </ConnSettings> I want to know how to configure my sql cache in this situation.
Hi When I try to configure a SQL datasource and I use a Microsoft SQL Server datasource I get a blank drop down list for server name. My aspnetdb is available in SQL Server 2005 and I can log into it through management studio, and the service is started. I am also using Vista Ultimate. Is there any other configuration I need to do for Visual Studio 2008 to see the SQL Server instance? Thanks Kwis
Hi all, I want to configure my SQL mail. I am able to configure SQL mail when i run the SQLAgent and SQL Server services under domain account for which Mail box was created in MS Exchange server. Also it works fine. But i want to Configure SQL mail without using Domain account (dont want to run services under domain account due to security reasons).
Anybody know how to Configure SQL mail without using domain account or is it possible to configure like that? Your suggestions are highly appreciated. thanks in advance. regards..
I am fairly new to SSRS. I want to install SSRS on my local system which has OS of Win XP sp2 (not a server). I have installed SQL Server instance on this box. Can any body tell if i need Win Server 2000 or 2003 OS to install SSRS or can i use Win XP, if so please give some guidelines.
I new to SQL 2005 and have couple of questions regarding configuring the user. Let me start by saying what I need to be achieved.
1. Create a new user which will be user by couple of people to create and execute stored procedures 2. Deny all permissions for this user to access data from certain tables. 3. Configure the role and permissions for this user so that the above two is possible.
I was able to do the first two steps and when the users uses a query, ( select * from 'that table' ) the result in a permissions error. Also that user cannot view the table in SQL manager
The problem now is that that user can create a stored procedure that has the SQL statement Select * from 'that table' and execute the stored procedure to view the table.
The reason I created this user is to allow a team in the company to create stored procedures for Crystal. In theory he should be able to create stored procedures but not get any data from the denied tables. How can I achieve this?
I am trying to resolve this issue for a while now and any help is greatly appreciated.
Hi All,My question is that can I configure SQL MAIL, if my EXCHANGESERVER is on different domain and my SQL is running on differentdomain.Can anyone guide me for this because I googled a lotwithout any result.I am using SQL SERVER 2000 Enterprise Edition with service pack 3.and MICROSOFT EXCHANGE SERVER 2000.ThanksT.V
Can someone help? I am new to Visual basic programming and have down-loaded Visual Basic Express. I followed the instructions for creating my first database but when I got to making the SQL database I got the following error message. " Generating user instances in SQL server is disabled, use sp_configure "user instances enabled" to generate user instances" Where do I find sp_configure and how do I start it.
While configuring reporting services, in the database setup section, I am entering the instance name connecting to, and the user-id details. On applying the changes, i am getting an error that "Using other edition of database for reporting data sources and/or report server databases is not allowed in this version of Reporting services"
wasnt sure which forum to put this in - so i'll attempt here!
i have been trying to setup SQLMail in SQL2000 but have been having a few problems
i have created an Outlook MAPI profile for SQL to use. i have tested the profile and it is sending emails - the profile was created using the correct user which has local admin privileges and also a domain account
i then assigned this profile to SQLMail within Enterprise manager and clicked Test. this completed successfully stating it was able to start & stop a MAPI session.
so i assumed that was all thats needed??
through Query Analyzer i run a simple test
EXEC xp_sendmail 'Craig.Gamble@<mydomain>.com' 'The DB is full'
i get a success message saying SQL mail sent. but it doesnt appear to send anything? i never recieve anything. The sent items does not contain anything and even when checking the actual Exchange server there is no sign of any mail being sent
i also checked the SQL account permissions to make sure it could access the Exchange Server using the
EXEC xp_cmdshell 'NET USE \EnterpriseIPC$' and again this returned success
so now im stumped!
is there anything else i can check to see where or what its supposedly sending??
I have a site with roles/membership working fine on my local machine, but I am having serious trouble deploying it to a shared host. My hosting provides a sql 2000 database, but the user created for it that I use is not dbo. I am errors when I try to access my site such as Could not find stored procedure 'dbo.aspnet_CheckSchemaVersion'The procedure was created under my user, so it's there, but it's full name ia myusername.aspnet_CheckSchemaVersionHow can I tell the asp.net membership/role provider to use my username instead of dbo when it looks for objects? Thanks
hi all i try all date convertion function that u imagen in my asp.net project to insert a date to sqlserver datetime field but always sql server get the data as "1/1/1900 00:00:00" how can i fix that from sql server plz
I've two servers on which there is the SQL 2000 Server and the IIS 6.0.
I installed the SQL Server Client Tools on web server.
And created the nwind as told in booksonline.
I declared the SQLXML extention and allowed it using IIS 6.
But there is a problem with the virtual directory I created using the tool "Configure XML Support in IIS". Web server doesn't realize it as a virtual directory. Even a jpg or a text file is not served by the IIS.
The web server shows the following error.
ERROR: 500 Internal Server Error HResult: 0x800706f4 Source: Microsoft SQL isapi extension Description: Invalid virtual root settings
How can I configure my workstation running WIN95 to used the SQL Enterprise Manager from the server end and other utilities. I have installed the client network utility.
Reason to use enterprise manager because I wanted to perform administrative tasks on my workstation and I've installed SQL SERVER 7.0(Standard version with 5 clients)on the WINNT 4.0 server.
Need some help with configuring the Disk Sub system using Raid 10
1. What shud be the disk configuration to have 4000 inserts per second on a RAID 1+0 ? 2. What is the best way to maintain a Hot Stand by Server for a very high OLTP system ( say for a day Trading Database ).
I need to connect my Local Database from SQL Server to the Web Database. I need to know what all things to be done for completing this process. Some one please please help me with this.
Ad if this information is not enough, please do ask me any question regarding this and iam ready to answer them.
One more thing, iam not that familiar with SQL Server 2000 but i can manage to do things.
I am running Microsoft SQL Server 2000 - 8.00.760 Enterprise Editionon Windows 2003 Enterprise Edition (NT 5.2 Build 3790:) I have 4CPUand 8GB of RAM. I have AWE enabled, /pae /3gb switch is on inboot.ini, In my errorlog I have noticed few times error messages as"The SQL Server cannot obtain a LOCK resource at this time. Rerun yourstatement when there are fewer active users or ask the systemadministrator to check the SQL Server lock and memory configuration.." I haven't modified anything in sp_configure 'locks', so i amassuming SQL server is dynamically managing the lock memory. Runningperfmon for few days I observed that Lock Requests/sec are usually few100,000 most of the times and sometimes goes to 2+ million. However Ihaven't seen Lock Memory (KB) going more than 40MB. So i wonder why amI seeing the Error: 1204 in my sql log ? And any advise on how toprevent it.ThanksAswinee
I am learning dotnet thru Microsoft Official Curriculum and I got stuck up at one point. That is I couldn't configure the required databases according to the sql server I have installed and configured. I created a doctors database and while configuring using the below code I couldn't know what is 'ASPNET' in "SELECT @s = @@servername + 'ASPNET'" Also what is 'webuser'
Can someone explain to me what SQLACCOUNT=<domainuser> is or should be set to? I get an error telling me to specify a built in account or give a username domain and password for each service. How do I specify these things I didnt see those in the 1 msdn page that explains this. I asked this before but I still cant figure it out. Thanks.
I have Visual Studio 2005 Professional installed and SQL Server Express installed. In following along with the Microsoft Visual C# Step by Step book, there is a procedure on page xxi to get access to SQL Server Express. At the 2> prompt, however, when I type in go and then press enter, I get the following message:
Msg 15247 Level 16, State 1, Server MyServerNameSQLEXPRESS, Procedure sp_grantlogin, Line 13
User does not have permission to perform this action.
The computer is my own personal computer, with Window Vista installed and no previous versions of MSDE installed. I also have administrator persmissions. The only guidelines in the procedure regarding what to do in the event of an error message is to ensure that the commands were typed in correctly. What could be causing this message, since I do have administrator permissions?
