what am i doing wrong.. :(
CREATE PROCEDURE getlistofspclass
(
@date datetime,
@s varchar(8000) output
)
AS
select @s = coalesce(@s + ' & ', '') + '"' + convert(varchar(10),dbo.calader.timestart)+ ' ' + dbo.classT.classname + '<p>'
FROM dbo.calader INNER JOIN dbo.classT ON dbo.calader.classid = dbo.classT.classcode
WHERE dbo.calader.caledardatedates = @date
exec( @s)
GO
This is the error it gives me for my code and then it calls out line 102. Line 102 is my buildDD(sql, ddlPernames) When I comment out this line the error goes away, but what I don't get is this is the same way I build all of my dropdown boxes and they all work but this one. Could it not like something in my sql select statement. thanksPrivate Sub DDLUIC_SelectedIndexChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles DDLUIC.SelectedIndexChanged
Dim taskforceID As Byte = ddlTaskForce.SelectedValueDim uic As String = DDLUIC.SelectedValue
sql = "select sidstrNAME_IND from CMS.dbo.tblSIDPERS where sidstrSSN_SM in (Select Case u.strSSN from tblAssignedPersonnel as u " _
& "where u.bitPresent = 1 and u.intUICID in (select intUICID from tblUIC where intTaskForceID = " & taskforceID & " and strUIC = '" & uic & "'))"ddlPerNames.Items.Add(New ListItem("", "0"))
buildDD(sql, ddlPerNames)
End Sub
G'day everyoneThat's a space between the ticks.It's all part of a longer script but seeing as the failure occurs online 1if exists (select * from dbo.sysobjects where id =object_id(N'[dbo].[config]') and OBJECTPROPERTY(id, N'IsUserTable') =1)drop table [dbo].[config]GOThat's three lines only. Does it matter that they're in Unicode?Any ideas?Kind regards,Bruce M. AxtensSoftware EngineerStrapper Technologies
View 3 Replies View Related HiIt's my stored procedure 1 CREATE PROCEDURE singleSearch2
2 @SQ nvarchar(30),
3 @pType nvarchar(11),
4 @pCol nvarchar(11)
5 AS
6 BEGIN
7 DECLARE @SQL NVarChar(1000)
8 SELECT @SQL='SELECT *,'
9 SELECT @SQL=@SQL+' CASE RTRIM(LTRIM(op))'
10 SELECT @SQL=@SQL+' WHEN '+CHAR(39)+'e'+CHAR(39)+' THEN '+CHAR(39)+'اجاره'+ CHAR(39)
11 SELECT @SQL=@SQL+' WHEN '+CHAR(39)+'r'+CHAR(39)+' THEN '+CHAR(39)+'رهن'+CHAR(39)
12 SELECT @SQL=@SQL+' WHEN '+CHAR(39)+'f'+ CHAR(39)+' THEN '+ CHAR(39) +' Ù?روش '+CHAR(39)
13 SELECT @SQL=@SQL+' WHEN '+CHAR(39)+'e r'+CHAR(39)+' THEN '+CHAR(39)+ 'اجاره - رهن '+CHAR(39)
14 SELECT @SQL=@SQL+' WHEN '+CHAR(39)+'e f'+CHAR(39)+' THEN '+CHAR(39)+'اجاره - Ù?روش '+CHAR(39)
15 SELECT @SQL=@SQL+' WHEN '+CHAR(39)+'r f'+CHAR(39)+' THEN '+CHAR(39)+' رهن - Ù?روش '+CHAR(39)
16 SELECT @SQL=@SQL+' WHEN '+CHAR(39)+'e r f'+CHAR(39)+' THEN '+CHAR(39)+'اجاره - رهن - Ù?روش'+CHAR(39)
17 SELECT @SQL=@SQL+' ELSE op END AS xop, CASE LTRIM(RTRIM(type)) '
18 SELECT @SQL=@SQL+' WHEN '+CHAR(39)+'z -'+CHAR(39)+' THEN '+CHAR(39)+'زمین'+CHAR(39)
19 SELECT @SQL=@SQL+' WHEN '+CHAR(39)+'m -'+CHAR(39)+' THEN '+CHAR(39)+'مسکونی'+CHAR(39)
20 SELECT @SQL=@SQL+' WHEN '+CHAR(39)+'t -'+CHAR(39)+' THEN '+CHAR(39)+'تجاری'+CHAR(39)
21 SELECT @SQL=@SQL+' ELSE [type] END AS [xtype] FROM [data] '
22 SELECT @SQL=@SQL+' WHERE ([type] LIKE %'+CHAR(39)+@pType+CHAR(39)+'%) AND ('+@pCol+' LIKE %'+CHAR(39)+@SQ+CHAR(39)+'%)'
23 Exec (@SQL)
24 END
25 GO
and i face this error: Line 1: Incorrect syntax near '-'.
Hello All,
Me saying " has any body come across such error would be
underestimating".
Well I am getting a very peculiar and unique error "Line 1: Incorrect
syntax near 'Actions'."
Explaining you the scene is the following Stored Proc.
This stored proc is execute from a VB code in the .net application as
like: -
{Try
Connection.Init_Variables()
cn.ConnectionString = Connection.gstrConnection
ResDb.ConnectionString = Connection.gresConnection
cn.Open()
With sqlCmd
.Connection = cn
.CommandText = "DSP_Get_Required"
.CommandType = CommandType.StoredProcedure
.Parameters.Add("@ActionId", SqlDbType.Int, 9).Value = ActionID
.Parameters("@ActionId").Direction = ParameterDirection.InputOutput
.Parameters.Add("@PersonID", SqlDbType.Int, 9).Value = PersonID
.Parameters("@PersonID").Direction = ParameterDirection.InputOutput
.Parameters.Add("@ReturnMessage", SqlDbType.VarChar, 1000).Value =
ReturnMessage.ToString
.Parameters("@ReturnMessage").Direction =
ParameterDirection.InputOutput
.Parameters.Add("@Exists", SqlDbType.Bit, 1).Value = Exists
.Parameters("@Exists").Direction = ParameterDirection.InputOutput
.Parameters.Add("@Days", SqlDbType.Int, 9).Value = 0
.Parameters("@Days").Direction = ParameterDirection.InputOutput
.Parameters.Add("@StartDate", SqlDbType.DateTime).Value = Now()
.Parameters("@StartDate").Direction = ParameterDirection.InputOutput
.Parameters.Add("@EndDate", SqlDbType.DateTime).Value = Now()
.Parameters("@EndDate").Direction = ParameterDirection.InputOutput
.Parameters.Add("@OutCome", SqlDbType.VarChar, 20).Value = "Stop"
.Parameters("@OutCome").Direction = ParameterDirection.InputOutput
.Parameters.Add("@Evaluate", SqlDbType.Int, 9).Value = 0
.Parameters("@Evaluate").Direction = ParameterDirection.InputOutput
.Parameters.Add("@DbName", SqlDbType.VarChar, 100).Value =
ResDb.Database.ToString
.Parameters("@DbName").Direction = ParameterDirection.InputOutput
.ExecuteReader(CommandBehavior.Default)
}
On Execution I get the subjected Error "Line 1: Incorrect syntax near
'Actions'." Any Ideas from your all experience to get away from this error will be
helpful. Look forward to read somebody soon.
Stored Proc:-
{SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE ID =
OBJECT_ID(N'dbo.DSP_Get_Required_ActionS') AND OBJECTPROPERTY(id,
N'ISPROCEDURE') = 1)
DROP PROCEDURE dbo.DSP_Get_Required_ActionS
GO
CREATE PROCEDURE DSP_Get_Required_ActionS
@ActionID INT OUTPUT,
@PersonID INT OUTPUT,
@ReturnMessage Varchar(1000) OUTPUT,
@Exists BIT OUTPUT,
@Days INT OUTPUT,
@StartDate DATETIME OUTPUT,
@EndDate DATETIME OUTPUT,
@OutCome VARCHAR(20) OUTPUT,
@Evaluate INT OUTPUT,
@DbName VARCHAR(100) OUTPUT
AS
SET NOCOUNT ON
--DECLARE @PopulateSQL AS NVarchar(4000)
DECLARE @Rule_ID AS NUMERIC(9)
DECLARE @Curr_ActionSubType AS VARCHAR(20)
DECLARE @Eval_SubType AS VARCHAR(20)
-- DECLARE @OutCome AS VARCHAR(20)
-- DECLARE @Evaluate AS INT
-- DECLARE @Days AS INT
DECLARE @Message AS VARCHAR(1000)
DECLARE @Mandatory AS BIT
-- This is the variable used to interpret the Precedant subtype
DECLARE @Prec_Subtype AS VARCHAR(20)
-- DECLARE @Exists AS BIT --this is supposed to be the deceision maker
variable to be used within the precedant check.
DECLARE @Precedant_SubTypes_Cnt AS INT --This is the variable used to
recordcount the Precedant Subtypes to be checked
DECLARE @Counter AS INT -- Counter used to loop through the Table of
precedant Subtypes.
DECLARE @ROWCOUNT AS INT -- Temporary ROWCOUNT
SET @Counter = 1
--Process to retrive @Curr_ActionSubType Variable
CREATE TABLE #Curr_ActionSubType(ActionSubType VARCHAR(100))
EXEC ('INSERT INTO #Curr_ActionSubType SELECT SubType FROM '+ @DbName
+'.resadm.action WHERE '+@DbName+'.resadm.action.Actionid = '+
@ActionID+' AND ' +@DbName+'.resadm.action.status =''A''')
SET @Curr_ActionSubType = (Select ActionSubType from #Curr_ActionSubType)
DROP TABLE #Curr_ActionSubType
--Process to retrive @StartDate Variable
CREATE TABLE #StartDate(StartDate DATETIME)
EXEC('INSERT INTO #StartDate SELECT CONVERT(DATETIME,
'+@DbName+'.Resadm.Action.DateofAction + '' ''+
'+@DbName+'.Resadm.Action.TimeOfAction)
FROM '+@DbName+'.resadm.action
WHERE '+@DbName+'.resadm.action.Actionid = '+ @ActionID +' AND '+@DbName+'.resadm.action.status =''A''' )
SET @StartDate = (Select StartDate from #StartDate)
DROP TABLE #StartDate
SET @Rule_ID = (SELECT Rule_ID FROM Rules WHERE Curr_Subtype =
@Curr_ActionSubType and Status <>0 GROUP BY RULE_ID)
SET @Eval_SubType = (SELECT Evaluate_Subtype FROM Rules WHERE
Curr_Subtype = @Curr_ActionSubType and Status <>0 GROUP BY
Evaluate_Subtype)
SET @OutCome = (SELECT OutCome FROM Rules WHERE Curr_Subtype =
@Curr_ActionSubType and Status <>0 GROUP BY OutCome)
SET @Evaluate = (SELECT Evaluate FROM Rules WHERE Curr_Subtype =
@Curr_ActionSubType and Status <>0 GROUP BY Evaluate)
SET @Days = (SELECT Days FROM Rules WHERE Curr_Subtype =
@Curr_ActionSubType and Status <>0 GROUP BY Days)
SET @Message = (SELECT Message FROM Rules WHERE Curr_Subtype =
@Curr_ActionSubType and Status <>0 GROUP BY Message)
SET @Mandatory = (SELECT Optional_Mandatory_Precedant FROM Rules WHERE
Curr_Subtype = @Curr_ActionSubType and Status <>0 GROUP BY
Optional_Mandatory_Precedant)
-- create the temporary table for the Subtypes to be evaluated
CREATE TABLE #Preceding_SubTypes_Details
( SubTypes_LIST_ID INT IDENTITY (1, 1) NOT NULL,
RULE_ID NUMERIC(9),
SubType VARCHAR(20),
)
-- insert the current subtype that needs to be evaluated.
INSERT INTO #Preceding_SubTypes_Details
SELECT Rule_ID, Prec_Subtype
FROM Rules_Details
WHERE Rule_ID = @Rule_ID
-- create the History table for Reference
--sk/* Modified to accomodatethe need ot dynamic database name to
retrive from the different Resman databases
CREATE TABLE #dsHistory ( ActionID INT,
PersonID INT,
ActionTypeID VARCHAR(1),
DateofAction DATETIME,
Status VARCHAR(1),
Subtype VARCHAR(6),
ActionTypeName VARCHAR(30),
ActionSubtypeID VARCHAR(6),
EffectCandidateCurrentState VARCHAR(10),
TaxCode VARCHAR(6)
)
EXEC ('INSERT INTO #dsHistory SELECT
'+@DbName+'.Resadm.Action.ActionID, '
+ @DbName+'.Resadm.Action.PersonID,
'+@DbName+'.Resadm.Action.ActionTypeID, '
+ 'CONVERT(DATETIME, '+@DbName+'.Resadm.Action.DateofAction + '' ''+
'+@DbName+'.Resadm.Action.TimeOfAction)DateofAction, '
+ @DbName+'.Resadm.Action.Status, '+@DbName+'.Resadm.Action.Subtype, '
+ @DbName+'.ResAdm.Action_Types.ActionTypeName,
'+@DbName+'.Resadm.Action_subtypes.ActionSubtypeID, '
+ @DbName+'.Resadm.Action_subtypes.EffectCandidateCurrentState,
'+@DbName+'.Resadm.Person.TaxCode '
+ ' FROM '+@DbName+'.Resadm.Action '
+ ' INNER JOIN '+@DbName+'.ResAdm.Action_Types WITH(NOLOCK) '
+ ' ON '+@DbName+'.ResAdm.Action_Types.ActionTypeID =
'+@DbName+'.Resadm.Action.ActionTypeID '
+ ' INNER JOIN '+@DbName+'.ResAdm.Action_SubTypes WITH(NOLOCK) '
+ ' ON '+@DbName+'.Resadm.Action.subtype =
'+@DbName+'.ResAdm.Action_SubTypes.actionsubtypeid '
+ ' INNER JOIN '+@DbName+'.Resadm.Person WITH(NOLOCK) '
+ ' ON '+@DbName+'.Resadm.Person.PersonID =
'+@DbName+'.Resadm.Action.PersonID '
+ ' WHERE '+@DbName+'.Resadm.Action.actionID <>
CONVERT(VARCHAR,'+@ActionID+')'
+ ' AND '+@DbName+'.Resadm.Action.PersonID =
CONVERT(VARCHAR,'+@PersonID+')'
+ ' AND '+@DbName+'.Resadm.Action.Status =''A'' '
+ 'AND (CONVERT(DATETIME,'+@DbName+'.Resadm.Action.DateofAction +''
''+ '+@DbName+'.Resadm.Action.TimeOfAction) > '
+ ' ISNULL(( SELECT
MAX(CONVERT(DATETIME,'+@DbName+'.Resadm.Action.DateofAction + '' ''+ '
+ @DbName+'.Resadm.Action.TimeOfAction)) '
+ ' FROM '+@DbName+'.Resadm.Action '
+ ' WHERE ('+@DbName+'.Resadm.Action.PersonID =
CONVERT(VARCHAR,'+@PersonID+')) AND '
+ ' ('+@DbName+'.Resadm.Action.Subtype =''ZERO'') '
+ ' AND ('+@DbName+'.Resadm.Action.Status=''A'')),0)) '
+ ' ORDER BY CONVERT(DATETIME,'+@DbName+'.Resadm.Action.DateofAction +
'' ''+ '+@DbName+'.Resadm.Action.TimeOfAction )DESC, ActionID DESC')
--sk*/
SET @EndDate = (SELECT ISNULL((SELECT DateOfAction
FROM #dsHistory
WHERE SubType = @Eval_SubType), getdate()))
-- set the rowcount to retrieve the number of check to be carried out
SET @Precedant_SubTypes_Cnt = (SELECT COUNT(*) FROM
#Preceding_SubTypes_Details)
WHILE @Counter <= @Precedant_SubTypes_Cnt
BEGIN
SET @Prec_Subtype = (SELECT SubType from #Preceding_SubTypes_Details
WHERE SubTypes_LIST_ID = @Counter)
SET @ROWCOUNT = (SELECT COUNT(*) FROM #dsHistory WHERE Subtype =
@Prec_Subtype)
IF @ROWCOUNT > 0
BEGIN
SET @Exists = 1
END
IF @ROWCOUNT = 0
BEGIN
IF @Mandatory = 1
BEGIN
SET @Message = @Message + ' ' + @Prec_Subtype + ' Is Missing; '
SET @Counter = @Precedant_SubTypes_Cnt
SET @Exists = 0
END
ELSE IF @Mandatory = 0
BEGIN
SET @Message = @Message + ' ' + @Prec_Subtype + ' Is Missing; '
SET @Exists = @Exists
END
END
SET @Counter = @Counter+1
END
IF @Exists = 0
BEGIN
EXEC(
' UPDATE '+@DbName+'.Resadm.Action '
+ ' SET '+@DbName+'.Resadm.Action.Status = ''I'' '
+ ' WHERE '+@DbName+'.Resadm.Action.ActionID = '+@ActionID+' SET
@ReturnMessage = '+@Message
)
END
ELSE
IF @Exists = 1
BEGIN
SET @ReturnMessage = @Message
END
IF @Rule_ID = Null
BEGIN
SET @ReturnMessage = 'Validation Rule Not Present'
END
-- Select 'Exist value : ', @Exists, 'Return message is : ',
@ReturnMessage
DROP TABLE #Preceding_SubTypes_Details
DROP TABLE #dshistory
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO}
Hi,I'm getting the above error when i try to fill a Dataset through adataAdapter.I presume it is to do with the sql statement. Below is the relevantcode:string strPntUnitID = patientCodeLbl.Text;string strPntFName = fNameLbl.Text;string strPntLName = lNameLbl.Text;// Create DataAdapter & DatasetSqlDataAdapter daRelateDocToPnt = new SqlDataAdapter("SELECT patientNo,doctorNo FROM tblPatient" +"WHERE (pntUnitID = '"+ strPntUnitID +"') AND (pntFName = '"+strPntFName +"')"+"AND (pntLName = '"+ strPntLName +"')", conn);DataSet dsDocNoToPnt = new DataSet();// Create command builder, automatically generates the update commandsSqlCommandBuilder pntCmd = new SqlCommandBuilder(daRelateDocToPnt);// Set the MissingSchemaAction property to AddWithKey because Fill willnot cause primary// key & unique key information to be retrieved unless AddWithKey isspecified.daRelateDocToPnt.MissingSchemaAction = MissingSchemaAction.AddWithKey;// Use dataAdapter to fill DataSetdaRelateDocToPnt.Fill(dsDocNoToPnt, "Patient");// place intDocNo into the dataset schemadsDocNoToPnt.Tables["Patient"].Rows[0]["doctorNo"] = "intDocNo";// Update The DatabasedaRelateDocToPnt.Update(dsDocNoToPnt, "Patient");Can anyone spot the problem?Thanks
View 3 Replies View RelatedHello All,Me saying " has any body come across such error would beunderestimating".Well I am getting a very peculiar and unique error "Line 1: Incorrectsyntax near 'Actions'."Explaining you the scene is the following Stored Proc.This stored proc is execute from a VB code in the .net application aslike: -{TryConnection.Init_Variables()cn.ConnectionString = Connection.gstrConnectionResDb.ConnectionString = Connection.gresConnectioncn.Open()With sqlCmd..Connection = cn..CommandText = "DSP_Get_Required".CommandType = CommandType.StoredProcedure..Parameters.Add("@ActionId", SqlDbType.Int, 9).Value = ActionID..Parameters("@ActionId").Direction = ParameterDirection.InputOutput..Parameters.Add("@PersonID", SqlDbType.Int, 9).Value = PersonID..Parameters("@PersonID").Direction = ParameterDirection.InputOutput..Parameters.Add("@ReturnMessage", SqlDbType.VarChar, 1000).Value =ReturnMessage.ToString..Parameters("@ReturnMessage").Direction =ParameterDirection.InputOutput..Parameters.Add("@Exists", SqlDbType.Bit, 1).Value = Exists..Parameters("@Exists").Direction = ParameterDirection.InputOutput..Parameters.Add("@Days", SqlDbType.Int, 9).Value = 0..Parameters("@Days").Direction = ParameterDirection.InputOutput..Parameters.Add("@StartDate", SqlDbType.DateTime).Value = Now()..Parameters("@StartDate").Direction = ParameterDirection.InputOutput..Parameters.Add("@EndDate", SqlDbType.DateTime).Value = Now()..Parameters("@EndDate").Direction = ParameterDirection.InputOutput..Parameters.Add("@OutCome", SqlDbType.VarChar, 20).Value = "Stop"..Parameters("@OutCome").Direction = ParameterDirection.InputOutput..Parameters.Add("@Evaluate", SqlDbType.Int, 9).Value = 0..Parameters("@Evaluate").Direction = ParameterDirection.InputOutput..Parameters.Add("@DbName", SqlDbType.VarChar, 100).Value =ResDb.Database.ToString..Parameters("@DbName").Direction = ParameterDirection.InputOutput..ExecuteReader(CommandBehavior.Default)}On Execution I get the subjected Error "Line 1: Incorrect syntax near'Actions'."Any Ideas from your all experience to get away from this error will behelpful. Look forward to read somebody soon.Stored Proc:-{SET QUOTED_IDENTIFIER OFFGOSET ANSI_NULLS OFFGOIF EXISTS (SELECT * FROM dbo.sysobjects WHERE ID =OBJECT_ID(N'dbo.DSP_Get_Required_ActionS') AND OBJECTPROPERTY(id,N'ISPROCEDURE') = 1)DROP PROCEDURE dbo.DSP_Get_Required_ActionSGOCREATE PROCEDURE DSP_Get_Required_ActionS@ActionID INT OUTPUT,@PersonID INT OUTPUT,@ReturnMessage Varchar(1000) OUTPUT,@Exists BIT OUTPUT,@Days INT OUTPUT,@StartDate DATETIME OUTPUT,@EndDate DATETIME OUTPUT,@OutCome VARCHAR(20) OUTPUT,@Evaluate INT OUTPUT,@DbName VARCHAR(100) OUTPUTASSET NOCOUNT ON--DECLARE @PopulateSQL AS NVarchar(4000)DECLARE @Rule_ID AS NUMERIC(9)DECLARE @Curr_ActionSubType AS VARCHAR(20)DECLARE @Eval_SubType AS VARCHAR(20)-- DECLARE @OutCome AS VARCHAR(20)-- DECLARE @Evaluate AS INT-- DECLARE @Days AS INTDECLARE @Message AS VARCHAR(1000)DECLARE @Mandatory AS BIT-- This is the variable used to interpret the Precedant subtypeDECLARE @Prec_Subtype AS VARCHAR(20)-- DECLARE @Exists AS BIT --this is supposed to be the deceision makervariable to be used within the precedant check.DECLARE @Precedant_SubTypes_Cnt AS INT--This is the variable used torecordcount the Precedant Subtypes to be checkedDECLARE @Counter AS INT -- Counter used to loop through the Table ofprecedant Subtypes.DECLARE @ROWCOUNT AS INT -- Temporary ROWCOUNTSET @Counter = 1--Process to retrive @Curr_ActionSubType VariableCREATE TABLE #Curr_ActionSubType(ActionSubType VARCHAR(100))EXEC ('INSERT INTO #Curr_ActionSubType SELECT SubType FROM '+ @DbName+'.resadm.action WHERE '+@DbName+'.resadm.action.Actionid = '+@ActionID+' AND ' +@DbName+'.resadm.action.status =''A''')SET @Curr_ActionSubType = (Select ActionSubType from#Curr_ActionSubType)DROP TABLE #Curr_ActionSubType--Process to retrive @StartDate VariableCREATE TABLE #StartDate(StartDate DATETIME)EXEC('INSERT INTO #StartDate SELECT CONVERT(DATETIME,'+@DbName+'.Resadm.Action.DateofAction + '' ''+'+@DbName+'.Resadm.Action.TimeOfAction)FROM '+@DbName+'.resadm.actionWHERE '+@DbName+'.resadm.action.Actionid = '+ @ActionID +' AND'+@DbName+'.resadm.action.status =''A''')SET @StartDate = (Select StartDate from #StartDate)DROP TABLE #StartDateSET @Rule_ID = (SELECT Rule_ID FROM Rules WHERE Curr_Subtype =@Curr_ActionSubType and Status <>0 GROUP BY RULE_ID)SET @Eval_SubType = (SELECT Evaluate_Subtype FROM Rules WHERECurr_Subtype = @Curr_ActionSubType and Status <>0 GROUP BYEvaluate_Subtype)SET @OutCome = (SELECT OutCome FROM Rules WHERE Curr_Subtype =@Curr_ActionSubType and Status <>0 GROUP BY OutCome)SET @Evaluate = (SELECT Evaluate FROM Rules WHERE Curr_Subtype =@Curr_ActionSubType and Status <>0 GROUP BY Evaluate)SET @Days = (SELECT Days FROM Rules WHERE Curr_Subtype =@Curr_ActionSubType and Status <>0 GROUP BY Days)SET @Message = (SELECT Message FROM Rules WHERE Curr_Subtype =@Curr_ActionSubType and Status <>0 GROUP BY Message)SET @Mandatory = (SELECT Optional_Mandatory_Precedant FROM Rules WHERECurr_Subtype = @Curr_ActionSubType and Status <>0 GROUP BYOptional_Mandatory_Precedant)-- create the temporary table for the Subtypes to be evaluatedCREATE TABLE #Preceding_SubTypes_Details(SubTypes_LIST_ID INT IDENTITY (1, 1) NOT NULL,RULE_ID NUMERIC(9),SubType VARCHAR(20),)-- insert the current subtype that needs to be evaluated.INSERT INTO #Preceding_SubTypes_DetailsSELECT Rule_ID, Prec_SubtypeFROM Rules_DetailsWHERE Rule_ID = @Rule_ID-- create the History table for Reference--sk/*Modified to accomodatethe need ot dynamic database name toretrive from the different Resman databasesCREATE TABLE #dsHistory ( ActionID INT,PersonID INT,ActionTypeID VARCHAR(1),DateofAction DATETIME,Status VARCHAR(1),Subtype VARCHAR(6),ActionTypeName VARCHAR(30),ActionSubtypeID VARCHAR(6),EffectCandidateCurrentState VARCHAR(10),TaxCode VARCHAR(6))EXEC ('INSERT INTO #dsHistory SELECT'+@DbName+'.Resadm.Action.ActionID, '+ @DbName+'.Resadm.Action.PersonID,'+@DbName+'.Resadm.Action.ActionTypeID, '+ 'CONVERT(DATETIME, '+@DbName+'.Resadm.Action.DateofAction + '' ''+'+@DbName+'.Resadm.Action.TimeOfAction)DateofActio n, '+ @DbName+'.Resadm.Action.Status, '+@DbName+'.Resadm.Action.Subtype, '+ @DbName+'.ResAdm.Action_Types.ActionTypeName,'+@DbName+'.Resadm.Action_subtypes.ActionSubtypeID , '+ @DbName+'.Resadm.Action_subtypes.EffectCandidateCu rrentState,'+@DbName+'.Resadm.Person.TaxCode '+ ' FROM '+@DbName+'.Resadm.Action '+ ' INNER JOIN '+@DbName+'.ResAdm.Action_Types WITH(NOLOCK) '+ ' ON '+@DbName+'.ResAdm.Action_Types.ActionTypeID ='+@DbName+'.Resadm.Action.ActionTypeID '+ ' INNER JOIN '+@DbName+'.ResAdm.Action_SubTypes WITH(NOLOCK) '+ ' ON '+@DbName+'.Resadm.Action.subtype ='+@DbName+'.ResAdm.Action_SubTypes.actionsubtypeid '+ ' INNER JOIN '+@DbName+'.Resadm.Person WITH(NOLOCK) '+ ' ON '+@DbName+'.Resadm.Person.PersonID ='+@DbName+'.Resadm.Action.PersonID '+ ' WHERE '+@DbName+'.Resadm.Action.actionID <>CONVERT(VARCHAR,'+@ActionID+')'+ ' AND '+@DbName+'.Resadm.Action.PersonID =CONVERT(VARCHAR,'+@PersonID+')'+ ' AND '+@DbName+'.Resadm.Action.Status =''A'' '+ 'AND (CONVERT(DATETIME,'+@DbName+'.Resadm.Action.Dateof Action +''''+ '+@DbName+'.Resadm.Action.TimeOfAction) > '+ ' ISNULL(( SELECTMAX(CONVERT(DATETIME,'+@DbName+'.Resadm.Action.Dat eofAction + '' ''+ '+ @DbName+'.Resadm.Action.TimeOfAction)) '+ ' FROM '+@DbName+'.Resadm.Action '+ ' WHERE ('+@DbName+'.Resadm.Action.PersonID =CONVERT(VARCHAR,'+@PersonID+')) AND '+ ' ('+@DbName+'.Resadm.Action.Subtype =''ZERO'') '+ ' AND ('+@DbName+'.Resadm.Action.Status=''A'')),0)) '+ ' ORDER BY CONVERT(DATETIME,'+@DbName+'.Resadm.Action.DateofA ction +'' ''+ '+@DbName+'.Resadm.Action.TimeOfAction )DESC, ActionID DESC')--sk*/SET @EndDate = (SELECT ISNULL((SELECT DateOfActionFROM #dsHistoryWHERE SubType = @Eval_SubType), getdate()))-- set the rowcount to retrieve the number of check to be carried outSET @Precedant_SubTypes_Cnt = (SELECT COUNT(*) FROM#Preceding_SubTypes_Details)WHILE @Counter <= @Precedant_SubTypes_CntBEGINSET @Prec_Subtype = (SELECT SubType from #Preceding_SubTypes_DetailsWHERE SubTypes_LIST_ID = @Counter)SET @ROWCOUNT = (SELECT COUNT(*) FROM #dsHistory WHERE Subtype =@Prec_Subtype)IF @ROWCOUNT > 0BEGINSET @Exists = 1ENDIF @ROWCOUNT = 0BEGINIF @Mandatory = 1BEGINSET @Message = @Message + ' ' + @Prec_Subtype + ' Is Missing; 'SET @Counter = @Precedant_SubTypes_CntSET @Exists = 0ENDELSE IF @Mandatory = 0BEGINSET @Message = @Message + ' ' + @Prec_Subtype + ' Is Missing; 'SET @Exists = @ExistsENDENDSET @Counter = @Counter+1ENDIF @Exists = 0BEGINEXEC(' UPDATE '+@DbName+'.Resadm.Action '+ ' SET '+@DbName+'.Resadm.Action.Status = ''I'' '+ ' WHERE '+@DbName+'.Resadm.Action.ActionID = '+@ActionID+' SET@ReturnMessage = '+@Message)ENDELSEIF @Exists = 1BEGINSET @ReturnMessage = @MessageENDIF @Rule_ID = NullBEGINSET @ReturnMessage = 'Validation Rule Not Present'END-- Select 'Exist value : ', @Exists, 'Return message is : ',@ReturnMessageDROP TABLE #Preceding_SubTypes_DetailsDROP TABLE #dshistoryGOSET QUOTED_IDENTIFIER OFFGOSET ANSI_NULLS ONGO}
View 1 Replies View RelatedHi Marc,
I have a similar problem as mentioned in the first mail of this chain. I am getting Line 202:Incorrect syntax near 'end'" while I try to execute the procedure from my VC++ code using
ExecuteNonQuery method.
can you guide me about solving this issue?
Regards,
Anni
Hi,
Uses: Sql server 2000, asp.net 1.1;
I've the following Stored Procedure:
<SQL Code>
CREATE PROCEDURE PABX_sp_MyPABX_ShowExtWatch
@FromDate DATETIME,
@ToDate DATETIME,
@PortalID INT = 1,
@UserID INT
AS
SELECT PABX_Ext_Watch.ExtNo As ExtNo, COUNT(*) AS Calls, SUM(PABX_Calls.Rate) AS NetAmt
FROM PABX_Ext_Watch
INNER JOIN PABX_Calls
ON PABX_Ext_Watch.ExtNo = PABX_Calls.ExtNo
INNER JOIN PABX_PortalUsers
ON PABX_Ext_Watch.PortalUserID = PABX_PortalUsers.PortalUserID
AND PABX_Calls.PortalID = PABX_PortalUsers.PortalID
WHERE PABX_Calls.sTime >= CONVERT(DATETIME, @FromDate, 102)
AND PABX_Calls.eTime <= CONVERT(DATETIME, @ToDate, 102)
AND PABX_PortalUsers.uID = @UserID
AND PABX_PortalUsers.PortalID = @PortalID
GROUP BY PABX_Ext_Watch.ExtNo
GO
</SQL Code>
However if I execute this stored procedure in asp.net I get the following error, despite the stored procedure executed successfully in query analyzer:
Line 1: Incorrect Syntax Near ......
When I check the sql profiler the stored procedure is been executed in this way:
exec sp_executesql N'PABX_sp_MYPABX_ShowExtWatch', N'@FromDate datetime,@ToDate datetime,@UserID int,@PortalID int', @FromDate = 'Sep 1 2006 12:00:00:000AM', @ToDate = 'Sep 30 2006 12:00:00:000AM', @UserID = 207, @PortalID = 2
I couldn't figure it out though sql server management console and query analyzer showed no signs of errors. I sense that the sql is not being executed correctly with letters as "N" in the sql profiler.
Could anyone help me out on this?
Thanks & Regards,
Hi;
I am trying to prepare a login page.My code first controls if user exists in database with GetUser stored procedure then if he/she exists getting the roles assigned to that user with GetUserRoles procedure but I am taking this error
Line 1: Incorrect syntax near 'GetUserRoles'.
GetUserRoles Procedure:
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
ALTER Procedure GetUserRoles
(
@UserName char(10),
@Roles char(10) Output
)
As
Select @Roles=RoleID From UserRoles Where
UserID=@UserName
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
My ASP.NET Code:
.......................
SqlParameter SQLParm=new SqlParameter();
SQLStr=new SqlCommand("GetUser",Con.GetConnection);
SQLStr.CommandType=CommandType.StoredProcedure;
SQLParm=SQLStr.Parameters.Add("ReturnValue",SqlDbType.Int);
SQLParm.Direction=ParameterDirection.ReturnValue;
SQLStr.Parameters.Add("@UserName",txtuser.Text);
SQLStr.Parameters.Add("@Pass",txtpass.Text);
Con.GetConnection.Open();
SQLStr.ExecuteNonQuery();
Returned=Convert.ToInt32(SQLStr.Parameters["ReturnValue"].Value);
if(Returned==1)
{
SQLStr=new SqlCommand("GetUserRoles",Con.GetConnection);
SQLStr.Parameters.Add("@UserName",txtuser.Text);
SQLParm=SQLStr.Parameters.Add("@Roles",SqlDbType.Char);
SQLParm.Size=50;
SQLParm.Direction=ParameterDirection.Output;
SQLStr.ExecuteNonQuery();
Roles=Convert.ToString(SQLStr.Parameters["@Roles"].Value);
LblMsg.Text=Roles;
Con.GetConnection.Close();
}
............................
............................
Thank you for your replies from now....
hi,
i got this error when i run app.
---------------
Line 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:
Line 40: Dim adpt As New SqlDataAdapter("SELECT * FROM SMS_student_class_master WHERE" & _
Line 41: "stud_id=" & sid, con)
Line 42: adpt.Fill(ds, "SMS_student_class_master")
Line 43: txt.Text = ds.Tables.Item("roll_no").ToString
Line 44: con.Close()
Source File: c:inetpubwwwrootaspnetsmsassignment_d.aspx.vb Line: 42
--------
what should i do?
anyone have any idea?
plz give solution.
it's urgent.
thanks in advance
I am confused. Any help you can provide, to resolve this error, would be very appreciated...... Thanks in advance!!
I am getting this error message......
Microsoft OLE DB Provider for ODBC Drivers error '80040e14'
[Microsoft][ODBC SQL Server Driver][SQL Server]Line 1: Incorrect syntax near '-'.
/formstoday/issue_log-preview.asp, line 76
The code for line 76 is thus......
objConn.execute sql
Here is the code gor the asp page........
<%
Dim StrEmail
Dim StrName
Dim StrIssue
StrEmail=request.form("email")
StrName=request.form("name")
StrIssue=request.form("issue")
Set objConn = Server.CreateObject("ADODB.Connection")
Set rs = server.createobject("ADODB.Recordset")
objConn.open "Driver={SQL server};Server=document;DSN=SQL;Database=issues;UI D=steu;PWD;"
sql = "Insert into issue-log2 (name,email,issue) values('" & _
strName & "','" & _
strEmail & "','" & _
StrIssue & "')"
objConn.execute sql
%>
Steu
Here is a Database script I wrote: I can't figure out what the problem is when I declare my primary Keys in the second and third table.
Thanks in Advance
Errors:
Msg 156, Level 15, State 1, Line 19
Incorrect syntax near the keyword 'IDENTITY'.
Msg 156, Level 15, State 1, Line 29
Incorrect syntax near the keyword 'IDENTITY'.
CREATE DATABASE GoGreen
Go
USE GoGreen
Create TABLE Student
(StudentID INT NOT NULL IDENTITY PRIMARY KEY, <---no Error here
DrivePassCode char(3) NOT NULL,
LName varchar(50) NOT NULL,
FName varchar(50) NOT NULL,
Address1 varchar(50) NOT NULL,
Address2 varchar(50) NULL,
City varchar(50) NOT NULL,
PostalCode varchar(20) NOT NULL,
PhoneNumber varchar(50) NOT NULL,
Email varchar(50) NOT NULL,
Smoker char(2) NOT NULL,
NearestCityOrTown varchar(50) NOT NULL,
PaymentType varchar(20) NOT NULL,)
CREATE TABLE CoordinatedRideShare
(RideID INT NOT NULL, IDENTITY PRIMARY KEY, <---Error #1
StudentID INT NOT NULL REFERENCES Student(StudentID)
[ON DELETE {CASCADE}]
[ON UPDATE {CASCADE}],
M-W ARRIVAL TIME smalldatetime NOT NULL,
M-W DEPART TIME smalldatetime NOT NULL,
T-TH ARRIVAL TIME smalldatetime NOT NULL,
T-TH DEPART TIME smalldatetime NOT NULL,)
CREATE TABLE MeetingLocation
(LocationID INT NOT NULL, IDENTITY PRIMARY KEY AUTONUMBER, <---Error#2
RideID INT NOT NULL REFERENCES Coordinated RideShare(RideID)
[ON DELETE {CASCADE}]
[ON UPDATE {CASCADE}],
House Pickup char(1) NULL,
General meeting place varchar(50) NULL,)
CREATE INDEX DrivePass
ON Student (DrivePass)
CREATE INDEX NearestCityOrTown
ON Sudent (NearestCityOrTown)
Hello,
Can anybody help me with this error , System.Data.SqlClient.SqlException. "Line 1: Incorrect Syntax near :'m' ."
I have a textbox named DESCRIPTION and am trying to insert the values entered by user in this textbox.
It works well if a user types " I am unable to " but it throws this error when a user types " I'm unable to "...because of I'm ..
How do i fix this?
My Query:
strSQL = "Insert into ABC(DESCRIPTION) values('" & strDescription & "')"
When i try below query in query analyser , am getting this error ....
Error 1: [Line 1: Incorrect syntax near 'm'.]
Error 2: [ Unclosed quotation mark before the character string '.]
update ABC set DESCRIPTION = 'I'm unable' WHERE ABC_ID = '142'
Thanks to reply.
Why does the following call to a stored procedure get me this error:
Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'CONVERT'.
Code Snippet
EXECUTE OpenInvoiceItemSP_RAM CONVERT(DATETIME,'01-01-2008'), CONVERT(DATETIME,'04/30/2008') , 1,'81350'
The stored procedure accepts two datetime parameters, followed by an INT and a varchar(10) in that order.
I can't find anything wrong in the syntax for CONVERT or any nearby items.
Help me please. Thank you.
I keep receiving the following error whenever I try and call this function to update my database.
The code was working before, all I added was an extra field to update.
Exception Details: System.Data.SqlClient.SqlException: Incorrect syntax near the keyword 'WHERE'
Public Sub MasterList_Update(sender As Object, e As DataListCommandEventArgs)
Dim strProjectName, txtProjectDescription, intProjectID, strProjectState as String
Dim intEstDuration, dtmCreationDate, strCreatedBy, strProjectLead, dtmEstCompletionDate as String
strProjectName = CType(e.Item.FindControl("txtProjectName"), TextBox).Text
txtProjectDescription = CType(e.Item.FindControl("txtProjDesc"), TextBox).Text
strProjectState = CType(e.Item.FindControl("txtStatus"), TextBox).Text
intEstDuration = CType(e.Item.FindControl("txtDuration"), TextBox).Text
dtmCreationDate = CType(e.Item.FindControl("txtCreation"),TextBox).Text
strCreatedBy = CType(e.Item.FindControl("txtCreatedBy"),TextBox).Text
strProjectLead = CType(e.Item.FindControl("txtLead"),TextBox).Text
dtmEstCompletionDate = CType(e.Item.FindControl("txtComDate"),TextBox).Text
intProjectID = CType(e.Item.FindControl("lblProjectID"), Label).Text
Dim strSQL As String
strSQL = "Update tblProject " _
& "Set strProjectName = @strProjectName, " _
& "txtProjectDescription = @txtProjectDescription, " _
& "strProjectState = @strProjectState, " _
& "intEstDuration = @intEstDuration, " _
& "dtmCreationDate = @dtmCreationDate, " _
& "strCreatedBy = @strCreatedBy, " _
& "strProjectLead = @strProjectLead, " _
& "dtmEstCompletionDate = @dtmEstCompletionDate, " _
& "WHERE intProjectID = @intProjectID"
Dim myConnection As New SqlConnection(System.Configuration.ConfigurationSettings.AppSettings("connectionstring"))
Dim cmdSQL As New SqlCommand(strSQL, myConnection)
cmdSQL.Parameters.Add(new SqlParameter("@strProjectName", SqlDbType.NVarChar, 40))
cmdSQL.Parameters("@strProjectName").Value = strProjectName
cmdSQL.Parameters.Add(new SqlParameter("@txtProjectDescription", SqlDbType.NVarChar, 30))
cmdSQL.Parameters("@txtProjectDescription").Value = txtProjectDescription
cmdSQL.Parameters.Add(new SqlParameter("@strProjectState", SqlDbType.NVarChar, 30))
cmdSQL.Parameters("@strProjectState").Value = strProjectState
cmdSQL.Parameters.Add(new SqlParameter("@intEstDuration", SqlDbType.NVarChar, 60))
cmdSQL.Parameters("@intEstDuration").Value = intEstDuration
cmdSQL.Parameters.Add(new SqlParameter("@dtmCreationDate", SqlDbType.NVarChar, 15))
cmdSQL.Parameters("@dtmCreationDate").Value = dtmCreationDate
cmdSQL.Parameters.Add(new SqlParameter("@strCreatedBy", SqlDbType.NVarChar, 10))
cmdSQL.Parameters("@strCreatedBy").Value = strCreatedBy
cmdSQL.Parameters.Add(new SqlParameter("@strProjectLead", SqlDbType.NVarChar, 15))
cmdSQL.Parameters("@strProjectLead").Value = strProjectLead
cmdSQL.Parameters.Add(new SqlParameter("@dtmEstCompletionDate", SqlDbType.NVarChar, 24))
cmdSQL.Parameters("@dtmEstCompletionDate").Value = dtmEstCompletionDate
cmdSQL.Parameters.Add(new SqlParameter("@intProjectID", SqlDbType.NChar, 5))
cmdSQL.Parameters("@intProjectID").Value = intProjectID
myConnection.Open()
cmdSQL.ExecuteNonQuery
myConnection.Close()
MasterList.EditItemIndex = -1
BindMasterList()
End Sub
Thankyou in advance.
What I am trying to create a query to check, If recDT is not value or null, then will use value from SELECT top 1 recDtim FROM Serv. Otherwise, will use the value from recDT. I have tried the below query but it doesn't work. The error says, Incorrect syntax near the keyword 'SELECT'.Incorrect syntax near the keyword 'else'.1 SELECT
2 case when recDT='' then SELECT top 1 recDtim FROM Serv else recDT end
3 FROM abc
4
Anyone can help? Thanks a lot.
I am not sure how to fix this??
Imports System.Data
Imports System.Web.UI.WebControls.FormView
Imports System.Data.SqlClient
Partial Class Assignment
Inherits System.Web.UI.Page
Protected Sub UpdateButton_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles UpdateButton.Click
Dim empID As Integer
Dim nbrID As Integer
Dim equid As Integer
Dim UpdateQuery
Dim empequipid As SqlCommand
Dim mySQLConnection As String = System.Configuration.ConfigurationManager.ConnectionStrings("DBConnectionString").ToString
Dim myConnection As New SqlConnection(mySQLConnection)
myConnection.Open()
empID = (Session("UserEmployeeID"))
equid = (Session("UserEquipID"))
If CType(empID, Integer) = Nothing Then
empID = (DropDownList1.SelectedValue)
'Response.Write("Insert: " & empID)
'Response.End()
Me.SqlDataSource1.InsertCommand = "INSERT INTO [EMPLOYEES_EQUIP] ([EquipID], [EmployeeID]) VALUES " & equid & " " & empID & ""
Me.SqlDataSource1.InsertCommand = SqlDataSourceCommandType.Text
Me.SqlDataSource1.Insert()
Else
empequipid = New SqlCommand("Select EmpEquipID From EMPLOYEES_EQUIP Where EmployeeID = " & empID & " AND EquipID = " & equid & " ", myConnection)
empequipid.CommandType = CommandType.Text
nbrID = empequipid.ExecuteScalar
'Response.Write("Select EmpEquipID: " & nbrID)
'Response.End()
empID = (DropDownList1.SelectedValue)
'Response.Write("<br>Update EmployeeID: " & empID)
'Response.End()
Me.SqlDataSource1.UpdateCommand = "UPDATE EMPLOYEES_EQUIP SET EmployeeID = " & empID & " WHERE EmpEquipID = " & nbrID & " "
Me.SqlDataSource1.UpdateCommand = SqlDataSourceCommandType.Text
Me.SqlDataSource1.Update() <---Right here is displaying "Incorrect syntax near '0'."
'Added to output the Update Query
UpdateQuery = "UPDATE EMPLOYEES_EQUIP SET EmployeeID = " & empID & " WHERE EmpEquipID = " & nbrID & " "
'Response.Write(UpdateQuery)
'Response.End()
End If
'Updating the session to new EmployeeID
Session("UserEployeeID") = (DropDownList1.SelectedValue)
'Response.Write("<br>UserEmployeeID Session: " & Session("UserEmployeeID"))
'Response.End()
'-- Turn On Employee Information Table
EmpInfo.Visible = True
'-- Hides the Employee Name's Drop Down List table.
maintable.Visible = False
End Sub
End Class
Why am I getting this error.
[SqlException (0x80131904): Incorrect syntax near '.123'.] System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) +857418 System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) +735030 System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) +188 System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) +1838 System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString) +149 System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async) +886 System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result) +132 System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe) +415 System.Data.SqlClient.SqlCommand.ExecuteNonQuery() +135 WebApplication10.Edit.dgrdEdit_UpdateCommand(Object source, DataGridCommandEventArgs e) in C:Inventoryedit.aspx.vb:186 System.Web.UI.WebControls.DataGrid.OnUpdateCommand(DataGridCommandEventArgs e) +105 System.Web.UI.WebControls.DataGrid.OnBubbleEvent(Object source, EventArgs e) +471 System.Web.UI.Control.RaiseBubbleEvent(Object source, EventArgs args) +35 System.Web.UI.WebControls.DataGridItem.OnBubbleEvent(Object source, EventArgs e) +117 System.Web.UI.Control.RaiseBubbleEvent(Object source, EventArgs args) +35 System.Web.UI.WebControls.LinkButton.OnCommand(CommandEventArgs e) +115 System.Web.UI.WebControls.LinkButton.RaisePostBackEvent(String eventArgument) +163 System.Web.UI.WebControls.LinkButton.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument) +7 System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument) +11 System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData) +174 System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +5102
Hi,I am trying to test a login form and I get this error message and can't find out why. Istarted out with the Login control, but since I have to try it on the ISP's server, I can't use the SQL Server Managment Studio's integrated authentication. So, I converted the login control to a template and assigned a handler for the login button:protected void LoginButton_Click(object sender, EventArgs e){String usrname = lpLogin.UserName.ToString(); //lpLogin is the <ASP:Login ...>String conString = "Data Source=mylocalserver\SQLEXPRESS;Initial Catalog=LPRU;Integrated Security=True";String selQuery = "SELECT [Password], [FirstName], [LastName] FROM [lpUserInfo] WHERE ([UserID] ='" + usrname + "';";SqlConnection con = new SqlConnection(conString);SqlCommand cmd = new SqlCommand(selQuery, con);con.Open();SqlDataReader rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection); // <--- it says "syntax error near ';' " on this line, I tried it without CommandBehaviorwhile (rdr.Read()){Label1.Text= rdr.GetString(0) + rdr.GetString(1); // for testing purposes, trying to print out first name and last name}rdr.Close();con.Close();}Is there a way of using SQLServer 2000, used by my ISP, and take advantage of .net 2.0's login control, roles, membership, ...? By just using a connection string?
View 6 Replies View RelatedOS: Windows2kPlatform: ASP.Net 2.0.50727DB Server: SQL Server 2000 I'm getting an error that I don't understand, could someone help me please? In the interest of brevity I threw most of the query out (it's a somewhat long join). --- query += "AND TOURNAMENT_ROUNDS.END_TIME BETWEEN ? AND ?"; System.Data.SqlClient.SqlCommand cmd = new SqlCommand(query, connection); cmd.Parameters.Add("@from",System.Data.SqlDbType.DateTime).Value = from; cmd.Parameters.Add("@to", System.Data.SqlDbType.DateTime).Value = to; SqlDataAdapter da = new SqlDataAdapter(query, connection); da.SelectCommand = cmd; DataSet ds = new DataSet(); int i = 0; try { connection.Open(); da.Fill(ds); --- The da.Fill(ds) is what's throwing. I was under the impression that ADO.Net would handle the syntax so that this shouldn't be a problem. Any idea about what direction I should be looking? If you need me to post more I'll be happy to do that. I've verified that the above is where the exception is throwing by taking out the particular AND clause, and the query worked as expected. Thanks for the help,Michael
View 2 Replies View RelatedHi Guys,I have moved my asp.net app from access db over to MS SQL 2005 DB.And I have got a slight problem when I go to view any productfor example if I type in the url of http://domain.com/catalog/Details.aspx?AdNum=1I get this errorServer Error in '/catalog' Application.--------------------------------------------------------------------------------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: Incorrect syntax near '?'.I have attached the details.aspx.I await for some suggestions.ThanksMatthew
------------------1 <%@ Page MasterPageFile="Classy.master" Explicit="True" Language="VB" Debug="True" %>
2
3 <asp:Content runat="server" ID="HeaderContent" ContentPlaceHolderID="PageHeader">
4 Ad Detail - <asp:BoundField DataField="Title" HeaderText="Title" SortExpression="Title" />
5 </asp:Content>
6
7 <asp:Content runat="server" ID="BodyContent" ContentPlaceHolderID="Body" >
8
9 <script runat="server">
10 Sub Page_Load(ByVal Sender As Object, ByVal E As EventArgs)
11 If Not IsPostBack Then
12 If Request.QueryString("AdNum") = "" Then
13 Response.Redirect("default.aspx")
14 End If
15 EditLink.NavigateUrl = "confirm.aspx?AdNum=" & Request.QueryString("AdNum")
16 End If
17 End Sub
18
19 Protected Sub DetailsView1_PageIndexChanging(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.DetailsViewPageEventArgs)
20
21 End Sub
22 </script>
23
24
25 <asp:DetailsView ID="DetailsView1" runat="server" AutoGenerateRows="False"
26 CellPadding="4" DataKeyNames="AdNum" DataSourceID="SqlDataSource1" ForeColor="#333333"
27 GridLines="None" Height="65px" Width="100%" Font-Names="Arial" Font-Size="8pt" OnPageIndexChanging="DetailsView1_PageIndexChanging">
28 <RowStyle BackColor="#FFFBD6" ForeColor="#333333" />
29 <FieldHeaderStyle BackColor="#FFFF99" Font-Bold="True" />
30 <Fields>
31 <asp:BoundField DataField="Title" HeaderText="Title" SortExpression="Title" />
32 <asp:BoundField DataField="Category" HeaderText="Category" SortExpression="Category" />
33 <asp:BoundField DataField="Description" HeaderText="Description" SortExpression="Description" />
34 <asp:BoundField DataField="Price" HeaderText="Price" SortExpression="Price" />
35 <asp:BoundField DataField="Phone" HeaderText="Phone" SortExpression="Phone" />
36 <asp:TemplateField HeaderText="Email">
37 <ItemTemplate>
38 <asp:HyperLink ID="HyperLink1" runat="server" Text=Email NavigateUrl='<%# Eval("Email", "mailto:{0}") %>' />
39 </ItemTemplate>
40 </asp:TemplateField>
41 <asp:BoundField DataField="State" HeaderText="State" SortExpression="State" />
42 </Fields>
43 </asp:DetailsView>
44 <p><i>To respond to this ad, just click the email address
45 above to send the poster
46 a message.</i></p>
47 If you created this ad, you can
48 <asp:hyperlink id="EditLink" runat="server" >edit or delete it.</asp:hyperlink> <br>
49 <asp:SqlDataSource ID="SqlDataSource1" runat="server"
50 ConnectionString="<%$ ConnectionStrings:classydbConnectionString %>"
51 ProviderName="<%$ ConnectionStrings:classydbConnectionString.ProviderName %>"
52 SelectCommand="SELECT * FROM [Ads] WHERE ([AdNum] = ?)">
53 <SelectParameters>
54 <asp:QueryStringParameter Name="AdNum" QueryStringField="AdNum" Type="Int32" />
55 </SelectParameters>
56 </asp:SqlDataSource>
57
58 </asp:content>
Can anyone see why I would get the 'Incorrect Syntax near AS' error in this?
USE [SQL2]GO/****** Object: StoredProcedure [dbo].[PlanGroupGrid] Script Date: 06/18/2007 06:07:34 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOALTER PROCEDURE [dbo].[PlanGroupGrid]@dp_id intASSELECT tblExpenses.gp_ID, tblExpenses.Amount INTO #tempApExpFROM tblExpensesSELECT tblGroups.dp_id, tblGroups.Group_ID, tblGroups.Group_Type, tblGroups.Group_Purpose, tblGroups.Group_StartDate, tblGroups.Group_EndDate, COALESCE (tblGroups.Group_Goal, 0) AS Goal, SUM(COALESCE(#tempApExp.Amount,0)) AS [Total Expenses], SUM(COALESCE (tblReceipts.AmountPaid, 0)) AS [Total Paid], SUM(COALESCE (tblReceipts.AmountPaid, 0)) - COALESCE (tblGroups.Group_Goal, 0) - SUM(COALESCE(#tempApExp.Amount,0)) AS [Total Expenses], AS VarianceFROM tblGroups LEFT OUTER JOIN tblTracking ON tblGroups.Group_ID = tblTracking.Group_ID LEFT OUTER JOIN tblReceipts ON tblTracking.gp_ID = tblReceipts.gp_ID LEFT OUTER JOIN #tempApExp ON tblTracking.gp_ID = #tempApExp.gp_IDWHERE (tblGroups.dp_id = @dp_id)GROUP BY tblGroups.dp_id, tblGroups.Group_ID, tblGroups.Group_Type, tblGroups.Group_Purpose, tblGroups.Group_StartDate, tblGroups.Group_EndDate, tblGroups.Group_GoalIf Exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tempApExp]') and OBJECTPROPERTY(id,N'IsUserTable') = 1)Drop table #tempApExp
I'm getting a pop up error "Incorrect syntax near the keyword 'ORDER'.
Can someone please tell me what is wrong with my code? It worked before I added the underlined part.
If FoundChecked = True Then
Dim SQLString As String
SQLString = "SELECT ID, Bedrooms, Bathrooms, Location, Rent FROM ListingsTable WHERE "
For Each Item In LocationList.Items
If Item.Selected Then
SQLString &= " Location = '" & Item.Value & "' OR " & "" & " ORDER BY Location" ----------------------This is the problem
End If
Hi
I'm trying to lookup a sigle value from an MS SQL Express database. However, I get an error - Incorrect Syntax near '' - when executing the code. The line in bold below is what is highlighted on the error I get back. Please Help!!
The code is below
Thanks
Dim oSQLConn As Data.SqlClient.SqlConnection = New Data.SqlClient.SqlConnection()oSQLConn.ConnectionString = "Data Source=.SQLEXPRESS;AttachDbFilename=|DataDirectory|SDCentral.mdf;Integrated Security=True;User Instance=True"oSQLConn.Open() Dim ValExtract As Data.SqlClient.SqlCommand = New Data.SqlClient.SqlCommand("SELECT * FROM ProjectsQRY", oSQLConn) Dim Get1 As Int32 = CInt(ValExtract.ExecuteScalar()) Me.Active1.Text = Get1
oSQLConn.Close()
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: Incorrect syntax near ')'.Source Error:
Line 42: objAdapter.SelectCommand = New System.Data.SqlClient.SqlCommand(strSQL, objConn)Line 43: ' Fill the dataset.Line 44: objAdapter.Fill(objDataset)Line 45: ' Create a new view.Line 46: Dim oView As New DataView(objDataset.Tables(0))
Sub Page_Load(ByVal sender As Object, ByVal e As EventArgs)
Dim AllorOne As String
Dim company As String
AllorOne = Request.QueryString("AllorOne")
company = Request.QueryString("company")
' Create a connection and open it.
Dim objConn As New System.Data.SqlClient.SqlConnection("User ID=******;Password=******;Initial Catalog=customer_config;Data Source=rqa4-sql01;")
objConn.Open()
Dim strSQL As String
Dim objDataset As New DataSet()
Dim objAdapter As New System.Data.SqlClient.SqlDataAdapter()
' Get all the sql
If AllorOne = "All" Then
strSQL = "SELECT [Company_Name] as 'Company Name', [Entity_Code] as 'Entity Code', [YTD_Transactions] as 'YTD_Transactions', [Num_Reports_DB] as 'Num_Reports_DB', [Reports_Advanced_Workflow] as 'Reports_Advanced_Workflow', [Has_Customization] as 'Has_Customization',[TA_Trans_All] as 'TA Trans All', [TA_Trans_US] as 'TA Trans US', [Using_VAT] as 'Using VAT', [Total_Users] as 'Total Users',[Non_English_Langages] as 'Non English Langages',[Non_English_Users] as 'Non English Users', [Using_Offline] as 'Using Offline', [Using_Audit_Services] as 'Using Audit Services' from(v_customer_master_list) order by company_name"
Else
strSQL = "SELECT [Company_Name] as 'Company Name', [Entity_Code] as 'Entity Code', [YTD_Transactions] as 'YTD_Transactions', [Num_Reports_DB] as 'Num_Reports_DB', [Reports_Advanced_Workflow] as 'Reports_Advanced_Workflow', [Has_Customization] as 'Has_Customization',[TA_Trans_All] as 'TA Trans All', [TA_Trans_US] as 'TA Trans US', [Using_VAT] as 'Using VAT', [Total_Users] as 'Total Users',[Non_English_Langages] as 'Non English Langages',[Non_English_Users] as 'Non English Users', [Using_Offline] as 'Using Offline', [Using_Audit_Services] as 'Using Audit Services' from(v_customer_master_list) where lower(company_name) like '%" & company & "%' order by company_name"
End If
objAdapter.SelectCommand = New System.Data.SqlClient.SqlCommand(strSQL, objConn)
' Fill the dataset.
objAdapter.Fill(objDataset)
' Create a new view.
Dim oView As New DataView(objDataset.Tables(0))
' Set up the data grid and bind the data.
SimpleDataGrid.DataSource = oView
SimpleDataGrid.DataBind()
End Sub
Can anyone tell me what causes this type of error, what they think is wrong, or what IS wrong if someone wants to take the time to go through the code. Anything helps. Eh, I feel like one of those guys on a street corner with a sign when I say that.
Hi there
I'm getting an Incorrect syntax near '='. error when I click update in my gridview.
Any ideas?
<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:Taskdb %>" SelectCommand="SELECT [taskid], [taskname], [taskdescription], [taskstatus] FROM [tblTask]" UpdateCommand="UPDATE [tbltask) set [taskname] = @taskname, [taskdescription] = @taskdescription, [taskstatus] = @taskstatus where [taskid] = @taskid"> <UpdateParameters> <asp:Parameter Type="String" Name="taskname" /> <asp:Parameter Type="String" Name="taskdescription" /> <asp:Parameter Type="Int16" Name="taskstatus" /> <asp:Parameter Type="Int16" Name="taskid" /> </UpdateParameters> </asp:SqlDataSource> Thanks in advance.
Could someone please tell me what is wrong with the following? Thanks!
Dim objcmd As New SqlCommand
Dim link As String
Dim pocount As Integer
objConn.Open()objcmd = New SqlCommand("SELECT COUNT(PONumber) FROM (SELECT PONumber FROM BOL_Dtl GROUP BY PONumber, BOLNumber HAVING BOLNumber = @BOLNumber)", objConn)
objcmd.Parameters.Add("@BOLNumber", SqlDbType.Int, 4)objcmd.Parameters("@BOLNumber").Value = Int32.Parse(Request.QueryString("BOLNumber"))
pocount = objcmd.ExecuteScalar()
Hi,
I have a table 'MemberPayment', into which I would like to pass a derived value, and a value from another table (table 'Vehicle', value 'Registration'). However, I keep getting errors with my syntax... would anybody be able to kindly tell me where I am going wrong? My code is;Declare @Total int
Declare @Registration str
SELECT (@Registration=(Vehicle.Registration), @Total=((MileageHistory.MileageUrban * Vehicle.EmissionsPerGramUrban) * 0.05) + ((MileageHistory.MileageCountry * Vehicle.EmissionsPerGramUrban) * 0.05) )
FROM [NeuCar].[dbo].[MileageHistory] JOIN [NeuCar].[dbo].[Vehicle]
ON MileageHistory.Registration = Vehicle.Registration
JOIN [NeuCar].[dbo].[Member] ON Vehicle.UserName = Member.UserName
WHERE Member.UserName = 'wenger1' AND (MileageHistory.[Date] >= CURRENT_TIMESTAMP - 30)
INSERT INTO MemberPayment (Registration, TotalCharge) VALUES ( @Registration, @Total )
Many thanks for your time,
Best regards,
Chima
Hi All,
When binding my dropdownlist I get this error: Incorrect syntax near '?'.
My code behind:
'Set country drop list value c = Request.QueryString("c") ddlCountry.SelectedValue = c
'Begin populating if country has been selected ddlLeaveType.DataBind()
My HTML:
<asp:DropDownList ID="ddlLeaveType" runat="server" CssClass="AdminLeaveDDL" DataSourceID="SDSLeaveTypes" DataTextField="Description" DataValueField="PTO_Leave_Type_ID"></asp:DropDownList>
<asp:SqlDataSource ID="SDSLeaveTypes" runat="server" ConnectionString="<%$ ConnectionStrings:PTOTrackConnectionString %>" ProviderName="<%$ ConnectionStrings:PTOTrackConnectionString.ProviderName %>" SelectCommand="SELECT PTO_Leave_Type_ID, Description FROM PTO_Leave_Type WHERE PTO_Leave_Type_ID NOT IN (SELECT PTO_Leave_Type_ID FROM PTO_Country_Leave_Type WHERE Country_ID = ?) ORDER BY Description"> <SelectParameters> <asp:QueryStringParameter Name="CountryID" QueryStringField="c" Type="Int32" /> </SelectParameters> </asp:SqlDataSource>
If I rund the query in sql management studio and replace the ? with a valid id, it runs fine.
Thanks,
Mark
Could someone please explain why the above error occurs ?
Dim connString As String = ConfigurationManager.AppSettings("Fleetcube.ConnectionString")
Dim myConnection As New System.Data.SqlClient.SqlConnection(connString)Dim Str As String = "UPDATE [sec_Administrator] SET [EmailAddress] = ?, [Password] = ?, [SecurityQuestion] = ?, [SecurityAnswer] = ? WHERE [id] = " & Session.Item("Admin_id")
Dim cmd As New System.Data.SqlClient.SqlCommand(Str, myConnection)Dim ds As New DataSetDim da As New SqlClient.SqlDataAdapter(cmd)
cmd.Parameters.Add("EmailAddress", SqlDbType.VarChar).Value = txtadmin_email.Text
cmd.Parameters.Add("Password", SqlDbType.VarChar).Value = txtadmin_pass.Text
cmd.Parameters.Add("SecurityQuestion", SqlDbType.VarChar).Value = txtadmin_SecQues.Text
cmd.Parameters.Add("SecurityAnswer", SqlDbType.VarChar).Value = txtadmin_SecAns.Text
cmd.Connection.Open()
cmd.ExecuteNonQuery()
I am getting the error Incorrect syntax near ',' while executing a query.
Here is tha code snippet.String querystring="update dbo.cstd_incident_survey set Question1="+q1+",Question2="+q2+",Question3="+q3+",Question4="+q4+ ",survey_date= '" + surveydate.ToString() + "'where incident_id='" + dr3[0]+"'";SqlCommand query4 = new SqlCommand(querystring , newconnection);
query4.ExecuteNonQuery();
Please help.
Thanks in advance,
Sangita
some one can help me about this error..i always get the error like this..but stilll dun know exactly what the error..pls help me..
here the message
Incorrect syntax near the keyword 'user'.
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: Incorrect syntax near the keyword 'user'.
Source Error:
Line 51: Dim objdr As SqlDataReader
Line 52: conn.Open()
Line 53: objdr = cmd.ExecuteReader(System.Data.CommandBehavior.CloseConnection)
Line 54: List1.DataSource = objdr
Line 55: List1.DataValueField = "user_id"
Source File: c:inetpubwwwroot
egisterupdate.aspx.vb Line: 53
Stack Trace:
[SqlException: Incorrect syntax near the keyword 'user'.]
System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream)
System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior)
register.update.binddata() in c:inetpubwwwroot
egisterupdate.aspx.vb:53
register.update.Page_Load(Object sender, EventArgs e) in c:inetpubwwwroot
egisterupdate.aspx.vb:42
System.Web.UI.Control.OnLoad(EventArgs e)
System.Web.UI.Control.LoadRecursive()
System.Web.UI.Page.ProcessRequestMain()
--------------------------------------------------------------------------------