Friday, March 30, 2012
inserting a new record
Inserting a document (PowerPoint, Word, PDF) into a report (SSRS 2
Which is the best way to insert a document (Word, PowerPoint, PDF) into a
report, which is also rendered with the masn report?
Writing a custom report item? Rendering as image?
But how to do a page break?
Is there a commercial solution available?
I'm using SSRS 2005.
Thanks
EricHi Eric,
Welcome to the MSDN newsgroup.
As for the displaying rich binary document in SQL server 2005reporting
service report, do you mean HTML format report? If so, I think you can
consider using the Html <iframe> element to embed the binary document
(powerpoint, word, pdf....)... Of course, you may need to create a custom
report item which can render out the html <iframe> element. For normal web
page, we can embed an <iframe> like below:
=========================<iframe id="frmPPT" runat="server" src="http://pics.10026.com/?src=small.ppt" width="100%"
height="500" >
======================
Hope this helps.
Regards,
Steven Cheng
Microsoft Online Support
Get Secure! www.microsoft.com/security
(This posting is provided "AS IS", with no warranties, and confers no
rights.)|||Thanks Steven,
But the report should also be rendered to PDF, not only HTML.
After the SQL2005 documentation as custom report item only the type 'image'
is supported, this means that i can't render html. Or do you mean a new item
based on the textbox (overloaded)?
Is there no other solution?
Is MS not working on a Word render extension?
Thanks
Eric
"Steven Cheng[MSFT]" wrote:
> Hi Eric,
> Welcome to the MSDN newsgroup.
> As for the displaying rich binary document in SQL server 2005reporting
> service report, do you mean HTML format report? If so, I think you can
> consider using the Html <iframe> element to embed the binary document
> (powerpoint, word, pdf....)... Of course, you may need to create a custom
> report item which can render out the html <iframe> element. For normal web
> page, we can embed an <iframe> like below:
> =========================> <iframe id="frmPPT" runat="server" src="http://pics.10026.com/?src=small.ppt" width="100%"
> height="500" >
> ======================> Hope this helps.
> Regards,
> Steven Cheng
> Microsoft Online Support
> Get Secure! www.microsoft.com/security
> (This posting is provided "AS IS", with no warranties, and confers no
> rights.)
>
>
>
>|||Thanks for your response.
For other format (PDf, ...) since they're binary format, I'm afraid there
is no support in the reporting service built-in components. This will
require the convertion between different format binary documents. Also, the
RS 2005 used to plan shipping a RTF/WORD render extension, however, it is
cancelled and I think one of the reason maybe there are already many 3rd
party convert tools for converting different format binary documents (like
word, pdf, chm, html...).
Regards,
Steven Cheng
Microsoft Online Support
Get Secure! www.microsoft.com/security
(This posting is provided "AS IS", with no warranties, and confers no
rights.)|||Hi Steven,
I do not fully agree with you. First there are not many RDL to Word, PDF or
HTML, ... converter available. I know only SoftArtisan, which isn't a fully
RDL to Word converter.
Second many of our customer in the financial industry want to have more
flexible reporting. This means they want to include "dynamic" informations
into the report which are not based one databases, ex. a word document with
personal informations for one client.
What's the meaning of a reporting server if you have at the end 2 documents?
For that we need a fully supported word renderer, or better a word report
item.
Are there more RDL to word converter available?
Thanks
Eric|||Thanks for your respones Eric,
Yes, so far there is no RDL to word render extension, as our dev team also
mentioned it's still a planed feature in future release. I think this is
also somewhat due to the open of new OFFICE XML format, the new word
rendering extension may mainly target the xml format which will be more
portable. BTW, the converter I mentioned earlier means some 3rd party tool
which can convert pdf, html(exiting file) to word , image or other format
though they not be directly plugged in SSRS.
Regards,
Steven Cheng
Microsoft Online Support
Get Secure! www.microsoft.com/security
(This posting is provided "AS IS", with no warranties, and confers no
rights.)
Inserting a document (PowerPoint, Word, PDF) into a report (SSRS 2
Which is the best way to insert a document into a report, which is also
rendered with the report?
Writing a custom report item? Rendering as image?
But how to do a page break?
Is there a commercial solution available?
Thanks
EricHi,
Not sure about my earlier post. Posting it again.
I feel the best way is to provide a link on the report (using action: jump
to URL) and when clicked it should open the attachments. If you want to
render it with the report, then render it as image but store it as external.
Your last ques.. How to do a page break ? with the image or with records ?
Not very sure about the question. Need to explain.
Amarnath
"Eric" wrote:
> Hi,
> Which is the best way to insert a document into a report, which is also
> rendered with the report?
> Writing a custom report item? Rendering as image?
> But how to do a page break?
> Is there a commercial solution available?
> Thanks
> Ericsql
Inserting a Decimal - HELP
For instance 12.5 will insert as 12.
Here is my SQL Parameter code:
[code]
MyCommand.Parameters.Add(New SqlParameter("@.Num", SqlDbType.Decimal))
MyCommand.Parameters("@.Num").Precision = 5
MyCommand.Parameters("@.Num").Scale = 2
MyCommand.Parameters("@.Num").Value = Convert.ToDecimal(TextBox1.Text)
[/code]
I also declared @.Num to be a decimal in my Stored Procedure.
As you can see I give the Parameter more than enough detail about the data type as well as convert the textbox value to a decimal, but it still rounds the value to an integer like data.
Again using a direct T-SQL statement inside the code works OK, but the stored procedure will not.
ANyone got any ideas why this is happening.Try running Profiler and tracing the transaction. Then you can determine if the precision is being lost when .NET sends the command to SQL Server, or when SQL Server calls the stored procedure. Have you tried not being so specific? Can you post the source code for your stored procedure?
MyCommand.Parameters.Add("@.Num", Convert.ToDecimal(TextBox1.Text))
INSERTING 20Mill Records into a Table with 100Mill Records..
I have three tables
Table 1 (100 Mill Rows)
Table 2 (20 Mill Rows)
Table 3 (10 Mill Rows)
INSERT INTO Table1
select Table2.* from Table 2
where exists (select 1 from Table3
where Table2.xyz = Table3.xyz
and Table2.abc = Table3.abc);
Whats the most efficient way to do this..
Iam already DISABL'ng the Indexes before the Insert on Table 1
Also -- Whats the SQLSRVR's equivalent to Rollback Segment?
I would suggest to use a join and be sure to have indexes in table2 and table3 by the columns used in the join.
- index on table2 by (xyz, abc)
- index on table3 by (xyz, abc)
The index could be also by (abc, xyz), but it will depend on the order of an existing constraint like primary key or foreign key, or in case there not a constraint, then the selectivity of those columns..
INSERT INTO Table1
select
Table2.*
from
Table 2 inner join Table3
on Table2.xyz = Table3.xyz and Table2.abc = Table3.abc
AMB
|||Inserting 20 mil rows at one time will create a log of Transaction Log activity.
IF, and that is a big IF, this is a singular operation, and if there is no other activity in the database, you may wish to change the recovery model to 'SIMPLE' (after first making a backup.)
Then do the import in batches of 100k rows - this will greatly reduce the logging pressure and could make a radical difference in speed.
When finished, return the recovery model to the previous setting. AND then make a full backup.
|||If you go with changing the Recovery Model to simple and back, you'll want to be sure to run a full back-up immediately after reverting back.
Switching to the Simple model breaks the log chain and a full back-up is required to establish a new chain.
|||Thanks, Dale,
I should have explicitly mentioned that (assumptions, assumptions, etc.)
inserting 100 records
You mean like:
INSERT INTO t1(c1,c2)
SELECT '1','2'
UNION
SELECT '3','4'
UNION
...
?
|||This will turn off the identity column for a table,
set identity_insert <tablename> on
[insert 100 records .. ]
set identity_insert <tablename> off
No i mean if identity column is off i.e.., the we should explicitly insert ID column by fetching an XML having 100 records for example
Table1
ID StudRollNo StudName
Inserting into table1(Identity column for column ID is OFF) where i will get the XML of table having 100 records like
ID StudRollNo Studname
|||If you mean to read data from XML into datbase table,?I?suggest?you?learn?XQuery?in?SQL2005Inserting .doc data into varbinary column
The varbinary datatype does not differentiate the contents of the field, it's all just binary data as far as SQL is concerned. The samples you've found for images should apply equally to any type of binary object, it just seems that most examples are focues on image since most people want to store image data.
Mike
|||Thanks Mike. I will try those examples.inserting <NULL>
i was trying something like this BUT when you try to write an IS NULL statement it doesnt work.
ISNULL(dbo.TRUNK02_LastVersion_PayableClaims_01.MO D1, NULL) AS Mod1,ISNULL(dbo.TRUNK02_LastVersion_PayableClaims_01.MO , NULL) AS Mod1,|||how is this any different?
ISNULL(dbo.qry_TRUNK02_LastVersion_PayableClaims01 .MOD1, NULL) AS Mod1
after i truncate and insert into my table i need to be able to query with an IS NULL statement.
select mod1
from table
where mod1 is null|||Books online: ISNULL Replaces NULL with the specified replacement value.
So what is the point of ISNULL([A], Null)?
Please explain more clearly what you are trying to do, and give some sample data.|||Yeah, what The Blind One said...
are you just meaning to insert a NULL into a column of a table?
as in:
INSERT INTO dbo.TRUNK02_LastVersion_PayableClaims_01 (Mod1, ...)
VALUES (NULL, ...)???
Inserting " ' "
I have a text field which consists of strings. And I want to insert
whatever the user types into the database. But if the user types some
punctuation marks, such as " ' ", it generates this error:
Unclosed quotation mark after the character string ')'.
the solutions should be so easy, but I couldn't find out where the
close it.
my update command is as follows:
sqlDS.InsertCommand = "INSERT INTO Table VALUES('" & User.Identity.Name
& "','" & Today.Date & "','" & TextBox1.Text & "','" & TextBox2.Text &
"','" & Today.Now & "','Y')"Hi
Add one more "'"
CREATE TABLE #Test (c VARCHAR(10))
INSERT INTO #Test VALUES ('O'' Connor')
"Dot Net Daddy" <cagriandac@.gmail.com> wrote in message
news:1155534287.413841.202530@.m79g2000cwm.googlegroups.com...
> Hello,
> I have a text field which consists of strings. And I want to insert
> whatever the user types into the database. But if the user types some
> punctuation marks, such as " ' ", it generates this error:
>
> Unclosed quotation mark after the character string ')'.
> the solutions should be so easy, but I couldn't find out where the
> close it.
> my update command is as follows:
> sqlDS.InsertCommand = "INSERT INTO Table VALUES('" & User.Identity.Name
> & "','" & Today.Date & "','" & TextBox1.Text & "','" & TextBox2.Text &
> "','" & Today.Now & "','Y')"
>|||This really isn't a SQL question because you need to know how to do
this in VB.NET before the query is passed to the database.
You would do this:
> sqlDS.InsertCommand = "INSERT INTO Table VALUES('" & User.Identity.Name
> & "','" & Today.Date & "','" & TextBox1.Text.Replace("'","''") & "','" & TextBox2.Text.Replace("'","''") &
> "','" & Today.Now & "','Y')"
Izzy
Dot Net Daddy wrote:
> Hello,
> I have a text field which consists of strings. And I want to insert
> whatever the user types into the database. But if the user types some
> punctuation marks, such as " ' ", it generates this error:
>
> Unclosed quotation mark after the character string ')'.
> the solutions should be so easy, but I couldn't find out where the
> close it.
> my update command is as follows:
> sqlDS.InsertCommand = "INSERT INTO Table VALUES('" & User.Identity.Name
> & "','" & Today.Date & "','" & TextBox1.Text & "','" & TextBox2.Text &
> "','" & Today.Now & "','Y')"|||Thank you for your helps.
Izzy wrote:
> This really isn't a SQL question because you need to know how to do
> this in VB.NET before the query is passed to the database.
> You would do this:
> > sqlDS.InsertCommand = "INSERT INTO Table VALUES('" & User.Identity.Name
> > & "','" & Today.Date & "','" & TextBox1.Text.Replace("'","''") & "','" & TextBox2.Text.Replace("'","''") &
> > "','" & Today.Now & "','Y')"
> Izzy
>
> Dot Net Daddy wrote:
> > Hello,
> >
> > I have a text field which consists of strings. And I want to insert
> > whatever the user types into the database. But if the user types some
> > punctuation marks, such as " ' ", it generates this error:
> >
> >
> > Unclosed quotation mark after the character string ')'.
> >
> > the solutions should be so easy, but I couldn't find out where the
> > close it.
> >
> > my update command is as follows:
> >
> > sqlDS.InsertCommand = "INSERT INTO Table VALUES('" & User.Identity.Name
> > & "','" & Today.Date & "','" & TextBox1.Text & "','" & TextBox2.Text &
> > "','" & Today.Now & "','Y')"sql
inserted the image in a column-how can i view the image
i have inserted the image present in mydocuments using alter command
create table aa(a int, d image)
insert into aa values (1,'F:\prudhvi\baba 002.jpg')
when i do
select * from aa
i am getting the result in the column d as
0x463A5C707275646876695C70727564687669203030322E6A 7067
how i can i view the image?
pls clarify my doubt
satish
Hi,
Sql is used for storing data... and image data is stored as varibale length
binary data...
image datatype...
Variable-length binary data from 0 through 231-1 (2,147,483,647) bytes.
Thats why you got that value...
For seeing it... just follow the below link... it uses ASP.NET and the "LOAD
FILE FROM DATABASE" property of text.
http://support.microsoft.com/default...b;en-us;326502
Thanks,
Sree
[Please specify the version of Sql Server as we can save one thread and time
asking back if its 2000 or 2005]
"satish" wrote:
> hi,
> i have inserted the image present in mydocuments using alter command
> create table aa(a int, d image)
> insert into aa values (1,'F:\prudhvi\baba 002.jpg')
> when i do
> select * from aa
> i am getting the result in the column d as
> 0x463A5C707275646876695C70727564687669203030322E6A 7067
>
> how i can i view the image?
> pls clarify my doubt
> satish
>
inserted the image in a column-how can i view the image
i have inserted the image present in mydocuments using alter command
create table aa(a int, d image)
insert into aa values (1,'F:\prudhvi\baba 002.jpg')
when i do
select * from aa
i am getting the result in the column d as
0x463A5C707275646876695C70727564687669203030322E6A7067
how i can i view the image?
pls clarify my doubt
satishHi,
Sql is used for storing data... and image data is stored as varibale length
binary data...
image datatype...
Variable-length binary data from 0 through 231-1 (2,147,483,647) bytes.
Thats why you got that value...
For seeing it... just follow the below link... it uses ASP.NET and the "LOAD
FILE FROM DATABASE" property of text.
http://support.microsoft.com/default.aspx?scid=kb;en-us;326502
Thanks,
Sree
[Please specify the version of Sql Server as we can save one thread and time
asking back if its 2000 or 2005]
"satish" wrote:
> hi,
> i have inserted the image present in mydocuments using alter command
> create table aa(a int, d image)
> insert into aa values (1,'F:\prudhvi\baba 002.jpg')
> when i do
> select * from aa
> i am getting the result in the column d as
> 0x463A5C707275646876695C70727564687669203030322E6A7067
>
> how i can i view the image?
> pls clarify my doubt
> satish
>
inserted the image in a column-how can i view the image
i have inserted the image present in mydocuments using alter command
create table aa(a int, d image)
insert into aa values (1,'F:\prudhvi\baba 002.jpg')
when i do
select * from aa
i am getting the result in the column d as
0x463A5C707275646876695C70727564687669203030322E6A 7067
how i can i view the image?
pls clarify my doubt
satishUse WRITETEXT/READTEXT instead of INSERT/SELECT to store / retrieve
There is quite a detailed explanation in Books Online
--
Jack Vamvas
___________________________________
Receive free SQL tips - www.ciquery.com/sqlserver.htm
"satish" <satishkumar.gourabathina@.gmail.com> wrote in message
news:1141732028.000941.183610@.e56g2000cwe.googlegr oups.com...
> hi,
> i have inserted the image present in mydocuments using alter command
> create table aa(a int, d image)
> insert into aa values (1,'F:\prudhvi\baba 002.jpg')
>
> when i do
> select * from aa
> i am getting the result in the column d as
> 0x463A5C707275646876695C70727564687669203030322E6A 7067
>
> how i can i view the image?
>
> pls clarify my doubt
>
> satish|||satish (satishkumar.gourabathina@.gmail.com) writes:
> i have inserted the image present in mydocuments using alter command
> create table aa(a int, d image)
> insert into aa values (1,'F:\prudhvi\baba 002.jpg')
> when i do
> select * from aa
> i am getting the result in the column d as
> 0x463A5C707275646876695C70727564687669203030322E6A 7067
>
> how i can i view the image?
You have not inserted the the image into the table. You have inserted the
disk location of the image into the table. Run
SELECT convert(varchar(80), d) FROM aa
to see.
There is no way to insert data into a column directly from a file. The
normal way of loading image is write a program that reads the file,
and the passes the binary stream through a parameterised INSERT statement
in a client API. You can also convert the contents to a hexstring and
build an INSERT statement from that.
Conversly, to display the image you also need an application. If you
have stored an image in a table, a SELECT on that table in Query Analyzer
or Mgmt Studio will only display a long hex string.
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx
inserted the image in a column-how can i view the image
i have inserted the image present in mydocuments using alter command
create table aa(a int, d image)
insert into aa values (1,'F:\prudhvi\baba 002.jpg')
when i do
select * from aa
i am getting the result in the column d as
0x463A5C707275646876695C7072756468766920
3030322E6A7067
how i can i view the image?
pls clarify my doubt
satishHi,
Sql is used for storing data... and image data is stored as varibale length
binary data...
image datatype...
Variable-length binary data from 0 through 231-1 (2,147,483,647) bytes.
Thats why you got that value...
For seeing it... just follow the below link... it uses ASP.NET and the "LOAD
FILE FROM DATABASE" property of text.
http://support.microsoft.com/defaul...kb;en-us;326502
Thanks,
Sree
[Please specify the version of Sql Server as we can save one thread and
time
asking back if its 2000 or 2005]
"satish" wrote:
> hi,
> i have inserted the image present in mydocuments using alter command
> create table aa(a int, d image)
> insert into aa values (1,'F:\prudhvi\baba 002.jpg')
> when i do
> select * from aa
> i am getting the result in the column d as
> 0x463A5C707275646876695C7072756468766920
3030322E6A7067
>
> how i can i view the image?
> pls clarify my doubt
> satish
>sql
inserted text take the wrong alignment
i try to insert the following string in the database
the red car (driver)
this string save like this
)the red car (driver
i have a problem when inserting string contains special character at the end of the string.
we have arabic and english string like this
???? ????? (R) radial ????
and it appear in reverse like this
???? (R) radial ???? ?????
You need to check the application that is inserting the data specifically the API commands being used. This is not a SQL Server problem per se. The database engine will store the values as passed from the client and doesn't manipulate it on the server. Also, where are you checking the display of the values? It is possible that the tool is doing something based on your language / regional settings. So this could just be a display issue also. Start with verifying the data in the back end tables directly, then your client code and then whatever UI you are using.|||hello Umachandar,
me and Batool posted this one together
I do import the data into the database through a certain script, but I thought it was an sql problem, because the data were in the correct alignment before inserting, I see them reversed in the tables directly, actually to test this issue I tried to enter data directly into the database so in the cell I press ctrl + Alt + shift to reverse the alignment inside the cell in table, and when I start submitting my data it is reversed.
how could this be a display problem when it's correct in all other applications on my machine
thank you
|||I believe I've seen funny behavior in Management Studio when you try to display mixed right-left and left-right scripts. (I doubt this is unique to MS.) Can you inspect the binary contents of the strings and see whether it contains what you expect?
Cheers,
|||You should verify the data first without involving any UI elements into the picture. The reason I say that it could be a display issue is that the tool might be doing something different when reading and displaying the data. This happens for float data type values today. The accuracy of the digits are different from ISQLW and in some cases two values that differ in say the 17th decimal digit will look the same. But this doesn't mean that the values are the same.
So you could write a script or program that does the insert, reads the data back and verifies it using SQL only. This will eliminate the UI from the picture. Additionally, tracing the calls to the server from the UI / tool via Profiler will also help. You can find out if the provider/driver is translating the string based on code page settings. There are just too many variables involved in this. Is it possible to do the following?
1. Post a simple DDL, insert statements and SELECT which shows the behavior (note that you may have to use the appropriate collation and Unicode data type to avoid any character translation)
2. If #1 doesn't work for you, is it possible to post some steps using say a particular UI (like ISQLW or SSMS). Please be clear on how you are inputting the data (open table, script/open table combination) and so on. Schema and data type of the column(s) are important here also. You talk about entering something in a cell - where is this? What UI are you talking about?
Lastly, the configuration of the OS (language/regional settings) may also be a factor and version of SQL Server. So please post those also.
Inserted Rows count from SSIS not like table Rows Count
Hi all
i using lookup error output to insert rows into table
Rows count rows has been inserted on the table 59,123,019 mill
table rows count 6,878,110 mill ............................
any ideas
So from the error output you are getting 59 123 019 rows? How are you counting this?What are you using to insert the rows?|||Mapping from values i get and columns on distination table|||How are you counting the rows inserted?|||
I figuer out the Reasone my lookup error output configure to ignore the error and the error was "can't insert duplicated key" i change the error output to insert duplicated rows in flat file but i don't test it yet , i will hint with updates , hope that's resolve the problem
wish me luck
Phil
I Count rows from distination table "Select Count "
|||
Hosam Abdel Wahab wrote:
Phil
I Count rows from distination table "Select Count "
I mean, how are you counting the rows inserted from within SSIS?
But, yes, it sounds like you are on the correct path to figuring out your problem.
Inserted records missing in sql table yet tables primary key field has been incremented.
I have a sql sever 2005 express table with an automatically incremented primary key field. I use a Detailsview to insert new records and on the Detailsview itemInserted event, i send out automated notification emails.
I then received two automated emails(indicating two records have been inserted) but looking at the database, the records are not there. Whats confusing me is that even the tables primary key field had been incremented by two, an indication that indeed the two records should actually be in table. Recovering these records is not abig deal because i can re-enter them but iam wondering what the possible cause is. How come the id field was even incremented and the records are not there yet iam 100% sure no one deleted them. Its only me who can delete a record.
And then how come i insert new records now and they are all there in the database but now with two id numbers for those missing records skipped. Its not crucial data but for my learning, i feel i deserve understanding why it happened because next time, it might be costly.
Hi Nick,
Your problem seems interesting. Would you please put some related code here. So that we can analyze what exactly going on there.
|||The code below indicates when the automated email is send and after that is the markup of my page.
ProtectedSub DetailsView1_ItemInserted(ByVal senderAsObject,ByVal eAs System.Web.UI.WebControls.DetailsViewInsertedEventArgs)
'I have code to send automated emails here.
EndIf
Catch exAs Exception
'iam not catching nor doing any thing here. (possibly i should have done some thing here)Finally
Response.Redirect("AfterInserting.aspx")EndTry
And the markup is below
<asp:ContentID="Content1"ContentPlaceHolderID="ContentPlaceHolder1"Runat="Server">
<table>
<tr>
<tdstyle="width: 100px; height: 21px; text-align: left;"valign="top">
<asp:LabelID="Label8"runat="server"Width="126px"></asp:Label>
<asp:LabelID="Label20"runat="server"Width="128px"ForeColor="#0000FF"></asp:Label></td>
<tdstyle="width: 100px; height: 21px; text-align: left;"valign="top">
<asp:DetailsViewID="DetailsView1"runat="server"AutoGenerateRows="False"DataKeyNames="Incident_id"
DataSourceID="SqlDataSource1"DefaultMode="Insert"Height="50px"Width="497px"Font-Size="Smaller"OnItemInserted="DetailsView1_ItemInserted"BackColor="LightGoldenrodYellow"BorderColor="Tan"BorderWidth="1px"CellPadding="2"ForeColor="Black"OnItemInserting="DetailsView1_ItemInserting">
<Fields>
<asp:TemplateFieldHeaderText="Incident_id"InsertVisible="False"SortExpression="Incident_id">
<EditItemTemplate>
<asp:LabelID="Label1"runat="server"Text='<%# Eval("Incident_id") %>'></asp:Label>
</EditItemTemplate>
<ItemTemplate>
<asp:LabelID="Label20"runat="server"Text='<%# Bind("Incident_id") %>'ToolTip="This is the Incident Number"></asp:Label>
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateFieldHeaderText="Person Raising Report"SortExpression="Incident_Reported_By">
<EditItemTemplate>
<asp:TextBoxID="TextBox7"runat="server"Text='<%# Bind("Incident_Reported_By") %>'></asp:TextBox>
</EditItemTemplate>
<InsertItemTemplate>
<asp:TextBoxID="TextBox2"runat="server"Text='<%# Bind("Incident_Reported_By") %>'ToolTip="Type the name of the person raising the report here (Your Name)"></asp:TextBox>
<asp:RequiredFieldValidatorID="RequiredFieldValidator1"runat="server"ControlToValidate="TextBox2"
ErrorMessage='You have not provided your name ......You must enter your name in the Person raising report field in order to report this incident .'
SetFocusOnError="True"ValidationGroup="email"EnableTheming="False">*</asp:RequiredFieldValidator>
</InsertItemTemplate>
<ItemTemplate>
<asp:LabelID="Label7"runat="server"Text='<%# Bind("Incident_Reported_By") %>'></asp:Label>
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateFieldHeaderText="Person Raising Report's Employee#"SortExpression="Emp_No">
<EditItemTemplate>
<asp:TextBoxID="TextBox2"runat="server"Text='<%# Bind("Emp_No") %>'></asp:TextBox>
</EditItemTemplate>
<InsertItemTemplate>
<asp:DropDownListID="DropDownList1"runat="server"DataSourceID="SqlDataSource20"
DataTextField="EmpNumber"DataValueField="EmpNumber"SelectedValue='<%# Bind("Emp_No") %>'
Width="156px"ToolTip="Select Your Employee Number here. If you have no number check other options at bottom of the list and select one that suits you">
</asp:DropDownList><asp:SqlDataSourceID="SqlDataSource20"runat="server"ConnectionString="<%$ ConnectionStrings:ConnectionString %>"
SelectCommand="SELECT [EmpNumber] FROM [EmpNumbers] ORDER BY [EmpNumber]"></asp:SqlDataSource>
<asp:RequiredFieldValidatorID="RequiredFieldValidator2"runat="server"ControlToValidate="DropDownList1"
ErrorMessage="You must select your Employee Number. Other options are : Trainee, Contractor, Casual, Canteen staff and Security personnel. "
InitialValue=".."ValidationGroup="email">.</asp:RequiredFieldValidator>
<asp:TextBoxID="TextBox24"runat="server"Text='<%# Eval("Emp_No") %>'Visible="False"></asp:TextBox>
</InsertItemTemplate>
<ItemTemplate>
<asp:LabelID="Label2"runat="server"Text='<%# Bind("Emp_No") %>'></asp:Label>
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateFieldHeaderText="Personnel Directly Involved"SortExpression="Personnel_Directly_Involved">
<EditItemTemplate>
<asp:TextBoxID="TextBox10"runat="server"Text='<%# Bind("Personnel_Directly_Involved") %>'></asp:TextBox>
</EditItemTemplate>
<InsertItemTemplate>
<asp:TextBoxID="TextBox4"runat="server"Text='<%# Bind("Personnel_Directly_Involved") %>'ToolTip="Type the name of the person directly involved in the Incident here"></asp:TextBox>
<asp:RequiredFieldValidatorID="RequiredFieldValidator4"runat="server"ControlToValidate="TextBox4"
ErrorMessage="Error in Personnel directly Involved Field....This field can not left blank"SetFocusOnError="True"
ValidationGroup="email">*</asp:RequiredFieldValidator>
</InsertItemTemplate>
<ItemTemplate>
<asp:LabelID="Label10"runat="server"Text='<%# Bind("Personnel_Directly_Involved") %>'></asp:Label>
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateFieldHeaderText="Witness 1">
<InsertItemTemplate>
<asp:TextBoxID="TextBox21"runat="server"ToolTip="Type the name of the witness here. You can not leave this field blank"></asp:TextBox>
<asp:RequiredFieldValidatorID="RequiredFieldValidator8"runat="server"ControlToValidate="TextBox21"
EnableTheming="True"ErrorMessage="You must atleast specify one witness to the Incident. Please type the witness name."
SetFocusOnError="True"ValidationGroup="email">.</asp:RequiredFieldValidator>
</InsertItemTemplate>
</asp:TemplateField>
<asp:TemplateFieldHeaderText="Witness 2">
<InsertItemTemplate>
<asp:TextBoxID="TextBox22"runat="server"Text='<%# Bind("Witness_2") %>'ToolTip="Type the name of the second witness here if any. (Optional)"></asp:TextBox>
</InsertItemTemplate>
</asp:TemplateField>
<asp:TemplateFieldHeaderText="Witness 3">
<InsertItemTemplate>
<asp:TextBoxID="TextBox23"runat="server"Text='<%# Bind("witness_3") %>'ToolTip="Type the name of the third witness here if any (Optional)"></asp:TextBox>
</InsertItemTemplate>
</asp:TemplateField>
<asp:TemplateFieldHeaderText="Date Incident Occured "SortExpression="Incident_Date">
<EditItemTemplate>
<asp:TextBoxID="TextBox1"runat="server"Text='<%# Bind("Incident_Date") %>'></asp:TextBox>
</EditItemTemplate>
<InsertItemTemplate>
<cc1:GMDatePickerID="GMDatePicker1"runat="server"AutoPosition="True"CalendarOffsetX="-200px"CalendarOffsetY="25px"CalendarTheme="Green"CalendarWidth="250px"CallbackEventReference=""Culture="English (United States)"DateString='<%# bind("Incident_Date") %>'EnableDropShadow="True"MaxDate="2020-12-31"MinDate=""NextMonthText=">"NoneButtonText="None"ShowNoneButton="False"ShowTodayButton="True"TextBoxWidth="150"ZIndex="1"InitialText="select date"ToolTip="Click the icon on the right to select the date on which the incident occurred">
<CalendarTodayDayStyleBackColor="#C0FFC0"/>
</cc1:GMDatePicker>
<asp:RequiredFieldValidatorID="RequiredFieldValidator6"runat="server"ControlToValidate="GMDatePicker1"
ErrorMessage="You must select the date on which this Incident Occurred. Click the icon next to the incident occurred date field to show a calendar and then click the desired date from the calendar."
SetFocusOnError="True"ValidationGroup="email"InitialValue="select date">.</asp:RequiredFieldValidator>
</InsertItemTemplate>
<ItemTemplate>
<asp:LabelID="Label1"runat="server"Text='<%# Bind("Incident_Date") %>'></asp:Label>
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateFieldHeaderText="Date Incident Is Reported "SortExpression="Date_Reported">
<EditItemTemplate>
<asp:TextBoxID="TextBox9"runat="server"></asp:TextBox>
</EditItemTemplate>
<InsertItemTemplate>
<asp:TextBoxID="Textbox3"runat="server"Text='<%# Bind("Date_Reported") %>'ReadOnly="True"Font-Size="9pt"ForeColor="#6666FF"ToolTip="Do not type anything here. This field is automated to always display and save the current date"></asp:TextBox>
<asp:RequiredFieldValidatorID="RequiredFieldValidator3"runat="server"ControlToValidate="TextBox3"
ErrorMessage="Error in Incident Reported Date....This field can not be left blank. "
SetFocusOnError="True"ValidationGroup="email">*</asp:RequiredFieldValidator>
<asp:CompareValidatorID="CompareValidator2"runat="server"ControlToValidate="TextBox3"
ErrorMessage='Error in Incident Reported Date Field. Re-enter date in month/day/year format '
Operator="DataTypeCheck"SetFocusOnError="True"Type="Date"ValidationGroup="email">*</asp:CompareValidator>
</InsertItemTemplate>
<ItemTemplate>
<asp:LabelID="Label9"runat="server"Text='<%# Bind("Date_Reported") %>'></asp:Label>
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateFieldHeaderText="Time Incident Occurred"SortExpression="TimeCoomencedshift">
<EditItemTemplate>
<asp:TextBoxID="TextBox16"runat="server"Text='<%# Bind("TimeCoomencedshift") %>'></asp:TextBox>
</EditItemTemplate>
<InsertItemTemplate>
<asp:TextBoxID="TextBox9"runat="server"Text='<%# Bind("time_incident_occurred") %>'Width="67px"Height="21px"ToolTip="Type the time at which the incident occurred here in 24 hour format."></asp:TextBox>
<asp:ListBoxID="ListBox1"runat="server"Height="24px"Width="55px"ToolTip="Use the up and down arrows to specify AM or PM">
<asp:ListItem>PM</asp:ListItem>
<asp:ListItem>AM</asp:ListItem>
</asp:ListBox>
<asp:RequiredFieldValidatorID="RequiredFieldValidator7"runat="server"ControlToValidate="TextBox9"
ErrorMessage="You must enter the time at which the Incident occurred"SetFocusOnError="True"
ValidationGroup="email"Height="10px">.</asp:RequiredFieldValidator>
</InsertItemTemplate>
<ItemTemplate>
<asp:LabelID="Label16"runat="server"Text='<%# Bind("TimeCoomencedshift") %>'></asp:Label>
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateFieldHeaderText="ReminderDate"SortExpression="ReminderDate">
<EditItemTemplate>
<asp:TextBoxID="TextBox8"runat="server"Text='<%# Bind("ReminderDate") %>'></asp:TextBox>
</EditItemTemplate>
<InsertItemTemplate>
<asp:TextBoxID="TextBox6"runat="server"Text='<%# Bind("ReminderDate") %>'Font-Size="9pt"ForeColor="#6666FF"ReadOnly="True"ToolTip="Do not type any thing here. This field is automated to always add 3 days to the current date "></asp:TextBox>
</InsertItemTemplate>
<ItemTemplate>
<asp:LabelID="Label8"runat="server"Text='<%# Bind("ReminderDate") %>'></asp:Label>
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateFieldHeaderText="Department">
<InsertItemTemplate>
<asp:DropDownListID="DropDownList5"runat="server"DataSourceID="DEPTDataSource1"
DataTextField="name"DataValueField="name"SelectedValue='<%# Bind("Dept") %>'
Width="155px"ToolTip="Click the arrow ponting down to select a department of the person involved from this list ">
</asp:DropDownList><asp:SqlDataSourceID="DEPTDataSource1"runat="server"ConnectionString="<%$ ConnectionStrings:ConnectionString %>"
SelectCommand="SELECT [name] FROM [Deptments] ORDER BY [name]"></asp:SqlDataSource>
</InsertItemTemplate>
</asp:TemplateField>
<asp:TemplateFieldHeaderText="Incident Location"SortExpression="Incident_Location">
<EditItemTemplate>
<asp:TextBoxID="TextBox3"runat="server"Text='<%# Bind("Incident_Location") %>'></asp:TextBox>
</EditItemTemplate>
<InsertItemTemplate>
<asp:DropDownListID="DropDownList2"runat="server"DataSourceID="SqlDataSource3"
DataTextField="Area_Name"DataValueField="Area_Name"SelectedValue='<%# Bind("Incident_Location") %>'
Width="155px"ToolTip="Select the location where the incident occurred from this list">
</asp:DropDownList><asp:SqlDataSourceID="SqlDataSource3"runat="server"ConnectionString="<%$ ConnectionStrings:ConnectionString %>"
SelectCommand="SELECT [Area_Name] FROM [Incident_Areas] ORDER BY [Area_Name]"></asp:SqlDataSource>
</InsertItemTemplate>
<ItemTemplate>
<asp:LabelID="Label3"runat="server"Text='<%# Bind("Incident_Location") %>'></asp:Label>
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateFieldHeaderText="Incident Category"SortExpression="Incident_Category">
<EditItemTemplate>
<asp:TextBoxID="TextBox4"runat="server"Text='<%# Bind("Incident_Category") %>'></asp:TextBox>
</EditItemTemplate>
<InsertItemTemplate>
<asp:DropDownListID="DropDownList3"runat="server"DataSourceID="SqlDataSource5"
DataTextField="Category_Name"DataValueField="Category_Name"SelectedValue='<%# Bind("Incident_Category") %>'
Width="155px"ToolTip="Select the category of the incident from this list. Please take special note of injuries ">
</asp:DropDownList><asp:SqlDataSourceID="SqlDataSource5"runat="server"ConnectionString="<%$ ConnectionStrings:ConnectionString %>"
SelectCommand="SELECT [Category_Name] FROM [Incident_Category] ORDER BY [Category_Name]">
</asp:SqlDataSource>
</InsertItemTemplate>
<ItemTemplate>
<asp:LabelID="Label4"runat="server"Text='<%# Bind("Incident_Category") %>'></asp:Label>
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateFieldHeaderText="Incident Severity"SortExpression="Incident_Severity">
<EditItemTemplate>
<asp:TextBoxID="TextBox5"runat="server"Text='<%# Bind("Incident_Severity") %>'></asp:TextBox>
</EditItemTemplate>
<InsertItemTemplate>
<asp:DropDownListID="DropDownList4"runat="server"DataSourceID="SqlDataSource7"
DataTextField="Incident_Severity"DataValueField="Incident_Severity"SelectedValue='<%# Bind("Incident_Severity") %>'
Width="157px"ToolTip="Select the incident severity from this list">
</asp:DropDownList><asp:SqlDataSourceID="SqlDataSource7"runat="server"ConnectionString="<%$ ConnectionStrings:ConnectionString %>"
SelectCommand="SELECT [Incident_Severity] FROM [Incident_Severity] ORDER BY [Incident_Severity]">
</asp:SqlDataSource>
</InsertItemTemplate>
<ItemTemplate>
<asp:LabelID="Label5"runat="server"Text='<%# Bind("Incident_Severity") %>'></asp:Label>
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateFieldHeaderText="Incident classification"SortExpression="Incident_classification"Visible="False">
<EditItemTemplate>
<asp:TextBoxID="TextBox12"runat="server"Text='<%# Bind("Incident_classification") %>'></asp:TextBox>
</EditItemTemplate>
<InsertItemTemplate>
<asp:DropDownListID="DropDownList7"runat="server"DataSourceID="SqlDataSource16"
DataTextField="classification"DataValueField="classification"SelectedValue='<%# Bind("Incident_classification") %>'
Width="157px">
</asp:DropDownList><asp:SqlDataSourceID="SqlDataSource16"runat="server"ConnectionString="<%$ ConnectionStrings:ConnectionString %>"
SelectCommand="SELECT [classification] FROM [Incident_Classification]"></asp:SqlDataSource>
</InsertItemTemplate>
<ItemTemplate>
<asp:LabelID="Label12"runat="server"Text='<%# Bind("Incident_classification") %>'></asp:Label>
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateFieldHeaderText=" Incident timing"SortExpression="TimingOfIncident"Visible="False">
<EditItemTemplate>
<asp:TextBoxID="TextBox19"runat="server"Text='<%# Bind("TimingOfIncident") %>'></asp:TextBox>
</EditItemTemplate>
<InsertItemTemplate>
<asp:DropDownListID="DropDownList8"runat="server"DataSourceID="SqlDataSource26"
DataTextField="timing"DataValueField="timing"SelectedValue='<%# Bind("TimingOfIncident") %>'
Width="155px">
</asp:DropDownList><asp:SqlDataSourceID="SqlDataSource26"runat="server"ConnectionString="<%$ ConnectionStrings:ConnectionString %>"
SelectCommand="SELECT [timing] FROM [roster_timing_OfIncident]"></asp:SqlDataSource>
</InsertItemTemplate>
<ItemTemplate>
<asp:LabelID="Label19"runat="server"Text='<%# Bind("TimingOfIncident") %>'></asp:Label>
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateFieldHeaderText="Shift Details"SortExpression="ShiftDetails"Visible="False">
<EditItemTemplate>
<asp:TextBoxID="TextBox18"runat="server"Text='<%# Bind("ShiftDetails") %>'></asp:TextBox>
</EditItemTemplate>
<InsertItemTemplate>
<asp:DropDownListID="DropDownList9"runat="server"DataSourceID="SqlDataSource27"
DataTextField="shiftdetails"DataValueField="shiftdetails"SelectedValue='<%# Bind("ShiftDetails") %>'
Width="158px">
</asp:DropDownList><asp:SqlDataSourceID="SqlDataSource27"runat="server"ConnectionString="<%$ ConnectionStrings:ConnectionString %>"
SelectCommand="SELECT [shiftdetails] FROM [ShiftDetails]"></asp:SqlDataSource>
</InsertItemTemplate>
<ItemTemplate>
<asp:LabelID="Label18"runat="server"Text='<%# Bind("ShiftDetails") %>'></asp:Label>
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateFieldHeaderText="Equipment Involved"SortExpression="EquipmentInvolved">
<EditItemTemplate>
<asp:TextBoxID="TextBox13"runat="server"Text='<%# Bind("EquipmentInvolved") %>'></asp:TextBox>
</EditItemTemplate>
<InsertItemTemplate>
<asp:DropDownListID="DropDownList6"runat="server"DataSourceID="SqlDataSource28"
DataTextField="Cause"DataValueField="Cause"SelectedValue='<%# Bind("EqiupmentInvolved") %>'
Width="156px"ToolTip="Select the equipment involved in incident. If the equipment involved does not exist in the list, please notify safety to have the equipment added to the list.">
</asp:DropDownList><asp:SqlDataSourceID="SqlDataSource28"runat="server"ConnectionString="<%$ ConnectionStrings:ConnectionString %>"
SelectCommand="SELECT [Cause] FROM [WhatCausedInjury] ORDER BY [Cause]"></asp:SqlDataSource>
</InsertItemTemplate>
<ItemTemplate>
<asp:LabelID="Label13"runat="server"Text='<%# Bind("EquipmentInvolved") %>'></asp:Label>
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateFieldHeaderText="N0. of days into Roster Cycle"SortExpression="Time Incident Occurred"Visible="False">
<EditItemTemplate>
<asp:TextBoxID="TextBox17"runat="server"Text='<%# Bind("NumberOfDaysintoRosterCycle") %>'></asp:TextBox>
</EditItemTemplate>
<InsertItemTemplate>
<asp:TextBoxID="TextBox10"runat="server"Text='<%# Bind("NumberOfDaysintoRosterCycle") %>'></asp:TextBox>
</InsertItemTemplate>
<ItemTemplate>
<asp:LabelID="Label17"runat="server"Text='<%# Bind("NumberOfDaysintoRosterCycle") %>'></asp:Label>
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateFieldHeaderText="Hours into shift"SortExpression="Hoursintoshift"Visible="False">
<EditItemTemplate>
<asp:TextBoxID="TextBox15"runat="server"Text='<%# Bind("Hoursintoshift") %>'></asp:TextBox>
</EditItemTemplate>
<InsertItemTemplate>
<asp:TextBoxID="TextBox8"runat="server"Text='<%# Bind("Hoursintoshift") %>'></asp:TextBox>
</InsertItemTemplate>
<ItemTemplate>
<asp:LabelID="Label15"runat="server"Text='<%# Bind("Hoursintoshift") %>'></asp:Label>
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateFieldHeaderText="Time To Finish shift"SortExpression="TimeToFinishshift"Visible="False">
<EditItemTemplate>
<asp:TextBoxID="TextBox14"runat="server"Text='<%# Bind("TimeToFinishshift") %>'></asp:TextBox>
</EditItemTemplate>
<InsertItemTemplate>
<asp:TextBoxID="TextBox7"runat="server"Text='<%# Bind("TimeToFinishshift") %>'></asp:TextBox>
</InsertItemTemplate>
<ItemTemplate>
<asp:LabelID="Label14"runat="server"Text='<%# Bind("TimeToFinishshift") %>'></asp:Label>
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateFieldHeaderText="Incident Brief Description"SortExpression="Incident_Description">
<EditItemTemplate>
<asp:TextBoxID="TextBox11"runat="server"Text='<%# Bind("Incident_Description") %>'></asp:TextBox>
</EditItemTemplate>
<InsertItemTemplate>
<asp:TextBoxID="TextBox5"runat="server"Height="45px"Text='<%# Bind("Incident_Description") %>'
TextMode="MultiLine"Width="199px"ToolTip="Briefly describe the incident here. You can type upto a maximum of 4000 characters"></asp:TextBox>
<asp:RequiredFieldValidatorID="RequiredFieldValidator5"runat="server"ControlToValidate="TextBox5"
ErrorMessage="Error in Incident Description Field....You must briefly describe the nature of the Incident"SetFocusOnError="True"
ValidationGroup="email">*</asp:RequiredFieldValidator>
</InsertItemTemplate>
<ItemTemplate>
<asp:LabelID="Label11"runat="server"Text='<%# Bind("Incident_Description") %>'></asp:Label>
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateFieldHeaderText="Immediate Action"SortExpression="Immediate_Action">
<EditItemTemplate>
<asp:TextBoxID="TextBox6"runat="server"Text='<%# Bind("Immediate_Action") %>'></asp:TextBox>
</EditItemTemplate>
<InsertItemTemplate>
<asp:TextBoxID="TextBox20"runat="server"Text='<%# Bind("Immediate_Action") %>'
TextMode="MultiLine"Height="41px"Width="201px"ToolTip="Type the immediate action taken when the incident occurred here"></asp:TextBox>
<asp:RequiredFieldValidatorID="RequiredFieldValidator9"runat="server"ControlToValidate="TextBox20"
ErrorMessage="No Immediate Action Entered: Please first enter the Immediate Action taken when the Incident Occurred"
SetFocusOnError="True"ValidationGroup="email">.</asp:RequiredFieldValidator>
</InsertItemTemplate>
<ItemTemplate>
<asp:LabelID="Label6"runat="server"Text='<%# Bind("Immediate_Action") %>'></asp:Label>
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateFieldHeaderText="Foward To Your Head Of Department">
<InsertItemTemplate>
<asp:DropDownListID="DropDownList10"runat="server"DataSourceID="SqlDataSource50"
DataTextField="Names"DataValueField="Names"SelectedValue='<%# Bind("Foward_to") %>'
Width="154px"ToolTip="Select the head of department you want to foward the incident to from here">
</asp:DropDownList><asp:SqlDataSourceID="SqlDataSource50"runat="server"ConnectionString="<%$ ConnectionStrings:ConnectionString %>"
SelectCommand="SELECT [Names] FROM [H.O.D's] ORDER BY [Names]"></asp:SqlDataSource>
<asp:RequiredFieldValidatorID="RequiredFieldValidator10"runat="server"ControlToValidate="DropDownList10"
ErrorMessage="You have not selected the Head Of Department. Please select your head of department and then report agian."SetFocusOnError="True"ValidationGroup="email">.</asp:RequiredFieldValidator>
</InsertItemTemplate>
</asp:TemplateField>
<asp:TemplateFieldShowHeader="False">
<InsertItemTemplate>
<asp:ButtonID="Button1"runat="server"CausesValidation="True"CommandName="Insert"
Text="Report Incident/Hazard"ValidationGroup="email"/>
<asp:ButtonID="Button2"runat="server"PostBackUrl="~/StartPage.aspx"Text="<< Exit"/>
</InsertItemTemplate>
<ItemStyleHorizontalAlign="Center"/>
<ItemTemplate>
<asp:ButtonID="Button1"runat="server"CausesValidation="False"CommandName="New"
Text="New"/>
</ItemTemplate>
</asp:TemplateField>
</Fields>
<FieldHeaderStyleHorizontalAlign="Right"/>
<InsertRowStyleHorizontalAlign="Left"/>
<FooterStyleBackColor="Tan"/>
<EditRowStyleBackColor="DarkSlateBlue"ForeColor="GhostWhite"/>
<PagerStyleBackColor="PaleGoldenrod"ForeColor="DarkSlateBlue"HorizontalAlign="Center"/>
<HeaderStyleBackColor="Tan"Font-Bold="True"/>
<AlternatingRowStyleBackColor="PaleGoldenrod"/>
</asp:DetailsView>
<asp:SqlDataSourceID="SqlDataSource1"runat="server"
ConnectionString="<%$ ConnectionStrings:ConnectionString %>"DeleteCommand="DELETE FROM [Report_Incident] WHERE [Incident_id] = @.original_Incident_id"
InsertCommand="INSERT INTO Report_Incident(Incident_Reported_By, Incident_Date, Date_Reported, ReminderDate, Personnel_Directly_Involved, Incident_Location, Incident_Category, Incident_Severity, Immediate_Action, Incident_Description, Incident_Assigned_To, EqiupmentInvolved, Emp_No, Foward_to, Dept, witness_1, witness_2, witness_3, time_incident_occurred) VALUES (@.Incident_Reported_By,@.Incident_Date,@.Date_Reported,@.ReminderDate, @.Personnel_Directly_Involved,@.Incident_Location,@.Incident_Category,@.Incident_Severity, @.Immediate_Action,@.Incident_Description,@.Incident_Assigned_To,@.EqiupmentInvolved, @.Emp_No,@.Foward_to,@.Dept,@.witness_1,@.witness_2,@.witness_3,@.time_incident_occurred) "
OldValuesParameterFormatString="original_{0}"SelectCommand="SELECT Incident_id, Incident_Reported_By, Incident_Date, Date_Reported, Personnel_Directly_Involved, Incident_Location, Incident_Category, Incident_Severity, Immediate_Action, Incident_Description, Incident_Assigned_To, Incident_classification, EqiupmentInvolved,Emp_No, ReminderDate,Foward_to, Dept, witness_1, witness_2, witness_3, time_incident_occurred FROM Report_Incident"EnableCaching="True">
<DeleteParameters>
<asp:ParameterName="original_Incident_id"Type="Int32"/>
</DeleteParameters>
<InsertParameters>
<asp:ParameterName="Incident_Reported_By"Type="String"/>
<asp:ParameterName="Emp_No"/>
<asp:ParameterName="Incident_Date"Type="DateTime"/>
<asp:ParameterName="Date_Reported"Type="DateTime"/>
<asp:ParameterName="ReminderDate"/>
<asp:ParameterName="Personnel_Directly_Involved"Type="String"/>
<asp:ParameterName="Incident_Location"Type="String"/>
<asp:ParameterName="Incident_Category"Type="String"/>
<asp:ParameterName="Incident_Severity"Type="String"/>
<asp:ParameterName="Immediate_Action"Type="String"/>
<asp:ParameterName="Incident_Description"Type="String"/>
<asp:ParameterName="Incident_Assigned_To"Type="String"/>
<asp:ParameterName="EqiupmentInvolved"/>
<asp:ParameterName="Foward_to"/>
<asp:ParameterName="Dept"/>
<asp:ParameterName="witness_1"/>
<asp:ParameterName="witness_2"/>
<asp:ParameterName="witness_3"/>
<asp:ParameterName="time_incident_occurred"/>
</InsertParameters>
</asp:SqlDataSource>
<asp:ValidationSummaryID="ValidationSummary1"runat="server"ShowMessageBox="True"
ShowSummary="False"ValidationGroup="email"Font-Strikeout="True"Height="1px"Width="179px"/>
</td>
<tdstyle="height: 21px; width: 3px;"valign="top">
<br/>
<br/>
<br/>
<br/>
<br/>
<br/>
<br/>
<br/>
<br/>
<asp:ButtonID="Button3"runat="server"Text="Get Help ?"Width="101px"Font-Bold="False"OnClientClick='window.open("Help/onreporting.aspx")'/><br/>
<br/>
<asp:ButtonID="Button1"runat="server"PostBackUrl="~/StartPage.aspx"Text="<< Back"
Width="97px"/><br/>
</td>
</tr>
</table></asp:Content>
|||Identity column data is not guaranteed to be consecutive.
If the inserts were done in the context of a transaction, and the transaction is rolled back, then that is exactly what you will see. They were there at one point, but since the transaction was rolled back they are no longer there, and the identity seed is incremented.
|||Thanks.|||
Motley:
Identity column data is not guaranteed to be consecutive.
If the inserts were done in the context of a transaction, and the transaction is rolled back, then that is exactly what you will see. They were there at one point, but since the transaction was rolled back they are no longer there, and the identity seed is incremented.
I got the problem. There was a field in the table with varchar(7) datatype and if some one tried to insert a record and typed more than 7 characters in the textbox that insertes into this table column, the identity field would be incremented but nothing would actually be saved in the database. In my own opinion, i would say microsoft should have designed it in a way that if nothing is inserted due to such a problem, then let nothing be done on database as well. Incrementing the identity field even when no record has been inserted makes it harder to troubleshoot.
Wednesday, March 28, 2012
inserted and deleted tables
if i do an insert with 5 rows does inserted have 5 rows in it?
if htats the case how do you check field values for every row
I was using
if (select newfield from #inserted) = this
begin
update #inserted set newfield = that
end
but that isnt going to work if inserted contains all 5 rows, i thought
inserted only had the current row and it passed through the instead of
trigger 5 times once for each row. if thats not the case how do you do
something like
for each newfield in #inserted do
if newfield is this
set it to this.
for example
say i have an insert with three fields
category, categoryid, name
and the 3 rows in my insert are
('Standard', 1, 'Toys')
('NonStandard, null, 'Games')
('Misc', null, 'Puzzles')
and in my instead of trigger i want to fill the nulls with the proper
number so in my instead of trigger i say
if (field2 is null)
begin
set field2 = (select rightnumber from mastertable where name = field2)
end
but it has to do it for each rowChris M wrote:
> ok i know the rows being affected are put in these temporary tables,
> but if i do an insert with 5 rows does inserted have 5 rows in it?
> if htats the case how do you check field values for every row
> I was using
> if (select newfield from #inserted) = this
> begin
> update #inserted set newfield = that
> end
> but that isnt going to work if inserted contains all 5 rows, i thought
> inserted only had the current row and it passed through the instead of
> trigger 5 times once for each row. if thats not the case how do you
> do something like
> for each newfield in #inserted do
> if newfield is this
> set it to this.
> for example
> say i have an insert with three fields
> category, categoryid, name
> and the 3 rows in my insert are
> ('Standard', 1, 'Toys')
> ('NonStandard, null, 'Games')
> ('Misc', null, 'Puzzles')
> and in my instead of trigger i want to fill the nulls with the proper
> number so in my instead of trigger i say
> if (field2 is null)
> begin
> set field2 = (select rightnumber from mastertable where name = field2)
> end
> but it has to do it for each row
Yes. The inserted and deleted logical tables contain all affected rows.
No. You cannot modify data in the inserted and deleted tables, so I'm
not quite sure how your code was even executing. The tables do not have
a '#' prefix. The are plainly 'inserted' and 'deleted'.
How would you get the "right number" from mastertable is the second
column is NULL. What are you joining on?
Personally, I would just throw up a RAISERROR. I don't really understand
your test scenario. If you could join up with mastertable, then it seems
you should be using a FK to that table rather than repeating data
values.
Could you provide the DDL for the tables in question.
David Gugick
Imceda Software
www.imceda.com|||It will do it for each row in inserted, just write the expression on the
right of the set newfield =...
so that it will be a different value for each row of inserted...
update Table set newfield =
Case newfield
When 'this' Then 'That'
When 'TheOther' Then 'OtherThat'
End
But what is #Inserted? a Temporary Table?
What are you trying to update in this trigger?
"Chris M" wrote:
> ok i know the rows being affected are put in these temporary tables, but
> if i do an insert with 5 rows does inserted have 5 rows in it?
> if htats the case how do you check field values for every row
> I was using
> if (select newfield from #inserted) = this
> begin
> update #inserted set newfield = that
> end
> but that isnt going to work if inserted contains all 5 rows, i thought
> inserted only had the current row and it passed through the instead of
> trigger 5 times once for each row. if thats not the case how do you do
> something like
> for each newfield in #inserted do
> if newfield is this
> set it to this.
> for example
> say i have an insert with three fields
> category, categoryid, name
> and the 3 rows in my insert are
> ('Standard', 1, 'Toys')
> ('NonStandard, null, 'Games')
> ('Misc', null, 'Puzzles')
> and in my instead of trigger i want to fill the nulls with the proper
> number so in my instead of trigger i say
> if (field2 is null)
> begin
> set field2 = (select rightnumber from mastertable where name = field2)
> end
> but it has to do it for each row
>|||--BEGIN PGP SIGNED MESSAGE--
Hash: SHA1
Stop thinking procedure and start thinking sets. If there are 3 rows in
the inserted set and 2 have NULL in some column (as your example shows)
you can do an UPDATE like this:
UPDATE original_table
SET column_name = (select rightnumber
from mastertable m inner join inserted i
on m.<join cols> = i.<join cols> )
WHERE EXISTS (SELECT * FROM inserted
WHERE column_name IS NULL
AND inserted.ID = original_table.ID)
The "WHERE column_name IS NULL" in the UPDATE's WHERE clause subquery
will identify the rows in original_table that have the 'column_name' set
to the "rightnumber."
The <join cols> have to be a column, or columns, that uniquely identify
the rows in inserted that relate to rows in mastertable, so the
"rightnumber" can be retrieved. I would have to see the design of
mastertable and original_table to determine which columns those would
be. You could even do w/o the inserted set and just use something in
the mastertable that identifies which row in mastertable has the correct
data that is to be placed in the original_table. IOW, if you had a
Default value in mastertable that always goes in that column - data in
mastertable looks like this:
column_ rightnumber
-- --
Price 25
The SET subquery would look like this:
SET Price = (select rightnumber
from mastertable
where column_ = 'Price')
NB: By now you should realize that you can create a DEFAULT on the
column(s) in original_table instead of using a trigger like the above.
E.g.: CREATE TABLE T (col_1 int, col_a char(2) default ('zz'))
insert into t (col_1) values (2)
select * from t
col_1 col_a
-- --
2 zz
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
--BEGIN PGP SIGNATURE--
Version: PGP for Personal Privacy 5.0
Charset: noconv
iQA/ AwUBQmguqYechKqOuFEgEQJJSgCg8iAqLWvq7TwF
9BLQlhBbcY/uxx0AnRRi
DXmdayzhLIxU5WBk4wSL4x4R
=mDFy
--END PGP SIGNATURE--
Chris M wrote:
> ok i know the rows being affected are put in these temporary tables, but
> if i do an insert with 5 rows does inserted have 5 rows in it?
> if htats the case how do you check field values for every row
> I was using
> if (select newfield from #inserted) = this
> begin
> update #inserted set newfield = that
> end
> but that isnt going to work if inserted contains all 5 rows, i thought
> inserted only had the current row and it passed through the instead of
> trigger 5 times once for each row. if thats not the case how do you do
> something like
> for each newfield in #inserted do
> if newfield is this
> set it to this.
> for example
> say i have an insert with three fields
> category, categoryid, name
> and the 3 rows in my insert are
> ('Standard', 1, 'Toys')
> ('NonStandard, null, 'Games')
> ('Misc', null, 'Puzzles')
> and in my instead of trigger i want to fill the nulls with the proper
> number so in my instead of trigger i say
> if (field2 is null)
> begin
> set field2 = (select rightnumber from mastertable where name = field2)
> end
> but it has to do it for each row
>|||MGFoster wrote:
> --BEGIN PGP SIGNED MESSAGE--
> Hash: SHA1
> Stop thinking procedure and start thinking sets. If there are 3 rows in
> the inserted set and 2 have NULL in some column (as your example shows)
> you can do an UPDATE like this:
> UPDATE original_table
> SET column_name = (select rightnumber
> from mastertable m inner join inserted i
> on m.<join cols> = i.<join cols> )
> WHERE EXISTS (SELECT * FROM inserted
> WHERE column_name IS NULL
> AND inserted.ID = original_table.ID)
> The "WHERE column_name IS NULL" in the UPDATE's WHERE clause subquery
> will identify the rows in original_table that have the 'column_name' set
> to the "rightnumber."
> The <join cols> have to be a column, or columns, that uniquely identify
> the rows in inserted that relate to rows in mastertable, so the
> "rightnumber" can be retrieved. I would have to see the design of
> mastertable and original_table to determine which columns those would
> be. You could even do w/o the inserted set and just use something in
> the mastertable that identifies which row in mastertable has the correct
> data that is to be placed in the original_table. IOW, if you had a
> Default value in mastertable that always goes in that column - data in
> mastertable looks like this:
> column_ rightnumber
> -- --
> Price 25
> The SET subquery would look like this:
> SET Price = (select rightnumber
> from mastertable
> where column_ = 'Price')
> NB: By now you should realize that you can create a DEFAULT on the
> column(s) in original_table instead of using a trigger like the above.
> E.g.: CREATE TABLE T (col_1 int, col_a char(2) default ('zz'))
> insert into t (col_1) values (2)
> select * from t
> col_1 col_a
> -- --
> 2 zz
I can do that sometimes but if i want to set something like an id number
based on a column in another table that i cannot join on i can't do it
with a set
unless there is a command like
select * into #inserted from inserted
update #inserted
set keyfield = getNextValueFromTable()
insert into myTable select * from #inserted
but I cannot figure out how to get the getNextValueFromTable()
procedure since i cannot call stored procedures that way and UDF's
cannot access my table values
I am only doing this the way i'm doing it to maintain compatibility with
a program. If i was to design this myself I'd be doing it with
constraints, foreign keys, identities, etc|||Chris M wrote:
> MGFoster wrote:
>
< SNIP >
> I can do that sometimes[,] but if i want to set something like an id numbe
r
> based on a column in another table that i cannot join on i can't do it
> with a set
>
<SNIP >
--BEGIN PGP SIGNED MESSAGE--
Hash: SHA1
How can you know which "id number[...]in another table" to use if you
cannot join on it? That implies that there is "some other" way of
determining the relationship between one table and another; and, that
that relationship is defined outside the database. This goes against
RDB design principles.
If the "id number [is] based on a column in another table" that means
there is a relationship between the 2 tables. If there is a
relationship between the 2 tables you can join them.
So, what's going on there? ;-)
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
--BEGIN PGP SIGNATURE--
Version: PGP for Personal Privacy 5.0
Charset: noconv
iQA/ AwUBQmgyZYechKqOuFEgEQJd5QCfaDO4xXjYNt2S
oYbvwG9acyT4ncAAoPyO
BL03rAJrHegoe1ktC8L/pRBI
=8GJu
--END PGP SIGNATURE--|||What do you mean..
<snip> ...that i cannot join on ...</snip>
Why Not?
If the objective here is to insert some records into MyTable, then just do
that in the trigger
Insert MyTable
Select <Stuff>
From inserted
The <Stuff> above needs t oeb written as a set-based expression, (Set of
expressions), such that the values will be appropriate... But there's no way
for us to guess what that is until you tell us whjat you are trying to do
with getNextValueFromTable()...
again, if all you are tyrying to do is set the value based on the value in
the inserted table, then, as an example...
Insert MyTable
Select <OtherColumns>,
Case newField
When <ValueA> Then <outValueA>
When <ValueB> Then <outValueB>
When <ValueC> Then <outValueC>
When <ValueD> Then <outValueD>
Else <OutVAlueDefault> End
From inserted|||MGFoster wrote:
> Chris M wrote:
>
> < SNIP >
>
> <SNIP >
> --BEGIN PGP SIGNED MESSAGE--
> Hash: SHA1
> How can you know which "id number[...]in another table" to use if you
> cannot join on it? That implies that there is "some other" way of
> determining the relationship between one table and another; and, that
> that relationship is defined outside the database. This goes against
> RDB design principles.
> If the "id number [is] based on a column in another table" that means
> there is a relationship between the 2 tables. If there is a
> relationship between the 2 tables you can join them.
> So, what's going on there? ;-)
A table called generators
create table generators (
generator_name varchar(50),
generator_lastid integer
)
in my table say myTable if i want to get the next ID from generators i
have to
select generator_lastid from generators where generator_name =
'gen_id_mytable)
so i do not know how i can join on that, and I'm sure this does violate
some rule, but its meant to simulate the sequence/generator object of
oracle/interbase/firebird|||> if i do an insert with 5 rows does inserted have 5 rows in it?
Yes if the insert/update was done as a single statement.
> if htats the case how do you check field values for every row
> I was using
> if (select newfield from #inserted) = this
> begin
> update #inserted set newfield = that
> end
What is "this"? Is the idea to override the values being inserted/updated in
the
trigger? If that is the case, then you need an InsteadOf trigger not an Afte
r
trigger.
> but that isnt going to work if inserted contains all 5 rows, i thought
> inserted only had the current row and it passed through the instead of tri
gger
> 5 times once for each row. if thats not the case how do you do something like[/co
lor]
No. That is not the case. Each *statement* fires the trigger once (ignoring
cascades for the moment). Thus, imagine the statement:
Insert Table(F1...Fn)
Select F1...FN
From Table
That might insert 1000 records with that once statement. That statement will
fire the trigger once and populate the "inserted" table with 1000 records. I
f it
is an update, then you will get 1000 records in the "inserted" table and 100
0
records in the "deleted" table.
> for each newfield in #inserted do
> if newfield is this
> set it to this.
Can't do that with an After trigger. You need to do that with an InsteadOf
trigger.
> for example
> say i have an insert with three fields
> category, categoryid, name
> and the 3 rows in my insert are
> ('Standard', 1, 'Toys')
> ('NonStandard, null, 'Games')
> ('Misc', null, 'Puzzles')
> and in my instead of trigger i want to fill the nulls with the proper numb
er
> so in my instead of trigger i say
> if (field2 is null)
> begin
> set field2 = (select rightnumber from mastertable where name = field2)
> end
Create Table Stuff
(
Category VarChar(50) Not Null
, SomeNumber Int Null
, Description VarChar(50) Not Null
)
Create Table SomeOtherTable
(
SingleValue Int
)
Insert SomeOtherTable(SingleValue) Values(99)
Create Trigger trigStuff On dbo.Stuff
Instead Of Insert
As
Begin
Insert Stuff(Category, SomeNumber, Description)
Select Category
, (Select SingleValue From SomeOtherTable)
, Description
From inserted As I
End
Insert Stuff(Category, SomeNumber, Description) Values('Standard', 1, 'Toys'
)
Insert Stuff(Category, SomeNumber, Description) Values('NonStandard', Null,
'Games')
Insert Stuff(Category, SomeNumber, Description) Values('Misc', Null, 'Puzzle
s')
Select * From Stuff
HTH
Thomas|||Chris M wrote:
> MGFoster wrote:
>
>
> A table called generators
> create table generators (
> generator_name varchar(50),
> generator_lastid integer
> )
>
> in my table say myTable if i want to get the next ID from generators i
> have to
> select generator_lastid from generators where generator_name =
> 'gen_id_mytable)
> so i do not know how i can join on that, and I'm sure this does violate
> some rule, but its meant to simulate the sequence/generator object of
> oracle/interbase/firebird
--BEGIN PGP SIGNED MESSAGE--
Hash: SHA1
Ah... In that case you can just insert that value "generator_lastid"
into the NULL columns in the original table like this (this is in the
trigger):
UPDATE original_table
SET null_column = (SELECT generator_nextid FROM generators
WHERE generator_name = null_column_name)
WHERE id IN (SELECT id FROM inserted WHERE null_column IS NULL)
Substitute correct table/column names where appropriate.
Each row would get the same number. This won't work if you want
incrementing numbers in each row that had the NULL valued column. There
is no way to increment the nextid for the next call. A function can't
be used 'cuz ya can't run an UPDATE inside a function (to increment the
nextid). A procedure can't be used 'cuz ya can't use a procedure as a
recordsource, like ya can w/ a function.
Looks like (ugh!) a WHILE loop would have to be used to cycle thru all
the inserted rows that had NULL values in the column.
@.count = (select count(*) from inserted where column_name is null)
while @.count > 0 begin
-- do the update & generate new nextid
@.count = @.count - 1
end
Quite a problem.
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
--BEGIN PGP SIGNATURE--
Version: PGP for Personal Privacy 5.0
Charset: noconv
iQA/ AwUBQmhsb4echKqOuFEgEQL6JwCguTf9eD2kFh7F
fZDJAtnUhIKvKWwAniqR
xD1fe46JZ8B2jXX11NQRQmtJ
=xD8y
--END PGP SIGNATURE--
InsertCommand, add strings...
I am submitting a telephone number into a table. I have 3 boxes for the telephone number. Telephone1,Telephone2,Telephone3. I need to insert the values of the 3 text boxes into a column called phone in my table.
so like
InsertCommand="INSERT INTO customer_mod (phone) Values (@.Telephone_1)
<asp:formparameter name="Telephone_1" formfield="Telephone1+Telephone2+Telephone3" />
I don't think that is gonna work, so can you please help me make that code work?
I think you'll have to write some code for this. Write below code in the submit button click event.
string Telephone =string.Empty;
Telephone = Telephone1.Text +"-" + Telephone2.Text +"-" + Telephone3.Text;
SqlDataSource1.InsertParameters.Add("@.Telephone", Telephone);
SqlDataSource1.Insert();
Hope this will help.
That works, but then it does not submit any of my other data. I have data being inserted into the database using...
InsertCommand="INSERT INTO customer_mod (CsrAgent,FirstName,LastName,Address1,City1,State1,Zipcode1,InstallAddress,InstallCity,InstallState,InstallZip,CrossStreets,InstallDate,InstallTimes,InstallTimesOther)
When using this on the button...
string phone =string.Empty;phone = Telephone1.Text + Telephone2.Text + Telephone3.Text;
SqlDataSource1.InsertParameters.Add("@.phone", phone);SqlDataSource1.InsertCommand ="INSERT INTO [customer_mod](phone) values ('" + phone +"')";
SqlDataSource1.Insert();
Only the data is shown. So basically it is only accepting one insert. Can I not use insert from my aspx page and cs page?
|||This was just to demonstrate you how you can merge the telephone numbers. In order to pass all other values, you just need to pass the value for each column as a parameter. So your insert query would be like:
INSERT INTO customer_mod ( CsrAgent , FirstName , LastName , Address1 , City1 , State1 , Zipcode1 , InstallAddress , InstallCity , InstallState , InstallZip , CrossStreets , InstallDate , InstallTimes , InstallTimesOther )values ( @.CsrAgent , @.FirstName , @.LastName , @.Address1 , @.City1 , @.State1 , @.Zipcode1 , @.InstallAddress , @.InstallCity , @.InstallState , @.InstallZip , @.CrossStreets , @.InstallDate , @.InstallTimes , @.InstallTimesOther )
and you'll have to add the value for each of the above parameters just like you did for phone number. Get the value from the text box or any other control on your form and pass it to the value for that parameter.
InsertCommand using data from a second SqlDataSource - ASP.NET 2.0
I have a process that inserts a new record using the InsertCommand of aSqlDataSource. As part of the process, I need to insert data the is available in a different SqlDataSource. I was trying this with the Insert Parameter:
<asp:FormParameterName="Change_Title"FormField="Change_Title"/>
where Change_Title is available on screen. Doesn't work. Is this possible?
HI
Can you see if this post helps or gives you some idea of how to achieve it.
http://forums.asp.net/p/1124558/1766373.aspx#1766373
The post though gives a way to avoid the need for two SQLDatasources but use one to handle both level updates.
Hope this helps.
VJ
Insert/Updated SP from multiple tables
table that already exist with data using an insert or update stored procedure?
OR...
How do I write an insert/Update stored procedure that has multiple select
and a where something = something statements?
This is what I have so far and it do and insert and does work and I have no idea where to begin to do an update stored procedure like this...
CREATE PROCEDURE AddDrawStats
AS
INSERT Drawing (WinnersWon,TicketsPlayed,Players,RegisterPlayers)
SELECT
WinnersWon = (SELECT Count(*) FROM Winner W INNER JOIN DrawSetting DS ON W.DrawingID = DS.CurrentDrawing WHERE W.DrawingID = DS.CurrentDrawing),
TicketsPlayed = (SELECT Count(*) FROM Ticket T INNER JOIN Student S ON T.AccountID = S.AccountID WHERE T.Active = 1),
Players = (SELECT Count(*) FROM Ticket T INNER JOIN Student S ON T.AccountID = S.AccountID WHERE T.AccountID = S.AccountID ),
RegisterPlayers = (SELECT Count(*) FROM Student S WHERE S.AccountID = S.AccountID )
FROM DrawSetting DS INNER JOIN Drawing D ON DS.CurrentDrawing = D.DrawingID
WHERE D.DrawingID = DS.CurrentDrawing
GO"INNER JOIN DrawSetting DS ON W.DrawingID = DS.CurrentDrawing"
and
"WHERE W.DrawingID = DS.CurrentDrawing"
are redundant. They both accomplish the same thing; associating records in the two tables. Among SQL Server DBAs, the INNER JOIN syntax is preferred, so drop the links in your WHERE clauses.
As to your other issues, I'm sorry but the SQL statement you posted is too disjointed to figure out what your intentions are. You will need to describe your tables and your objective if you want more help, but embedding subqueries into the SELECT clause is rarely a good idea. I highly suspect that what your SQL statement describes is not really what you are trying to do.|||yes my attention is that I have Four related/non-related table and I would like to get some statistical data such as the count of how many student are in the student table, how many student are playing the current drawing, how many tickets are in the current drawing, and how many students won the current drawing. Setting up a common inner join would not allow me to get the exact data I need. Plus, I need to insert this data in the drawing table record that already have data but these fields are null. My stored procedure works somewhat, but it creates a new record; I want the stored procedure to insert this information in the record that already exist where drawing = the CurrentDrawing. So should I do an insert/update stored procedure, and how? All I need to see is an example of a stored procedure that insert or update data in some table where some criteria are met which comes from multiple select statements using different table within those select statement.|||This is what your SQL Statement describes, but again, I doubt that it is exactly what you want:
CREATE PROCEDURE AddDrawStats
AS
Declare @.Players int
Declare @.RegisterPlayers int
Declare @.TicketsPlayed int
set @.Players = (SELECT Count(*) FROM Ticket T INNER JOIN Student S ON T.AccountID = S.AccountID)
set @.RegisterPlayers = (SELECT Count(*) FROM Student)
set @.TicketsPlayed = (SELECT Count(*) FROM Ticket T INNER JOIN Student S ON T.AccountID = S.AccountID WHERE T.Active = 1),
Update Drawing
set WinnersWon = WinnersSubquery.WinnersWon,
TicketsPlayed = @.TicketsPlayed,
Players = @.Players,
RegisterPlayers = @.RegisterPlayers
from Drawing
inner join
(SELECT DS.CurrentDrawing, count(*) as WinnersWon
FROM DrawSetting DS
INNER JOIN Winner W on DS.CurrentDrawing = w.DrawingID
GROUP BY DS.CurrentDrawing) WinnersSubquery
on Drawing.DrawingID = WinnersSubquery.CurrentDrawing
INSERT INTO Drawing (DrawingID, WinnersWon, TicketsPlayed, Players, RegisterPlayers)
select WinnersSubquery.CurrentDrawing,
WinnersSubquery.WinnersWon,
@.TicketsPlayed,
@.Players,
@.RegisterPlayers
from (SELECT DS.CurrentDrawing, count(*) as WinnersWon
FROM DrawSetting DS
INNER JOIN Winner W on DS.CurrentDrawing = w.DrawingID
GROUP BY DS.CurrentDrawing) WinnersSubquery
left outer join Drawing on WinnersSubquery.CurrentDrawing = Drawing.DrawingID
where Drawing.DrawingID is null|||Thanks for all the help! This works but I have two questions?
Could I have written this Stored procedure better? and...
Why this statement yeilds the wrong results? *i.e.*each player can have up to five tickets in the ticket table, but this statement count each ticket as a player.How do I write this statement to get only unigue AccountID within the ticket table?
**SET @.Players = (SELECT Count(*) FROM Ticket T INNER JOIN Student S ON T.AccountID = S.AccountID)
CREATE PROCEDURE AddDrawStats2
AS
DECLARE @.WinnersWon INT
DECLARE @.TicketsPlayed INT
DECLARE @.Players INT
DECLARE @.RegisterPlayers INT
SET @.WinnersWon = (SELECT COUNT(*) FROM Winner W INNER JOIN DrawSetting DS ON W.DrawingID = DS.CurrentDrawing)
SET @.TicketsPlayed = (SELECT COUNT(*) FROM Ticket T WHERE T.Active = 1)
SET @.Players = (SELECT Count(*) FROM Ticket T INNER JOIN Student S ON T.AccountID = S.AccountID)
SET @.RegisterPlayers = (SELECT COUNT(*) FROM Student )
UPDATE Drawing
SET WinnersWon = @.WinnersWon,
TicketsPlayed = @.TicketsPlayed,
Players = @.Players,
RegisterPlayers = @.RegisterPlayers
WHERE DrawingID = (SELECT CurrentDrawing FROM DrawSetting)
GO|||SET @.Players = (SELECT Count(Distinct T.AccountID) FROM Ticket T INNER JOIN Student S ON T.AccountID = S.AccountID)|||Thanks so much for all the help. This stored procedure does the job, but you see any drawbacks?
CREATE PROCEDURE AddDrawStats
AS
DECLARE @.WinnersWon INT
DECLARE @.TicketsPlayed INT
DECLARE @.Players INT
DECLARE @.RegisterPlayers INT
SET @.WinnersWon = (SELECT COUNT(*) FROM Winner W INNER JOIN DrawSetting DS ON W.DrawingID = DS.CurrentDrawing)
SET @.TicketsPlayed = (SELECT COUNT(*) FROM Ticket T WHERE T.Active = 1)
SET @.Players =(SELECT Count(Distinct T.AccountID) FROM Ticket T WHERE T.Active = 1)
SET @.RegisterPlayers = (SELECT COUNT(*) FROM Student )
UPDATE Drawing
SET
WinnersWon = @.WinnersWon,
TicketsPlayed = @.TicketsPlayed,
Players = @.Players,
RegisterPlayers = @.RegisterPlayers
WHERE DrawingID = (SELECT CurrentDrawing FROM DrawSetting)
GO