Em Andamento

Help to sort probelm with code

We have had code done a few years ago to automate the stock on our website from our backend database.

We have recently had an upgrade so our internal server is now SQL (was MSDE). For some reason there is now a problem with teh information as it is not correct.

It may be that the database fields have changed although other coding is still working.

What I need is for a coder to be availabel to be able to dial into our machine and find our what is causing the issue.

You must be available at times during GMT hours.

Simple job for person with right skills

## Deliverables

I need the code to run and delvier the right stock to my websites.

We run Vista machines although this should not be a factor.

RAC have no disk space? to upload code so I will post here

xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx

Option Explicit

'Declaration Section

Dim oConnection? ? ? ? ? ? As New [url removed, login to view]

Dim oConnectionRemote As New [url removed, login to view]

Dim oAdoRs? ? ? ? ? ? ? ? ? ? ? As [url removed, login to view]

Dim oAdoRs1? ? ? ? ? ? ? ? ? ? ? As [url removed, login to view]

Dim sConnectionString As String

Dim sConnectionStringRemote As String

Dim sQuery? ? ? ? ? ? ? ? ? ? ? As String

Dim sLogFile? ? ? ? ? ? ? ? ? As String

'*******************************? Main Function **************************************

Private Sub Main()

On Error GoTo ErrTrap

? ? ? Dim sUserName As String, sPassword As String, sServerName As String, sDBName As String

? ? ? Dim sUserNameRemote As String, sPasswordRemote As String, sServerNameRemote As String, sDBNameRemote As String

? ? ?

? ? ? 'Setting the remote databse connection string

? ? ? sServerNameRemote = "[url removed, login to view]"

? ? ? sDBNameRemote = "thomasrj"

? ? ? sUserNameRemote = "thomasrj"

? ? ? sPasswordRemote = "np109fq"

? ? ?

'? ? ? Setting the local databse connection string

'''? ? ? sServerName = "(local)"

'''? ? ? sDBName = "Advancepro"

'''? ? ? sUserName = "sa"

'''? ? ? sPassword = ""

? ? ? sServerName = "mainserverpc\ADVANCEPRO"

? ? ? sDBName = "Advance"

? ? ? sUserName = "sa"

? ? ? sPassword = "VIA58nMay21"

? ? ? sConnectionString = "Provider=SQLOLEDB.1;Password='" & sPassword & "';Persist Security Info=True;User ID='" & sUserName & "';Initial Catalog='" & sDBName & "';Data Source='" & sServerName & "'"

? ? ?

? ? ? ActivityLog "*********************Stock updation started**********************"

? ? ?

? ? ? 'Database connection

? ? ? Set oConnection = New [url removed, login to view]

? ? ? With oConnection

? ? ? ? ? ? ? .ConnectionTimeout = 0

? ? ? ? ? ? ? .CommandTimeout = 0

? ? ? ? ? ? ? .CursorLocation = adUseClient

? ? ? ? ? ? ? .Open sConnectionString

? ? ? End With

? ? ? ActivityLog "Database connected "

? ? ?

? ? ? sConnectionStringRemote = "Provider=SQLOLEDB.1;Password='" & sPasswordRemote & "';Persist Security Info=True;User ID='" & sUserNameRemote & "';Initial Catalog='" & sDBNameRemote & "';Data Source='" & sServerNameRemote & "'"

? ? ? 'Remote Database connection

? ? ? Set oConnectionRemote = New [url removed, login to view]

? ? ? With oConnectionRemote

? ? ? ? ? ? ? .ConnectionTimeout = 0

? ? ? ? ? ? ? .CommandTimeout = 0

? ? ? ? ? ? ? .CursorLocation = adUseClient

? ? ? ? ? ? ? .Open sConnectionStringRemote

? ? ? End With

? ? ? ActivityLog "Database remote connected "

? ? ?

? ? ? ? ? ? ?

? ? ? 'sQuery = "SELECT B.C002_product_sku,C.C014_QUANTITY,[url removed, login to view] " & _

? ? ? ? ? ? ? " FROM OPENROWSET('MSDASQL'," & _

? ? ? ? ? ? ? " 'DRIVER={SQL Server};SERVER=" & sServerNameRemote & ";UID=" & sUserNameRemote & ";PWD=" & sPasswordRemote & "'," & _

? ? ? ? ? ? ? " " & sDBNameRemote & ".[url removed, login to view]) AS A, " & _

? ? ? ? ? ? ? " T002_PRODUCTS AS B,T014_INVENTORY As C? " & _

? ? ? ? ? ? ? " Where B.C002_PRODUCT_ID = C.C014_INVENTORY_ID And [url removed, login to view] = B.C002_product_sku " & _

? ? ? ? ? ? ? " AND convert(int,C.C014_QUANTITY) <> convert(int,[url removed, login to view]) " & _

? ? ? ? ? ? ? " AND WHERE Partno = '2001'" & _

? ? ? ? ? ? ? " ORDER BY B.C002_product_sku"

? ? ? ? ? ? ?

? ? ? sQuery = "SELECT B.C002_product_sku,C.C014_QUANTITY " & _

? ? ? ? ? ? ? " FROM T002_PRODUCTS AS B,T014_INVENTORY As C? " & _

? ? ? ? ? ? ? " WHERE B.C002_PRODUCT_ID = C.C014_INVENTORY_ID " & _

? ? ? ? ? ? ? " ORDER BY B.C002_product_sku"

? ? ? ? ? ? ?

? ? ? 'Fetching product match records and stock quantity not equal in both table

? ? ? Set oAdoRs = New [url removed, login to view]

? ? ? [url removed, login to view] sQuery, oConnection, adOpenDynamic, adLockOptimistic, adCmdText

? ? ? Dim iQuantity As Integer, iStockLevel As Integer

? ? ? If Not [url removed, login to view] Then

? ? ? ? ? ? ? Do While Not [url removed, login to view]

? ? ? ? ? ? ? ? ? ? ?

? ? ? ? ? ? ? ? ? ? ? 'ActivityLog "partno: " & oAdoRs("C002_product_sku") & "-Qty:" & oAdoRs("C014_QUANTITY")

? ? ? ? ? ? ? ? ? ? ? sQuery = "SELECT [url removed, login to view] FROM [Stock] AS A " & _

? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? " WHERE [url removed, login to view] = '" & oAdoRs("C002_product_sku") & "'"

? ? ? ? ? ? ? ? ? ? ? Set oAdoRs1 = New [url removed, login to view]

? ? ? ? ? ? ? ? ? ? ? [url removed, login to view] sQuery, oConnectionRemote, adOpenDynamic, adLockOptimistic, adCmdText

? ? ? ? ? ? ? ? ? ? ? If Not [url removed, login to view] Then

? ? ? ? ? ? ? ? ? ? ? ? ? ? ? iQuantity = Val(oAdoRs("C014_QUANTITY"))

? ? ? ? ? ? ? ? ? ? ? ? ? ? ? iStockLevel = Val(oAdoRs1("StockLevel"))

? ? ? ? ? ? ? ? ? ? ? ? ? ? ? 'ActivityLog "partno: " & oAdoRs("C002_product_sku") & "-Stock Qty:" & iStockLevel

? ? ? ? ? ? ? ? ? ? ? ? ? ? ? If iQuantity <> iStockLevel Then

? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ActivityLog "partno: " & oAdoRs("C002_product_sku") & Space(25 - Len(oAdoRs("C002_product_sku"))) & "Qty:" & iQuantity & Space(15 - Len(iQuantity)) & "Stock Qty:" & iStockLevel

? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ?

? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? 'Stock updation in remote database

? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? sQuery = "UPDATE [Stock] " & _

? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? " SET StockLevel = " & iQuantity & " " & _

? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? " WHERE Partno = '" & oAdoRs("C002_product_sku") & "' "

? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? [url removed, login to view] (sQuery)

? ? ? ? ? ? ? ? ? ? ? ? ? ? ? End If

? ? ? ? ? ? ? ? ? ? ? End If

? ? ? ? ? ? ? ? ? ? ? [url removed, login to view]

? ? ? ? ? ? ? ? ? ? ? Set oAdoRs1 = Nothing

? ? ? ? ? ? ? ? ? ? ?

? ? ? ? ? ? ? ? ? ? ?

? ? ? ? ? ? ? ? ? ? ? [url removed, login to view]

? ? ? ? ? ? ? Loop

? ? ? ? ? ? ? [url removed, login to view]

? ? ? End If

? ? ? 'Connection closing

? ? ? Set oAdoRs = Nothing

? ? ? [url removed, login to view]

? ? ? Set oConnection = Nothing

? ? ? [url removed, login to view]

? ? ? Set oConnectionRemote = Nothing

? ? ? ActivityLog "Database closed"

? ? ? ActivityLog "*********************Stock updation completed********************"

? ? ? End

? ? ? Exit Sub

ErrTrap:

? ? ? ActivityLog [url removed, login to view]

? ? ? End

End Sub

'*******************************? Log file creation **************************************

Public Sub ActivityLog(sLog As String)

? ? ? If sLogFile = "" Then

? ? ? ? ? ? ? sLogFile = Format(Now, "DD-MM-YYYY-HHMMSS-AM/PM") & ".log"

? ? ? ? ? ? ? Open [url removed, login to view] & "\" & sLogFile For Output As #2 ' New file.

? ? ? Else

? ? ? ? ? ? ? Open [url removed, login to view] & "\" & sLogFile For Append As #2 ' Append file.

? ? ? End If

? ? ? Print #2, Now & " - " & sLog

? ? ? Close #2

End Sub

'********************************** End of program ****************************************

Habilidades: Engenharia, MySQL, PHP, Gestão de projetos, Arquitetura de software, Teste de Software, SQL, Visual Basic

Ver mais: where to find a coder, where do i find a coder, what is sql coding, what is job id, string match, sort function c, remote coding php, problem with error, probelm, need to find a coder, match string, integer factor, id to code, find code, driver select, driver job, dial a code, c sort function, catalog creation, basic coding skills, info product creation, find a code app, remote php job, remote job php, find an app coder

Acerca do Empregador:
( 18 comentários ) United Kingdom

ID do Projeto: #2984435

Premiar a:

sankettshah

See private message.

$12 USD em 2 dias
(161 Avaliações)
6.0

4 freelancers estão ofertando em média $25 para este trabalho

agupta14

See private message.

$17 USD in 2 dias
(24 Comentários)
4.4
rajivambegaokar

See private message.

$51 USD in 2 dias
(3 Comentários)
1.5
wizardguru

See private message.

$21.25 USD in 2 dias
(3 Comentários)
0.0