SQL Questions

tcgoh

Active Member

Licensed User

Longtime User

• Dec 24, 2007

• #1

Hi Erel,

I am trying out this this SQL expression

SUB btnExecute_click
cmd.CommandText=" SELECT DISTINCT NAME, RANK, CODE FROM CUSTS ORDER BY NAME"
cmd.ExecuteTable("table1",0)
cmd.CommandText=" SELECT DISTINCT DUTY, DATE1, TIME, FLTNO, DATE2 FROM CUSTS WHERE NAME='JOHN' ORDER BY DATE1"
cmd.ExecuteTable("table2",0)
end sub

From the WHERE NAME='JOHN', how could I replace 'JOHN' form a textbox.text?

Thanks
TC

 

B

BjornF

Active Member

Licensed User

Longtime User

• Dec 24, 2007

• #2

Dear TC,

Have a look at the demo-program by Giannimaione (thread http://www.b4x.com/forum/showthread.php?t=310&highlight=demosql)

It is a very short program but it contains many useful hints, amongst them the answer to your question.

all the best,

Björn

*****************************

quote=CHR(39)

Sub Button1_Click
'insert data
MyData1=quote & "ALFA" & quote
'or MyData1=quote & textbox.Text & quote
MyData2=Quote & "BETA" & quote

text="insert into NameTable (Field1, Field2) values ("
text=text & MyData1 & "," & MyData2 & ")"
Msgbox(text)
cmd.CommandText=text
cmd.ExecuteNonQuery
End Sub

 

Erel

B4X founder

Staff member

Licensed User

Longtime User

• Dec 24, 2007

• #3

Try:

B4X:

   cmd.CommandText="... WHERE NAME = '" & textbox.text & "' ORDER ..."

 

tcgoh

Active Member

Licensed User

Longtime User

• Dec 26, 2007

• #4

Got it!

Thanks Erel.

 

tcgoh

Active Member

Licensed User

Longtime User

• Dec 29, 2007

• #5

Hi AnyOne with SQL knowledge,

1st of all thanks for all the help, now I'm learning to program SQL slowly.

I have a problem now with WHERE statement:

If WHERE = ' name with a' in between' How do I go around with this ' problem?

Thanks

 

D

dzt

Active Member

Licensed User

• Dec 29, 2007

• #6

Add one more ' beside the one that exists

 

tcgoh

Active Member

Licensed User

Longtime User

• Dec 31, 2007

• #7

Thanks! But what if the database comes with names which have ' ?

How do I filter out these names?

Thanks n Happy New year.

 

D

dzt

Active Member

Licensed User

• Dec 31, 2007

• #8

You should search for it and replace it

StrReplace("name", "'", "''")