7 Replies - 6313 Views - Last Post: 14 January 2013 - 11:12 AM Rate Topic: -----

#1 djluda  Icon User is offline

  • New D.I.C Head

Reputation: -2
  • View blog
  • Posts: 5
  • Joined: 25-July 12

How to Update a SQL table with data from a DataSet

Posted 11 January 2013 - 02:36 PM

I am pulling data from 1 database(QAD) into a DataSet. I need help trying to Update a table in a different database(Inventory_Test) with the data collected in the DataSet.
Here is how i pull in the data from the databases to the DataSets:
Imports System
Imports System.Data
Imports System.Data.SqlClient
Imports System.Data.DataRelation

Public Class Form1
    Private QAD As SqlDataAdapter
    Private Inventory_Test As SqlDataAdapter
    Private QADStatus As DataSet


    Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

        Dim connectionString As String

        connectionString = "My connection to QAD Database"

        Dim objConn As New SqlConnection(connectionString)
        objConn.Open()
        Dim sqlString As String
        sqlString = ""
        sqlString = "SELECT PT_Part, PT_STATUS, PT_DRWG_SIZE FROM PT_MSTR"

        QAD = New SqlDataAdapter(sqlString, objConn)

        connectionString = ""
        connectionString = "My Connection to Inventory_Test Database"
        objConn = New SqlConnection(connectionString)
        objConn.Open()
        sqlString = ""
        sqlString = "SELECT Inventory_Number, Status, drwg_size FROM Inventory_Test"

        Inventory_Test = New SqlDataAdapter(sqlString, objConn)

        QADStatus = New DataSet
        QAD.Fill(QADStatus, "QAD")
        Inventory_Test.Fill(QADStatus, "Inventory_Test")

        
        Dim QADtable As DataColumn
        Dim InvenTable As DataColumn
        Dim data_relation As DataRelation

        QADtable = QADStatus.Tables("QAD").Columns("PT_Part")
        InvenTable = QADStatus.Tables("Inventory_Test").Columns("Inventory_Number")
        data_relation = New DataRelation("QADtoInventory_Test", QADtable, InvenTable)
        QADStatus.Relations.Add(data_relation)

        DataGridView1.DataSource = QADStatus
   End Sub

Private Sub Button1_Click(sender As System.Object, e As System.EventArgs) Handles Button1.Click
        Inventory_Test.Update(QADStatus, "Inventory_Test")
        MsgBox("SQL Server DB was updated " & Chr(13) & "Check Server Explorer to see changes")
    End Sub



When i run this, nothing happens. Any ideas??

Is This A Good Question/Topic? 0
  • +

Replies To: How to Update a SQL table with data from a DataSet

#2 tlhIn`toq  Icon User is offline

  • Please show what you have already tried when asking a question.
  • member icon

Reputation: 5677
  • View blog
  • Posts: 12,211
  • Joined: 02-June 10

Re: How to Update a SQL table with data from a DataSet

Posted 11 January 2013 - 03:42 PM

Q:... how to do x,y,z with a database {probably for the first time}...
A: Read this tutorial
Entire section of tutorials
Parameterizing Your SQL Queries: The RIGHT Way To Query A Database.
Using SqlDependency to monitor SQL database changes


There is also similar conversation going on just a few below yours
http://www.dreaminco...date-statement/
Was This Post Helpful? 0
  • +
  • -

#3 djluda  Icon User is offline

  • New D.I.C Head

Reputation: -2
  • View blog
  • Posts: 5
  • Joined: 25-July 12

Re: How to Update a SQL table with data from a DataSet

Posted 14 January 2013 - 08:53 AM

I already read all this and that. Would you like a screen shot on my HISTORY/Cookies...I know to first research before posting a question...I just need to know how to take the data from the DataSet(QADStatus) into Database2(PT_MSTR_TEMP).

[code]
Imports System
Imports System.Data
Imports System.Data.SqlClient
Imports System.Data.DataRelation

Public Class Form1
Public QAD As SqlDataAdapter
Public Inventory_Test As SqlDataAdapter
Public QADStatus As DataSet


Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

Dim connectionString As String

connectionString = "SQL Database"

Dim objConn As New SqlConnection(connectionString)
objConn.Open()
Dim sqlString As String
sqlString = ""
sqlString = "SELECT PT_Part, PT_STATUS, PT_DRWG_SIZE FROM PT_MSTR"

QAD = New SqlDataAdapter(sqlString, objConn)

QADStatus = New DataSet
QAD.Fill(QADStatus, "QAD")
DataGridView1.DataSource = QADStatus.Tables(0)

End Sub
Private Sub Button1_Click(sender As System.Object, e As System.EventArgs) Handles Button1.Click
Dim DA As SqlDataAdapter = New SqlDataAdapter
Dim Parm As New SqlParameter
Parm.Value = DataGridView1.CurrentRow.Index

Dim connString As String
connString = "SQL CONNECTION"
Dim conn As New SqlConnection(connString)
conn.Open()

