Error converting data type varchar to numeric.

following is the error I am getting.

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

Source Error:

Line 107:                " select  '" & eventnumber & "','" & username & "','" & requestor & "',  '" & bcode & "',  max([Loan_ID]) from loan   "
Line 108: Dim myCommand1 As New SqlCommand(sql1, myConnection)
Line 109: myCommand1.ExecuteNonQuery()
Line 110:
Line 111: Dim sql2 As String = " update Equipment set [Available_for_Loan] = 0 where barcode = '" & bcode & "'"

Source File: C:\Inetpub\loans\Admin\Issue.aspx.vb    Line: 109

Stack Trace:

[SqlException (0x80131904): Error converting data type varchar to numeric.]
System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) +857242
System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) +734854
System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) +188
System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) +1838
System.Data.SqlClient.SqlCommand.RunExecuteNonQueryTds(String methodName, Boolean async) +192
System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe) +380
System.Data.SqlClient.SqlCommand.ExecuteNonQuery() +135
Request.Button1_Click(Object sender, EventArgs e) in C:\Inetpub\loans\Admin\Issue.aspx.vb:109
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

 

this is code ...

Protected Sub Button1_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button1.Click



        lblBcodeMsg.Visible = False
        'declare array
        Dim Irequests(8) As String
        Dim barcode(8) As String
        'assign values to array indexes
        Irequests(0) = txtreq0.Text
        Irequests(1) = txtreq1.Text
        Irequests(2) = txtreq2.Text
        Irequests(3) = txtreq3.Text
        Irequests(4) = txtreq4.Text
        Irequests(5) = txtreq5.Text
        Irequests(6) = txtreq6.Text
        Irequests(7) = txtreq7.Text

        barcode(0) = txtBarcode0.Text
        barcode(1) = txtBarcode1.Text
        barcode(2) = txtBarcode2.Text
        barcode(3) = txtBarcode3.Text
        barcode(4) = txtBarcode4.Text
        barcode(5) = txtBarcode5.Text
        barcode(6) = txtBarcode6.Text
        barcode(7) = txtBarcode7.Text

        'declare vars

        Dim b As Integer = 0
        Dim nhi As String = txtNHI.Text
        Dim finishdate As String = txtFinishDate.Text
        Dim accnumber As String = txtAccnumber.Text
        Dim eventnumber As String = txtEventnumber.Text
        Dim startdate As String = txtIssueDate.Text
        Dim requestor As String = lblRequestor.Text
        Dim requestdate As String = lblRequestDate.Text
        Dim requireddate As String = lblRequiredDate.Text
        Dim issuer As String = txtusername.Text
        Dim reqid As String = DropDownList2.SelectedValue
        Dim i As Integer = 0
        Dim bcode As String



        'Connection String  value
        Dim conn As String = ConfigurationManager.ConnectionStrings("LoansConnectionString").ConnectionString

        'Create a SqlConnection instance
        Using myConnection As New SqlConnection(conn)
            'open connection
            myConnection.Open()
            ' test to see that both arrays have values on matching indices         
            For i = 0 To 7
                'If (Irequests(i).ToString.Equals(String.Empty)) Or (barcode(i).ToString.Equals(String.Empty)) Then
                '    lblBcodeMsg.Visible = True
                '    Exit For
                '    Exit Sub
                'End If
                bcode = barcode(i)
                'bcode.Trim()
                lblbcode2.Text = "this is bcode value: --'" & bcode & "'--"
                'Specify the SQL query Insert Into Loan
                Dim sql As String = "set DATEFORMAT dmy; insert into loan ( [NHI],[Start_Date], [Finish_Date],[ACC_Number], [Event_Number], [Req_ID] ) " & _
                "values ( @nhi,@startdate,@finishdate,@accnumber,@eventnumber,@reqid)"
                Dim myCommand As New SqlCommand(sql, myConnection)
                Dim pnhi As New SqlParameter("@nhi", nhi)
                Dim pstartdate As New SqlParameter("@startdate", startdate)
                Dim pfinishdate As New SqlParameter("@finishdate ", finishdate)
                Dim peventnumber As New SqlParameter("@eventnumber", CStr(eventnumber))
                Dim paccnumber As New SqlParameter("@accnumber", CStr(accnumber))
                Dim preqid As New SqlParameter("@reqid", reqid)

                myCommand.Parameters.Add(pnhi)
                myCommand.Parameters.Add(pstartdate)
                myCommand.Parameters.Add(pfinishdate)
                myCommand.Parameters.Add(peventnumber)
                myCommand.Parameters.Add(paccnumber)
                myCommand.Parameters.Add(preqid)

                myCommand.ExecuteNonQuery()
                Dim username As String = CStr(Session("username"))

                Dim sql1 As String = "insert into Loan_Equipment ([Event_Number], [Issuer_Username],[Requestor],[Barcode], [Loan_ID])  " & _
                " select  '" & eventnumber & "','" & username & "','" & requestor & "',  '" & bcode & "',  max([Loan_ID]) from loan   "
                Dim myCommand1 As New SqlCommand(sql1, myConnection)
                myCommand1.ExecuteNonQuery()

                Dim sql2 As String = " update Equipment set [Available_for_Loan] = 0 where barcode = '" & bcode & "'"
                Dim myCommand2 As New SqlCommand(sql2, myConnection)
                myCommand2.ExecuteNonQuery()

                Dim sql4 As String = " update requests set [issued] = 1 where req_id = '" & reqid & "'"
                Dim myCommand4 As New SqlCommand(sql4, myConnection)
                myCommand4.ExecuteNonQuery()
                'i = i + 1
            Next

1
ghw123
1/14/2007 1:00:07 AM
asp.net.web-forms 93655 articles. 4 followers. Follow

11 Replies
3648 Views

Similar Articles

[PageSpeed] 2

 its SQLException, so looks more likely error occurs in sql statement.

my suggestion is find out whats in the sql1

and run that sql1 in the sql server management studio to find out what field is in wrong data type

 

Or

use try catch satement,

in sqlexception catch more information

 


James Wu (MIB426)
.NET is only way to go
MCP, MCSE, MCDBA, MCSD, MCAD
0
MIB426
1/14/2007 1:15:16 AM

Hi there,

I would politely suggest that you re-work your solution. the later SQL Commands have are using constructed text commands dissuimilar to the first SQL command that is using commands parameters.

It would appear that there are several dependencies between each of the queries, and as such they should all be wrapped in a Transaction.

You should really be completing this in a Stored Procedure and you should declare your command parameters by type.

First step is to identify all the parameters as required by any of the individual Commands. Distinguishing between those that are provided as input and those that are inherited from the new records, or other existing records in the db. You pass only the required parameters to the SPROC (Stored Procedure) and let it all happen on the database.

As for your error you are wrapping all params - except one - in SQL1 with quotes and it would seem that at least one is a number column.

I would be very concerned if max(Loan_ID) is supposed to be providing a relationship between the two tables.

If you would like help to work it up, it's available. 

Rgds,

Martin.


Rgds,
Martin.

For the benefit of all users please mark any post answers as appropriate.
-1
mokeefe
1/14/2007 5:41:46 AM
Thanks for the replies, all help greatfully recieved.
0
ghw123
1/14/2007 7:26:49 PM

MIB42

this is sql statement without variables just values

insert into Loan_Equipment ([Event_Number], [Issuer_Username],[Requestor],[Barcode], [Loan_ID])
                 select  999,'robemorg','philm',  1111,  max([Loan_ID]) from loan 

this inserts into table no problems.

 

0
ghw123
1/14/2007 7:31:14 PM
mokeefe:

you are wrapping all params - except one - in SQL1 with quotes and it would seem that at least one is a number column.

Now witha working statement we can see that eventnumber and Barcode are numberic values and as per your working statement should not be wrapped with single quotes and treated as VarChar.

Eg (quotes removed on first and fourth params).

Dim

sql1 As String = "insert into Loan_Equipment ([Event_Number], [Issuer_Username],[Requestor],[Barcode], [Loan_ID]) " & _

" select " & eventnumber & ",'" & username & "','" & requestor & "', " & bcode & ", max([Loan_ID]) from loan "

Rgds,

Martin.


Rgds,
Martin.

For the benefit of all users please mark any post answers as appropriate.
0
mokeefe
1/14/2007 10:21:43 PM

 

Mokeefe, I changed that but still no luck

</p><p>Dim sql1 As String = "insert into Loan_Equipment ([Event_Number], [Issuer_Username],[Requestor],[Barcode], [Loan_ID])&nbsp; " &amp; _
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; " select&nbsp; '" &amp; eventnumber &amp; "','" &amp; username &amp; "','" &amp; requestor &amp; "',&nbsp; " &amp; bcode &amp; ",&nbsp; max([Loan_ID]) from loan&nbsp;&nbsp; "
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Dim myCommand1 As New SqlCommand(sql1, myConnection)
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; myCommand1.ExecuteNonQuery()</p><p>
 

I have declared bcode as integer.

Ihave option strict on so when I assign value to bcode it requires that I do it so

bcode = CInt(barcode(i))

when I run in debug mode I can see that bcode holds the value given it

        bcode    2222    Integer

and that the first index of the array barcode(i) has the value

-        barcode    {Length=9}    String()

        (0)    "2222"    String



the vaues within the array are datatype string but when I use bcode = CInt(barcode(i))

then this should convert them to integer and from the feedback in debug that is what is happening

so why am I getting this error message? 

 

0
ghw123
1/14/2007 11:00:25 PM

sorry bout that used wrong tags

 code should be as follows

Dim sql1 As String = "insert into Loan_Equipment ([Event_Number], [Issuer_Username],[Requestor],[Barcode], [Loan_ID])  " & _
                " select  '" & eventnumber & "','" & username & "','" & requestor & "',  " & bcode & ",  max([Loan_ID]) from loan   "
                Dim myCommand1 As New SqlCommand(sql1, myConnection)
                myCommand1.ExecuteNonQuery()

0
ghw123
1/14/2007 11:02:23 PM

eventnumber still has single quotes.

If you have altered your code so that bcode is now an Integer variable - while bcode is being passed as a text in a SQL Text Command it should really be passed as a string - bcode.ToString().

 


Rgds,
Martin.

For the benefit of all users please mark any post answers as appropriate.
0
mokeefe
1/14/2007 11:25:45 PM
have altered eventnumber so no '' but still same problem.
0
ghw123
1/14/2007 11:57:10 PM

This is no longer amusing for any of us!

As per your earlier trace output, please alter your code as below, and post the debug output.

Dim sql1 As String = "insert into Loan_Equipment ([Event_Number], [Issuer_Username],[Requestor],[Barcode], [Loan_ID])  " & _

" select  '" & eventnumber & "','" & username & "','" & requestor & "',  '" & bcode & "',  max([Loan_ID]) from loan   "

Dim myCommand1 As New SqlCommand(sql1, myConnection)

 

' ADD this line

System.Diagnostics.Debug.WriteLine(sql1.ToString())

 

' Place Breakpoint here - copuy and paste the output from the debug as your earlier examples

myCommand1.ExecuteNonQuery()

 

 


Rgds,
Martin.

For the benefit of all users please mark any post answers as appropriate.
0
mokeefe
1/15/2007 1:11:55 AM

problem solved. When there was no value in the array then the conversion error appeared

as it was trying to convert "" to integer. putting the if statement at the beginning of the for loop solved the problem.

 Thanks for all the help, much appreciated.
 

 

For i = 0 To 7
                If (Irequests(i) = "") Then
                    Exit For
                End If
                bcode = CInt(barcode(i))

0
ghw123
1/15/2007 6:58:28 PM
Reply:

Similar Artilces:

Error converting data type varchar to numeric
hi All, This case statement is giving me the error "Error converting data type varchar to numeric". the column dbo.tblKMTermloanFees.base_min datatype is varchar.  And please see below for sample data in the column. i have to convert this into number. Any suggestions guys how to do this, Thanks in advance. Shilpa.  CASE  WHEN dbo.tblKMTermloanFees.base_min LIKE '%/6M%' THEN CONVERT (DECIMAL (5,2), LEFT (Base_Min, CHARINDEX ('/', Base_Min)-1)) / 6 WHEN dbo.tblKMTermloanFees.base_min LIKE '%/M%' THEN CONVERT (DECIMAL (5,2), LEFT (Base_Mi...

Arithmetic overflow error converting varchar to data type numeric
Hello, i am getting "Arithmetic overflow error converting varchar to data type numeric"  while i am trying to cast the numeric value to varchar in SQL Server 2000(This is the Query  (cast(' + @Qty + ' as varchar(50)) If any knows plz help me, thanks.        "Arithmetic overflow error converting varchar to data type numeric" stands for error while converting varchar to numeric, and you say "trying to cast the numeric value to varchar", this is kind a confusing thing. Can you explain your problem more detailed?Nev...

SQL Server Error: "Arithmetic overflow error converting numeric to data type numeric"
I'm still quite new to SQL, and so I may be missing something quite obvious. Apologies in advance if that's the case. I've included my query at the bottom of the message as it is quite long; my problem is that I get "Arithmetic overflow error converting numeric to data type numeric" when the first parameter to my COALESCE calls is not NULL. That is, the COALESCEs and the query work as I expect when SUM(cust_vw_actual.Actual) is NULL (they return 0.00), but if it isn't I get the error. I am also getting "Warning: Null value eliminated from aggregate.&...

PB 11.2/MS SQL 2005 Error converting data type varchar to numeric
Am getting the above error from PB 11.2 when executing a stored proc. The proc take no parameters. Once i get this error, i cannot execute the proc succesfully from PB 11.2 until i recompile the stored proc. At the same time the proc works fine from Query Analyzer, as well as from PB 7. What is PB 11.2 doing to mess this up? Any ideas....it's wrecking havoc on our production app. Forgot to add the error is C0195:SQLSTATE =3D 22018 On Dec 13, 4:00=A0pm, bre...@friesens.com wrote: > Am getting the above error from PB 11.2 when executing a stored proc. > The proc take no...

How to convert varchar data type into datetime data type without data lose
Hi all, i have stored date as varchar(30) data type and Date format is dd/MM/yyyy now i have to convert it into datatime data type can u help me in query ?  i have 330 records and each recod contain the Date in format of dd/MM/YYYY but due to varchar data type i can not perform calculation on Date so i have to convert into datetime without data lose. please help me in this regard. Best RegardsRameezWaheed  life is name of learning!Mark as an answer if it helps You can check these posts http://forums.microsoft.com/TechNet/ShowPost.aspx?PostID=2039108&SiteID=17   ...

Help needed: arithmetic overflow error converting numeric to data type numeric
Hi, I have a SQL server 2005 db. The table structure (from big list of columns, only decimal columns I mentioned) below.  CREATE TABLE [Product].[MetricMeasures](    [MetricMeasureID] [int] IDENTITY(1,1) NOT NULL,    [ProductID] [int] NOT NULL,    [ProductSizeValue] [decimal](4, 2) NULL,    [ItemWeightValue] [decimal](3, 3) NULL,    [MasterCartonWeightValue] [decimal](3, 3) NULL,    [MasterCartonVolumeValue] [decimal](4, 4) NULL, CONSTRAINT [PK_MetricMeasures] PRIMARY KEY CLUSTERED ( &nbs...

Error 8114 (rc -1) : Error converting data type varchar to n
Hi PBers, I'm using PB 6.5.1 build 1278 and MS SQL Server 7. I have 2 views: CREATE view V772EVEN as ( SELECT SQ_EVT, DE_TIT_EVT, dt_ini_evt, dt_fim_evt, cd_spr_rsp, convert(decimal(7),right(mt_rsp_evt,6)) as 'mt_rsp_evt' FROM drhu772..t772even WHERE sq_evt > 199800000 ) create view dbo.V772PEVT as ( SELECT sq_evt, convert(decimal(7),right(sq_aln,6)) as 'mt_epg' FROM drhu772..T772PEVT WHERE tp_aln = 0 and upper(left(...

System.Data.SqlClient.SqlException: Error converting data type numeric to decimal.
Hi There, I'm using C# to get a value for a DOUBLE precision variable, called "Length", from a textBox using the following line: Length = Convert.ToDouble( txtLength.Text ) I'm also using the following lines to prepare my stored procedure call: arParms[9] = new SqlParameter("@Length", SqlDbType.Decimal, 5); arParms[9].Value = record.Length; My stored procedure has the following parameter definition: @Length decimal(9,3) My problem is that if someone types a value bigger than 999999 in the textbox he will get for sure the following error: System....

Formview gives Error converting data type nvarchar to numeric error when comma is in the input
I have a formview that collects various data including money and decimal data. I had a problem where the $ was casuing the "Error converting data type nvarchar to numeric" error and think I corrected it by changing the format to {0:C} for the field in the data bindings. I am still having an issue with commas in the data in a decimal field. The field in the database is decimal (14,9) and the format in the textbox in the formview is {0:n}. The data being input is something like 123,456.0. This gives the Error converting data type nvarchar to numeric error. If you remove the comma, 1...

System.Data.SqlClient.SqlException: Syntax error converting the varchar value 'V' to a column of data type int
 I am using  a stored procedure which returns a value of charecter datatype 'V' to the calling program.I am getting an sql exception System.Data.SqlClient.SqlException: Syntax error converting the varchar value 'V' to a column of data type inti didnot define any int datatype in my tablethis is my codeSqlCommand com = new SqlCommand("StoredProcedure4", connection);com.CommandType = CommandType.StoredProcedure;  SqlParameter p1 = com.Parameters.Add("@uname", SqlDbType.NVarChar);SqlParameter p2 = com.Parameters.Add("@op...

Update a Tablefield with Concatenating 4 fields
Hi,Within the vb-script from an ASP.NET webpage i'm trying to update an sqltable. I want to update a particular tablefield. Using the String as below:The field docpad (important to now) is a VARCHAR field with length 250. After executing the code i become an error: Syntax error converting the varchar value '~/Uploadmap/' to a column of data type int.I do not understaend this error because the targetfield is type VARCHAR and not int.   This is the code i'm using. What is it what i'm doing wrong?  sql2.Append("UPDATE dbo.T_20001_Documenten SET docpad = &...

Error converting data type varchar to datetime
We recently installed PB 7.0. We are using MS SQL Server 7.0 on the backend. When we try to create a datawindow whose source is a stored procedure with an argument of datetime we get the following error: Cannot create Datawindow Error converting data type varchar to datetime. 1 execute dbo.my_proc; 1 @ai_arg = '0', @adt_date = '0' Has anyone seen this before? Any solutions? Thanks, Chris Chris, We use to get this problem on MS SQL 6.5, the problem is that the retrieve arguments are not binding to store procedure parameters, thus you get ...

Error converting data type varchar to int.
can anyone see as to why I would get this error with the following SP?  ALTER PROCEDURE [dbo].[SP] @ID int = 0, @emailFrom VARCHAR(50) = Null, @emailDate VARCHAR(50) = Null, @emailSubj VARCHAR(50) = Null, @emailTxtBody VARCHAR(1000) = Null, @emailHtmlBody VARCHAR(1000) = Null AS -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON; DECLARE @Notes VARCHAR (8000) DECLARE @TicketID INT DECLARE @emailBody VARCHAR (1000) DECLARE @Length Int SET @Notes = '' SET @Length = LEN(@emailSubj) ...

Error converting data type varchar to bigint
Hello, I tried to search on forum for what i listed about but kept timing out. So thought i would just post it up here. SERVER: * Windows 2003 Server Standard SP2 (running on VM) * AMD Opteron 2.2Ghz with 2GB RAM * ZPM v6.3.2.700 * SQL Server 2005 (express) v9.001399.06 * ~1250 devices registered on server (All Windows PC/Servers) Getting this error when trying to query on the web console: Error converting data type varchar to bigint Selection: Devices Group = All Status = Enabled If i was to select Devices Group = Only Windows Status = Enabled It works. ...

Web resources about - Error converting data type varchar to numeric. - asp.net.web-forms

PastBook’s Filepicker.io Integration Eases Process Of Converting Facebook Content To Books
PastBook , one of several companies that allow Facebook users to publish their content on the social network in actual books , announced the ...

Facebook No Longer Converting Groups Into Pages
Back when Facebook first launched Facebook Pages, many businesses and brands who had built up substantial audiences in their Facebook Groups ...

Zwartz Laminating-Converting B.V. on the App Store on iTunes
Get Zwartz Laminating-Converting B.V. on the App Store. See screenshots and ratings, and read customer reviews.


"Occupier" Thanked Former Soviet Citizen for "Converting" Him to Capitalism, Pro-Israel, Pro-USA - YouTube ...
May Day Demonstration on Union Square in New York City Zionism & Birth of Modern Israel in 1948: Former Soviet Citizen Pays Tribute to Ben-Gurion ...

Click go fears of converting print files
Is there a way to convert a print queue item to a .RTF or .PDF file? I like to save or email them. - The Sydney Morning Herald

Sudanese woman ordered to hang under sharia law for converting to Christianity gives birth
Khartoum, Sudan: A Christian Sudanese woman sentenced to hang for apostasy has given birth in jail, a Western diplomat said on Tuesday.

Imams warn against radicalism to Aboriginal inmates converting to Islam
The prison system has enlisted the help of ASIO to crack down on radicalisation behind bars amid revelations that Aboriginals are converting ...

Converting the world's companies one by one - The Science Show - ABC Radio National (Australian Broadcasting ...
Image: Trucks carrying logs make their way up a road in Jambi, Indonesia. A vast area of the Sumatran forest, and orangutan habitat, is being ...

Rothesay building new arena, converting existing rink to fieldhouse
The Town of Rothesay plans to build a new arena and convert the existing one into a fieldhouse.

Resources last updated: 1/13/2016 7:29:06 AM