Database

Richard.Berry100 wrote:

only one row based on conditions in the WHERE clause

Richard.Berry100 wrote:

but I was wondering if there was a way to eliminate declaring a DataTable, and then equating the DataRow to the first row from the DataTable?

Bastard Programmer from Hell

cjb110 wrote:

*only* develop SSIS packages on a machine with a full version of SQL Server

Richard.Berry100 wrote:

I posted a question on the same topic, a few days ago

Bastard Programmer from Hell

Provider=Microsoft.ACE.OLEDB.12.0;Data source= C:\myDb.accdb; Jet OLEDB:Database Locking Mode=1
'Locking mode: 0 = Page Level, 1 = Record Level

Friend Function AddStkReceived(ByVal TransactType As String, ByVal StockCode As String, ByVal WH As String, ByVal Qty As Double, ByVal Comment As String) As Boolean
        Dim sucess As Boolean = False
        If IsNothing(Comment) Then Comment = ""
        Dim params() As String = {Qty, StockCode, WH}
        Dim strUpdate As String = "UPDATE stock_qty SET  Qty = Qty + ? WHERE StockCode = ? AND WH = ?"

        If Not DA.UpdateRecord2(strUpdate, params) Then
            sucess = False
        Else
            sucess = True
        End If

        If sucess Then
            If Not WriteStkHist(StockCode, Qty, TransactType, WH, Comment) Then
                sucess = False
            Else
                sucess = True
            End If
        End If

        Return sucess
    End Function

Public Function UpdateRecord2(ByVal strUpdate As String, params() As String) As Boolean
        Dim RecordsUpdated As Integer = 0
        Dim result As Boolean = False

        Using cn As New OleDb.OleDbConnection(cnStr)
            Dim da As OleDb.OleDbDataAdapter = New OleDb.OleDbDataAdapter
            Dim cmd As OleDb.OleDbCommand
            cmd = New OleDb.OleDbCommand(strUpdate, cn)
            cmd.CommandTimeout = 30
            For i As Integer = 0 To params.Length - 1
                If params(i) = Nothing Then params(i) = ""
                cmd.Parameters.AddWithValue("?", params(i))
            Next
            da.InsertCommand = cmd
            Try
                cn.Open()
                RecordsUpdated = da.InsertCommand.ExecuteNonQuery()
                cn.Close()
                If RecordsUpdated = 1 Then
                    result = True
                Else
                    result = False
                End If
            Catch ex As Exception
                 result = False
            Finally
                cn.Close()
            End Try
        End Using

        Return result


    End Function

Public Function InsertRecord2(ByVal strInsert As String, params() As String) As Boolean

       Using cn As New OleDb.OleDbConnection(cnStr)
           Dim da As New OleDb.OleDbDataAdapter
           Dim cmd As New OleDb.OleDbCommand(strInsert, cn)
           cmd.CommandTimeout = 30

           For i As Integer = 0 To params.Length - 1
               If params(i) = Nothing Then params(i) = ""
               cmd.Parameters.AddWithValue("@" & i.ToString, params(i))
           Next

Richard.Berry100 wrote:

Then I loop through the array and add the parameters. So i need to change all the query strings in the app (there are lots of them )

Richard.Berry100 wrote:

My other concern is that the company uses Small Business Server, which I understand also uses SSE, and I'm terrified I'm going to mess up the server when I install another instance of SSE....

Bastard Programmer from Hell