Database

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

Quote:

Seems you already have the code to add parameters. How much could you change using "search and replace" over, say, the weekend? Smile |

Quote:

..adding another instance of Sql Server would make the entire PC somewhat slower. Adding another server would be costly too.

Is at a large database? How many tables are we talking?

Richard.Berry100 wrote:

Busy doing this now - Its a long weekend...

Richard.Berry100 wrote:

There are about 20 tables, some of the tables have about 4000 records (stock_qty)

In a month they added about 4000 rows to the Sales Order Detail table, each of which has a correspronding entry in a Stock History table

 

The Access db is about 4MB

Bastard Programmer from Hell

Richard.Berry100 wrote:

I'd get someone who knows a bit more about servers to assist!

Richard.Berry100 wrote:

I was leaning towards them getting another PC on the network and put and instance of SSE (with different instance name to the default) on that... Perhaps a performance loss but less risk

Bastard Programmer from Hell

SELECT a1.EmpID, COUNT(l1.LeaveID) as LeaveRecords FROM
Attendance a1 LEFT JOIN
Leave l1 ON l1.EmpID = a1.EmpID AND a1.Att_Date BETWEEN l1.LeaveFrom AND l1.LeaveTo
GROUP BY a1.EmpID

C:>sqlcmd /?
Microsoft (R) SQL Server Command Line Tool
Version 10.50.2500.0 NT x64
Copyright (c) Microsoft Corporation.  All rights reserved.

usage: Sqlcmd            [-U login id]          [-P password]
  [-S server]            [-H hostname]          [-E trusted connection]
  [-N Encrypt Connection][-C Trust Server Certificate]
  [-d use database name] [-l login timeout]     [-t query timeout]
  [-h headers]           [-s colseparator]      [-w screen width]
  [-a packetsize]        [-e echo input]        [-I Enable Quoted Identifiers]
  [-c cmdend]            [-L[c] list servers[clean output]]
  [-q "cmdline query"]   [-Q "cmdline query" and exit]
  [-m errorlevel]        [-V severitylevel]     [-W remove trailing spaces]
  [-u unicode output]    [-r[0|1] msgs to stderr]
  [-i inputfile]         [-o outputfile]        [-z new password]
  [-f <codepage> | i:<codepage>[,o:<codepage>]] [-Z new password and exit]
  [-k[1|2] remove[replace] control characters]
  [-y variable length type display width]
  [-Y fixed length type display width]
  [-p[1] print statistics[colon format]]
  [-R use client regional setting]
  [-b On error batch abort]
  [-v var = "value"...]  [-A dedicated admin connection]
  [-X[1] disable commands, startup script, enviroment variables [and exit]]
  [-x disable variable substitution]
  [-? show syntax summary]