Results 1 to 5 of 5

Thread: [RESOLVED] error converting data type varchar to bit in MS SQL

  1. #1

    Thread Starter
    Lively Member nvierros's Avatar
    Join Date
    Nov 2005
    Location
    Melbourne, Australia
    Posts
    110

    Resolved [RESOLVED] error converting data type varchar to bit in MS SQL

    hey guys can someone please tell me what im doing wrong.

    My sproc is something like this:

    Code:
    create proc dt_addnewcustomer
    @CustIsInactive bit,
    @CustFName varchar(100)
    SET NOCOUNT ON
    AS 
    insert into tlbcustomers (CustFName, CustIsInactive) VALUES (@CustFname, @CustIsInactive)
    
    go
    my vbcode is like this:

    VB Code:
    1. dim CustIsInactive as boolean
    2. CustIsInactive = false
    3. Set adocommand = New ADODB.Command
    4. With adocommand
    5. .ActiveConnection = con
    6. .CommandType = adCmdStoredProc
    7. .CommandText = "dt_addnewcustomer"
    8. .Parameters.Append .CreateParameter("@CustFName", adVarChar, adParamInput, 100, Trim(CustFName.Text))
    9. .Parameters.Append .CreateParameter("@CustIsInactive", adboolean, adParamInput, 1, CustIsInactive)
    10. .Execute , , adCmdStoredProc + adExecuteNoRecords
    11. Set adocommand = Nothing
    12. end with

    I really dont see what im doing wrong, and it's driving me nuts!! Someone please help

  2. #2
    MS SQL Powerposter szlamany's Avatar
    Join Date
    Mar 2004
    Location
    Connecticut
    Posts
    18,263

    Re: error converting data type varchar to bit in MS SQL

    SET NOCOUNT ON goes in the AS statement...

    Code:
    create proc dt_addnewcustomer
    @CustIsInactive bit,
    @CustFName varchar(100)
    AS 
    SET NOCOUNT ON
    insert into tlbcustomers (CustFName, CustIsInactive) VALUES (@CustFname, @CustIsInactive)
    
    go
    Why are you using a BIT parameter? Is that the datatype of CustIsInactive? adboolean seems like it did not properly case after being typed - is that accurate? I've never used a BIT parameter or datatype myself - try changing the parameter definition to INT and see if that works...

    *** Read the sticky in the DB forum about how to get your question answered quickly!! ***

    Please remember to rate posts! Rate any post you find helpful - even in old threads! Use the link to the left - "Rate this Post".

    Some Informative Links:
    [ SQL Rules to Live By ] [ Reserved SQL keywords ] [ When to use INDEX HINTS! ] [ Passing Multi-item Parameters to STORED PROCEDURES ]
    [ Solution to non-domain Windows Authentication ] [ Crazy things we do to shrink log files ] [ SQL 2005 Features ] [ Loading Pictures from DB ]

    MS MVP 2006, 2007, 2008

  3. #3

    Thread Starter
    Lively Member nvierros's Avatar
    Join Date
    Nov 2005
    Location
    Melbourne, Australia
    Posts
    110

    Re: error converting data type varchar to bit in MS SQL

    Quote Originally Posted by szlamany
    SET NOCOUNT ON goes in the AS statement...
    My bad, in my sproc it is in the as statement.

    I have tried everything, if i change it to int the error changes to error converting data type varchar to int??

    It is if the parameter is applying it as varchar even though i have explicity told it to be boolean

  4. #4
    PowerPoster
    Join Date
    Oct 2002
    Location
    British Columbia
    Posts
    9,758

    Re: error converting data type varchar to bit in MS SQL

    Using the Bit datatype and adBoolean is fine.

    The problem is you have reversed the parameters when appending them to the Command object - @CustIsInactive should be appended first.

    Or

    If you are using ADO 2.7 or higher, set the .NamedParameters property to True.

  5. #5

    Thread Starter
    Lively Member nvierros's Avatar
    Join Date
    Nov 2005
    Location
    Melbourne, Australia
    Posts
    110

    Re: error converting data type varchar to bit in MS SQL

    Quote Originally Posted by brucevde
    If you are using ADO 2.7 or higher, set the .NamedParameters property to True.
    Bruce your a GUN! thanks sooo much

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width