hello everyone,
i want to do something like this in stored procedure
declare @Dop as varchar
declare @AnyVar as int
set @Dop = '>'
select * from Table1 where Field @Dop @AnyVar
but it dont work
what can i do ?
Thank you
Hi guys
need some help, someone has given me some code to help me filter a gridview by the surname field by the selected letter of a dropdown list.
My code is below, however if i select a letter from the dropdown and click the button i get an error, can someone take a look please. <%@ Page Language="VB" MasterPageFile="~/MasterPage.master" Title="Untitled Page" %>
<script runat="server">
Protected Sub Button1_Click(ByVal sender As Object, ByVal e As System.EventArgs)
Dim dset As New Data.DataSet
Dim dview As New Data.DataView
dview.Table = dset.Tables("modelRegistration1")
dview.RowFilter = "surname LIKE" + surnameFilter.SelectedValue.ToString() + " % "
GridView1.DataSource = dview
GridView1.DataBind()
End Sub
</script>
<asp:Content ID="Content1" ContentPlaceHolderID="ContentPlaceHolder1" Runat="Server">
<table>
<tr>
<td style="width: 100px">
Surname Filter:</td>
<td style="width: 100px">
<asp:DropDownList ID="surnameFilter" runat="server" AppendDataBoundItems="True">
<asp:ListItem> - Please Select -</asp:ListItem>
<asp:ListItem Value="A">A</asp:ListItem>
<asp:ListItem Value="B">B</asp:ListItem>
<asp:ListItem Value="C">C</asp:ListItem>
<asp:ListItem Value="D">D</asp:ListItem>
<asp:ListItem Value="E">E</asp:ListItem>
<asp:ListItem Value="F">F</asp:ListItem>
<asp:ListItem Value="G">G</asp:ListItem>
<asp:ListItem Value="H">H</asp:ListItem>
<asp:ListItem Value="I">I</asp:ListItem>
<asp:ListItem Value="J">J</asp:ListItem>
<asp:ListItem Value="K">K</asp:ListItem>
<asp:ListItem Value="L">L</asp:ListItem>
<asp:ListItem Value="M">M</asp:ListItem>
<asp:ListItem Value="N">N</asp:ListItem>
<asp:ListItem Value="O">O</asp:ListItem>
<asp:ListItem Value="P">P</asp:ListItem>
<asp:ListItem Value="Q">Q</asp:ListItem>
<asp:ListItem Value="R">R</asp:ListItem>
<asp:ListItem Value="S">S</asp:ListItem>
<asp:ListItem Value="T">T</asp:ListItem>
<asp:ListItem Value="U">U</asp:ListItem>
<asp:ListItem Value="V">V</asp:ListItem>
<asp:ListItem Value="W">W</asp:ListItem>
<asp:ListItem Value="X">X</asp:ListItem>
<asp:ListItem Value="Y">Y</asp:ListItem>
<asp:ListItem Value="Z">Z</asp:ListItem>
</asp:DropDownList></td>
<td style="width: 100px">
<asp:Button ID="Button1" runat="server" Text="Filter:" OnClick="Button1_Click" /></td>
<td style="width: 100px">
</td>
<td style="width: 100px">
</td>
<td style="width: 100px">
</td>
</tr>
<tr>
<td style="width: 100px">
</td>
<td style="width: 100px">
</td>
<td style="width: 100px">
</td>
<td style="width: 100px">
</td>
<td style="width: 100px">
</td>
<td style="width: 100px">
</td>
</tr>
</table>
<br />
<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" DataKeyNames="ID"
DataSourceID="getModel" AllowPaging="True" EmptyDataText="N/A" PageSize="15">
<Columns>
<asp:BoundField DataField="ID" HeaderText="ID" InsertVisible="False" ReadOnly="True"
SortExpression="ID" />
<asp:BoundField DataField="applicationDate" HeaderText="Sign Up Date" SortExpression="applicationDate" />
<asp:BoundField DataField="firstName" HeaderText="First Name" SortExpression="firstName" />
<asp:BoundField DataField="surname" HeaderText="Surname" SortExpression="surname" />
<asp:BoundField DataField="dateOfBirth" HeaderText="DOB" SortExpression="dateOfBirth" />
<asp:BoundField DataField="postcode" HeaderText="Post Code" SortExpression="postcode" />
<asp:TemplateField HeaderText="Male" SortExpression="isMale">
<ItemTemplate>
<asp:CheckBox ID="isMale" runat="server" Checked='<%# Eval("isMale") %>' />
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="Female" SortExpression="isFemale">
<ItemTemplate>
<asp:CheckBox ID="isFemale" runat="server" Checked='<%# Eval("isFemale") %>' />
</ItemTemplate>
</asp:TemplateField>
</Columns>
</asp:GridView>
<asp:ObjectDataSource ID="getModel" runat="server"
SelectMethod="GetDataBySurname" TypeName="KayKayDataTableAdapters.modelRegistration1TableAdapter">
</asp:ObjectDataSource>
</asp:Content>
error message is
Server Error in '/KayKay' Application.
Syntax error: Missing operand after 'LIKEC' operator.
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.SyntaxErrorException: Syntax error: Missing operand after 'LIKEC' operator.Source Error:
Line 6: Dim dview As New Data.DataView
Line 7: dview.Table = dset.Tables("modelRegistration1")
Line 8: dview.RowFilter = "surname LIKE" + surnameFilter.SelectedValue.ToString() + " % "
Line 9: GridView1.DataSource = dview
Line 10: GridView1.DataBind()Source File: E:InetpubwwwrootKayKayAdminupdateModels.aspx Line: 8 Stack Trace:
[SyntaxErrorException: Syntax error: Missing operand after 'LIKEC' operator.]
System.Data.ExpressionParser.Parse() +3158
System.Data.DataExpression..ctor(DataTable table, String expression, Type type) +109
System.Data.DataView.set_RowFilter(String value) +171
ASP.admin_updatemodels_aspx.Button1_Click(Object sender, EventArgs e) in E:InetpubwwwrootKayKayAdminupdateModels.aspx:8
System.Web.UI.WebControls.Button.OnClick(EventArgs e) +75
System.Web.UI.WebControls.Button.RaisePostBackEvent(String eventArgument) +97
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) +4921
I have a stored procedure that works fine on SQL Server 2000 or 2005,but when run on an machine running MSDE, I get the Operand type clash,int is incompatible with text data type error. The data type isactually text and text is the type of data being passed. Anyparameters not being used are defaulted to null. Any ideas why thiswould happen on MSDE and work fine anywhere else?Thanks so much.
View 2 Replies View RelatedHello!I am trying to update a DetailsView but I keep getting the error of Operand type clash: int is incompatible with text. I have double checked the stored procedure but I don't see any obvious offending parameters. ID is the only parameter that is an interger. I am not trying to update the ID at all, just for the filter purposes to update the records. ALTER PROCEDURE [dbo].[proc_update] /* ( @parameter1 int = 5, @parameter2 datatype OUTPUT ) */ @BUILDING_SV float, @BUILDING_ADDS float, @BUILDING_DEL float, @BUILDING_TOTAL float, @ME_SV float, @ME_ADDS float, @ME_DEL float, @ME_TOTAL float, @PATTERNS_SV float, @PATTERNS_ADDS float, @PATTERNS_DEL float, @PATTERNS_TOTAL float, @SS_VALUE float, @SS_TOT float, @COMMENTS text, @OCCUPANCY_TYPE varchar(255), @REVISED_BY varchar(255), @DRAWINGS float, @INVENTORY float, @TOTAL float, @DIVISION varchar(255), @LOCATION varchar(255), @LOCATIONCODE varchar(255), @ADDRESS1 varchar(255), @ADDRESS2 varchar(255), @ADDRESS3 varchar(255), @ADDRESS4 varchar(255), @PROP_ID float, @POST_DATE smalldatetime, @ID intAS /* SET NOCOUNT ON */ UPDATE dbo.PROPERTY_VALUES SET BUILDING_SV = @BUILDING_SV, BUILDING_ADDS = @BUILDING_ADDS, BUILDING_DEL = @BUILDING_DEL, BUILDING_TOTAL = @BUILDING_TOTAL, ME_SV = @ME_SV, ME_ADDS = @ME_ADDS, ME_DEL = @ME_DEL, ME_TOTAL = @ME_TOTAL, PATTERNS_SV = @PATTERNS_SV, PATTERNS_ADDS = @PATTERNS_ADDS, PATTERNS_DEL = @PATTERNS_DEL, PATTERNS_TOTAL = @PATTERNS_TOTAL, SS_VALUE = @SS_VALUE, SS_TOT = @SS_TOT, COMMENTS = @COMMENTS,OCCUPANCY_TYPE = @OCCUPANCY_TYPE, REVISED_BY = @REVISED_BY, DRAWINGS = @DRAWINGS, INVENTORY = @INVENTORY, TOTAL = @TOTAL, DIVISION = @DIVISION, LOCATION = @LOCATION, LOCATIONCODE = @LOCATIONCODE, ADDRESS1 = @ADDRESS1, ADDRESS2 = @ADDRESS2, ADDRESS3 = @ADDRESS3, ADDRESS4 = @ADDRESS4, PROP_ID = @PROP_ID, POST_DATE = @POST_DATE WHERE [ID] = @ID RETURN Help! Thanks.
View 13 Replies View RelatedI'm getting this error when trying to insert data into a table.
"Operand type clash: int is incompatible with date"
This is the SQL I used to create the table:
Code:
CREATE TABLE customers(
customer_id int IDENTITY(1,1) NOT NULL,
member_yn char(1) NOT NULL,
membership_number int NOT NULL,
date_became_member date NOT NULL,
[Code]....
This is the SQL I am using to add data: (Note that there are two dates in each line, scroll to the side.)
Code:
INSERT INTO customers VALUES ('y', 156, 2010-08-29, 'John', 'Smith', '1235 Main Street, Dunmore, PA 18512', 6963287654, 'jsmith@hotmail.com', 1986-06-23);
INSERT INTO customers VALUES ('y', 159, 04/15/2011, 'Mary', 'Jones', '235 Ardmore Blvd, Pittsburgh, PA 15221', 3429831594, 'jones_mary@gmail.com', 01/12/1992);
Each of the lines above to add data uses a different data format because I tried both, while commenting out the other, hoping for the problem to go away with the change in format.
Here is the SQL code to create a primary key for this table:
Code:
ALTER TABLE customers ADD CONSTRAINT pk_customers PRIMARY KEY (customer_id);
I am trying to combine columns and add it to an existing table
insert into Future_Link.dbo.tbFLink (Fastighet, Anlaggning)
values (
(select strFastbeteckningTrakt + ' ' + strFastbeteckningBlock + strFastbeteckningTecken + intFastbeteckningEnhet
from EDPFutureGagnef.dbo.tbFuAnlaggning),
(select strAnlnr
from EDPFutureGagnef.dbo.tbFuAnlaggning)
)
Generate error:
Msg 206, Level 16, State 2, Line 1
Operand type clash: int is incompatible with text
Im trying to insert a new record into tblMessages...the unique ID generated for MessageID is the value I want to use as a value for the MessageID field when inserting a new record in tblUsersAndMessagesI have the following sp:
-- ================================================
-- Template generated from Template Explorer using:
-- Create Procedure (New Menu).SQL
--
-- Use the Specify Values for Template Parameters
-- command (Ctrl-Shift-M) to fill in the parameter
-- values below.
--
-- This block of comments will not be included in
-- the definition of the procedure.
-- ================================================
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
CREATE PROCEDURE spNewMessage
@UserIDSender nvarchar(256),
@MessageTitle nvarchar(50),
@MessageContent text,
@MessageType int,
@MessageID uniqueidentifier,
@UserID uniqueidentifier
AS
Begin
Set NoCount on
DECLARE @WhateverID uniqueidentifier
INSERT INTO tblMessages(UserIDSender,MessageTitle,MessageContent,MessageType)
VALUES (@UserIDSender,@MessageTitle,@MessageContent,@MessageType)
Select @WhateverID=@@Identity
INSERT INTO tblUsersAndMessages(MessageID,UserID)
VALUES (@WhateverID,@UserID)
End
GO
And here are my tables:tblUsersAndMessages allow nullsMessageID uniqueidentifier falseUserID uniqueidentifier falseNew bit false *default set to ((1))
tblMessages allow nullsMessageID uniqueidentifier false *PK default set to (newid())UserIDSender uniqueidentifier falseMessageTitle nvarchar(50) trueMessageContent text trueSentDateTime datetime false * default set to (getdate())MessageType int false
Now when I try to add my SP I get this error:Msg 206, Level 16, State 2, Procedure spNewMessage, Line 22Operand type clash: numeric is incompatible with uniqueidentifierWhat can this be?!?
I uploading an image from a web page into Sql Server 2000 database. I call a stored procedure with the parameter @Flag of type Image. When I tried to pass a null value to this parameter I got the error:"Operand type clash: nvarchar is incompatible with image".I was adding the parameter like this:sqlCmd.Parameters.AddWithValue("@Flag", DBNull.Value);After trying few different things I found a workaround using the following:sqlCmd.Parameters.Add("@Flag", SqlDbType.Image);sqlCmd.Parameters["@Flag"].Value = DBNull.Value;Possibly a bug in the SqlCommand.Parameters.AddWithValue method ?
View 1 Replies View RelatedI am using the below query for calculation and I get this error.
Operand data type nvarchar is invalid for sum operator.
'$ '+ REVERSE(SUBSTRING(REVERSE(CONVERT(varchar,(CAST(round(isnull(sum(t7.[Pre Override Cost]),0),0) as money) + 1 -
cast(round(isnull(sum(t8.[Shared Dollars]),0),0)as money)),1)),4,255)) as [PreOverride L2],
'$ '+ REVERSE(SUBSTRING(REVERSE(CONVERT(varchar,(CAST(round(isnull(sum(t7.[Post Override Cost]),0),0) as money) -
cast(round(isnull(sum(t8.[Shared Dollars]),0),0)as money)),1)),4,255)) as [PostOverride L2]
This is a piece of my code:
SELECT TOP 1000 *...
case when Rownum<= datediff(day, salesdate, baseenddate)
then DATEADD(mm, RowNum, salesdate) /*error at this point*/
else 0 end as subscriptionrowdate
FROM Subsrow
Rownum is an integer type. DATEADD is the part when the error is but i dont know how to convert this to int
Hi all I am trying to convert the string "(null)" in the [PASSWORD] column of my table to an actual NULL value. I have tried to use two different forms of a conditional operator to achieve this end. However I am getting the below errors both can be summed up with the following statement.
DT_STR operand cannot be used with the conditional operation. The expression directly below however is using a type DT_I4 in the conditional clause as this is what FINDSTRING returns. Hence the equivalencey test to the literal integer 0. So I must say I am somewhat confused by this. Does anyone know why neither of the below statements are working?
Also is there an easy way to accomplish what I am trying to do - convert the string "(null)" in the [PASSWORD] column of my table to an actual NULL value?
FINDSTRING([PASSWORD], "(null)", 1) == 0 ? [PASSWORD] : NULL(DT_STR, 255, 1252)
Error at Administrator Data Flow Task [Derived Column [1985]]: For operands of the conditional operator, the data type DT_STR is supported only for input columns and cast operations. The expression "FINDSTRING(PASSWORD,"(null)",1) == 0 ? PASSWORD : NULL(DT_STR,255,1252)" has a DT_STR operand that is not an input column or the result of a cast, and cannot be used with the conditional operation. To perform this operation, the operand needs to be explicitly cast with a cast operator.
LOWER( TRIM( [PASSWORD] ) ) != "(null)" ? [PASSWORD] : NULL(DT_STR, 255, 1252)
Error at Administrator Data Flow Task [Derived Column [1985]]: For operands of the conditional operator, the data type DT_STR is supported only for input columns and cast operations. The expression "LOWER(TRIM(PASSWORD)) != "(null)" ? PASSWORD : NULL(DT_STR,255,1252)" has a DT_STR operand that is not an input column or the result of a cast, and cannot be used with the conditional operation. To perform this operation, the operand needs to be explicitly cast with a cast operator.
I get this error "Operand data type varchar is invalid for divide operator".
select V.[Parent Name],
[ID],
round((V.SoftValue/VTMValue)*100,0) 'SPercentage',
round((V.HMUValue/VTMValue)*100,0) 'HPercentage2',
round((V.PrimaryValue/VTMValue)*100,0) 'PPercentage2'
[code]...
I have even converted the values to float.