I have a page that has about 8 dropdown boxes that need to be populated from sql tables. What is the best way to populate these boxes.
Here is how I have it now
conn = New SqlConnection(ConfigurationManager.AppSettings("SQLString"))
''''''''''''' Fill in DropDownList Status '''''''''''''''''''
strSelect = "SELECT * FROM Requests_Status"
cmdSelect = New SqlCommand(strSelect, conn)
conn.Open()
dtrSearch = cmdSelect.ExecuteReader()
ddlRequestStatus.DataSource = dtrSearch
ddlRequestStatus.DataTextField = "RequestStatusName"
ddlRequestStatus.DataValueField = "RequestStatus"
ddlRequestStatus.DataBind()
ddlRequestStatus.Items.Insert(0, New ListItem("-- Select Below --", -1))
cmdSelect.Cancel()
dtrSearch.Close()
conn.Close()
''''''''''''' Fill in DropDownList Container '''''''''''''''''''
strSelect = "SELECT * FROM Containers"
cmdSelect = New SqlCommand(strSelect, conn)
conn.Open()
dtrSearch = cmdSelect.ExecuteReader()
ddlContainer.DataSource = dtrSearch
ddlContainer.DataTextField = "ContainerName"
ddlContainer.DataValueField = "ContainerID"
ddlContainer.DataBind()
ddlContainer.Items.Insert(0, New ListItem("-- Select Below --", -1))
cmdSelect.Cancel()
dtrSearch.Close()
conn.Close()
'''''''''''''''''''''''''''''
I then repeat the same commands as above for the other 6 dropdowns. This seems like a bad way to have to do all this.
Thanks
Craig
Hello I have a project that uses a large number of MS Data access pages created in Access 2003 and runs on MS SQL2005.
When I am on lets say my client, (first page in a series) data access page and I have completed the fields in the (DAP), I am directing my users to the next step of the registration process by means of a hyperlink to another Data access page in the same web but in a linked or sometimes different table.
I need to pass data entered /created on the first page to the next page and populate the next page with some data from the first page / table. (like staying on the client name and ID when i go to the next page)
I also need the first data access page to open and display a blank or new record. Not an existing record. I will also be looking to creata a drop down box as a record selector.
Any pointers in the right direction would be appreciated. I am some what new to data access pages so a walk through would be nice but anything you got is welcome. Thanks Peter€¦
I have a table that is fairly large, and I need to make a change to one of the columns in the table. Namely I need to change the datatype and rename that column. When I try to save the updated table, I keep getting a timeout error that says.
'eligibility (dbo)' table - Unable to create index 'PK_eligibility'. Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.
Any ideas on how to make the table change more efficient or change the timeout period. I need to keep the existing data in the table. I am using sql server managment studio(2005) connected to a sql server 2000 database.
I have a report with multiple tables. I need to show each tables in different pages. When there is no data for tables/tables , it is coming with the next table which has data. I have given "Add a page break after" option in the tablix but still the tables are coming together when no data available. How can I show it in different page?
Hi - I am pretty new to Reporting Services. I need to create a report where a single result row from the Select Statement populates an entire page of data. The regular grid or Matrix reports don't fit this need. Is there a simple way to do this?
Here is what I have and (somewhat understand). I’m using Visual Web Developer 2005 Express Edition and have setup my application to use form authentication (which automatically creates the ASPNETDB.MDF file with several default tables and views). I’m using the CreateUserWizard which is fine…but I need to collect additional information like (firstname, lastname, address…and on..). What I’ve done. I’ve created a tabled named UserProfile and set UserId as the primary key (uniqueidentifier). I then setup a 1-to-1 relationship between aspnet_Users and UserProfile (which I think is correct). On my UpdateContactInfo.aspx page (where users go to update their personal information) I use a hidden label control (UserValue) to receive the UserId during the page_load event as below:
Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load UserValue.Text = Membership.GetUser().ProviderUserKey().ToString() End Sub Now with the UserID available I need to populate the UserProfile table with the UserId, firstname, lastname, address of the currently logged in user. How can I do this and am I on the right track..?
How can I populate options in a dropdown (combo?) box based on data pulled from a SQL database compared against certain paramaters (if it is not equal to x, equal to y, etc)?
I apologize about the newbie-ness of these questions, but I am a total newbie (I have used VB for a while but this is my first 5 hours with ASP ever).
I just asked how to populate dropdowns, which I have found out how to do, but this question is quite a bit more difficult for me.
I have a SQL database, and in that database is a table, with a few columns. The first columnis a list of user id's, and the second is list of "options".
I want to make it on my webpage so that if the userid matches the column1 data, then it will display a checkbox for the column 2 option. Ie, if its "USERID1,OPTION1" in the SQL table, then if userID = "USERID1" then it will make an checkbox with caption/name "OPTION1". It has to loop through all the rows in the SQL table and make all the checkboxes for these options that match.
I have no idea how to do this, any help would be great.
My ASP.Net app is multilingual and all my translations are stored in seperate MSDE tables, for example, tblEN for English, tblES for Spanish and tblTH for Thai.
When I send the installation files to my clients, I get them to double click on 4 MS DOS batch files that use OSQL to run 4 scripts.....
1. DataBase&Tables.sql - creates the database and tables 2. Logins&Users.sql 3. StoredProcedures.sql 4. Permissions.sql
This worked great before my language table came along to spoil the party and I now need some way of getting the data stored in the language on my server into the table on the client.
If I export the data to a text file and then send it out with the rest of the installation files - what are my options for transferring the data into the table ?
I have the data as below,in which owner is assigning some task to another user.
INPUT #########
OWNER ID TASK_ID TASK_ASSIGNEE
user1 11user2 user112user3 user1 13user4
PRIVILEGE table #########
USER_DETAIL PRIVILEGE_ID
user110 user111 user112 user28 user35 user46
OWNER has one set of privilege details in privilege table. I need to check privilege for user2,user3 and user4 in privilege table, if privilege not matches with the user1 then i want to populate the data output as below
I am trying to populate a dropdownlist from a sql data source in my codebehind using c#. I thought I had the code correct but I keep getting the following error and I am stuck: "Only assignment, call, increment, decrement, and new object expressions can be used as a statement" Here is the code:protected void populateOppNameList(){ SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString);SqlCommand cmd = new SqlCommand("SELECT empname FROM opportunity WHERE (username = " + userName + ")", conn); SqlDataAdapter adapter = new SqlDataAdapter(cmd); try { conn.Open();DataSet ds = new DataSet();DropDownList ddl_OppNames = (DropDownList)FormView1.FindControl("dropdownlist1"); ddl_OppNames.DataSource = ds; ddl_OppNames.DataBind;}catch (Exception e) { } finally {if (conn != null) { conn.Close(); } }
I am creating a scheduling web application. I have managed to insert data into the database. The code is as follow:
Dim insertSQLDatasource As New SqlDataSource() insertSQLDatasource.ConnectionString = ConfigurationManager.ConnectionStrings("ScheduleConnectionString").ToString
Try insertSQLDatasource.Insert() Catch ex As Exception Panel1.Visible = True InsertMsgLabel.Text = ex.Message.ToString Finally insertSQLDatasource = Nothing End Try
Now I am trying to select the values for a particular event. For example, if the user selects an event with id 40, the values that corresponds to that row will be read and filled into the respective controls. I tried the following code, but doesn't work.
Dim sqlConn As SqlConnection = New SqlConnection("ScheduleConnectionString") Dim com As SqlCommand = New SqlCommand("SELECT * FROM Demo_Theatre_DB WHERE ID=@ID", sqlConn) sqlConn.Open() Dim r As SqlDataReader = com.ExecuteReader() While r.Read() StartTimeTextBox.Text = r("StartTime") EndTimeTextBox.Text = r("EndTime") CompanyNameTextBox.Text = r("CompanyName") PurposeTextBox.Text = r("Purpose") AccountManagerTextBox.Text = r("AccountManager") PresenterTextBox.Text = r("Presenter") ColorDDL.SelectedValue = r("ColorCode") StatusRadioButtonList.SelectedValue = r("Status") CommentTextBox.Text = r("Comments") End While
I'm trying to write a stored procedure that will parse XML attributes and populate columns within a DB with the stripped data. I'm a complete novice who prior to this week knew nothing about SQL commands, My understanding at least is that I need to perform a bulk insert.
Example XML file:
<?xml version="1.0" encoding="ISO-8859-1"?> <!DOCTYPE Asset_Collection SYSTEM "Asset_Collection.dtd"> <Asset_Collection> <Collection_Metadata Name="Asset Collection" Description="Random XML Feed Test"
[Code] ....
Table/Columns which need to be inserting into: Table: TABLE_A
I have a table which is already populated with data (Microsoft SQL 2008). I have now created a new column (int) which i want to populate with sequential numbers so that the new column created will serve let me know how many records exist in the table at a glance.
what SQL statement I need to write that will automatically polulate the newly created column with 1,2,,3,4,5 etc so that I can sort of number the records within the table.
I have 50000 records which I need to number and I really dont want to number the column manually via hand editing.
I have 5 or more tables to join to get a particular output which has to be sent to a destination table. In the 5 tables some are inner joins and some are left outer join. I am opting for stored procedure at this point. But I would like to know how can this be done in data flow transformations having multiple souce and merge joins or any other alternates. I tried using merge join, but this does not accept more than two tables.
I saw this simple post which kick started me to use ssis transformations to stored procedures. But I encounter issue. http://www.mssqltips.com/tip.asp?tip=1322 error "The destination component does not have any available inputs for use in creating a path".
I use a ole db to get data from database as source data, and use ole db destination to put data into excel, destination component connect to an excel file . and got below warning:
Warning 10 Validation warning. {9FA859ED-E4C7-4EA1-AE32-11F21CFDC23D} OLE DB Destination [136]: Truncation may occur due to inserting data from data flow column "sMessage" with a length of 2000 to database column "sMessage" with a length of 255. how to populate data length >255 to excel
I have created one reports but all the records are displaying on one page.find a solution to display the records page by page. I created the same report without group so the records are displaying in page by page.
I'm trying to figure this out I have a store procedure that return the userId if a user exists in my table, return 0 otherwise ------------------------------------------------------------------------ Create Procedure spUpdatePasswordByUserId @userName varchar(20), @password varchar(20) AS Begin Declare @userId int Select @userId = (Select userId from userInfo Where userName = @userName and password = @password) if (@userId > 0) return @userId else return 0 ------------------------------------------------------------------ I create a function called UpdatePasswordByUserId in my dataset with the above stored procedure that returns a scalar value. When I preview the data from the table adapter in my dataset, it spits out the right value. But when I call this UpdatepasswordByUserId from an asp.net page, it returns null/blank/0 passport.UserInfoTableAdapters oUserInfo = new UserInfoTableAdapters(); Response.Write("userId: " + oUserInfo.UpdatePasswordByUserId(txtUserName.text, txtPassword.text) ); Do you guys have any idea why?
I am using SQL Server report 2008/2012 (SSRS) and my report viewer contains body content with 3 Row groups. While printing the report, Â data print with blank space and move to continue data to next page.Â
Departure flight : 70 rows First Page           : 42 rows printed Second Page     : 23  rows printed  [ Supposed to be print 28 ,  if the total count of records more than 23 and less than 42 then the page print only 23 records ] Third Page          : 5 rows printed
Departure flight : 42  rows First Page           : 42  rows printed [Report max. record allowed to print 42 rows so if total record is 42 then print perfectly ]
Departure flight : 26 rows First Page           : 23 rows printed [Supposed to be print 26, if the total count of records more than 23 and less than 42 then the page print only 23 records ] Second Page     : 3 rows printed
I have a report where in I have a combination of matrix ,table data regions.
The problem what I am facing is that the data tables don't remain fixed in their position and they tend to move down.
E.g. table 1 and table 2Â are on the same page in design time side by side (right and left)however during the runtime the table1 is pushed down and table2 is at its position .
Now how can I keep them all fixed in their same position. Most of the tables have fixed size rows and some who have high size of rows have been put at the end . What settings we can set?
In case some of you have read my previous posts, you may be aware that I'm writing a webboard application as a replacement for the old one.The old one currently have approximately 50000 topics in the database, each has on average 10 replies (I just check recently. I though it was only 7000 topics).I need to provide paging and sorting feature to the topic list. But I can't just SELECT all of them and let GridView do the paging/sorting, right?I have been using stored procedures to store my SQL statement for several projects now. I know how to deal with the paging feature (ROW_NUMBER), but the sorting requires me to change to change the "ORDER BY" clause.1. Can somebody tell me how to change the ORDER BY clause in the stored procedure(s) at runtime? Or does anyone have other approach? Currently I'm thinking about moving back from store procedures to hard-code SQL statements, and then modify/generate the SQL statement for each paging / sorting. But I've learn that stored procedures give more performance and security.2. According to the situation I provided, is it worth moving from stored procedures to hard-code SQL?I'm also using 3-tier architecture approach + OOP. But I reach a conflict in my thoughts. You see, according to OOP, I'm supposed to create classes that reflect the actual objects in the real-world, right? In my case the classes are "Board, Topic, Reply, ...." According to this and 3-tier approach, I intend to use ObjectDataSource as a bridge between Presentation Logic and Business Logic. But I wonder what my datasource class should return3. Should my data source class return data objects like1st approach[DataObject(True)]pubic class TopicDataSource{ public static Topic[] GetTopicList() { }}or should it return DataSet / DataTable / DataReader like2nd approach [DataObject(True)]public class TopicDataSource{ public static DataTable GetTopicList() {}}Personally I think approach 1 is more OOP and allow for more extendability, but approach 2 might be faster.4. If I go with approach 1, how should I control which property of my data objects is read-only after it's has been inserted/created? Can I just set my data object's property to be readonly? Or do I have to set it at page level (i.e. GridView-> Columns -> BoundField -> ReadOnly=True)? Or do I set it and the page level and write a code to throw an exception in the rare case the application / user try to change it's value? Or else?Please help. These questions slow me down for days now.If there's any concepts that I misunderstood, please tell me. I'm aware that I don't know as much as some of you.I will be extremely grateful to anyone who answer any of my questions.Thanks a lot.PS. For those who think my questions are stupid, I'm very, very sorry that I bother you.
I seem to have somehow got myself into a situation where I'm having to run the following SELECTs, one after another, on a single ASP page. This is not tidy. How can I join them all together so I get a single recordset returned with all my stats in different columns?
SELECT COUNT(*) FROM tblQuiz WHERE [q3] = '5 years +' OR [q3] = '2 - 4 years' SELECT COUNT(*) FROM tblQuiz WHERE [q4] <> '' AND [q4] IS NOT NULL SELECT COUNT(*) FROM tblQuiz WHERE [q5] = 'Unhappy' SELECT COUNT(*) FROM tblQuiz WHERE [q6] = 'Yes' SELECT COUNT(*) FROM tblQuiz WHERE [q7] = 'Yes' SELECT COUNT(*) FROM tblQuiz WHERE [q8] <> '' AND [q8] IS NOT NULL
Hello all,I have a database in SQL Server that should save data from a CRM-likeapplication.The database consists of tables like products, services, customers,partners etc. Problem is that the users should be able to find theseitems on different properties and with or without substring finding(SQL: LIKE). Example: I want the users to be able to find a customer,providing a customerID, but also providing a customername, zipcode orjust a part of those strings.This will result in a lot of queries. I bet there are some nicesolutions to this, since I will not be the first with this situation.If anyone can help, please.Thank you in advance.Regards,Freek Versteijn
Apparently, deleting 7,000,000 records from a table of about 20,000,000 is not advisable. We were able to take orders at 8:00AM, but not at 7:59.
So, what's the best way of going about deleting a large number of records? Pretty basic lookup table, no relationships with other tables, 12 or so address-type fields, 4 or 5 simple indexes. I can take it down for a weekend or night, if needed.
DTS the ones to keep to another table, drop the old and rename the new table? Bulk copy out, truncate and bring back in? DTS to text, truncate and import back? Other ways?
Never worked with such a large table and need a little experienced guidance.
I am planning an application where ~1000 companies will be accessing data. Should I use a key to identify the company and place all data in one table i.e (WHERE company =123) or should the application create company specific tables i.e should I have 1000 small tables with 100 records in each, or one table with 100,000 records?
You know how there are lots of hosted applications out there, many of them provide you with your own database (not shared).
1. If a server has 1K databases on it, will this slow down the server just due to the # of databases? (each user has their own database, but they won't be accessing it that much really).
A seperate database is required for security purposes usually.
I have a strange situation. Performance monitor shows that SQLServer:Transactions Transactions value is 125, but SQL Server Profiler does not show any activity.
I ran sp_who2 and I have a bunch of processes with SUSPENDED status. Would those be counted in Performance monitor?
I used MS SQL Server Management Studio Express to back up my SQL Server 2005 database called "PMDB" on a server in my office to a jump drive. Then, I removed the jump drive from the server and plugged it into my laptop. I then tried using MS SQL Server Management Studio Express to restore "PMDB" to my laptop SQL Server 2005 Express Edition (instance "Primavera") to a database called "pmdb$primavera". I've had several issues:
1. pmdb$primavera is now "locked up" "in the middle of a restore". I can't seem to unlock it even by rebooting my laptop. 2. when I execute the restore (I've used several command sequences...) I get a message that there are additional "families" restore is waiting for. I don't understand that. 3. I tried restoring to a completely new database (in the same instance...), but got the "family" problem. The query just hangs forever. When I cancel it, it hangs the database (see #1). 4. I now have two databases in the "Primavera" instance on my laptop in the "restoring" state. I can't get to either one of them.
I'm desperate. I have a customer presentation on Thursday where I must have the database working. Help!
Here's one of the queries I executed.... though I tweaked items here and there for 3 and 4 above.
restore database pmdb
from disk='f:databasebackupPMDB-BAK.BAK'
with recovery,
move 'pmdb_Dat' to 'C:Program FilesMSSQLPrimaveraMSSQL.1MSSQLDATApmdb$primavera_DAT.MDF',
move 'pmdb_log' to 'C:Program FilesMSSQLPrimaveraMSSQL.1MSSQLDATApmdb$primavera_LOG.LDF'
Every time a transaction log is dumped we see the following message in the log file:
BackupDiskFile:penMedia: Backup device '\s-sqlbkups-1g$myserverlogmy_databasemydatabase_backup_200711071430.trn' failed to open. Operating system error 2(The system cannot find the file specified.).