500 Points: Adding Multiple Rows

I assume you currently have a datatable (named dt) bound to the grid

for x as integer = 1 to textbox1.text
  dim dr as datarow
  dr = dt.newrow
  dt.rows.add(dr)
next

this will add the number (from textbox1) of empty rows to the datatable (and automatically reflected in the grid)

Sorry...I don't have a datatable.  I also don't really know what the difference between a datatable and datagrid is.  Hence, I was asking for a full example to learn these things.  Thanks for the reply thou.

Muligan

Here is how I'm filling my datagrid...I left out the sql call:

Dim dsSpecialPrice As New DataSet
        objAdapter.Fill(dsSpecialPrice, "dtContracts")

        Dim dvContracts As New DataView(dsSpecialPrice.Tables("dtContracts"))
        dvContracts.Sort = viewstate("sortexpression") & viewstate("sortdirection")

        dgContracts.DataSource = dvContracts
        dgContracts.DataBind()

        objConnection.Close()

A datagrid is just a container, a display mechanism of sorts.  It has no intrinsic data of its own.  It requires a datasource of some kind, typically a datatable or dataview, to be of any use.

A datagrid is a control which you can drag from the toolbox to your form this will display data in a grid like fashion (like when you view a table in msaccess), you fill a datagrid with a data (from a database like access or sqlserver etc..), usually a table (maybe contained in a dataset). Or you can fill a datagrid with a table you have constructed in code.

If you tell what you want to show in the grid, maybe I can help you a bit further :-)

You do have a datatable.  It is created by your dataadapter.  Your table is ...

dsSpecialPrice.Tables("dtContracts")

A couple of notes...I don't believe you need either the DataBind code or the Connection.Close as they are inherent in setting the datasource and the Fill methods respectively (someone may correct me if I'm wrong here but I've never needed them).  If you use Ronald's suggestion for adding the rows to your datasource you can simply update your database using the SQLDataAdapter.Fill method.

Ah so you do know something, the below code should do it

for x as integer = 1 to textbox1.text
  dim dr as datarow
  dr = dsSpecialPrice.Tables("dtContracts").newrow
  dsSpecialPrice.Tables("dtContracts").rows.add(dr)
next

You do need databind in a Web application (which I think he is making), databind doesn't even work in win apps. and closing your connection is good practice don't loose the habit :-)

looking...

Thanks guys for the little tutorial.

Ronald...  when I use your code I get the error: dsSpecialPrice is not declared.

The "add rows" function is in a different sub than my main SQL call.

Dim dsSpecialPrice As New DataSet

should be declared at the form level not within a sub or a function (I think you are now declaring it in the page load )

Ronald... that solved that error...now I get this one:

Object reference not set to an instance of an object.

Where do you get that error ?

in the browser, after I recompiled.

At what line (show the code that produced that error)

Here is my sub:

Private Sub AddRow_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles AddRow.Click

        If (IsNumeric(RowCount.Text)) Then
            Dim i As Integer
            For i = 1 To CInt(RowCount.Text)
                Dim dr As DataRow
                dr = dsSpecialPrice.Tables("dtContracts").NewRow
                dsSpecialPrice.Tables("dtContracts").Rows.Add(dr)
            Next
        End If

        ReadExpenses()
    End Sub

Yes and at what line  in that sub do you get that error

doesn't like this one:

dr = dsSpecialPrice.Tables("dtContracts").newrow

BRB

Well the only thing I can think off, is that there isn't a table named like that, because I just tested it and it works

maybe try this

Private Sub AddRow_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles AddRow.Click

        If (IsNumeric(RowCount.Text)) Then
            Dim i As Integer
            For i = 1 To CInt(RowCount.Text)
                Dim dr As DataRow
                dr = dsSpecialPrice.Tables(0).NewRow
                dsSpecialPrice.Tables(0).Rows.Add(dr)
            Next
        End If

        ReadExpenses()
    End Sub

I'm sorry but I'm going home now (have to pick up the daughter), maybe one off the other experts can help you further, otherwise it will have to wait until tomorrow

Ronald...When I try your last example... I get the following error: Cannot find table 0

b1xml2 ...

Do you have any suggestions?

Are you by any chance testing the new sub before you have called the sub that contains the line

        objAdapter.Fill(dsSpecialPrice, "dtContracts")

Roger

Good question...Roger

How do I determine that?

i am creating a whopper of a sample, =)))

awesome! ... thanks b1xml2

Make sure you are in Debug (middle dropdown box at top of screen).  Put a break point (click in left hand margin) by the line

        objAdapter.Fill(dsSpecialPrice, "dtContracts")

and then run.  If it stops at that line before you get to click on your AddRow button, you are filling your dataset before you are testing the new sub.  If not, you're not.

It is just that, if dsSpecialPrice is being filled, I just cannot see how dsSpecialPrice.Tables can fail to recognise either the table name you have specifically put in it "dtContracts" or even any table: if there is only one table its index must be 0.

Roger

Roger... sorry I don't think I can do that kind of debugging at the moment.  I can not run the project locally (due to database connection), so I don't think I will be able to debug in that fashion?

b1xml2... Sorry to bother.  Just checking in to see how that example is coming?

OK, another question. Are  you seeing that the datagrid is filled with data before you press your AddRow button?

If (as I assume) you are not - because you haven't got the database connection - that means that your AddRow sub will not be able to find the table it is trying to add rows to because that very table is actually created in your project by use of the database connection.

In other words, the lines

                dr = dsSpecialPrice.Tables("dtContracts").NewRow
                dsSpecialPrice.Tables("dtContracts").Rows.Add(dr)

and

                dr = dsSpecialPrice.Tables(0).NewRow
                dsSpecialPrice.Tables(0).Rows.Add(dr)

both depend, just like the datagrid filling with data does, on there having been a connection to the database.  The connection does not have to be live at the time.  It is just used by the DataAdapter to fill the DataSet (and may be needed later if you wish to update the database with what has happened in your application).  But you won't be able to test those two lines of code without a database connection.

I shouldn't worry, though.  They looked solid enough to Ronald (and he said he had tested them).  And they look solid enough to me.

Roger

Hey Guys... if this helps... here is an article I found:

http://www.dotnetjohn.com/articles.aspx?articleid=83

Demo:

http://www.dotnetjohn.com/runtime/MultiLineEdit.aspx

That has the functionality that I want.  But he is using a dataset that isn't from a DB and I don't how to adjust the code.

Roger...

My datagrid is being filled.  I only get that error after I click the Add button.

enjoy this:

1. remember to copy and paste to notepad and save as aspx file.
2. The customer id and customer name are required, I have not ahd the time to add the validators.
3. We grab the top 10 records
4. It shows how we can add/delete/insert multiple records and let ADO.NET do it all for you.

cool...i'll give it a try.

we are using the Northwind database, ple check on the ConnectionString property and change accordingly... We are using SQL Server

when you add the rows, they will appear empty, click on edit to add new values and then update to persist them. Click save when u are ready to pump the changes thru to the database

Ok... I'm getting an error, see if you can make heads of it?

Invalid object name 'Customers'.

Line 11:      If Not IsPostBack Then
Line 12:           Dim adapter As SqlDataAdapter = MyDataItem.NewAdapter()
Line 13:           adapter.Fill(Me.PageMember)
Line 14:           adapter.Dispose()
Line 15:           BindGrid()

Source File: d:\inetpub\wwwroot\test5.aspx    Line: 13

My bad... I forgot to change the web.config app settings.

it's a connection thingy:

see this:
Private Shared ReadOnly Property ConnectionString() As String
      Get
            Return ConfigurationSettings.AppSettings("ConnectionString")
      End Get
End Property

well either set this in ypur web config:
<configuration>
  <appSettings>
      <add key="ConnectionString" value="Data Source=(local);Initial Catalog=Northwind;User Id=<username>;Password=<password>;" />
  </appSettings>
where <username> is the user name to Sql server and <password> is the password

I'm off out now for a couple of hours.  I'll look in again when I'm back.  Good luck.

Roger

Thx Roger!

b1...

I already have a setting like that in my web.config file that is referencing a different table as the initial catalog.

Can I just add an additional "connectionstring" key?

Because...I tried just adding a second key and then running the code..and I get the same error.  Maybe we would just change :

Private Shared ReadOnly Property ConnectionString() As String
     Get
          Return ConfigurationSettings.AppSettings("ConnectionString")
     End Get
End Property

into a regular call to the db right here?

Thoughts?

yep, u could return a valid Connection string...

ok...here is what I have:

Private Shared ReadOnly Property ConnectionString() As String
          Get
               Return ConfigurationSettings.AppSettings("MM_CONNECTION_STRING_SQL2")
          End Get
     End Property
     
     
     
     Private Shared Function NewConnection() As SqlConnection
          Return New SqlConnection(ConnectionString)
     End Function

This does not work.

Here is my key:

<add key="MM_CONNECTION_STRING_SQL2" value="Initial Catalog=Northwind;Data Source=server;User ID=sa;Password=password;" />

change the Data Source to point to your sql server. I presume, it is local, then use (local)

make sure your sql server is in Mixed Mode Security

sorry...it is that way... I just changed it when I pasted it in here... so my database name isn't out here floating around.

kewl, well make sure the connection string is correct... and try again, I am off to bed =)

b1... I really appreciate the help.  Please send me your address to : cboyce@ngensoft.com .  I would like to send you a dinner gift certificate.

b1... it still doesn't work for me

if you see closely, it is a simple case of grabbing the data from the database :

Private Const SelectSqlCommandText As String = "SELECT TOP 5 CustomerID, CompanyName, ContactName, ContactTitle FROM Customers ORDER BY 1"

you can comment out the binding portion and see how rows are added but it would be an issue of the Connection to sql server....

I just threw in the 5 records to show how one can modify existing records...

Protected Sub Page_Load(ByVal sender As Object,ByVal e As EventArgs)
     If Not IsPostBack Then
          'Dim adapter As SqlDataAdapter = MyDataItem.NewAdapter()
          'adapter.Fill(Me.PageMember)
          'adapter.Dispose()
          BindGrid()
     End If
End Sub

The connection error I'm getting now is:

The ConnectionString property has not been initialized.

I comment out those 3 lines... and now I see the app.

make it simpler:


Private Shared ReadOnly Property ConnectionString() As String
Get
      Return "Data Source=<server>;Initial Catalog=NorthWind;User Id=<user>;Password=<pwd>;"
End Get
End Property

Yeah... it definitely doesn't like this line: adapter.Fill(Me.PageMember) ... by the way... what does "me." represent?

Me in VB.NET is this in C#. It refers to the current instance of the class

still this will be a Connection Issue and if you cant srot this out, you wont be able to see whether the Update method is successful

yeah... it is a connection issue I have to work out.  Weird... because everything else connects just fine with the same connection string in the same directory.

you could convert that to code behind and then complie in VS.NET. Sometimes when you have Windows authentication set in the web.config, and/or machine config, such standalone examples of mine can fall down due to insufficient perms as under the IUSR_<machine name>

But standalone examples are the best for everyone to learn from.

YUP... figured it out.  SQL setting.

excellent =))

Hi!  I'm back.  Is this all sorted now?

Roger

dunno, he's probably asleep =)

Yup...its all figured out.  I still have to totally evaluate b1 sample, but everything looks good at this point.

Roger, thank you for checking back.  I really appreciate it!

mulligan, please look very hard at the architecture of the classes. Pay attention to how the adapter gets its information and how you do not use variables to hold ConnectionStrings and the like. Use classes wherever you can to centralise the solution to a problem, and to make it easier to maintain over time.

Thanks B1... I totally appreciate your help.  At my job, I'm the only one that does this stuff so I really don't have anyone to bounce ideas off from and your input has been very valuable!  If your comfortable...some how... I would like to send you a gift certificate.  I've told my wife how much you've helped me and she insists.

Glad it all worked out, and thanks for the points.

Just for the record, having eliminated (I think) all the other possibilities I am pretty certain that the problem you were having with Ronald's original code was caused by "shadowing".  

I think when, in answer to Ronald's suggestion because of the "dsSpecialPrice is not declared" error, you declared

    Dim dsSpecialPrice As New DataSet

at form level, you also left the same declaration in its original place - perhaps in the Form_Load sub.  If so, that would mean that there were two versions of dsSpecialPrice, not just one, but with different scope.  The one in the original sub, which was being filled by the objAdapter.Fill() call, and from which your datagrid was being filled, did have the table "dtContracts" in it.  But that was only visible within the original sub.  The one which was being looked at by the new AddRow_Click sub was that which had been declared at Form level and that had never been filled and so did not have the table "dtContracts" in it, or indeed anything in it at all.

Roger

actually muligan,

