September 6, 2008 at 1:23 am
Iam writing the following procedure for inserting phone call details into sqlserver.Iam using c# language on aspnet2.0 platform. 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
September 6, 2008 at 6:16 am
Hello,
I am assuming that no .Net exceptions are being thrown? If there are any, then what errors are you getting?
If .Net thinks everything is fine, than here are a couple more questions that might help you:-
1) Does the procedure insert data okay if you execute it directly in a Query window (of SQL Server Management Studio)?
2) Are there any triggers on the Table?
3) Can you Profile the SQL Server to capture exactly the statement that is being executed by your application, and then manually execute it in a Query Window?
Regards,
John Marsh
www.sql.lu
SQL Server Luxembourg User Group
September 7, 2008 at 11:44 am
Well If it is not inserting any rows, and no errors are being returned, then it seems to me that it is going into the Update statement.
Manually run the stored procedure with the entered values using debug statements.
but one criticism in bold Below
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'
-- This is a poor performing way to impliment this check. you have to select all the rows in that table? is your code expecting this dataset? if so put the statement at the bottom of the code.
-- select * from PhoneCalls where Extension = @Extension and CallDate = @CallDate and CallTime = @CallTime
-- IF @@rowcount = 0
-- This is a better way to check if there are no rows then perform insert.
if not exists (select * from PhoneCalls where Extension = @Extension and CallDate = @CallDate and CallTime = @CallTime)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
September 7, 2008 at 9:33 pm
thanks for the replies.
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'.
If we manually delete double quotes and place single quotes for calldate and time it is inserting in sqlquery window.How to make this run by sqlserver automatically.
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
September 7, 2008 at 10:27 pm
thanks for all of your 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
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply