Value from a DataReader to a string

[Catalin Porancea]

Whenever I try this:

__________________________________________________ __________________
Dim cmd_sp_payments As SqlCommand = New SqlCommand("sp_payments", con)
cmd_sp_payments.CommandType = CommandType.StoredProcedure
Dim CustID As SqlParameter = cmd_sp_payments.Parameters.Add("@cust_id",
SqlDbType.Text)
CustID.Value = strCustID
Dim myReader As SqlDataReader = cmd_sp_payments.ExecuteReader()
If myReader.HasRows Then
Dim strLastPayAmt As String = "$" & myReader.GetString("Pay_Amount")
lblLastPayAmt.Text = strLastPayAmt
myReader.Close()
Else
myReader.Close()
End If
__________________________________________________ _____________________
I get this error:
__________________________________________________ _____________________
Operand type clash: text is incompatible with int
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: Operand type clash:
text is incompatible with int

Source Error:

Line 140: CustID.Value = strCustID
Line 141:
Line 142: Dim myReader As SqlDataReader =
cmd_sp_payments.ExecuteReader()
Line 143: If myReader.HasRows Then
Line 144: Dim strLastPayAmt As String = "$" &
myReader.GetString("Pay_Amount")
Source File: C:\Inetpub\wwwroot\MTIWEB\CustomerPage.aspx.vb Line: 142

Stack Trace:

[SqlException: Operand type clash: text is incompatible with int]
System.Data.SqlClient.SqlCommand.ExecuteReader(Com mandBehavior
cmdBehavior, RunBehavior runBehavior, Boolean returnStream)
System.Data.SqlClient.SqlCommand.ExecuteReader()
MTIWEB.CustomerPage.Page_Load(Object sender, EventArgs e) in
C:\Inetpub\wwwroot\MTIWEB\CustomerPage.aspx.vb:142
System.Web.UI.Control.OnLoad(EventArgs e) +67
System.Web.UI.Control.LoadRecursive() +35
System.Web.UI.Page.ProcessRequestMain() +731
Version Information: Microsoft .NET Framework Version:1.1.4322.573; ASP.NET
Version:1.1.4322.573
__________________________________________________ __________________________
_____________________

Can anybody help?
Thank you.
--
Catalin Porancea

[Teemu Keiski]

Hi,

GetString method of DataReader expects only ordinal position of the column
(index), it doesn't accept column name.

You could use either the index of the column (integer) instead of column
name or optionally something like this:

...."$" & Convert.ToString(myReader("Pay_Amount"))

--
Teemu Keiski
MCP, Microsoft MVP (ASP.NET), AspInsiders member
ASP.NET Forum Moderator, AspAlliance Columnist

"Catalin Porancea" <cp*******@midwestern.net> wrote in message
news:u7****************@TK2MSFTNGP10.phx.gbl...

Whenever I try this:

__________________________________________________ __________________
Dim cmd_sp_payments As SqlCommand = New SqlCommand("sp_payments", con)
cmd_sp_payments.CommandType = CommandType.StoredProcedure
Dim CustID As SqlParameter = cmd_sp_payments.Parameters.Add("@cust_id",
SqlDbType.Text)
CustID.Value = strCustID
Dim myReader As SqlDataReader = cmd_sp_payments.ExecuteReader()
If myReader.HasRows Then
Dim strLastPayAmt As String = "$" & myReader.GetString("Pay_Amount")
lblLastPayAmt.Text = strLastPayAmt
myReader.Close()
Else
myReader.Close()
End If
__________________________________________________ _____________________
I get this error:
__________________________________________________ _____________________
Operand type clash: text is incompatible with int
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: Operand type clash:
text is incompatible with int

Source Error:

Line 140: CustID.Value = strCustID
Line 141:
Line 142: Dim myReader As SqlDataReader =
cmd_sp_payments.ExecuteReader()
Line 143: If myReader.HasRows Then
Line 144: Dim strLastPayAmt As String = "$" &
myReader.GetString("Pay_Amount")
Source File: C:\Inetpub\wwwroot\MTIWEB\CustomerPage.aspx.vb Line: 142

Stack Trace:

