SQL Server Performance

Error converting data type nvarchar to float

Discussion in 'SQL Server 2005 General Developer Questions' started by caddyjoe77, Aug 4, 2007.

  1. caddyjoe77 New Member

    wondering what i may be doing wrong and i think it is on the SQL side but not 100 percent sure.
    I have a DB that has 8 tables and what i am trying to do is once i get the result set of my query pulling information from 3 tables, which i built a hyperlink in ASP and an open new window upon click.
    When i try and click the hyperlink I get the following error "Error converting data type nvarchar to float", so i looked it up on technet and am not sure exactly what it is trying to convert? What is a float; isnt that a floating integer decimal point?
    here is the DDR for my table i have my query setup against. SET
    ANSI_NULLS ONGOSET
    QUOTED_IDENTIFIER ONGOCREATE
    TABLE [dbo].[tbl3215]([id] [float]
    NULL,[IttNumber] [float]
    NULL,[IttTitle] [nvarchar]
    (255) NULL,[IttDescription] [nvarchar]
    (max) NULL,[IttJustification] [nvarchar]
    (max) NULL,[TechSolution] [nvarchar]
    (max) NULL,[DateReceived] [datetime]
    NULL,[DateRequired] [datetime]
    NULL,[Customer] [nvarchar]
    (255) NULL,[OrgID] [float]
    NULL,[Funded] [float]
    NULL,[HBApID] [float]
    NULL,[Disapproved] [nvarchar]
    (255) NULL,[DisReason] [nvarchar]
    (255) NULL,[Completed] [float]
    NULL,[CompletionDate] [datetime]
    NULL,[Priority] [nvarchar](255) NULL
    )
    ON [PRIMARY]
    my sql string is very simple select * from dbo.tbl3215 where ittnumber = @ittnumber
    it is supposed to pull all the tables from a previously selected item, but iu keep getting that error.
    Thank You
    J
  2. FrankKalis Moderator

    It looks as if you don't have some data in your table that isn't convertible to a number. Have you checked this?
  3. caddyjoe77 New Member

    yes, i can run the query all day in the query analyzer, that is why i am so stumped.
  4. FrankKalis Moderator

    Does that mean that it works in SQL Server, but not when called from a client? What is the client sending? Is it probably a formatted string with ',' or other characters?
  5. Madhivanan Moderator

    What is the value you supplied to @ittnumber?
  6. martins New Member

    My guess is that the problem lies within your front-end application. The value entered for ittnumber (on the front end) is probably not a valid float, and then when the parameter is passed to SQL the conversion error is returned.
  7. Adriaan New Member

    It may be something like the workstation has the wrong decimal separator, and the client app code is not anticipating that. You're likely to run into more such localization issues especially concerning date formats.
  8. caddyjoe77 New Member

    How would i determine that?
    thank you
  9. caddyjoe77 New Member

    Here is the asp code for the front end page.
    Code:
    [B]1    [/B]<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" DataSourceID="SqlDataSource2" AllowPaging="True" AllowSorting="True" CellPadding="4" ForeColor="#333333" GridLines="None" PageSize="50" SelectedIndex="0" BorderColor="#E0E0E0" BorderStyle="Solid" BorderWidth="1px"> 
    [B]2    [/B]            <Columns> 
    [B]3    [/B]                <asp:HyperLinkField DataTextField="ittnumber" target="_blank" HeaderText="ittnumber" DataNavigateUrlFields="ittnumber" DataNavigateUrlFormatString="results.aspx?ittnumber={0}" /> 
    [B]4    [/B]                <asp:BoundField DataField="itttitle" HeaderText="3215Title" SortExpression="itttitle" />   
    [B]5    [/B]                <asp:BoundField DataField="daterequired" HeaderText="DateRequired" SortExpression="daterequired" /> 
    [B]6    [/B]                </Columns> 
    [B]7    [/B]            <FooterStyle BackColor="#5D7B9D" Font-Bold="True" ForeColor="White" /> 
    [B]8    [/B]            <RowStyle BackColor="#F7F6F3" ForeColor="#333333" /> 
    [B]9    [/B]            <EditRowStyle BackColor="#999999" /> 
    [B]10   [/B]            <SelectedRowStyle BackColor="#E2DED6" Font-Bold="True" ForeColor="#333333" /> 
    [B]11   [/B]            <PagerStyle BackColor="#284775" ForeColor="White" HorizontalAlign="Center" /> 
    [B]12   [/B]            <HeaderStyle BackColor="#5D7B9D" Font-Bold="True" ForeColor="White" /> 
    [B]13   [/B]            <AlternatingRowStyle BackColor="White" ForeColor="#284775" /> 
    [B]14   [/B]        </asp:GridView> 
    [B]15   [/B]        <asp:SqlDataSource ID="SqlDataSource2" runat="server" ConnectionString="<%$ ConnectionStrings:SCX_3215ConnectionString3 %>" 
    [B]16   [/B]            SelectCommand="select distinct dbo.tbl3215.ittnumber, dbo.tbl3215.itttitle,dbo.tbl3215.ittdescription, dbo.tbl3215.daterequired from dbo.tblassignment inner join dbo.tbl3215 on dbo.tbl3215.ittnumber = dbo.tblassignment.ittnumber inner join dbo.tblfltwrkcntr on dbo.tblfltwrkcntr.fltwkcntrid = dbo.tblassignment.assfltwrkid where (itttitle is not null or ittdescription is not null) and dbo.tblfltwrkcntr.wrkcntr = @temp"> 
    [B]17   [/B]            <SelectParameters> 
    [B]18   [/B]                <asp:ControlParameter ControlID="DropDownList1" Name="temp" PropertyName="SelectedValue" /> 
    [B]19   [/B]            </SelectParameters> 
    [B]20   [/B]        </asp:SqlDataSource> 
    
    there is no code behind as i am not quite sure exactly how to write something to make that work; very inexperienced in coding asp and vb.net...
    the format of the date goes like this yyyymmdd so for example today would be 20070807 is that an invalid floating integer? Honestly what i could do is select the id of the row and hyperlink that but i am interested to see why this doesnt work the way it is now.
  10. caddyjoe77 New Member

    the value supplied to ittnumber is dependent of which number i click on and displayed in the gridview.
    for example, if i select ittnumber 20070805 it should query the database and pull all the colums for that number.
  11. martins New Member

    Your asp page calls another page as soon as you click on the ittnumber (<"results.aspx?ittnumber={0}")
    You should check that page and see exactly what it is doing.
    Just remember, especially with scripting languages like vbscript, you need to explicitly convert or cast the data passed as parameter into your stored proc and build in some error resolution code in case it doesn't conform to your data type in SQL. Also remember that data types in vbscript are not always equal to the data type in SQL with (seemingly) the same name. For instance the integer datatype integer in vbscript is smaller that int in SQL. This might cause some issues in certain cases where the data types might be incompatible.
    Have a look at the above mentioned page and see if you can see what happens to the ittnumber variable and how it is passed to the stored proc.
  12. caddyjoe77 New Member

    [quote user="martins"]
    Your asp page calls another page as soon as you click on the ittnumber (<"results.aspx?ittnumber={0}")
    You should check that page and see exactly what it is doing.
    Just remember, especially with scripting languages like vbscript, you need to explicitly convert or cast the data passed as parameter into your stored proc and build in some error resolution code in case it doesn't conform to your data type in SQL. Also remember that data types in vbscript are not always equal to the data type in SQL with (seemingly) the same name. For instance the integer datatype integer in vbscript is smaller that int in SQL. This might cause some issues in certain cases where the data types might be incompatible.
    Have a look at the above mentioned page and see if you can see what happens to the ittnumber variable and how it is passed to the stored proc.
    [/quote]
    Thanks, you are correct it calls a page called details.aspx which i was setting up as a grid view and from there i was going to allow updating..etc
    so i never knew how to declare a variable inside SQL and now is when i am getting the error inside instead of returned on the page. I am sorry for the misinformation that i posted above, i was confused between the web page and the backend sql query. I am still pretty new at this...
    here is the query.
    DECLARE @temp float (the only reason i made it a float is because thats what sql set it too...can i change this?
    select distinct dbo.tbl3215.ittnumber, dbo.tbl3215.itttitle,dbo.tbl3215.ittdescription, dbo.tbl3215.daterequired from dbo.tblassignment inner join dbo.tbl3215
    on dbo.tbl3215.ittnumber = dbo.tblassignment.ittnumber
    inner join dbo.tblfltwrkcntr on
    dbo.tblfltwrkcntr.fltwkcntrid = dbo.tblassignment.assfltwrkid
    where (itttitle is not null or ittdescription is not null)
    and dbo.tblfltwrkcntr.wrkcntr = @temp

Share This Page