August 13, 2008 at 1:12 am
Hi guys
i've spent hours looking for the problem but to no avail. i've got a table Equipment with a couple of FK to Drawings & Project table. when adding new records via a front end app (asp) if i dont populate the DrawingID in the Equipment table this new record will not be created. the DrawingID in the Equipment table is set to Allow Nulls. i've even tried to delete the relationship between Equipment & Drawings table but it still does not work.
has anyone a clue where i should look for this problem?
regards
Jerry
August 13, 2008 at 2:26 am
Hello,
Do you get any related error message? What data access technology is the Front End Application using e.g.ADO.Net? Is there any possibility that the application is trying to write an empty string (or similar) instead of a Null? Can you include a code snippet, or is it too confidential?
Regards,
John Marsh
www.sql.lu
SQL Server Luxembourg User Group
August 13, 2008 at 2:41 am
hi thanks for responding.
my front end is on asp/vbscript and its a standard insert into routine. i've tried including and excluding the DrawingID field on the record form but it still will not perform an insert into sql server.
are there are any issues or options i need to check?
August 13, 2008 at 2:52 am
Hello,
You are right in thinking Nulls should be allowed in FK fields, which is why I am thinking that may be the Front End Application is trying to write (something like) an empty string.
Have you tried running a SQL Insert statement (with a Null for the FK) directly i.e. via SSMS (SQL Server Management Studio)? If so, does it work, or what error do you get? If it does work directly then you could run a profile on the SQL Server and capture exactly the Insert Statement that the Application is trying to execute.
I have vague memories of encountering this problem myself, but it's been a few years since I've used ASP/VBScript and my memory banks aren’t what they used to be 😉
Regards,
John Marsh
www.sql.lu
SQL Server Luxembourg User Group
August 13, 2008 at 3:34 am
i've tried a direct insert into from sql server, code below but rec'd this error;
===
Insert into dbo.Equipment (EquipmentNumber, ProjectID, DrawingID)
Values (T57777,18,NULL)
=====
error is "The name "T57777" is not permitted in this context. Valid expressions are constants, constant expressions, and (in some contexts) variables. Column names are not permitted."
EquipmentNumber is Varchar(50)
1 other point: i Excluded the DrawingID field from the front end app, so its completely excluded in the Insert into statement going into sql server... any suggestions?
August 13, 2008 at 3:55 am
Hello,
For the manual insert, the T57777 needs to be enclosed in single quotes i.e.
Insert into dbo.Equipment (EquipmentNumber, ProjectID, DrawingID)
Values ('T57777',18,NULL)
It is difficult to tell what is going wrong with the application's Insert statement without an error message and preferably the statement itself. If you can run a profile on the SQL Server it is possible to capture exactly the Insert statement that is being generated. If not then a couple of things to check - Does it explicitly define the list of columns to insert into? Is the list of values to insert definitely in the matching order with the list of fields? Are strings in single quotes?
Hope that helps.
Regards,
John Marsh
www.sql.lu
SQL Server Luxembourg User Group
August 13, 2008 at 4:55 am
Hi John
could you guide me thru how to run a profile on sql server 2005? this is way beyond my knowledge now so a step by step procedure would be great. thks in advance.
August 13, 2008 at 5:19 am
Hi Jerry,
I'm hoping your SQL Server is fairly quiet, otherwise you have to filter the results of the profile to find what you are looking for.
If you already have SSMS open, select Tools, SQL Server Profiler.
In the Profiler, Select New Trace, then chose to connect to your SQL Server. Give the Trace a name and keep the default Standard Template. I would recommend "Save to file" and then browse to a folder with plenty of free space. Give the file a name (this is where Profiler will store the results).
If you are ready to execute the insert from your application then click the "Run" button, swap to your application and execute the insert. Come back to Profiler and after a second or so press the stop button in the toolbar (a little red square).
In the Trace results you should see an Event-Class of SQL:BatchStarting. If you click on that, the lower pane will display the SQL that your application was executing. If the SQL Server is busy, there could be lots of records. If so, you can use the find option to search for the correct record e.g. via the Login that the application uses to access SQL.
Let me know if you have any questions.
Regards,
John
www.sql.lu
SQL Server Luxembourg User Group
August 13, 2008 at 8:00 am
hi john
thks for the procedure, you've been a great help.
i'll get back to you tomorrow (thurs) as its late here on wed night in australia.
regards
jerry
August 13, 2008 at 4:56 pm
Hi John
after a rest from this, i took another look at the problem i'm having. executing an insert into directly from sql server revealed my problem. there was a conflict in another trigger for the same table. amending this solved ALL of the issues i faced. thanks for your help.
would you run your eye over this code below, there is an error msg "Msg 102, Level 15, State 1, Procedure trgMaint1, Line 40. Incorrect syntax near 'END'. " i cant spot the error and am wondering if you could.
==
Declare @intEquipID int
Declare @strMaintDescription varchar(50)
Declare @intRecurr int
Declare @i int
Declare @intFrequency int
Declare @dteMaintStartDate datetime
Declare @MaintFlag numeric(18,0)
Select @intEquipID = (Select EquipID From Inserted)
Select @MaintFlag = (Select MaintFlag From Inserted)
Select @intFrequency = (Select Frequency From Inserted)
Select @intRecurr = (Select NoOfRecurrances From Inserted)
Select @dteMaintStartDate = (Select MaintStartDate From Inserted)
Select @strMaintDescription = (Select MaintDescription From Inserted)
Set @i = 0
If @MaintFlag =0
Begin
While (@i < @intRecurr)
Set @intFrequency = (@intFrequency * @i)
Set @dteMaintStartDate = DateAdd(Day, @intFrequency, @dteMaintStartDate)
Insert Into MaintenanceDetails (ScheduleDate, MaintDescription) VALUES (@dteMaintStartDate, @strMaintDescription)
If @i = (@intRecurr -1)
Begin
Update dbo.Equipment Set dbo.Equipment.MaintFlag=1 Where dbo.Equipment.EquipID=@intEquipID
End
END
==
August 14, 2008 at 12:28 am
hi john
got it sorted, found that there was a missing closing 'end'.
thks again for your help.
August 14, 2008 at 1:12 am
try this out
---------------------------------------
Declare @intEquipID int
Declare @strMaintDescription varchar(50)
Declare @intRecurr int
Declare @i int
Declare @intFrequency int
Declare @dteMaintStartDate datetime
Declare @MaintFlag numeric(18,0)
Select @intEquipID = (Select EquipID From Inserted)
Select @MaintFlag = (Select MaintFlag From Inserted)
Select @intFrequency = (Select Frequency From Inserted)
Select @intRecurr = (Select NoOfRecurrances From Inserted)
Select @dteMaintStartDate = (Select MaintStartDate From Inserted)
Select @strMaintDescription = (Select MaintDescription From Inserted)
Set @i = 0
If @MaintFlag =0
Begin
While (@i < @intRecurr)
begin
Set @intFrequency = (@intFrequency * @i)
Set @dteMaintStartDate = DateAdd(Day, @intFrequency, @dteMaintStartDate)
Insert Into MaintenanceDetails (ScheduleDate, MaintDescription) VALUES (@dteMaintStartDate, @strMaintDescription)
If @i = (@intRecurr -1)
Begin
Update dbo.Equipment Set dbo.Equipment.MaintFlag=1 Where dbo.Equipment.EquipID=@intEquipID
End
end
END
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
August 14, 2008 at 1:13 am
try thi out
------------------------------------------------
Declare @intEquipID int
Declare @strMaintDescription varchar(50)
Declare @intRecurr int
Declare @i int
Declare @intFrequency int
Declare @dteMaintStartDate datetime
Declare @MaintFlag numeric(18,0)
Select @intEquipID = (Select EquipID From Inserted)
Select @MaintFlag = (Select MaintFlag From Inserted)
Select @intFrequency = (Select Frequency From Inserted)
Select @intRecurr = (Select NoOfRecurrances From Inserted)
Select @dteMaintStartDate = (Select MaintStartDate From Inserted)
Select @strMaintDescription = (Select MaintDescription From Inserted)
Set @i = 0
If @MaintFlag =0
Begin
While (@i < @intRecurr)
begin
Set @intFrequency = (@intFrequency * @i)
Set @dteMaintStartDate = DateAdd(Day, @intFrequency, @dteMaintStartDate)
Insert Into MaintenanceDetails (ScheduleDate, MaintDescription) VALUES (@dteMaintStartDate, @strMaintDescription)
If @i = (@intRecurr -1)
Begin
Update dbo.Equipment Set dbo.Equipment.MaintFlag=1 Where dbo.Equipment.EquipID=@intEquipID
End
end
END
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
August 14, 2008 at 2:23 am
Hi Jerry,
Thanks for the update. I'm glad to hear your problems are solved now.
Regards,
John
www.sql.lu
SQL Server Luxembourg User Group
August 14, 2008 at 2:57 am
GLAD TO KNOW THAT YOUR PROBLEM HAS BEEN SOLVED BUT
MY NAME IS NOT JENNY
I AM BHUVNESH:)
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply