Follow @dporton

JustCode : Update a record in a SQL Table

In this example we will show you how to update a record in a VDATA table.

 
This is the routing we used:

Routing(Web2)
    Dim oInfo

    Set oInfo = CreateObject("Scripting.Dictionary")
        oInfo.Add("Message","")
        oInfo.Add("ID",-1)

    Set oInfo = FindVDataRecord("MyServer","ADO","VDATA","Respondent.Serial","Name = 'Test'","MyAdmin","ABCD",oInfo)

    ConnectionInfo.Label.Inserts["Info"] = Ctext(oInfo.item["Message"])
    ConnectionInfo.Show()

    If ( oInfo.item["ID"] <> -1  )  Then

        ' Lets Update the Record
        Set oInfo = UpdateVDataRecord("MyServer","ADO","VDATA","Name = 'DELETE'","Respondent.Serial = " + CTEXT(oInfo.item["ID"]),"MyAdmin","ABCD",oInfo)
        ConnectionInfo.Label.Inserts["Info"] = Ctext(oInfo.item["Message"])
        ConnectionInfo.Show()
    End If

    Name.Ask()


Function InsertVDataRecord(sServer,sDatabase,sTable,sFields,sValues,sUser,sPassword,oReturnObject)
    Dim oConnection, oRecordset
    Dim sInfo ,sConnection, sSQL

On Error Goto ErrorMessage

    Set oConnection = CreateObject("ADODB.Connection")

        sConnection = "Provider=mrOleDB.Provider.2;Data Source=mrRdbDsc2;"
        sConnection = sConnection + "Location='Provider=SQLOLEDB.1;Password=" + sPassword
        sConnection = sConnection + ";Persist Security Info=True;User ID="
        sConnection = sConnection + sUser + ";Initial Catalog=" + sDatabase
        sConnection = sConnection + ";Data Source=" + sServer
        sConnection = sConnection + "';Initial Catalog=\\" + sServer
        sConnection = sConnection + "SPSSMR_FMROOT\Master" + sDatabase + ""
        sConnection = sConnection + sDatabase + ".mdd;MR Init Project=" + sDatabase

        oConnection.Open(sConnection)

        If ( oConnection.State = 1 ) Then

            sSQL = "INSERT INTO " + sTable + " (" + sFields + ") VALUES " + "(" + sValues + ")"

            oConnection.Execute(sSQL)

            oReturnObject.Remove("Message")
            oReturnObject.Add("Message", "Connection to the Database worked, Insert Complete")

        Else

            oReturnObject.Remove("Message")
            oReturnObject.Add("Message", "Connection to the Database failed")

        End If

        oConnection.Close()

    Goto EndOfFunction

ErrorMessage:

    sInfo = sInfo + "Error : "
    sInfo = sInfo + Err.Description + ""
    sInfo = sInfo + CTEXT(Err.LineNumber)
    oReturnObject.Remove("Message")
    oReturnObject.Add("Message", sInfo)

EndOfFunction:

Set oConnection = Null
Set InsertVDataRecord = oReturnObject

End Function

Function UpdateVDataRecord(sServer,sDatabase,sTable,sUpdate,sWhere,sUser,sPassword,oReturnObject)
Dim oConnection, oRecordset
Dim sInfo , sConnection, sSQL

On Error Goto ErrorMessage

Set oConnection = CreateObject("ADODB.Connection")

sConnection = "Provider=mrOleDB.Provider.2;Data Source=mrRdbDsc2;"
sConnection = sConnection + "Location='Provider=SQLOLEDB.1;Password=" + sPassword
sConnection = sConnection + ";Persist Security Info=True;User ID="
sConnection = sConnection + sUser + ";Initial Catalog=" + sDatabase
sConnection = sConnection + ";Data Source=" + sServer
sConnection = sConnection + "';Initial Catalog=\" + sServer
sConnection = sConnection + "SPSSMR_FMROOTMaster" + sDatabase + ""
sConnection = sConnection + sDatabase + ".mdd;MR Init Project=" + sDatabase

oConnection.Open(sConnection)

If ( oConnection.State = 1 ) Then

sSQL = "UPDATE " + sTable + " SET " + sUpdate + " WHERE " + sWhere

oConnection.Execute(sSQL)

oReturnObject.Remove("Message")
oReturnObject.Add("Message", "Connection to the Database worked, Update Complete.")

Else

oReturnObject.Remove("Message")
oReturnObject.Add("Message", "Connection to the Database failed")

End If

oConnection.Close()

Goto EndOfFunction

ErrorMessage:

sInfo = sInfo + "Error : "
sInfo = sInfo + Err.Description + ""
sInfo = sInfo + CTEXT(Err.LineNumber)
oReturnObject.Remove("Message")
oReturnObject.Add("Message", sInfo)

EndOfFunction:

Set oConnection = Null
Set UpdateVDataRecord = oReturnObject

End Function

Function DeleteVDataRecord(sServer,sDatabase,sTable,sWhere,sUser,sPassword,oReturnObject)
Dim oConnection, oRecordset
Dim sInfo , sConnection, sSQL

On Error Goto ErrorMessage

Set oConnection = CreateObject("ADODB.Connection")

