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
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
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?
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
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 Related
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?
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 Relatedi can't seem to get this query to work, it just keep returning nulls with ever values i set . 1 SELECT Bedrooms, Description, Image,
2 (SELECT Location
3 FROM Location_Table
4 WHERE (Property_Table.LocationID = LocationID)) AS Location, LocationID, Price, Price AS PriceMax, PropertyID, Title, TypeID,
5 (SELECT TypeOfProperty
6 FROM Type_Table
7 WHERE (Property_Table.LocationID = TypeID)) AS TypeOfProperty
8 FROM Property_Table
9 WHERE (TypeID = @TypeID OR
10 TypeID IS NULL) AND (LocationID = @LocationID OR
11 LocationID IS NULL) AND (Price >= @MinPrice OR
12 Price IS NULL) AND (PriceMax <= @MaxPrice OR
13 PriceMax IS NULL)
This is working:
SELECT...
"CAST(MONTH(Some_Date) as int) as Month, " &_
"CAST(DAY(Some_Date) as int) as Day " &_
"FROM Deceased " &_
"WHERE Active = 1 AND " &_
"MONTH(Some_Date) >= MONTH(GETDATE()) " &_
"ORDER BY Month, Day DESC"
This is NOT:
SELECT...
"CAST(MONTH(Some_Date) as int) as Month, " &_
"CAST(DAY(Some_Date) as int) as Day " &_
"FROM Deceased " &_
"WHERE Active = 1 AND " &_
Month >= MONTH(GETDATE()) " &_
"ORDER BY Month, Day DESC"
it says - Invalid column name 'Month'
Why ? Why ? Why ?
I'm just learning SQL after using it for about a year now and I'm trying to add a Check constraint to a Social Security Field (See Below) and I can't figure out what is wrong with the syntax. In QA it errors out stating: Line 4: Incorrect syntax near '0-9'.
use Accounting
Alter Table Employees
Add Constraint CK_SNN
Check (SSN Like [0-9][0-9][0-9]-[0-9][0-9]-[0-9][0-9][0-9][0-9])
Any help would be nice. Thanks in advance.
Hello !! I have just createt a simple login page and reg page, login is working when I make one useraccound directly on to MsSql server, I can login successfully, but the problem is REGISTER PAGE with INSERT code. Here down is the code ov the login.aspx page
Function AddUser(ByVal userID As Integer, ByVal userName As String, ByVal userPassword As String, ByVal name As String, ByVal email As String) As Integer
Dim connectionString As String = "server='(local)'; trusted_connection=true; database='music'"
Dim dbConnection As System.Data.IDbConnection = New System.Data.SqlClient.SqlConnection(connectionString)
Dim queryString As String = "INSERT INTO [users] ([UserID], [UserName], [UserPassword], [Name], [Email]) VALUE"& _
"S (@UserID, @UserName, @UserPassword, @Name, @Email)"
Dim dbCommand As System.Data.IDbCommand = New System.Data.SqlClient.SqlCommand
dbCommand.CommandText = queryString
dbCommand.Connection = dbConnection
Dim dbParam_userID As System.Data.IDataParameter = New System.Data.SqlClient.SqlParameter
dbParam_userID.ParameterName = "@UserID"
dbParam_userID.Value = userID
dbParam_userID.DbType = System.Data.DbType.Int32
dbCommand.Parameters.Add(dbParam_userID)
Dim dbParam_userName As System.Data.IDataParameter = New System.Data.SqlClient.SqlParameter
dbParam_userName.ParameterName = "@UserName"
dbParam_userName.Value = userName
dbParam_userName.DbType = System.Data.DbType.String
dbCommand.Parameters.Add(dbParam_userName)
Dim dbParam_userPassword As System.Data.IDataParameter = New System.Data.SqlClient.SqlParameter
dbParam_userPassword.ParameterName = "@UserPassword"
dbParam_userPassword.Value = userPassword
dbParam_userPassword.DbType = System.Data.DbType.String
dbCommand.Parameters.Add(dbParam_userPassword)
Dim dbParam_name As System.Data.IDataParameter = New System.Data.SqlClient.SqlParameter
dbParam_name.ParameterName = "@Name"
dbParam_name.Value = name
dbParam_name.DbType = System.Data.DbType.String
dbCommand.Parameters.Add(dbParam_name)
Dim dbParam_email As System.Data.IDataParameter = New System.Data.SqlClient.SqlParameter
dbParam_email.ParameterName = "@Email"
dbParam_email.Value = email
dbParam_email.DbType = System.Data.DbType.String
dbCommand.Parameters.Add(dbParam_email)
Dim rowsAffected As Integer = 0
dbConnection.Open
Try
rowsAffected = dbCommand.ExecuteNonQuery
Finally
dbConnection.Close
End Try
Return rowsAffected
End Function
Sub LoginBtn_Click(sender As Object, e As EventArgs)
If AddUser(txtUserName.Text, txtUserPassword.Text, txtName.Text, txtEmail.Text) > 0
Message.Text = "Register Successed, click on the link WebCam for login"
Else
Message.Text = "Failure"
End If
End Sub
and here is the error I receive when I try to open this register.aspx page:
Description: An error occurred during the compilation of a resource required to service this request. Please review the following specific error details and modify your source code appropriately.
Compiler Error Message: BC30455: Argument not specified for parameter 'email' of 'Public Function AddUser(userID As Integer, userName As String, userPassword As String, name As String, email As String) As Integer'.
Source Error:
Line 52: Sub LoginBtn_Click(sender As Object, e As EventArgs)
Line 53:
Line 54: If AddUser(txtUserName.Text, txtUserPassword.Text, txtName.Text, txtEmail.Text) > 0
Line 55: Message.Text = "Register Successed, click on the link WebCam for login"
Line 56:
Source File: c:inetpubwwwrootweb_sitemusic
egister.aspx Line: 54
In the DB the fields are added as :
UserID as int
UserName as varchar
UserPassword as varchar
Name as varchar
Email as varchar
and I have TRYED to change from "varchar" on to "text" but I receive same error message.
PLEASE HELP !!!!!!!! this is not first time I get the same errors on the all reg pages :( WHY ? WHAT LINE I HAVE TO EDIT ?
Thank You !!!
Regards
Hi please lok at this SP I have written and point where am I commiting mistake.
The PROD_ID_NUM field is a varchar field in the actual database.
Any help appreciated.
CREATE PROCEDURE [cp_nafta_dws].[spMXGetProductDetails]
(
@ProductCode Int = null
)
AS
DECLARE @sqlString AS nvarchar(2000)
SET @sqlString = 'SELECT Master.PROD_ID_NUM AS ProductCode,
Master.PROD_DESC_TEXT AS ProductName,
Detail.PiecesPerBox, Detail.Price
FROM cp_nafta_dws.PRODUCT AS Master
INNER JOIN cp_nafta_dws.PRODUCT_MEXICO AS Detail
ON Master.PROD_ID_NUM = Detail.PROD_ID_NUM'
BEGIN
IF NOT (@ProductCode = NULL)
BEGIN
SET @sqlString = @sqlString + ' WHERE Master.PROD_ID_NUM = ' + @ProductCode
END
END
EXEC @sqlString
GO
Thanks
Hello,
SELECT dbo.tSp.pID, dbo.tLo.oS
FROM dbo.tSp INNER JOIN
dbo.tLo ON dbo.tSp.SpID = dbo.tLo.SpID
WHERE (dbo.tLo.oS = N'[MyText]')
This works without Where and I see MyText available in oS column. Why does it not bring anything when Where is there?
Thanks,
This code is not updating the database, please help me
SqlCommand UpdCmd; SqlCommand SelCmd; SqlDataAdapter da; DataSet ds = new DataSet();
da = new SqlDataAdapter(); if (!(Conn.State == ConnectionState.Open)) { Conn.Open(); }
SelCmd = null; SelCmd = new SqlCommand("sp_SelectUserInfo",Conn); SelCmd.CommandType = CommandType.StoredProcedure;
oSelCmd.Parameters.Add("@UserID",userid);
da.SelectCommand = oSelCmd;
da.Fill(ds,"UserTab"); oUpdCmd = null; UpdCmd = new SqlCommand("sp_UpdateUserInfo",Conn); UpdCmd.CommandType = CommandType.StoredProcedure;
UpdCmd.Parameters.Add("@UserID",userid); UpdCmd.Parameters.Add("@FirstName",firstName); UpdCmd.Parameters.Add("@LastName",lastName); UpdCmd.Parameters.Add("@Region",region); da.UpdateCommand = UpdCmd; da.Update(ds,"UserTab");
Conn.Close();
Here is the codeLine 84: Line 85: searchDataAdapter = New System.data.sqlclient.sqldataadapter("SELECT * FROM Inventory Where title=" & title, searchConnection)Line 86: searchDataAdapter.Fill(objItemInfo, "ItemInfo")Line 87: Line 88: Return objItemInfohere is the errorLine 1: Incorrect syntax near '='. Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. Exception Details: System.Data.SqlClient.SqlException: Line 1: Incorrect syntax near '='.Source Error:
View 3 Replies View RelatedI have a SQL query in my asp.net & c# application. im trying to retrieve the data from two tables where the ID's of the tables match. once this is found i am obtaining the value associated with one of the keys. e.g. my two tables
Event EventCategoryTypeField Type Example Field Type ExampleEventID Int(4) 1 CategoryID(PK)int(4) 1CategoryID(FK)int(4) 1 Type varchar(50) ExerciseType varchar (200) Exercise Color varchar(50) Brown
The CategoryID is a 1:n relationship. my SQL query will retrive the values where the CategoryIDs match and the Tyoe matches as well. once this is found it will apply the associated color with that categoryID (each unique category has its own Color).
my application will read all the data correctly (ive checked it with a breakpoint too and it reads all the different colors for the different ID's) but it wont display the text in the right color from the table. it will just display everything in the first color it comes across.
Im including my code if it helps. can anyone tell me where i am going wrong please?? (the procedures are called on the On_Page_Load method)
private void Load_Events(){///<summary>///Loads the events added from the NewEvent from into a dataset///and then just as with the Holidyas, the events are wriiten to///the appropriate calendar cell by comparing the date. only the ///title and time will be displayed in the cell. other event details///such as, Objective, owner will be shown in a dialog box syle when ///the user hovers over the event.///</summary>
mycn = new SqlConnection(strConn);myda = new SqlDataAdapter("SELECT * FROM Event, EventTypeCategory WHERE Event.CategoryID = EventTypeCategory.CategoryID AND Event.Type = EventTypeCategory.CategoryType", mycn);myda.Fill(ds2, "Events");}
private void Populate_Events(object sender, System.Web.UI.WebControls.DayRenderEventArgs e){///<summary>///This procedure will read all the data from the dataset - Events and then///write each event to the appropriate calendar cell by comparing the date of ///the EventStartDate. if an event is found, the title and time are written///to the cell, other details are shown by hovering over the cell to bring///up another function that will display the data in a dialogBox. once the///event is written, the appropriate color is applied to the text.///</summary>if (!e.Day.IsOtherMonth){foreach (DataRow dr in ds2.Tables[0].Rows){if ((dr["EventStartDate"].ToString() != DBNull.Value.ToString())){DateTime evStDate = (DateTime)dr["EventStartDate"];string evTitle = (string)dr["Title"];string evStTime = (string)dr["EventStartTime"];string evEnTime = (string)dr["EventEndTime"];string evColor = (string)dr["CategoryColor"];
if(evStDate.Equals(e.Day.Date)){e.Cell.Controls.Add(new LiteralControl("<br>"));e.Cell.Controls.Add(new LiteralControl("<FONT COLOR = evColor>"));e.Cell.Controls.Add(new LiteralControl(evTitle + " " + evStTime + " - " + evEnTime));}}}}else{e.Cell.Text = "";}}