[SqlException: Operand type clash: text is incompatible with int]
System.Data.SqlClient.SqlCommand.ExecuteReader(Com mandBehavior
cmdBehavior, RunBehavior runBehavior, Boolean returnStream)
System.Data.SqlClient.SqlCommand.ExecuteReader()
MTIWEB.CustomerPage.Page_Load(Object sender, EventArgs e) in
C:\Inetpub\wwwroot\MTIWEB\CustomerPage.aspx.vb:142
System.Web.UI.Control.OnLoad(EventArgs e) +67
System.Web.UI.Control.LoadRecursive() +35
System.Web.UI.Page.ProcessRequestMain() +731
Version Information: Microsoft .NET Framework Version:1.1.4322.573; ASP.NET Version:1.1.4322.573
__________________________________________________ __________________________ _____________________

Can anybody help?
Thank you.
--
Catalin Porancea

[Catalin Porancea]

Thanks Teemu.

Now the message I get is
Invalid attempt to read when no data is present.

In query analyzer, exec sp_payments 579 returns one row, as it should.

Can you help me with this?

--
Catalin Porancea
"Teemu Keiski" <jo****@aspalliance.com> wrote in message
news:u%***************@TK2MSFTNGP11.phx.gbl...
: Hi,
:
: GetString method of DataReader expects only ordinal position of the column
: (index), it doesn't accept column name.
:
: You could use either the index of the column (integer) instead of column
: name or optionally something like this:
:
: ..."$" & Convert.ToString(myReader("Pay_Amount"))
:
: --
: Teemu Keiski
: MCP, Microsoft MVP (ASP.NET), AspInsiders member
: ASP.NET Forum Moderator, AspAlliance Columnist
:
: "Catalin Porancea" <cp*******@midwestern.net> wrote in message
: news:u7****************@TK2MSFTNGP10.phx.gbl...
: > Whenever I try this:
: >
: > __________________________________________________ __________________
: > Dim cmd_sp_payments As SqlCommand = New SqlCommand("sp_payments", con)
: > cmd_sp_payments.CommandType = CommandType.StoredProcedure
: > Dim CustID As SqlParameter = cmd_sp_payments.Parameters.Add("@cust_id",
: > SqlDbType.Text)
: > CustID.Value = strCustID
: > Dim myReader As SqlDataReader = cmd_sp_payments.ExecuteReader()
: > If myReader.HasRows Then
: > Dim strLastPayAmt As String = "$" & myReader.GetString("Pay_Amount")
: > lblLastPayAmt.Text = strLastPayAmt
: > myReader.Close()
: > Else
: > myReader.Close()
: > End If
: > __________________________________________________ _____________________
: > I get this error:
: > __________________________________________________ _____________________
: > Operand type clash: text is incompatible with int
: > 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: Operand type
clash:
: > text is incompatible with int
: >
: > Source Error:
: >
: > Line 140: CustID.Value = strCustID
: > Line 141:
: > Line 142: Dim myReader As SqlDataReader =
: > cmd_sp_payments.ExecuteReader()
: > Line 143: If myReader.HasRows Then
: > Line 144: Dim strLastPayAmt As String = "$" &
: > myReader.GetString("Pay_Amount")
: >
: >
: > Source File: C:\Inetpub\wwwroot\MTIWEB\CustomerPage.aspx.vb Line: 142
: >
: > Stack Trace:
: >
: > [SqlException: Operand type clash: text is incompatible with int]
: > System.Data.SqlClient.SqlCommand.ExecuteReader(Com mandBehavior
: > cmdBehavior, RunBehavior runBehavior, Boolean returnStream)
: > System.Data.SqlClient.SqlCommand.ExecuteReader()
: > MTIWEB.CustomerPage.Page_Load(Object sender, EventArgs e) in
: > C:\Inetpub\wwwroot\MTIWEB\CustomerPage.aspx.vb:142
: > System.Web.UI.Control.OnLoad(EventArgs e) +67
: > System.Web.UI.Control.LoadRecursive() +35
: > System.Web.UI.Page.ProcessRequestMain() +731
: >
: >
: > Version Information: Microsoft .NET Framework Version:1.1.4322.573;
: ASP.NET
: > Version:1.1.4322.573
: >
:
__________________________________________________ __________________________
: > _____________________
: >
: > Can anybody help?
: > Thank you.
: >
: >
: > --
: > Catalin Porancea
: >
: >
:
:

[Teemu Keiski]

Ah yes, you need to call Read method for the reader at least once to get the
row.

For example:

Dim myReader As SqlDataReader = cmd_sp_payments.ExecuteReader()
If myReader.HasRows Then

myReader.Read()

Dim strLastPayAmt As String = "$" & myReader.GetString("Pay_Amount")
lblLastPayAmt.Text = strLastPayAmt
myReader.Close()
Else
myReader.Close()
End If

Though this assumes you know, there is always only one row or not a row at
all.

