Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Save datatable changes to sql database

Posted on 2014-12-14
11
Medium Priority
?
127 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 4
  • 3
11 Comments
 
LVL 64

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 64

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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
LVL 34

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 34

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 64

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 64

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 34

Accepted Solution

by:
Mike Eghtebas earned 2000 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

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Article by: Kraeven
Introduction Remote Share is a simple remote sharing tool, enabling you to see, add and remove remote or local shares. The application is written in VB.NET targeting the .NET framework 2.0. The source code and the compiled programs have been in…
A while ago, I was working on a Windows Forms application and I needed a special label control with reflection (glass) effect to show some titles in a stylish way. I've always enjoyed working with graphics, but it's never too clever to re-invent …
This is my first video review of Microsoft Bookings, I will be doing a part two with a bit more information, but wanted to get this out to you folks.
Want to learn how to record your desktop screen without having to use an outside camera. Click on this video and learn how to use the cool google extension called "Screencastify"! Step 1: Open a new google tab Step 2: Go to the left hand upper corn…
Suggested Courses

704 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