creating a group unique ID

Use NEWID() to create unique IDs for each user.

http://msdn.microsoft.com/en-us/library/ms190348.aspx

You can then have a separate column in the database table that will hold a common ID for each group which you can create using NEWID().

create a new column in you table for groups
simple
 
 

Im trying to assimilate the concept and how to apply it.

I have a table called TBableA
I have cols.
RegistrationID(primary)
firstname
Lastname
etc...
.. I assume I have to add a column here for the GroupID which is a foreign key
GroupID
CourseID

But then somehow I think there should be another  table to be created  to have the groupid as primary key

TableB
GroupID
Cost
etc..

Im trying to figure out how to apply it in vb.net

Is that correct?

groupID doesnt need to be a foreign Key
you can just place it in TbableA

unless the groupID has a relationship with RegistrationID (which it  looks like it doesn't)
there is no need to make another table

How would the sp look like  since I will be calling this to create the groupID?

sp ?

can it be created directly from vb.net code?

you dont even need to do that just open the table in server explorer inside visual studio  and add a new column :)

I will need to store the value as a variable so there is programming involve.

Here's how a SQL novice like me would do it.

A new group is entered via your web form. First step in code-behind after form submission is create a new group ID in your group table using NEWID(). Then immediately SELECT the newly created id value back into a variable. Then proceed to add the individuals to the registration table, each with a NEWID() and the group column filled in with the variable value for the group ID.

When you have to retrieve group data, you can use a SELECT statement that says something like:

SELECT registrationID, firstname, lastname FROM TableA WHERE groupID=@groupID

to select particular groups.

Do you have the vb.code to do what you envision?

I have tons of VB code for reading and writing database tables. I'll have to dig through and see what I can find that is relevant.

Thanks.I just found a vb.net function to do that that will take the GUID and store it as a string,but id like to use the Newid() But I'm having difficulty store it as a variable in vb.net. The link found in this thread on Newid() is excellent but i have to bring the value as a variable.

I modified some code to more closely resemble what you are doing. It demonstrates two ways for create unique IDs in tables. For the group table (TableB) I'm using an IDENTITY INT that auto increments in the table. Notice when I insert a new group I can return the IDENTITY at the same time to use in the registrant table (TableA). For the Registrants table (TableA) I use the NEWID() to create unique IDs.

One code block is the Page_Load Sub for my form and it's there to simulate adding a group. The other code block is my Sql functions class.

Hope that helps.

Partial Public Class WebForm3
    Inherits System.Web.UI.Page

    Dim sqlFunctions As New SqlFunctions

    Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
        'This is really crude, but it just simulates a new group of registrants returned from the web form
        Dim returnedId As String = sqlFunctions.InsertGroup("$927.00") 'a new group is registering, add a new group to the group table (TableB) and return the groupId
        Dim returnedError As String = String.Empty
        If returnedId.StartsWith("Error:") Then
            Me.returnLbl.Text = returnedId   'return any sql errors to the page
        Else  'insert the group of new registrants using the new groupId for the "group" column in TableA
            returnedError = sqlFunctions.InsertRegistrant("John", "Doe", CType(returnedId, Integer))
            If returnedError = "" Then
                returnedError = sqlFunctions.InsertRegistrant("Jane", "Doe", CType(returnedId, Integer))
                If returnedError = "" Then
                    returnedError = sqlFunctions.InsertRegistrant("Fred", "Smith", CType(returnedId, Integer))
                Else
                    Me.returnLbl.Text = returnedError   'return any sql errors to the page
                End If
            Else
                Me.returnLbl.Text = returnedError   'return any sql errors to the page
            End If
        End If
    End Sub
End Class

Select allOpen in new window

Imports System.Data.SqlTypes

Public Class SqlFunctions
    Protected WithEvents SqlDataAdapter1 As System.Data.SqlClient.SqlDataAdapter
    Protected WithEvents SqlInsertGroup As System.Data.SqlClient.SqlCommand
    Protected WithEvents SqlInsertRegistrant As System.Data.SqlClient.SqlCommand
    Protected WithEvents SqlConnection1 As System.Data.SqlClient.SqlConnection
    Dim SqlConnectionStr As String = System.Configuration.ConfigurationManager.ConnectionStrings("TestStuffConnectionString").ConnectionString

    Private Sub InitializeGroupEntry()
        Me.SqlDataAdapter1 = New System.Data.SqlClient.SqlDataAdapter
        Me.SqlConnection1 = New System.Data.SqlClient.SqlConnection
        Me.SqlInsertGroup = New System.Data.SqlClient.SqlCommand
        Me.SqlConnection1.ConnectionString = Me.SqlConnectionStr
        Me.SqlInsertGroup.Connection = Me.SqlConnection1
        Me.SqlDataAdapter1.TableMappings.AddRange(New System.Data.Common.DataTableMapping() {New System.Data.Common.DataTableMapping("Table", "TableB", New System.Data.Common.DataColumnMapping() {New System.Data.Common.DataColumnMapping("groupId", "groupId"), New System.Data.Common.DataColumnMapping("cost", "cost")})})
        Me.SqlInsertGroup.Parameters.Add(New System.Data.SqlClient.SqlParameter("@cost", System.Data.SqlDbType.VarChar, 50, "cost"))
    End Sub

    Private Sub InitializeRegistrantEntry()
        Me.SqlDataAdapter1 = New System.Data.SqlClient.SqlDataAdapter
        Me.SqlConnection1 = New System.Data.SqlClient.SqlConnection
        Me.SqlInsertRegistrant = New System.Data.SqlClient.SqlCommand
        Me.SqlInsertRegistrant.CommandText = "INSERT INTO TableA(registrationId, firstname, lastname, [group]) " & _
        "VALUES (NEWID(), @firstname, @lastname, @group)"
        Me.SqlConnection1.ConnectionString = Me.SqlConnectionStr
        Me.SqlInsertRegistrant.Connection = Me.SqlConnection1
        Me.SqlDataAdapter1.TableMappings.AddRange(New System.Data.Common.DataTableMapping() {New System.Data.Common.DataTableMapping("Table", "TableA", New System.Data.Common.DataColumnMapping() {New System.Data.Common.DataColumnMapping("registrationId", "registrationId"), New System.Data.Common.DataColumnMapping("firstname", "firstname"), New System.Data.Common.DataColumnMapping("lastname", "lastname")})})
        'Me.SqlInsertRegistrant.Parameters.Add(New System.Data.SqlClient.SqlParameter("@registrationId", System.Data.SqlDbType.DateTime, 50, "registrationId"))
        Me.SqlInsertRegistrant.Parameters.Add(New System.Data.SqlClient.SqlParameter("@firstname", System.Data.SqlDbType.VarChar, 50, "firstname"))
        Me.SqlInsertRegistrant.Parameters.Add(New System.Data.SqlClient.SqlParameter("@lastname", System.Data.SqlDbType.VarChar, 50, "lastname"))
        Me.SqlInsertRegistrant.Parameters.Add(New System.Data.SqlClient.SqlParameter("@group", System.Data.SqlDbType.Int))
    End Sub

    Function InsertGroup(ByVal cost As String) As String
        Me.InitializeGroupEntry()
        Dim errorStr As String = ""
        Dim query1 As String = "INSERT INTO TableB (cost) VALUES (@cost);SELECT SCOPE_IDENTITY()"
        With Me.SqlInsertGroup
            .CommandTimeout = 100
            .CommandType = CommandType.Text
            .Parameters("@cost").Value = cost
            .CommandText = query1
        End With
        Try
            If SqlConnection1.State = Data.ConnectionState.Closed Then
                SqlConnection1.Open()
            End If
            errorStr = CType(Me.SqlInsertGroup.ExecuteScalar(), String)
            SqlConnection1.Close()
        Catch ex As Exception
            If SqlConnection1.State = Data.ConnectionState.Open Then
                SqlConnection1.Close()
            End If
            errorStr = "Error: " & ex.ToString()
        End Try
        Return errorStr
    End Function

    Function InsertRegistrant(ByVal firstname As String, ByVal lastname As String, ByVal group As Integer) As String
        Me.InitializeRegistrantEntry()
        Dim errorStr As String = ""
        With Me.SqlInsertRegistrant
            .CommandTimeout = 100
            .CommandType = CommandType.Text
            .Parameters("@firstname").Value = firstname
            .Parameters("@lastname").Value = lastname
            .Parameters("@group").Value = group
        End With
        Try
            If SqlConnection1.State = Data.ConnectionState.Closed Then
                SqlConnection1.Open()
            End If
            Me.SqlInsertRegistrant.ExecuteScalar()
            SqlConnection1.Close()
        Catch ex As Exception
            If SqlConnection1.State = Data.ConnectionState.Open Then
                SqlConnection1.Close()
            End If
            errorStr = "Error: " & ex.ToString()
        End Try
        Return errorStr
    End Function

    
