Severe Error selecting ntext using sproc vb.net

  • Thread starter Thread starter abefuzzleduser2
  • Start date Start date
A

abefuzzleduser2

I have a two column table (identity and ntext) setup to be used by
spro0c and vb.net. I can not get this select sproc to work from vb.net
but I can exec the sproc by itself? So it must be my sproc params?
I was selecting ntext through sproc and vb.net I have tried several
things in sproc definition and looked on groups. My ntext will be for
serialized xml about 10k to 20k. I just inserted some dummy text
values for testing.

"SqlException:A severe error occurred on the current command."
Error: 17805, Severity: 20, State: 3 Invalid buffer received from
client. SQL2000SP3,

CREATE TABLE IQ] (
[InstallQuoteID] [int] IDENTITY (1, 1) NOT NULL ,
[InstallQuoteXML] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

--------------------
CREATE PROCEDURE usp_tblInstallQuote_Select
(
@Identity int,
@InstallQuoteXML ntext OUTPUT
)
AS
SET NOCOUNT ON;
Select InstallQuoteXML
from tblInstallQuote
where InstallQuoteID = @Identity
GO
---- vb.net code from select
' IDENTITY Input
sProcCmd3.Parameters.Add("@Identity", SqlDbType.Int)
sProcCmd3.Parameters("@Identity").Value = rowid
sProcCmd3.Parameters("@Identity").Direction =
ParameterDirection.Input
' add itemnmbr xml parameter OUTPUT
sProcCmd3.Parameters.Add("@InstallQuoteXML", SqlDbType.NText,
1073741823)
sProcCmd3.Parameters("@InstallQuoteXML").Value = New
System.Data.SqlTypes.SqlString(Nothing)
sProcCmd3.Parameters("@InstallQuoteXML").Direction =
ParameterDirection.Output
Dim rowstr As String
* sProcCmd3.ExecuteScalar()
rowstr = CType(sProcCmd3.Parameters("@InstallQuoteXML").Value,
String)

-------------------------------
Source Error:

Line 48: sProcCmd3.Parameters("@InstallQuoteXML").Direction =
ParameterDirection.Output
Line 49: Dim rowstr As String
Line 50: sProcCmd3.ExecuteScalar()
Line 51: 'rowstr =
CType(sProcCmd3.Parameters("@InstallQuoteXML").Value, String)
Line 52: 'Response.Write("row ftn=" & rowstr)


Source File: C:\Inetpub\wwwroot\INTRA0514\InstallQuoteDAL.vb Line:
50

Stack Trace:


[SqlException: A severe error occurred on the current command. The
results, if any, should be discarded.]
System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior
cmdBehavior, RunBehavior runBehavior, Boolean returnStream) +723
System.Data.SqlClient.SqlCommand.ExecuteScalar() +177
INTRA0514.InstallQuoteDAL.DbSelect(Int32 rowid) in
C:\Inetpub\wwwroot\INTRA0514\InstallQuoteDAL.vb:50
INTRA0514.InstallQuote.btnSave_Click(Object sender, EventArgs e) in
C:\Inetpub\wwwroot\INTRA0514\InstallQuote.aspx.vb:300
System.Web.UI.WebControls.Button.OnClick(EventArgs e) +108

System.Web.UI.WebControls.Button.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String
eventArgument) +57
System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler
sourceControl, String eventArgument) +18
System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData)
+33
System.Web.UI.Page.ProcessRequestMain() +1277
 
You are performing a query. Why is the ntext column marked
as an output parameter? You never populate it. It isn't really
working in Query Analyzer. You are running the query. QA
is returning the results. But, QA is not display the output
in the output variable.

Typically, you'd called the stored procedure and use a SqlDataReader
or SqlDataAdapter to read the returned values or load them into a
DataTable.

--
2005 Microsoft MVP C#
Robbe Morris
http://www.robbemorris.com
http://www.mastervb.net/home/ng/forumvbcode/post10017013.aspx
http://www.eggheadcafe.com/articles/adonet_source_code_generator.asp



I have a two column table (identity and ntext) setup to be used by
spro0c and vb.net. I can not get this select sproc to work from vb.net
but I can exec the sproc by itself? So it must be my sproc params?
I was selecting ntext through sproc and vb.net I have tried several
things in sproc definition and looked on groups. My ntext will be for
serialized xml about 10k to 20k. I just inserted some dummy text
values for testing.

"SqlException:A severe error occurred on the current command."
Error: 17805, Severity: 20, State: 3 Invalid buffer received from
client. SQL2000SP3,

CREATE TABLE IQ] (
[InstallQuoteID] [int] IDENTITY (1, 1) NOT NULL ,
[InstallQuoteXML] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

--------------------
CREATE PROCEDURE usp_tblInstallQuote_Select
(
@Identity int,
@InstallQuoteXML ntext OUTPUT
)
AS
SET NOCOUNT ON;
Select InstallQuoteXML
from tblInstallQuote
where InstallQuoteID = @Identity
GO
---- vb.net code from select
' IDENTITY Input
sProcCmd3.Parameters.Add("@Identity", SqlDbType.Int)
sProcCmd3.Parameters("@Identity").Value = rowid
sProcCmd3.Parameters("@Identity").Direction =
ParameterDirection.Input
' add itemnmbr xml parameter OUTPUT
sProcCmd3.Parameters.Add("@InstallQuoteXML", SqlDbType.NText,
1073741823)
sProcCmd3.Parameters("@InstallQuoteXML").Value = New
System.Data.SqlTypes.SqlString(Nothing)
sProcCmd3.Parameters("@InstallQuoteXML").Direction =
ParameterDirection.Output
Dim rowstr As String
* sProcCmd3.ExecuteScalar()
rowstr = CType(sProcCmd3.Parameters("@InstallQuoteXML").Value,
String)

-------------------------------
Source Error:

Line 48: sProcCmd3.Parameters("@InstallQuoteXML").Direction =
ParameterDirection.Output
Line 49: Dim rowstr As String
Line 50: sProcCmd3.ExecuteScalar()
Line 51: 'rowstr =
CType(sProcCmd3.Parameters("@InstallQuoteXML").Value, String)
Line 52: 'Response.Write("row ftn=" & rowstr)


Source File: C:\Inetpub\wwwroot\INTRA0514\InstallQuoteDAL.vb Line:
50

Stack Trace:


[SqlException: A severe error occurred on the current command. The
results, if any, should be discarded.]
System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior
cmdBehavior, RunBehavior runBehavior, Boolean returnStream) +723
System.Data.SqlClient.SqlCommand.ExecuteScalar() +177
INTRA0514.InstallQuoteDAL.DbSelect(Int32 rowid) in
C:\Inetpub\wwwroot\INTRA0514\InstallQuoteDAL.vb:50
INTRA0514.InstallQuote.btnSave_Click(Object sender, EventArgs e) in
C:\Inetpub\wwwroot\INTRA0514\InstallQuote.aspx.vb:300
System.Web.UI.WebControls.Button.OnClick(EventArgs e) +108

System.Web.UI.WebControls.Button.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String
eventArgument) +57
System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler
sourceControl, String eventArgument) +18
System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData)
+33
System.Web.UI.Page.ProcessRequestMain() +1277
Click to expand...
 
Thanks Robbe,

I will change the sproc and will go back to using a dataset.

I was trying to get around using the dataset. I will go back to dataset
since I need to read/write serialized (or encoded) xml file anyway.

Thanks
P
Robbe said:
You are performing a query. Why is the ntext column marked
as an output parameter? You never populate it. It isn't really
working in Query Analyzer. You are running the query. QA
is returning the results. But, QA is not display the output
in the output variable.

Typically, you'd called the stored procedure and use a SqlDataReader
or SqlDataAdapter to read the returned values or load them into a
DataTable.

--
2005 Microsoft MVP C#
Robbe Morris
http://www.robbemorris.com
http://www.mastervb.net/home/ng/forumvbcode/post10017013.aspx
http://www.eggheadcafe.com/articles/adonet_source_code_generator.asp



I have a two column table (identity and ntext) setup to be used by
spro0c and vb.net. I can not get this select sproc to work from vb.net
but I can exec the sproc by itself? So it must be my sproc params?
I was selecting ntext through sproc and vb.net I have tried several
things in sproc definition and looked on groups. My ntext will be for
serialized xml about 10k to 20k. I just inserted some dummy text
values for testing.

"SqlException:A severe error occurred on the current command."
Error: 17805, Severity: 20, State: 3 Invalid buffer received from
client. SQL2000SP3,

