Iam writing the following procedure for inserting phone call details into sqlserver.Iam using c# language on aspnet2.0 platform and sqlserver as backend. In the debugging the code everything is fine and showing that record has inserted into procedure and cmd.ExecuteNonqQuery also returning value 1 each time. While accessing sqlserver phonedetails table it is not showing any records which are inserted.previosly this application was running fine and now also procedure running fine.dont know what the problem is.Here is the procedure...help me CREATE PROCEDURE [dbo].[Ph_Insert] @Extension char(10), @CallDate datetime, @CallTime Datetime, @Phone char(20), @Duration smallmoney, @Trunk int, @calls int, @CallType char(20), @cost smallmoney AS declare @type char(1) IF @CallType = 'Inc Call' SET @type = 'I' ELSE IF @CallType = 'Missed Call' SET @type = 'M' ELSE SET @type = 'O' select * from PhoneCalls where Extension = @Extension and CallDate = @CallDate and CallTime = @CallTime IF @@rowcount = 0 BEGIN INSERT INTO PhoneCalls ( Extension, CallDate, CallTime, Phone, Duration, Trunk, calls, CallType, Flag, InsertedOn, DtandTime ) VALUES ( @Extension, @CallDate, @CallTime, @Phone, @Duration, @Trunk, @calls, @cost, @CallType, @type, Getdate(), convert(char(11),@CallDate,106) + ' ' + convert(char(10),@CallTime,108) ) END ELSE BEGIN UPDATE PhoneCalls SET Extension = @Extension, CallDate = @CallDate, CallTime = @CallTime, Phone = @Phone, Duration = @Duration, Trunk = @Trunk, calls = @calls, cost = @cost, CallType = @CallType, Flag = @type, UpdatedOn = GetDate(), DtandTime = convert(char(11),@CallDate,106) + ' ' + convert(char(10),@CallTime,108) WHERE Extension = @Extension and CallDate = @CallDate and CallTime = @CallTime END
A common issue with your stored procedure is it is multi statement and no eror handling is maintained. ALso keep SET NOCOUNT ON as the first statement so that you will not be creating unnecessary roundtrips. when you do these changes, you may be able to view the problem. To answer your second question, there is no way on inserting a row against the existing primary keys unique keys foreign keys etc. Either you need to delete them or diable them inorder to make a forcefull insert.
Thanks preethviraj for welcoming.I havent used stored procedures more.Now i have to use in my project, could you please tell me sample error handling of stored procedure if possible.Iam trying the suggestion u have given meanwhile
Can you please post the code you use to access the table? Also, error handling differs depending on which SQL Server version you use. In SQL Server 2005 you can use a TRY-CATCH construct similar to C#, in SQL Server 2000 have a look at @@ERROR.
here is the sql profiler trace exec Phone_Insert @Extension='901 ', @CallDate=''2008-02-09 00:00:00:000'', @Calltime=''1900-01-01 16:25:28:000'', @Phone='9869313128', @Duration=$0.1000, @Trunk=702, @calls=1, @cost=$0.8500, @CallType='Local' if we execute in the sqlserver query window it is showing error as Incorrect syntax near '2008'. even in my dotnet coding if i substring also iam getting same trace as calldate ''2008-02-09 00:00:00:000'' using the dotnet coding iam importing .csv file of phone calls history and inserting using phone_insert stored procedure string strConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + System.IO.Path.GetDirectoryName(FileName) + ";" + "Extended Properties="Text;HDR=NO;FMT=Delimited""; System.Data.OleDb.OleDbConnection conn = new System.Data.OleDb.OleDbConnection(strConnectionString); System.Data.OleDb.OleDbCommand cmd = new System.Data.OleDb.OleDbCommand("SELECT * FROM " +System.IO.Path.GetFileName(FileName), conn); System.Data.OleDb.OleDbDataReader dr; conn.Open(); dr = cmd.ExecuteReader(CommandBehavior.CloseConnection); SqlConnection Sconn = new SqlConnection("data source=192.168.1.12; initial catalog=website; user id=sa; password=password"); SqlCommand Scmd = new SqlCommand("Phone_Insert", Sconn); //in while loop Extension = dr[11].ToString().Trim(); CallDate = dr[21].ToString().Trim(); CallDate = CallDate.Substring(0, 20); help me
thanks for the support, i got the solution. actually in the import file list the date format is the conflict.It is going to update statement and updating 09-feb-2008 instead of inserting actuall 02-sep-2008.I have been checking 02-sep-2008 records only till i got the solution. thanks once again. ramakrishna
hi preethi ...cau provide me notes of MCDBA 2005 online certificati0on ..i will be greatful to you for this..... my id : bhuvnesh.dogra@gmail.com
Hi, I am clueless about your question. DId I mention anything about MCDBA or 2005 in my reply? Is there any relationship for that to this topic? If you want to do a Certification (BTW, MCDBA is no more available. The new certification is called MCITP) The best method is studying. I have done my certificates almost an year ago using the books available (and books online). I can't share them online. What do you mean by notes? The notes I take are useful for my job and not limited to the certification. I write them in note books (not computers!) I am not is a position to share them. If you are asking me to provide the questions, I ma sorry. We should not share the questions, you should not relay on questions to take an exam. Hope this helps!