End Class

Select allOpen in new window

Is it possible to add a column in TABLEA a PersonID field. This would be unique to the individual person although he is part of the group?
Coz right now the GroupID is a foreigh key in TABLEA. In TABLEA like wise , the RegistrationID is the primary key. The only way to trace history of the individual member is thru the RegistrationID.
From the Registration ID, you will get his name,address,email and all personal data of teh individual.
Isn't that not a good design coz we mix the Registration data-classes,workshop he picked with his personal data. I think the personal data should be another table. Is it possible that when registering individually the members in the form , the data will first be inserted in the 3rd Tablle but immediatlely grab the PersonId and insert it to the registration tablA.
So 3 things happen
Create a groupID and leave it in standby status(TableB) while waiting for the personID created
Create a personID in Person table(3rd table)

Get the groupId and personID and insert it to TableA.

Let me know if you have some techniques/strategies cleaner than what I thought.

thanks

Here's the simple premise I use for database design and I think it's fairly universal. The ideal relationship to have in a database between tables is a one-to-many relationship. If you have two tables related to each other on a one-to-one basis, you have too many tables. Conversely, if you have two tables with a many-to-many relationship, you need to add tables until you get back to a one-to-many relationship between all tables.

Assume you have a table of groups where one group has a relationship to many individuals in another table. Having a third table for registration would be redundant IF either every group needs to be registered (one-to-one) or every individual needs to be registered (one-to-one again).

If, on the other hand, the situation is, for example, that every individual needs to be registered, BUT they can be registered with more than one workshop, now you need more tables.  Now you have a one-to-many relationship between individuals and the workshops they signed up for. So you create a registration table that lists all the registrations to workshops, one row for each registration with the individual's id, and the workshop they signed up for. At this point, you could have a "workshops" table that contains the details of each workshop and a workshop id which you could plug in to the registration table in the workshop column. Naturally, an individual's id could appear multiple times in the registration table if they signed up for more than one workshop, but each registration would be unique. If it's the group that signs up for workshops instead of individuals, then substitute "group" for "individual" in the above example.

I hope that clears things up for you.

>>>If it's the group that signs up for workshops instead of individuals, then substitute "group" for "individual" in the above example.



It's possible that an individual will register himself. Nevertheless he will get a groupID with this design.
The individuals will give the person entering the data in the registration form their choices  for workshops. The person entering the data creates a group registration with possibly different classes/workshops for different individuals. They will just give him their details/choices.
The 'group' here is defined as one person entering for many.

My question : Can the groupID,RegistrationID,PersonID(this one is ??) be in one table?

I don't see a problem with an individual who signs himself up also getting a group id.

If an individual signs up a group of individuals and those individuals can have a varying number and choice of workshops then the registration table should be by individual to capture the variety, but each row should also contain a group id to cross-reference to the group table. This would cover a scenario where you might want to select for example all individuals from a certain group who signed up for a particular workshop. It means that both the individuals data table AND the registration table contain the group id from the group table. I don't see a problem with that.

You can force integrity in the database by creating dependent relationships between primay keys and foreign keys. I personally haven't found much real world application in my line of work and it does complicate error handling and coding.

Any comment about the Person tbl?
So 3 tables
Group
Registration
Person

any comment on this design.
requires complicated coding th

hit button too fast .also with person table,one can trace historical data

I agree with having a person table (I called it individuals table) and I recommend a fourth table to hold workshops and associated details.

The only design part that would add a degree of complexity is if you were to force relationships between primary and foreign keys, but doing that is optional. Otherwise you can use my code as a starting point and add on to include reading and writing to other tables. The code will be the same for each read or write, only the variables change. Just be sure you have your error handling properly set up.

My only objection in placing aPeron table is the nature of the environment. If it is a network app,then you can get historical and other relationships using the Person table but since it is a web environment,a person table with personid on it may not provide any links to previous years or future years for using the app. Unless an email address is required, then it is possible to build historical data using that field but that is not consistent either. Some may or may not have email addreses..

How other web apps do it in tracing previous appearances/attendance , I have no idea what are the basis or ways of tracing is used- Do you have ideas?
Im about limiting it to 2 tables as initially planned without a person table.

BTW-WHY UNIQUEIDENTIFIER TYPE FOR Registrant table and not Indentity int

Does using the app require a log in? If so, you can track history by log in date/time, what IP address used, what browser, what resources accessed, etc. This would be another table.

Do you think you are saving yourself some trouble by limiting the number of tables? Don't do it for that reason. You need as many tables as the app requires, no less, no more. SQL Server doesn't care how many tables you create. We are talking small potatoes here, so I don't think performance is an issue.

This explains Uniqueidentifier vs. IDENTITY better than I could.

http://www.sqlteam.com/article/uniqueidentifier-vs-identity

no login page provided

iI was looking at your code and I thought of doing it as Stored proc just to not expose any sql in the webpage in case an error occurs or is forced to occur for security. Do you have any ideas or comment on this or maybe Im wrong.

Tommy,thanks for your replies

Anytime you are collecting information from the web, you leave yourself open to attack. Whether you use stored procedures or not, the incoming data has to be scrubbed and parsed to eliminate injection attacks. You still have to send the publicly collected data to the stored procedure for processing anyway.

I see experts on this forum claim that if you use stored procedures, there's no way you can be victimized. Not true. Just do a little research on that and you will see. I'm not saying security will not improve with stored procedures, just that you should not depend on them as your only line of defense. I have a few live websites out there that collect data. One site was hacked right after launch using a SQL injection in the query string of the url. I fixed it by using more stringent parsing of the query strings, not by switching to stored procedures. The site has been fine now for 4 years.

A properly set up web server never exposes the server side code or the web.config file to the end user. Further, unlike a server side language like PHP, with asp.net, the code is compiled into a .dll that is unreadable. The raw VB.NET code you are writing does not get uploaded to the server at all. The main root of attack is most often through the web form or the url string where malicious code can be entered instead of legitimate data, not by trying to access the server side code.

How about encrypting the URL string?

Url encrypting is definitely a valid way to slow down hackers if done correctly, i.e. use a salt value, change the salt value often (which unfortunately screws up the visitor's browser history), include a time limit in the hashed value. I've never used it in any of my websites because I don't like the ugly url strings produced and it complicates server side coding because you obviously need to encrypt all urls server side while building your pages. Understand that encrypting urls is a method for securing and limiting requests via "get" for web resources. There's no practical way that I know of to encrypt user input entered in a form that is submitted via "post", so I don't see how that helps in your case. If you are planning to use url parameters and the parameters are simple, just check the length to validate and prevent SQL injection. Example: You have an url like http://www,mypage.aspx?ID=1234. When that url is requested, check server side that the id value is not more than four characters (because you know that your IDs are never more than four characters long) before granting the request. A hacker would not be able to add a SQL statement to the parameter with just four characters to work with.

Btw,in looking at ur code and planning to test in my scenario.i created them as individual classes

So I have to call the class something like
Dim pdata as new hlsqlginctions

Why is the in page load? Can it be
better in a button click event?

What I mean is just encrypt the value that is passed re: ID

I don't think placing the code to fire right away on page load is not a good idea.
The reason is that that the cost varies depending the number of people is being registererd.

for example:
If # of people is 1 - 4    cost is  $12 per person
 5 - 8   cost is  $8/person

over 8 is $6/person

Im thinking of putting a form with a question or something askign for an input but the cost will be automatically calculated.

So there should be a a textbox for # of people and a button to confirm it.
When that button is clicked, behind the scene, the groupID is generated which is stored in a variable
Then the form to input the firstname,lastname,etc... f displays
At the footer of the form will be another button to say register..
This button will  redirect it to a page that will display all the names he inputted  which could be a grid or listview with ability to edit or cancel(not sure of this?)

In that confirmation page, there will be a input asking for payment information like card or check
Nothing will be committed to the tables if this transaction did not validate.

What do you think about this idea? Would it work? How would you assess it?

As I said when I posted the code, it's only in Page_Load "to simulate adding a group". I was not suggestion that it should go there in you project. The important point was the SQL read/write code.

People get upset when they enter information into a web form only to have it lost in a failed postback and have to type it all over again.

I would handle it like this: A user indicates they want to enter a group ( your choice on how to do that). You do a postback to generate the group I'd which you save to Session state. On the return, you present a form for entering the first individual's personal data and a "Add Another" button and a "Done" button and a "Cancel" button (always). When they click "Add Another", do a postback and record the data to Session state. Upon return, hard code the first individual's data to the page so the user can see that it has been added, clear the form to allow the second individual's data. Repeat the process until the user clicks "Done" in which case you record the last individual's data, count the number of persons in Session state so you can calculate the final cost. Upon return, present all individual's data, final price, and a "Submit" button so the user can finalize the deal. On the "Submit" postback move all data from Session state to the database and present a confirmation page to the user.

1.Do you have good example for saving in session or viewstate?
2. The question 'how many are you registering in the group? If he enters 5,it should be validated that the # of people is 5 and removed the ability to add another

1.)  Session("key") = value
      ViewState("key") = value

I would use Session state not ViewState because ViewState rides back and forth with postbacks and what you are doing, the overhead can grow to levels that can affect performance.

2.) Are there restrictions as to the size a group can be? If so then give them a dropdown with the possible group sizes so they are limited to those choices. If the group can be any size UP TO a certain limit, then let them add new members until they reach the limit, then provide an alert that says "You reached the limit". Why bother asking the user what size their group is at the outset. If they make a mistake on that step, they would need to start over. Just provide a "Done" button and you will know then how many are in the group.

BTW, I like the idea of providing an edit button next to each members personal data during the process. Always assume people will make mistakes and give them a way to correct the mistakes before finalizing.

I think so. Limiting how many they want in the beginning would involved a ton of validation.

>>>You do a postback to generate the group I'd which you save to Session state. On the return, you present a form for entering the first individual's personal data and a "Add Another" button and a "Done" button and a "Cancel" button (always). When they click "Add Another", do a postback and record the data to Session state. Upon return, hard code the first individual's data to the page so the user can see that it has been added, clear the form to allow the second individual's data. Repeat the process until the user clicks "Done" in which case you record the last individual's data, count the number of persons in Session state so you can calculate the final cost.

First, Here, I was thinking of displaying the recently entered info  in a gridview or Listview when it postback because one can add templates for edit,cancel,etc.

Can you show the code to count the session states as you mentioned above?

Listview would be great. In fact, you can add an InsertItemtemplate and use that to add new members of a group rather than create a separate form.

To count the number of members in the group, just create a separate Session variable and increment for each new member, decrement for deleted members. There's also the items.count property of the ListView.

Can you share me code how the increment,decrement session variable implementation? Thanks

What about if a user deciided to cancel or discontinuing  his decision to register and chosing to cancel or just leave the page but already has began the process meaning a groupid was already created??
What would happen to the groupID that will eventually be wasted?

Session("memberCount") = Session("memberCount") + 1  'increment
Session("memberCount") = Session("memberCount") - 1  'decrement

When you create a new row that has an IDENTITY column in a Sql table and then you delete it, the ID automatically assigned when the row was created is now lost, leaving a gap in the numbering. This does not present a problem as long as you never assume that the groupId numbers are consecutive or that they match up with the row indexes in the table. Put another way, never assume that the highest number in the groupID column is the number of groups you have. Instead use COUNT(*) to get the number of groups.

So, when the user cancels the session, just do some clean up and delete the row from the database.

So there will be 2 session state variables?. When I create a groupid,Session("groupid")=groupid then immediately I store that to another variable,session("membercount"),them as I add another member,it would be I=session("membercount")+ 1,then in the "done" button,summarize the count.i will pass that count in a query  string as URL that will decode to the listview in that new page,displaying-first and,lStnams ,event registering,etc. There will be edit iremtemplate and below it is table showing cost and a sunny button..

Can't quite make out what your saying. It sounds right, but let me put it my way and see if you agree

The user indicates they want to add a group by clicking something to start the process --> postback--> create groupId (add a row to groups table in the database and retrieve IDENTITY value)--> save the groupID to Session("groupID")--> return a page to user with a ListView control that has an InsertItemTemplate which allows user to start entering member personal data and click "Insert"--> postback--> create a datatable with columns that match the personal information table in the database, including the groupID column--> add a row to the datatable with the new member's information using Session("groupID") to fill the groupID column--> save the datatable to Session("newGroupMembersDT")--> bind the datatable to the ListView--> return to the user with the updated ListView which now includes an empty "Insert" row and the row showing the member already added which will have an "Edit" and "Delete" button.

At this point the user can continue to add new members to the group. Each time they click "Insert", you will copy the Session("newGroupMembersDT") datatable to a new datatable instance, add a row with the new member's information, and re-save the datatable back to Session("newGroupMembersDT").

Of course if the user clicks "Edit" you will need to make that row in the ListView editable. If they click "Delete", you remove the member from the Session("newGroupMemberDT") datatable and re-save (always re-save).

When the user is finished, they click "Done"--> postback --> add the latest member as before--> re-save the datatable--> get the count of rows (this is how many members there are)--> return to the user for collecting the money, when they click "Submit" to finalize the transaction--> postback-->retrieve the Session("newGroupMemberDT") to a new datatable instance and write the information row by row to the database--> return to the user with a confirmation message.

