Sage project from VS 2010

Hi, I am trying to make a little app that will help at work. It's about 8 years since i did any VB and I realised after I started I don't remember anything, and was never very good to start with! Add to that that this is VB.Net which is completely new to me. VS 2010 is also so different from what I used before I'm really stuck. The trial of VS 2010 expires in 90 days I think so I need to get moving.

I want to connect to Sage Data, to the stock table, and select certain columns such as SKU, title, description, sales price and then select the rows I want and send these to an excel spreadsheet formatted in the style required for upload as product feeds to google shopping, shopzilla, amazon and Ebay etc.

Each feed is formatted differently and I have to create these manually all the time so I want to try and automate the process. We would have much better control of our ecommerce if I could do this quickly with custom program.


So far I have managed, after a lot of disappointments, to connect to the sage data through the sage ODBC and populate a datagridview. I've only got this far by luck, with many errors due to me and system and software problems. I thought this would be relatively easy as I once used an app to create a word document programmatically with a very thorough guide, it's not looking so easy now. I have also added a check box column to the datagridview for selecting rows and thats as far as I have got.

I was thinking perhaps of passing the selected data to an array and then creating and populating the spreadsheet. Any ideas on how to proceed with this? I haven't figured out how to get selected rows from DGV.

If i had some clues to the steps I should take to achieve this i can look for tutorials on those steps and maybe kickstart my memory!


Best regards skaman :confused:

First up, you don't really need to worry about the trial expiring. VB Express is free so you can just install that. VB Express doesn't have all the features of VS but I think it unlikely that you'll need any of the missing features right away anyway.

As for the application, if you're using ODBC then you should use an OdbcDataAdapter to populate a DataTable. If you need a bit of help refining your ADO.NET code then follow the Database FAQ link in my signature. Amongst other things, it provides a link to my own Retrieving & Saving Data thread in the CodeBank forum, which provides example code for this and other common ADO.NET scenarios.

You can then display the contents of that DataTable in a DataGridView in one line of code:

vb.net Code:

myDataGridView.DataSource = myDataTable.

Before you do that though, you might like to add an extra column to the table:

vb.net Code:

myDataTable.Columns.Add("Selected", GetType(Boolean))

That column will then be automatically bound to a check box column in the grid. Once the user has selected the appropriate rows, you simply loop through the table and process the selected rows:

vb.net Code:

For Each row As DataRow In myDataTable.Rows
    If CBool(row("Selected")) Then
        'Use row here.
    End If
Next

As for creating the worksheets, you have two main options:

1. Use a StreamReader to output the data to a CSV file, which you can then open in Excel and convert to an XLS document. That's probably the easier option, if you don't mind the extra manual step and the required format is suitable.

2. Use Office Automation to control an Excel instance and generate the XLS file directly. I can't help you further with this option but fellow member RobDog888 is the local Office guru. Use the Community menu to find his profile and check out the links in his signature.

Actually, it occurs to me that you should probably add the extra column first like this:

vb.net Code:

myDataTable.Columns.Add("Selected", GetType(Boolean)).DefaultValue = False

That will ensure that all rows contain False in that column by default, rather than NULL. NULL values would cause the loop I posted to throw an exception.

Hi, thanks for the reply. It's a starting place for me. I have VB express as well but i couldn't find any way to setup a connection to ODBC. I will try your advice when i get home tonight. This might simplify things as I have a form with 2 tableadapters, 2 datasets, 3 bindingsources and a few other bits and I'm sure I don't need them all!

Regards skaman :thumb:

You can write code to connect to any database at all in VB Express. The Data Source wizard only supports few Microsoft data sources though.

Hi J, thanks for the help, it has taken a bit of trying but i have got data in the DGV and seems to be working so far. Here is what I have got to date, Is this on the right track?

Regards skaman

form so far Code:

Imports System.Data.Odbc
Public Class Form2
 
    Private Sub Form2_Load(sender As System.Object, e As System.EventArgs) Handles MyBase.Load
 
 
    End Sub
 
 
    Private Sub Button1_Click_1(sender As System.Object, e As System.EventArgs) Handles Button1.Click
        Call FillDataGrid()
    End Sub
 
 
 
    Sub FillDataGrid()
 
        Dim ObjConn As OdbcConnection
        Dim DBComm As OdbcCommand
        Dim StrConnectionString As String
        Dim StrSql As String
        Dim StockTable As New DataTable
 
        StrConnectionString = "Dsn=SageLine50v13;uid=****;Pwd=****;"
        StrSql = "SELECT STOCK_CODE, Description FROM STOCK"
        ObjConn = New OdbcConnection(StrConnectionString)
        ObjConn.Open()
        DBComm = New OdbcCommand(StrSql, ObjConn)
 
 
 
        Using DtaReader As OdbcDataReader = DBComm.ExecuteReader()
 
            StockTable.Columns.Add("Selected", GetType(Boolean)).DefaultValue = False
            StockTable.Load(DtaReader)
            MyDataGridView.DataSource = StockTable
 
        End Using
 
        ObjConn.Close()
        DBComm.Dispose()
        ObjConn.Dispose()
    End Sub
 
 
 
 
End Class

Quote:

---

Originally Posted by skamanchero

Hi J, thanks for the help, it has taken a bit of trying but i have got data in the DGV and seems to be working so far. Here is what I have got to date, Is this on the right track?

Regards skaman

form so far Code:

Imports System.Data.Odbc
Public Class Form2
 
    Private Sub Form2_Load(sender As System.Object, e As System.EventArgs) Handles MyBase.Load
 
 
    End Sub
 
 
    Private Sub Button1_Click_1(sender As System.Object, e As System.EventArgs) Handles Button1.Click
        Call FillDataGrid()
    End Sub
 
 
 
    Sub FillDataGrid()
 
        Dim ObjConn As OdbcConnection
        Dim DBComm As OdbcCommand
        Dim StrConnectionString As String
        Dim StrSql As String
        Dim StockTable As New DataTable
 
        StrConnectionString = "Dsn=SageLine50v13;uid=****;Pwd=****;"
        StrSql = "SELECT STOCK_CODE, Description FROM STOCK"
        ObjConn = New OdbcConnection(StrConnectionString)
        ObjConn.Open()
        DBComm = New OdbcCommand(StrSql, ObjConn)
 
 
 
        Using DtaReader As OdbcDataReader = DBComm.ExecuteReader()
 
            StockTable.Columns.Add("Selected", GetType(Boolean)).DefaultValue = False
            StockTable.Load(DtaReader)
            MyDataGridView.DataSource = StockTable
 
        End Using
 
        ObjConn.Close()
        DBComm.Dispose()
        ObjConn.Dispose()
    End Sub
 
 
 
 
End Class

---

Hmm.. should you be using ODBC access or using the SDO interface? I work for Sage 50 R&D but not in the Accounts team but I'd have thought that you should be using Sage Data Objects and not ODBC. I'll pass your query on to the development team lead for Line 50 accounts and see if they can advise.