Problem with function (Converting data type varchar to bigint)

I get the following warning in visual studio 2005 for this function(bottom of page).

"Warning    1    Function 'check_availability' doesn't return a value on all code paths. A null reference exception could occur at run time when the result is used.    C:\Documents and Settings\XXX\My Documents\Visual Studio 2005\WebSites\Loans\Admin\Issue.aspx.vb    492    5    C:\...\Loans\
"

When I run the site in debug mode I get this...

Error converting data type varchar to bigint.

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 varchar to bigint.

Source Error:

Line 411:            myConnection.Open()
Line 412: dbReader = myCommand.ExecuteReader
Line 413: While dbReader.Read()
Line 414: avValue = CBool((dbReader.Item("available_for_loan")))
Line 415: End While

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

Stack Trace:

[SqlException (0x80131904): Error converting data type varchar to bigint.]
System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) +925466
System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) +800118
System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) +186
System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) +1932
System.Data.SqlClient.SqlDataReader.HasMoreRows() +150
System.Data.SqlClient.SqlDataReader.ReadInternal(Boolean setTimeout) +212
System.Data.SqlClient.SqlDataReader.Read() +9
Request.check_availability(String s) in C:\Inetpub\loans\Admin\Issue.aspx.vb:413
Request.txtBarcode0_TextChanged(Object sender, EventArgs e) in C:\Inetpub\loans\Admin\Issue.aspx.vb:429
System.Web.UI.WebControls.TextBox.OnTextChanged(EventArgs e) +1234972
System.Web.UI.WebControls.TextBox.RaisePostDataChangedEvent() +52
System.Web.UI.WebControls.TextBox.System.Web.UI.IPostBackDataHandler.RaisePostDataChangedEvent() +7
System.Web.UI.Page.RaiseChangedEvents() +117
System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +1646
 

 FUNCTION.

Public Function check_availability(ByVal s As String) As String
        Dim rtrnvalue As Integer
        Dim avValue As Boolean

        'checking to see if item is  available
        Dim sql2 As String = "select isnull(available_for_loan,'') as available_for_loan from equipment where barcode = '" & s & "'"
        'Connection String  value
        Dim conn As String = ConfigurationManager.ConnectionStrings("LoansConnectionString").ConnectionString
        Using myConnection As New SqlConnection(conn)
            Dim myCommand As New SqlCommand(sql2, myConnection)
            'declare  datareader
            Dim dbReader As SqlDataReader
            myConnection.Open()
            dbReader = myCommand.ExecuteReader
            While dbReader.Read()
                avValue = CBool((dbReader.Item("available_for_loan")))
            End While
            If CInt(avValue) = 0 Then
                lblbcodeerror.Visible = True
                rtrnvalue = 0
                Response.Write(rtrnvalue)
            End If
            'if connection not closed then close
            If (Not conn Is Nothing) Then
                myConnection.Close()
                myConnection.Dispose()
            End If
        End Using

    End Function

0
ghw123
7/21/2008 2:09:18 AM
asp.net.sql-datasource 29906 articles. 0 followers. Follow

8 Replies
581 Views

Similar Articles

[PageSpeed] 52

in you db, column available_for_loan must contain only 0s and 1s and no NULLs... check if its true...; it seems that the datatype of available_for_loan is varchar, change it to boolean.

 

0
alexey_kay
7/21/2008 2:39:27 AM

 

Thanks for the reply, the data type for available_for_loan is bit there is no boolean datatype as such in sqlserver 2005
0
ghw123
7/21/2008 2:45:41 AM

It still seems that you have a problem with your data. Would you be able to create a new empty table really quick, with no values, connect it to this code, run it to see if it gives the same exception?

0
alexey_kay
7/21/2008 3:02:58 AM

 Created new table with 2 fields available_for_loan and barcode no data in either field. returned same error.

0
ghw123
7/21/2008 8:23:58 PM

Hi,

did you try getting rid of the extra set of brackets avValue = CBool((dbReader.Item("available_for_loan"))) not sure if it will make the difference;

Also, it seems from the code that your While loop doesn't actually produce results for each row (which is what it is suppose to do), but probably either for the first row, or just the last;

try to put this code inside the while loop?

            If CInt(avValue) = 0 Then
                lblbcodeerror.Visible = True
                rtrnvalue = 0
                Response.Write(rtrnvalue)
            End If

0
alexey_kay
7/22/2008 3:29:48 AM