--
Teemu Keiski
MCP, Microsoft MVP (ASP.NET), AspInsiders member
ASP.NET Forum Moderator, AspAlliance Columnist

"Catalin Porancea" <cp*******@midwestern.net> wrote in message
news:u8**************@TK2MSFTNGP12.phx.gbl...

Thanks Teemu.

Now the message I get is
Invalid attempt to read when no data is present.

In query analyzer, exec sp_payments 579 returns one row, as it should.

Can you help me with this?

--
Catalin Porancea
"Teemu Keiski" <jo****@aspalliance.com> wrote in message
news:u%***************@TK2MSFTNGP11.phx.gbl...
: Hi,
:
: GetString method of DataReader expects only ordinal position of the column : (index), it doesn't accept column name.
:
: You could use either the index of the column (integer) instead of column
: name or optionally something like this:
:
: ..."$" & Convert.ToString(myReader("Pay_Amount"))
:
: --
: Teemu Keiski
: MCP, Microsoft MVP (ASP.NET), AspInsiders member
: ASP.NET Forum Moderator, AspAlliance Columnist
:
: "Catalin Porancea" <cp*******@midwestern.net> wrote in message
: news:u7****************@TK2MSFTNGP10.phx.gbl...
: > Whenever I try this:
: >
: > __________________________________________________ __________________
: > Dim cmd_sp_payments As SqlCommand = New SqlCommand("sp_payments", con)
: > cmd_sp_payments.CommandType = CommandType.StoredProcedure
: > Dim CustID As SqlParameter = cmd_sp_payments.Parameters.Add("@cust_id", : > SqlDbType.Text)
: > CustID.Value = strCustID
: > Dim myReader As SqlDataReader = cmd_sp_payments.ExecuteReader()
: > If myReader.HasRows Then
: > Dim strLastPayAmt As String = "$" & myReader.GetString("Pay_Amount")
: > lblLastPayAmt.Text = strLastPayAmt
: > myReader.Close()
: > Else
: > myReader.Close()
: > End If
: > __________________________________________________ _____________________ : > I get this error:
: > __________________________________________________ _____________________ : > Operand type clash: text is incompatible with int
: > 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: Operand type
clash:
: > text is incompatible with int
: >
: > Source Error:
: >
: > Line 140: CustID.Value = strCustID
: > Line 141:
: > Line 142: Dim myReader As SqlDataReader =
: > cmd_sp_payments.ExecuteReader()
: > Line 143: If myReader.HasRows Then
: > Line 144: Dim strLastPayAmt As String = "$" &
: > myReader.GetString("Pay_Amount")
: >
: >
: > Source File: C:\Inetpub\wwwroot\MTIWEB\CustomerPage.aspx.vb Line: 142 : >
: > Stack Trace:
: >
: > [SqlException: Operand type clash: text is incompatible with int]
: > System.Data.SqlClient.SqlCommand.ExecuteReader(Com mandBehavior
: > cmdBehavior, RunBehavior runBehavior, Boolean returnStream)
: > System.Data.SqlClient.SqlCommand.ExecuteReader()
: > MTIWEB.CustomerPage.Page_Load(Object sender, EventArgs e) in
: > C:\Inetpub\wwwroot\MTIWEB\CustomerPage.aspx.vb:142
: > System.Web.UI.Control.OnLoad(EventArgs e) +67
: > System.Web.UI.Control.LoadRecursive() +35
: > System.Web.UI.Page.ProcessRequestMain() +731
: >
: >
: > Version Information: Microsoft .NET Framework Version:1.1.4322.573;
: ASP.NET
: > Version:1.1.4322.573
: >
:
__________________________________________________ __________________________ : > _____________________
: >
: > Can anybody help?
: > Thank you.
: >
: >
: > --
: > Catalin Porancea
: >
: >
:
:

[Catalin Porancea]

Thanks Teemu. It works now.