sConnection = "Provider=mrOleDB.Provider.2;Data Source=mrRdbDsc2;"
sConnection = sConnection + "Location='Provider=SQLOLEDB.1;Password=" + sPassword
sConnection = sConnection + ";Persist Security Info=True;User ID="
sConnection = sConnection + sUser + ";Initial Catalog=" + sDatabase
sConnection = sConnection + ";Data Source=" + sServer
sConnection = sConnection + "';Initial Catalog=\" + sServer
sConnection = sConnection + "SPSSMR_FMROOT\Master" + sDatabase + ""
sConnection = sConnection + sDatabase + ".mdd;MR Init Project=" + sDatabase

oConnection.Open(sConnection)

If ( oConnection.State = 1 ) Then

sSQL = "DELETE FROM " + sTable + " WHERE " + sWhere

oConnection.Execute(sSQL)

oReturnObject.Remove("Message")
oReturnObject.Add("Message", "Connection to the Database worked, Record deleted.")
Else

oReturnObject.Remove("Message")
oReturnObject.Add("Message", "Connection to the Database failed")

End If

oConnection.Close()

Goto EndOfFunction

ErrorMessage:

sInfo = sInfo + "Error : "
sInfo = sInfo + Err.Description + ""
sInfo = sInfo + CTEXT(Err.LineNumber)
oReturnObject.Remove("Message")
oReturnObject.Add("Message", sInfo)

EndOfFunction:

Set oConnection = Null
Set DeleteVDataRecord = oReturnObject

End Function


Function FindVDataRecord(sServer,sDatabase,sTable,sKey,sWhere,sUser,sPassword,oReturnObject)
Dim oConnection, oRecordset
Dim sInfo , sConnection, sSQL

On Error Goto ErrorMessage

Set oConnection = CreateObject("ADODB.Connection")

sConnection = "Provider=mrOleDB.Provider.2;Data Source=mrRdbDsc2;"
sConnection = sConnection + "Location='Provider=SQLOLEDB.1;Password=" + sPassword
sConnection = sConnection + ";Persist Security Info=True;User ID="
sConnection = sConnection + sUser + ";Initial Catalog=" + sDatabase
sConnection = sConnection + ";Data Source=" + sServer
sConnection = sConnection + "';Initial Catalog=\" + sServer
sConnection = sConnection + "SPSSMR_FMROOTMaster" + sDatabase + ""
sConnection = sConnection + sDatabase + ".mdd;MR Init Project=" + sDatabase

oConnection.Open(sConnection)

If ( oConnection.State = 1 ) Then

sInfo = "Connection to the Database worked"

Set oRecordset = CreateObject("ADODB.Recordset")

sSQL = "SELECT " + sKey + " FROM " + sTable + " WHERE " + sWhere

oRecordSet.Open(sSQL,oConnection,3,1)
If ( oRecordSet.EOF = true and oRecordset.BOF = true ) Then
sInfo = sInfo + "Name Not Found"
oReturnObject.Remove("ID")
oReturnObject.Add("ID", -1)

Else
sInfo = sInfo + "Name Found !!!"
oReturnObject.Remove("ID")
oReturnObject.Add("ID", oRecordset[sKey].Value)

End if

oRecordset.Close()

oReturnObject.Remove("Message")
oReturnObject.Add("Message", sInfo)

Else

oReturnObject.Remove("Message")
oReturnObject.Add("Message", "Connection to the Database failed")

End If

oConnection.Close()

Goto EndOfFunction

ErrorMessage:

sInfo = sInfo + "Error : "
sInfo = sInfo + Err.Description + ""
sInfo = sInfo + CTEXT(Err.LineNumber)
oReturnObject.Remove("Message")
oReturnObject.Add("Message", sInfo)

EndOfFunction:

Set oConnection = Null
Set FindVDataRecord = oReturnObject

End Function




End Routing

JustCode : Find first cell with specific content

This function will find the first cell of the active worksheet with a specific content and return the column or row number.

' ****************************************
' Designed by : Smarter Dimensions
' Last Updated : 5th July 2009
' Function to find first cell with specific content
' ****************************************

Dim oExcel,oWorkBook,oSheet
dim iCel,oCell,iStartCol,iEndCol

Set oExcel = createobject("Excel.Application")

oExcel.Workbooks.Open("C:\TEMP\ExcelExport.xls")

oExcel.Visible = True

oWorkBook = oExcel.ActiveWorkbook

Set oSheet = oWorkBook.ActiveSheet

iStartCol = 1
iEndCol = FindFirstCell(3,9,"C","",oSheet)


Function FindFirstCell(iCol,iRow,sDirection,sLookFor,oSheet)

     Do while oSheet.Cells[iRow][iCol].text <> sLookFor
         Select Case sDirection
             Case = "C"
                 iCol = iCol + 1
             Case = "R"
                 iRow = iRow + 1
        End Select
     Loop

     Select Case sDirection
         Case = "C"
             FindFirstCell = iCol - 1
         Case = "R"
             FindFirstCell = iRow - 1
     End Select

End Function

Site Under Renovation

Hi,

just to let you know that this site is undergoing some re-work. The site should be up at all times and you should be able to get to the content that you need.

Things to do :

  1. Choose a new theme.
  2. Make sure links work.
  3. Will be deleting all user accounts shortly due to spam users.
  4. Will be deleting all old job post.

I am also looking for content writers so if you are interested in writing articles for this site let me know and lets see if we can make something happen.

Hope you find what you need.

Regards
Admin

%d bloggers like this: