ADO.NET paremeters

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
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,

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

Kerry Moorman
 
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
 
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 :)
 
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 :)
 
Back
Top