I am in process of building a website where the user can upload files and then those files are loaded in to a sql server database. I am using some sprocs to scrub the data and then insert them into the production database.. And in my sprpc before and after updating or inserting a record or scrubing... i am returning the count by raising an error. or returning the rownumber where the error occured.. is there any way i can get the raise error part or whatever error i get while scrubing the data and relay it back to the user in a user freindly way or in a text file... or the best thing is can i open smalll window where i can show them what processing is goin on and alert them if there are any errors...
Any help will be appreciated.
Regards
Karen
Take a look forSqlException.Errors. Any SqlError object has the number of error and another informations.
PS.: To another databases, take a look forOleDbException.Errors.
|||Hi Karenros,
Based on my understanding, you want to use Raiserror to generate an error message and pass it to the client user. Client user may get an alerting message or write it to a text file when getting the error message. If I've misunderstood you ,please feel free to tell me, thanks.
You can put your sqlcommand in a try block and in your catch block, write sqlconnection.errors.message to a text file. Please remember do not assign the severity value of your error message more than 19, or else it maybe cause terminate your connection. Sample code is like the following:
try { con.Open(); cmd.ExecuteNonQuery(); }catch (SqlException ex) {using(StreamWriter sw=new StreamWriter("your text log file path here")) {foreach (SqlError errin ex.Errors) sw.WriteLine(err.Message+"\n"); } }Hope my suggestion helps|||
Chen,
Thanks for your answer. yeah and thats exactly that i wanted to do... so that user would know if the import process was successful or not...
I have tried using sqlexception before with no luck.. may be i didnt import the right header files in order for that work and i have also seen on msdn that we need a sqlinfomessage class or something like to do it.. Pls correct me if i am wrong...
anyways i am gonna give it a try and will let you know...
Regards
Karen
|||Below is an example of how you can use the InfoMessage event handler:
First you'll have to create an event handler for this event like below:
con.Open();
con.InfoMessage +=new SqlInfoMessageEventHandler(con_InfoMessage);// here i've registered for the event
... set up the command object
cmd.NotificationAutoEnlist =true;
cmd.ExecuteNonQuery();
Then you can go on and write any code you want in that event handler method.
private void con_InfoMessage(object sender, SqlInfoMessageEventArgs e)
{
// your file writing code goes here. The eventArgs e holds errors, messages, source etc.
// you can just use e.ToString() and everything is there for you.
}
Hope this will help.
|||Hi Karenros,
I've tested the sqlexception code on my local machine and it does work fine. So, maybe you have made some mistakes somewhere else.
However, I think you can also trydhimant 's solution. That's really a good method to solve your problem. thanks
utpal_nandi: