return value from sql server insert proc

[Eric Effer]

Hi

I am a newbie with vb.net. I am working with vb.net 2.0 and sql server 2005.
I am trying to get the return value from my insert stored proc.Does anyone
know how to do this?

thanks

E

[James Irvine]

"Eric Effer" <be****@gmail.comwrote in message
news:%2****************@TK2MSFTNGP04.phx.gbl...

Hi

I am a newbie with vb.net. I am working with vb.net 2.0 and sql server
2005.
I am trying to get the return value from my insert stored proc.Does anyone
know how to do this?

thanks

E

Just include an integer to receive the results of the call to the stored
proc. In C# it's like this:
int rowsInserted = cmdUpdateReminders.ExecuteNonQuery();

vb must be near identical syntax
the full C# listing:

public string InsertRemindersRecord(string p_eMailAddress, DateTime
p_requestedSendDateTime, string p_timeZone, string p_messageHeader, string
p_messageBody)
{
SqlConnection cq = new SqlConnection("xxx");
SqlCommand cmdUpdateReminders = new
SqlCommand("spInsertRemindersRecord", cq);
cmdUpdateReminders.CommandType = CommandType.StoredProcedure;
cq.Open();

cmdUpdateReminders.Parameters.Add("@eMailAddress",
SqlDbType.NVarChar, 99);
cmdUpdateReminders.Parameters["@eMailAddress"].Value =
p_eMailAddress;

cmdUpdateReminders.Parameters.Add("@requestedSendD ateTime",
SqlDbType.DateTime);
cmdUpdateReminders.Parameters["@requestedSendDateTime"].Value =
p_requestedSendDateTime;

cmdUpdateReminders.Parameters.Add("@timeZone", SqlDbType.NVarChar,
50);
cmdUpdateReminders.Parameters["@timeZone"].Value = p_timeZone;

cmdUpdateReminders.Parameters.Add("@messageHeader" ,
SqlDbType.NText);
cmdUpdateReminders.Parameters["@messageHeader"].Value =
p_messageHeader;

cmdUpdateReminders.Parameters.Add("@messageBody", SqlDbType.NText);
cmdUpdateReminders.Parameters["@messageBody"].Value = p_messageBody;

// execute the stored proc:
try
{
int rowsInserted = cmdUpdateReminders.ExecuteNonQuery();
_msgBack = rowsInserted + " record inserted";
cq.Close();
return _msgBack;
}
catch (Exception ex)
{
string _full_msgBack = ex.Message;
if (_full_msgBack.Substring(0, 24) == "Violation of PRIMARY
KEY")
_msgBack = "This record already exists";
else
_msgBack = _full_msgBack;

return _msgBack;
}
}