CREATE TABLE IQ] (
[InstallQuoteID] [int] IDENTITY (1, 1) NOT NULL ,
[InstallQuoteXML] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

--------------------
CREATE PROCEDURE usp_tblInstallQuote_Select
(
@Identity int,
@InstallQuoteXML ntext OUTPUT
)
AS
SET NOCOUNT ON;
Select InstallQuoteXML
from tblInstallQuote
where InstallQuoteID = @Identity
GO
---- vb.net code from select
' IDENTITY Input
sProcCmd3.Parameters.Add("@Identity", SqlDbType.Int)
sProcCmd3.Parameters("@Identity").Value = rowid
sProcCmd3.Parameters("@Identity").Direction =
ParameterDirection.Input
' add itemnmbr xml parameter OUTPUT
sProcCmd3.Parameters.Add("@InstallQuoteXML", SqlDbType.NText,
1073741823)
sProcCmd3.Parameters("@InstallQuoteXML").Value = New
System.Data.SqlTypes.SqlString(Nothing)
sProcCmd3.Parameters("@InstallQuoteXML").Direction =
ParameterDirection.Output
Dim rowstr As String
* sProcCmd3.ExecuteScalar()
rowstr = CType(sProcCmd3.Parameters("@InstallQuoteXML").Value,
String)

-------------------------------
Source Error:

Line 48: sProcCmd3.Parameters("@InstallQuoteXML").Direction =
ParameterDirection.Output
Line 49: Dim rowstr As String
Line 50: sProcCmd3.ExecuteScalar()
Line 51: 'rowstr =
CType(sProcCmd3.Parameters("@InstallQuoteXML").Value, String)
Line 52: 'Response.Write("row ftn=" & rowstr)


Source File: C:\Inetpub\wwwroot\INTRA0514\InstallQuoteDAL.vb Line:
50

Stack Trace:


[SqlException: A severe error occurred on the current command. The
results, if any, should be discarded.]
System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior
cmdBehavior, RunBehavior runBehavior, Boolean returnStream) +723
System.Data.SqlClient.SqlCommand.ExecuteScalar() +177
INTRA0514.InstallQuoteDAL.DbSelect(Int32 rowid) in
C:\Inetpub\wwwroot\INTRA0514\InstallQuoteDAL.vb:50
INTRA0514.InstallQuote.btnSave_Click(Object sender, EventArgs e) in
C:\Inetpub\wwwroot\INTRA0514\InstallQuote.aspx.vb:300
System.Web.UI.WebControls.Button.OnClick(EventArgs e) +108

System.Web.UI.WebControls.Button.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String
eventArgument) +57
System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler
sourceControl, String eventArgument) +18
System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData)
+33
System.Web.UI.Page.ProcessRequestMain() +1277

--------------------------------------------------------------------------------
Version Information: Microsoft .NET Framework Version:1.1.4322.573;
ASP.NET Version:1.1.4322.573
Click to expand...
Click to expand...
 
Robbe,

I still get the same error after switching to da, ds and using fill
method. It must be from returning ntext column from database. I
still get the same error from selecting using da.fill (ds, table). I
must have to convert the return value?
 
Here is some of the code my ADO.NET code generator (link below)
spit out for retrieving an ntext column (HelpText)

From SQL Server

CREATE PROCEDURE dbo.GetHelpText
(
@QuestionID int
)
AS

select DisplayOrder,Description,HelpText
from Question
where QuestionID = @QuestionID

ADO.NET code

#region Prepare Command

public static SqlCommand PrepareCommand(int QuestionID)

{

SqlCommand oCmd = new SqlCommand();

SqlParameter oPrm;


try

{


oCmd.CommandType=CommandType.StoredProcedure;

oCmd.CommandText="GetHelpText";


oPrm = new SqlParameter();

oPrm.ParameterName = "@QuestionID";

oPrm.SqlDbType = SqlDbType.Int;

oPrm.Precision = (byte)int.Parse("0");

oPrm.Size = int.Parse("0");

oPrm.Scale = (byte)int.Parse("0");

oPrm.SourceColumn = null;

oPrm.Direction = ParameterDirection.Input;

oPrm.Value = QuestionID;

oCmd.Parameters.Add(oPrm);


}

catch (Exception e) { throw e; }

return oCmd;

}

#endregion



#region To DataTable

public static DataTable ToDataTable(string ConnectionString,int QuestionID)

{

DataTable dt = new DataTable();


try

{


SqlCommand oCmd = PrepareCommand(QuestionID);


using (SqlConnection oConn = new SqlConnection())

{


oConn.ConnectionString = ConnectionString;

oConn.Open();

oCmd.Connection = oConn;


using(SqlDataAdapter da = new SqlDataAdapter(oCmd))

{

da.Fill(dt);

oCmd.Dispose();

}

}


}

catch (Exception e) { throw e; }

return dt;

}

#endregion



--
2005 Microsoft MVP C#
Robbe Morris
http://www.robbemorris.com
http://www.mastervb.net/home/ng/forumvbcode/post10017013.aspx
http://www.eggheadcafe.com/articles/adonet_source_code_generator.asp
 
Back
Top