C# Program With SqlDbType.Text Parameter

[Steve Harclerode]

I'm trying to get a parameter of type SqlDbType.Text to work properly. Is
there some significant difference between Text parameters and other
SqlDbTypes? I'm hoping that there's some simple thing that I'm missing. The
code directly below (1) works fine, but the code at the bottom (2) gives the
"syntax" exception at the bottom of the page. The xmlText parameter (passed
into this function) varies between 2K and 30K of xml.

I appreciate any help, I've been working on this for a while.

Thank you,

- Steve Harclerode

-------------- code 1 begin (WORKS!)
string connectionString = "server=" + server + "; uid=" + userId +
"; pwd=" + password + ";";
System.Data.SqlClient.SqlConnection con =
new System.Data.SqlClient.SqlConnection( connectionString );
con.Open();

System.Data.SqlClient.SqlCommand cmd =
new System.Data.SqlClient.SqlCommand();
cmd.Connection = con;

cmd.CommandText = "InsertHealthData @xmlText='" + xmlText + "'";

cmd.ExecuteNonQuery();
con.Close();
-------------- code 1 end

-------------- code 2 begin (FAILS)
string connectionString = "server=" + server + "; uid=" + userId +
"; pwd=" + password + ";";
System.Data.SqlClient.SqlConnection con =
new System.Data.SqlClient.SqlConnection( connectionString );
con.Open();

System.Data.SqlClient.SqlCommand cmd =
new System.Data.SqlClient.SqlCommand();
cmd.Connection = con;

cmd.CommandText = "InsertHealthData";
System.Data.SqlClient.SqlParameter param =
cmd.Parameters.Add("@xmlText", SqlDbType.Text );
/* this doesn't work either: cmd.Parameters.Add("@xmlText", SqlDbType.Text,
2147483647 ) */
param.Direction = ParameterDirection.Input;
param.Value = xmlText;

cmd.ExecuteNonQuery();
con.Close();
-------------- code 2 end

-------------- exception begin
System.Data.SqlClient.SqlException: Line 1: Incorrect syntax near
'InsertHealthData'.
at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior
cmdBehavior, RunBehavior runBehavior, Boolean returnStream)
at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
-------------- exception end

[Juan Gabriel Del Cid]

> cmd.CommandText = "InsertHealthData";
> System.Data.SqlClient.SqlParameter param =
> cmd.Parameters.Add("@xmlText", SqlDbType.Text);

You are not including your parameter in the CommandText. I think it would
work if you set it like this:

cmd.CommandText = "InsertHealthData xmlText=@xmlText";

I have added some sample code that might be of help.
-Juan Gabriel

//////////
/// Inserting a row into a table named Users
///

To insert a row into a table I would do this:

cmd.CommandText = "INSERT INTO Users(UserName, FullName, Age) " +
"VALUES(@name, @fullName, @description)";

cmd.Parameters.Add("@name", SqlDbType.VarChar, 20).Value = name;
cmd.Parameters.Add("@fullName", SqlDbType.VarChar, 100).Value = fullName;
cmd.Parameters.Add("@age", SqlDbType.Int).Value = age;

And to call a stored procedure that looks like this:

CREATE STORED PROCUDURE Users_Insert
@name varchar(20),
@fullName varchar(100),
@age int
AS ...

I would do this:

cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "Users_Insert @name, @fullName, @age";

cmd.Parameters.Add("@name", SqlDbType.VarChar, 20).Value = name;
cmd.Parameters.Add("@fullName", SqlDbType.VarChar, 100).Value = fullName;
cmd.Parameters.Add("@age", SqlDbType.Int).Value = age;

[Steve Harclerode]

Thanks for putting me on the right path. What you said was correct.

However, I also had to change the CommandText to "Exec InsertHealthData"
before the name of the stored proc, in order to get it to work with the
explicit parameter.

I'm going to write Jesse Liberty about this -- I used an example from his
ASP.NET book (which is otherwise quite good, in my opinion).

-- Steve Harclerode

"Juan Gabriel Del Cid" <jde...@atrevido.nospam.net> wrote in message
news:eEVPjFB7...@TK2MSFTNGP12.phx.gbl...

[Steve Harclerode]

Whoops, never mind, Jesse was using a DataAdapter and a DataSet, apparently
the requirements for calling using ExecuteNonQuery() are different.

- Steve

"Steve Harclerode" <CamelSoftw...@hotmail.com> wrote in message
news:#l7BAuB7...@TK2MSFTNGP11.phx.gbl...

[Juan Gabriel Del Cid]

Yeah, DataAdapters and DataSets are beautiful, :-D.

-JG

"Steve Harclerode" <CamelSoftw...@hotmail.com> wrote in message

news:er3he0B7...@TK2MSFTNGP12.phx.gbl...

[Jesse Liberty]

Glad you were able to sort it out. If you ever have a question about one of
my books, don't hesitate to check my web site
(http://www.LibertyAssociates.com - click on books). You'll find a FAQ,
errata, source code and (most important) a link to a private support
discussion group where you can post your question.

Thanks again.

-j

------
Jesse Liberty, President
Liberty Associates, Inc.

"Steve Harclerode" <CamelSoftw...@hotmail.com> wrote in message

news:er3he0B7...@TK2MSFTNGP12.phx.gbl...

[Steve Harclerode]

Thanks for the tip (and for the books).

- Steve

"Jesse Liberty" <jlib...@libertyassociates.com> wrote in message
news:#O9jEIN7...@TK2MSFTNGP11.phx.gbl...