--
Catalin Porancea
"Teemu Keiski" <jo****@aspalliance.com> wrote in message
news:uL*************@TK2MSFTNGP09.phx.gbl...
: Ah yes, you need to call Read method for the reader at least once to get
the
: row.
:
: For example:
:
: Dim myReader As SqlDataReader = cmd_sp_payments.ExecuteReader()
: If myReader.HasRows Then
:
: myReader.Read()
:
: Dim strLastPayAmt As String = "$" & myReader.GetString("Pay_Amount")
: lblLastPayAmt.Text = strLastPayAmt
: myReader.Close()
: Else
: myReader.Close()
: End If
:
: Though this assumes you know, there is always only one row or not a row at
: all.
:
: --
: Teemu Keiski
: MCP, Microsoft MVP (ASP.NET), AspInsiders member
: ASP.NET Forum Moderator, AspAlliance Columnist
:
: "Catalin Porancea" <cp*******@midwestern.net> wrote in message
: news:u8**************@TK2MSFTNGP12.phx.gbl...
: > Thanks Teemu.
: >
: > Now the message I get is
: > Invalid attempt to read when no data is present.
: >
: > In query analyzer, exec sp_payments 579 returns one row, as it should.
: >
: > Can you help me with this?
: >
: > --
: > Catalin Porancea
: > "Teemu Keiski" <jo****@aspalliance.com> wrote in message
: > news:u%***************@TK2MSFTNGP11.phx.gbl...
: > : Hi,
: > :
: > : GetString method of DataReader expects only ordinal position of the
: column
: > : (index), it doesn't accept column name.
: > :
: > : You could use either the index of the column (integer) instead of
column
: > : name or optionally something like this:
: > :
: > : ..."$" & Convert.ToString(myReader("Pay_Amount"))
: > :
: > : --
: > : Teemu Keiski
: > : MCP, Microsoft MVP (ASP.NET), AspInsiders member
: > : ASP.NET Forum Moderator, AspAlliance Columnist
: > :
: > : "Catalin Porancea" <cp*******@midwestern.net> wrote in message
: > : news:u7****************@TK2MSFTNGP10.phx.gbl...
: > : > Whenever I try this:
: > : >
: > : > __________________________________________________ __________________
: > : > Dim cmd_sp_payments As SqlCommand = New SqlCommand("sp_payments",
con)
: > : > cmd_sp_payments.CommandType = CommandType.StoredProcedure
: > : > Dim CustID As SqlParameter =
: cmd_sp_payments.Parameters.Add("@cust_id",
: > : > SqlDbType.Text)
: > : > CustID.Value = strCustID
: > : > Dim myReader As SqlDataReader = cmd_sp_payments.ExecuteReader()
: > : > If myReader.HasRows Then
: > : > Dim strLastPayAmt As String = "$" & myReader.GetString("Pay_Amount")
: > : > lblLastPayAmt.Text = strLastPayAmt
: > : > myReader.Close()
: > : > Else
: > : > myReader.Close()
: > : > End If
: > : >
: __________________________________________________ _____________________
: > : > I get this error:
: > : >
: __________________________________________________ _____________________
: > : > Operand type clash: text is incompatible with int
: > : > 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: Operand type
: > clash:
: > : > text is incompatible with int
: > : >
: > : > Source Error:
: > : >
: > : > Line 140: CustID.Value = strCustID
: > : > Line 141:
: > : > Line 142: Dim myReader As SqlDataReader =
: > : > cmd_sp_payments.ExecuteReader()
: > : > Line 143: If myReader.HasRows Then
: > : > Line 144: Dim strLastPayAmt As String = "$" &
: > : > myReader.GetString("Pay_Amount")
: > : >
: > : >
: > : > Source File: C:\Inetpub\wwwroot\MTIWEB\CustomerPage.aspx.vb Line:
: 142
: > : >
: > : > Stack Trace:
: > : >
: > : > [SqlException: Operand type clash: text is incompatible with int]
: > : > System.Data.SqlClient.SqlCommand.ExecuteReader(Com mandBehavior
: > : > cmdBehavior, RunBehavior runBehavior, Boolean returnStream)
: > : > System.Data.SqlClient.SqlCommand.ExecuteReader()
: > : > MTIWEB.CustomerPage.Page_Load(Object sender, EventArgs e) in
: > : > C:\Inetpub\wwwroot\MTIWEB\CustomerPage.aspx.vb:142
: > : > System.Web.UI.Control.OnLoad(EventArgs e) +67
: > : > System.Web.UI.Control.LoadRecursive() +35
: > : > System.Web.UI.Page.ProcessRequestMain() +731
: > : >
: > : >
: > : > Version Information: Microsoft .NET Framework Version:1.1.4322.573;
: > : ASP.NET
: > : > Version:1.1.4322.573
: > : >
: > :
: >
:
__________________________________________________ __________________________
: > : > _____________________
: > : >
: > : > Can anybody help?
: > : > Thank you.
: > : >
: > : >
: > : > --
: > : > Catalin Porancea
: > : >
: > : >
: > :
: > :
: >
: >
:
:

[vijay]

hi,
The error might be in calling the stored procedure with valid
parameters please check if you are passing the correct parameters or
not.

and also another method for getting values from reader is

myReader.GetValue(index).ToString();


"Teemu Keiski" <jo****@aspalliance.com> wrote in message news:<uL*************@TK2MSFTNGP09.phx.gbl>...

Ah yes, you need to call Read method for the reader at least once to get the
row.

For example:

Dim myReader As SqlDataReader = cmd_sp_payments.ExecuteReader()
If myReader.HasRows Then

myReader.Read()

Dim strLastPayAmt As String = "$" & myReader.GetString("Pay_Amount")
lblLastPayAmt.Text = strLastPayAmt
myReader.Close()
Else
myReader.Close()
End If

Though this assumes you know, there is always only one row or not a row at
all.

--
Teemu Keiski
MCP, Microsoft MVP (ASP.NET), AspInsiders member
ASP.NET Forum Moderator, AspAlliance Columnist

"Catalin Porancea" <cp*******@midwestern.net> wrote in message
news:u8**************@TK2MSFTNGP12.phx.gbl...

Thanks Teemu.

Now the message I get is
Invalid attempt to read when no data is present.

In query analyzer, exec sp_payments 579 returns one row, as it should.

Can you help me with this?

--
Catalin Porancea
"Teemu Keiski" <jo****@aspalliance.com> wrote in message
news:u%***************@TK2MSFTNGP11.phx.gbl...
: Hi,
:
: GetString method of DataReader expects only ordinal position of the

column

: (index), it doesn't accept column name.
:
: You could use either the index of the column (integer) instead of column
: name or optionally something like this:
:
: ..."$" & Convert.ToString(myReader("Pay_Amount"))
:
: --
: Teemu Keiski
: MCP, Microsoft MVP (ASP.NET), AspInsiders member
: ASP.NET Forum Moderator, AspAlliance Columnist
:
: "Catalin Porancea" <cp*******@midwestern.net> wrote in message
: news:u7****************@TK2MSFTNGP10.phx.gbl...
: > Whenever I try this:
: >
: > __________________________________________________ __________________
: > Dim cmd_sp_payments As SqlCommand = New SqlCommand("sp_payments", con)
: > cmd_sp_payments.CommandType = CommandType.StoredProcedure
: > Dim CustID As SqlParameter =

cmd_sp_payments.Parameters.Add("@cust_id",

: > SqlDbType.Text)
: > CustID.Value = strCustID
: > Dim myReader As SqlDataReader = cmd_sp_payments.ExecuteReader()
: > If myReader.HasRows Then
: > Dim strLastPayAmt As String = "$" & myReader.GetString("Pay_Amount")
: > lblLastPayAmt.Text = strLastPayAmt
: > myReader.Close()
: > Else
: > myReader.Close()
: > End If
: >

__________________________________________________ _____________________

: > I get this error:
: >

__________________________________________________ _____________________

: > Operand type clash: text is incompatible with int
: > 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: Operand type

clash:

: > text is incompatible with int
: >
: > Source Error:
: >
: > Line 140: CustID.Value = strCustID
: > Line 141:
: > Line 142: Dim myReader As SqlDataReader =
: > cmd_sp_payments.ExecuteReader()
: > Line 143: If myReader.HasRows Then
: > Line 144: Dim strLastPayAmt As String = "$" &
: > myReader.GetString("Pay_Amount")
: >
: >
: > Source File: C:\Inetpub\wwwroot\MTIWEB\CustomerPage.aspx.vb Line:

142

: >
: > Stack Trace:
: >
: > [SqlException: Operand type clash: text is incompatible with int]
: > System.Data.SqlClient.SqlCommand.ExecuteReader(Com mandBehavior
: > cmdBehavior, RunBehavior runBehavior, Boolean returnStream)
: > System.Data.SqlClient.SqlCommand.ExecuteReader()
: > MTIWEB.CustomerPage.Page_Load(Object sender, EventArgs e) in
: > C:\Inetpub\wwwroot\MTIWEB\CustomerPage.aspx.vb:142
: > System.Web.UI.Control.OnLoad(EventArgs e) +67
: > System.Web.UI.Control.LoadRecursive() +35
: > System.Web.UI.Page.ProcessRequestMain() +731
: >
: >
: > Version Information: Microsoft .NET Framework Version:1.1.4322.573;

ASP.NET

: > Version:1.1.4322.573
: >
:

__________________________________________________ __________________________

: > _____________________
: >
: > Can anybody help?
: > Thank you.
: >
: >
: > --
: > Catalin Porancea
: >
: >
:
: