Solved

'Char' values cannot be converted to 'Integer'.

Posted on 2003-11-11
37
2,228 Views
Last Modified: 2008-02-01
Hello Experts!

I'm trying to pass a number like: 65283 to an asp.net page.  The page takes the number and gets info from a sql database.  I'm using this inside a datagrid with a simply hyperlink:<asp:HyperLinkColumn DataTextField="Ref.No" HeaderText="Deal #" datanavigateurlfield="Ref.No" datanavigateurlformatstring="dealno.aspx?Ref.No={0}" navigateurl="dealno.aspx" target="_blank"/>

Ref.No is the same as DealNo in the other table.

The sql table has the field DealNo(varchar)
The info I'm passing is an integer.  So everytime I open this page it gives me the following error:

Compiler Error Message: BC32006: 'Char' values cannot be converted to 'Integer'. Use 'Microsoft.VisualBasic.AscW' to interpret a character as a Unicode value or 'Microsoft.VisualBasic.Val' to interpret it as a digit.

Source Error:

 

Line 15:     If Not IsPostBack Then
Line 16:         DealNoSent = Request.QueryString("Ref.No")
Line 17:         GetDealNo(DealNoSent)
Line 18:         Row = DealInfo.Tables("aafifimast").Rows(0)
Line 19:         CusNameText.Text = Row.Item("B.LST.NME")
 
Line 17.

How do I fix this?

here's my code:

<script runat="server">

    Dim ConnectString, SelectStatement As String
    Dim Connect As SqlConnection = New SqlConnection
    Dim Adapter As SqlDataAdapter = New SqlDataAdapter
    Dim DealInfo As DataSet = New DataSet
    Dim Row As DataRow
    Dim DealNoSent As char

    Sub Page_Load(Sender As Object, E As EventArgs)
    Me.Button1.Attributes.Add("OnClick", "window.close()")
    If Not IsPostBack Then
        DealNoSent = Request.QueryString("Ref.No")
        GetDealNo(DealNoSent)
        Row = DealInfo.Tables("aafifimast").Rows(0)
        CusNameText.Text = Row.Item("B.LST.NME")
        DateText.Text = Row.Item("Deal.Date")
        DMNAMEText.Text = Row.Item("DM.NAME")
        StatText.Text = Row.Item("Stat")
        GrosText.Text = Row.Item("Gros.Pybl")
        HoldText.Text = Row.Item("Holdback")
        BackText.Text = Row.Item("TFI.RES")
        DealText.Text = Row.Item("DealNo")
        SalesM1Text.Text = Row.Item("s1.Name")
        SalesM2Text.Text = Row.Item("s2.Name")
        MdlText.Text = Row.Item("Mdl.No")
        StkText.Text = Row.Item("Stk.No")
        NUOText.Text = Row.Item("NUO")
        DescText.Text = Row.Item("Desc.DV")
        CarlineText.Text = Row.Item("Carline")
        YRText.Text = Row.Item("YR")
        FMIText.Text = Row.Item("C.FMI")
        CityText.Text = Row.Item("B.City")
        StText.Text = Row.Item("B.ST")
        ZipText.Text = Row.Item("B.ZIP")
        PhoneText.Text = Row.Item("B.H.Phone")
        CashText.Text = Row.Item("Cash.Down")
        TRModelText.Text = Row.Item("TR1.Model")
        TradeText.Text = Row.Item("Acv.Trade1")
    End If
    End Sub


    Sub GetDealNo(DealNoSent As integer)
    SelectStatement = "Select  * From aafifimast Where DealNo=" & _
        DealNoSent
    ConnectString = "server=myserver;uid=uid;PWD=pwd;database=default;"
    Connect.ConnectionString = ConnectString
    Adapter.SelectCommand = new SqlCommand(SelectStatement, Connect)
    Adapter.Fill(DealInfo, "aafifimast")
    End Sub

</script>

