none
need help with using linq to sql to get prices from a sql database in a format i can use in calculations RRS feed

  • Question

  • Hi
    I am new to visual basic express 2010 and this is my first project using vb 2010 express

     I have posted before but I am still in total confusion here so desparately need help. I have watched videos and looked at walkthrus   and read up about linq to sql on msdn ( a lot of it was over my head there) for over a month and have ended up totally confused as to what  I am supposed to do to get the information from the database in a usable form.

    Basically I have prices for shop items stored in the data base with their prices entered as cents. ie $1.50 is entered as 150  which is the price per metre

    In a sql database  called prices.mdf. I have got several tables in this which basically have an id, a description and a price. eg one table is called Timberprices and the columns and datatypes in there are id which is an integer, timber in cents which is an nvarchar(100) and a price which is money.
    When you look at the entries added under these colums there is 19 enteries. ie 19 diff size timbers and prices with ids from 1-19.

    Under datasources I have made a dataset called getpricesdataset.xsd This has all the tables from the mdf database in it. It is called getpricesdatacontext

    I have also made a linq to sql with all the tables in it called getprices.dbml I dragged all the tables from the prices.mdf tables folder to the ord designer

    What i want to do is access the database and get the price for a particular size timber based on the id in the table  and have this price in a format i can work with in calculations eg multiply it by numbers etc to work out the price of the timber used.

    If you look in the dbml at the timberprice table its name is timberprice and source is dbo.timberprices and the names of the properties is id, timber_in_cents, and price

    Now i have experimented with dragging a table onto a form and can get the whole table to load into the datagrid when u run the program, but it is not what I am wanting to do.

    All i want to do is get the price for a particular timber to use in my calculations.

    I have tried writing code where I select the the id working on the assumptionon another form you will tell the program which id to use. in this example for the timber I am using the count variable which is a single. eg count =8 .

    At the moment I am putting the code on form 2 in the form load just to experiment. it may end up on a different form later as I won't be displaying the answer in a datagrid view. tho I have the datagrid view on there atm

     Public db As New getpricesDataContext
        Private Sub Form2_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
            'TODO: This line of code loads data into the 'GetpricesDataSet.Timberprices' table. You can move, or remove it, as needed.
            Me.TimberpricesTableAdapter.Fill(Me.GetpricesDataSet.Timberprices)

            Dim count As Single
            count = 3
            Dim mainboard = From timberprice In db.Timberprices
                                    Where timberprice.id = count
                                    Select timberprice.Price


    When I run this all the 19 enteries load into the datagrid view but I have no idea how to check if the extra code I wrote actually works and if the price i selected is the one which is in the columns by the id 3.

    I have tried multiplying iboth mainboard  and the timberprice.price by  say 2
    but it gives errors ct4 and ct3 are variables used in my calculations

    eg

     Dim ct4 As Single
            Dim ct3 As Single
            ct4 = mainboard              ' this is line 15
            ct3 = mainboard * 2          ' this is line 16 in code


    Error    1    Value of type 'System.Linq.IQueryable(Of Decimal)' cannot be converted to 'Single'.    M:\All Documents\Visual Studio 2010\Projects\Work Pricing Program\Form2.vb    15    15    Work Pricing Program


    Error    2    Operator '*' is not defined for types 'System.Linq.IQueryable(Of Decimal)' and 'Integer'.    M:\All Documents\Visual Studio 2010\Projects\Work Pricing Program\Form2.vb    16    15    Work Pricing Program

    I haven't really got a clue what I am doing and have spent weeks and weeks trying to work it out for myself but am at the stage of tearing out my hair.

    If anyone can figure out what i am doing wrong and give me the code i should be using or instructions I would be very very grateful. Remember I am a beginner  so complicated instructions or explanations just confuse me more.

    Thanks for your help and patience ppl :) If i can get it figured for this one i should be able to use that to figure out what to do for the other tables using this as a guide.

    Robyn
    Saturday, September 11, 2010 7:35 AM

Answers

  • I don't know visual basic, I use C#.  The errors are the same, I just cannot write the solution.

    The problems is a conversion problem.  This returns a list of TimberPrices where id equals count.   Although you may only have one that matches that it will return a list with count of one then.   In C# i would wrap the query with parens "( )" and add .FirstOrDefault() method to the end of it.  That will change the list of prices in to the first item or zero.

    The second looks like a similar problem.  You have a list (probably of count 1) that you are trying to do a calculation on.

    Additionally you could use array access on list.  So in C# I would write List[0]  to get the first element in List.  You want to check the count first so you dont get an OutOfBounds runtime error. 

    Also, you can do db.Timberprices.SingleOrDefault( x=> x.id == count );  which will return the element that matches.  The "x =>" is a lambda expresion, your saying x such that.  This can also be thought as a method (because it is)  where the method signature returns bool, and has a parameter of a Timberprice.  Your really saying "private bool Method(Timberprice x) { return x.id == count; } ".  That is C#, VB should have something similar.   

     

    Monday, September 13, 2010 8:18 PM