Loops to create a page!

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 database
Loop through each of the results to build a statement
Nest a 2nd loop to split the returned data from the correct table
Build a listbox for each table returned

This 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 note
I cut this down to just show 2 result but there is about 30 odd.

1DBConn =New SqlConnection("Server=SD02;Initial Catalog=WhoGetsWhat;Persist Security Info=True;UID=x;Password=xx")2'Dim DBDataAdapter As SqlDataAdapter3 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.SelectedValue6 DBDataAdapter.Fill(DSPageData,"Courses")78'Loop through each record, split at + and place in ListBoxs910 VarDS = DSPageData.Tables("Courses").Rows(0).Item("AOE")11Dim VarArrayAs String() = VarDS.Split("+")12Dim iAs Integer13 For i = 0To VarArray.Length - 11415Dim liAs New ListItem()16 li.Text = VarArray(i).ToString()17 li.Value = VarArray(i).ToString()18Me.txtAOE.Items.Add(li)19Next i2021 VarDS = DSPageData.Tables("Courses").Rows(0).Item("ADC")22 VarArray =Nothing23 VarArray = VarDS.Split("+")24 i =Nothing25 For i = 0To VarArray.Length - 12627Dim liAs New ListItem()28 li.Text = VarArray(i).ToString()29 li.Value = VarArray(i).ToString()30Me.txtADC.Items.Add(li)31Next 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

1DBConn =New SqlConnection("Server=SD02;Initial Catalog=WhoGetsWhat;Persist Security Info=True;UID=wgw;PWD=wgwsql;")2 DBSelect.Connection = DBConn3 DBSelect.Connection.Open()4'Get the row number in the database5 DBSelect.CommandText ="SELECT COUNT(*) FROM TBL_All"6 DBResult = DBSelect.ExecuteScalar()7 DBSelect.Connection.Close()8Dim Count = DBResult9'Get all the Tables and Keys in the Database's10 DBDataAdapter =New SqlDataAdapter("SELECT * FROM TBL_All", DBConn)11 DBDataAdapter.Fill(DSPageData,"All")12'declare all loop vars13Dim XAs Integer14 Dim YAs Integer15 Dim iAs Integer16'Loops through all the tables17Dim DSArrayAs String() = DSPageData.Tables("All").Items()18For Y = 0To Count19Dim VarDSAs String() = DSPageData.Tables("All").Rows(0).Item(DSArray(Y))20Dim SplitArrayAs String() = VarDS.Split("+")212223For i = 0To SplitArray.Length - 124Dim LiAs New ListItem()25 Li.Text = SplitArray(i).ToString()26 Li.Value = SplitArray(i).ToString()27Me.txt & DSArray(Y) &.Items.Add(Li)28Next i2930Next Y

I have just realised, in each loop for each course I will need to add the select statement so I think I need to add

DBDataAdapter = New SqlDataAdapter("SELECT " & Dr & "FROM TBL_" & DSArray(Y), DBConn)
DBDataAdapter.Fill(DSPageData, "Courses")

Into it

|||

ok think I am making some head way and I think I have logically what I want.

For every row of column TName in TBL_ALL, assign to var Dim TName
For every row of column PKey in TBL_ALL, assign to var Dim Pkey
For every row create statement "SELECT '" & Pkey & "' FROM '" & TName " "'")

Once I have made the loop that creates all of them select statements, I can run a loop to execute them,
and then a loop to assign the values to listboxes, but first step at a time.

Here is what I have so far, it doesn't work and I know its not right, but maybe someone can see where I am comming from
point my in a better direction. Please also note, the response.write bits are to check when my code i have working is working
but they are not needed

Thanks
Chris