Dim sc As New SqlCommand("Insert Into PT_MSTR_TEMP(PT_PART, PT_STATUS, PT_DRWG_SIZE) " & "SELECT PT_PART, PT_STATUS_PT_DRWG_SIZE FROM @MyTable", conn)
sc.Parameters.Add(Parm)
sc.Parameters.AddWithValue("@MyTable", QADStatus)
sc.ExecuteNonQuery()
MsgBox("SQL Server DB was updated successfully" & Chr(13) & "Check Server Explorer to see changes")
End Sub
End Class
Was This Post Helpful? 0
  • +
  • -

#4 djluda  Icon User is offline

  • New D.I.C Head

Reputation: -2
  • View blog
  • Posts: 5
  • Joined: 25-July 12

Re: How to Update a SQL table with data from a DataSet

Posted 14 January 2013 - 09:59 AM

"SqlBulkCopy"
reply time 7 minutes

Thank you sqlmaster from another website



tlhIn`toq
Re: How to Update a SQL table with data from a DataSet
Posted 11 January 2013 - 03:42 PM


Q:... how to do x,y,z with a database {probably for the first time}...
A: Read this tutorial
Entire section of tutorials
Parameterizing Your SQL Queries: The RIGHT Way To Query A Database.
Using SqlDependency to monitor SQL database changes


There is also similar conversation going on just a few below yours
http://www.dreaminco...date-statement/
My latest tutorials:

Classes & Objects

Properties

Request a tutorial


Popular articles:

I don't know where to start on my problem"


What does this error mean?

Tutorial on finding and fixing the most common errors.



The volunteers here put in lots of hours for free to help you out. If their advice does help you then give them a point by clicking that green (+). Its the only sugar they get.
Was This Post Helpful? !-!ell NO!!!![/b]
Was This Post Helpful? -2
  • +
  • -

#5 DarenR  Icon User is offline

  • D.I.C Lover

Reputation: 498
  • View blog
  • Posts: 3,317
  • Joined: 12-January 10

Re: How to Update a SQL table with data from a DataSet

Posted 14 January 2013 - 10:05 AM

dont insult people who come here to help you out... fast way of not getting help.....
Was This Post Helpful? 0
  • +
  • -

#6 djluda  Icon User is offline

  • New D.I.C Head

Reputation: -2
  • View blog
  • Posts: 5
  • Joined: 25-July 12

Re: How to Update a SQL table with data from a DataSet

Posted 14 January 2013 - 10:14 AM

View PostDarenR, on 14 January 2013 - 10:05 AM, said:

dont insult people who come here to help you out... fast way of not getting help.....


How was that helping me....I ask a question because I spent 8 hours searching and reading forums, posts and when I felt like I had a legit question....I get an answer of "KEEP LOOKING"....how is that helpful. I mean no disrespect to you or anyone else, ask the users and sites i have donated!
Was This Post Helpful? 0
  • +
  • -

#7 DarenR  Icon User is offline

  • D.I.C Lover

Reputation: 498
  • View blog
  • Posts: 3,317
  • Joined: 12-January 10

Re: How to Update a SQL table with data from a DataSet

Posted 14 January 2013 - 10:29 AM

are you getting erros in your code?
Was This Post Helpful? 0
  • +
  • -

#8 djluda  Icon User is offline

  • New D.I.C Head

Reputation: -2
  • View blog
  • Posts: 5
  • Joined: 25-July 12

Re: How to Update a SQL table with data from a DataSet

Posted 14 January 2013 - 11:12 AM

View PostDarenR, on 14 January 2013 - 10:29 AM, said:

are you getting erros in your code?


No errors...
I am now using SqlBulkCopy:

Imports System
Imports System.Data
Imports System.Data.SqlClient

Public Class frmUpdataStatLC
    Public PtMaster As System.Data.Odbc.OdbcDataAdapter
    Public qadstatlc As DataSet
    Public PtMasterTemp As SqlDataAdapter
    Public qadlcstat As DataSet

    Private Sub frmUpdataStatLC_Load(sender As System.Object, e As System.EventArgs) Handles MyBase.Load
        Label1.Text = frmLogIn.UserName & " is Logged In"
        Dim destinationString As String
        Dim QADconnection As New System.Data.Odbc.OdbcConnection


        destinationString = "Data Source = ; Initial Catalog = ; User Id = ; Password = "
        QADconnection.ConnectionString = "DSN=" 'Progress Database
        QADconnection.Open()

        Dim cmdQADdata As Odbc.OdbcCommand = New Odbc.OdbcCommand("SELECT PT_Part, PT_STATUS, PT_DRWG_SIZE FROM
        PT_MSTR", QADconnection) 
        Dim reader As Odbc.OdbcDataReader = cmdQADdata.ExecuteReader

        Using destinationConnection = New SqlConnection(destinationString)
            destinationConnection.Open()

            Using bulkCopy As SqlBulkCopy = New SqlBulkCopy(destinationConnection)
                bulkCopy.DestinationTableName = "db.PT_MSTR_TEMP"

                Try
                    bulkCopy.WriteToServer(reader)
                    MsgBox("Data Loaded", MsgBoxStyle.Information)
                Catch ex As Exception
                    MsgBox("Error :" & ex.Message)
                Finally
                    reader.Close()
                End Try

            End Using



        End Using

    End Sub




Thank you
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1