Using VB2008 to acccess the Betfair API: A tutorial

[Camper]

How can I know wich prices are lay and wich are back using CompleteMarketPricesCompressed ?

Dumb question Using .layamount and .backamount

[Camper]

How can I know wich prices are lay and wich are back using CompleteMarketPricesCompressed ?

[luckyjock]

lucky


(this is not gospel as i usually work in access vba and not up to speed in vb2010) but I think the connection is not being made to the database

have you tried putting test data in the table and seeing if you can read that data from your progarm -- this would verify that the connection is ok

also you dont say what type of database it is trying to connect too

if its sql server try looking here at connection strings - http://www.sqlteam.com/article/sql-s...nection-string

Thanks for replying.

I managed to get it to connect using

Public tblMarketTableAdapter As New DataBetfairDataSetTableAdapters.tblMarketsTableAdapter()

and then putting a

tblMarketTableAdapter.Insert(.........)

statement in.

Now i've got to have a go at the GetMarketPricesCompressed data and put that into a different table.

[cfwnotlob]

reply to luckyjock

lucky


(this is not gospel as i usually work in access vba and not up to speed in vb2010) but I think the connection is not being made to the database

have you tried putting test data in the table and seeing if you can read that data from your progarm -- this would verify that the connection is ok

also you dont say what type of database it is trying to connect too

if its sql server try looking here at connection strings - http://www.sqlteam.com/article/sql-s...nection-string

[luckyjock]

Many thanks to Mumbles for his great tutorial, i have followed it so that i now have a working form.

However, i would like to try and save the markets information into a database. I have spent the last 3 nights reading, reading and more reading about datasets, datatables, databases, tableadapters, binding sources and i have got totally and utterly confused.

My first experience of any type of programming was 10 days ago when i first saw this thread and then thought i'd give it a go

I have followed the tutorial where it teaches you how to display the Markets information in a datagridview. I have changed my application so it shows all the columns that the 'marketdata' output provides in my datagridview.

I understand that the matchdata object holds the information i need to get to my database.

I spent days trying to use the tableadapters and the data sources within vb 2010 but tonight learnt that they are for typed datasets. Am i right in assuming that i have to create an untyped dataset that the datatable dtmarkets sits in, and therefore i am unable to connect the database so it shoes in a dataset that appears in the solution explorer?

Could anyone have a look at my code below and see where i am going wrong, or just give me a gentle push in the right direction as i am very keen to learn how to do stuff myself? My Sql connection works fine, i have tested this by putting in a breakpoint at the 'dtMarkets.Rows.Add(matchdata)' line and then checking the connection state and it is open. At that moment after i havei clicked on my SqlConnect button the textbox shows that the header is ok and the errorcode ok. The 'matchdata' object has 16 fields of data within it as it should do, but when i check the database table (tblMarkets) in my database there is no data in it?

