Solved

Save datatable changes to sql database

Posted on 2014-12-14
11
115 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
 
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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Introduction As chip makers focus on adding processor cores over increasing clock speed, developers need to utilize the features of modern CPUs.  One of the ways we can do this is by implementing parallel algorithms in our software.   One recent…
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…
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
This video gives you a great overview about bandwidth monitoring with SNMP and WMI with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're looking for how to monitor bandwidth using netflow or packet s…

707 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

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now