By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,962 Members | 1,997 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,962 IT Pros & Developers. It's quick & easy.

return value from sql server insert proc

P: n/a
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


Jan 13 '07 #1
Share this Question
Share on Google+
1 Reply


P: n/a

"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;
}
}
Jan 14 '07 #2

This discussion thread is closed

Replies have been disabled for this discussion.