-
October 27th, 2005, 06:31 AM
#1
how to convert string to integer in sql statement
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!
-
October 27th, 2005, 06:53 AM
#2
Re: how to convert string to integer in sql statement
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:
Code:
select * from DeviceType where
DeviceType.ID = CInt(frmDataEnv.Text1.Text)
should work.
If the code is in VB 6 something like:
Code:
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)
Last edited by klintan; October 27th, 2005 at 07:03 AM.
-
October 27th, 2005, 07:10 AM
#3
Re: how to convert string to integer in sql statement
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!
-
October 27th, 2005, 07:52 AM
#4
Re: how to convert string to integer in sql statement
Originally Posted by Alif
.... 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 :
Originally Posted by Alif
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
-
October 27th, 2005, 08:31 AM
#5
Re: how to convert string to integer in sql statement
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,
Code:
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 ?)'
Code:
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
-
October 27th, 2005, 08:57 AM
#6
Re: how to convert string to integer in sql statement
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"
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|