avValue = CBool(dbReader.Item("available_for_loan"))

If there is no data in the "available_for_load" column (i.e, the actual data is NULL in database), you are now tring to convert a NULL reference to a bool instance. Thus you would get the error message. I suggest you add a "nonull" constraint to your "availabe_for_load" column and things would work fine.

Hope my suggestion helps.

 


Please remember to click “Mark as Answer” on the post that helps you, and to click “Unmark as Answer” if a marked post does not actually answer your question. This can be beneficial to other community members reading the thread.
0
Bo
7/22/2008 4:09:09 AM

I don't see anything wrong with the code that handles the available_for_load column.  Select IsNull(available_for_load,'') will return 0 if available_for_load is Null. Although Select IsNull(available_for_load,0) makes more sense.

I would suspect the problem is with the BarCode field.  If it is a BigInt don't delimit the value in the sql statement and make sure the S variable (why is it declared as String) contains a valid integer?

"Select ... Where barcode = " & s

 

 

0
brucevde
7/22/2008 6:12:26 AM

ghw123:
Dim sql2 As String = "select isnull(available_for_loan,'') as available_for_loan from equipment where barcode = '" & s & "'"
 

Well, this is different from what is being discussed here.

The first thing is that you should make you query a parameterized one rather than supplying your values using string concatenation, as you've done for the barcode value.

I'm actually interested in knowing what is the data type of the barcode column and what value you are passing in your variable named "s".

I'm thinking about the query first, as according to your post, you've got the error at the line where you've tried to execute the read method.


Thanks,
Dhimant Trivedi
"When the going gets tough, tough gets going."

"Mark as Answer" the post(s) which helped you solve the problem
0
dhimant
7/22/2008 7:02:31 AM
Reply:

Similar Artilces:

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   ...

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. ...

Error converting data type varchar to bigint.
I have the following query which has been giving me headaches for days :  SELECT SCCode [Service Catalog Code], FileName [File Name], FullName [Full Name], FileExtension [Extension], FileSize [Size],  Author [Author], CONVERT(VARCHAR(50),CAST(LastModified AS SMALLDATETIME), 120) [Last Modified],   CONVERT(VARCHAR(50),CAST(LastAccessed AS SMALLDATETIME), 120) [Last Accessed], TimesAccessed [Downloads],  UploadedBy [Uploaded By], CONVERT(VARCHAR(50),CAST(UploadedAt AS SMALLDATETIME), 120) [Uploaded At]  FROM #TempTable  WHERE id > ...

convert an sql datetime column to .net datetime data type in front end...?
I have a field (ldate) in a table that typically displays data in the following format:2007-11-12 20:30:47.000In the front end I want the time and the date separately. But as I try to cast it to a datetime object it is giving me a System.NullReferenceException. What I tried to do was just this: Protected Sub gvTopics_RowDataBound(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.GridViewRowEventArgs) Handles gvTopics.RowDataBound Dim img As Image = gvTopics.FindControl("imgIcon") Dim lbDate, lbTime As Label lbDate = gvTopics.FindControl(...

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...

Implicit conversion from data type ntext to varchar is not allowed. Use the CONVERT function to run this query.
Hello Guys,Have been getting this error( Implicit conversion from data type ntext to varchar is not allowed. Use the CONVERT function to run this query. ) when running on the live environment but it was fine when run locally. If anyone has similar problem please let me know the fix you have done. Thank you.~ Remember To Mark The Posts Which Helped You As The ANSWER ~  Seems like your database schema differs between live environment and local. Check the definition of the table, and also what type of parameter you use in your code.If this post was useful to you, please mark it as ...

error in SQL Store Proc ''Conversion failed when converting the varchar value to data type int'
Hi , I am having problem and error of 'Conversion failed when converting the varchar value to data type int ' CREATE PROC dbo.testingloop (DECLARE @testid varchar(max))ASBEGINSET NOCOUNT ONDECLARE @SQL varchar(600)SET @testvalue = CURSOR FORSELECT [test ID])  FROM Data42 where [test ID] = 36OPEN @testvalue      FETCH NEXT     FROM @testvalue INTO @testid      WHILE @@FETCH_STATUS = 0       BEGIN      ' My update statement here  ...

