Visual Basic

C#Coudou wrote:

How can I join an access table with sql server table using vb.net

Function Load_Table(ByVal SQLString As String, ByVal TableName As String) As Boolean

       Dim SQLstr As String
       Dim tableISadded As Boolean = False
       Try
           'open connection
           If SQLcon.State = ConnectionState.Closed Then
               SQLcon.Open()
           End If
           SQLstr = SQLString
           Dim cmd As SqlCommand = New SqlCommand(SQLstr, SQLcon)
           Dim da As New SqlDataAdapter(SQLstr, SQLcon)
           da.Fill(pDS, TableName)
           da.FillSchema(pDS, SchemaType.Mapped)
           For Each dt As DataTable In pDS.Tables
               If dt.TableName = TableName Then
                   tableISadded = True
               Else
                   tableISadded = False
               End If
           Next
           Return tableISadded
       Catch ex As Exception
           MessageBox.Show(ex.Message)
           Return False
       Finally
           'close connection
           SQLcon.Close()
       End Try

   End Function

              'Remove the orders table
               MAIN.data.DS.Tables.Remove("Orders")
               ''Reload datatable to dataset.
               MAIN.data.Load_Table("SELECT * FROM Orders", "Orders")

Dim pDS As New DataSet

    Public Property DS() As DataSet
        Get
            Return pDS
        End Get
        Set(ByVal value As DataSet)
            pDS = value
        End Set
    End Property

Sub MakeConnect()
        SQLcon.ConnectionString = "Data Source=192.168.10.24\BDMS;" & _
                                        "Initial Catalog=FLATPAN;" & _
                                        "User ID=flatpan;" & _
                                        "Password=*******"
        Try
            SQLcon.Open()
        Catch ex As Exception
            MessageBox.Show(ex.Message)
        End Try

    End Sub
 
Function Load_Table(ByVal SQLString As String, ByVal TableName As String) As Boolean

        Dim SQLstr As String
        Dim tableISadded As Boolean = False
        Try
            'open connection
            If SQLcon.State = ConnectionState.Closed Then
                SQLcon.Open()
            End If
            SQLstr = SQLString
            Dim cmd As SqlCommand = New SqlCommand(SQLstr, SQLcon)
            Dim da As New SqlDataAdapter(SQLstr, SQLcon)
            da.Fill(pDS, TableName)
            da.FillSchema(pDS, SchemaType.Mapped)
            For Each dt As DataTable In pDS.Tables
                If dt.TableName = TableName Then
                    tableISadded = True
                Else
                    tableISadded = False
                End If
            Next
            Return tableISadded
        Catch ex As Exception
            MessageBox.Show(ex.Message)
            Return False
        Finally
            'close connection
            SQLcon.Close()
        End Try

Private Sub Orders_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
       'Load the orders table and lu products table
       MAIN.data.Load_Table("SELECT * FROM Orders", "Orders") 'THIS IS THE DA FILL FOR ORDERS
       MAIN.data.Load_Table("SELECT * FROM LU_Products", "LU_Products")
       MAIN.data.Load_Table("SELECT * FROM Status_Orders", "Status_Orders")
       Calculate_Total_Rows(0, "LOAD") 'SOME FORM ACTIONS
       Fields_Load(rowCounter) 'NOT PART OF UPDATE ONLY TO VIEW DATA
       Fields_State("LOCK") 'LOCK FIELDS SO USER CANT CHANGE
   End Sub

Function Add_Orders_Row() As Boolean
        Try
            Dim RowISAdded As Boolean = False
            If txtQuantity.Text <> "" And txtProductionNo.Text <> "" _
            And txtWeek.Text <> "" And txtPart.Text <> "" _
            And cboProductCode.Text <> "" And txtPart.Text <> "" _
            And txtWorksOrderNo.Text <> "" Then
                Dim dt As DataTable = MAIN.data.DS.Tables("Orders")
                Dim dr As DataRow = dt.NewRow
                dr.Item("OrderStatus") = cboOrderStatus.Text
                dr.Item("SamsungRef") = "NA"
                dr.Item("ProductCode") = cboProductCode.Text
                dr.Item("ProductSerialNo") = txtProductSerialNo.Text
                dr.Item("ProductionNo") = txtProductionNo.Text
                dr.Item("WeekNo") = txtWeek.Text
                dr.Item("PartNo") = CType(txtPart.Text, Integer)
                dr.Item("OrderDate") = dtpOrderDate.Text
                dr.Item("QuantityOrder") = CType(txtQuantity.Text, Integer)
                dr.Item("QuantityScanned") = 0
                dr.Item("WorksOrderNo") = txtWorksOrderNo.Text
                If txtFirstSerial.Text = "" Then
                    dr.Item("FirstSerialNo") = "<SCAN>"
                    dr.Item("LastSerialNo") = "<CALC>"
                Else
                    dr.Item("FirstSerialNo") = txtFirstSerial.Text
                    dr.Item("LastSerialNo") = txtLastSerial.Text
                End If
                dr.Item("timenow") = Date.Now
                dt.Rows.Add(dr)
                'Update new row to database
                Dim qry As String = "SELECT * FROM Orders"
                If MAIN.data.Conn.State = ConnectionState.Closed Then MAIN.data.Conn.Open()
                Dim da1 As New SqlClient.SqlDataAdapter
                da1.SelectCommand = New SqlClient.SqlCommand(qry, MAIN.data.Conn)
                Dim cb1 As SqlClient.SqlCommandBuilder = New System.Data.SqlClient.SqlCommandBuilder(da1)
                da1.Update(MAIN.data.DS, "Orders")
                
                'REMOVE THE DATABLE
                MAIN.data.DS.Tables.Remove("Orders")
                
                'Reatach datatable to dataset. Refresh primary keys
                MAIN.data.Load_Table("SELECT * FROM Orders", "Orders")
                
                'MAKE SURE UPDATE MADE
                Dim findrow As DataRow() = dt.Select("ProductionNo= '" & txtProductionNo.Text & "'")
                If findrow.Length = 1 Then
                    RowISAdded = True
                Else
                    RowISAdded = False
                End If
            Else
                                RowISAdded = False
            End If

            Return RowISAdded
        Catch ex As Exception
            MessageBox.Show("Add_Orders_Row, " & ex.Message)
            Return False
        End Try

    End Function

Dim da As New SqlClient.SqlDataAdapter
            da.SelectCommand = New SqlClient.SqlCommand("SELECT * FROM UNTIS", MAIN.data.Conn)

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

Public Class Example
    Dim SQLcon As New SqlConnection
    Dim pDS As New DataSet

    Sub MakeConnect()
        SQLcon.ConnectionString = "Data Source=192.168.10.24\BDMS;" & _
                                        "Initial Catalog=FLATPAN;" & _
                                        "User ID=flatpan;" & _
                                        "Password=*********"
        Try
            SQLcon.Open()
        Catch ex As Exception
            MessageBox.Show(ex.Message)
        End Try

    End Sub

    Sub Load_Table()
        Dim SQLstr As String = "SELECT * FROM Orders"
        Try
            'open connection
            If SQLcon.State = ConnectionState.Closed Then
                SQLcon.Open()
            End If
            Dim cmd As SqlCommand = New SqlCommand(SQLstr, SQLcon)
            Dim da As New SqlDataAdapter(SQLstr, SQLcon)
            da.Fill(pDS, "ORDERS")
            
            'Added this to see if it helped
            da.FillSchema(pDS, SchemaType.Mapped)
        Catch ex As Exception
            MessageBox.Show(ex.Message)
        Finally
            'close connection
            SQLcon.Close()
        End Try
    End Sub

    Sub Add_New_Record()
        Try
            Dim dt As DataTable = pDS.Tables("Orders")
            Dim dr As DataRow = dt.NewRow
            dr.Item("OrderStatus") = "A"
            dr.Item("SamsungRef") = "B"
            dr.Item("ProductCode") = "C"
            dr.Item("ProductSerialNo") = "D"
            dr.Item("ProductionNo") = "E"
            dt.Rows.Add(dr)

            'Update new row to database
            Dim qry As String = "SELECT * FROM Orders"
            If SQLcon.State = ConnectionState.Closed Then SQLcon.Open()
            Dim da1 As New SqlClient.SqlDataAdapter
            da1.SelectCommand = New SqlClient.SqlCommand(qry, MAIN.data.Conn)
            Dim cb1 As SqlClient.SqlCommandBuilder = New System.Data.SqlClient.SqlCommandBuilder(da1)
            da1.Update(pDS, "Orders")

            'Had to add this to register change in dataset
            pDS.Tables.Remove("Orders")
            'Reatach datatable to dataset. Refresh primary keys
            MAIN.data.Load_Table("SELECT * FROM Orders", "Orders")

        Catch ex As Exception
            MessageBox.Show("Add_New_Record, " & ex.Message)
        End Try
    End Sub
End Class

Dim openedConnectionString As Boolean = False
If Not connectionString.State = ConnectionState.Open Then
    connectionString.Open()
    openedConnectionString = True
End If

Dim insertionDS As New DataSet
Dim newDataAdapter As OleDbDataAdapter

Dim sql As String = "SELECT * FROM [" & ActiveProject.Identifier & " Populations] ORDER BY ID;"
newDataAdapter = New OleDb.OleDbDataAdapter(sql, connectionString)

'Create mapping
newDataAdapter.TableMappings.Add("Table", "Populations")
With newDataAdapter.TableMappings(0).ColumnMappings
    .Add("ID", "ID")
    .Add("Name", "Name")
    .Add("Char ID", "Char ID")
    .Add("Stratum", "Stratum")
    .Add("PopLevel", "PopLevel")
End With

'Make sure field names are bracketed
Dim cb As New OleDbCommandBuilder(newDataAdapter)
cb.QuotePrefix = " ["
cb.QuoteSuffix = "] "

'Add Table
newDataAdapter.Fill(insertionDS)

'Get last ID number to increment
Dim lastIndex As Integer = 0

With insertionDS.Tables("Populations")
    If .Rows.Count <> 0 Then
        lastIndex = .Rows(.Rows.Count - 1).Item("ID")
    End If

    'Insert record
    Dim newRow As DataRow = .Rows.Add

    newRow.BeginEdit()
    newRow.Item("ID") = lastIndex + 1
    newRow.Item("Name") = Population
    newRow.Item("Char ID") = ""
    newRow.Item("Stratum") = Stratum
    newRow.Item("PopLevel") = Level
    newRow.EndEdit()
End With

'Save changes
newDataAdapter.InsertCommand = cb.GetInsertCommand
newDataAdapter.Update(insertionDS)

newDataAdapter.Dispose()

'If we opened the connectionstring, then close it
If openedConnectionString Then
    connectionString.Close()
End If