Value from a DataReader to a string

  • Thread starter Thread starter Catalin Porancea
  • Start date Start date
C

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(CommandBehavior
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.
 
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 said:
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(CommandBehavior
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
Click to expand...
____________________________________________________________________________
_____________________

Can anybody help?
Thank you.
Click to expand...
 
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
: 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
:
: : > 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(CommandBehavior
: > 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
: >
: >
:
:
 
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 said:
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
: 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
:
: : > 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(CommandBehavior
: > 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
: >
: >
:
:
Click to expand...
 
Thanks Teemu. It works now.

--
Catalin Porancea
: 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
:
: : > 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
: > : > : 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
: > :
: > : : > : > 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(CommandBehavior
: > : > 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
: > : >
: > : >
: > :
: > :
: >
: >
:
:
 
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 said:
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 said:
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
: 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
:
: : > 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(CommandBehavior
: > 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
: >
: >
:
:
Click to expand...
Click to expand...
 
Back
Top