Insert Into Weekly(TesterID)
Select TesterID
FROM ALD where TesterID = 'CMT32' , 'CMT31' , 'CMT02','CMT33','CMT04','CMT36','CMT37','CMT38','CMT03','CMT05','CMT42','CMT34','CMT41','CMT40','CMT29','CMT30','CMT01','CMT39','CMT35'
Im receiving the following error while trying to execute the given query. Advice please.
Msg 102, Level 15, State 1, Line 3
Incorrect syntax near ','.
Hi All,
I've looked through the forum hoping I'm not the only one with this issue but alas, I have found nothing so I'm hoping someone out there will give me some assistance.
My problem is the case statement in my Insert Statement. My overall goal is to insert records from one table to another. But I need to be able to assign a specific value to the incoming data and thought the case statement would be the best way of doing it. I must be doing something wrong but I can't seem to see it.
Here is my code:
Insert into myTblA
(TblA_ID,
mycasefield =
case
when mycasefield = 1 then 99861
when mycasefield = 2 then 99862
when mycasefield = 3 then 99863
when mycasefield = 4 then 99864
when mycasefield = 5 then 99865
when mycasefield = 6 then 99866
when mycasefield = 7 then 99867
when mycasefield = 8 then 99868
when mycasefield = 9 then 99855
when mycasefield = 10 then 99839
end,
alt_min,
alt_max,
longitude,
latitude
(
Select MTB.LocationID
MTB.model_ID
MTB.elevation, --alt min
null, --alt max
MTB.longitude, --longitude
MTB.latitude --latitude
from MyTblB MTB
);
The error I'm getting is:
Incorrect syntax near '='.
I have tried various versions of the case statement based on examples I have found but nothing works.
I would greatly appreciate any assistance with this one. I've been smacking my head against the wall for awhile trying to find a solution.
When my production server processing some queries suddenly the SQL Server service crashed and following error was in the error log:
SQL Server Internal Error. Text manager cannot continue with current statement.
The server is running SQL Server 2000 with SP4.
I am really concerned because this is a production sever and there are over 300 users access concurrently.
Please help me to find a solution.
Thanks,
Roshan.
Hi, I m Trying TO use A sql insert Query but it showing an error
i m trying to insert value in Filed Name PNR from Str.text and Coresspond Field Name PNR1 valuse is 1 less than from pnr.text and PNR1 is a Auto Number Field
my code for insert query is
Dim q1 As OleDb.OleDbCommand = New OleDb.OleDbCommand("insert into res (PNR) values('" & Str.Text & "') where PNR1='" & pnr.Text - 1 & " ' ", con)
and Error is Shown by browser is as follows
Server Error in '/WebApplication1' Application.
Missing semicolon (;) at end of SQL statement.
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.OleDb.OleDbException: Missing semicolon (;) at end of SQL statement.Source Error:
Line 466: con.Open()
Line 467: Dim q1 As OleDb.OleDbCommand = New OleDb.OleDbCommand("insert into res (PNR) values('" & Str.Text & "') where PNR1='" & pnr.Text - 1 & " ' ", con)
Line 468: q1.ExecuteNonQuery()
Line 469: con.Close()
Line 470: End SubSource File: C:InetpubwwwrootWebApplication12.aspx.vb Line: 468 Stack Trace:
[OleDbException (0x80040e14): Missing semicolon (;) at end of SQL statement.]
System.Data.OleDb.OleDbCommand.ExecuteCommandTextErrorHandling(Int32 hr) +41
System.Data.OleDb.OleDbCommand.ExecuteCommandTextForSingleResult(tagDBPARAMS dbParams, Object& executeResult) +174
System.Data.OleDb.OleDbCommand.ExecuteCommandText(Object& executeResult) +92
System.Data.OleDb.OleDbCommand.ExecuteCommand(CommandBehavior behavior, Object& executeResult) +65
System.Data.OleDb.OleDbCommand.ExecuteReaderInternal(CommandBehavior behavior, String method) +112
System.Data.OleDb.OleDbCommand.ExecuteNonQuery() +66
WebApplication1._2.Button2_Click(Object sender, EventArgs e) in C:InetpubwwwrootWebApplication12.aspx.vb:468
System.Web.UI.WebControls.Button.OnClick(EventArgs e) +108
System.Web.UI.WebControls.Button.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument) +57
System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument) +18
System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData) +33
System.Web.UI.Page.ProcessRequestMain() +1277
Version Information: Microsoft .NET Framework Version:1.1.4322.573; ASP.NET Version:1.1.4322.573
SELECT QueryOne.StudentID, Max(QueryOne.Year) AS MaxOfYear, Count(QueryOne.StudentID) AS CountYear, IIf([CountYear]<2,"1","0") AS NewStudent, IIf([CountYear]>1,"1","0") AS ContStudent, IIf(Max([Order]-1)<5,"K-4",Max([Order])-1) AS [Grade Level], Max(Lookupgradelevel.APR_Order) AS Sorted_Grade_LevelFROM Lookupgradelevel RIGHT JOIN QueryOne ON Lookupgradelevel.gradelevel = QueryOne.GradelevelGROUP BY QueryOne.StudentIDHAVING (((Max(QueryOne.Year))=[Forms]![APR]![Year]))ORDER BY Max(Lookupgradelevel.APR_Order);---------------------------------------------------------------------------------------------------I run the above statements in sql server 2005 ,it says that there are errors near "<", and near "Max"Could you help me, thanks.
View 7 Replies View RelatedAnyone see any thing wrong with this syntax? Getting an exeption error on the update command:
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 ','.Source Error:
An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below. Stack Trace:
[SqlException (0x80131904): Incorrect syntax near ','.]
System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) +925466
System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) +800118
System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) +186
System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) +1932
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) +1005
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) +149
System.Data.SqlClient.SqlCommand.ExecuteNonQuery() +135
System.Web.UI.WebControls.SqlDataSourceView.ExecuteDbCommand(DbCommand command, DataSourceOperation operation) +404
System.Web.UI.WebControls.SqlDataSourceView.ExecuteUpdate(IDictionary keys, IDictionary values, IDictionary oldValues) +721
System.Web.UI.DataSourceView.Update(IDictionary keys, IDictionary values, IDictionary oldValues, DataSourceViewOperationCallback callback) +78
System.Web.UI.WebControls.DetailsView.HandleUpdate(String commandArg, Boolean causesValidation) +1152
System.Web.UI.WebControls.DetailsView.HandleEvent(EventArgs e, Boolean causesValidation, String validationGroup) +440
System.Web.UI.WebControls.DetailsView.OnBubbleEvent(Object source, EventArgs e) +95
System.Web.UI.Control.RaiseBubbleEvent(Object source, EventArgs args) +35
System.Web.UI.WebControls.DetailsViewRow.OnBubbleEvent(Object source, EventArgs e) +109
System.Web.UI.Control.RaiseBubbleEvent(Object source, EventArgs args) +35
System.Web.UI.WebControls.Button.OnCommand(CommandEventArgs e) +115
System.Web.UI.WebControls.Button.RaisePostBackEvent(String eventArgument) +163
System.Web.UI.WebControls.Button.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) +33
System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +1746
UPDATE tblRMADetail SET ToVendorShipDate =, ToVendorShipTime =, ToVendorShipMethod =, ToVendorTrackingNumber =, Status = WHERE (RMADetailKeyID = @original_RMADetailKeyID) AND (FKRMA = @original_FKRMA) AND (RMANumber = @original_RMANumber) AND (ItemID = @original_ItemID) AND (ItemDescription = @original_ItemDescription) AND (ItemCost = @original_ItemCost) AND (DatePlacedOnRMA = @original_DatePlacedOnRMA) AND (Quantity = @original_Quantity) AND (QuantityFilled = @original_QuantityFilled) AND (QuantityReceived = @original_QuantityReceived) AND (QuantityCancelled = @original_QuantityCancelled) AND (SONumber = @original_SONumber) AND (FKExchange = @original_FKExchange) AND (ToVendorShipDate = @original_ToVendorShipDate) AND (ToVendorShipTime = @original_ToVendorShipTime) AND (ToVendorShipMethod = @original_ToVendorShipMethod) AND (ToVendorTrackingNumber = @original_ToVendorTrackingNumber) AND (ExpectedDate = @original_ExpectedDate) AND (ExpectedTime = @original_ExpectedTime) AND (ExpectedShipMethod = @original_ExpectedShipMethod) AND (VendorReimbursementAmount = @original_VendorReimbursementAmount) AND (ReimbursementDate = @original_ReimbursementDate) AND (Status = @original_Status) AND (ItemSource = @original_ItemSource) AND (BinLocation = @original_BinLocation) AND (AccountNumber = @original_AccountNumber) AND (CustomerRMANumber = @original_CustomerRMANumber) AND (ReceivedFromCustomerDate = @original_ReceivedFromCustomerDate) AND (ShipMethodFromCustomer = @original_ShipMethodFromCustomer) AND (ContractNumber = @original_ContractNumber) AND (FKCustomerInventory = @original_FKCustomerInventory) AND (FKCustomerInventoryAssemblyDetail = @original_FKCustomerInventoryAssemblyDetail) AND (Comments = @original_Comments) AND (GLCode = @original_GLCode) AND (Serialized = @original_Serialized) AND (Type = @original_Type) AND (QuantityReturnedToStockOrCustomer = @original_QuantityReturnedToStockOrCustomer) AND (CancelDate = @original_CancelDate) AND (CancelReason = @original_CancelReason) AND (CancelledBy = @original_CancelledBy) AND (ReplacementCostDifferential = @original_ReplacementCostDifferential) AND (ReplacementGLCode = @original_ReplacementGLCode) AND (VendorInvoiceNumber = @original_VendorInvoiceNumber) AND (DropShipFlag = @original_DropShipFlag) AND (ShipToName = @original_ShipToName) AND (ShipToAddress1 = @original_ShipToAddress1) AND (ShipToAddress2 = @original_ShipToAddress2) AND (ShipToCity = @original_ShipToCity) AND (ShipToState = @original_ShipToState) AND (ShipToPostalCode = @original_ShipToPostalCode) AND (ShipToCountry = @original_ShipToCountry) AND (PONumber = @original_PONumber)
Thanks.
hi, i try the below sql code, however when i try to execute the command, it always give me the error 'Cannot use an aggregate or a subquery in an expression used for the group by list of a GROUP BY clause.'
If i remove the "(SELECT [description] = CASE WHEN ([description]) <> '' THEN [description] ELSE [tbl_batch_completed].[status] END)" under the group by clause, it will work
do I need to change something in the sql statement?
Thanks in advance
SELECT TAG_FACE_CON.REQUEST_ID,
(SELECT [description] = CASE WHEN ([description]) <> '' THEN [description] ELSE [tbl_batch_completed].[status] END)AS status
FROM (TAG_FACE_CON RIGHT JOIN tbl_batch_completed ON TAG_FACE_CON.GROUP_ID = tbl_batch_completed.ID)
GROUP BY TAG_FACE_CON.REQUEST_ID,(SELECT [description] = CASE WHEN ([description]) <> '' THEN [description] ELSE [tbl_batch_completed].[status] END)
dear sirs i am using sql server 2000 enterprise edition, i am new to sql server and also am learning the sql language...
I have a table named spt_datatype_info
that table has a column called TYPE_NAME
I have given a WHERE statement in a query:
SELECT *
FROM spt_datatype_info
WHERE (TYPE_NAME = smallint)
i know actually the value smallint has to be given in quotes...
Now my question is: When i give the Verify SQL syntax
then it does not return any error, but when i run it...then it given the following error...
[Microsoft][ODBC SQL Server Driver][SQL Server]Invalid column name 'smallint'.
what does this mean...??
If the SQL statement is wrong then it should return an error when verifying the statement...
regards,
kanishk
I have a case statement that is reading a value from a csv column, and based on where that value falls, calculates a product's a numeric value. It works on one computer, and when copied/pasted to mine, it throws errors.
This is the error: Error: "You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '121.67END' at line 1"
Here is the case statement:
CASE
WHEN [CSV_COL(8)] BETWEEN 0 AND .01 THEN ((([CSV_COL(6)] * .029) + .30) + ((([CSV_COL(6)] * .029) + .30) + [CSV_COL(6)]) / (100 - 7) * 100) + 4.36
WHEN [CSV_COL(8)] BETWEEN .011 AND .05 THEN ((([CSV_COL(6)] * .029) + .30) + ((([CSV_COL(6)] * .029) + .30) + [CSV_COL(6)]) / (100 - 7) * 100) + 5.44
WHEN [CSV_COL(8)] BETWEEN .051 AND 1 THEN ((([CSV_COL(6)] * .029) + .30) + ((([CSV_COL(6)] * .029) + .30) + [CSV_COL(6)]) / (100 - 7) * 100) + 11
[code]....
DECLARE CareModel_cursor cursor FOR SELECT DISTINCT ObAnteEvent.UrNo
FROM ObBabyEpisode INNER JOIN ObAnteEvent ON
ObAnteEvent.UrNo = ObBabyEpisode.UrNo AND ObAnteEvent.EpisodeID = ObBabyEpisode.EpisodeID
WHERE ObAnteEvent.EventType='CareModel' ORDER BY EventDate,EventTime
AND CONVERT(DateTime,ObBabyEpisode.BirthDate,103)>= Convert(DateTime,'1/1/2000',103)
AND CONVERT(DateTime, ObBabyEpisode.BirthDate,103)<= Convert(DateTime,'1/1/2007',103)
Error is at 'AND ObAnteEvent.EpisodeID = ObBabyEpisode.EpisodeID' Why is this a problem and how do I solve this?
HI
i am getting a error with my sql statement. i am trying to join to two tables together. this is the error it is giving.
Syntax error (missing operator) in query expression '12Package_Listing.Package_id LIKE 11Package.Package_id'.
my sql statement is
string strSQL1 = "select * from 12Package_Listing INNER JOIN 11Package ON 12Package_Listing.Package_id LIKE 11Package.Package_id";
I want to know, what is the error in the below block,
BEGIN
Declare @StoreId as varchar(20)
Declare @DepartmentId as varchar(20)
Declare @VendorNumber as varchar(20)
Declare @SortBy as int
Declare @AllVendorSelected as int
Declare @SQL as nvarchar(500)
Set @StoreId = '1'
Set @DepartmentId = 'NONE'
Set @VendorNumber = 'NONE'
Set @SortBy = 0
Set @AllVendorSelected = 0
SET @SQL = 'SELECT
Inventory.ItemNum
,Inventory.ItemName
,Inventory.ItemName_Extra
,Inventory.Dept_ID
,Inventory.In_Stock
,Inventory.Cost
,Inventory.Price
,Inventory.NumBoxes
,Inventory.NumPerCase
,Inventory.Store_ID
,Departments.Description
FROM
Inventory
INNER JOIN Departments ON Inventory.Store_ID = Departments.Store_ID AND Inventory.Dept_ID = Departments.Dept_ID
LEFT OUTER JOIN Inventory_Vendors ON Inventory.ItemNum = Inventory_Vendors.ItemNum AND Inventory.Store_ID = Inventory_Vendors.Store_ID
WHERE
Inventory.Store_Id in (@StoreId)
AND Inventory.Dept_ID in (@DepartmentId)
AND (@AllVendorSelected = 1 OR Inventory_Vendors.Vendor_Number in (@VendorNumber))'
IF (@SortBy = 0)
SET @SQL = @SQL + 'ORDER BY Inventory.ItemNum'
ELSE
SET @SQL = @SQL + 'ORDER BY Inventory.ItemName'
EXEC @SQL
END
hi i have copied this from my other page where it works fine and i cant understand what is going wrong! maybe one of your guys can point out what i cant see! herei s my code
string strOrderID = Request.QueryString["orderID"].ToString();int intOrderID = Convert.ToInt32(strOrderID);
int intCustID = Convert.ToInt32(Request.QueryString["qsnOrderCustID"].ToString());lblCustomerID.Text = Request.QueryString["qsnOrderCustID"].ToString();lblOrderID.Text = Request.QueryString["orderID"].ToString();
SqlConnection myConn = new SqlConnection("Data Source=xxxx;Initial Catalog=xxxx;User ID=xxxx;Password=xxxx");
//This is the sql statement.string sql = "SELECT [del_address], [del_post_code], [del_time] From tbl_del WHERE order_ID = " + intOrderID;
//This creates a sql command which executes the sql statement.SqlCommand sqlCmd = new SqlCommand(sql, myConn);
myConn.Open();SqlDataReader dr = sqlCmd.ExecuteReader();
//This reads the first result from the sqlReader
dr.Read();
try
{
lblDelTime.Text = Convert.ToString(dr["del_time"].ToString);
lblDelAddy.Text = dr["del_address"].ToString();lblDelPCode.Text = dr["del_post_code"].ToString();if (lblDelAddy.Text != "")
{
lblDelDate.Visible = true;lblDelTime.Visible = true;
Label1.Visible = true;Label2.Visible = true;
}
}catch (Exception except)
{lblerror.Text = Convert.ToString(except);
}
Regards
Jez
Receive Error: when I Run Select Statement Asking to Declare @Country
Not sure how to Declare the variable '@Country'. Any help would be appreciated... Thank You.
Here is the Code:
____________________________________________________________________________________________________________
Private Sub Page_Load(ByVal sender As Object, ByVal e As EventArgs)
If Not IsPostBack Then
Dim conCommerce As SqlConnection Dim cmdSelect As SqlCommand Dim CategoriesCounter As SqlDataReader Dim StylesSource As SqlDataReader
conCommerce = New SqlConnection(ConfigurationSettings.AppSettings("ConnectionString"))
conCommerce.Open()
cmdSelect = New SqlCommand("Select CountrySubCatCount FROM StylesSource WHere Country = @Country", conCommerce)
StylesSource = cmdSelect.ExecuteReader()
txtCounter1.DataSource = StylesSource txtCounter1.DataTextField = "Country"
txtCounter1.DataBind()
StylesSource.Close() conCommerce.Close()
End If
______________________________________________________________________________
Error Message:
Must declare the variable '@Country'. 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: Must declare the variable '@Country'.
Source Error:
Line 220: cmdSelect = New SqlCommand("Select CountrySubCatCount FROM StylesSource WHere Country = @Country", conCommerce)Line 221:Line 222: StylesSource = cmdSelect.ExecuteReader()Line 223:Line 224:
SELECT DISTINCT CONVERT (nvarchar , tblSubject.Subject, 108) AS SubjectTime, CONVERT (nvarchar(11), tblSubject.Subject, 100) AS Date FROM tblLooker,tblSubject,tblStop WHERE NOT (SELECT DISTINCT CONVERT (nvarchar , tblSubject.Subject, 108) AS SubjectTime, CONVERT (nvarchar(11), tblSubject.Subject, 100) AS Date FROM tblSubject, tblLooker, tblStop WHERE tblSubject.Subject > tblLooker.Looker AND tblSubject.Subject < tblStop.Stop AND tblLooker.id = tblStop.id) Msg 170, Level 15, State 1, Line 5Line 5: Incorrect syntax near ')'. Can anyone tell me why this is not working? Thanks
View 1 Replies View RelatedIm getting this error when i try to change my value of my int RoleID: UPDATE statement conflicted with COLUMN FOREIGN KEY
constraint 'Roles_Users_FK1'. The conflict occurred in database
'lyngso', table 'Roles', column 'RoleID'.The statement has been terminated.
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:
UPDATE statement conflicted with COLUMN FOREIGN KEY constraint
'Roles_Users_FK1'. The conflict occurred in database 'lyngso', table
'Roles', column 'RoleID'.The statement has been terminated.
Source Error:
Line 373: tryLine 374: {Line 375: rowsAffected = dbCommand.ExecuteNonQuery();Line 376: }Line 377: finallyThe code that courses this error should be this code from my DatabaseHandler class:... queryString = "SELECT unitID FROM Units WHERE containerID = @containerID AND logDateTime = @logDateTime"; dbCommand = new SqlCommand(queryString, dbConnection); //ContainerID Param IDataParameter dbParam_containerID = new SqlParameter(); dbParam_containerID.ParameterName = "@containerID"; dbParam_containerID.Value = containerID; dbParam_containerID.DbType = System.Data.DbType.String; dbCommand.Parameters.Add(dbParam_containerID); //LogDateTime Param IDataParameter dbParam_logDateTime = new SqlParameter(); dbParam_logDateTime.ParameterName = "@logDateTime"; dbParam_logDateTime.Value = logDateTime; dbParam_logDateTime.DbType = System.Data.DbType.DateTime; dbCommand.Parameters.Add(dbParam_logDateTime);...The logDateTime param comes as a param to the method this code i located in. In my database the two tables are created as the following:CREATE TABLE [dbo].[Roles] ( [RoleID] [int] IDENTITY (1, 1) NOT NULL , [Name] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL , [RoleLevel] [int] NOT NULL ) CREATE TABLE [dbo].[Users] ( [UserID] [int] IDENTITY (1, 1) NOT NULL , [Firstname] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL , [Lastname] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL , [varchar] (250) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [Phone] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [MobilPhone] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [IsActive] [bit] NOT NULL , [RoleID] [int] NOT NULL ) To me it all looks fine, but my application doesn't agree with me....can anybody help here?
Hi!
I want to select just 1 day older rows from table.
I run next statement:
select*from report
where Date = ltrim (DATEPART (dd,GETDATE())>1)+'/'+
ltrim (DATEPART(mm,GETDATE()))+'/'+ltrim (DATEPART(yyyy, GETDATE()))
the datetime in the column Date is: dd/mm/yyyy
and here is error message:
Server: Msg 170, Level 15, State 1, Line 2
Line 2: Incorrect syntax near '>'.
I tried everything , but can't get rid of this error message.
Please help!
I am getting an error message when trying to run this update statement:
update table1
set col1 = (select substring(col2,1,2) + '-' + substring(col2,3,7) from table1)
Error message:
Server: Msg 512, Level 16, State 1, Line 1
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
The statement has been terminated.
What am I doing wrong? Please help. Thanks.
Hi All,
I am trying to run an insert statement and getting an error.
Insert statement:
insert into conv_owner (name,street_num,
street_direction,street_name,street_type,street_un it,
address2,city,state,city_state,zip,wphone,inservic e,
db_name,table_name)
select "Facility's Owner",null,null,null,null,null,null,null,
null,null,null,"Owner's Telephone Number",inservice,
'BKFoodProtection.mdb','FP_Master'
from fp_master
Error message:
Server: Msg 8114, Level 16, State 5, Line 1
Error converting data type varchar to numeric.
Any idea why? Thanks.
Hi All,
I have a following update statement that returns an error:
update job_schedule_rep
set tlbkup = (select Right(Convert(VarChar(30), Convert(DateTime, Stuff(Stuf
f(Right(Replicate('0', 6) + Convert(VarChar(8), tl.active_start_time), 6), 3
,0, ':'), 6, 0, ':')), 100),7) as 'Transaction Log Backup'
from
(select name, job_id, active_start_time
from sysjobschedules
where name like 'TLBkup%') as tl
join
(select name, job_id
from sysjobs
where name like 'TL Backup%') as sjt
on sjt.job_id = tl.job_id
where sjt.name not like '%Maintenance%'
order by sjt.name)
Here is an error:
Server: Msg 512, Level 16, State 1, Line 1
Subquery returned more than 1 value. This is not permitted when the subquery
follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
The statement has been terminated.
I have tried replacing '=' with in and exists but it didn't make any differe
nce.
How can I fix it?
Thanks.
Hi,
I have a coding which state as below:
CREATE PROCEDURE [dbo].[crossTab]
@select varchar(8000),
@sumfunc varchar(100),
@pivot varchar(100),
@table varchar(100)
AS
DECLARE @sql varchar(8000), @delim varchar(1)
SET NOCOUNT ON
SET ANSI_WARNINGS OFF
EXEC ('SELECT ' + @pivot + ' AS pivot INTO ##pivot FROM ' + @table + ' WHERE 1=2')
EXEC ('INSERT INTO ##pivot SELECT DISTINCT ' + @pivot + ' FROM ' + @table + ' WHERE ' + @pivot + ' Is Not Null')
SELECT @sql='' , @sumfunc=stuff(@sumfunc, len(@sumfunc), 1, ' END)' )
SELECT @delim=CASE Sign( CharIndex('char', data_type)+CharIndex('date', data_type) )
WHEN 0 THEN '' ELSE '''' END
FROM tempdb.information_schema.columns
WHERE table_name='##pivot' AND column_name='pivot'
SELECT @sql=@sql + '''' + convert(varchar(100), pivot) + ''' = ' + stuff(@sumfunc,charindex( '(', @sumfunc )+1, 0, ' CASE ' + @pivot + ' WHEN ' + @delim + convert(varchar(100), pivot) + @delim + ' THEN ' ) + ', ' FROM ##pivot
DROP TABLE ##pivot
SELECT @sql=left(@sql, len(@sql)-1)
SELECT @select=stuff(@select, charindex(' FROM ', @select)+1, 0, ', ' + @sql + ' ')
EXEC (@select)
SET ANSI_WARNINGS ON
GO
I just woud like why it come out error stated that
Server: Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'END'.
when i key in
exec crossTab 'select dbo,fgo,rgt from Table A', 'sum(fgo)', 'rgt', 'Table A'
Please help me
Thanx
In SQL Sever 2005, I am trying to retrieve data using this simple query from a table called 'User':
select *
from dbo.User
where UserID='sam'
And, I keep getting the following error:
Msg 156, Level 15, State 1, Line 2
Incorrect syntax near the keyword 'User'.
Thank you for your help
Hi Guys, I have been trying to get this SQL string below to run, but for some reason I carry on getting an error near the EXEC Statements.
Does anyone have any idea what I doing wrong?
CREATE PROCEDURE [dbo].[UpdateWarnings]
@EndDate DateTime
AS
DECLARE @iid varchar(100)
DECLARE @fid varchar(100)
SET @iid = EXEC('SELECT id FROM memorial WHERE warnings >= 3 AND expires <= getdate()')
IF @iid <> ''
BEGIN
EXEC('UPDATE memorial SET active=0 WHERE id IN ('+ @iid +')')
END
SET @fid = EXEC('SELECT id FROM memorial WHERE warnings < 3 AND expires <= getdate()')
IF @fid <> ''
BEGIN
EXEC('UPDATE memorial SET warning=(warning+1) WHERE id IN ('+ @fid + ')')
END
IF @enddate <> ''
BEGIN
UPDATE warnings SET startdate=getdate(), enddate=@enddate, warnings=(warnings + 1) WHERE id=1
END
GO
Hi,I am relatively new to SQL. I am using SQL 2000. I am trying toUpdate a field base in a criteria in a scond table.UPDATE Tbl1SET Tbl1.Row2 = '1'WHERE Tbl1.Row1 =(SELECT Tbl1.Row1FROM Tbl2, Tbl1WHERE Tbl2.Row1 = Tbl1.Row1 AND ({ fnCURRENT_TIMESTAMP () } >= Tbl2.Row3))Row 1 is the key between the two table. If I am doing only the selectbelow, I am getting the right value.SELECT Tbl1.Row1FROM Tbl2, Tbl1WHERE Tbl2.Row1 = Tbl1.Row1 AND ({ fnCURRENT_TIMESTAMP () } >= Tbl2.Row3)When I am running the entire querry, I am getting this error:Subquery returned more than 1 value. This is not permitted when thesubquery follows =, !=, <, <= , >, >= or when the subquery is used asan expression.The statement has been terminated.What I am trying to do is to update a field in Tbl1 base on a date inTbl2. If the date is expire, I want to raise a flag, in Tbl1.Thank youPhilippe
View 4 Replies View RelatedPlease help me debug this statement:Select vVehRegNAddr.RegContChar1 From (((vVeh LEFT OUTER JOINvVehBrand ON vVeh.Plate = vVehBrand.Plate) LEFT OUTER JOINvVehRegNAddr ON vVeh.Plate = vVehRegNAddr.Plate) LEFT OUTER JOINvVehLegal ON vVeh.Plate = vVehLegal.Plate) Where ((vVeh.Active = 1)AND (vVeh.VUse NOT IN ('C/G','H/D','SNO','SNX')) AND (vVeh.LExpDt ='20050715'))ERROR MESSAGE:"Error in selection of Aggregates:If ANY aggregate functions(Count, Sum,...) are chosen,All fields mustbe aggregate that are not in Grouping"
View 2 Replies View RelatedHi
All as i have a class where i wrote a procedure for insert statement and i passed the value from the form and previously it was fine and now its giving the following error
" The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.The statement has been terminated."
And
code is as follows
Code Snippet
Public Sub Purchase_Header(ByVal BillNo As VariantType, ByVal BillDate As Date, ByVal GRNNO As VariantType, ByVal GRNdate As Date, ByVal TOP As Integer, ByVal Supplier As Integer, ByVal TotalPurchasevalue As Double, ByVal TotalProductValue As Double, ByVal BillPaid As Date, ByVal BillDue As Date, ByVal NoOfSBills As Integer, ByVal noofbreceived As Integer, ByVal billstatus As Integer, ByVal paymentstatus As Integer, ByVal appstatus As Integer, ByVal recitstatus As Boolean, ByVal sbillstatus As Boolean, ByVal ccindicator As String)
connection()
myCommand = New SqlCommand("INSERT INTO Purchase_Header(PH_Voucher_Date,PH_Bill_No,PH_Bill_Date,PH_GRN_No,PH_GRN_Date,PH_Type_Of_Purchase_Id,PH_Supplier_Id,PH_Total_Purchase_Value,PH_Total_Product_Value,PH_Bill_Paid_On,PH_Bill_Due_On,PH_No_Of_Sub_Bills,PH_No_Of_Sub_Bills_Received,PH_Bill_Status,PH_Payment_Status,PH_Apportionment_Status,PH_Goods_Receipt_Status,PH_Sub_Bill_Receipt_Status,PH_Cash_Credit_Indicator,PH_Total_Tax_Paid) VALUES('" & System.DateTime.Today & "'," & BillNo & ",'" & BillDate & "'," & GRNNO & ",'" & GRNdate & "'," & TOP & "," & Supplier & "," & TotalPurchasevalue & "," & TotalProductValue & ",'" & BillPaid & "','" & BillDue & "'," & NoOfSBills & "," & noofbreceived & "," & billstatus & "," & paymentstatus & "," & appstatus & ",'" & recitstatus & "','" & sbillstatus & "' ,'" & ccindicator & "',0)", myConnection)
myCommand.ExecuteReader()
MsgBox("Data saved successfully")
myConnection.Close()
and table structure is as follows and for date types i am passing them through date variables from textbox
INSERT INTO [MoneyBee].[dbo].[Purchase_Header]
([PH_Voucher_Date]
,[PH_Bill_No]
,[PH_Bill_Date]
,[PH_GRN_No]
,[PH_GRN_Date]
,[PH_Type_Of_Purchase_Id]
,[PH_Supplier_Id]
,[PH_Total_Purchase_Value]
,[PH_Total_Product_Value]
,[PH_Bill_Paid_On]
,[PH_Bill_Due_On]
,[PH_No_Of_Sub_Bills]
,[PH_No_Of_Sub_Bills_Received]
,[PH_Bill_Status]
,[PH_Payment_Status]
,[PH_Apportionment_Status]
,[PH_Goods_Receipt_Status]
,[PH_Sub_Bill_Receipt_Status]
,[PH_Cash_Credit_Indicator]
,[PH_Total_Tax_Paid])
VALUES
(<PH_Voucher_Date, datetime,>
,<PH_Bill_No, varchar(15),>
,<PH_Bill_Date, datetime,>
,<PH_GRN_No, numeric,>
,<PH_GRN_Date, datetime,>
,<PH_Type_Of_Purchase_Id, numeric,>
,<PH_Supplier_Id, numeric,>
,<PH_Total_Purchase_Value, numeric,>
,<PH_Total_Product_Value, numeric,>
,<PH_Bill_Paid_On, datetime,>
,<PH_Bill_Due_On, datetime,>
,<PH_No_Of_Sub_Bills, numeric,>
,<PH_No_Of_Sub_Bills_Received, numeric,>
,<PH_Bill_Status, numeric,>
,<PH_Payment_Status, numeric,>
,<PH_Apportionment_Status, numeric,>
,<PH_Goods_Receipt_Status, bit,>
,<PH_Sub_Bill_Receipt_Status, bit,>
,<PH_Cash_Credit_Indicator, varchar(6),>
,<PH_Total_Tax_Paid, numeric,>)
Thanks
Avinash
hi, i've got this code
Code Snippet
go
create proc dbo.sp_GetFilterOrgIDs(@ID int)
as
select distinct
O.ID
from
Organisations O
if (@ID != 0)
begin
where O.ID = @ID
end
and am getting
Incorrect syntax near the keyword 'where'.
any idea why??
hi
i'm having a problem with my project we need to make for school with asp.net & c#. the problem is i'm a newbie with c#.
the project we need to make is a survey. so the problem is my first question of the survey works fine when i push the go to next it enters the data nice into my sql db and my second question loads, then I enter the answer for my second question i press go to next question and i get this error: " The INSERT statement conflicted with the FOREIGN KEY constraint "FK_Antwoorden_Gebruikers". The conflict occurred in database "GIP_enquete", table "dbo.Gebruikers", column 'Gebruiker_ID'. " I don't know how i can fix this cause im to newbie to understand whats wrong.
here is a view of my code:
Code Snippet
using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using enqueteTableAdapters;
public partial class _Default : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
int Vraag_ID = Convert.ToInt16(Request.QueryString["vraagid"]);
VragenTableAdapter VraagAdapter = new VragenTableAdapter();
lblVraag.Text = Convert.ToString(VraagAdapter.GeefVraag(Vraag_ID));
if (Vraag_ID == 1)
{
pnlVraag1.Visible = true;
}
if (Vraag_ID == 2)
{
pnlVraag2.Visible = true;
}
}
protected void btnVraag1_Click(object sender, EventArgs e)
{
//make a new user
GebruikersTableAdapter GebruikerAdapter = new GebruikersTableAdapter();
DateTime Moment = System.DateTime.Now;
GebruikerAdapter.GebruikerToevoegen(Moment);
int GebruikerID = Convert.ToInt16(GebruikerAdapter.GeefGebruikerID(Moment));
this.ViewState.Add("gebruiker_id", GebruikerID);
//send answer
AntwoordenTableAdapter AntwoordAdapter = new AntwoordenTableAdapter();
AntwoordAdapter.AntwoordIngeven(1, GebruikerID, txtVraag1.Text, null);
//go to 2nd question
Response.Redirect("Default.aspx?vraagid=2", true);
}
protected void btnVraag2_Click(object sender, EventArgs e)
{
//get back the user from question 1
int GebruikerID = Convert.ToInt16(this.ViewState["gebruiker_id"]);
//send answer
AntwoordenTableAdapter AntwoordAdapter = new AntwoordenTableAdapter();
AntwoordAdapter.AntwoordIngeven(2, GebruikerID, txtVraag2.Text, null);
when i run debug it allways stops here
//go too 3th question
Response.Redirect("Default.aspx?vraagid=3", true);
}
}
can somebody plz help me ? =(
ps: sorry for my bad english
I have a piece of code that uses the db-library with sql server 2000/2005 and runs the following delete statement:
DELETE FROM TABLE1 WHERE COL1 IN( 'Some Val1' ) AND COL2 IN( 'Some Val2' ) AND Col3 IN( integer1 ) AND Col4 IN( integer2 ) AND Col5 IN( 'Some Val3' )
on TABLE1, uploads data into TABLE1 through bulk loading, calls a stored procedure that uses the data, and then deletes the data through the SAME delete statement with EXACTLY the same parameter values. The first delete statement is always successful, but the second statement intermittently gives the following error:
0,0,MS SQL Server Message :
SQL Server message 512, state 1, severity 16:
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
SQL Server message 3621, state 0, severity 0:
The statement has been terminated.
Note: I was initially using the equality operator instead of the IN operator in the query but that gave the same results.
Can somebody tell me whats going wrong here? I can easily ignore this error because my work is done after the stored proc but I fear amassing a lot of useless data in the table over time. Also http://support.microsoft.com/kb/195491 talks about a case where the delete statement is actually successful but still causes an error when using ADO.
Hi!
I have the following error during setting partner on mirror server
Msg 1431, Level 16, State 4, Line 1
Neither the partner nor the witness server instance for database "masterserver" is available. Reissue the command when at least one of the instances becomes available.
The partner is available through telnet. I've also checked ports vai netstat and have no found errors.
There are two noteworthy erros in the error log at mirror server
Error: 9642, Severity: 16, State: 3.
and
An error occurred in a Service Broker/Database Mirroring transport connection endpoint, Error: 8474, State: 11. (Near endpoint role: Target, far endpoint address: '')
Security settings it seems are set accurately.
I am trying to figure out the syntax for the following:
=iif(fields!ExtTripCount.value = 1,sum(Fields!ExtTripCount.value),0) / (count(fields!SvcCallNumber.value))* 100
I am trying to take all the trips that equal a one and divide them by the total number of service calls and then multiple them by 100 to get the percentage.
I am receiving the following errror: "The value expression refers to the field 'SvcCallNumber'. Report item expressions can only refer to fields within the current dta set scope or, if inside an aggregate, the specified data set scope...
Hi,I try to insert record using select statement. But, I am having a problem with this code:Insert into PaymentVoucherTable (ID, Duration1, min1, Total1, Duration2, min2, Total2)select (ID, Duration1, min1, (Duration1 * min1), (select Duration2,min2,(Duration2 * min2) from Table2)from Table1The MSSQL keep displaying error message : "the number of column in INSERT statement is not the same as the select statement...".Is my subselect statement structure correct ???Thanks...
View 2 Replies View RelatedOk, the following four lines are four lines of code that I'm running, I'll post the code and then explain my issue:
sqlCommand = New SQLCommand("INSERT INTO Bulk (Bulk_Run, Bulk_Totes, Bulk_Drums, Bulk_Boxes, Bulk_Bags, Bulk_Bins, Bulk_Crates) VALUES (" & RunList(x,0) & ", " & Totes & ", " & Drums & ", " & Boxes & ", " & Bags & ", " & Bins & ", " & Crates & ")", Connection) sqlCommand.ExecuteNonQuery() sqlCommand = New SQLCommand("INSERT INTO Presort (Presort_Run, Presort_Totes, Presort_Drums, Presort_Boxes, Presort_Bags, Presort_Bins, Presort_Crates) VALUES (" & RunList(x,0) & ", " & Totes & ", " & Drums & ", " & Boxes & ", " & Bags & ", " & Bins & ", " & Crates & ")", Connection) sqlCommand.ExecuteNonQuery()
The two tables (Bulk & Presort) are <b>exactly</b> the same. This includes columns, primary keys, IDs, and even permissions. If I run the last two liens (the INSERT INTO Presort) then it works fine without error. But whenever I run the first two lines (the INSERT INTO Bulk) I get the following error:
Incorrect syntax near the keyword 'Bulk'.
Anyone have any ideas, thanks