how to convert string to integer in sql statement
CodeGuru Home VC++ / MFC / C++ .NET / C# Visual Basic VB Forums Developer.com
Results 1 to 6 of 6

Thread: how to convert string to integer in sql statement

  1. #1
    Join Date
    Feb 2004
    Posts
    74

    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!

  2. #2
    Join Date
    Apr 2005
    Posts
    576

    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 08:03 AM.

  3. #3
    Join Date
    Feb 2004
    Posts
    74

    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!

  4. #4
    Join Date
    Jun 2001
    Location
    Sri Lanka
    Posts
    272

    Re: how to convert string to integer in sql statement

    Quote 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 :
    Quote 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

  5. #5
    Join Date
    Feb 2004
    Posts
    74

    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

  6. #6
    Join Date
    Jun 2001
    Location
    Sri Lanka
    Posts
    272

    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
  •  


Windows Mobile Development Center


Click Here to Expand Forum to Full Width

This is a CodeGuru survey question.


Featured


HTML5 Development Center