Naturally, there would be a "Cancel" button next to the "Done" button so at any time in the process, the user could cancel adding the group and which point you postback, make Session("newGroupMemberDT") = Nothing, and delete the row in the database: DELETE FROM groups WHERE groupID=Session("groupID")

My ThinkPad just went out.It has the vb code and battery is dead.
For unknown reason it does not take in power from an external plug.I was just writing a comment when it shut down w/o any warning.
Well I was about to let you that it might be the best idea to create the groupie on page load than when the first entry is done
. I know that some depts.will announce  the event and provide a link to the app from their homepage
So when the page opens immediately as we already talked about,a groupid is created.Now he will asked how many members he wants to register with corresponding total fees.
Then he enters individuals in the form. Can I asked you a question? If atany point he selects the back arrow on top of the menu and moves out but with intention of coming back to the page later maybe or not, would I delete the groupie? Thanks

Keep in mind that I am not familiar with the requirements of your project. I'm just trying to help with the logic.

>>create the groupie on page load than when the first entry is done

Fine, whatever meets your requirements.

>>I know that some depts.will announce  the event and provide a link to the app from their homepage

No idea what you are referring to here. I'm not familiar with your organization.

>>Now he will asked how many members he wants to register with corresponding total fees.

I would assume that once in a while, group leaders would make a mistake here by entering the wrong number of members so while I understand that you want to calculate the fees up front, I would not prevent the user from entering a different number of members when filling out the member information section.

>>If atany point he selects the back arrow on top of the menu and moves out but with intention of coming back to the page later maybe or not, would I delete the groupie?

Unless there's a postback, how would the server know the user has left the page and thus delete the groupId? This presents a problem in that you could potentially have many orphaned groupIds. As far as coming back to the page after leaving, there are methods for restoring a user's session. Each session has a session id that you can retrieve and use to restore the session. But sessions do expire. It's a big subject and there's tons of information and methodologies available, but it gets way beyond the original scope of this question. Perhaps a better way to handle the whole situation is to NOT create a groupId until the user has finalized the transaction. Then, just before adding the members to the database, create the groupId, add it to the groups table, then add the members. This way you will have a groupId ONLY when there are associated members. The other way to do it is to create the groupId in the beginning as we have been discussing, but have a clean up routine that runs anytime the groups table is accessed that removes any groupId that does not also exist somewhere in the members table as a foreign key.

I got a new adapter . I can now access my code. Thanks

>>> Perhaps a better way to handle the whole situation is to NOT create a groupId until the user has finalized the transaction. Then, just before adding the members to the database, create the groupId, add it to the groups table, then add the members. This way you will have a groupId ONLY when there are associated members.

I  think this is a better and safer route and implementation will probably a bit cumbersome .
Also this is probably effective if the right asp.net control is used. Listview or Gridview is probably better to use because you can see all the entries before finalizing the transaction, then submit  it and the groupid is created  with subsequent foreign keys to the registration table. BUT because I have so many required fields that need input, I can't use a Listview or griidview and I  created an input form.

I would'ne see the entire submission summary with the list of people entered until I go to a new page  where they are in a listview or griedview. So to display it in the listview ofn the new page, I need to write the result in the database table so I can read it from there.



>>>The other way to do it is to create the groupId in the beginning as we have been discussing, but have a clean up routine that runs anytime the groups table is accessed that removes any groupId that does not also exist somewhere in the members table as a foreign key.

This approach may not work becasue if several people are on the same page at the same time, it may be a problem for teh database to delete which record, etrc.

>>So to display it in the listview ofn the new page, I need to write the result in the database table so I can read it from there.

You shouldn't write anything to the database table until the user finalizes the transaction, because you want to allow them to cancel at any time during the process. Up to the final "Submit" you are storing all entered data in a datatable and saving it to the Session state. With each postback you are binding the datatable to a ListView or GridView control so the user can see the new members as they are added. Also, you want to provide the option to edit or delete any members before finalizing.

Binding the datatable to the ListView (or GridView) control:

Me.ListView1.DataSource = dt    'dt is your datatable
Me.ListView1.DataBind()

>>This approach may not work becasue if several people are on the same page at the same time
True, don't use that method.

Im closely looking at your code. Id like to find out what the InitializeGroup entry and InitializeRegistry entry do.

I have controls
For example

Firstname-Texbox1
Lstname-Textbox2
Address-textbox3...
.etc

What  does the routine do and also has datamapping. What is the datamapping do purpose?

I always arrange my sql read/write code by creating Initializing sub routines that handle the repetitive parts of reading and writing to a database so I can just call that sub first before getting to the work of reading or writing specific data. It's the standard practice of code re-use rather than code repeat. In the small example I posted, it doesn't make much sense to have it separated out like that. But in the original file, there were dozens of subs that called those Initialization subs first.

Use of the table mappings is optional and is unnecessary if the columns in your database table match up exactly with the data you are writing to the database. As I said, I extracted and modified some existing code to better reflect what you were doing. Some unnecessary code may have carried over.

My style is to write the stored proc,write a calling function or subroutine for,'inserts,deletes sir updates.From the sp,I mapped the parameters
In the form,I call the store procedure and pass the .net controls that are mapped to the parameters
That is why I'm mot familiar with sqladpter.datamappimgs.thanks

In the Group Registration table,
I have the groupid as Indentity (int) increment

However, I have the code function that creates an id which I placed in the button.
Function CreateGroupID() As SqlDataReader

        myconnection.ConnectionString = sqlconnectionString
        Dim myCommand As New SqlCommand
        myCommand.Connection = myconnection
        myCommand.CommandType = CommandType.Text
        myCommand.CommandText = "Select NEWID() as iGroupID"
        myconnection.Open()
        Dim dReader As SqlDataReader = myCommand.ExecuteReader(CommandBehavior.CloseConnection)
        Return dReader
    End Function

It is going to be the variable that I intend to pass in the registration table for each member however
this is not the ID automatically created by the group registration table. Will that be a problem?

The example code you provided was dataset or datatable?can you provide as link for datatable that can closely work with my question.You mentioned you have ton of code which is amazing.

>>It is going to be the variable that I intend to pass in the registration table for each member however
this is not the ID automatically created by the group registration table. Will that be a problem?

It's not a problem but if you intend for that NEWID() value to be the group identifier instead of the IDENTITY (int), then of course you will have to save to a new column in the same row of the groups table. Curious then, what's the point of having an IDENTITY column?

>>The example code you provided was dataset or datatable?can you provide as link for datatable that can closely work with my question

I was suggesting that you use a datatable to hold the members personal information while the user finished building the group and finalized the transaction. I did not provide any code for initializing or setting up the datatable. Is that what you need? It's very simple. Here's an example pulled from this site: http://www.devasp.net/net/articles/display/184.html

    Dim objDataTable As New System.Data.DataTable  

    'Create three columns with string as their type
    objDataTable.Columns.Add("Column 1", String.Empty.GetType())
    objDataTable.Columns.Add("Column 2", String.Empty.GetType())
    objDataTable.Columns.Add("Column 3", String.Empty.GetType())

Thanks for returning and looking at this. I completed the form design but the logic is what Im working on. I just want to make sure that Im doing it right that's why I keep on repeating my question. Thanks for being patient

