Hi,Right, I have this problem, and it;s more through lack of understanding vb.net that well more then an actual problem I will out line what I want to do,basically it all revolves around a page that needs to be built when navigated to so it can be easily updated without anyone having to edit the code.Get all the table names from a databaseLoop through each of the results to build a statementNest a 2nd loop to split the returned data from the correct tableBuild a listbox for each table returnedThis is what I have currently, this works but the problem is, if another course is added, someone will need to manually edit the code on the page to add a new code to get the new course hence why I want to create a loop that gets all the data so all someone needs to do is put in the all table the new course name. Please noteI cut this down to just show 2 result but there is about 30 odd. 1 DBConn = New SqlConnection("Server=SD02;Initial Catalog=WhoGetsWhat;Persist Security Info=True;UID=x;Password=xx") 2 'Dim DBDataAdapter As SqlDataAdapter 3 DBDataAdapter = New SqlDataAdapter("Select AOE,ADC, FROM TBL_Role WHERE Title = @ddlTitle", DBConn) 4 DBDataAdapter.SelectCommand.Parameters.Add("@ddlTitle", SqlDbType.NVarChar) 5 DBDataAdapter.SelectCommand.Parameters("@ddlTitle").Value = TitleDropDown.SelectedValue 6 DBDataAdapter.Fill(DSPageData, "Courses") 7 8 'Loop through each record, split at + and place in ListBoxs 9 10 VarDS = DSPageData.Tables("Courses").Rows(0).Item("AOE") 11 Dim VarArray As String() = VarDS.Split("+") 12 Dim i As Integer 13 For i = 0 To VarArray.Length - 1 14 15 Dim li As New ListItem() 16 li.Text = VarArray(i).ToString() 17 li.Value = VarArray(i).ToString() 18 Me.txtAOE.Items.Add(li) 19 Next i 20 21 VarDS = DSPageData.Tables("Courses").Rows(0).Item("ADC") 22 VarArray = Nothing 23 VarArray = VarDS.Split("+") 24 i = Nothing 25 For i = 0 To VarArray.Length - 1 26 27 Dim li As New ListItem() 28 li.Text = VarArray(i).ToString() 29 li.Value = VarArray(i).ToString() 30 Me.txtADC.Items.Add(li) 31 Next i Now here is my pseudo code to what I roughly want to do, hope it makes sense to someone and someone can point me in the correct direction. Please note,I know the split bit works, so at the minute I am just trying to get the loop to get all my courses 1 DBConn = New SqlConnection("Server=SD02;Initial Catalog=WhoGetsWhat;Persist Security Info=True;UID=wgw;PWD=wgwsql;") 2 DBSelect.Connection = DBConn 3 DBSelect.Connection.Open() 4 'Get the row number in the database 5 DBSelect.CommandText = "SELECT COUNT(*) FROM TBL_All" 6 DBResult = DBSelect.ExecuteScalar() 7 DBSelect.Connection.Close() 8 Dim Count = DBResult 9 'Get all the Tables and Keys in the Database's 10 DBDataAdapter = New SqlDataAdapter("SELECT * FROM TBL_All", DBConn) 11 DBDataAdapter.Fill(DSPageData, "All") 12 'declare all loop vars 13 Dim X As Integer 14 Dim Y As Integer 15 Dim i As Integer 16 'Loops through all the tables 17 Dim DSArray As String() = DSPageData.Tables("All").Items() 18 For Y = 0 To Count 19 Dim VarDS As String() = DSPageData.Tables("All").Rows(0).Item(DSArray(Y)) 20 Dim SplitArray As String() = VarDS.Split("+") 21 22 23 For i = 0 To SplitArray.Length - 1 24 Dim Li As New ListItem() 25 Li.Text = SplitArray(i).ToString() 26 Li.Value = SplitArray(i).ToString() 27 Me.txt & DSArray(Y) &.Items.Add(Li) 28 Next i 29 30 Next Y
Hey i have a table A that contains 3 columns : id, entry ,sessionid i want to create a view on this table that will contain - for each sessionid s in A --> select top 5 rows having s as sessionid and ordered by id desc (s can have 1 or 2 or 5 or 300 entries i want to get only the latest 5 rows that correspond to this session) I tried many queries and different combinations i could find one yet to do the following. Can anyone help me plz? Can we have a loop in a view?is it possible?
I have a stored proc I am running, and I would like to create a list of email addresses from a table and put that list into a variable. I did a basic while loop to work on syntax, but now I don't know how to actually get each address added on. Here's how I started it
declare @start int, @testEmail nvarchar(2000)
set @start = 1 set @testEmail = NULL while @start <= (Select count(PADM_Email) from PADM_Emails) Begin --Print @start set @testEmail = @testEmail + (Select distinct PADM_Email from PADM_Emails) + ';' set @start = @start + 1 End
I know that the above is wrong, but I don't know how to get it right. Ideally, I want the @testEmail to look like this:
I need to run a script to create a trigger on 18 tables on 3 databases. The code is identical with the exception of the table and trigger names at the beginning. Does anyone know of a way to create them all with a loop instead of manually replacing the table and trigger names and executing over and over? I tried variables but get an 'Incorrect syntax near '@TriggerName' error.
if exists (select * from sysobjects where id = object_id (N'dbo.tgUsersAudit') and objectproperty (id, N'IsTrigger') = 1) drop trigger dbo.tgUsersAudit go
CREATE TRIGGER tgUsersAudit on tblUsers FOR insert, update, delete AS DECLARE @TableName varchar(128) SET @TableName = tblUsers
..................from here the code is the same for all
I'm new to sql and could do with some help resolving this issue.
My problem is as follows,
I have two tables a BomHeaders table and a BomComponents table which consists of all the components of the boms in the BomHeaders table.
The structure of BOMs means that BOMs reference BOMs within themselves and can potentially go down many levels:
In a simple form it would look like this:
LevelRef: BomA
1component A 1component B 1Bom D 1component C
What i would like to do is potentially create a temporary table which uses the BomReference as a parameter and will loop through the records and bring me back every component from every level
Which would in its simplest form look something like this
LevelRef: BomA
1......component A 1......component B 1......Bom D 2.........Component A 2.........Component C 2.........Bom C 3............Component F 3............Component Z 1......component C
I would like to report against this table on a regular basis for specific BomReferences and although I know some basic SQL this is a little more than at this point in time i'm capable of so any help or advice on the best method of tackling this problem would be greatly appreciated.
also i've created a bit of a diagram just in case my ideas weren't conveyed accurately.
I am developing for a customer and they want a search facility that uses boolean logic and special characters. So they want to be able to add "AND" "OR" "NOT" "*" and "?". And for this to effect the search in the predicted way and ranked. I was wondering if there is any examples of this type of search implemented?Â
I have a table with Year , Account and Amount as fields. I want to
SELECT Year, Account, sum(Amount) AS Amt
FROM GLTable
WHERE Year <= varYear
varYear being a variable which is each year from a query
SELECT Distinct Year FROM GLTable
My thought was that I would need to pass a variable into a select statement which then would be used as the source in my Data Flow Task.
What I have done is to defined two variables as follows
Name: varYear (this will hold the year)
Scope: Package
Data type: String
Name:vSQL (This will hold a SQL statement using the varYear)
Scope: Package
Data type: String
Value: "SELECT Year, Account, sum(Amount) AS Amount FROM GLTable WHERE Year <=" + @[User::varYear]
I've created a SQL Task as follows
Result set: Full Result Set
Connection Type: OLE DB
SQL Statement: SELECT DISTINCT Year FROM GLTable
Result Name: 0
Variable Name: User::varYear
Next I created a For Each Loop container with the following parameters
Enumerator: Foreach ADO Enumerator
ADO Object source Variable: User::varYear
Enumeration Mode: Rows in First Table
I then created a Data Flow Task in the Foreach Loop Container and as the source used OLE DB Source as follows
Data Access Mode: SQL Command from Variable
Variable Name: User::varYear
However this returns a couple of errors "Statement(s) could not be prepared."
and "Incorrect syntax near '='.".
I'm not sure what is wrong or if this is the right way to accomplish what I am trying to do. I got this from another thread "Passing Variables" started 15 Nov 2005.
Got a tough one here for you SQL junkies.I'm working on a website (in ASP) for a national greek/collegeorganization. All it's college chapters have greek chapter names,i.e. Alpha Chapter, Delta Chapter, Delta Iota Chapter, Gamma PhiChapter, ect. ect.I need the SQL to return the chapters in greek alphabet ordering. Seebelow:1. Alpha2. Beta3. Gamma4. Delta5. Epsilon6. Zeta7. Eta8. Theta9. Iota10. Kappa11. Lambda12. Mu13. Nu14. Xi15. Omicron16. Pi17. Rho18. Sigma19. Tau20. Upsilon21. Phi22. Chi23. Psi24. OmegaBasically I need a way to specify this type of presedence for wordingand keep all the results in greek-letter order, so Sigma Upsilon comesbefore Sigma Tau, and so on.Also, to make it even more difficult, these are written words, not theactual greek symbols like α β etc. Everything is stored inthe DB as Alpha Beta Whateva Chapter.Hopefully some of you SQL junkies will be able to help on this one ;)
Which function should I use to remove alphabet in a string?
For example, 60a , 50b, 34s, 34k. I want to remove the suffix alphabet. I tried to use filter but it return an array. i want the return value to be string or int to display.
I have a for each loop(ADO Enumerator) container which executes for each Advertiserid which is coming from database. In for each loop I have to create a new excel file with the advertiser name. So if the loop executes 7 times there should be seven excel spreadsheets with seven advertiser names.
How can i create an excel dynamically in the foreach loop container.
I am looking for the best way in SSIS to do the following. I have an SQL table that for each row in the table I want to take an element from the table do a lookup in a Teredata Table, return information from the teredata source. Use that returned data to do some calculations and create a derived column from my calculations and place the data into the same SQL table that I am parsing through.
Is there an easy way to loop through all rows and remove allinternational alphabet characters from a column in a table, for exampleremove German umlauts "ü" and convert them to a simple "u".Thanks,lq
I have a report which lists out the employees' details. I need A-Z links above the report, On clicking on an alphabet, say "C", should display all the employee details whose name starts with the selected alphabet. In the stored procedure we can accept the character and return back those results. But it is not a drill report and we need the result in the same report. Is there any way so that on clicking each link, the output will be shown in the same report. Any help is appreciated.
In my application I must store over 16000 character in a sql table field . When I split into more than 1 field it gives "unclosed quotation mark" message. How can I store over 16000 characters to sql table field (only one field) with language specific characters?
Hi everybody, I would like to know if there is any property in sql2000 database to separate lowercase characters from uppercase characters. I mean not to take the values €˜child€™ and €˜Child€™ as to be the same. We are transferring our ingres database into sqlserver. In ingres we have these values but we consider them as different values. Can we have it in sqlserver too?
I have a table called Tbltimes in an access database that consists of the following fields:
empnum, empname, Tin, Tout, Thrs
what I would like to do is populate a grid view the a select statement that does the following.
display each empname and empnum in a gridview returning only unique values. this part is easy enough. in addition to these values i would also like to count up all the Thrs for each empname and display that sum in the gridview as well. Below is a little better picture of what I€™m trying to accomplish.
Tbltimes
|empnum | empname | Tin | Tout | Thrs |
| 1 | john | 2:00PM | 3:00PM |1hr |
| 1 | john | 2:00PM | 3:00PM | 1hr |
| 2 | joe | 1:00PM | 6:00PM | 5hr |
GridView1
| 1 | John | 2hrs |
| 2 | Joe | 5hrs |
im using VWD 2005 for this project and im at a loss as to how to accomplish these results. if someone could just point me in the right direction i could find some material and do the reading.
I'm trying to create a proc for granting permission for developer, but I tried many times, still couldn't get successful, someone can help me? The original statement is:
I created a cursor that moves through a table to retrieve a user's name.When I open this cursor, I create a variable to store the fetched name to use within the BEGIN/END statements to create a login, user, and role.
I'm getting an 'incorrect syntax' error at the variable. For example ..
CREATE LOGIN @NAME WITH PASSWORD 'password'
I've done a bit of research online and found that you cannot use variables to create logins and the like. One person suggested a stored procedure or dynamic SQL, whereas another pointed out that you shouldn't use a stored procedure and dynamic SQL is best.
