Solved

Save datatable changes to sql database

Posted on 2014-12-14
11
121 Views
Last Modified: 2015-01-02
I have a dataset and data table that I have created and populated using code.  How do I save the data to the sql database?   It would be considered a new record and not an update to and existing record


I am using vb,net saving to sql server database.
0
Comment
Question by:Mike_Stevens
  • 4
  • 4
  • 3
11 Comments
 
LVL 62

Expert Comment

by:Fernando Soto
ID: 40498989
That all depends on how you filled the DataSet / DataTable in code. Did you fill using SqlDataAdapter and filled the data table directly from using the Fill method? Did you just created a DataSet object and manually created a DataTable object and just added records?

Can you please post the code you used to do this.
0
 

Author Comment

by:Mike_Stevens
ID: 40499083
I created the dataset using code and populated it using code.  

- Created using
           dt .Columns.Add("Date_Created", GetType(String))
           dt. Columns.Add("Title", GetType(String))
          ds.Tables.Add(dt)

- Added record
Dim dr As Data.DataRow = ds.Tables(0).NewRow.
     dr("Date_Created") = Now.AddHours(3)
     dr("Rec_Title") = Me.textbox1.Text
     ....
ds.Tables(0).Rows.Add(dr)
0
 
LVL 62

Expert Comment

by:Fernando Soto
ID: 40499088
Hi Mike;

OK, are you yet connecting to the database? If so what technology are you using? For example plane ADO.Net, Linq to SQL, Link To Entity Framework or some other technology.

What database will you be using, Microsoft SQL server, Oracle, MySql or some other?

Does the database and the table you wish to insert records into already exist?
0
Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

 
LVL 33

Expert Comment

by:Mike Eghtebas
ID: 40499091
I am here as Fernando's student.

I think Mike is using untyped dataSet where he starts with

Dim ds as New DataSet

and then he defines columns, rows to add to a table.       In addition to the Dataset object, the Connection, Command, and Data Adapter objects must also be created.

Basically, it seems he needs to build his InsertCommand object to do what he wants to accomplish.

Mike needs to post his complete code.
0
 

Author Comment

by:Mike_Stevens
ID: 40499144
I post my complete code....I am creating the dataset and table using code and then adding records to it (normally only one or two rows of data).  No I need to save the data back to the sqlserver database table that has field names that are the same as the column names in the datatable.
0
 
LVL 33

Expert Comment

by:Mike Eghtebas
ID: 40499270
re:> I post my complete code
Where is your connection code?
Where is your insert command?
Where is your dataadapter?

Why aren't using typed dataset where you don't have to lift finger to save your data to the databse (data is bound to the dgv)?
0
 

Author Comment

by:Mike_Stevens
ID: 40500774
I have a connection string that the project already uses.

- I did not post the insert command because I don't have one yet..
- I did not post the data adapter yet because I don't have one yet..

I posted my question asking why I need to do to save my data and showed what code I had.  It appears I need to create a insert command and a data adapter.
0
 
LVL 62

Expert Comment

by:Fernando Soto
ID: 40501067
Hi Mike;

Try it this way, it is about the most direct way of doing it. Please note the comments in the code.

'' Insert your connection string in the below line of code.
Dim sqlConnection1 As New SqlConnection("Your connection string goes here")

Dim cmd As New SqlCommand
cmd.CommandType = System.Data.CommandType.Text
'' This your SQL Insert command. Make sure that all column names are spelled the same way as in the database
'' Change Buildings to your Table Name, the next list of comma separated names are the column names.
'' The next list of comma separated VALUES are the values to be inserted into the columns in the same order and
'' data types, strings have double quotes around it numeric values do not. 
cmd.CommandText = "INSERT INTO Buildings (LOC_ID, BLDG_CODE, ROOM, CAPACITY) VALUES (15, 'NorthWestern', 209, 50)"
cmd.Connection = sqlConnection1

sqlConnection1.Open()
cmd.ExecuteNonQuery()
sqlConnection1.Close()

Open in new window

Also in the above table Buildings the Primary Key is NOT marked as an Identity and so it needs to be specified as one of the columns in the Insert command. If your Primary Key is marked as an Identity then do NOT include it in the insert command because SQL server will do this automatically for you.
0
 

Author Comment

by:Mike_Stevens
ID: 40504829
Thanks Fernando for your example.  That's what I was looking for .  Where do I call in from ?  What event in the grid?
?
0
 
LVL 62

Expert Comment

by:Fernando Soto
ID: 40504873
Hi Mike;

To your question, "Where do I call it from ?", any place in your code that you wish to insert the data into the database.

To the question, "What event in the grid?", well one place in the grid you can use is the DataGridView.RowLeave Event.
0
 
LVL 33

Accepted Solution

by:
Mike Eghtebas earned 500 total points
ID: 40505018
Because the dataset is untyped there is no row changing event for a specific table therefore you must use the concept of Dynamic even handling and delegates to handle the row changing event.  

Look at this example which is using txtBoxes no a gridview (I hope it helps):
Public Sub Update_Row()      
	drCust.Item("CustomerID") = txtCustomerID.Text
        drCust.Item("CompanyName") = txtCompanyName.Text
        drCust.Item("ContactName") = txtContactName.Text
 
        AddHandler dsNorthwind.Tables("Customers").RowChanging, _
             AddressOf Customer_RowChanging
 
        drCust.EndEdit()
        RemoveHandler dsNorthwind.Tables("Customers").RowChanging, _
            AddressOf Customer_RowChanging
 
End Sub

Open in new window


This is for validation.
Private Sub Customer_RowChanging(ByVal sender As Object, _
	ByVal e As System.Data.DataRowChangeEventArgs)
        '*** Data verification throw exception is invalid 
        Dim strErrors As String
        If txtCustomerID.Text = "" Then
            strErrors = "CustomerID Req."
        ElseIf txtCompanyName.Text = "" Then
            strErrors = "Name Req."
        End If
        If strErrors <> "" Then
            Throw New System.Exception(strErrors)
        End If
 
    End Sub

Open in new window

Maybe Fernando could shape this up to make it for your case.
0

Featured Post

Does Powershell have you tied up in knots?

Managing Active Directory does not always have to be complicated.  If you are spending more time trying instead of doing, then it's time to look at something else. For nearly 20 years, AD admins around the world have used one tool for day-to-day AD management: Hyena. Discover why

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This tutorial demonstrates one way to create an application that runs without any Forms but still has a GUI presence via an Icon in the System Tray. The magic lies in Inheriting from the ApplicationContext Class and passing that to Application.Ru…
The ECB site provides FX rates for major currencies since its inception in 1999 in the form of an XML feed. The files have the following format (reducted for brevity) (CODE) There are three files available HERE (http://www.ecb.europa.eu/stats/exch…
This Micro Tutorial hows how you can integrate  Mac OSX to a Windows Active Directory Domain. Apple has made it easy to allow users to bind their macs to a windows domain with relative ease. The following video show how to bind OSX Mavericks to …
This video shows how to use Hyena, from SystemTools Software, to bulk import 100 user accounts from an external text file. View in 1080p for best video quality.

778 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question