So the original code that was posted has datatablemappings from source to destination.
("table","tableB')
then
("table","tableA")

does this code create a temporary tbl in memory ?

my tables in the database are WebGroup and WebIndividual..

what is that 'table"  then.

BTW Im working with the datatable also but I need clarification what the code does? thanks

No, not a temporary table. The DataTableMapping simply allows you to create aliases for the name of the datatable and it's columns. So, "table" becomes an alias for the actual datatable name, "tableA" (but I did not name any datatables in the code I posted, so it doesn't matter). Mappings are optional and just used for convenience to replace long abstract table and column names with more user friendly names . It's not being used in any way in the code I posted. I should have been more careful to cull unnecessary code rather than confuse you.

Here's some detailed information.

http://msdn.microsoft.com/en-us/library/ks92fwwh.aspx

thanks,
I found a link online that I think will help..

http://www.aspnettutorials.com/tutorials/controls/data-table-csharp.aspx

I just need to convert it to vb  but I can handle that.

Now, when do I need to move or insert the data to the live tables from datatable?
at what point.
Because the postback loads to mememry/

There are plenty of VB examples like the one I sent you. The link you cited shows the longhand way of creating a datatable. The vb.net code I posted here shows the shorthand method.

>>when do I need to move or insert the data to the live tables from datatable?

I would do it at the very end after any chance of the user further modifying the data or canceling the process has been eliminated. The only thing that should happen after the writing to the database is a confirmation message to the end user that their transaction is complete.

Should be a button  labeled "Done".
In the link I sent you, there is this line

row("id") = Guid.NewGuid().ToString()

well this look like the New(ID) for the indiviidual member of the group.
How about the groupID that would link each member to a group?
where will I get that id?
thanks

Guid.NewGuid() is a method for creating a unique id in C# and VB programming. Don't confuse it with the SQL function NEWID().

>>How about the groupID that would link each member to a group?
where will I get that id?

I suggested early on in the code I posted that you use an IDENTITY column in the groups table to hold the group ID. Creating and retrieving that ID can be done in two simple steps as I demonstrated:

1.) INSERT INTO TableB (cost) VALUES (@cost);SELECT SCOPE_IDENTITY()

2.) errorStr = CType(Me.SqlInsertGroup.ExecuteScalar(), String)

errorStr (from my code) now holds the value of your new groupId which you can now insert directly into the members table for each new member of the new group.

I thought all along that you were going with that solution until you hit me with this:

Select NEWID() as iGroupID

Now I'm not sure where you are.

I created it this way:

Public Function CreateGroupID() As SqlDataReader

        myconnection.ConnectionString = sqlconnectionString
        Dim myCommand As New SqlCommand
        myCommand.Connection = myconnection
        myCommand.CommandType = CommandType.Text
        myCommand.CommandText = "Select NEWID() as iGroupID"
        myconnection.Open()
        Dim dReader As SqlDataReader = myCommand.ExecuteReader(CommandBehavior.CloseConnection)
        Return dReader
    End Function


The code above: QUESTION

>>>1.) INSERT INTO TableB (cost) VALUES (@cost);SELECT SCOPE_IDENTITY()

>>>2.) errorStr = CType(Me.SqlInsertGroup.ExecuteScalar(), String)

I assume those 2 lines have to be in the button click..

>>>1.) INSERT INTO TableB (cost) VALUES (@cost);SELECT SCOPE_IDENTITY()  

I have more than 8 values to insert -does it matter?  I assume as long as I have the line
"SELECT SCOPE_IDENTITY()  ", a group id will be created. I just want to confimm that number of fields and values to be inserted  is irrelevant in this case..
 






and I call it in button click as

     Dim sGroupID As Guid
        dr = odata.CreateGroupID
        dr.Read()
        sGroupID = dr("iGroupID")
        dr.Close()

I was just experimenting on it..

1

Those two lines of code are just excerpts from the code I posted originally to demonstrate my methods for reading and writing to databases. No new ground was covered and no, they would not go into the button click. Instead you would call the entire function InsertGroup() from the button click.

2

If you are creating your groupId like this:

Public Function CreateGroupID() As SqlDataReader

        myconnection.ConnectionString = sqlconnectionString
        Dim myCommand As New SqlCommand
        myCommand.Connection = myconnection
        myCommand.CommandType = CommandType.Text
        myCommand.CommandText = "Select NEWID() as iGroupID"
        myconnection.Open()
        Dim dReader As SqlDataReader = myCommand.ExecuteReader(CommandBehavior.CloseConnection)
        Return dReader
    End Function

...then that is your groupId and why bother with the IDENTITY column at all? You don't need two unique id columns in the table to identify each group. The SELECT SCOPE_IDENTITY() becomes unnecessary if you're not using the IDENTITY column for your unique groupId. Pick one method for creating group IDs and abandon the other.

You do realize that if you use your CreateGroupID function you are going to have to write that ID to the groups table in another function, right? With the method I showed you it's all done in one step with one connection to the database. Your method will need two steps and two connections.

3

No, it would not matter if you had 8 values to enter in the group table.

>>>>...then that is your groupId and why bother with the IDENTITY column at all? You don't need two unique id columns in the table to identify each group. The SELECT SCOPE_IDENTITY() becomes unnecessary if you're not using the IDENTITY column for your unique groupId. Pick one method for creating group IDs and abandon the other.

Visually, this is how I understand this..
IIf I follow my method... the GroupTable will look something like this

WebID          GroupID                     Cost

1               A78y3788shvjs8           $100.00
2               By7999n78838hh          $50.00

then in the Members table it would look like this
Since the Identifier is datatype uniqueidentifier

RegID              GroupID                 Firstname      Lastname          course
78A76d9967        A78y3788shvjs8      John             Doe                  Spanish
845U99f422         A78y3788shvjs8      Georeg         Doe                  Greek

In your method ...

GroupID                     Cost

1                          $100.00
2                           $50.00

RegID          GroupID          Firstname    LastName
Y4653242       1                  John          Doe
B7889973        1                George       Doe


Current Status: I was able to populate  the Listview from the Data entry form; however I only selected
8 out of 20 fields to be displayed in the listview using datatable..
There is also a session("mytable") created each time I click the add button that sends output to the Listview.

Question
Im now ready to insert the data to both tables(group and indivisual)

How would I get all the data in the group- which means the  individuals I entered in the form that cumulated in the listview. Id liike to count the rows added and insert the data in the members table?
Can you explain what happens to the session I created and how I will make use of it to get the data iof indiividuals?

>>If I follow my method... the GroupTable will look something like this

I doesn't matter what the data looks like as long as it makes efficient use of storage space and has integrity. It's the Data layer. Concentrate your efforts at making things visually appealing in the Presentation layer of the project. My version of the group table is obviously more efficient in both storage space and read/write operations, but it's your project, you're the boss.

>>Can you explain what happens to the session I created and how I will make use of it to get the data iof indiividuals?

You are using a datatable and re-saving it to session state each time the user adds a new member, so in the end the datatable holds all the new members information.

Copy the session state datatable to a new instance of DataTable once again, call it dt, and you can get the rows count by assigning dt.Rows.Count() to an Integer.

Ready to write the members information to the database?

       Dim dr As Data.DataRow
       For Each dr In dt.Rows()
            With Me.SqlInsertCommand1
                  'Build your SQL insert command here
            End With
            Try
                If SqlConnection1.State = Data.ConnectionState.Closed Then
                    SqlConnection1.Open()
                End If
                Me.SqlInsertCommand1.ExecuteNonQuery()
            Catch ex As Exception
                errorStr = "Error: " & ex.ToString()
            End Try
        Next

