Error converting data type nvarchar to numeric

Discussion in 'ASP .Net' started by Adam_Cogswell, Feb 29, 2008.

  1. Hello everyone,

    I've been beating my head on this for two days and it's driving me
    crazy. First things first, I'm learning ASP.NET and SQL integration
    as I go here so please be kind. :D I've done other web projects, but
    this is the most complicated one I've attempted so far. This forum
    has been a great resource and has saved me tons of time and energy but
    I'm stuck...

    A little background: I have a SQL 2000 db with a few related tables.
    I am trying to create a asp.net front end to manage them. I'm fine as
    long as the tables I'm updating don't have relatsionships, but the
    items in one table in particular that has several foreign key
    relationships. Ever time I try to insert a record to this table I get
    the "Error Converting Data Type nvarchar to numeric". I think this is
    happening because asp is trying to pass the ID field from one of the
    drop down lists (asp:listbox) through as text and SQL is expecting a
    numeric but I don't know how to fix it. Here's the code for my page
    (again, please be kind :D ):

    <%@ Import Namespace="System.Data" %>
    <%@ Import Namespace="System.Data.SQLClient" %>
    <script language="VB" Runat="server">

    Dim strConn as string =
    ConfigurationManager.ConnectionStrings("EATData").ConnectionString

    Sub Page_Load(Source as Object, E as EventArgs)

    Dim GetListBoxValues as New SQLConnection(strConn)
    GetListBoxValues.Open()

    'Populate the drop down lists
    Dim SolutionListSQL as string = "SELECT * FROM tblSolutions ORDER
    BY Solution"
    Dim CSPListSQL as string = "SELECT * FROM tblCodeLevels ORDER BY
    CodeLevel"
    Dim PackageTypeListSQL as string = "SELECT * FROM tblPackageTypes
    ORDER BY PackageType"

    Dim getSolutionList as New SQLCommand (SolutionListSQL,
    GetListBoxValues)
    Dim getCSPList as New SQLCommand (CSPListSQL, GetListBoxValues)
    Dim getPackageTypeList as New SQLCommand (PackageTypeListSQL,
    GetListBoxValues)

    'Bind the listbox controls to the appropriate data reader
    Dim objDR1 as SQLDataReader
    objDR1 = getSolutionList.ExecuteReader()
    lstSolutions.DataSource = objDR1
    lstSolutions.DataBind()
    objDR1.Close()

    Dim objDR2 as SQLDataReader
    objDR2 = getCSPList.ExecuteReader()
    lstCSP.DataSource = objDR2
    lstCSP.DataBind()
    objDR2.Close()

    Dim objDR3 as SQLDataReader
    objDR3 = getPackageTypeList.ExecuteReader()
    lstPackageTypes.DataSource = objDR3
    lstPackageTypes.DataBind()
    objDR3.Close()

    GetListBoxValues.Close()

    'Populate the list of current values
    If not Page.IsPostBack then
    getentries
    end if

    End Sub

    Sub doInsert(Source as Object, E as EventArgs)

    'Insert a new record
    Dim InsertSQL as string = "Insert INTO tblPackages (PackageNum,
    Description, SolutionID) VALUES (@PackageNum, @Description,
    @lstSolutions)"
    Dim InsertConn as New SQLConnection(strConn)
    Dim Cmd as New SQLCommand (InsertSQL, InsertConn)
    cmd.Parameters.Add(New SQLParameter("@PackageNum",
    txtPackageNum.text))
    cmd.Parameters.Add(New SQLParameter("@Description",
    txtDescription.text))
    cmd.Parameters.Add(New SQLParameter("@lstSolutions",
    lstSolutions.datavaluefield))
    InsertConn.Open()
    Cmd.ExecuteNonQuery()
    InsertConn.Close()

    'Update the list of current entries
    getEntries

    End Sub

    Sub getEntries()
    Dim MySQL as string = "SELECT * FROM tblPackages"
    Dim MyConn as New SQLConnection(strConn)
    Dim objDR as SQLDataReader
    Dim Cmd as New SQLCommand(MySQL, MyConn)
    MyConn.Open()
    objDR=Cmd.ExecuteReader
    MyDataGrid.DataSource = objDR
    MyDataGrid.DataBind()
    MyConn.Close()

    End Sub
    </script>

    <html>
    <head>
    </head>
    <body class="sidelights" onload="preloadImages();">
    <p>
    <form id="frmManageIssues" runat="server">
    <div style="float:left">
    <table width="100%">
    <tr>
    <td width="15%">Package Number:</td>
    <td align="left"><asp:TextBox id="txtPackageNum" runat="server"/></
    td>
    </tr>
    <tr>
    <td width="15%">Description:</td>
    <td><asp:TextBox id="txtDescription" runat="server"/></td>
    </tr>
    <tr>
    <td width="15%">Solution:</td>
    <td><asp:ListBox id="lstSolutions" rows="1"
    datatextfield="Solution" datavaluefield="SolutionID" runat="server"></
    asp:ListBox></td>
    </tr>
    </table>
    <br>
    <br>
    <asp:Button id="InsertIssue" text="Insert" onclick="doInsert"
    runat="server"/>
    <br>
    <br>
    <asp:GridView runat="server"
    id="MyDataGrid"/>
    </asp:GridView>
    </div>
    </form>
    </p>
    </body>
    </html>

    I've been back and forth through the forum looking for a solution and
    I'm sure I'm doing something stupid it but any help would be greatly
    appreciated. Also, if this is the wrong forum, I apologize in
    advance. Thanks!
     
    Adam_Cogswell, Feb 29, 2008
    #1
    1. Advertising

  2. I forgot, here's the actual error stack from the Insert failure:

    Error converting data type nvarchar to numeric.
    Description: An unhandled exception occurred during the execution of
    the current web request. Please review the stack trace for more
    information about the error and where it originated in the code.

    Exception Details: System.Data.SqlClient.SqlException: Error
    converting data type nvarchar to numeric.

    Source Error:


    Line 69: 'cmd.Parameters.Add(New SQLParameter("@Notes",
    txtNotes.text))
    Line 70: InsertConn.Open()
    Line 71: Cmd.ExecuteNonQuery()
    Line 72: InsertConn.Close()
    Line 73:


    Source File: c:\inetpub\wwwroot\eat\admin_manageissues_debug.aspx
    Line: 71

    Stack Trace:


    [SqlException (0x80131904): Error converting data type nvarchar to
    numeric.]
    System.Data.SqlClient.SqlConnection.OnError(SqlException exception,
    Boolean breakConnection) +862234
    System.Data.SqlClient.SqlInternalConnection.OnError(SqlException
    exception, Boolean breakConnection) +739110

    System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject
    stateObj) +188
    System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior,
    SqlCommand cmdHandler, SqlDataReader dataStream,
    BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject
    stateObj) +1956
    System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader
    ds, RunBehavior runBehavior, String resetOptionsString) +149

    System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior
    cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean
    async) +903
    System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior
    cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String
    method, DbAsyncResult result) +132

    System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult
    result, String methodName, Boolean sendToPipe) +415
    System.Data.SqlClient.SqlCommand.ExecuteNonQuery() +135
    ASP.eat_admin_manageissues_debug_aspx.doInsert(Object Source,
    EventArgs E) in c:\inetpub\wwwroot\eat\admin_manageissues_debug.aspx:
    71
    System.Web.UI.WebControls.Button.OnClick(EventArgs e) +105
    System.Web.UI.WebControls.Button.RaisePostBackEvent(String
    eventArgument) +107

    System.Web.UI.WebControls.Button.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String
    eventArgument) +7
    System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler
    sourceControl, String eventArgument) +11
    System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData)
    +33
    System.Web.UI.Page.ProcessRequestMain(Boolean
    includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint)
    +5102
     
    Adam_Cogswell, Feb 29, 2008
    #2
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. luna
    Replies:
    1
    Views:
    13,892
    Kevin Spencer
    Feb 6, 2004
  2. Curious Trigger
    Replies:
    2
    Views:
    1,854
    Curious Trigger
    Sep 9, 2006
  3. darrel
    Replies:
    4
    Views:
    869
    darrel
    Jul 19, 2007
  4. Replies:
    0
    Views:
    1,028
  5. maurox

    Problem with field type nvarchar(max)

    maurox, Jan 17, 2007, in forum: ASP General
    Replies:
    3
    Views:
    211
    maurox
    Jan 17, 2007
Loading...

Share This Page