Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Save datatable changes to sql database

Posted on 2014-12-14
11
Medium Priority
?
128 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 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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
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

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Question has a verified solution.

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

If you're writing a .NET application to connect to an Access .mdb database and use pre-existing queries that require parameters, you've come to the right place! Let's say the pre-existing query(qryCust) in Access takes a Date as a parameter and l…
It was really hard time for me to get the understanding of Delegates in C#. I went through many websites and articles but I found them very clumsy. After going through those sites, I noted down the points in a easy way so here I am sharing that unde…
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an anti-spam), the admin…
Please read the paragraph below before following the instructions in the video — there are important caveats in the paragraph that I did not mention in the video. If your PaperPort 12 or PaperPort 14 is failing to start, or crashing, or hanging, …

824 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