I will use  your method so Id learn something new.
What test should I do to know/confirm that the members I added in the form are still in memory?
It has not hit me yet  to realize  that everytime I add a member and it adds to the datatable, I can still read and process those, that all of it is waiting to be inserted. I thought you mentioned that the session state may be lost.  Thanks.

>>>Copy the session state datatable to a new instance of DataTable once again, call it dt

How do I do this?

I saw an example to copy a datatable from msdn

Private Sub CopyDataTable(ByVal table As DataTable )
    ' Create an object variable for the copy.
    Dim copyDataTable As DataTable
    copyDataTable = table.Copy()

    ' Insert code to work with the copy.
 End Sub

now I can do
Dim i as integer =  copyDataTable.rows.count

Now Id like to know how can I access the members in that datatable and insert it to the database. Thanks ,

I assume that the insert would be another button ...

Here's  what I got..up


Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
       
        Dim wdata As New SQLFunctions

        If Not IsPostBack Then
            '    dr.Read()
            '    GridView1.DataSource = dr
            '    GridView1.DataBind()
            '    GridView1.Visible = Trued


            Dim dt As DataTable = wdata.CreateColumnnsForData
            Session("myDatatable") = dt
            Me.ListView1.DataSource = dt
            Me.ListView1.DataBind()

        End If
End Sub


    Public Sub AddDataToDataTable(ByVal lastname As String, ByVal firstname As String, ByVal firstchoiceA As String, _
                              ByVal firstchoiceB As String, ByVal firstchoiceC As String, ByVal secondchoiceA As String, _
                             ByVal secondchoiceB As String, ByVal secondchoiceC As String, ByVal lunch As String, ByVal dt As DataTable)
        Dim row As DataRow

        row = myTable.NewRow()

        'row("id") = Guid.NewGuid().ToString()
        'row("username") = username
        row("lastname") = lastname
        row("firstname") = firstname
        row("fchoiceA") = firstchoiceA
        row("fchoiceB") = firstchoiceB
        row("fC") = firstchoiceC
        row("schoiceA") = secondchoiceA
        row("schoiceB") = secondchoiceB
        row("schoiceeC") = secondchoiceC
        row("lunch") = lunch
        dt.Rows.Add(row)
    End Sub

     Protected Sub Button4_Click(sender As Object, e As System.EventArgs) Handles Button5.Click
     
      'I did not include the details...but basically mapping controls to variables


    wdata.AddDataToDataTable(sHLast, sHFirst, sFChoiceA, sFChoiceB, sFChoiceC, sSChoiceA, sSChoiceB, sSChoiceC, sLunch, DirectCast(Session("myDatatable"), DataTable))

        Me.ListView1.DataSource = DirectCast(Session("myDatatable"), DataTable).DefaultView
        Me.ListView1.DataBind()
        Call Clearfields()
    End Sub
     
Please note that no reference to GroupID here  - Just simply building the datatable

Let me know how to proceed here.. Of course I entered the cost somewhere in the form which is a Group data.

I'm out in the field today, no time to sit in front of a computer. Kind of hard to consume all that info on an iPhone. I'll have to read it later.

Thanks tommyBoy for all your HELP.

I did not put all the fields in the listview that are in the form. How would I accout for those if I want to include writing it to the Membertable considering that I would loop in the datatable

>>I did not put all the fields in the listview that are in the form. How would I accout for those if I want to include writing it to the Membertable considering that I would loop in the datatable.

It depends. If you intend the missing fields to be empty in the database and those columns accept null values, you can just leave them out of your insert statement. Example: Your table has 5 columns of data but you only intend to write to three of them, then instead of INSERT INTO TableA (col_1,col_2, col_3, col_4, col_5) VALUES (@Col_1, @Col_2, @Col_3, @Col_4, @Col_5), you would simply write INSERT INTO TableA (col_1, col_3, col_5) VALUES (@Col_1, @Col_3, @Col_5). Columns 2 and 4 would just be null.

If, on the other hand you want to add your own values to those columns not represented in the form, you would need to first have the columns properly set up when you first create the datatable then write your values to them at the time of insert. Creating the columns was covered in post ID:36957676.

This: row("lastname") = lastname
Should be this: row.Item("lastname") = lastname

Thankd tommyboy.  I think im successful populaitng the datatable. It''s making a some sense now.
Im planning to implement you code in the other button.

Now here's what Im looking forward to as early at this point even though I have not written anything yet to the 2 tables (group and registration)

This user has to submit a payment for the cost of the registraiton. The only thing I have is a link to the payment office site. There is a form which the user has to complete,enter credit card info or echeck as payment. But this routine is done in the other site and not in the app.

Well here I am already writing their data to the tables even though user has'nt paid yet.
I m thinking of adding a column in TableB registration table and Grouptbl. This column will be the status column.  I will write ' booked' and if payment is succeful I will update the field into "confirmed".

What do you think? too much work

If you had a status column, it would just need to be a boolean value of type "bit". False means the package was booked but not yet paid for and true means booked AND paid for. Never use strings in a database column where there are only two possible values. String types take up way more space than bit types.

Problem #1: What kind of "payment received" message can you get from the payment site that returns back to the application to let you know that it's ok to change the status column from false to true?

Problem #2:  Timing. Do you expect this "payment received" message to come back quickly while you still have the application session active? If so then no need for the status column. Just write the data from session state to the database IF and only IF payment was received. If the "payment received" message will come back at some time after the application session has ended, then by what mechanism are you going to update the status column. This goes back to Problem #1, what kind of "payment received" message can you get from the payment site.

I've read somethng about web services. Is this something that can be done? What are the requirements to have set up like this?
Just the confirmation aspect like if payment site sents 'OK' signal then, an update can be initiated.
This is just trying to get possibilities...

>>>Ready to write the members information to the database?

       Dim dr As Data.DataRow
       For Each dr In dt.Rows()
            With Me.SqlInsertCommand1
                  'Build your SQL insert command here
            End With
            Try
                If SqlConnection1.State = Data.ConnectionState.Closed Then
                    SqlConnection1.Open()
                End If
                Me.SqlInsertCommand1.ExecuteNonQuery()
            Catch ex As Exception
                errorStr = "Error: " & ex.ToString()
            End Try
        Next


How did you build the datatable here (dt) abd is referenced?

>>>With Me.SqlInsertCommand1

What is the syntax sqlInsertcommand? I have'nt seen that...
I always use.. INSERT INTO...sql statement
so its that new?

No, not new. If you look back at my original code example you will see that created other SQLCommand objects and named them. Like anything else in object oriented programming, you assign a name to any object you create so you can refer to it in the code. You need to build you command object and specify the various parameters; commandType, commandText (where your INSERT INTO... goes), commandTimeout, etc. Creating a "With" block is just a shorthand way of filling in the parameters without saying Me.SqlInsertCommand1 for every line.

>>>>       Dim dr As Data.DataRow
       For Each dr In dt.Rows()

How is the dt derived here? where does the data originaed,built?

??
From a copy of the datatable you have been building all along in Session state.

But that copy of the datatable was executed in another button_click event.
Do you mean that the copy is still enforce or should I make another copy under this new button?

ok, I think I should make a copy of the datatable again in the  second button.

Private Sub CopyDataTable(ByVal myDataTable As DataTable )
    ' Create an object variable for the copy.
    Dim copyDataTable As DataTable
    copyDataTable = myDataTable.Copy()
    ' Insert code to work with the copy.
 End Sub

How would I use this  or how would I call this subroutine to create a copy of the datatable  n the button?

