ADO.NET paremeters

Hi everyone. I have a small problem that I can not seem to resolve. I am
executing a stored procedure using the ExecuteReader method. However it is
failing. Here is my code in short. I debugged it using Sql profiler it
appears that my currentdatetime parameter is going to the next line and I am
not sure why, but I am positive that this is the reason it is failing. The
output is below the code.

SqlParameter physID = cmd.Parameters.Add("@phys_id", SqlDbType.Int);
SqlParameter lName = cmd.Parameters.Add("@lname",
SqlDbType.VarChar, 25);
SqlParameter fName = cmd.Parameters.Add("@fname",
SqlDbType.VarChar, 15);
SqlParameter mName = cmd.Parameters.Add("@mname",
SqlDbType.VarChar, 15);
SqlParameter suffixCode =
cmd.Parameters.Add("@suffix_code", SqlDbType.Char, 4);
SqlParameter legalNameInd =
cmd.Parameters.Add("@legal_name_ind", SqlDbType.Char, 1);
SqlParameter insertLogin =
cmd.Parameters.Add("@insert_login", SqlDbType.VarChar, 30);
SqlParameter currentDateTime =
cmd.Parameters.Add("@currentdatetime", SqlDbType.VarChar, 30);
SqlParameter rc = cmd.Parameters.Add("@rc",
SqlDbType.Int);

//Set the parameter values.
physID.Value = physician.PhysId;
lName.Value = physician.LastName;
fName.Value = physician.FirstName;
mName.Value = physician.MidName;
suffixCode.Value = physician.SuffixCode;
legalNameInd.Value = physician.LegalNameInd;
insertLogin.Value = currentUser.Username;
currentDateTime.Value = date;
rc.Value = 0;

rdr = cmd.ExecuteReader();
rdr.Close();

exec phys_names_isp1
@phys_id=34219,@lname='Test4',@fname='Test4',@mname='',@suffix_code=default,@legal_name_ind='Y',@insert_login='kareno_rmf',@currentdatetime='12/27/2005
3:53:43 PM',@rc=0

Marina
Tue Dec 27 15:10:58 CST 2005

2 thoughts:

1. The newline may be due to lack of spaces in the beginning of the call,
and the profiler is just trying to find a place to wrap. So it may not be a
true newline
2. You are declaring the currentdatetime parameter as a Varchar 30. Why?
This should be declared as a datetime parameter. What is happening right
now, is that ToString() is being called on your date object, and that is
being sent. If you are trying to put that value into a column declared as
datetime, then it is failing because it cannot parse what you are giving it.
Calling ToString on a datetime is just returning a default string
representation of the date object, which is not necessarily compatible with
what the database parser expects. Not to mention that if someone has any
sort of date format settings set, etc, that what this method returns will
vary.

"WXMCDBA" <WXMCDBA@discussions.microsoft.com> wrote in message
news:0F3E0675-2D87-4D42-B646-C942A2FB6152@microsoft.com...
> Hi everyone. I have a small problem that I can not seem to resolve. I am
> executing a stored procedure using the ExecuteReader method. However it is
> failing. Here is my code in short. I debugged it using Sql profiler it
> appears that my currentdatetime parameter is going to the next line and I
> am
> not sure why, but I am positive that this is the reason it is failing. The
> output is below the code.
>
> SqlParameter physID = cmd.Parameters.Add("@phys_id", SqlDbType.Int);
> SqlParameter lName = cmd.Parameters.Add("@lname",
> SqlDbType.VarChar, 25);
> SqlParameter fName = cmd.Parameters.Add("@fname",
> SqlDbType.VarChar, 15);
> SqlParameter mName = cmd.Parameters.Add("@mname",
> SqlDbType.VarChar, 15);
> SqlParameter suffixCode =
> cmd.Parameters.Add("@suffix_code", SqlDbType.Char, 4);
> SqlParameter legalNameInd =
> cmd.Parameters.Add("@legal_name_ind", SqlDbType.Char, 1);
> SqlParameter insertLogin =
> cmd.Parameters.Add("@insert_login", SqlDbType.VarChar, 30);
> SqlParameter currentDateTime =
> cmd.Parameters.Add("@currentdatetime", SqlDbType.VarChar, 30);
> SqlParameter rc = cmd.Parameters.Add("@rc",
> SqlDbType.Int);
>
> //Set the parameter values.
> physID.Value = physician.PhysId;
> lName.Value = physician.LastName;
> fName.Value = physician.FirstName;
> mName.Value = physician.MidName;
> suffixCode.Value = physician.SuffixCode;
> legalNameInd.Value = physician.LegalNameInd;
> insertLogin.Value = currentUser.Username;
> currentDateTime.Value = date;
> rc.Value = 0;
>
> rdr = cmd.ExecuteReader();
> rdr.Close();
>
> exec phys_names_isp1
> @phys_id=34219,@lname='Test4',@fname='Test4',@mname='',@suffix_code=default,@legal_name_ind='Y',@insert_login='kareno_rmf',@currentdatetime='12/27/2005
> 3:53:43 PM',@rc=0

KerryMoorman
Tue Dec 27 16:10:46 CST 2005

WXMCDBA,

Should the @currentdatetime parameter be SqlDbType.DateTime instead of
SqlDbType.VarChar?

Kerry Moorman


"WXMCDBA" wrote:

> Hi everyone. I have a small problem that I can not seem to resolve. I am
> executing a stored procedure using the ExecuteReader method. However it is
> failing. Here is my code in short. I debugged it using Sql profiler it
> appears that my currentdatetime parameter is going to the next line and I am
> not sure why, but I am positive that this is the reason it is failing. The
> output is below the code.
>
> SqlParameter physID = cmd.Parameters.Add("@phys_id", SqlDbType.Int);
> SqlParameter lName = cmd.Parameters.Add("@lname",
> SqlDbType.VarChar, 25);
> SqlParameter fName = cmd.Parameters.Add("@fname",
> SqlDbType.VarChar, 15);
> SqlParameter mName = cmd.Parameters.Add("@mname",
> SqlDbType.VarChar, 15);
> SqlParameter suffixCode =
> cmd.Parameters.Add("@suffix_code", SqlDbType.Char, 4);
> SqlParameter legalNameInd =
> cmd.Parameters.Add("@legal_name_ind", SqlDbType.Char, 1);
> SqlParameter insertLogin =
> cmd.Parameters.Add("@insert_login", SqlDbType.VarChar, 30);
> SqlParameter currentDateTime =
> cmd.Parameters.Add("@currentdatetime", SqlDbType.VarChar, 30);
> SqlParameter rc = cmd.Parameters.Add("@rc",
> SqlDbType.Int);
>
> //Set the parameter values.
> physID.Value = physician.PhysId;
> lName.Value = physician.LastName;
> fName.Value = physician.FirstName;
> mName.Value = physician.MidName;
> suffixCode.Value = physician.SuffixCode;
> legalNameInd.Value = physician.LegalNameInd;
> insertLogin.Value = currentUser.Username;
> currentDateTime.Value = date;
> rc.Value = 0;
>
> rdr = cmd.ExecuteReader();
> rdr.Close();
>
> exec phys_names_isp1
> @phys_id=34219,@lname='Test4',@fname='Test4',@mname='',@suffix_code=default,@legal_name_ind='Y',@insert_login='kareno_rmf',@currentdatetime='12/27/2005
> 3:53:43 PM',@rc=0

WXMCDBA
Wed Dec 28 08:21:03 CST 2005

Thank you for your respsonse.

William Xifaras, MCDBA

"Kerry Moorman" wrote:

> WXMCDBA,
>
> Should the @currentdatetime parameter be SqlDbType.DateTime instead of
> SqlDbType.VarChar?
>
> Kerry Moorman
>
>
> "WXMCDBA" wrote:
>
> > Hi everyone. I have a small problem that I can not seem to resolve. I am
> > executing a stored procedure using the ExecuteReader method. However it is
> > failing. Here is my code in short. I debugged it using Sql profiler it
> > appears that my currentdatetime parameter is going to the next line and I am
> > not sure why, but I am positive that this is the reason it is failing. The
> > output is below the code.
> >
> > SqlParameter physID = cmd.Parameters.Add("@phys_id", SqlDbType.Int);
> > SqlParameter lName = cmd.Parameters.Add("@lname",
> > SqlDbType.VarChar, 25);
> > SqlParameter fName = cmd.Parameters.Add("@fname",
> > SqlDbType.VarChar, 15);
> > SqlParameter mName = cmd.Parameters.Add("@mname",
> > SqlDbType.VarChar, 15);
> > SqlParameter suffixCode =
> > cmd.Parameters.Add("@suffix_code", SqlDbType.Char, 4);
> > SqlParameter legalNameInd =
> > cmd.Parameters.Add("@legal_name_ind", SqlDbType.Char, 1);
> > SqlParameter insertLogin =
> > cmd.Parameters.Add("@insert_login", SqlDbType.VarChar, 30);
> > SqlParameter currentDateTime =
> > cmd.Parameters.Add("@currentdatetime", SqlDbType.VarChar, 30);
> > SqlParameter rc = cmd.Parameters.Add("@rc",
> > SqlDbType.Int);
> >
> > //Set the parameter values.
> > physID.Value = physician.PhysId;
> > lName.Value = physician.LastName;
> > fName.Value = physician.FirstName;
> > mName.Value = physician.MidName;
> > suffixCode.Value = physician.SuffixCode;
> > legalNameInd.Value = physician.LegalNameInd;
> > insertLogin.Value = currentUser.Username;
> > currentDateTime.Value = date;
> > rc.Value = 0;
> >
> > rdr = cmd.ExecuteReader();
> > rdr.Close();
> >
> > exec phys_names_isp1
> > @phys_id=34219,@lname='Test4',@fname='Test4',@mname='',@suffix_code=default,@legal_name_ind='Y',@insert_login='kareno_rmf',@currentdatetime='12/27/2005
> > 3:53:43 PM',@rc=0

WXMCDBA
Wed Dec 28 08:21:03 CST 2005

Thank you for your response.

William Xifaras, MCDBA

"Marina" wrote:

> 2 thoughts:
>
> 1. The newline may be due to lack of spaces in the beginning of the call,
> and the profiler is just trying to find a place to wrap. So it may not be a
> true newline
> 2. You are declaring the currentdatetime parameter as a Varchar 30. Why?
> This should be declared as a datetime parameter. What is happening right
> now, is that ToString() is being called on your date object, and that is
> being sent. If you are trying to put that value into a column declared as
> datetime, then it is failing because it cannot parse what you are giving it.
> Calling ToString on a datetime is just returning a default string
> representation of the date object, which is not necessarily compatible with
> what the database parser expects. Not to mention that if someone has any
> sort of date format settings set, etc, that what this method returns will
> vary.
>
> "WXMCDBA" <WXMCDBA@discussions.microsoft.com> wrote in message
> news:0F3E0675-2D87-4D42-B646-C942A2FB6152@microsoft.com...
> > Hi everyone. I have a small problem that I can not seem to resolve. I am
> > executing a stored procedure using the ExecuteReader method. However it is
> > failing. Here is my code in short. I debugged it using Sql profiler it
> > appears that my currentdatetime parameter is going to the next line and I
> > am
> > not sure why, but I am positive that this is the reason it is failing. The
> > output is below the code.
> >
> > SqlParameter physID = cmd.Parameters.Add("@phys_id", SqlDbType.Int);
> > SqlParameter lName = cmd.Parameters.Add("@lname",
> > SqlDbType.VarChar, 25);
> > SqlParameter fName = cmd.Parameters.Add("@fname",
> > SqlDbType.VarChar, 15);
> > SqlParameter mName = cmd.Parameters.Add("@mname",
> > SqlDbType.VarChar, 15);
> > SqlParameter suffixCode =
> > cmd.Parameters.Add("@suffix_code", SqlDbType.Char, 4);
> > SqlParameter legalNameInd =
> > cmd.Parameters.Add("@legal_name_ind", SqlDbType.Char, 1);
> > SqlParameter insertLogin =
> > cmd.Parameters.Add("@insert_login", SqlDbType.VarChar, 30);
> > SqlParameter currentDateTime =
> > cmd.Parameters.Add("@currentdatetime", SqlDbType.VarChar, 30);
> > SqlParameter rc = cmd.Parameters.Add("@rc",
> > SqlDbType.Int);
> >
> > //Set the parameter values.
> > physID.Value = physician.PhysId;
> > lName.Value = physician.LastName;
> > fName.Value = physician.FirstName;
> > mName.Value = physician.MidName;
> > suffixCode.Value = physician.SuffixCode;
> > legalNameInd.Value = physician.LegalNameInd;
> > insertLogin.Value = currentUser.Username;
> > currentDateTime.Value = date;
> > rc.Value = 0;
> >
> > rdr = cmd.ExecuteReader();
> > rdr.Close();
> >
> > exec phys_names_isp1
> > @phys_id=34219,@lname='Test4',@fname='Test4',@mname='',@suffix_code=default,@legal_name_ind='Y',@insert_login='kareno_rmf',@currentdatetime='12/27/2005
> > 3:53:43 PM',@rc=0
>
>
>

WXMCDBA
Wed Dec 28 08:40:02 CST 2005

Marina I orginally had the currentdatetime parameter as a datetime and this
is what the output looked like. If you look at the output now you will see
two single quotes and a new line. Thanks again.

William Xifaras, MCDBA

exec phys_names_isp1
@phys_id=34219,@lname='Test4',@fname='Test4',@mname='',@suffix_code=default,@legal_name_ind='Y',@insert_login='kareno_rmf',@currentdatetime=''2005-12-28
09:30:09:080'',@rc=0
go

"Marina" wrote:

> 2 thoughts:
>
> 1. The newline may be due to lack of spaces in the beginning of the call,
> and the profiler is just trying to find a place to wrap. So it may not be a
> true newline
> 2. You are declaring the currentdatetime parameter as a Varchar 30. Why?
> This should be declared as a datetime parameter. What is happening right
> now, is that ToString() is being called on your date object, and that is
> being sent. If you are trying to put that value into a column declared as
> datetime, then it is failing because it cannot parse what you are giving it.
> Calling ToString on a datetime is just returning a default string
> representation of the date object, which is not necessarily compatible with
> what the database parser expects. Not to mention that if someone has any
> sort of date format settings set, etc, that what this method returns will
> vary.
>
> "WXMCDBA" <WXMCDBA@discussions.microsoft.com> wrote in message
> news:0F3E0675-2D87-4D42-B646-C942A2FB6152@microsoft.com...
> > Hi everyone. I have a small problem that I can not seem to resolve. I am
> > executing a stored procedure using the ExecuteReader method. However it is
> > failing. Here is my code in short. I debugged it using Sql profiler it
> > appears that my currentdatetime parameter is going to the next line and I
> > am
> > not sure why, but I am positive that this is the reason it is failing. The
> > output is below the code.
> >
> > SqlParameter physID = cmd.Parameters.Add("@phys_id", SqlDbType.Int);
> > SqlParameter lName = cmd.Parameters.Add("@lname",
> > SqlDbType.VarChar, 25);
> > SqlParameter fName = cmd.Parameters.Add("@fname",
> > SqlDbType.VarChar, 15);
> > SqlParameter mName = cmd.Parameters.Add("@mname",
> > SqlDbType.VarChar, 15);
> > SqlParameter suffixCode =
> > cmd.Parameters.Add("@suffix_code", SqlDbType.Char, 4);
> > SqlParameter legalNameInd =
> > cmd.Parameters.Add("@legal_name_ind", SqlDbType.Char, 1);
> > SqlParameter insertLogin =
> > cmd.Parameters.Add("@insert_login", SqlDbType.VarChar, 30);
> > SqlParameter currentDateTime =
> > cmd.Parameters.Add("@currentdatetime", SqlDbType.VarChar, 30);
> > SqlParameter rc = cmd.Parameters.Add("@rc",
> > SqlDbType.Int);
> >
> > //Set the parameter values.
> > physID.Value = physician.PhysId;
> > lName.Value = physician.LastName;
> > fName.Value = physician.FirstName;
> > mName.Value = physician.MidName;
> > suffixCode.Value = physician.SuffixCode;
> > legalNameInd.Value = physician.LegalNameInd;
> > insertLogin.Value = currentUser.Username;
> > currentDateTime.Value = date;
> > rc.Value = 0;
> >
> > rdr = cmd.ExecuteReader();
> > rdr.Close();
> >
> > exec phys_names_isp1
> > @phys_id=34219,@lname='Test4',@fname='Test4',@mname='',@suffix_code=default,@legal_name_ind='Y',@insert_login='kareno_rmf',@currentdatetime='12/27/2005
> > 3:53:43 PM',@rc=0
>
>
>

WXMCDBA
Wed Dec 28 13:15:02 CST 2005

My code is working. I feel so foolish. I am using the new System.Transactions
namespace and I was missing a commit in my code so the code was rolling
back...ughhh
I guess these things happen :)

"Kerry Moorman" wrote:

> WXMCDBA,
>
> Should the @currentdatetime parameter be SqlDbType.DateTime instead of
> SqlDbType.VarChar?
>
> Kerry Moorman
>
>
> "WXMCDBA" wrote:
>
> > Hi everyone. I have a small problem that I can not seem to resolve. I am
> > executing a stored procedure using the ExecuteReader method. However it is
> > failing. Here is my code in short. I debugged it using Sql profiler it
> > appears that my currentdatetime parameter is going to the next line and I am
> > not sure why, but I am positive that this is the reason it is failing. The
> > output is below the code.
> >
> > SqlParameter physID = cmd.Parameters.Add("@phys_id", SqlDbType.Int);
> > SqlParameter lName = cmd.Parameters.Add("@lname",
> > SqlDbType.VarChar, 25);
> > SqlParameter fName = cmd.Parameters.Add("@fname",
> > SqlDbType.VarChar, 15);
> > SqlParameter mName = cmd.Parameters.Add("@mname",
> > SqlDbType.VarChar, 15);
> > SqlParameter suffixCode =
> > cmd.Parameters.Add("@suffix_code", SqlDbType.Char, 4);
> > SqlParameter legalNameInd =
> > cmd.Parameters.Add("@legal_name_ind", SqlDbType.Char, 1);
> > SqlParameter insertLogin =
> > cmd.Parameters.Add("@insert_login", SqlDbType.VarChar, 30);
> > SqlParameter currentDateTime =
> > cmd.Parameters.Add("@currentdatetime", SqlDbType.VarChar, 30);
> > SqlParameter rc = cmd.Parameters.Add("@rc",
> > SqlDbType.Int);
> >
> > //Set the parameter values.
> > physID.Value = physician.PhysId;
> > lName.Value = physician.LastName;
> > fName.Value = physician.FirstName;
> > mName.Value = physician.MidName;
> > suffixCode.Value = physician.SuffixCode;
> > legalNameInd.Value = physician.LegalNameInd;
> > insertLogin.Value = currentUser.Username;
> > currentDateTime.Value = date;
> > rc.Value = 0;
> >
> > rdr = cmd.ExecuteReader();
> > rdr.Close();
> >
> > exec phys_names_isp1
> > @phys_id=34219,@lname='Test4',@fname='Test4',@mname='',@suffix_code=default,@legal_name_ind='Y',@insert_login='kareno_rmf',@currentdatetime='12/27/2005
> > 3:53:43 PM',@rc=0

WXMCDBA
Wed Dec 28 13:15:03 CST 2005

My code is working. I feel so foolish. I am using the new System.Transactions
namespace and I was missing a commit in my code so the code was rolling
back...ughhh
I guess these things happen :)

"Marina" wrote:

> 2 thoughts:
>
> 1. The newline may be due to lack of spaces in the beginning of the call,
> and the profiler is just trying to find a place to wrap. So it may not be a
> true newline
> 2. You are declaring the currentdatetime parameter as a Varchar 30. Why?
> This should be declared as a datetime parameter. What is happening right
> now, is that ToString() is being called on your date object, and that is
> being sent. If you are trying to put that value into a column declared as
> datetime, then it is failing because it cannot parse what you are giving it.
> Calling ToString on a datetime is just returning a default string
> representation of the date object, which is not necessarily compatible with
> what the database parser expects. Not to mention that if someone has any
> sort of date format settings set, etc, that what this method returns will
> vary.
>
> "WXMCDBA" <WXMCDBA@discussions.microsoft.com> wrote in message
> news:0F3E0675-2D87-4D42-B646-C942A2FB6152@microsoft.com...
> > Hi everyone. I have a small problem that I can not seem to resolve. I am
> > executing a stored procedure using the ExecuteReader method. However it is
> > failing. Here is my code in short. I debugged it using Sql profiler it
> > appears that my currentdatetime parameter is going to the next line and I
> > am
> > not sure why, but I am positive that this is the reason it is failing. The
> > output is below the code.
> >
> > SqlParameter physID = cmd.Parameters.Add("@phys_id", SqlDbType.Int);
> > SqlParameter lName = cmd.Parameters.Add("@lname",
> > SqlDbType.VarChar, 25);
> > SqlParameter fName = cmd.Parameters.Add("@fname",
> > SqlDbType.VarChar, 15);
> > SqlParameter mName = cmd.Parameters.Add("@mname",
> > SqlDbType.VarChar, 15);
> > SqlParameter suffixCode =
> > cmd.Parameters.Add("@suffix_code", SqlDbType.Char, 4);
> > SqlParameter legalNameInd =
> > cmd.Parameters.Add("@legal_name_ind", SqlDbType.Char, 1);
> > SqlParameter insertLogin =
> > cmd.Parameters.Add("@insert_login", SqlDbType.VarChar, 30);
> > SqlParameter currentDateTime =
> > cmd.Parameters.Add("@currentdatetime", SqlDbType.VarChar, 30);
> > SqlParameter rc = cmd.Parameters.Add("@rc",
> > SqlDbType.Int);
> >
> > //Set the parameter values.
> > physID.Value = physician.PhysId;
> > lName.Value = physician.LastName;
> > fName.Value = physician.FirstName;
> > mName.Value = physician.MidName;
> > suffixCode.Value = physician.SuffixCode;
> > legalNameInd.Value = physician.LegalNameInd;
> > insertLogin.Value = currentUser.Username;
> > currentDateTime.Value = date;
> > rc.Value = 0;
> >
> > rdr = cmd.ExecuteReader();
> > rdr.Close();
> >
> > exec phys_names_isp1
> > @phys_id=34219,@lname='Test4',@fname='Test4',@mname='',@suffix_code=default,@legal_name_ind='Y',@insert_login='kareno_rmf',@currentdatetime='12/27/2005
> > 3:53:43 PM',@rc=0
>
>
>