I try to help anyone willing to listen to what I have to say, without any expectation of recognition nor wanting any gifts. I have had been offered with chocolates once from a very grateful fellow in Belgium since I helped him solidify his XML knowledge in the Microsoft XML Newsgroups and yes, I did take him up on the offer and my wife enjoyed all of it (well most of it anyways). The greatest joy I get is when someone understands what I am trying to demonstrate. That has always been my trait. Understanding is key to growth in all areas.

I would rather that you provide a needy fellow with some food, for in doing so, you add a little more happiness to this very confused world.

b1xml2 =)

Thanks again guys for all your comments.

Roger... for the record... I am only declaring dsSpecialPrice once at the form level.

B1... Question for ya.  Looking at this piece of the code:

Me.PageMember.Rows.Add(New Object() {Nothing,Nothing,Nothing,Nothing})

I have a fully editable datagrid (same datagrid that we've been working on), so instead of having the new rows being filled with nothing... can we fill them with textboxes?  Also, do you forsee any problem because I have to convert your example to a single "one click and update" rather than single line edits.

Thoughts?

Muligan

you can, that code just shows how to add "empty" rows...

Sorry... miskey
 Should read:

Also, do you forsee any problem because I have to convert your example from a single "one click and update" rather than single line edits.

Gheez... you can tell it is the morning.  I was right with my first question.  Sorry for the confusion.

Muligan

Thanks for letting me know.  I'm stumped then.

Roger

one click and update well, it means less flexibility for the user since it is automated... what if users start to want exceptions ?!

b1... I just noticed...

When using your example, If I try to update or delete any existing customer in is in the datagrid, nothing happens...even after I click the Save link.

Did you do that on purpose?

The update, delete..etc.  works on only the new rows that were added.

Thoughts?

logical flaw:

change this class:

'nested class
Public Class ControlItems

      Public ReadOnly CustomerID As String
      Public ReadOnly CompanyName As String
      Public ReadOnly ContactName As String
      Public ReadOnly ContactTitle As String
      
      Public Sub New(ByVal item As DataGridItem)
            Dim CustomerIDTextBox As TextBox = CType(item.FindControl("CustomerIDTextBox"),TextBox)
            
            If Not CustomerIDTextBox Is Nothing Then
                  CustomerID = CustomerIDTextBox.Text.Trim
            Else
                  CustomerID = item.Cells(0).Text.Trim
            End If
            
            Dim CompanyNameTextBox As TextBox = CType(item.FindControl("CompanyNameTextBox"),TextBox)
            If Not CompanyNameTextBox Is Nothing Then
                  CompanyName = CompanyNameTextBox.Text.Trim
            Else
                  CompanyName = String.Empty
            End If
            
            
            Dim ContactNameTextBox As TextBox = CType(item.FindControl("ContactNameTextBox"),TextBox)
            If Not ContactNameTextBox Is Nothing Then
                  ContactName = ContactNameTextBox.Text.Trim
            Else
                  ContactName = String.Empty
            End If
            
            Dim ContactTitleTextBox As TextBox = CType(item.FindControl("ContactTitleTextBox"),TextBox)
            If Not ContactTitleTextBox Is Nothing Then
                  ContactTitle = ContactTitleTextBox.Text.Trim
            Else
                  ContactTitle = String.Empty
            End If
            
            
      End Sub
End Class

Hmm...weird

The updates work now, but the deletes don't work for existing items.  The Delete will work if it is a customer that I added, but not a record that was already in the db.

Thoughts?

it appears that the delete will fail unless you delete the other tables containing the customer id. This is because of foreign key implementation...referential integrity..

I see... that makes sense.  In my app...I will not have to worry about that...so we should be good.

b1...another question:

I try to set the SQL call to select WHERE Customer = Request.Querystring (in the proper format)

and I get an error says that the "Request" is: Reference to a non-shared member requires an object reference.  

Do you know what that means?

I should say that I'm using Request.Querystring in other sub's... but when I put them in your code... I get that error.

well that's a bit dangerous...you will need to use parameters and avoid SQL Server Injection attacks plus the added possibility of errors.

maybe... but due to deadlines...I can not worry about that at the moment... thanks for the tips thou.  Do you know why I am unable to use a Request.Querystring in your example?

Thanks!

Muligan

where are you setting this?

Right inside your SQL call:

Private Const SelectSqlCommandText As String = "SELECT * FROM Customers Where Customer = '" Request.Querystring(Customer)"'"

that's not part of the code and that's wrong syntax.

i feel that's enough for this thread. unsubscribing....