469,133 Members | 1,167 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,133 developers. It's quick & easy.

SQL Server Insert Problem

I have created a class and stored procedure to insert records into a table.
The insert works but only the first character of the "Title" and "Body"
fields are added. I have traced up to the point of executing the sproc and
the string values are intact up to this point. Below is my code for the
SPROC and the insert method of the class:

.................................................. .......................................
Sproc
.................................................. .......................................
CREATE PROCEDURE spAddBlog
@pTitle varchar,
@pBody varchar,
@pStartdate datetime,
@pEnddate datetime,
@pActive bit
AS
INSERT INTO blog
Values (@pTitle,@pBody,@pStartdate,@pEnddate,@pActive)
GO
.................................................. .......................................
Class
.................................................. .......................................
mStartdate = DateTime.Now;
mEnddate = DateTime.Now;
mActive = 1;

/* Connection Object */
SqlConnection conn = new
SqlConnection(ConfigurationSettings.AppSetting["connString"]);
conn.Open();

/* Command Object */
SqlCommand comm = new SqlCommand();
comm.Connection = conn;
comm.CommandText = "spAddBlog";
comm.CommandType = CommandType.StoredProcedure;

SqlParameter objParam;

objParam = comm.Parameters.Add("@pTitle",SqlDbType.VarChar);
objParam.Direction = ParameterDirection.Input;
objParam.Value = mTitle;

objParam = comm.Parameters.Add("@pBody",SqlDbType.VarChar);
objParam.Direction = ParameterDirection.Input;
objParam.Value = mBody;

objParam = comm.Parameters.Add("@pStartdate",SqlDbType.DateTi me);
objParam.Direction = ParameterDirection.Input;
objParam.Value = mStartdate;

objParam = comm.Parameters.Add("@pEnddate",SqlDbType.DateTime );
objParam.Direction = ParameterDirection.Input;
objParam.Value = mEnddate;

objParam = comm.Parameters.Add("@pActive",SqlDbType.Bit);
objParam.Direction = ParameterDirection.Input;
objParam.Value = mActive;

try
{
comm.ExecuteNonQuery();
comm.Dispose();
return true;
}
catch
{
return false;
}

Nov 18 '05 #1
1 1196
you have to specify the size of varchar... if you dont specify its taken as
just a char
CREATE PROCEDURE spAddBlog
@pTitle varchar(100),
@pBody varchar(3000),
.......

also

objParam = comm.Parameters.Add("@pTitle",SqlDbType.VarChar, 100);
objParam.Direction = ParameterDirection.Input;
objParam.Value = mTitle;

objParam = comm.Parameters.Add("@pBody",SqlDbType.VarChar,300 0);
objParam.Direction = ParameterDirection.Input;
objParam.Value = mBody;
--

Regards,

Hermit Dave
(http://hdave.blogspot.com)
"NathanV" <Na*****@discussions.microsoft.com> wrote in message
news:86**********************************@microsof t.com...
I have created a class and stored procedure to insert records into a table. The insert works but only the first character of the "Title" and "Body"
fields are added. I have traced up to the point of executing the sproc and the string values are intact up to this point. Below is my code for the
SPROC and the insert method of the class:

.................................................. ...........................
............. Sproc:
.................................................. ...........................
............. CREATE PROCEDURE spAddBlog
@pTitle varchar,
@pBody varchar,
@pStartdate datetime,
@pEnddate datetime,
@pActive bit
AS
INSERT INTO blog
Values (@pTitle,@pBody,@pStartdate,@pEnddate,@pActive)
GO
.................................................. ...........................
............. Class:
.................................................. ...........................
............. mStartdate = DateTime.Now;
mEnddate = DateTime.Now;
mActive = 1;

/* Connection Object */
SqlConnection conn = new
SqlConnection(ConfigurationSettings.AppSetting["connString"]);
conn.Open();

/* Command Object */
SqlCommand comm = new SqlCommand();
comm.Connection = conn;
comm.CommandText = "spAddBlog";
comm.CommandType = CommandType.StoredProcedure;

SqlParameter objParam;

objParam = comm.Parameters.Add("@pTitle",SqlDbType.VarChar);
objParam.Direction = ParameterDirection.Input;
objParam.Value = mTitle;

objParam = comm.Parameters.Add("@pBody",SqlDbType.VarChar);
objParam.Direction = ParameterDirection.Input;
objParam.Value = mBody;

objParam = comm.Parameters.Add("@pStartdate",SqlDbType.DateTi me);
objParam.Direction = ParameterDirection.Input;
objParam.Value = mStartdate;

objParam = comm.Parameters.Add("@pEnddate",SqlDbType.DateTime );
objParam.Direction = ParameterDirection.Input;
objParam.Value = mEnddate;

objParam = comm.Parameters.Add("@pActive",SqlDbType.Bit);
objParam.Direction = ParameterDirection.Input;
objParam.Value = mActive;

try
{
comm.ExecuteNonQuery();
comm.Dispose();
return true;
}
catch
{
return false;
}

Nov 18 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

5 posts views Thread by Ritesh | last post: by
2 posts views Thread by Steve Kuekes | last post: by
7 posts views Thread by Andres Rormoser | last post: by
1 post views Thread by CARIGAR | last post: by
1 post views Thread by Mortomer39 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.