1DBConn =New SqlConnection("Server=SD02;Initial Catalog=WhoGetsWhat;Persist Security Info=True;UID=x;PWD=xx;")2 DBSelect.Connection = DBConn3 DBSelect.Connection.Open()4'Get the row number in the database5 DBSelect.CommandText ="SELECT COUNT(*) FROM TBL_All"6 DBResult = DBSelect.ExecuteScalar()7 DBSelect.Connection.Close()8Dim Count = DBResult9'Get all the Tables and Keys in the Database's10 DBDataAdapter =New SqlDataAdapter("SELECT TName FROM TBL_All", DBConn)11 DBDataAdapter.Fill(DSPageData,"TName")12 DBDataAdapter =New SqlDataAdapter("SELECT PKey FROM TBL_All", DBConn)13 DBDataAdapter.Fill(DSPageData,"PKey")14'declare all loop vars1516Dim DrAs DataRow17Dim DcAs DataColumn18'Loops through all the tables1920 'For Each Dr In DSPageData.Tables("All").Rows21 'For Each Dc In DSPageData.Tables("All").Columns22 'Dim Column As DataColumnCollection = DSPageData.Tables("All").Columns23 'Dim Table As String = (Dr(Dc.ColumnName.ToString))24 'Response.Write(Table)25 'DBConn = New SqlConnection("Server=SD02;Initial Catalog=WhoGetsWhat;UID=wgw;PWD=wgwsql")26 'DBSelect.Connection = DBConn27 'DBSelect.Connection.Open()28 'DBSelect.CommandText = "SELECT '" & Dr(Dc.ColumnName.ToString) & "' FROM ('" & Table & "')"29 'DBSelect.ExecuteNonQuery()30 'Next Dc31 'Next Dr3233Dim Search()As SqlDataAdapter34Dim PKeyAs String35 Dim TNameAs String36 Dim ResultAs String()37For Each DrIn DSPageData.Tables("TName").Rows38For Each DcIn DSPageData.Tables("TName").Columns39'For i = 0 To Count40 PKey = (Dr(Dc.ColumnName.ToString))41 Response.Write(PKey)42 TName = (Dr(Dc.ColumnName.ToString))43 Response.Write(TName)44'DBSelect.CommandText = "SELECT '" & PKey & "' FROM '" & TName & "'"45 'DBResult = DBSelect.ExecuteNonQuery46 'Result(i) = DBResult47 'Next48Next49 Next50 For Each DrIn DSPageData.Tables("PKey").Rows51For Each DcIn DSPageData.Tables("PKey").Columns52'For i = 0 To Count53 PKey = (Dr(Dc.ColumnName.ToString))54 Response.Write(PKey)55 TName = (Dr(Dc.ColumnName.ToString))56 Response.Write(TName)57'DBSelect.CommandText = "SELECT '" & PKey & "' FROM '" & TName & "'"58 'DBResult = DBSelect.ExecuteNonQuery59 'Result(i) = DBResult60 'Next61Next62 Next63 End Sub

|||

maybe I am totally confusing everyone lol? I am so lost :(

Lookup cachetype = none question

HI, I use a lookup transform on one of my dataflow. My data look a bit like this (the actual data is more complicated, kit is a combination of type 1 - description and type 2 - code):

ID Code Description
-- -
1 AAA PRODUCT1
2 AAA PRODUCT2
3 AAA PRODUCT3
4 BBB PRODUCT4

The problem is simple: I would like to insert ID 1 and update it with subsequent rows that have the same code.

If the lookup transform finds a match, the row is updated, else, the row is inserted (using the error path of the lookup) via an OLE_DB command. The lookup cachetype is set to none. My problem is all rows are inserted. But if I use a second lookup that gives me the ID using the Code column, the second lookup sees the inserted data.

My question is why the second lookup is able to find out the inserted data while the first one cannot? The SCD wizard cannot resolve this either. I resolved this by using an asynchronous script component and manage the logic in there. But, still, a lookup with no cache (roud trip to the DB every time) should be able to do the job.

Thank you,
Ccote

Generally this is because the dataflow works on buffers not rows. All the rows in a buffer are processed by a component before the rows are passed on to the next component. So the 1st lookup doesn't find the data because it most likely hasn't actually been inserted yet, while the 2nd one finds it because the data has actually been inserted.

HTH,

Matt

Looking for tool to export data/definitions for later SQL setup.

Any plugable options out there that I can use to export database/table setup
and actual data to xml?
Best yet, would be a .Net class that I could use to control the process for
creating a setup file and then using my own install program to import the
defs/data.
Thanks in Advance,
Donald Adams
"Donald Adams" <BDA_2003@.hotmail.com> wrote in message
news:O0rFG$CIEHA.520@.tk2msftngp13.phx.gbl...
> Any plugable options out there that I can use to export database/table
setup
> and actual data to xml?
> Best yet, would be a .Net class that I could use to control the process
for
> creating a setup file and then using my own install program to import the
> defs/data.
Don't know of any offhand, but you could use SQLXML to export table
definitions from SQL Server (and then you could deserialize them into .Net
classes).
Bryant