>>But that copy of the datatable was executed in another button_click event.
>>ok, I think I should make a copy of the datatable again in the  second button.

What is "first" button, "second" button?

The session state copy of the datatable remains available for as long as the session is active or you specifically assign it to a Nothing value.

first button is  just adds to the listview>>id ID: 36963044


second button is to insert the the data to table> im building this now but im stuck with syntax issues
I would like to know how to read content of the listview row by row and store it in variables..

something like this..(not yet finish using your code conceptually...
 
 Dim pdata As New PLSQLFunctions
       Dim dt As DataTable
        dt = Session("myDatatable")
        Dim copytbl As New DataTable
        copytbl = CopyDataTable(dt)
Dim dr As Data.DataRow
 For Each dr In copytbl.Rows()
            With pdata.InsertGroup(seventyear, slevel, regCount, regcost, , amountdue, sUser, sdate)
                'Build your SQL insert command here
      im stuck here reading the listviiew contents which i built in the first button

                 pdata.InsertRegistrant(slevel,

'll deal with the lines below later but first figure out above code
            End With
            Try
                If pdata.SqlConnection1.State = Data.ConnectionState.Closed Then
                    SqlConnection1.Open()
                End If
                Me.SqlInsertCommand1.ExecuteNonQuery()
            Catch ex As Exception
                errorStr = "Error: " & ex.ToString()
            End Try
        Next

BTW Good Morning! ..

I can't figure out why you would ever need to read the listview to get the data back out

Here's the scenario again for adding new members. This represents one button clicked repeatedly if necessary.

--user enters member info into a form, clicks "Add" (or whatever you call the button), postback
--in code behind retrieve the datatable from session state into a new instance of DataTable (or create it if it does not exist yet)
--add a row, add the new member information, re-bind the datatable to the listview, re-save the datatable to session state, return to the user
--user continues to add new members, you repeat the process on the server side.

The listview is just for display purposes, not for holding the data for later retrieval.

When the user is done with adding members, they click a "Done" button (or whatever you call the button). After you figure out the whole collecting payment problem, it would be time to retrieve the datatable again from session state (not from the listview) and write it to the database table.

This looks completely wrong:

With pdata.InsertGroup(seventyear, slevel, regCount, regcost, , amountdue, sUser, sdate)
                'Build your SQL insert command here
      im stuck here reading the listviiew contents which i built in the first button

                 pdata.InsertRegistrant(slevel,

'll deal with the lines below later but first figure out above code
            End With

What is pdata.InsertGroup?

It should be:

With MyInsertCommand  'whatever your command object is called
End With

Or:

MyInsertCommand.CommandType = CommandType.Text
MyInsertCommand.CommandTimeout = 100
MyInsertCommand.Parameters("@cost").Value = cost  'and so on for any parameters
MyInsertCommand.CommandText = "INSERT INTO TableB (cost) VALUES (@cost)"  'or whatever your insert statement is

You would read the data from the datatable using a for each loop as I demonstrated in post ID:36961497

pdata.Insertgroup
comes from class PLSQLfunction

The arguments I passed here are the in form's controls currently open.....

Public Function InsertGroup(ByVal EventYear As String,
                         ByVal code As String,
                         ByVal Count As Integer,
                         ByVal lCost As Decimal,
                         ByVal lastUpdUser As String,
                        ByVal lastupddate As String) As String
        Me.InitializeGroupEntry()
        Dim errorStr As String = ""
        Dim strGroup As String = "INSERT INTO Web_Group_Registration (EVENTYEAR,PROCESS_LEVEL,REG_COUNT,COST,LAST_UPD_USER,LAST_UPD_DATE) VALUES (@eventyear,@level,@count,@cost," & _@lastupduser,@lastupddate);SELECT SCOPE_IDENTITY()"

        With Me.SqlInsertGroup
            .CommandTimeout = 100
            .CommandType = CommandType.Text
            .Parameters("@eventyear").Value = EventYear
            .Parameters("@processlevel").Value = Processlevel
            .Parameters("@regcount").Value = RegistrationCount
            .Parameters("@regcost").Value = RegistrationCost
            .Parameters("@lastupduser").Value = lastUpdUser
            .Parameters("@lastupddate").Value = lastupddate


            .CommandText = strGroup
        End With
        Try
            If SqlConnection1.State = Data.ConnectionState.Closed Then
                SqlConnection1.Open()
            End If
            errorStr = CType(Me.SqlInsertGroup.ExecuteScalar(), String)
            SqlConnection1.Close()
        Catch ex As Exception
            If SqlConnection1.State = Data.ConnectionState.Open Then
                SqlConnection1.Close()
            End If
            errorStr = "Error: " & ex.ToString()
        End Try
        Return errorStr
    End Function


>>>With MyInsertCommand  'whatever your command object is called
End With

Is Mynsertcommand created in a class or in the form?

I like to put my sql functions in a separate class, so that's where I create my sqlCommand objects.

From my code:

Protected WithEvents SqlInsertGroup As System.Data.SqlClient.SqlCommand

That is exactly what I'm doing.when I call Insertgroup which is under class Plsqlfunctions,this 'sqlinsertgroyp'is embedded in that function.
I'm calling this function from a page so I need to create an instance of Class PlSQLfunctions and I'm passing all the fields in the Group Registration table. I'm using the code that you gave in this button.

I working with the done button but I'm confused to how to used the class.

My power is out, all I have is my iPhone.
The code you posted last is getting close. Your InsertGroup function, which resides in your PLSQLFunctions classes,  is called from your aspx code behind, but you only need to pass in the datatable as the only parameter. Then in the function itself, you loop through all the rows (For Each dr In dt.Rows()), inserting one row of data at a time.

my thinking is getting clearer on this and let me know what you think

1. Insert the group data to the group table.
 2. This will create a groupid
3. Now loop to the datatable,
Inserrt the datatable AND the groupid for each indiviidual.

Is this for the 'Done' button?

Yes, for the "Done" button, but it does not address the getting paid issue.

Im working on it.
Does it matter if I create a datatable not consistent with the real table in the database as far as structure is.

For example

Datatable
First name,second Name,ID,location...

real table has

ID,Location,First name,Second name.

Does it matter when I mapped it during insert to real table? or ishould it be in order. I dont want t change the design of real table just to accommodate datatable.  Thanks

Doesn't matter. You don't even have to fill in every column if the empty columns allow null values.

Great.how will I test and see what's loaded in datatable?

You could use the debugger in Visual Studio or you could write some temporary code in Page_Load that loops through the datatable and prints the contents to a label. Don't you have a ListView already that displays the data?

I have a listview that is fired by first button but id like to see the data when fired by 2nd button .is there a command to expose it.Does datatable persists

The session copy of the datatable persists until the session has ended. There's no single command to expose it. You'll have to look at the Watch window in the debugger or write a loop to expose the data. Or, just write it to the database and select it back out again in Sql Server.

How would I read the  datatable since Im pasing it as an argument dt
In the button_click event? and then call it as  Insertgroup(dt,firstname,last... etc)

Can you show me a few llines of  code how to read the datable from the button click event so as to start with?
thanks

Confused. Do you want to read it prior to inserting it in the database but still during the execution of the "Done" button click event? For what purpose?

In any case, reading it would be by looping through the rows and doing something with the data:

Dim str As String = String.Empty
Dim dr As Data.DataRow
For Each dr In dt.Rows()
     str &= dr.Item(0) & ", " & dr.Item(1) & ", " & dr.Item(2) & ", " & dr.Item(3) & ", " & dr.Item(4) & System.Environment.NewLine()
Next

Now the data is in a string variable. What do you want to do with it?

BTW. in the above code, if any datatable items are not in string format, you would have to cast them to strings or apply the ToString() function.

I started this way... for 'Done button

      Dim pdata As New PSQLFunctions
        'Create a copy of the datatable
        Dim dt As DataTable
        dt = Session("myDatatable")
        Dim copytbl As New DataTable- 'Is this copy necessary but u said persists but this is idone in the second button??
        copytbl = CopyDataTable(dt)

Then I did..

       Dim dr As Data.DataRow
        For Each dr In copytbl.Rows
           slastname = dr("lastname")
            sfirstname = dr("firstname")
            sfchoiceA = dr("firschoiceA")
            sfchoiceB = dr("firstchoiceB")
            sfchoiceC = dr("firstchoiceC")
            ssecondchoiceA = dr("secondchoiceA")
            ssecondchoiceB = dr("secondchoiceB")
            ssecondchoiceC = dr("secondchoiceC")
next

          But I noticed that you structured it to look like a table with the concaenation of the rows in your code. Is this necessary to structured it like a table or looping is fine?

then the next line would be the insert...

 Dim errorStr As String
        Try
            pdata.InsertGroup(copytbl, seventyear, sprocesslevel, regCount, regcost, numlunch, lunchfees, regandlunchfees, amountdue, sUser, sdate)
        Catch ex As Exception
            errorStr = "Error: " & ex.ToString()
        End Try

>>Is this copy necessary but u said persists but this is idone in the second button??

The only copy that will persist across postbacks is the session copy. "copytbl" will be disposed of when the server side is done and the page returned to the user. So each time you want to use the datatable, you need to retrieve it from session and make a copy.

>>Is this necessary to structured it like a table or looping is fine?

I'm really confused as to why you keep asking me how to read the datatable. I provided complete code more than once and you have a working example in your own code already. The procedure is always the same. Loop through the rows and get the data with dr("columnName"). Then, do whatever you need to do with it -- copy it to a string, insert it into a database, bind it to a control, etc..

>>> So each time you want to use the datatable, you need to retrieve it from session and make a copy.

Is'nt that exactly what I did here. make a acopy.
I don't know ehat u mean by make a copy?


Dim pdata As New PSQLFunctions
        'Create a copy of the datatable
        Dim dt As DataTable
        dt = Session("myDatatable")
        Dim copytbl As New DataTable- 'Is this copy necessary but u said persists but this is idone in the second button??
        copytbl = CopyDataTable(dt)

Im not a strong closer but I think Im close to see the end of the tunnel. I have some issues.

First- Im gettting Nullreference issues
and object not set .Use new keyword errors
I got it when it goes to the Insertgroup code.

Id like to check if each datatable is Null or have values...

What can I add to check each field to test for null. thanks

 str &= dr.Item(1) & ", " & dr.Item(2) & ", " & dr.Item(3) & ", " & dr.Item(4) & ", " & _
                dr.Item(5) & ", " & dr.Item(6) & ", " & dr.Item(7) & ", " & dr.Item(8) & ", " & dr.Item(9) & ", " & _
             dr.Item(10) & ", " & dr.Item(11) & ", " & dr.Item(12) & ", " & dr.Item(13) & ", " & dr.Item(14) & ", " & _
              dr.Item(15) & ", " & dr.Item(16) & ", " & dr.Item(17) & "," & dr.Item(18)
            'Next
        Next

>>What can I add to check each field to test for null. thanks

You can use the IsDBNull(value) function to check if a datatable value is null.

What are you planning to use this string for if I may ask?

Im confused how to use tthe insertgroup function
First I don't know how to pass the dt argument from the button. Is it thur the string str that was built.
If after the loop next.. what sould I define the str as datatable?

And in the Insertgroup function, rightr away i get error in the Initializeentry. Is it necessary step to initialize? Even when I comment out I get error
in the
    With Me.SqlInsertGroup
            .CommandTimeout = 100>>> right away here I get error(null reference exception
            .CommandText = strGroup

The string str has nothing to do with this project. You asked me how to read the datatable contents and I gave you yet another example, this time with the contents read out to a string, AS AN EXAMPLE. Not as part of the project. I'm going to keep my answers as short and succinct as possible so as not to add any more confusion.

RE: the error
Must be an error in your initialization sub. This makes Me.SqlInsertGroup a null object.

>>>The string str has nothing to do with this project. You asked me how to read the datatable contents and I gave you yet another example, this time with the contents read out to a string, AS AN EXAMPLE. Not as part of the project. I'm going to keep my answers as short and succinct as possible so as not to add any more confusion.

THEN WHAT SHOULD IT BE?

What does "IT" refer to in that question?

IT ? WHAT IS THE DT ARGUMENT  IN CODE COMING FROM A BUTTON_CLICK

 pdata.InsertGroup(dt, seventyear, sprocesslevel, regCount, regcost, numlunch, lunchfees, regandlunchfees, amountdue, sUser, sdate)

iM PASSING dt. but ihow is it defined before it is passed
and you showed me a string str?? n

Here's how dt is defined before passing it as an argument.

Dim dt As New DataTable
dt = Session("whateverYouCalledIt")
pdata.InsertGroup(dt, seventyear, spro.....

Never mind the string. You are confusing issues with your incorrect use of programming terminology. The string str was in response to this statement from you "how to read the datable from the button click event". I kept asking you, why would you want to "read" the datatable from a button click event. What are you going to do with it? But you never responded. You just kept asking for code to "read" the datatable.

regarding the string. It because I saw concatentation I taught that is how to pass the dt.

Dim str As String = String.Empty
Dim dr As Data.DataRow
For Each dr In dt.Rows()
     str &= dr.Item(0) & ", " & dr.Item(1) & ", " & dr.Item(2) & ", " & dr.Item(3) & ", " & dr.Item(4) & System.Environment.NewLine()
Next

>>>Dim dt As New DataTable
dt = Session("whateverYouCalledIt")
pdata.InsertGroup(dt, seventyear, spro.....

When it gets to pdata.Insertgroup,
what is the funciton code.. I know you put this somewhere before but tjust to confirm it.
How is dt presented there?

What is the INitializegroupentry and why is it needed?

>>regarding the string. It because I saw concatentation I taught that is how to pass the dt.

Please realize that I am not intimate with your project. I cannot see what you see. ANY code I provide here should be considered a code example or code snippet. Not code that you can copy and paste into your project and expect it to work. You need to extract the CONCEPT behind the code I post and incorporate THAT into your project, not the code itself.

>>When it gets to pdata.Insertgroup, what is the funciton code..

ID:36975015 has a good example of the code for InsertGroup function.

>>How is dt presented there?

dt is the full datatable as passed in by the button click event.

>>What is the INitializegroupentry and why is it needed?

InitializeGroupEntry from my original code demonstrates the method I use when coding sql transactions. That is, I like to separate repetitive steps into a sub of their own so I can re-use it instead of repeating it. Doing it this way is optional. The purpose of the code in the InitializeGroupEntry is to declare the objects needed to establish the connection to the sql database.

Lessons learned here are:
1. creating unique id using New ID()
2. Object Oriented method for data manipulation.--
3. Datatable a nd persistence
4.Session States
Thank you for your patience and help .  It was well time spent.

You're welcome, glad to help.