SqlParameter Fails With SqlDbType

[SBA-CDeCinko]

The following function works fine.

public int AddRecord(String MsgTo, String MsgFrom, String MsgCC, String MsgBC, DateTime DateSent)
		{
			SqlParameter[] sqlParams = new SqlParameter[] {
			new SqlParameter("@MsgTo", MsgTo),
			new SqlParameter("@MsgFrom", MsgFrom),
			new SqlParameter("@MsgCC", MsgCC),
			new SqlParameter("@MsgBC", MsgBC),
			new SqlParameter("@DateSent", DateSent),
			};

			return logContactUsMessage.insertMessage(sqlParams);
		}

However, when I try and add the dbtype and length, VS2010 says all is ok and compiles, but the code won't run in the browser. I also cannot find any web examples using this format, even though autocomplete tells me it is correct and valid.

public int AddRecord(String MsgTo, String MsgFrom, String MsgCC, String MsgBC, DateTime DateSent)
		{
			SqlParameter[] sqlParams = new SqlParameter[] {
			new SqlParameter("@MsgTo", SqlDbType.VarChar, 250, "MsgTo"),
			new SqlParameter("@MsgFrom", SqlDbType.VarChar, 250, "MsgFrom"),
			new SqlParameter("@MsgCC", SqlDbType.VarChar, 250, "MsgCC"),
			new SqlParameter("@MsgBC", SqlDbType.VarChar, 250, "MsgBC"),
			new SqlParameter("@DateSent", SqlDbType.DateTime, 8, "DateSent"),
			};

			return logContactUsMessage.insertMessage(sqlParams);
		}

[Momerath]

In your 2nd code example, you realize that you are setting each of those parameters to the literal strings you are supplying, not to values stored in variables of those names? While the first 4 would be fine (they are VarChar), the last one would not as "DateSent" can't be converted into a datetime.

[SBA-CDeCinko]

That's how I've seen if shown in some examples found recently. I've tried it without the quotes and it still fails.

[Momerath]

How does it fail? What is the Exception raised? What's the inner Exception?

[SBA-CDeCinko]

Error: The parameterized query '(@MsgTo varchar(250),@MsgFrom nvarchar(19),@MsgCC nvarchar(19),@Ms' expects the parameter '@MsgTo', which was not supplied.

[Momerath]

Is the error happening in the code you displayed? If not, lets see the code (and query).

[SBA-CDeCinko]

The code is shown above.

The query is

public static int insertMessage(params SqlParameter[] arrParam)
		{
			string sqlInsertCommand =
				"INSERT INTO [emailLog] ([MsgTo], [MsgFrom], [MsgCC], [MsgBC], [DateSent], [IPAddress], [UserAgent], [Subject], [Body], [Website], [FormApp]) VALUES (@MsgTo, @MsgFrom, @MsgCC, @MsgBC, @DateSent, @IPAddress, @userAgent, @Subject, @Body, @Website, @FormApp)";

			SqlConnection con = new SqlConnection(ConnectionString);
			SqlCommand cmd = new SqlCommand(sqlInsertCommand, con);
			cmd.CommandType = CommandType.Text;

			if (con.State == ConnectionState.Closed || con.State == ConnectionState.Broken)
				con.Open();

			try
			{
				if (arrParam != null)
				{
					foreach (SqlParameter param in arrParam)
						cmd.Parameters.Add(param);
				}

				int rowsAffected = cmd.ExecuteNonQuery();
				return rowsAffected;

			}
			catch (Exception ex)
			{
				throw new Exception("Error: " + ex.Message);
			}
			finally
			{
				cmd.Dispose();
				con.Close();
			}
		}

[Momerath]

Is it failing in line 21 of the above code? If so, set a breakpoint at that line and take a look at your arrParam list. If it's not, which line exactly is it failing at?

Also, why are some of the parameters listed as nvarchar and you are typing them as SqlDbType.VarChar?