I have a database with 2 tables.
I update the first table - Insert a record in the second table.
All is ok at this point.
I then try to get the record from the second table and it fails.
Is there something special I should do.
Got some code to show?
Are you trying to reuse the same statement handle without freeing it first?
Freed the first set of handles.
Used new ones for the second table and freed as completed.
It creates a record but I read it back and it is not correct.
The rec_id (auto number) that is returned on the read is wrong but I get no errors.
Still need to see some code. Auto numbers are assigned by the database driver.
The routine with the error.
sub updstkquote(){
string sel,error,dissym;
dstring cur_symbol[10],cur_tick[5],cur_name[40];
dstring prc_symbol[10],prc_date[18];
double cur_lstprc = 0;
double prc_lstprc = 0;
int cur_id,prc_id,nReturn,sptr,eptr,cnt,prccnt,curprcidx;
int stkstmt_select,stkstmt_update,stkstmt_insert,stkstmt_delete;
int prcstmt_select,prcstmt_update,prcstmt_insert,prcstmt_delete;
//The database object
CDatabase stock_db;
nReturn = stock_db.Connect("Microsoft Access Driver (*.mdb)",GetStartPath() + "Stkkepr.mdb","",0);
IF nReturn = 0 {
MessageBox(0,"Failed to open Database","Error");
return;
}
site = "finance.yahoo.com";
ref = "Stock Keeper";
prccnt = 0;
hopen = InternetOpenA(ref,INTERNET_PRECONFIG,"","",0);
IF hopen {
hconnect = InternetConnectA(hopen,site,80,"","",INTERNET_SERVICE_HTTP,0,0);
IF hconnect != 0 {
sel = "SELECT * FROM stock";
stkstmt_select = stock_db.ExecSQL(sel);
error = stock_db.GetErrorCode(stkstmt_select);
IF(Len(error)) {
messagebox(0,error,"OPEN ERROR");
}
IF stkstmt_select {
while stock_db.Get(stkstmt_select){
stock_db.GetData(stkstmt_select,1,cur_id,TYPE_INT);
stock_db.GetData(stkstmt_select,2,cur_symbol,TYPE_STRING);
stock_db.GetData(stkstmt_select,6,cur_name,TYPE_STRING);
stock_db.GetData(stkstmt_select,14,cur_lstprc,TYPE_DOUBLE);
doc = "d/quotes.txt?s=" + cur_symbol + "&f=sl1n";
hhttp = HttpOpenRequestA(hconnect,"GET",doc,null,"",null,null,0);
IF hhttp {
IF HttpSendRequestA(hhttp,null,0,null,0) {
DO {
InternetReadFile(hhttp,buffer,254,br);
buffer = left$(buffer,br);
IF(br) {
sptr = 1;
eptr = 1;
cnt = 1;
while((buffer[sptr] != ',') AND (buffer[sptr] != null)){
sptr = sptr + 1;
}
sptr = sptr + 2;
eptr = sptr;
while((buffer[sptr] != ',') AND (buffer[sptr] != null)){
cnt = cnt + 1;
sptr = sptr + 1;
}
sptr = sptr + 3;
if (STRTONUM(StrMid(buffer,eptr,cnt)) = cur_lstprc) {
cur_tick = "Even";
} else if (STRTONUM(StrMid(buffer,eptr,cnt)) > cur_lstprc) {
cur_tick = "Up";
} else {
cur_tick = "Down";
}
cur_lstprc = STRTONUM(StrMid(buffer,eptr,cnt));
eptr = sptr;
cnt = 1;
while((buffer[sptr] != '"') AND (buffer[sptr] != null)){
cnt = cnt + 1;
sptr = sptr + 1;
}
sptr = sptr + 1;
cur_name = strmid(buffer,eptr,cnt);
stkstmt_update = NULL;
stkstmt_update = stock_db.PrepareSQL("UPDATE stock SET name=?,lstprc=?,tick=? WHERE id="+NumToStr(cur_id));
stock_db.BindParameter(stkstmt_update,1,cur_name,TYPE_STRING,40);
stock_db.BindParameter(stkstmt_update,2,cur_lstprc,TYPE_DOUBLE,9);
stock_db.BindParameter(stkstmt_update,3,cur_tick,TYPE_STRING,5);
stock_db.Execute(stkstmt_update);
error = stock_db.GetErrorText(stkstmt_update);
IF(Len(error)) {
messagebox(0,error,"WRITE ERROR");
}
stock_db.FreeSQL(stkstmt_update);
Price[prccnt].stkid = cur_id;
Price[prccnt].symbol = cur_symbol;
Price[prccnt].date = today();
Price[prccnt].lstprc = cur_lstprc;
prccnt = prccnt + 1;
}
buffer = "";
} UNTIL br = 0;
}
}
InternetCloseHandle(hhttp);
}
stock_db.FreeSQL(stkstmt_select);
}
InternetCloseHandle(hconnect);
curprcidx = 0;
do {
prcstmt_insert = stock_db.ExecSQL("INSERT INTO hstprc (symbol,prcdt,price) VALUES('bogus','bogus',0)");
error = stock_db.GetErrorText(prcstmt_insert);
IF(Len(error)) {
messagebox(0,error,"PRICE INSERT ERROR");
}
stock_db.FreeSQL(prcstmt_insert);
dissym = "bogus";
sel = "SELECT * FROM hstprc WHERE hstprc.symbol = dissym";
prcstmt_select = stock_db.ExecSQL(sel);
error = stock_db.GetErrorText(prcstmt_select);
IF(Len(error)) {
messagebox(0,error,"PRICE SEL ERROR");
}
stock_db.GetData(prcstmt_select,1,prc_id,TYPE_INT);
error = stock_db.GetErrorText(prcstmt_select);
IF(Len(error)) {
messagebox(0,error,"PRICE GET DATA ERROR");
}
stock_db.FreeSQL(prcstmt_select);
messagebox(0,numtostr(prc_id),"DEBUG");
prcstmt_update = stock_db.PrepareSQL("UPDATE hstprc SET symbol=?,prcdt=?,price=? WHERE id="+NumToStr(prc_id));
stock_db.BindParameter(prcstmt_update,1,Price[curprcidx].symbol,TYPE_STRING,10);
stock_db.BindParameter(prcstmt_update,2,Price[curprcidx].date,TYPE_STRING,25);
stock_db.BindParameter(prcstmt_update,3,Price[curprcidx].lstprc,TYPE_DOUBLE,9);
stock_db.BindParameter(prcstmt_update,4,Price[curprcidx].stkid,TYPE_INT,9);
stock_db.Execute(prcstmt_update);
error = stock_db.GetErrorText(stkstmt_update);
IF(Len(error)) {
messagebox(0,error,"WRITE ERROR");
}
stock_db.FreeSQL(prcstmt_update);
curprcidx = curprcidx + 1;
} UNTIL curprcidx = prccnt;
}
}
stock_db.Disconnect();
InternetCloseHandle(hopen);
return;
}
dissym = "bogus";
sel = "SELECT * FROM hstprc WHERE hstprc.symbol = dissym";
Seems to be your problem. The select statement is just a string so your querying "dissym" and not "bogus". The statement should either be:
dissym = "bogus";
sel = "SELECT * FROM hstprc WHERE hstprc.symbol = " + dissym;
or:
sel = "SELECT * FROM hstprc WHERE hstprc.symbol = bogus";
Changed it - still get error:
Too few parameters - expected 1.
The records gets created - but the select statement fails.
Which select statement?
sel = "SELECT * FROM hstprc WHERE hstprc.symbol = bogus";
I still think that select statement is wrong. Should probably be:
sel = "SELECT * FROM hstprc WHERE symbol = 'bogus'";
Vikki would know in an instance ;)
Had a problem with that as well.
I did get it to work with the other version:
sel = "SELECT * FROM hstprc WHERE hstprc.symbol = 'bogus'";
Also had to add:
stock_db.Get(prcstmt_select);
just before the GetData statement - Duh.
Moving on to my next undiscovered error - thanks for the help.
Quote from: Paul Turley on June 30, 2006, 01:28:58 PM
I still think that select statement is wrong.ÂÃ, Should probably be:
sel = "SELECT * FROM hstprc WHERE symbol = 'bogus'";
Vikki would know in an instance ;)
As usual days late and....ÂÃ, :)
For strings, SQL needs single quotes around strings. For numbers, no quotes are needed.
Sorry, I should have known the answer to that one. I learned the hard way with PHP and MySQL. My query strings would never work, until I looked at phpMyAdmin's in detail.