Convert Access Query to VB.net SQL Statement -

-

i have table in access database called historical_stock_prices filled various companies historical stock prices. need run query convert raw data (the stock prices) quarterly growth rates , display quarterly growth rates in datagridview.

i've written the following query in sql view of access database , works within access.

select minmaxyrqtrdates.yrqtr, minmaxyrqtrdates.ticker, minmaxyrqtrdates.maxdate, [historical prices].close, minmaxyrqtrdates.mindate, [historical prices_1].open, ([historical prices].[close]/[historical prices_1].[open]-1)*100 growthrate [historical prices] [historical prices_1] inner join ([historical prices] inner join [select year([date]) & "-" & datepart("q",[date]) yrqtr, [historical prices].ticker, max([historical prices].date) maxdate, min([historical prices].date) mindate [historical prices] group year([date]) & "-" & datepart("q",[date]), [historical prices].ticker]. minmaxyrqtrdates on ([historical prices].date = minmaxyrqtrdates.maxdate) , ([historical prices].ticker = minmaxyrqtrdates.ticker)) on ([historical prices_1].ticker = minmaxyrqtrdates.ticker) , ([historical prices_1].date = minmaxyrqtrdates.mindate);

i need able call within program , display results in datagridview. i've tried copy sql statement access , use sql statement in code doesn't work. don't errors, datagridview blank. here code far:

imports system.io imports system.data.oledb  public class historical_growth_rates_annual  public tblname string = "historical_stock_prices"  private sub historical_growth_rates_annual_load(sender object, e eventargs) handles mybase.load     if (file.exists(nordeen_investing_3.databasename))         nordeen_investing_3.con.open()         dim restrictions(3) string         restrictions(2) = tblname         dim dbtbl datatable = nordeen_investing_3.con.getschema("tables", restrictions)         if dbtbl.rows.count = 0             messagebox.show("historical stock prices tables not exist in database.  please update")         else             dim da oledbdataadapter = new oledbdataadapter("select minmaxyrqtrdates.yrqtr, minmaxyrqtrdates.ticker, minmaxyrqtrdates.maxdate, [historical_stock_prices].close1, minmaxyrqtrdates.mindate, [historical_stock_prices_1].open1, ([historical_stock_prices].[close1]/[historical_stock_prices_1].[open1]-1)*100 growthrate [historical_stock_prices] [historical_stock_prices_1] inner join ([historical_stock_prices] inner join [select year([date1]) & " - " & datepart('q',[date1]) yrqtr, [historical_stock_prices].ticker, max([historical_stock_prices].date) maxdate, min([historical_stock_prices].date) mindate [historical_stock_prices] group year([date1]) & " - " & datepart('q',[date1]), [historical_stock_prices].ticker]. minmaxyrqtrdates on ([historical_stock_prices].date = minmaxyrqtrdates.maxdate) , ([historical_stock_prices].ticker = minmaxyrqtrdates.ticker)) on ([historical_stock_prices_1].ticker = minmaxyrqtrdates.ticker) , ([historical_stock_prices_1].date = minmaxyrqtrdates.mindate);", nordeen_investing_3.con)             'create new dataset             dim ds new dataset()             'fill datset             da.fill(ds)             'attach dataset datagrid             datagridview1.datasource = ds.tables(0)             ds = nothing             da = nothing             nordeen_investing_3.con.close()         end if     else         messagebox.show("database not exist.  please update.")     end if end sub end class

i'm stuck , use help! thanks!

database example

you want vb.net code recreate same select statement works in access. however, looking @ syntax highlighting vim, think may creating else. (it may creating string difference of 2 other strings: "string 1" - "string 2").

but whether or not guessed correctly, use string variable hold select statement. print string console or write text file can examine actual statement you're giving db engine.

or save working query in access named query object , use query name vb.net code --- absolutely guarantee using same sql confirmed work in access.


Comments

Post a Comment

Popular posts from this blog

java - Run a .jar on Heroku -

-
i trying run .jar file on heroku, , can't find instructions anywhere on how this. have read wants me compile uploading heroku. possible, , if is, how this? if helps, trying run minecraft craftbukkit server, , don't have pom.xml file yet. you didn't provide details i'll try give , people reaches question way it. first of all, create heroku account , create heroku application (assumed have done that). second, need generate application jar , upload heroku. so, open terminal , install heroku cli (assuming unix terminal): $ sudo npm install -g heroku-cli you need have npm , nodejs installed, can check link: https://www.digitalocean.com/community/tutorials/how-to-install-node-js-on-ubuntu-16-04 once done that, can use npm update node version acceptable heroku-cli: $ sudo npm install -g n $ sudo n 7.10.0 and run: $ sudo npm install -g heroku-cli in order deploy jars, need install deploy plugin: $ sudo heroku plugins:install heroku-cli-deploy ...
Read more

java - Jtable duplicate Rows -

-
Image
when add new rows in jtable duplicate rows many times this method update jtable public final void tableaucomande(){ ((defaulttablemodel)tabcommande.getmodel()).getdatavector().clear(); session s=hibernateutil.getsessionfactory().opensession(); criteria cr1= s.createcriteria(commande.class); list <commande> lcs=cr1.list() ; criteria cr2= s.createcriteria(lignecommande.class); list <lignecommande> lck=cr2.list() ; if(lcs.size()!=0 && lck.size()!=0) { (commande lc:lcs) { for(lignecommande lig:lck){ ((defaulttablemodel)tabcommande.getmodel()).addrow(new object[]{ lc.getid_cmd(),lc.getnumcmd(),lc.getdate_cmd(),lc.getclient().getnomprenom(), lig.getproduit().getlib_prd(),lig.getquantite(),lig.getproduit().getid_prd() }); } } // tabcommande.setrowselectioninterval(tabcommande.getrowcount()-1,tabcommande.getrowcount()-1); } } this code add add data rows displaye...
Read more

validation - How to pass paramaters like unix into windows batch file -

-
i need pass various parameters .cmd file unix format file.cmd -configuration=value -source=value -flag but, try this: startlocal @echo off cls setlocal set cmdline=%* set configuration= set source= set badargs= set validation= goto main :splitargs echo splitargs(%*) if "%*" neq "" ( /f "tokens=1,2,* delims== " %%i in ("%*") call :assignkeyvalue %%i %%j & call :splitargs %%k ) goto :eof :assignkeyvalue echo assignkeyvalue(%1, %2) if /i %1==-configuration ( set configuration=%2 ) else if /i %1==-source ( set source=%2 ) else ( rem append unrecognised [key,value] badargs echo unknown key %1 set badargs=%badargs%[%1, %2] ) goto :eof :validate echo validating set validation=fail if defined configuration ( echo -configuration ok if defined source ( echo -source ok if not defined badargs ( set validation=success ) ) ) goto :eof :main cl...
Read more