how to convert string to integer in sql statement

[Alif]

hi,

I have a VB form that contains a textbox 'Text1' and a command button 'show report'. the 'Text1' textbox value should be used during report run,
see my query in the data report command, my question refers to this query,


Code:
select * from DeviceType where
DeviceType.ID = 'frmDataEnv.CInt(Text1.Text)'

1. I added the CInt because the Text1 textbox is not from type integer but string. an dthe 'ID" in the DB table is integer.
can i set the Text1 in the properties, if so, how ?

2. while running the DataReport1.show an error message occurs saying that 'Syntax error converting the varchar value 'frmDataEnv.CInt(Text1.Text) to a column of data type int.


thanks!

[klintan]

You are comparing the string 'frmDataEnv.CInt(Text1.Text)' to an Int, anything between '' is a string.

I assume you are using MS Access and the VB code is inside Access Forms (something which I never have used), but something like:

select * from DeviceType where 
DeviceType.ID = CInt(frmDataEnv.Text1.Text)

should work.

If the code is in VB 6 something like:

IF IsNumeric(frmDataEnv.Text1.Text) THEN
   Dim command as String
   command = 
       "select * from DeviceType where DeviceType.ID = " + 
       frmDataEnv.Text1.Text
   ...
ELSE
    ' Do something
END IF

should work (I have not used VB for a while so maybe my syntax is not completely correct)

[Alif]

i'm using access DB, and the sql statement is not in the code, but in the access, so do you know how to use the cint ?
thanks a lot!

[srinika]

.... sql statement is not in the code, but in the access ...

Where in Access ? In a Query ?
What is ur frmDataEnv ? Access Form or VB Form ?

What do u mean by :

I have a VB form

Is it VB or Access only.

Can't u use it with the following notation (To make it simple for u)
Backend Database : MS Access
Frontend Program : VB
From VB program Queries are made using ADO
Simple & easy.

If u follow that the answer of klintan will resolve ur issue?

U can get a good idea from the Data Project of example VB projects

[Alif]

I am using vb, and should run a report (vb data report).

my VB form contains a textbox 'Text1' and a command button 'show report'. the 'Text1' textbox value should be used during report run,
the SQL query is set in the data report command, and my question refers to this query,

select * from DeviceType where 
DeviceType.ID = 'frmDataEnv.CInt(Text1.Text)'

how to use the cint in order to replate the text (string) to an integer ?

can i use klintan suggestion, but instead of 'dim command as string', to use 'dim command as integer (for the whole statement ?)'

IF IsNumeric(frmDataEnv.Text1.Text) THEN
   Dim command as String
   command = 
       "select * from DeviceType where DeviceType.ID = " + 
       frmDataEnv.Text1.Text
   ...
ELSE
    ' Do something
END IF

thanks

[srinika]

Please Alif,

be specific.

Where r u writing the select ... statement ?
If u r writing it in VB, klintan suggestion will definitely work.

U can't define a variable as an integer and assign a String to it!!

eg:
dim command as integer
command = "Select ..... " is incorrect - u will get a type mismatch error

Advise:

First try to get the report using a table name (by the DataEnvironment, Connection, Command)
Then get a report using a SQL query (built on to the report)
Then get a report using a query passed by a VB Form
Then go to the task of passing parameters .

U'll get useful info in VB Help (MSDN) Search for "Creating a simple data report"