INSERT INTO MainSearch (Date,Time,RemoteHost,Make,Model,PriceLower,PriceUpper) VALUES ('04/05/2004','12:35:49','127.0.0.1','AUDI','A6','1000','2000')
It crashes and says "Error in INSERT INTO statement", can anyone help?
Thanks
Below is the code used to process information provided from a form connected it :
Code:
'INSERTING INFO INTO THE VISITOR'S TABLE IN ATLAS when ALL info is needed - D is for Departure
'MAKE SURE to ENABLE ALL the passing-value formulas above for statement below to work properly
mycn.Execute "Insert into visitor " & _
"(lastname,firstname,affiliation,visitortype,a1line1,a1line2,a1city,a1state,a1zip," & _
"a1country,homephone,workphone,email,dob,citizenship,idtype,idnumber,idplace," & _
"minor1,m1dob,minor2,m2dob,minor3,m3dob,event1,voyage,event,CBY)" & _
"values(" & _
LastName & FirstName & affiliation & visitortype & a1line1 & a1line2 & a1city & a1state & a1zip & _
a1country & homephone & workphone & email & dob & citizenship & idtype & idnumber & idplace & _
minor1 & m1dob & minor2 & m2dob & minor3 & m3dob & event1 & voyage & "'D','WEB')"
set mycn = nothing
The code works correctly how it is setup here but it is no longer useful for this new form. The event variable, set as 'D' in the values section now has to be decided on outside of the insert statement and for some reason when I make the change in red below I get an unknown error:
Code:
'INSERTING INFO INTO THE VISITOR'S TABLE IN ATLAS when ALL info is needed - D is for Departure
'MAKE SURE to ENABLE ALL the passing-value formulas above for statement below to work properly
mycn.Execute "Insert into visitor " & _
"(lastname,firstname,affiliation,visitortype,a1line1,a1line2,a1city,a1state,a1zip," & _
"a1country,homephone,workphone,email,dob,citizenship,idtype,idnumber,idplace," & _
"minor1,m1dob,minor2,m2dob,minor3,m3dob,event1,voyage,event,CBY)" & _
"values(" & _
LastName & FirstName & affiliation & visitortype & a1line1 & a1line2 & a1city & a1state & a1zip & _
a1country & homephone & workphone & email & dob & citizenship & idtype & idnumber & idplace & _
minor1 & m1dob & minor2 & m2dob & minor3 & m3dob & event1 & voyage & event & "'WEB')"
set mycn = nothing
Hi all
insert into externaltemp(Cid,Keywords)
values(select id as Cid, f_name+','+ l_name as keywords from RPO.dbo.REQUIREMENT_RESOURCE)
Thanks in Advance.
Malathi Rao
here is my code:
Dim cn As New System.Data.SqlClient.SqlConnection(ConfigurationManager.ConnectionStrings("LocalSqlServer").ToString())
cn.Open()
Dim adapter1 As New System.Data.SqlClient.SqlDataAdapter()
adapter1.SelectCommand = New Data.SqlClient.SqlCommand("update aspnet_Membership_BasicAccess.Products
set id = '" & textid.Text & "', name = '" & textname.Text & "', price = '" & textprice.Text & "', description = '" &
textdescription.Text & "', count = '" & textcount.Text & "', pictureadd = '" & textpictureadd.Text & "', artist = '" &textartist.Text & "', catergory = '" & textcategory.text & "' where id = " & Request.Item("id") & ";", cn)
cn.Close()
Response.Redirect("database.aspx")
it posts and the page loads but the data is still the same in my datagrid. what could be wrong with this simple statement... i've tried testing the statement above with constant values of the correct type but i don't think that matters because the SqlCommand() accepts a string only anyways.. doesn't it?
I would like to INSERT an array of integer into a table in MSSQL Server, then count the number of rows in the table with c++ using ODBC. Here you find my code to do this task:
#include <windows.h>
#include <stdlib.h>
#include <stdio.h>
#include<tchar.h>
#include <sql.h>
#include <sqlext.h>
#include<sqltypes.h>
[Code] ....
In my code, I would like to Insert the array and then count the number of rows:
SQLTCHAR Statement[] = _T("INSERT INTO information1(Wert1,Wert2) VALUES(?,?) select count(*) as a from information1 ") ;
Problem : My expectation is, that first 9 rows are inserted into table then comes 9 as result to user (if the table is empty) but this code returns me 1 if first the table is empty. If the table is not empty, it returns 1+number of existing rows in the table. If I take a look inside the table, the 9 rows are successfully inserted in it. Only the number of rows in the table is wrong.
Hint : If I monitor the database using SQL Profiler. It looks like this:
Why this statement doesn't work correctly?
I am trying to write a stored procedure with an OR statement and can't get it to work. Using SQL Server 2000. (wish I had SQL 2005)
SELECT [C_CompanyName], [C_ID], [C_Email], [C_Phone], [C_SubExpireDate], [C_CompanyEmail] FROM [tblCompanies] WHERE ([C_Email] OR [C_CompanyEmail]) LIKE ('%' + @C_Email + '%')
Compilers tells me there is a problem near the OR
THank you
cmdSQL.Parameters.Add("@SlitWidth", System.Data.SqlDbType.Decimal).Value = IIf(slitwidth = "", System.DBNull.Value, cdec(slitwidth))
When I run the application, I get the following error:
Conversion from string "" to type 'Decimal' is not valid
I can't see where the problem is with this..
Thanks for any help!
declare @name nvarchar(30)
declare @Location nvarchar(30)
declare @nonbill float
declare @empType varchar(30)
select @location=location, @Name = name, @empType = employeetype, @nonbill = CASE cbetts.v_payrolltotals.jobno WHEN left(cbetts.v_payrolltotals.jobno,3) = '900' THEN sum(totals) ELSE 0 END from cbetts.v_payrolltotals
where userid = 1025
and we = '6/4/2004'
i am getting the error for the case statment.. "Incorrect syntax near '=', Incorrect syntax near the keyword 'END'.
This is under the "Trigger Properties" section.
CREATE TRIGGER [TRIGGER NAME] ON [dbo]
FOR INSERT, UPDATE, DELETE
AS
We have very little experience with this so any help will be appreciated. In fact we even tried to hire someone to come out and look at it.... Never called back...????
I need to do a count of the number of lines of record returned from the sub query below. However the server keeps giving an error something like this
syntax error FROM CLAUSE. pls help me as it is urgent.
SELECT COUNT(*) AS DP FROM
(SELECT MOBIX.FROM_COSG_ID, MOBIX.TO_COSG_ID
FROM Mobix
WHERE (((Mobix.CONSG_DO_NO)=[testing]))
GROUP BY Mobix.FROM_COSG_ID, Mobix.To_COSG_ID);
I actualy using SQL server 2005. I'm a stored procedure and it's accusing error near '=' that is in case clause. Do you know in what I'm wrong?
Select u.nick as 'user', l.nick as 'last_user', t.*
from threads t
inner join users u on t.user_id = u.user_id
inner join users l on t.last_user_id = l.user_id
where t.creation > @Period and forum_id=(@forum_id) and
case when @PageType = 'votacao' then t.votacao = 1 end,
case when @PageType = 'topicos' then t.votacao = 0 end,
case when @PageType = 'todos' then end
Thank you very much in advance.
<%@ Language="VBSCRIPT" %>
<!-- #include file="../includes/include.asp" -->
<%
connect.open connectString
Dim testSQL
testSQL = "INSERT INTO development (address) VALUES 'some address'"
connect.execute(testSQL)
%>
<% connect.close() %>
Any idea what could be wrong with this statement? It's causing the following error:
Microsoft OLE DB Provider for ODBC Drivers error '80040e14'
[Microsoft][ODBC Microsoft Access Driver] Syntax error in INSERT INTO statement.
/asa_dev/gm/dbTest.asp, line 10
Thanks,
Jason
Hi to every one
I have tow tables on sqlce server Trans & Transd
Trans is master (TTRID auto number)
TransD is child
Code Snippet
cmd.CommandText = "insert trans ([trdate],[trtype],[trempid],[trloc])" _
& "Values('" & LbTime.Text & "', '" & cboType.Text & "', '" & txtEmpID.Text _
& "', '" & txtRBar.Text & "')"
cmd.ExecuteNonQuery()
cmd.CommandText = " SELECT MAX(TTRID) AS TTRID FROM Trans"
rdr = cmd.ExecuteReader
'Here is aproblem
If rdr.Read Then MsgBox(rdr.Item("TTRID"))
LstAsset.SelectedIndex = 0
Do
cmd.CommandText = "insert transd ([ttrid],[asssn])" _
& "Values('" & rdr.Item("TTRID") & "', '" & LstAsset.Text & "')"
cmd.ExecuteNonQuery()
If LstAsset.SelectedIndex < LstAsset.Items.Count - 1 Then
LstAsset.SelectedIndex = LstAsset.SelectedIndex + 1
ElseIf LstAsset.SelectedIndex = LstAsset.Items.Count - 1 Then
Exit Do
End If
Loop
I try to read ttrid from reader but is say there is no Colum, but when i add if rdr.read it's wooooooooork.
Whey???
Hi,
can any body tell me what is wrong in my sql statement
SELECT title, price,
Budget = CASE price
WHEN price > 20.00 THEN 'Expensive'
WHEN price BETWEEN 10.00 AND 19.99 THEN 'Moderate'
WHEN price < 10.00 THEN 'Inexpensive'
ELSE 'Unknown'
END
FROM titles
it gives me this error
Msg 170, Level 15, State 1, Line 3
Line 3: Incorrect syntax near '>'.
but when i use somthing like that i will works fine
SELECT
Budget
i am using sql server 2000
I am getting an error Msg 8155, Level 16, State 2, Procedure usp_ImportNewASC_ParticipantPlanFundBalances, Line 1No column was specified for column 49 of 'a'.I think its something to do with the line number 48 and 102 ALLOC_PER is char(9) so i want to to cast it as a int and insert into the prodction data. 1 UPDATE
2 Statements..ParticipantPlanFundBalances1
3 SET
4 Act1 = ACT_ID1,
5 TotAct1 = TOT_ACT1,
6 Act2 = ACT_ID2,
7 TotAct2 = TOT_ACT2,
8 Act3 = ACT_ID3,
9 TotAct3 = TOT_ACT3,
10 Act4 = ACT_ID4,
11 TotAct4 = TOT_ACT4,
12 Act5 = ACT_ID5,
13 TotAct5 = TOT_ACT5,
14 Act6 = ACT_ID6,
15 TotAct6 = TOT_ACT6,
16 Act7 = ACT_ID7,
17 TotAct7 = TOT_ACT7,
18 Act8 = ACT_ID8,
19 TotAct8 = TOT_ACT8,
20 Act9 = ACT_ID9,
21 TotAct9 = TOT_ACT9,
22 Act10 = ACT_ID10,
23 TotAct10 = TOT_ACT10,
24 Act11 = ACT_ID11,
25 TotAct11 = TOT_ACT11,
26 Act12 = ACT_ID12,
27 TotAct12 = TOT_ACT12,
28 Act13 = ACT_ID13,
29 TotAct13 = TOT_ACT13,
30 Act14 = ACT_ID14,
31 TotAct14 = TOT_ACT14,
32 Act15 = ACT_ID15,
33 TotAct15 = TOT_ACT15,
34 Act16 = ACT_ID16,
35 TotAct16 = TOT_ACT16,
36 Act17 = ACT_ID17,
37 TotAct17 = TOT_ACT17,
38 Act18 = ACT_ID18,
39 TotAct18 = TOT_ACT18,
40 Act19 = ACT_ID19,
41 TotAct19 = TOT_ACT19,
42 Act20 = ACT_ID20,
43 TotAct20 = TOT_ACT20,
44 OpeningUnits = UNIT_OP,
45 OPricePerUnit = PRICE_OP,
46 ClosingUnits = UNIT_CL,
47 CPricePerUnit = PRICE_CL,
48 AllocationPercent = i.ALLOC_PER1
49
50 FROM
51 Statements..ParticipantPlanFundBalances1 pfb
52 JOIN (
53 Select
54 cp.PlanId,
55 p.ParticipantId,
56 @PeriodId Period,
57 f.FundId,
58 a.ACT_ID1,
59 a.TOT_ACT1,
60 a.ACT_ID2,
61 a.TOT_ACT2,
62 a.ACT_ID3,
63 a.TOT_ACT3,
64 a.ACT_ID4,
65 a.TOT_ACT4,
66 a.ACT_ID5,
67 a.TOT_ACT5,
68 a.ACT_ID6,
69 a.TOT_ACT6,
70 a.ACT_ID7,
71 a.TOT_ACT7,
72 a.ACT_ID8,
73 a.TOT_ACT8,
74 a.ACT_ID9,
75 a.TOT_ACT9,
76 a.ACT_ID10,
77 a.TOT_ACT10,
78 a.ACT_ID11,
79 a.TOT_ACT11,
80 a.ACT_ID12,
81 a.TOT_ACT12,
82 a.ACT_ID13,
83 a.TOT_ACT13,
84 a.ACT_ID14,
85 a.TOT_ACT14,
86 a.ACT_ID15,
87 a.TOT_ACT15,
88 a.ACT_ID16,
89 a.TOT_ACT16,
90 a.ACT_ID17,
91 a.TOT_ACT17,
92 a.ACT_ID18,
93 a.TOT_ACT18,
94 a.ACT_ID19,
95 a.TOT_ACT19,
96 a.ACT_ID20,
97 a.TOT_ACT20,
98 a.UNIT_OP,
99 a.PRICE_OP,
100 a.UNIT_CL,
101 a.PRICE_CL,
102 Cast(i.ALLOC_PER1 as int)
103 FROM
104 ASDBF a
105 -- Derive the unique PlanId from the Statements ClientPlan table
106 INNER JOIN Statements..ClientPlan cp
107 ON a.PLAN_NUM = cp.ClientPlanId
108 AND
109 cp.ClientId = @ClientId
110 -- Derive the unique ParticipantId from the Statements Participant table
111 INNER JOIN Statements..Participant p
112 ON a.PART_ID = p.PartId
113 --Derive the unique FundID from the Statements Fund Table...
114 INNER JOIN Statements..Fund f
115 ONa.FUND_ID = f.Cusip
116 OR
117 a.FUND_ID = f.Ticker
118 OR
119 a.FUND_ID = f.ClientFundId
120 -- get the allocation percent from the INVSRC
121 LEFT Outer JOIN INVSRC i
122 ONa.FUND_ID = i.INV_ID
123 AND
124 a.PLAN_NUM = i.Plan_Number
125 AND
126 a.PART_ID = i.PART_ID
127 --INNER JOIN Statements..AscActCodes as
128 --ON
129
130 WHERE
131 a.Import = 1
132 )a
133 ON pfb.PlanId = a.PlanId
134 AND
135 pfb.ParticipantId = a.ParticipantId
136 AND
137 pfb.PeriodId = PeriodId
138 AND
139 pfb.FundId = a.FundId
140
Any help will be appreciated
Regards
Karen
select name from child order by name
union all
select aname from agenerate order by aname
Malathi Rao
Query analyser is saying there is an error near the WHERE keyword:
INSERT INTO EMPLOYEE_TABLE (BANK_ACC, SORT_CODE)
VALUES (63035391,603030)
WHERE 40 IN (SELECT EMPLOY_REF FROM EMPLOYEE_TABLE);
Thanks people.
Hello well-intentionned reader,
I'm having trouble figuring out what's wrong with my statement. This is what the table looks like. I want to display employees under the manager with the last name King. my statements are below the tableName Null? Type
----------------------------------------- -------- ----------------------------
EMPLOYEE_ID NOT NULL NUMBER(6)
LAST_NAME NOT NULL VARCHAR2(25)
SALARY NUMBER(8,2)
MANAGER_ID NUMBER(6)
My statement
SQL> SELECT last_name, salary
FROM employees
WHERE manager_id =
(SELECT employee_id
FROM employees
WHERE EMPLOYEE.LAST_NAME = .King.); 2 3 4 5 6
WHERE EMPLOYEE.LAST_NAME = .King.)
*
ERROR at line 6:
ORA-00936: missing expression
Thank you for your time.
/gozu
When I attempt to run the following select statement in Aqua I get 2 errors:
- Incorrect syntax near the keyword 'SELECT'.
- Incorrect syntax near ')'.
I belive they both refer to the creation of the subquery (i.e. "From (" and ") t" ) but I can't for the life of me discover anything wrong with the statement. If you run the multiple union subquery by itself, it works just fine. (In case your wondering, the eventual the purpose of creating a sub query is so that I can use the UnPivot statement)
SELECT DATE_DAY
, METRIC
, NUM_OF_TICKETS
, SEVERITY
, EQ_MARKET_CLUSTER
--Performance
, NUM_DISPATCH_TO
, DURATION_DISPATCH_TO
, SLA_DURATION_DISPATCH_TO
, SLA_DURATION_DISPATCH_TO_G_L
, NUM_DISPATCH_FROM
, DURATION_DISPATCH_FROM
, SLA_DURATION_DISPATCH_FROM
, SLA_DURATION_DISPATCH_FROM_G_L
, NUM_MTTR
, DURATION_MTTR
, SLA_MTTR
, SLA_MTTR_G_L
, COUNT_CLOSED_MET_DUE_DATE
, NUM_CLOSED_MET_DUE_DATE
--Costs
, OVERTIME
, COUNT_NTF
, NUM_DISPATCH_NTF
, NUM_CTS_DISPATCH
, TICKET_NOT_DISPATCHED
, REMOTE_RESTORAL
--Productivity
, NUM_TICKET_RATIO
, NUM_TECHS_RATIO
, NUM_DISPATCH_TICKET_RATIO
--FMS
, NUM_MET_DISPATCH_TO
, MET_SLA_DISPATCH_TO
, NUM_MET_DISPATCH_FROM
, MET_SLA_DISPATCH_FROM
, NUM_WIP
, DURATION_WIP_TO_RESOLVE
, SLA_WIP_TO_RESOLVE
, SLA_WIP_TO_RESOLVE_G_L
, NUM_MET_WIP
, MET_SLA_WIP_TO_RESOLVE
, NUM_MET_MTTR
, MET_SLA_MTTR
, COUNT_RESOLVE_FD
, NUM_RESOLVE_FD
--Action Reports
, AR_NUM_CREATED
, AR_NUM_CLOSED
, AR_NUM_OPEN
, COUNT_METTING_DUE_DATE
, NUM_OPEN_IN_DUE_DATE
--All Tickets
, COUNT_DISPATCHED
, NUM_AWAITING_DISPATCH
, NUM_IN_JEOPARDY
FROM (
--FMS
SELECT DATE_DAY
, CASE
WHEN upper(ENTRY_TYPE) = 'ACTION REPORT' THEN
(CASE WHEN PROBLEM_CATEGORY IS NULL THEN NULL WHEN PROBLEM_CATEGORY IN ('Compliance','Routine') THEN PROBLEM_CATEGORY ELSE 'Other' END)
WHEN upper(ENTRY_TYPE) IN ( 'TROUBLE REPORT','NET REPORT' ) THEN
(CASE WHEN SEVERITY IN ( 'Critical','Major' ) THEN SEVERITY ELSE 'Other' END)
ELSE SEVERITY END AS SEVERITY
, EQ_MARKET_CLUSTER
--Performance
, sum( C_NUM_DISPATCH_TO ) AS NUM_DISPATCH_TO
, sum( C_DURATION_DISPATCH_TO ) AS DURATION_DISPATCH_TO
, max( C_SLA_DURATION_DISPATCH_TO ) AS SLA_DURATION_DISPATCH_TO
, max( C_SLA_DURATION_DISPATCH_TO_G_L ) AS SLA_DURATION_DISPATCH_TO_G_L
, sum( C_NUM_DISPATCH_FROM ) AS NUM_DISPATCH_FROM
, sum( C_DURATION_DISPATCH_FROM ) AS DURATION_DISPATCH_FROM
, max( C_SLA_DURATION_DISPATCH_FROM ) AS SLA_DURATION_DISPATCH_FROM
, max( C_SLA_DURATION_DISPATCH_FROM_G_L ) AS SLA_DURATION_DISPATCH_FROM_G_L
, sum( NUM_CLOSED ) AS NUM_MTTR
, sum( DURATION_MTTR ) AS DURATION_MTTR
, max( SLA_MTTR ) AS SLA_MTTR
, max( SLA_MTTR_G_L ) AS SLA_MTTR_G_L
, NULL AS COUNT_CLOSED_MET_DUE_DATE
, NULL AS NUM_CLOSED_MET_DUE_DATE
--Costs
, 0 AS OVERTIME
, sum( NUM_CLOSED ) AS COUNT_NTF
, sum( NUM_DISPATCH_NTF ) AS NUM_DISPATCH_NTF
, NULL AS NUM_CTS_DISPATCH
, NULL AS TICKET_NOT_DISPATCHED
, 0 AS REMOTE_RESTORAL
--Productivity
, NULL AS NUM_TICKET_RATIO
, NULL AS NUM_TECHS_RATIO
, NULL AS NUM_DISPATCH_TICKET_RATIO
--FMS
, sum( C_NUM_DISPATCH_TO ) AS NUM_MET_DISPATCH_TO
, sum( C_MET_SLA_DISPATCH_TO ) AS MET_SLA_DISPATCH_TO
, sum( C_NUM_DISPATCH_FROM ) AS NUM_MET_DISPATCH_FROM
, sum( C_MET_SLA_DISPATCH_FROM ) AS MET_SLA_DISPATCH_FROM
, sum( NUM_CLOSED ) AS NUM_WIP
, sum( DURATION_WIP_TO_RESOLVE ) AS DURATION_WIP_TO_RESOLVE
, max( SLA_WIP_TO_RESOLVE ) AS SLA_WIP_TO_RESOLVE
, max( SLA_WIP_TO_RESOLVE_G_L ) AS SLA_WIP_TO_RESOLVE_G_L
, sum( NUM_CLOSED ) AS NUM_MET_WIP
, sum( MET_SLA_WIP_TO_RESOLVE ) AS MET_SLA_WIP_TO_RESOLVE
, sum( NUM_CLOSED ) AS NUM_MET_MTTR
, sum( MET_SLA_MTTR ) AS MET_SLA_MTTR
, sum( NUM_CLOSED ) AS COUNT_RESOLVE_FD
, sum( NUM_RESOLVE_FD ) AS NUM_RESOLVE_FD
--Action Reports
, NULL AS AR_NUM_CREATED
, NULL AS AR_NUM_CLOSED
, NULL AS AR_NUM_OPEN
, NULL AS COUNT_METTING_DUE_DATE
, NULL AS NUM_OPEN_IN_DUE_DATE
--All Tickets
, sum( NUM_DISPATCH_FROM ) AS COUNT_DISPATCHED
, sum( NUM_AWAITING_DISPATCH ) AS NUM_AWAITING_DISPATCH
, sum( CASE WHEN upper(FORCE_STATUS) = 'JEOPARDY' THEN NUM_OPEN ELSE NULL END ) AS NUM_IN_JEOPARDY
FROM CTS.dbo.NDC_Scorecard_Summary_Table t
WHERE DATE_DAY >= cast(convert(varchar(8),getdate()-1,1) as datetime)
AND DATE_DAY < cast(convert(varchar(8),getdate(),1) as datetime)
AND ENTRY_TYPE IN ('Trouble Report')
AND EQ_REGION IN ( 'NSD', 'NORTHEAST', 'CENTRAL', 'SOUTHEAST', 'WEST' )
AND EQ_MARKET_CLUSTER IN ( 'NEW ENGLAND','GEORGIA' )
AND SEVERITY IN ( 'Critical','Major' )
GROUP BY DATE_DAY
, CASE
WHEN upper(ENTRY_TYPE) = 'ACTION REPORT' THEN
(CASE WHEN PROBLEM_CATEGORY IS NULL THEN NULL WHEN PROBLEM_CATEGORY IN ('Compliance','Routine') THEN PROBLEM_CATEGORY ELSE 'Other' END)
WHEN upper(ENTRY_TYPE) IN ( 'TROUBLE REPORT','NET REPORT' ) THEN
(CASE WHEN SEVERITY IN ( 'Critical','Major' ) THEN SEVERITY ELSE 'Other' END)
ELSE SEVERITY END-- AS SEVERITY
, EQ_MARKET_CLUSTER
UNION
--AR
SELECT DATE_DAY
, CASE
WHEN upper(ENTRY_TYPE) = 'ACTION REPORT' THEN
(CASE WHEN PROBLEM_CATEGORY IS NULL THEN NULL WHEN PROBLEM_CATEGORY IN ('Compliance','Routine') THEN PROBLEM_CATEGORY ELSE 'Other' END)
WHEN upper(ENTRY_TYPE) IN ( 'TROUBLE REPORT','NET REPORT' ) THEN
(CASE WHEN SEVERITY IN ( 'Critical','Major' ) THEN SEVERITY ELSE 'Other' END)
ELSE SEVERITY END AS SEVERITY
, EQ_MARKET_CLUSTER
--Performance
, NULL AS NUM_DISPATCH_TO
, NULL AS DURATION_DISPATCH_TO
, NULL AS SLA_DURATION_DISPATCH_TO
, NULL AS SLA_DURATION_DISPATCH_TO_G_L
, NULL AS NUM_DISPATCH_FROM
, NULL AS DURATION_DISPATCH_FROM
, NULL AS SLA_DURATION_DISPATCH_FROM
, NULL AS SLA_DURATION_DISPATCH_FROM_G_L
, NULL AS NUM_MTTR
, NULL AS DURATION_MTTR
, NULL AS SLA_MTTR
, NULL AS SLA_MTTR_G_L
, sum( NUM_CLOSED ) AS COUNT_CLOSED_MET_DUE_DATE
, sum( AR_NUM_MET_DUE_DATE ) AS NUM_CLOSED_MET_DUE_DATE
--Costs
, NULL AS OVERTIME
, NULL AS COUNT_NTF
, NULL AS NUM_DISPATCH_NTF
, NULL AS NUM_CTS_DISPATCH
, NULL AS TICKET_NOT_DISPATCHED
, NULL AS REMOTE_RESTORAL
--Productivity
, NULL AS NUM_TICKET_RATIO
, NULL AS NUM_TECHS_RATIO
, NULL AS NUM_DISPATCH_TICKET_RATIO
--FMS
, NULL AS NUM_MET_DISPATCH_TO
, NULL AS MET_SLA_DISPATCH_TO
, NULL AS NUM_MET_DISPATCH_FROM
, NULL AS MET_SLA_DISPATCH_FROM
, NULL AS NUM_WIP
, NULL AS DURATION_WIP_TO_RESOLVE
, NULL AS SLA_WIP_TO_RESOLVE
, NULL AS SLA_WIP_TO_RESOLVE_G_L
, NULL AS NUM_MET_WIP
, NULL AS MET_SLA_WIP_TO_RESOLVE
, NULL AS NUM_MET_MTTR
, NULL AS MET_SLA_MTTR
, NULL AS COUNT_RESOLVE_FD
, NULL AS NUM_RESOLVE_FD
--Action Reports
, NULL AS AR_NUM_CREATED
, NULL AS AR_NUM_CLOSED
, NULL AS AR_NUM_OPEN
, sum( NUM_OPEN ) AS COUNT_METTING_DUE_DATE
, sum( NUM_OPEN_IN_DUE_DATE ) AS NUM_OPEN_IN_DUE_DATE
--All Tickets
, sum( NUM_DISPATCH_FROM ) AS COUNT_DISPATCHED
, sum( NUM_AWAITING_DISPATCH ) AS NUM_AWAITING_DISPATCH
, sum( CASE WHEN upper(FORCE_STATUS) = 'JEOPARDY' THEN NUM_OPEN ELSE NULL END ) AS NUM_IN_JEOPARDY
FROM CTS.dbo.NDC_Scorecard_Summary_Table t
WHERE DATE_DAY >= cast(convert(varchar(8),getdate()-1,1) as datetime)
AND DATE_DAY < cast(convert(varchar(8),getdate(),1) as datetime)
AND ENTRY_TYPE IN ('Action Report')
AND EQ_REGION IN ( 'NSD', 'NORTHEAST', 'CENTRAL', 'SOUTHEAST', 'WEST' )
AND EQ_MARKET_CLUSTER IN ( 'NEW ENGLAND','GEORGIA' )
GROUP BY DATE_DAY
, CASE
WHEN upper(ENTRY_TYPE) = 'ACTION REPORT' THEN
(CASE WHEN PROBLEM_CATEGORY IS NULL THEN NULL WHEN PROBLEM_CATEGORY IN ('Compliance','Routine') THEN PROBLEM_CATEGORY ELSE 'Other' END)
WHEN upper(ENTRY_TYPE) IN ( 'TROUBLE REPORT','NET REPORT' ) THEN
(CASE WHEN SEVERITY IN ( 'Critical','Major' ) THEN SEVERITY ELSE 'Other' END)
ELSE SEVERITY END-- AS SEVERITY
, EQ_MARKET_CLUSTER
UNION
--CTS
SELECT CTS_DATE AS DATE_DAY
, CASE WHEN SEVERITY IN ( 'Critical','Major' ) THEN SEVERITY ELSE 'Other' END AS SEVERITY
, EQ_MARKET_CLUSTER
--Performance
, NULL AS NUM_DISPATCH_TO
, NULL AS DURATION_DISPATCH_TO
, NULL AS SLA_DURATION_DISPATCH_TO
, NULL AS SLA_DURATION_DISPATCH_TO_G_L
, NULL AS NUM_DISPATCH_FROM
, NULL AS DURATION_DISPATCH_FROM
, NULL AS SLA_DURATION_DISPATCH_FROM
, NULL AS SLA_DURATION_DISPATCH_FROM_G_L
, NULL AS NUM_MTTR
, NULL AS DURATION_MTTR
, NULL AS SLA_MTTR
, NULL AS SLA_MTTR_G_L
, NULL AS COUNT_CLOSED_MET_DUE_DATE
, NULL AS NUM_CLOSED_MET_DUE_DATE
--Costs
, NULL AS OVERTIME
, NULL AS COUNT_NTF
, NULL AS NUM_DISPATCH_NTF
, sum( Closed_Tickets_External_NotDispatched + Closed_Tickets_NOC_NotDispatched ) AS NUM_CTS_DISPATCH
, sum( Closed_Tickets_NOC_NotDispatched ) AS TICKET_NOT_DISPATCHED
, NULL AS REMOTE_RESTORAL
--Productivity
, NULL AS NUM_TICKET_RATIO
, NULL AS NUM_TECHS_RATIO
, NULL AS NUM_DISPATCH_TICKET_RATIO
--FMS
, NULL AS NUM_MET_DISPATCH_TO
, NULL AS MET_SLA_DISPATCH_TO
, NULL AS NUM_MET_DISPATCH_FROM
, NULL AS MET_SLA_DISPATCH_FROM
, NULL AS NUM_WIP
, NULL AS DURATION_WIP_TO_RESOLVE
, NULL AS SLA_WIP_TO_RESOLVE
, NULL AS SLA_WIP_TO_RESOLVE_G_L
, NULL AS NUM_MET_WIP
, NULL AS MET_SLA_WIP_TO_RESOLVE
, NULL AS NUM_MET_MTTR
, NULL AS MET_SLA_MTTR
, NULL AS COUNT_RESOLVE_FD
, NULL AS NUM_RESOLVE_FD
--Action Reports
, NULL AS AR_NUM_CREATED
, NULL AS AR_NUM_CLOSED
, NULL AS AR_NUM_OPEN
, NULL AS COUNT_METTING_DUE_DATE
, NULL AS NUM_OPEN_IN_DUE_DATE
--All Tickets
, NULL AS COUNT_DISPATCHED
, NULL AS NUM_AWAITING_DISPATCH
, NULL AS NUM_IN_JEOPARDY
FROM CTS.dbo.FMS_Daily_Summary_Regional
WHERE CTS_DATE >= cast(convert(varchar(8),getdate()-1,1) as datetime)
AND CTS_DATE <= cast(convert(varchar(8),getdate(),1) as datetime)
AND upper( EQ_REGION ) IN ( 'NSD', 'NORTHEAST', 'CENTRAL', 'SOUTHEAST', 'WEST' )
AND EQ_MARKET_CLUSTER IN ( 'NEW ENGLAND','GEORGIA' )
GROUP BY CTS_DATE-- AS DATE_DAY
, CASE WHEN SEVERITY IN ( 'Critical','Major' ) THEN SEVERITY ELSE 'Other' END-- AS SEVERITY
, EQ_MARKET_CLUSTER
UNION
--Tickets Resolved per Tech
SELECT DATE_DAY
, ENTRY_TYPE AS SEVERITY
, EQ_MARKET_CLUSTER
--Performance
, NULL AS NUM_DISPATCH_TO
, NULL AS DURATION_DISPATCH_TO
, NULL AS SLA_DURATION_DISPATCH_TO
, NULL AS SLA_DURATION_DISPATCH_TO_G_L
, NULL AS NUM_DISPATCH_FROM
, NULL AS DURATION_DISPATCH_FROM
, NULL AS SLA_DURATION_DISPATCH_FROM
, NULL AS SLA_DURATION_DISPATCH_FROM_G_L
, NULL AS NUM_MTTR
, NULL AS DURATION_MTTR
, NULL AS SLA_MTTR
, NULL AS SLA_MTTR_G_L
, NULL AS COUNT_CLOSED_MET_DUE_DATE
, NULL AS NUM_CLOSED_MET_DUE_DATE
--Costs
, NULL AS OVERTIME
, NULL AS COUNT_NTF
, NULL AS NUM_DISPATCH_NTF
, NULL AS NUM_CTS_DISPATCH
, NULL AS TICKET_NOT_DISPATCHED
, NULL AS REMOTE_RESTORAL
--Productivity
, sum( NUM_OF_TICKETS ) AS NUM_CLOSED_TICKET_RATIO
, sum( NUM_OF_TECHS ) AS NUM_CLOSED_TECHS_RATIO
, 0 AS NUM_DISPATCH_TICKET_RATIO
--FMS
, NULL AS NUM_MET_DISPATCH_TO
, NULL AS MET_SLA_DISPATCH_TO
, NULL AS NUM_MET_DISPATCH_FROM
, NULL AS MET_SLA_DISPATCH_FROM
, NULL AS NUM_WIP
, NULL AS DURATION_WIP_TO_RESOLVE
, NULL AS SLA_WIP_TO_RESOLVE
, NULL AS SLA_WIP_TO_RESOLVE_G_L
, NULL AS NUM_MET_WIP
, NULL AS MET_SLA_WIP_TO_RESOLVE
, NULL AS NUM_MET_MTTR
, NULL AS MET_SLA_MTTR
, NULL AS COUNT_RESOLVE_FD
, NULL AS NUM_RESOLVE_FD
--Action Reports
, NULL AS AR_NUM_CREATED
, NULL AS AR_NUM_CLOSED
, NULL AS AR_NUM_OPEN
, NULL AS COUNT_METTING_DUE_DATE
, NULL AS NUM_OPEN_IN_DUE_DATE
--All Tickets
, NULL AS COUNT_DISPATCHED
, NULL AS NUM_AWAITING_DISPATCH
, NULL AS NUM_IN_JEOPARDY
FROM CTS.dbo.vw_CLOSED_TICKET_TO_TECH_RATIO
WHERE DATE_DAY >= cast(convert(varchar(8),getdate()-1,1) as datetime)
AND DATE_DAY < cast(convert(varchar(8),getdate(),1) as datetime)
AND EQ_REGION IN ( 'NSD', 'NORTHEAST', 'CENTRAL', 'SOUTHEAST', 'WEST' )
AND EQ_MARKET_CLUSTER IN ( 'NEW ENGLAND','GEORGIA' )
GROUP BY DATE_DAY
, ENTRY_TYPE-- AS SEVERITY
, EQ_MARKET_CLUSTER
UNION
--Tickets Resolved per Tech - Total
SELECT DATE_DAY
, 'Total' AS SEVERITY
, EQ_MARKET_CLUSTER
--Performance
, NULL AS NUM_DISPATCH_TO
, NULL AS DURATION_DISPATCH_TO
, NULL AS SLA_DURATION_DISPATCH_TO
, NULL AS SLA_DURATION_DISPATCH_TO_G_L
, NULL AS NUM_DISPATCH_FROM
, NULL AS DURATION_DISPATCH_FROM
, NULL AS SLA_DURATION_DISPATCH_FROM
, NULL AS SLA_DURATION_DISPATCH_FROM_G_L
, NULL AS NUM_MTTR
, NULL AS DURATION_MTTR
, NULL AS SLA_MTTR
, NULL AS SLA_MTTR_G_L
, NULL AS COUNT_CLOSED_MET_DUE_DATE
, NULL AS NUM_CLOSED_MET_DUE_DATE
--Costs
, NULL AS OVERTIME
, NULL AS COUNT_NTF
, NULL AS NUM_DISPATCH_NTF
, NULL AS COUNT_RFD
, NULL AS NUM_CTS_DISPATCH
, NULL AS TICKET_NOT_DISPATCHED
--Productivity
, sum( NUM_OF_TICKETS ) AS NUM_TICKET_RATIO
, sum( NUM_OF_TECHS ) AS NUM_TECHS_RATIO
, 0 AS NUM_DISPATCH_TICKET_RATIO
--FMS
, NULL AS NUM_MET_DISPATCH_TO
, NULL AS MET_SLA_DISPATCH_TO
, NULL AS NUM_MET_DISPATCH_FROM
, NULL AS MET_SLA_DISPATCH_FROM
, NULL AS NUM_WIP
, NULL AS DURATION_WIP_TO_RESOLVE
, NULL AS SLA_WIP_TO_RESOLVE
, NULL AS SLA_WIP_TO_RESOLVE_G_L
, NULL AS NUM_MET_WIP
, NULL AS MET_SLA_WIP_TO_RESOLVE
, NULL AS NUM_MET_MTTR
, NULL AS MET_SLA_MTTR
, NULL AS COUNT_RESOLVE_FD
, NULL AS NUM_RESOLVE_FD
--Action Reports
, NULL AS AR_NUM_CREATED
, NULL AS AR_NUM_CLOSED
, NULL AS AR_NUM_OPEN
, NULL AS COUNT_METTING_DUE_DATE
, NULL AS NUM_OPEN_IN_DUE_DATE
--All Tickets
, NULL AS COUNT_DISPATCHED
, NULL AS NUM_AWAITING_DISPATCH
, NULL AS NUM_IN_JEOPARDY
FROM CTS.dbo.vw_CLOSED_TICKET_TO_TECH_RATIO
WHERE DATE_DAY >= cast(convert(varchar(8),getdate()-1,1) as datetime)
AND DATE_DAY < cast(convert(varchar(8),getdate(),1) as datetime)
AND EQ_REGION IN ( 'NSD', 'NORTHEAST', 'CENTRAL', 'SOUTHEAST', 'WEST' )
AND EQ_MARKET_CLUSTER IN ( 'NEW ENGLAND','GEORGIA' )
GROUP BY DATE_DAY
, EQ_MARKET_CLUSTER
--AR Count
SELECT DATE_DAY
, 'All Severities' AS SEVERITY
, EQ_MARKET_CLUSTER
--Performance
, NULL AS NUM_DISPATCH_TO
, NULL AS DURATION_DISPATCH_TO
, NULL AS SLA_DURATION_DISPATCH_TO
, NULL AS SLA_DURATION_DISPATCH_TO_G_L
, NULL AS NUM_DISPATCH_FROM
, NULL AS DURATION_DISPATCH_FROM
, NULL AS SLA_DURATION_DISPATCH_FROM
, NULL AS SLA_DURATION_DISPATCH_FROM_G_L
, NULL AS NUM_MTTR
, NULL AS DURATION_MTTR
, NULL AS SLA_MTTR
, NULL AS SLA_MTTR_G_L
, NULL AS COUNT_CLOSED_MET_DUE_DATE
, NULL AS NUM_CLOSED_MET_DUE_DATE
--Costs
, NULL AS OVERTIME
, NULL AS COUNT_NTF
, NULL AS NUM_DISPATCH_NTF
, NULL AS COUNT_RFD
, NULL AS NUM_CTS_DISPATCH
, NULL AS TICKET_NOT_DISPATCHED
--Productivity
, NULL AS NUM_TICKET_RATIO
, NULL AS NUM_TECHS_RATIO
, NULL AS NUM_DISPATCH_TICKET_RATIO
--FMS
, NULL AS NUM_MET_DISPATCH_TO
, NULL AS MET_SLA_DISPATCH_TO
, NULL AS NUM_MET_DISPATCH_FROM
, NULL AS MET_SLA_DISPATCH_FROM
, NULL AS NUM_WIP
, NULL AS DURATION_WIP_TO_RESOLVE
, NULL AS SLA_WIP_TO_RESOLVE
, NULL AS SLA_WIP_TO_RESOLVE_G_L
, NULL AS NUM_MET_WIP
, NULL AS MET_SLA_WIP_TO_RESOLVE
, NULL AS NUM_MET_MTTR
, NULL AS MET_SLA_MTTR
, NULL AS COUNT_RESOLVE_FD
, NULL AS NUM_RESOLVE_FD
--Action Reports
, sum( NUM_CREATED ) AS AR_NUM_CREATED
, sum( NUM_CLOSED ) AS AR_NUM_CLOSED
, sum( NUM_OPEN ) AS AR_NUM_OPEN
, NULL AS COUNT_METTING_DUE_DATE
, NULL AS NUM_OPEN_IN_DUE_DATE
--All Tickets
, NULL AS COUNT_DISPATCHED
, NULL AS NUM_AWAITING_DISPATCH
, NULL AS NUM_IN_JEOPARDY
FROM CTS.dbo.NDC_Scorecard_Summary_Table t
WHERE DATE_DAY >= cast(convert(varchar(8),getdate()-1,1) as datetime)
AND DATE_DAY < cast(convert(varchar(8),getdate(),1) as datetime)
AND ENTRY_TYPE IN ('Action Report')
AND EQ_REGION IN ( 'NSD', 'NORTHEAST', 'CENTRAL', 'SOUTHEAST', 'WEST' )
AND EQ_MARKET_CLUSTER IN ( 'NEW ENGLAND','GEORGIA' )
GROUP BY DATE_DAY
, CASE
WHEN upper(ENTRY_TYPE) = 'ACTION REPORT' THEN
(CASE WHEN PROBLEM_CATEGORY IS NULL THEN NULL WHEN PROBLEM_CATEGORY IN ('Compliance','Routine') THEN PROBLEM_CATEGORY ELSE 'Other' END)
WHEN upper(ENTRY_TYPE) IN ( 'TROUBLE REPORT','NET REPORT' ) THEN
(CASE WHEN SEVERITY IN ( 'Critical','Major' ) THEN SEVERITY ELSE 'Other' END)
ELSE SEVERITY END-- AS SEVERITY
, EQ_MARKET_CLUSTER
) t
hello,
I have wriiten this query to select all records from those tables in a database that have "to_be_transffered" column where this "to_be_transferred_column" is not null
--code
EXEC sp_MSForEachTable 'IF EXISTS(SELECT c.table_name, c.column_name
FROM information_schema.columns c
INNER JOIN information_schema.tables t
ON c.table_name = t.table_name
WHERE c.table_name = SUBSTRING(''?'', (CHARINDEX(''['',''?'', 2) + 1),
((LEN(''?'') - (CHARINDEX(''['',''?'', 2) + 1))))
AND c.column_name = ''to_be_transferred''
AND t.table_type = ''BASE TABLE''
)
BEGIN
SELECT * FROM ?
WHERE to_be_transferred IS NOT NULL
END'
But I am getting the following error
Msg 207, Level 16, State 3, Line 12
Invalid column name 'to_be_transferred'
I thought the if statement filters all those tables with the specified column and do select statement fot those tables only
what did I do wrong, any suggestionm?
Thanks
The query is (Select (25/20*100))
The wrong giving result is 100 it's should be 125
How I can use a query statement to get a correct result?
I wrote this update statement and it looks right but I keep getting an error that says "incorrect syntax next to c" what did I do wrong?
Here is the statement:
UPDATE tblclients c
SET intproviderId = (SELECT TOP 1 t.inttransferid FROM tbltransfer t WHERE t.intclientids = c.intclientid ORDER BY t.inttransferid DESC)
The following sql statement is rejected by asp.net (working with sqlserver). It says something is wrong near "TableFlights". But I don'tsee anything wrong with it.Any help is appreciated:SELECT TableFlights.IdFlight, Concat = (TableFlights.OrganizerLastName+ ', ' + TableFlights.OrganizerFirstName + ' Flt:' +TableFlights.FlightNumber + ' on ' +Convert(char(10,TableFlights.FlightDate,101)) FROM TableFlights INNERJOIN TablePassengers ON TableFlights.IdFlight=TablePassengers.IdFlightWhere TablePassengers.Email = 'marvin@micro-net.com' Order ByTableFlights.OrganizerLastName, TableFlights.OrganizerFirstName-- Marvin
View 1 Replies View RelatedhelloI have strange problem.I write to MySQL data to table with one column of varchar(8000). I write750000 bytes, so it get 93 records of 8000 bytes and the last - 6000bytes.but what is strange - when I check length of this records:select len(column_name) from table_namethis last record ( which is 6000bytes length ) isn't on 94 position, but ...on 14 position !?The same, when I try to reads recods:select column name from table namethis 6000 record apear on 14 position instead on 94 positionIt's important to me to get this record on proper ( last) position to notchange ( complicating ) my C++ application algoritm.Could someone help me, pleasethanks in advanceAdam
View 4 Replies View RelatedHi frdz, I m the new user of ASP.NET WEB APPLICATIONS WITH C# LANGUAGE. I m using SQL SERVER 2005 and SQLDATASOURCE to get or retrieve the data from the database. I have created the stored procedure for insert and update. The stored procedure is executing fine when i m running it from sqlserver2005. My problem is with the web-application page. I m not able to insert or update data thru that... Pls check the code and tell me what's missing out ..........SQLDATASOURCE<asp:SqlDataSource ID="srcemp" runat="server" ConnectionString="<%$ ConnectionStrings:empmaster %>" InsertCommand="empStoredProcedure" InsertCommandType="StoredProcedure" UpdateCommand="empStoredProcedure" UpdateCommandType="StoredProcedure" DeleteCommand="DELETE FROM empmaster WHERE empid = @empid" DeleteCommandType="Text" SelectCommand="select * from empmaster " SelectCommandType="Text"> <InsertParameters><asp:Parameter Name="empname" Type="String" /><asp:Parameter Name="address" Type="String" /><asp:Parameter Name="city" /><asp:Parameter Name="pincode" /><asp:Parameter Name="state" /></InsertParameters> <UpdateParameters><asp:Parameter Name="empname" Type="String" /><asp:Parameter Name="address" Type="String" /><asp:Parameter Name="city" /><asp:Parameter Name="pincode" /><asp:Parameter Name="state" /></UpdateParameters> </asp:SqlDataSource>GRIDVIEW <asp:GridView ID="empGridView" runat="server" AutoGenerateColumns="False" DataKeyNames="empid" DataSourceID="srcemp" Width="56px" > <Columns> <asp:TemplateField> <ItemTemplate> <asp:LinkButton ID="btnDelete" runat="server" CommandName="Delete" OnClientClick="return confirm('Are you sure you want to delete this row ?');">Delete</asp:LinkButton> </ItemTemplate> </asp:TemplateField> <asp:boundfield datafield="empname" headertext="emp Name"/> <asp:boundfield datafield="address" headertext="Address"/> <asp:boundfield datafield="city" headertext="City"/> <asp:boundfield datafield="state" headertext="State"/> <asp:CheckBoxField DataField="deleted" HeaderText="In Existance" /> </Columns> </asp:GridView>C# code protected void cmdsubmit_Click(object sender, EventArgs e) { srcemp.InsertParameters["empname"].DefaultValue = tbcompanyname.Text; srcemp.InsertParameters["address"].DefaultValue = tbaddress.Text; srcemp.InsertParameters["city"].DefaultValue = tbcity.Text; srcemp.InsertParameters["pincode"].DefaultValue = tbpincode.Text; srcemp.InsertParameters["state"].DefaultValue = cmbstate.SelectedItem.ToString(); srcemp.Insert();}Note :I m not getting a single error msg for the above code in web-page or stored procedure but it does not insert,update or delete the record from the database....Thanxs in adv...pls reply at earliest if possible...What's missing ??can anyone check out..what's wrong ??
View 1 Replies View RelatedHi,I have a field: usercode [tinyint]In Query Analyzer:UPDATE tblUserProcessSET usercode = 1002Result: Error "Arithmetic overflow error for data type tinyint, value = 1002.The statement has been terminated."In VBA/Access ( linked to SQL Server ):intOptions = 512pstrQuerySQL = "UPDATE ..."CurrentDb.Execute pstrQuerySQL, intOptionsResult: no errors, insert value 223 (???)Why?Thanks, Eugene
View 6 Replies View RelatedI've have a need with SQL Server 2005 (so I've no MERGE statement), I have to merge 2 tables, the target table has 10 fields, the first 4 are the clustered index and primary key, the source table has the same fields and index.Since I can't use the MERGE statement (I'm in SQL 2005) I have to make a double step operation, and INSERT and an UPDATE, I can't figure how to design the WHERE condition for the insert statement.
View 2 Replies View RelatedAn insert statement was not inserting all the data into a table. Found it very strange as the other fields in the row were inserted. I ran SQL profiler and found that sql statement had all the fields in the insert statement but some of the fields were not inserted. Below is the sql statement which is created dyanmically by a asp.net C# class. The columns which are not inserted are 'totaltax' and 'totalamount' ...while the 'shipto_name' etc...were inserted.there were not errors thrown. The sql from the code cannot be shown here as it is dynamically built referencing C# class files.It works fine on another test database which uses the same dlls. The only difference i found was the difference in date formats..@totalamount=1625.62,@totaltax=125.62are not inserted into the database.Below is the statement copied from SQL profiler.exec sp_executesql N'INSERT INTO salesorder(billto_city, billto_country, billto_line1, billto_line2, billto_name,billto_postalcode, billto_stateorprovince, billto_telephone, contactid, CreatedOn, customerid, customeridtype,DeletionStateCode, discountamount, discountpercentage, ModifiedOn, name, ordernumber,pricelevelid, salesorderId, shipto_city, shipto_country,shipto_line1, shipto_line2, shipto_name, shipto_postalcode, shipto_stateorprovince,shipto_telephone, StateCode, submitdate, totalamount,totallineitemamount, totaltax ) VALUES(@billto_city, @billto_country, @billto_line1, @billto_line2,@billto_name, @billto_postalcode, @billto_stateorprovince, @billto_telephone, @contactid, @CreatedOn, @customerid,@customeridtype, @DeletionStateCode, @discountamount,@discountpercentage, @ModifiedOn, @name, @ordernumber, @pricelevelid, @salesorderId,@shipto_city, @shipto_country, @shipto_line1, @shipto_line2,@shipto_name, @shipto_postalcode, @shipto_stateorprovince, @shipto_telephone,@StateCode, @submitdate, @totalamount, @totallineitemamount, @totaltax)',N'@billto_city nvarchar(8),@billto_country nvarchar(13),@billto_line1 nvarchar(3),@billto_line2 nvarchar(4),@billto_name nvarchar(15),@billto_postalcode nvarchar(5),@billto_stateorprovince nvarchar(8),@billto_telephone nvarchar(3),@contactid uniqueidentifier,@CreatedOn datetime,@customerid uniqueidentifier,@customeridtype int,@DeletionStateCode int,@discountamount decimal(1,0),@discountpercentage decimal(1,0),@ModifiedOn datetime,@name nvarchar(33),@ordernumber nvarchar(18),@pricelevelid uniqueidentifier,@salesorderId uniqueidentifier,@shipto_city nvarchar(8),@shipto_country nvarchar(13),@shipto_line1 nvarchar(3),@shipto_line2 nvarchar(4),@shipto_name nvarchar(15),@shipto_postalcode nvarchar(5),@shipto_stateorprovince nvarchar(8),@shipto_telephone nvarchar(3),@StateCode int,@submitdate datetime,@totalamount decimal(6,2),@totallineitemamount decimal(6,2),@totaltax decimal(5,2)',@billto_city=N'New York',@billto_country=N'United States',@billto_line1=N'454',@billto_line2=N'Road',@billto_name=N'Hillary Clinton',@billto_postalcode=N'10001',@billto_stateorprovince=N'New York',@billto_telephone=N'124',@contactid='8DAFE298-3A25-42EE-B208-0B79DE653B61',@CreatedOn=''2008-04-18 13:37:12:013'',@customerid='8DAFE298-3A25-42EE-B208-0B79DE653B61',@customeridtype=2,@DeletionStateCode=0,@discountamount=0,@discountpercentage=0,@ModifiedOn=''2008-04-18 13:37:12:013'',@name=N'E-Commerce Order (Before billing)',@ordernumber=N'BRKV-CC-OKRW5764YS',@pricelevelid='B74DB28B-AA8F-DC11-B289-000423B63B71',@salesorderId='9CD0E11A-5A6D-4584-BC3E-4292EBA6ED24',@shipto_city=N'New York',@shipto_country=N'United States',@shipto_line1=N'454',@shipto_line2=N'Road',@shipto_name=N'Hillary Clinton',@shipto_postalcode=N'10001',@shipto_stateorprovince=N'New York',@shipto_telephone=N'124',@StateCode=0,@submitdate=''2008-04-18 14:37:10:140'',@totalamount=1625.62,@totallineitemamount=1500.00,@totaltax=125.62
thanks
Hello
I have a problem with setting relations properly when inserting data using adonet. Already have searched for a solutions, still not finding a mistake...
Here's the sql management studio diagram :
and here goes the code1 DataSet ds = new DataSet();
2
3 SqlDataAdapter myCommand1 = new SqlDataAdapter("select * from SurveyTemplate", myConnection);
4 SqlCommandBuilder cb = new SqlCommandBuilder(myCommand1);
5 myCommand1.FillSchema(ds, SchemaType.Source);
6 DataTable pTable = ds.Tables["Table"];
7 pTable.TableName = "SurveyTemplate";
8 myCommand1.InsertCommand = cb.GetInsertCommand();
9 myCommand1.InsertCommand.Connection = myConnection;
10
11 SqlDataAdapter myCommand2 = new SqlDataAdapter("select * from Question", myConnection);
12 cb = new SqlCommandBuilder(myCommand2);
13 myCommand2.FillSchema(ds, SchemaType.Source);
14 pTable = ds.Tables["Table"];
15 pTable.TableName = "Question";
16 myCommand2.InsertCommand = cb.GetInsertCommand();
17 myCommand2.InsertCommand.Connection = myConnection;
18
19 SqlDataAdapter myCommand3 = new SqlDataAdapter("select * from Possible_Answer", myConnection);
20 cb = new SqlCommandBuilder(myCommand3);
21 myCommand3.FillSchema(ds, SchemaType.Source);
22 pTable = ds.Tables["Table"];
23 pTable.TableName = "Possible_Answer";
24 myCommand3.InsertCommand = cb.GetInsertCommand();
25 myCommand3.InsertCommand.Connection = myConnection;
26
27 ds.Relations.Add(new DataRelation("FK_Question_SurveyTemplate", ds.Tables["SurveyTemplate"].Columns["id"], ds.Tables["Question"].Columns["surveyTemplateID"]));
28 ds.Relations.Add(new DataRelation("FK_Possible_Answer_Question", ds.Tables["Question"].Columns["id"], ds.Tables["Possible_Answer"].Columns["questionID"]));
29
30 DataRow dr = ds.Tables["SurveyTemplate"].NewRow();
31 dr["name"] = o[0];
32 dr["description"] = o[1];
33 dr["active"] = 1;
34 ds.Tables["SurveyTemplate"].Rows.Add(dr);
35
36 DataRow dr1 = ds.Tables["Question"].NewRow();
37 dr1["questionIndex"] = 1;
38 dr1["questionContent"] = "q1";
39 dr1.SetParentRow(dr);
40 ds.Tables["Question"].Rows.Add(dr1);
41
42 DataRow dr2 = ds.Tables["Possible_Answer"].NewRow();
43 dr2["answerIndex"] = 1;
44 dr2["answerContent"] = "a11";
45 dr2.SetParentRow(dr1);
46 ds.Tables["Possible_Answer"].Rows.Add(dr2);
47
48 dr1 = ds.Tables["Question"].NewRow();
49 dr1["questionIndex"] = 2;
50 dr1["questionContent"] = "q2";
51 dr1.SetParentRow(dr);
52 ds.Tables["Question"].Rows.Add(dr1);
53
54 dr2 = ds.Tables["Possible_Answer"].NewRow();
55 dr2["answerIndex"] = 1;
56 dr2["answerContent"] = "a21";
57 dr2.SetParentRow(dr1);
58 ds.Tables["Possible_Answer"].Rows.Add(dr2);
59
60 dr2 = ds.Tables["Possible_Answer"].NewRow();
61 dr2["answerIndex"] = 2;
62 dr2["answerContent"] = "a22";
63 dr2.SetParentRow(dr1);
64 ds.Tables["Possible_Answer"].Rows.Add(dr2);
65
66 myCommand1.Update(ds,"SurveyTemplate");
67 myCommand2.Update(ds, "Question");
68 myCommand3.Update(ds, "Possible_Answer");
69 ds.AcceptChanges();
70
and that causes (at line 67):"The INSERT statement conflicted with the FOREIGN KEY constraint
"FK_Question_SurveyTemplate". The conflict occurred in database
"ankietyzacja", table "dbo.SurveyTemplate", column
'id'.
The statement has been terminated.
at System.Data.Common.DbDataAdapter.UpdatedRowStatusErrors(RowUpdatedEventArgs rowUpdatedEvent, BatchCommandInfo[] batchCommands, Int32 commandCount)
at System.Data.Common.DbDataAdapter.UpdatedRowStatus(RowUpdatedEventArgs rowUpdatedEvent, BatchCommandInfo[] batchCommands, Int32 commandCount)
at System.Data.Common.DbDataAdapter.Update(DataRow[] dataRows, DataTableMapping tableMapping)
at System.Data.Common.DbDataAdapter.UpdateFromDataTable(DataTable dataTable, DataTableMapping tableMapping)
at System.Data.Common.DbDataAdapter.Update(DataSet dataSet, String srcTable)
at AnkietyzacjaWebService.Service1.createSurveyTemplate(Object[] o) in J:\PL\PAI\AnkietyzacjaWebService\AnkietyzacjaWebServicece\Service1.asmx.cs:line 397"
Could You please tell me what am I missing here ?
Thanks a lot.
This problem is being seen on SQL 2005 SP2 + cumulative update 4
I am currently successfully using the output clause of an insert statement to return the identity values for inserted rows into a table variable
I now need to add an "instead of insert" trigger to the table that is the subject of the insert.
As soon as I add the "instead of insert" trigger, the output clause on the insert statement does not return any data - although the insert completes successfully. As a result I am not able to obtain the identities of the inserted rows
Note that @@identity would return the correct value in the test repro below - but this is not a viable option as the table in question will be merge replicated and @@identity will return the identity value of a replication metadata table rather than the identity of the row inserted into my_table
Note also that in the test repro, the "instead of insert" trigger actually does nothing apart from the default insert, but the real world trigger has additional code.
To run the repro below - select each of the sections below in turn and execute them
1) Create the table
2) Create the trigger
3) Do the insert - note that table variable contains a row with column value zero - it should contain the @@identity value
4) Drop the trigger
5) Re-run the insert from 3) - note that table variable is now correctly populated with the @@identity value in the row
I need the behaviour to be correct when the trigger is present
Any thoughts would be much appreciated
aero1
/************************************************
1) - Create the table
************************************************/
CREATE TABLE [dbo].[my_table](
[my_table_id] [bigint] IDENTITY(1,1) NOT NULL,
[forename] [varchar](100) NULL,
[surname] [varchar](50) NULL,
CONSTRAINT [pk_my_table] PRIMARY KEY NONCLUSTERED
(
[my_table_id] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF, FILLFACTOR = 70) ON [PRIMARY]
)
GO
/************************************************
2) - Create the trigger
************************************************/
CREATE TRIGGER [dbo].[trig_my_table__instead_insert] ON [dbo].[my_table]
INSTEAD OF INSERT
AS
BEGIN
INSERT INTO my_table
(
forename,
surname)
SELECT
forename,
surname
FROM inserted
END
/************************************************
3) - Do the insert
************************************************/
DECLARE @my_insert TABLE( my_table_id bigint )
declare @forename VARCHAR(100)
declare @surname VARCHAR(50)
set @forename = N'john'
set @surname = N'smith'
INSERT INTO my_table (
forename
, surname
)
OUTPUT inserted.my_table_id INTO @my_insert
VALUES( @forename
, @surname
)
select @@identity -- expect this value in @my_insert table
select * from @my_insert -- OK value without trigger - zero with trigger
/************************************************
4) - Drop the trigger
************************************************/
drop trigger [dbo].[trig_my_table__instead_insert]
go
/************************************************
5) - Re-run insert from 3)
************************************************/
-- @my_insert now contains row expected with identity of inserted row
-- i.e. OK
Hi All,
I've looked through the forum hoping I'm not the only one with this issue but alas, I have found nothing so I'm hoping someone out there will give me some assistance.
My problem is the case statement in my Insert Statement. My overall goal is to insert records from one table to another. But I need to be able to assign a specific value to the incoming data and thought the case statement would be the best way of doing it. I must be doing something wrong but I can't seem to see it.
Here is my code:
Insert into myTblA
(TblA_ID,
mycasefield =
case
when mycasefield = 1 then 99861
when mycasefield = 2 then 99862
when mycasefield = 3 then 99863
when mycasefield = 4 then 99864
when mycasefield = 5 then 99865
when mycasefield = 6 then 99866
when mycasefield = 7 then 99867
when mycasefield = 8 then 99868
when mycasefield = 9 then 99855
when mycasefield = 10 then 99839
end,
alt_min,
alt_max,
longitude,
latitude
(
Select MTB.LocationID
MTB.model_ID
MTB.elevation, --alt min
null, --alt max
MTB.longitude, --longitude
MTB.latitude --latitude
from MyTblB MTB
);
The error I'm getting is:
Incorrect syntax near '='.
I have tried various versions of the case statement based on examples I have found but nothing works.
I would greatly appreciate any assistance with this one. I've been smacking my head against the wall for awhile trying to find a solution.
In the below code i want to use select statement for getting customer
address1,customeraddress2,customerphone,customercity,customerstate,customercountry,customerfirstname,customerlastname
from customer table.Rest of the things will be as it is in the following code.How do i do this?
INSERT INTO EMImportListing ("
sql += " CustId,Title,Description,JobCity,JobState,JobPostalCode,JobCountry,URL,Requirements, "
sql += " IsDraft,IsFeatured,IsApproved,"
sql += " Email,OrgName,customerAddress1,customerAddress2,customerCity,customerState,customerPostalCode,
[code]....
Im trying to insert a record in my sql server 2005 express database.The following function tries that and without an error returns true.However, no data is inserted into the database...Im not sure whether my insert statement is correct: I saw other example with syntax: insert into table values(@value1,@value2)....so not sure about thatAlso, I havent defined the parameter type (eg varchar) but I reckoned that could not make the difference....Here's my code: Function CreateNewUser(ByVal UserName As String, ByVal Password As String, _ ByVal Email As String, ByVal Gender As Integer, _ ByVal FirstName As String, ByVal LastName As String, _ ByVal CellPhone As String, ByVal Street As String, _ ByVal StreetNumber As String, ByVal StreetAddon As String, _ ByVal Zipcode As String, ByVal City As String, _ ByVal Organization As String _ ) As Boolean 'returns true with success, false with failure Dim MyConnection As SqlConnection = GetConnection() Dim bResult As Boolean Dim MyCommand As New SqlCommand("INSERT INTO tblUsers(UserName,Password,Email,Gender,FirstName,LastName,CellPhone,Street,StreetNumber,StreetAddon,Zipcode,City,Organization) VALUES(@UserName,@Password,@Email,@Gender,@FirstName,@LastName,@CellPhone,@Street,@StreetNumber,@StreetAddon,@Zipcode,@City,@Organization)", MyConnection) MyCommand.Parameters.Add(New SqlParameter("@UserName", SqlDbType.NChar, UserName)) MyCommand.Parameters.Add(New SqlParameter("@Password", Password)) MyCommand.Parameters.Add(New SqlParameter("@Email", Email)) MyCommand.Parameters.Add(New SqlParameter("@Gender", Gender)) MyCommand.Parameters.Add(New SqlParameter("@FirstName", FirstName)) MyCommand.Parameters.Add(New SqlParameter("@LastName", LastName)) MyCommand.Parameters.Add(New SqlParameter("@CellPhone", CellPhone)) MyCommand.Parameters.Add(New SqlParameter("@Street", Street)) MyCommand.Parameters.Add(New SqlParameter("@StreetNumber", StreetNumber)) MyCommand.Parameters.Add(New SqlParameter("@StreetAddon", StreetAddon)) MyCommand.Parameters.Add(New SqlParameter("@Zipcode", Zipcode)) MyCommand.Parameters.Add(New SqlParameter("@City", City)) MyCommand.Parameters.Add(New SqlParameter("@Organization", Organization)) Try MyConnection.Open() MyCommand.ExecuteNonQuery() bResult = True Catch ex As Exception bResult = False Finally MyConnection.Close() End Try Return bResult End FunctionThanks!
View 1 Replies View Related