Help!
0
Comment
Question by:jay-are
  • 16
  • 12
  • 9
37 Comments
 
LVL 58

Expert Comment

by:amit_g
ID: 9725195
Change

Dim DealNoSent As char

to

Dim DealNoSent As String

and change

GetDealNo(DealNoSent)

to

GetDealNo(Convert.ToInt32(DealNoSent))
0
 
LVL 12

Expert Comment

by:kingsfan76
ID: 9725197
hi jay:

try this:

DealNoSent = CInt(Request.QueryString("Ref.No"))

and see if it still give you the error
0
 
LVL 12

Expert Comment

by:kingsfan76
ID: 9725215
or

Sub GetDealNo(DealNoSent As char)

or what amit_g suggest.  whichever works   :-)
0
 

Author Comment

by:jay-are
ID: 9725243
amit

It gives me this error now:

System.Data.SqlClient.SqlException: Syntax error converting the varchar value ' ' to a column of data type int.

Source Error:


Line 51:     Connect.ConnectionString = ConnectString
Line 52:     Adapter.SelectCommand = new SqlCommand(SelectStatement, Connect)
Line 53:     Adapter.Fill(DealInfo, "aafifimast")
Line 54:     End Sub
Line 55:
 
Gonna try yours now Kingsfan
0
 

Author Comment

by:jay-are
ID: 9725259
Kings, your solution gives me the same error.
0
 
LVL 58

Expert Comment

by:amit_g
ID: 9725279
That is because the empty string cannot be converted to integer. You can try simething like this to take care of it ...

Add another variable

Dim DealNoSent_Int As Int32

...

DealNoSent = Request.QueryString("Ref.No")

if (IsNumeric(DealNoSent)) then
    DealNoSent_Int = Convert.ToInt32(DealNoSent)
else
    DealNoSent_Int = 0
end if

GetDealNo(DealNoSent_Int)
0
 

Author Comment

by:jay-are
ID: 9725330
Do I need to modify this at all?  I'm still getting the same error.  

Sub GetDealNo(DealNoSent As integer)
        SelectStatement = "Select  * From aafifimast Where DealNo=" & _
        DealNoSent
    ConnectString = "server=myserver;uid=uid;PWD=pwd;database=default;"
    Connect.ConnectionString = ConnectString
    Adapter.SelectCommand = new SqlCommand(SelectStatement, Connect)
    Adapter.Fill(DealInfo, "aafifimast")
    End Sub

Syntax error converting the varchar value ' ' to a column of data type int.
0
 
LVL 58

Expert Comment

by:amit_g
ID: 9725348
Not inside the sub but outside. Basically before calling the sub GetDealNo, you want to make sure that the GetDealNo contains a valid integer.
0
 

Author Comment

by:jay-are
ID: 9725392
Ok I think I did that right.  Is this correct:

 Dim ConnectString, SelectStatement As String
    Dim Connect As SqlConnection = New SqlConnection
    Dim Adapter As SqlDataAdapter = New SqlDataAdapter
    Dim DealInfo As DataSet = New DataSet
    Dim Row As DataRow
    Dim DealNoSent As string
    Dim DealNoSent_Int As Int32

    Sub Page_Load(Sender As Object, E As EventArgs)
    Me.Button1.Attributes.Add("OnClick", "window.close()")
    If Not IsPostBack Then
        DealNoSent = (Request.QueryString("Ref.No"))

        if (IsNumeric(DealNoSent)) then
        DealNoSent_Int = Convert.ToInt32(DealNoSent)
        else
        DealNoSent_Int = 0
        end if
        GetDealNo(DealNoSent)
        Row = DealInfo.Tables("aafifimast").Rows(0)
        CusNameText.Text = Row.Item("B.LST.NME")
        DateText.Text = Row.Item("Deal.Date")
        DMNAMEText.Text = Row.Item("DM.NAME")
        StatText.Text = Row.Item("Stat")
        GrosText.Text = Row.Item("Gros.Pybl")
        HoldText.Text = Row.Item("Holdback")
        BackText.Text = Row.Item("TFI.RES")
        DealText.Text = Row.Item("DealNo")
        SalesM1Text.Text = Row.Item("s1.Name")
        SalesM2Text.Text = Row.Item("s2.Name")
        MdlText.Text = Row.Item("Mdl.No")
        StkText.Text = Row.Item("Stk.No")
        NUOText.Text = Row.Item("NUO")
        DescText.Text = Row.Item("Desc.DV")
        CarlineText.Text = Row.Item("Carline")
        YRText.Text = Row.Item("YR")
        FMIText.Text = Row.Item("C.FMI")
        CityText.Text = Row.Item("B.City")
        StText.Text = Row.Item("B.ST")
        ZipText.Text = Row.Item("B.ZIP")
        PhoneText.Text = Row.Item("B.H.Phone")
        CashText.Text = Row.Item("Cash.Down")
        TRModelText.Text = Row.Item("TR1.Model")
        TradeText.Text = Row.Item("Acv.Trade1")
    End If
    End Sub


    Sub GetDealNo(DealNoSent As integer)
        SelectStatement = "Select  * From aafifimast Where DealNo=" & _
        DealNoSent
    ConnectString = "server=myserver;uid=uid;PWD=pwd;database=default;"
    Connect.ConnectionString = ConnectString
    Adapter.SelectCommand = new SqlCommand(SelectStatement, Connect)
    Adapter.Fill(DealInfo, "aafifimast")
    End Sub
0
 
LVL 58

Expert Comment

by:amit_g
ID: 9725403
Yes looks like. If you get any error, post the error and the line you get that.
0
 

Author Comment

by:jay-are
ID: 9725416
Here is the erro:

Exception Details: System.Data.SqlClient.SqlException: Syntax error converting the varchar value ' ' to a column of data type int.

Source Error:


Line 58:     Connect.ConnectionString = ConnectString
Line 59:     Adapter.SelectCommand = new SqlCommand(SelectStatement, Connect)
Line 60:     Adapter.Fill(DealInfo, "aafifimast")
Line 61:     End Sub
Line 62:
 

Source File: c:\inetpub\wwwroot\aspx\new\dealno.aspx    Line: 60

0
 
LVL 12

Expert Comment

by:kingsfan76
ID: 9726087
i see something wrong on your code above, though i don't know if that's what causes the error:

DealNoSent = (Request.QueryString("Ref.No"))

       if (IsNumeric(DealNoSent)) then
       DealNoSent_Int = Convert.ToInt32(DealNoSent)
       else
       DealNoSent_Int = 0
       end if
       GetDealNo(DealNoSent)


it seems you pass the wrong variable to GetDealNo, should be:

GetDealNo(DealNoSent_Int)
0
 

Author Comment

by:jay-are
ID: 9726114
Oh yeah, I didn't notice that.  I changed it.  Still get the same error  :)
0
 
LVL 58

Expert Comment

by:amit_g
ID: 9726211
Before

Adapter.SelectCommand = new SqlCommand(SelectStatement, Connect)

add

Response.Write SelectStatement
Response.End

Run and post the result.
0
 

Author Comment

by:jay-are
ID: 9726223
Select * From aafifimast Where DealNo=65283

0
 
LVL 58

Expert Comment

by:amit_g
ID: 9726257
So the deal number is getting passed. Can you execute this SQL in SQL Query Analyzer? If yes the problem is somewhere else. Are you sure the line with error is line 60 and that line is

Adapter.Fill(DealInfo, "aafifimast")
0
 

Author Comment

by:jay-are
ID: 9726284
That error report was a copy/paste.

I ran that select in the sql analyzer and here's the results:
Syntax error converting the varchar value ' ' to a column of data type int.

I browse the data and notice that right in the middle of the table that symbol goes through all columns....straight across...argh!

How do I get around that?  That's like a null value I guess?
0
 
LVL 12

Expert Comment

by:kingsfan76
ID: 9726315
is the column DealNo a type INT or type CHAR or VARCHAR?

what do you mean by the "symbol" goes through all columns?  you mean everything is null?
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 58

Expert Comment

by:amit_g
ID: 9726348
The table has DealNo as varchar and some rows have it as blank. Since you have condition  DealNo=65283, SQL tries to convert it to int and so the error. Change the SQL

Select * From aafifimast Where DealNo='65283'

and see if that works. If it does change the code to

SelectStatement = "Select  * From aafifimast Where DealNo= '" & DealNoSent & "'"

Note that there are ' in the string so copy it exactly.
0
 
LVL 12

Expert Comment

by:kingsfan76
ID: 9726351
as i remeber from last time, your DealNo is probably a type VARCHAR... if that's the case, try this statemetn in your analyzer:

Select * From aafifimast Where DealNo='65283'

if it works that means you need to add single quote to the sql statement
0
 

Author Comment

by:jay-are
ID: 9726354
It's a varchar.

That symbol appears about midway through the table.  Dunno why.

0
 
LVL 12

Expert Comment

by:kingsfan76
ID: 9726359
haha identical thoughts with amit_g

:-)
0
 
LVL 58

Expert Comment

by:amit_g
ID: 9726371
:)

Where did you get the data in that table? Is it valid data i.e. can you have non-numeric data in that column?
0
 

Author Comment

by:jay-are
ID: 9726402
amit and kingsfan:

Select * From aafifimast Where DealNo='65283'

This returns the desired results.  There is that symbol midway through the table(looks like a box) and then again at the end of the table.  So that's probably giving me the null values.

The entire table is all varchar's.  So it could have anything in it I guess.

With SelectStatement = "Select  * From aafifimast Where DealNo= '" & DealNoSent & "'" being used it now throws this error at me:

Exception Details: System.Data.SqlClient.SqlException: Could not find stored procedure 'False'.

Source Error:


Line 57:     Connect.ConnectionString = ConnectString
Line 58:     Adapter.SelectCommand = new SqlCommand(SelectStatement, Connect)
Line 59:     Adapter.Fill(DealInfo, "aafifimast")
Line 60:     End Sub
Line 61:
 

line 59

??????  The dealno.aspx page doesn't even call a stored procedure.  The other page uses one.  Why the conflict?
0
 
LVL 12

Expert Comment

by:kingsfan76
ID: 9726406
also if it is type varchar, then you should take out the check if isNumeric part

DealNoSent = Request.QueryString("Ref.No")       'it is still a string type

Sub GetDealNo(DealNoSent As string)
SelectStatement = "Select  * From aafifimast Where DealNo='" & _
       DealNoSent & "'"
   ConnectString = "server=myserver;uid=uid;PWD=pwd;database=default;"
   Connect.ConnectionString = ConnectString
   Adapter.SelectCommand = new SqlCommand(SelectStatement, Connect)
   Adapter.Fill(DealInfo, "aafifimast")
   End Sub


0
 
LVL 58

Expert Comment

by:amit_g
ID: 9726420
This error is not from these lines. Check where it is coming from. Also do a Response.Write and Response.End to make sure that the SQL is correct.
0
 

Author Comment

by:jay-are
ID: 9726431
I did this:

Sub GetDealNo(DealNoSent As integer)
        SelectStatement = SelectStatement = "Select  * From aafifimast Where DealNo= '" & DealNoSent & "'" & _
        DealNoSent
    ConnectString = "server=gwpf-web01;uid=sa;PWD=bigdellsql1;database=gwpf;"
    Connect.ConnectionString = ConnectString
    Response.Write(SelectStatement)
    Response.End
    Adapter.SelectCommand = new SqlCommand(SelectStatement, Connect)
    Adapter.Fill(DealInfo, "aafifimast")
    End Sub

The page now simply displays the word "False".
0
 
LVL 58

Expert Comment

by:amit_g
ID: 9726438
       SelectStatement = SelectStatement = "Select  * From aafifimast Where DealNo= '" & DealNoSent & "'" & _
        DealNoSent

is incorrect. Change it to ...

        SelectStatement = "Select  * From aafifimast Where DealNo= '" & DealNoSent & "'" & DealNoSent
0
 

Author Comment

by:jay-are
ID: 9726467
Haha wow...that's pretty sad that I did that.  Sorry for that!


Ok that's fixed.  Now here's the new error:

Exception Details: System.Data.SqlClient.SqlException: Line 1: Incorrect syntax near '65283'.

Source Error:


Line 60:    
Line 61:     Adapter.SelectCommand = new SqlCommand(SelectStatement, Connect)
Line 62:     Adapter.Fill(DealInfo, "aafifimast")
Line 63:     End Sub
Line 64:
 

Source File: c:\inetpub\wwwroot\aspx\new\dealno.aspx    Line: 62

0
 
LVL 58

Expert Comment

by:amit_g
ID: 9726508
SelectStatement = "Select  * From aafifimast Where DealNo= '" & DealNoSent & "'" & DealNoSent

is also incorrect :) Use this

SelectStatement = "Select  * From aafifimast Where DealNo= '" & DealNoSent & "'"
0
 

Author Comment

by:jay-are
ID: 9726525
Ok changed it.  Guess what?  Next error!

Cast from type 'DBNull' to type 'String' is not valid.
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.InvalidCastException: Cast from type 'DBNull' to type 'String' is not valid.

Source Error:


Line 34:         SalesM1Text.Text = Row.Item("s1.Name")
Line 35:         SalesM2Text.Text = Row.Item("s2.Name")
Line 36:         MdlText.Text = Row.Item("Mdl.No")
Line 37:         StkText.Text = Row.Item("Stk.No")
Line 38:         NUOText.Text = Row.Item("NUO")
 

Source File: c:\inetpub\wwwroot\aspx\new\dealno.aspx    Line: 36


Hah!  That symbol that is going through the middle of the table is causing a problem here I'm sure?

Do I just add & "" after my  MdlText.Text = Row.Item("Mdl.No")?
0
 

Author Comment

by:jay-are
ID: 9726543
Yes tada!  That works!

Thank you both for taking the time to help me.  I really appreciate it!

How do I do the points for this?  I'm still new here...

Accept amit, and give the assist to kingsfan?
0
 
LVL 58

Accepted Solution

by:
amit_g earned 500 total points
ID: 9726556
Fine with me. Glad to be of help :) See you around.
0
 

Author Comment

by:jay-are
ID: 9726576
Kingsfan, I'm not sure how to give you credit for this also.  You did help so I need to give ya something!  I'll create a new question.

Thanks for your help guys!
0
 
LVL 12

Expert Comment

by:kingsfan76
ID: 9726596
sorry i didn't have time to follow-up.  sucks to work on verteran's say.. sigh.
yeah amit_g definately deserve most of the credits   :-)
0
 
LVL 12

Expert Comment

by:kingsfan76
ID: 9726613
oh ok.  thank.

actually there's a link for "Split points" at the bottom of the screen.
maybe you can use that option next time  :-)
0
 

Author Comment

by:jay-are
ID: 9726629
0

Featured Post

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

I have helped a lot of people on EE with their coding sources and have enjoyed near about every minute of it. Sometimes it can get a little tedious but it is always a challenge and the one thing that I always say is:  The Exchange of information …
This demonstration started out as a follow up to some recently posted questions on the subject of logging in: http://www.experts-exchange.com/Programming/Languages/Scripting/JavaScript/Q_28634665.html and http://www.experts-exchange.com/Programming/…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
This tutorial demonstrates a quick way of adding group price to multiple Magento products.

708 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now