Add data to a datatable 1

[accjrf]

Hi, I am new to VB. I am actually using Microsoft VB 2008 Express Edition so I hope I am posting in the right place. I have used VBA for many years but I am trying to learn VB and I am having trouble with probably the easiest code to most of you. I have searched and read for hours on msdn for help but I am totally stumped.

I have created a table and am trying to just add data to it but cant seem to accomplish this.

Lets say I have Table1 under the tab DataSet1.xsd. This table has some columns called Customer Name and Date. I want to populate those 2 columns with "John" and "12/15/09". Can someone tell me what my Dim statement should be and also the following code to update those rows?

Thanks

 

[Andrzejek]

Right from the start I would strongly recomend to avoid using reserved words for your column names, like Date or Name. Use something like Cust_Date or Date_of_Purchase.

Also, life is a lot easier when you do not use 'multiple words' column names like 'Customer Name'. Consider 'Customer_Name' (since Name is another reserved word.)

Have fun.

---- Andy

 

[accjrf]

Andy,

Thanks for the tips. I was just using a made up example so someone could direct me on how to write what I am trying to do.

Any help with how to do it?

 

[Bluejay07]

First thing I would check is if you have a valid connection to the SQL database (eg. Did you set the connection string and open a connection?)

If you need help with connections, this is a good site:

http://www.connectionstrings.com/

 

[accjrf]

Maybe I am missing something. In Access if I have a local table "Table1", I can refer to it in VBA and update records on it.

Example:

Dim D1 as Database
Dim R1 as Recordset

Set D1 = currentdb()
Set R1 = D1.openrecordset("Table1")

R1.Addnew
R1("Cust_name") = "John"
R1("DOS") = Date()
R1.Update

This is what I am trying to do in VB. I dont need a connection string in Access because it is a local table in my own database.

I have created a datatable in a project and just want to update the rows with information. I have the info to update it with, but i am having trouble getting it to the table that I've created.

 

[Sorwen]

    Private Sub PlayWithTable()
        Dim dtCurrent As New DataTable
        dtCurrent.Columns.Add("CustomerName", System.Type.GetType("System.String"))
        dtCurrent.Columns.Add("SomeDate", System.Type.GetType("System.DateTime"))

        Dim drCurrent As DataRow = dtCurrent.NewRow
        drCurrent("CustomerName") = "John"
        drCurrent("SomeDate") = "#12/15/09#"

        dtCurrent.Rows.Add(drCurrent)
    End Sub

That is how you add data to a table (DataTable) and that does absolutely nothing for you as the table is gone right after it hits "End Sub". Because you are not working directly in a DataBase it isn't as simple as "How do I add data to a table". First, where is the data you are adding to coming from? Then where is the data "John" and "12/15/09" coming from? Are you adding just that line or multiple lines? Do you want apple juice or orange juice? You still have all of the questions you would ask yourself as if you where creating something for use in Access the only difference is now you may have to do it in multiple places with a few different words.

Think about all of what you are doing and then tell us and then maybe we can suggest something a little more exact. We will help you with a conversion or point you in the right direction. We will not build it for you. The thing is it isn't as simple as just doing it though.

-I hate Microsoft!
-Forever and always forward.
-My kingdom for a edit button!

 

[Sorwen]

Let me approach this another way at the same time. Assuming you got data step right what is "Table1 under the tab DataSet1.xsd"? .xsd is usually a XML Schema file, but it doesn't have to be. Are you saying you have xsd file that you are getting data from? That still gives no meaning (for me at least) Table1 under tab DataSet1.xsd because in VB.Net you don't have anything unless you put it there. So what did you put there? A TabControl with a DataGridView or are you saying you have this in something else like Access?

-I hate Microsoft!
-Forever and always forward.
-My kingdom for a edit button!

 

[accjrf]

Sorwen,

Answering your second post...

Table1 under the tab DataSet1.xsd is the table I created to put the data in to store it. I am going out to an internet page, using variables to hold several pieces of data from that site and I want to stick that data into Table1 at the end of the code. After inserting 1 row of data, the code has a loop to gather other info from another page so the table will have several rows in it when done. Everything in my code works just fine except I dont think I am putting any of the information into the table that I've created.

To create the table I clicked on "Project", "Add New Item", "DataSet", then right-clicked on the screen to add "Data Table". From there, I added all my column headings to refer to in the code. I have one called "Title" and another called "Created_By".

here is the part of the code that I thought would allow me to insert the data into that table..

Dim workTable As DataTable = New DataTable("Table1")
Dim MyDataRow = workTable.NewRow()

'Go to web page and gather data using variables called webTitle and webCreatedBy

MyDataRow.Title = webTitle
MyDataRow.Created_by = webCreatedBy
workTable.Rows.Add(MyDataRow)

'Loop for more data and continue until finished

The code runs completely through with no errors but I don't think anything is being put into my table.

Thanks for looking at this.

 

[Sorwen]

Ok. This: "To create the table I clicked on "Project", "Add New Item", "DataSet", then right-clicked on the screen to add "Data Table"." makes this: "Table1 under the tab DataSet1.xsd" make sense now. The xsd is just a visual way for you to see what is happening, but doesn't change how you work with the DataSet. So basically what you have is a DataSet with a global scope within your class (so it goes away if your form closes). So say your code is like what you showed above:

Dim workTable As DataTable = New DataTable("Table1")
Dim MyDataRow = workTable.NewRow()

[red]For ...[/red]
'Go to web page and gather data using variables called webTitle and webCreatedBy
[red]Next[/red]

MyDataRow.Title = webTitle
MyDataRow.Created_by = webCreatedBy
workTable.Rows.Add(MyDataRow)

And assumeing for the moment that is how you loop through it is something like that (since you didn't indicate where) then this is how you would code it.

        Dim workTable As DataTable = Me.DataSet1.Tables("Table1") [green]'You can use an integer (0 for the first table) or you can use the tables name.[/green]

        [red]For ...[/red]
            [green]'Go to web page and gather data using variables called webTitle and webCreatedBy[/green]

            Dim MyDataRow [red]As DataRow[/red] = workTable.NewRow()
            MyDataRow[red]("Title")[/red] = webTitle
            MyDataRow[red]("Created_by")[/red] = webCreatedBy
            workTable.Rows.Add(MyDataRow)
        [red]Next[/red]

-I hate Microsoft!
-Forever and always forward.
-My kingdom for a edit button!

 

[accjrf]

Dim workTable As DataTable = Me.DataSet1.Tables("Table1")

I get an error that says "Dataset1 is not a member of the form name that I have the button on to execute the code.

Do I have to link them somehow? And you are saying that if I close the form, that my data disappears with it leaving an empty shell of a table? Does VB not allow you to store data in a table until the user chooses to delete or overwrite it?

Thank you for all your help. this part of it is so much different than VBA is.

 

[Sorwen]

A "Table" (DataTable) is only in memory unless you save it to a database like Access, SQL, etc. When you drag add a DataSet to a form that DataSet only exists so long as that form does. I was a little off on my explanation because a for isn't necessarily disposed when it is closed depending on how it was opened. That said the problem right now is how your program is set up. If this doesn't work you will need to show more of your code before I'll be able to help.

I assume that you have a form (Form1) that loads when it starts. Then on that form is the DataSet? Then you hit a button that opens another form and that is the form you are getting the web info from? If that is the case then you need to use the name of that first form instead. So if it was Form1 then instead of "Dim workTable As DataTable = Me.DataSet1.Tables("Table1")" it would be "Dim workTable As DataTable = [red]Form1[/red].DataSet1.Tables("Table1")".

It is really hard working blindly like this so if that doesn't work you'll need to post more code and a more indepth explination of what is going on. However I pretty sure that should do it for you so we will see.

-I hate Microsoft!
-Forever and always forward.
-My kingdom for a edit button!

 

[accjrf]

I did not realize that I had to drag the DataSet onto the form. I thought it would populate the table and then I could open the table from the .xsd tab.

However, this line of code:

"Dim workTable As DataTable = Me.DataSet1.Tables("Table1")"

does not produce an error at the bottom of the screen but gives me an error as soon as I try to run the code which states...

"InnerException: use the "new" keyword to create an object instance"
and
"InnerException: Check to determine if object is null"

I tried to modify the code as such:

"Dim workTable As NEW DataTable = Me.DataSet1.Tables
("Table1")"

but it gives me an error stating "End of Statement expected"

I feel like you are helping me to get closer...I really appreciate it!

 

[Sorwen]

It isn't visually on the form it will show up on a bar under the form in the designer window. It is still a part of that form so it is "on" the form in a code sense. It would be more correct to say it is part of the forms class. Check your DataTable Name. Basically that error means that when Me.DataSet1 tried to pass back "Table1" it didn't find one so workTable is nothing. If it is the only table you have then just use Me.DataSet1.Tables(0).

-I hate Microsoft!
-Forever and always forward.
-My kingdom for a edit button!

 

[accjrf]

Sorwen,

Ok, I think that solved all my issues. Thank you so much for all your help! You def earned a Star!

 

[Sorwen]

NP. Glad we got it worked out.

-I hate Microsoft!
-Forever and always forward.
-My kingdom for a edit button!