Data type problem in External functions to call win32 api functions
I want to call the win32 api function GetCurrentDirectory from powerbuilder 5.0 I declared this global external function : FUNCTION ulong GetCurrentDirectory(ulong num, REF string buffer) LIBRARY "KERNEL32.DLL" ....but the aplication crashes when I call it from a script Can someone please tell me if this is a datatype problem with the arguments. I tried other API functions successfully but this one gives me problems. The API declaration in C++ notation for this function is: DWORD GetCurrentDirectory( DWORD nBufferLength, // size of directory buffer LPTSTR ...

Delphi function parameter data type and asa char, varchar & long varchar
I'm currently writing my own smtp email function using Delphi 7. This function which is located in an external dll will be called by Sybase. I have an issue between Sybase and the Delphi function parameter type e.g. when Sybase send a field data type varchar or Long varchar, what Delphi data type should be used ? If someone has some experience and/or sample code... Thanks in advance. See and tanslate to Delphi pascal "extfnapi.h". To provide values for INOUT or OUT parameters, use the set_value API function. To read IN and INOUT parameters, use the get_...

Connectivity problem with IIS 6 mode ASPX and SQL Anywhere .NET Data Provider iAnywhere.Data.SQLAnywhere SAConnection
This is a multi-part message in MIME format. ------=_NextPart_000_0013_01CABC4F.394AC480 Content-Type: text/plain; charset="iso-8859-7" Content-Transfer-Encoding: quoted-printable Connectivity problem with IIS 6 mode (Windows 2003) and Sybase = SQLAnywhere Web Edition with SQL Anywhere .NET Data Provider = (iAnywhere.Data.SQLAnywhere iAnywhere.Data.SQLAnywhere.SAConnection() ) using aspx. When I try to connect to Sybase SQLAnywhere Web Edition database from = aspx using SQL Anywhere .NET Data Provider (iAnywhere.Data.SQLAnywhere iAnywhere.Data.SQLAnywhere.SAConn...

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...

I cnahged a coloumn from int to varchar and now Subsonic says: Conversion failed when converting the varchar value 'anytext' to data type int.
I had created a table with column "ColoumnName" as int. I run SubCommander\sonic.exe to generate all classes (in Generated dir) and worked/coded for a while (weeks) without any probs. Then I had to change that coloumn from int to varchar. I run sonic.exe again but this instruction:        ds = DataService.GetDataSet(q); (where ds is a DataSet and q is a QueryCommand) gives the error: Conversion failed when converting the varchar value 'anytext' to data type int. Please note that the above instruction didn't change recently and worked...

Converting a gridView using an Sql Datasource to a gridview who is not using an SQL DataSource
How can i rewrite this page so that i doesnt make use of an SqlDataSource?  <%@ Control Language="C#" AutoEventWireup="true" CodeFile="LoginHistory.ascx.cs" Inherits="Private_UserControls_WebUserControl" %> <html> <head> <%--<link rel="stylesheet" href="BaldwinPortal.css" mce_href="BaldwinPortal.css" type="text/css" />--%> <title>Login History</title> </head> <body style="margin:0 0 0 0;padding:0 0 0 0"> ...

Converting Gridview Checkbox data type into new Label data type
I have a backend page where the client can check boxes of available time slots.  The frontend takes that boolean(bit) value, to change the label property, if False, returns lbl.Visible=false and changes the label text to a hard coded time value (i.e. 5am); using an if statement.  Here is my example...Thank you so much for any input, this site and members are the best. Code Behind:protected void GridView3_RowDataBound(object sender, GridViewRowEventArgs e)    {        if (e.Row.RowType == DataControlRowType.DataRow)  &...

Web resources about - Problem with function (Converting data type varchar to bigint) - asp.net.sql-datasource

Facebook Begins Converting Users To HTTPS
Are you willing to sacrifice a little bit of speed for a lot more safety? Facebook is asking that very question with its announcement that it ...

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 ...

Vert - simply converting for iPhone, iPad, and iPod touch on the iTunes App Store
Get Vert - simply converting on the App Store. See screenshots and ratings, and read customer reviews.

Converting SIM Card to Micro SIM Card - Flickr - Photo Sharing!
Place new Micro SIM into the iPhone SIM card tray

Ayaan Hirsi Ali on Converting Muslims to Christianity - YouTube
Complete video at: http://fora.tv/2010/07/29/Nomad_From_Islam_to_America_with_Ayaan_Hirsi_Ali Ayaan Hirsi Ali explains her support of missionary ...

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 ...

Converting Churches Into Homes Is The Latest Hollywood Trend
You don't have to be a believer to be moved by the beauty of a church.

Resources last updated: 12/13/2015 5:09:00 PM