Upload to MSSQL from Spreadsheet

[JPBeers]

Scenario:
We pull data into a LibreOffice Calc spreadsheet from an MSSQL Databaase.
An admin person goes through and checks the data for validity.
As they go through each row, they want to mark that row as complete in some way and re-upload to the DB, to indicate it’s been checked.
This way, each time they pull the data, they know where they’re up to.

Let’s say we have 4 columns of data:

NAME   HOURS   UNIQUE_TABLE_ID   IS_CHECKED
Fred   10      FredABC           Y
Wilma  12      WilmaABC          Y
Barney 12      BarneyABC         N
Betty  10      BettyABC          N

The admin person wants to change cells in the IS_CHECKED column to Y (or N, but theoretically could be any text), then click a button (Macro) to upload this data back into the DB, using the UNIQUE_TABLE_ID to ensure the correct DB entry is updated.

I can update a single row of data using this code as a starting point:

Sub Amend1Record
	Sheets = ThisComponent.Sheets
	Sheet1 = Sheets.getByIndex(1)
	NAME = Sheet1.getCellByPosition(1,1)
	NAMETEXT = NAME.String
	HOURS = Sheet1.getCellByPosition(2,1)
	HOURSTEXT = HOURS.String
	UNIQUE_TABLE_ID = Sheet1.getCellByPosition(3,1)
	UNIQUE_TABLE_IDTEXT = UNIQUE_TABLE_ID.String
	IS_CHECKED = Sheet1.getCellByPosition(4,1)
	IS_CHECKEDTEXT = IS_CHECKED.String
	Context = CreateUnoService("com.sun.star.sdb.DatabaseContext")
    DBName = "Attendance"
    DB = Context.getByName(DBName)
    Conn = DB.getConnection("","" ) 
    Stmt = Conn.createStatement()
    Stmt.executeupdate("update ""AttendanceData"" set ""aNAME"" = '" & NAMETEXT &_
     "', ""aHOURS"" = '" & HOURSTEXT &_
      "', ""aUNIQUE_TABLE_ID"" = '" & UNIQUE_TABLE_IDTEXT &_
       "', ""aIS_CHECKED"" = '" & IS_CHECKEDTEXT &_
        "' where ""aUNIQUE_TABLE_ID"" = '" & UNIQUE_TABLE_IDTEXT & "' ")
End Sub

I don’t know how to do multiple (and varying in quantity) rows though.

Can anyone help?

Thanks

James

[Ratslinger]

Hello,

This appears to be a poor and faulty method to verify database data. It would see to be done more effectively in a simple Base form. One blatant problem here is the distinct possibility to inadvertently modify the primary key/id.

In using your method, the larger problem is discovering the end of the data. For that you may want to view these posts:

macro to obtain row column like ctrl-end does

Find Last Row with data in a column

Once you know where the data starts & ends, and this really also depends on how the sheeet is set up, then you can just loop through the cells with a For statement.

[JPBeers]

Thank you for your reply and the methods - You’re correct with regards to a Base Form being better, and ordinarily I would do this. The sheet in question though is much more complex in it’s functionality than I’ve outlined here, and my request is just one aspect of its design. I find some of the other things needed, such as calculations, barcode generation, conditional formatting, error checking, auto producing and emailing PDF reports etc cannot be done in Base (with my skill set anyway )

[SM_Riga]

Hello @JPBeers.

Here is a brief example of the code to demonstrate how you can select all data range on the specified sheet and iterate rows/cells one by one, there are no any error handling or optimization:

REM  *****  BASIC  *****
Function GetDataRange (iSheetIndex as Integer) as Object 'Creates rectangular range, which includes all cells with data. Returns CellRange object
oSheet = ThisComponent.Sheets.getByIndex(iSheetIndex)
oCursor = oSheet.createCursor()
oCursor.gotoEndOfUsedArea(True)
GetDataRange = oCursor
End Function

Function GetDataRow (oDataRange as Object, iIndex as Integer) as Object 'Select one row with specified index to use it within loop
iLastColumn = oDataRange.Columns.Count - 1
GetDataRow = oDataRange.getCellRangeByPosition(0,iIndex,iLastColumn,iIndex)
End Function

Sub ParseRow (oRow as object) 'Just an example for how to use loop for parsing every cell in row. Here you can  
iLastColumn = oRow.Columns.Count - 1
	For x = 0 to iLastColumn
		' ...  your code goes here
		'Below is an example of code
		oCell = oRow.getCellByPosition(x,0)
		Msgbox "Cell: " & x + 1 & "/" & iLastColumn + 1 & chr(13) & "Selected cell is: " & oCell.AbsoluteName _
		& chr(13) & "Cell contents: " & oCell.String, 0
	Next x	
End Sub

Sub IterateRows 'Example subroutine that selects all data and iterate rows one by one
oDataRange = GetDataRange(0)
iLastRow = oDataRange.Rows.Count - 1
For i = 1 To iLastRow ' first row is a header, so we start loop from first data row
	oRow = GetDataRow(oDataRange, i) 'get row with index i
	' ...  your code goes here
	'Below is an example of code
	Msgbox "Row count: " & i & "/" & iLastRow & chr(13) & "Selected range is: " & oRow.AbsoluteName, 0
Next i
End Sub

Sub IterateCells 'Example subroutine that selects all data, iterate rows one by one and parses every cell in the row
oDataRange = GetDataRange(0)
iLastRow = oDataRange.Rows.Count - 1
For i = 1 To iLastRow ' first row is a header, so we start loop from first data row
	oRow = GetDataRow(oDataRange, i)
	ParseRow (oRow)
Next i
End Sub

— Update —
Please also find Demo Spreadsheet attached with the same macro included. In any case, I don’t think that it is good practice to use create and close database connections and execute statements within each For loop. I am not sure about OOo Basic, but in PHP I used prepared string like INSERT INTO $tablename ($field names) VALUES ($values) ON DUPLICATE KEY UPDATE so the whole statement was executed in a single connection session. Maybe you can use For loop just to prepare such a statement and then execute it.

[JPBeers]

Thank you for the reply and update - I’ll try to wrap my brain around it today and let you know how it goes. Between this and the other answers posted here, I’m hopeful of a solution.