Private Sub bSqlConnect_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles bSqlConnect.Click

        sqlComm.Connection = sqlConn        'Connection string and command string set as public in Unpack module
        sqlConn.Close()
        sqlConn.Open()

        ' Set Dataset and datatable 
        Dim dsBetfair As DataSet = New DataSet("MarketDetails")
        Dim dtMarkets As DataTable = dsBetfair.Tables.Add("Markets")

        Try
            With dtMarkets.Columns
                .Add("MarketID", GetType(System.String))
                .Add("MarketName", GetType(System.String))
                .Add("MarketType", GetType(System.String))
                .Add("MarketStatus", GetType(System.String))
                .Add("EventDate", GetType(System.DateTime))
                .Add("MenuPath", GetType(System.String))
                .Add("EventHierachy", GetType(System.String))
                .Add("BetDelay", GetType(System.String))
                .Add("ExchangeID", GetType(System.String))
                .Add("ISO3CountryCode", GetType(System.String))
                .Add("LastRefresh", GetType(System.DateTime))
                .Add("NoRunners", GetType(System.String))
                .Add("NoWinners", GetType(System.String))
                .Add("TtlAmountMatched", GetType(System.Double))
                .Add("BspMarket", GetType(System.Boolean))
                .Add("TurningInPlay", GetType(System.Boolean))
            End With

            Print("*** Data to SQL ***")
            Dim oMarketsReq As New BFUK.GetAllMarketsReq
            Dim oMarketsResp As BFUK.GetAllMarketsResp
            Dim matchdata(15) As Object
            With oMarketsReq
                .header = oHeaderUK()
                ReDim .eventTypeIds(0) : .eventTypeIds(0) = 7       'For Horse Racing
                ReDim .countries(1) : .countries(0) = "GBR" : .countries(1) = "ZAF"
                .fromDate = Today.AddDays(0)
                .toDate = Today.AddDays(1)
            End With
            oMarketsResp = BetFairUK.getAllMarkets(oMarketsReq)     'Call the UK API
            With oMarketsResp
                Checkheader(.header)
                Print("ErrorCode = " & .errorCode.ToString)
                If .errorCode = BFUK.GetAllMarketsErrorEnum.OK Then
                    Dim AllMarkets As New UnpackAllMarkets(.marketData)
                    With AllMarkets
                        For i = 0 To .marketData.Length - 1
                            With .marketData(i)
                                matchdata(0) = .marketId
                                matchdata(1) = .marketName
                                matchdata(2) = .marketType
                                matchdata(3) = .marketStatus
                                matchdata(4) = .eventDate.ToLocalTime
                                matchdata(5) = .menuPath
                                matchdata(6) = .eventHeirachy
                                matchdata(7) = .betDelay
                                matchdata(8) = .exchangeId
                                matchdata(9) = .countryCode
                                matchdata(10) = .lastRefresh
                                matchdata(11) = .noOfRunners
                                matchdata(12) = .noOfWinners
                                matchdata(13) = .totalAmountMatched
                                matchdata(14) = .bspMarket
                                matchdata(15) = .turningInPlay

                                dtMarkets.Rows.Add(matchdata)


                                Dim fillQueryString As String = _
              "SELECT marketId, marketName, marketType, marketStatus, eventDate, menuPath, eventHierachy, betDelay, exchangeId," & _
              "iso3CountryCode, lastRefresh, noOfRunners, noOfWinners, totalAmountMatched, bspMarket, turningInPlay FROM dbo.tblMarkets"
                                Dim adapter As SqlClient.SqlDataAdapter = New SqlClient.SqlDataAdapter(fillQueryString, sqlConn)

                                adapter.Fill(dsBetfair, "tblMarkets")
                            End With
                        Next
                    End With
                End If
            End With


        Catch ex As Exception
            MessageBox.Show("Failed to connect to data source")

            sqlConn.Close()
        End Try
    End Sub

[NFLMAN]

underlying connection was closed

I'm occasionaly getting the "ERROR: The underlying connection was closed: A connection that was expected to be kept alive was closed by the server." message but don't know how or where to catch it because my application doesn't stop. I get a JIT message and the program carries on working in the background. I just have to click 'continue' to get rid of the message. Annoying rather than a show stopper but I would still like to stop it happening.

My error handling knowledge is not great so where do I put the code to catch this error popping up and what would the code look like?

Thanks in advance

[NFLMAN]

You can write data to a file using:

My.Computer.FileSystem.WriteAllText(filename,data, append)

filename = the name of the file to write the data too as a string
data = the data you want to write to the file, price, betID etc...
append = True/False value depending on whether you want to append the file or not

This will create/append a .txt file which Excel will open.

There are probably other ways to do this but this method works for my needs

[neverfails]

Also.... is anyone in this forum based in London? If so I'd really like to meet up to discuss something I'm trying to do -- but I don't have the necessary VBA/VB.net programming skills to do.

Will pay for the project - so if anyone here is London-based and can help pls send me a private message! Tks!

[neverfails]

Hi - I'm having the same issues - would be great if someone could advise.

Also does anyone has the code already setup in an Excel spreadsheet - that can pull in all the *UK win markets* only from the "Horse Racing - Todays Card"?

I would really appreciate if someone could share that.... Thanks!!

[nanook]

Exporting data to excel

I'm a rookie VB programmer and have implemented Mumbles' code in VB 2010 (many thanks!). However I have been unable to figure out how to export data (eg. prices) to an excel worksheet which I would like to do so I can interact with some VBA programs.

Any help would be greatly appreciated.

[Camper]

I don't know why this doesn't work, it's my code for greening a two selections market:

    Dim backgreen, laygreen, difference, sizeG, greened As Decimal
    Dim bettypeG, selectionG As Integer '0 Bak, 1 Lay
    Private Sub GreenStatus_Tick(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles GreenStatus.Tick
        If Val(aB0.Text) < Val(bB0.Text) Then 'Favorito A
            selectionG = id0
            backgreen = CDec(Val(aB0.Text))
            laygreen = CDec(Val(aL0.Text))
            difference = CDec(profitA - profitB)
            If laygreen > 0 Then
                If difference > 0 Then
                    bettypeG = 1
                    sizeG = CDec(FormatNumber(difference / laygreen, 2))
                    greened = CDec(profitA - sizeG * (laygreen - 1))
                End If
                If difference < 0 Then
                    bettypeG = 0
                    sizeG = CDec(FormatNumber(difference / backgreen, 2))
                    greened = CDec(profitA + sizeG * (backgreen - 1))
                End If
            End If
        Else 'Favorito B
            selectionG = id1
            backgreen = CDec(Val(bB0.Text))
            laygreen = CDec(Val(bL0.Text))
            difference = CDec(profitB - profitA)
            If laygreen > 0 Then
                If difference > 0 Then
                    bettypeG = 1
                    sizeG = CDec(FormatNumber(difference / laygreen, 2))
                    greened = CDec(profitB - sizeG * (laygreen - 1))
                End If
                If difference < 0 Then
                    bettypeG = 0
                    sizeG = CDec(FormatNumber(difference / backgreen, 2))
                    greened = CDec(profitB + sizeG * (backgreen - 1))
                End If
            End If
        End If
        If greened < 0 Then
            Panel3.BackColor = Color.Red
        Else
            Panel3.BackColor = Color.LightGreen
        End If
            Label18.Text = CStr(FormatNumber(greened, 2))
    End Sub
    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        If bettypeG = 0 Then
            prc = laygreen
            sid = selectionG
            sz = sizeG
            PlaceLet(markid, sid, BFUK.BetTypeEnum.L, prc, sz)
        End If
        If bettypeG = 1 Then
            prc = backgreen
            sid = selectionG
            sz = sizeG
            PlaceBet(markid, sid, BFUK.BetTypeEnum.B, prc, sz)
        End If
    End Sub

It correctly determines wich bet I need to place (back or lay) the selection and the amount. It's working fine but after i click the button to green nothing happens.

I get an OK error from the place bet don't know what I'm missing here.

EDIT: I figured it out. Thanks anyway.

[Camper]

Camper,

hmmmm - Inplay hasn't been covered in this tutorial so Mumbles may wish to cover it as a new step.

I don't bet "Inplay" so my knowledge is gained from generally reading other posts, so my advice may not be absolutely correct.

My understanding is that the delay reflects the state of the market and when delay > 0 then a delay is placed on placing new bets and increasing the size of a bet placed - it does not affect the market prices.

I would be looking to the sub inplay() as causing the problem if market prices go haywire after delay > 0 - what does it do, why is it required? (same with inplay0())?

You need to show what this code does:

                            If .delay > 0 Then
                                inplay()
                            Else
                                inplay0()
                            End If

That code only changes a TextBox Text to inplay if the market is in play, nothing else.

The problem is not there I'm sure.

[davecon]

Hi Camper - Bigsprout
Bigsprout is correct regarding the Delay - .delay simply returns the amount of Seconds Delay when placing a bet in running (eg 1 sec for UK Racing) Cancelling a Bet is still instant
This does not affect the prices in Any Way
So the Delay is 0 until the "Off" then this Changes to 1
But it Remains at 1 until the Market is Closed (It does not revert to 0 when Suspended at the end of the race!!)
I use the .delay along with various combinations of the .marketStatus (which returns whether the market is Active - Suspended or Closed), For Example I use these as Identifiers in a Heading label and to use a Gas() Sub to up my Prices Timer Speed to 1100 for In Running and back to 2000 once Suspended (Free api restriction) It reverts back to speed if reopens again for Photo etc then Back again etc
You can also use it to cancel any "Kept" bets at the Off for instance
Does your Code work ok if you Comment out the delay code highlighted by Bigsprout?
If you want it to work differently when "In Running" you will have to incorporate your code directly into your InPlay Subs
Hope this helps a bit
Dave

[BigSprout]

Camper,

hmmmm - Inplay hasn't been covered in this tutorial so Mumbles may wish to cover it as a new step.

I don't bet "Inplay" so my knowledge is gained from generally reading other posts, so my advice may not be absolutely correct.

My understanding is that the delay reflects the state of the market and when delay > 0 then a delay is placed on placing new bets and increasing the size of a bet placed - it does not affect the market prices.

I would be looking to the sub inplay() as causing the problem if market prices go haywire after delay > 0 - what does it do, why is it required? (same with inplay0())?

You need to show what this code does:

                            If .delay > 0 Then
                                inplay()
                            Else
                                inplay0()
                            End If

[Camper]

Could someone check what's wrong with this code?
I'm getting wrong price values when market is inplay.

Private Sub BetFairUK_getMarketPricesCompressedCompleted(ByVal sender As Object, ByVal e As BFUK.getMarketPricesCompressedCompletedEventArgs) Handles BetFairUK.getMarketPricesCompressedCompleted
        Dim tot1, tot2 As Double
        id0 = CInt(Val(bSelecid.Text))
        id1 = CInt(Val(bSelectid1.Text))
        Try
            If Not e.Cancelled Then
                With e.Result
                    CheckHeader(.header)
                    If .errorCode = BFUK.GetMarketPricesErrorEnum.OK Then
                        Dim oMarketPrices As New UnpackMarketPricesCompressed(.marketPrices)
                        With oMarketPrices
                            If .delay > 0 Then
                                inplay()
                            Else
                                inplay0()
                            End If
                            For i = 0 To .runnerPrices.Length - 1
                                With .runnerPrices(i)
                                    If .selectionId = id0 Then
                                        tot1 = CDbl(FormatNumber(.totalAmountMatched, 0))
                                        If .bestPricesToBack.Length > 0 Then
                                            Dim graphvalue As Double
                                            graphvalue = .bestPricesToBack(0).price
                                            aB0.Text = Replace(CStr(.bestPricesToBack(0).price), ",", ".")
                                            aBA0.Text = CStr(Int(.bestPricesToBack(0).amountAvailable))
                                            pgraph0.Add(graphvalue)
                                        Else
                                            aB0.Text = "Offer"
                                            aBA0.Text = ""
                                        End If
                                        If .bestPricesToBack.Length > 1 Then
                                            aB1.Text = Replace(CStr(.bestPricesToBack(1).price), ",", ".")
                                            aBA1.Text = CStr(Int(.bestPricesToBack(1).amountAvailable))
                                        Else
                                            aB1.Text = ""
                                            aBA1.Text = ""
                                        End If
                                        If .bestPricesToBack.Length > 2 Then
                                            aB2.Text = Replace(CStr(.bestPricesToBack(2).price), ",", ".")
                                            aBA2.Text = CStr(Int(.bestPricesToBack(2).amountAvailable))
                                        Else
                                            aB2.Text = ""
                                            aBA2.Text = ""
                                        End If
                                        If .bestPricesToLay.Length > 0 Then
                                            Dim graphvalue As Double
                                            graphvalue = .bestPricesToLay(0).price
                                            aL0.Text = Replace(CStr(.bestPricesToLay(0).price), ",", ".")
                                            aLA0.Text = CStr(Int(.bestPricesToLay(0).amountAvailable))
                                            prgraph2.Add(graphvalue)
                                        Else
                                            aL0.Text = ""
                                            aLA0.Text = ""
                                        End If
                                        If .bestPricesToLay.Length > 1 Then
                                            aL1.Text = Replace(CStr(.bestPricesToLay(1).price), ",", ".")
                                            aLA1.Text = CStr(Int(.bestPricesToLay(1).amountAvailable))
                                        Else
                                            aL1.Text = ""
                                            aLA1.Text = ""
                                        End If
                                        If .bestPricesToLay.Length > 2 Then
                                            aL2.Text = Replace(CStr(.bestPricesToLay(2).price), ",", ".")
                                            aLA2.Text = CStr(Int(.bestPricesToLay(2).amountAvailable))
                                        Else
                                            aL2.Text = ""
                                            aLA2.Text = ""
                                        End If
                                    End If
                                    If .selectionId = id1 Then
                                        tot2 = CDbl(FormatNumber(.totalAmountMatched, 0))
                                        If .bestPricesToBack.Length > 0 Then
                                            Dim graphvalue As Double
                                            graphvalue = .bestPricesToBack(0).price
                                            bB0.Text = Replace(CStr(.bestPricesToBack(0).price), ",", ".")
                                            bBA0.Text = CStr(Int(.bestPricesToBack(0).amountAvailable))
                                            pgraph1.Add(graphvalue)
                                        Else
                                            bB0.Text = "Offer"
                                            bBA0.Text = ""
                                        End If
                                        If .bestPricesToBack.Length > 1 Then
                                            bB1.Text = Replace(CStr(.bestPricesToBack(1).price), ",", ".")
                                            bBA1.Text = CStr(Int(.bestPricesToBack(1).amountAvailable))
                                        Else
                                            bB1.Text = ""
                                            bBA1.Text = ""
                                        End If
                                        If .bestPricesToBack.Length > 2 Then
                                            bB2.Text = Replace(CStr(.bestPricesToBack(2).price), ",", ".")
                                            bBA2.Text = CStr(Int(.bestPricesToBack(2).amountAvailable))
                                        Else
                                            bB2.Text = ""
                                            bBA2.Text = ""
                                        End If
                                        If .bestPricesToLay.Length > 0 Then
                                            Dim graphvalue As Double
                                            graphvalue = .bestPricesToLay(0).price
                                            bL0.Text = Replace(CStr(.bestPricesToLay(0).price), ",", ".")
                                            bLA0.Text = CStr(Int(.bestPricesToLay(0).amountAvailable))
                                            prgraph3.Add(graphvalue)
                                        Else
                                            bL0.Text = ""
                                            bLA0.Text = ""
                                        End If
                                        If .bestPricesToLay.Length > 1 Then
                                            bL1.Text = Replace(CStr(.bestPricesToLay(1).price), ",", ".")
                                            bLA1.Text = CStr(Int(.bestPricesToLay(1).amountAvailable))
                                        Else
                                            bL1.Text = ""
                                            bLA1.Text = ""
                                        End If
                                        If .bestPricesToLay.Length > 2 Then
                                            bL2.Text = Replace(CStr(.bestPricesToLay(2).price), ",", ".")
                                            bLA2.Text = CStr(Int(.bestPricesToLay(2).amountAvailable))
                                        Else
                                            bL2.Text = ""
                                            bLA2.Text = ""
                                        End If
                                    End If
                                    Dim x As String
                                    x = FLogin.bCurrency.Text
                                    If x = "Euro" Then
                                        HeaderTM.Text = ("Total Matched:" & " " & tot1 + tot2 & " €")
                                    Else
                                        HeaderTM.Text = ("Total Matched:" & " " & tot1 + tot2 & " £")
                                    End If
                                End With
                            Next
                        End With
                    End If
                End With
            End If
        Catch ex As ApplicationException
        End Try
    End Sub