[nsbasic-ce] Re: SQL WHERE
What do you want to show up as output in this example? You're selecting 'every field in table namedb', so the msgbox command doesn't understand how to show all those fields, in potentially more than one record. If you just want the first field, (firstname,) of the first record, then you could use code like: cmd = select * from namedb where lastname = ' tbLastName.text ' set r = db.execute (cmd) val = r(1)(1) msgbox val If you want both firstname and lastname, you could change the val line to: val = r(1)(1) r(1)(2) Does this help? I think that you and Woody might have been talking at cross-purposes, because he was thinking of looping over a large result set, while you only wanted to pull a few values out of it. I believe that using a single quote inside your cmd is better SQLite syntax than escaping a by doubling it up. --- In nsbasic...@yahoogroups.com, EMERSON VIER emersonv...@... wrote: Sorry I make mistake to write here I use this form cmd=SELECT * FROM NameDB WHERE lastname = tbLastName.Text showStatus cmd Set r=db.Execute( cmd) MsgBox r How I get the result for this action? I use this database NameDB sample FirstName LastName Joe Bob Henry Bill Tedy Herman Thx for all EMERSON VIER --- In nsbasic...@yahoogroups.com, Harold Wood hwoody2wood@ wrote: you need a properly formatted query. select * from [table name goes here] where [column name goes here] = ' tbLastName.Text ' --- On Wed, 5/20/09, EMERSON VIER emersonvier@ wrote: From: EMERSON VIER emersonvier@ Subject: [nsbasic-ce] SQL WHERE To: nsbasic...@yahoogroups.com Date: Wednesday, May 20, 2009, 10:16 PM How I found the last name on the sample on TN15 I try cmd=SELECT * FROM WHERE NameDB lastname = tbLastName.Text showStatus cmd Set r=db.Execute( cmd) MsgBox r But this not return the line. EMERSON VIER --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups nsb-ce group. To post to this group, send email to nsb-ce@googlegroups.com To unsubscribe from this group, send email to nsb-ce+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/nsb-ce?hl=en -~--~~~~--~~--~--~---
Re: [nsbasic-ce] Re: SQL WHERE
yeah, i was thinking how to retrieve all rows in a result set. thanks woody --- On Sat, 5/23/09, Chris Kenworthy chrisk...@gmail.com wrote: From: Chris Kenworthy chrisk...@gmail.com Subject: [nsbasic-ce] Re: SQL WHERE To: nsbasic...@yahoogroups.com Date: Saturday, May 23, 2009, 5:53 PM What do you want to show up as output in this example? You're selecting 'every field in table namedb', so the msgbox command doesn't understand how to show all those fields, in potentially more than one record. If you just want the first field, (firstname,) of the first record, then you could use code like: cmd = select * from namedb where lastname = ' tbLastName.text ' set r = db.execute (cmd) val = r(1)(1) msgbox val If you want both firstname and lastname, you could change the val line to: val = r(1)(1) r(1)(2) Does this help? I think that you and Woody might have been talking at cross-purposes, because he was thinking of looping over a large result set, while you only wanted to pull a few values out of it. I believe that using a single quote inside your cmd is better SQLite syntax than escaping a by doubling it up. --- In nsbasic...@yahoogro ups.com, EMERSON VIER emersonvier@ ... wrote: Sorry I make mistake to write here I use this form cmd=SELECT * FROM NameDB WHERE lastname = tbLastName.Text showStatus cmd Set r=db.Execute( cmd) MsgBox r How I get the result for this action? I use this database NameDB sample FirstName LastName Joe Bob Henry Bill Tedy Herman Thx for all EMERSON VIER --- In nsbasic...@yahoogro ups.com, Harold Wood hwoody2wood@ wrote: you need a properly formatted query. select * from [table name goes here] where [column name goes here] = ' tbLastName.Text ' --- On Wed, 5/20/09, EMERSON VIER emersonvier@ wrote: From: EMERSON VIER emersonvier@ Subject: [nsbasic-ce] SQL WHERE To: nsbasic...@yahoogro ups.com Date: Wednesday, May 20, 2009, 10:16 PM How I found the last name on the sample on TN15 I try cmd=SELECT * FROM WHERE NameDB lastname = tbLastName.Text showStatus cmd Set r=db.Execute( cmd) MsgBox r But this not return the line. EMERSON VIER --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups nsb-ce group. To post to this group, send email to nsb-ce@googlegroups.com To unsubscribe from this group, send email to nsb-ce+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/nsb-ce?hl=en -~--~~~~--~~--~--~---
[nsbasic-ce] Re: SQL WHERE
I undested your information, but I need search the data on my database by SQL function WHERE or other. Can you help me? EMERSON VIER --- In nsbasic...@yahoogroups.com, Harold Wood hwoody2w...@... wrote: the query looks good now. remember that nsbasic treats a record set as a multidimensial array, with so you shoudl first look at the recordcount property fo r to determine how many rows were returned then loop thru the columns to return the individual fields. Dim recsBack dim recRead dim curRec dim Cols recsBack = r.RecordCount recRead = 1 while recRead recsback for Cols = 1 to r(recRead).Count Step 1 msgbox r(recRead)(Cols) next RecRead = recRead + 1 wend see if that helps. Woody --- On Thu, 5/21/09, EMERSON VIER emersonv...@... wrote: From: EMERSON VIER emersonv...@... Subject: [nsbasic-ce] Re: SQL WHERE To: nsbasic...@yahoogroups.com Date: Thursday, May 21, 2009, 12:07 PM Sorry I make mistake to write here I use this form cmd=SELECT * FROM NameDB WHERE lastname = tbLastName.Text showStatus cmd Set r=db.Execute( cmd) MsgBox r How I get the result for this action? I use this database NameDB sample FirstName LastName Joe Bob Henry Bill Tedy Herman Thx for all EMERSON VIER --- In nsbasic...@yahoogro ups.com, Harold Wood hwoody2wood@ ... wrote: you need a properly formatted query. select * from [table name goes here] where [column name goes here] = ' tbLastName.Text ' --- On Wed, 5/20/09, EMERSON VIER emersonvier@ ... wrote: From: EMERSON VIER emersonvier@ ... Subject: [nsbasic-ce] SQL WHERE To: nsbasic...@yahoogro ups.com Date: Wednesday, May 20, 2009, 10:16 PM How I found the last name on the sample on TN15 I try cmd=SELECT * FROM WHERE NameDB lastname = tbLastName.Text showStatus cmd Set r=db.Execute( cmd) MsgBox r But this not return the line. EMERSON VIER --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups nsb-ce group. To post to this group, send email to nsb-ce@googlegroups.com To unsubscribe from this group, send email to nsb-ce+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/nsb-ce?hl=en -~--~~~~--~~--~--~---
Re: [nsbasic-ce] Re: SQL WHERE
you arent understanding. use a query to return a record set that matches your query criteria, then loop thru the record set per the example. my method is used after teh query has selected the appropriate records from your database. take a look at the example in http://www.nsbasic.com/ce/info/technotes/TT09.htm it shows looping thru rows and columns after a query has already selected my desired rows. Woody --- On Thu, 5/21/09, EMERSON VIER emersonv...@yahoo.com.br wrote: From: EMERSON VIER emersonv...@yahoo.com.br Subject: [nsbasic-ce] Re: SQL WHERE To: nsbasic...@yahoogroups.com Date: Thursday, May 21, 2009, 4:31 PM I undested your information, but I need search the data on my database by SQL function WHERE or other. Can you help me? EMERSON VIER --- In nsbasic...@yahoogro ups.com, Harold Wood hwoody2wood@ ... wrote: the query looks good now. remember that nsbasic treats a record set as a multidimensial array, with so you shoudl first look at the recordcount property fo r to determine how many rows were returned then loop thru the columns to return the individual fields. Dim recsBack dim recRead dim curRec dim Cols recsBack = r.RecordCount recRead = 1 while recRead recsback for Cols = 1 to r(recRead).Count Step 1 msgbox r(recRead)(Cols) next RecRead = recRead + 1 wend see if that helps. Woody --- On Thu, 5/21/09, EMERSON VIER emersonvier@ ... wrote: From: EMERSON VIER emersonvier@ ... Subject: [nsbasic-ce] Re: SQL WHERE To: nsbasic...@yahoogro ups.com Date: Thursday, May 21, 2009, 12:07 PM Sorry I make mistake to write here I use this form cmd=SELECT * FROM NameDB WHERE lastname = tbLastName.Text showStatus cmd Set r=db.Execute( cmd) MsgBox r How I get the result for this action? I use this database NameDB sample FirstName LastName Joe Bob Henry Bill Tedy Herman Thx for all EMERSON VIER --- In nsbasic...@yahoogro ups.com, Harold Wood hwoody2wood@ ... wrote: you need a properly formatted query. select * from [table name goes here] where [column name goes here] = ' tbLastName.Text ' --- On Wed, 5/20/09, EMERSON VIER emersonvier@ ... wrote: From: EMERSON VIER emersonvier@ ... Subject: [nsbasic-ce] SQL WHERE To: nsbasic...@yahoogro ups.com Date: Wednesday, May 20, 2009, 10:16 PM How I found the last name on the sample on TN15 I try cmd=SELECT * FROM WHERE NameDB lastname = tbLastName.Text showStatus cmd Set r=db.Execute( cmd) MsgBox r But this not return the line. EMERSON VIER --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups nsb-ce group. To post to this group, send email to nsb-ce@googlegroups.com To unsubscribe from this group, send email to nsb-ce+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/nsb-ce?hl=en -~--~~~~--~~--~--~---
