I am parsing a documnet and i am grabbing that document into a variable of datatype varchar. The document contains more than 8k(upto 30k characters) characters. So how can I handle this situation.How can I get that document into my local varchar variable.
thanks.
Can i still get the sum of a field even if the data type is of type varchar?
thanks!
Funnyfrog
I am using a stored procedure which returns a value of charecter datatype 'V' to the calling program.I am getting an sql exception System.Data.SqlClient.SqlException: Syntax error converting the varchar value 'V' to a column of data type inti didnot define any int datatype in my tablethis is my codeSqlCommand com = new SqlCommand("StoredProcedure4", connection);com.CommandType = CommandType.StoredProcedure; SqlParameter p1 = com.Parameters.Add("@uname", SqlDbType.NVarChar);SqlParameter p2 = com.Parameters.Add("@opwd", SqlDbType.NVarChar);SqlParameter p3 = com.Parameters.Add("@role", SqlDbType.NVarChar);p3.Direction = ParameterDirection.ReturnValue;p1.Value = username.Text.Trim();p2.Value = password.Text.Trim();com.ExecuteReader();lblerror2.Text = (string)(com.Parameters["@role"].Value); can your figure out what is the error ? Is it a coding error or error of the databse
View 3 Replies View RelatedI encounter this particular error.
Exception Details: System.Data.SqlClient.SqlException: The data types varchar and text are incompatible in the equal to operator.
Line 21: Dim reader As SqlDataReader = command.ExecuteReader()
This is the first time I'm trying out with MS SQL so I'm abit lost. I hope my code is correct and I've did a little search. I did not set "Text" in my database, I use int and varchar. Here's the affected part of my code and the database. Dim password As String = ""
Dim querystring As String = "SELECT Password FROM Member WHERE Username = @username"
'Dim conn as SqlConnection
Using conn As New SqlConnection(ConfigurationManager.ConnectionStrings("mainconnect").ConnectionString)
Dim command As New SqlCommand(querystring, conn)
command.Parameters.Add("@username", SqlDbType.Text)
command.Parameters("@username").Value = txtLogin.Text
conn.Open()
Dim reader As SqlDataReader = command.ExecuteReader()
While reader.Read()
password = reader("Password").ToString()
End While
reader.Close()
End Using
My database:
User_ID int(4)
Username varchar(50)
Password varchar(255)
Email varchar(50)
Any ideas?
can anyone see as to why I would get this error with the following SP?
ALTER PROCEDURE [dbo].[SP]
@ID int = 0,
@emailFrom VARCHAR(50) = Null,
@emailDate VARCHAR(50) = Null,
@emailSubj VARCHAR(50) = Null,
@emailTxtBody VARCHAR(1000) = Null,
@emailHtmlBody VARCHAR(1000) = Null
AS
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
DECLARE @Notes VARCHAR (8000)
DECLARE @TicketID INT
DECLARE @emailBody VARCHAR (1000)
DECLARE @Length Int
SET @Notes = ''
SET @Length = LEN(@emailSubj)
-- insert a new entry
INSERT INTO PopEmail
( emailFrom, emailDate, emailSubj, emailTxtBody, emailHtmlBody )
VALUES
( @emailFrom, @emailDate, @emailSubj, @emailTxtBody, @emailHtmlBody )
-- get the new ID
SET @ID = @@identity
If ( @ID <> 0 ) AND ( ISNUMERIC(@emailSubj) = 1 )
Begin
IF @emailTxtBody IS NULL
BEGIN
Set @emailBody = @emailHtmlBody
PRINT '@emailHtmlBody: ' + @emailBody
END
ELSE
BEGIN
SET @emailBody = @emailTxtBody
PRINT '@emailTxtBody: ' + @emailBody
END
SET @TicketID = CAST( @emailSubj AS int )
SET @Notes = @emailFrom + ', ' + @emailDate + ', ' + @emailBody
Select @ID = ID From TicketDetails Where TicketDetails.TicketID = @TicketID
Exec differentSP @ID, @TicketID, @Notes
PRINT 'Subject: [' + @emailSubj + ']'
print 'length: ' + CAST(@Length as varchar (10))
Print 'emailSubj: ' + CAST( @emailSubj AS int )
PRINT 'ID: ' + Cast( @ID as varchar ( 10 ) )
PRINT 'TicketID: ' + Cast( @TicketID AS Varchar ( 10 ) )
PRINT 'Notes: ' + @Notes
PRINT 'ID: ' + Cast( @ID as varchar ( 10 ) )
END
ELSE
BEGIN
Print 'ID: ' + CAST(@ID AS VarChar(10))
PRINT 'ISNUMERIC: ' + CAST(ISNUMERIC(@emailSubj) AS VarChar (10))
PRINT 'Subject: [' + @emailSubj + ']'
END
I have a table with a column (locationID) of int data type, I want to pass a list of locationID's to a stored procedure using the IN operator (e.g. WHERE LocationID IN (list of locationID's)). What can I do to achieve this????
My stored procedure looks like this:
CREATE PROCEDURE [sp_test]
@sLocIDs varchar(30)
AS
select count(distinct TimeZoneID) As timezones,
from dimLocation
WHERE LocationID IN (@sLocIDs)
When I test it:
exec sp_test
@sLocIDs = '1,2,10'
I get "Error converting data type varchar to int"
when i execute my store procedure it give this error.
USE [CWMNEW]
GO
DECLARE@return_value int
EXEC@return_value = [dbo].[BOQ]
SELECT'Return Value' = @return_value
GO
[code]....
Hi,
I have a parameter @name varchar(50)
but the table has a field with datatype GUID
how I can convert varchar to guid before insertion...
Thanks
Hi,
It is not exactly what I stated in the subject - It's an outcome - exception thrown while executing non-query command.
I get this exception when I try to execute my stored procedure that takes datetime as one of its parameters.
I am using dataset designer to create table adapters and build queries. Then I simply use objectdatasource component that uses one of the table adapters and bind it to for example a detailsview control.
When I run this in debug mode and trace the objects everything looks perfect including these datetime parameters. It is sql server that throws the exception. I ran the sql profiler to see what exactly is going on, and I captured the command that is sent by ADO - it's broken into several lines right in the middle of my datetime parameters... this is the source of the problem. Everything is working fine when I take this command and execute it as a single line in the sql management studio.
Is there anything about ADO that I do not know?
I have the following query which has been giving me headaches for days : SELECT SCCode [Service Catalog Code], FileName [File Name], FullName [Full Name], FileExtension [Extension], FileSize [Size], Author [Author], CONVERT(VARCHAR(50),CAST(LastModified AS SMALLDATETIME), 120) [Last Modified], CONVERT(VARCHAR(50),CAST(LastAccessed AS SMALLDATETIME), 120) [Last Accessed], TimesAccessed [Downloads], UploadedBy [Uploaded By], CONVERT(VARCHAR(50),CAST(UploadedAt AS SMALLDATETIME), 120) [Uploaded At] FROM #TempTable WHERE id > @firstitem AND id < @lastitem ORDER BY CASE WHEN @sortcolumn = 'Service Catalog Code' THEN SCCode WHEN @sortcolumn = 'File Name' THEN FileName WHEN @sortcolumn = 'Extension' THEN FileExtension WHEN @sortcolumn = 'Size' THEN FileSize WHEN @sortcolumn = 'Author' THEN Author WHEN @sortcolumn = 'Last Modified' THEN CONVERT(VARCHAR(50),CAST(LastModified AS SMALLDATETIME), 120) WHEN @sortcolumn = 'Last Accessed' THEN CONVERT(VARCHAR(50),CAST(LastAccessed AS SMALLDATETIME), 120) WHEN @sortcolumn = 'Downloads' THEN TimesAccessed WHEN @sortcolumn = 'Uploaded By' THEN UploadedBy WHEN @sortcolumn = 'Uploaded At' THEN CONVERT(VARCHAR(50),CAST(UploadedAt AS SMALLDATETIME), 120) END ASCWhen my @sortcolumn parameter is either FileSize (BIGINT) or TimesAccessed (BIGINT) then the query returns the data without any problem. However, if I use a different @sortcolumn value such as "Author" then I keep getting "Error converting data type varchar to bigint."A solution that seems to work is to drop CASE and use lots of IF statements instead. I don't prefer to do this because it requires me to repeat the same select statement for each and every possible @sortcolumn value!Does anyone know how to solve this? Help!
View 2 Replies View Relatedhi
i want to pass a datetime variable from a asp page to MSSQL through a stored procedure, & i get this error:
Microsoft OLE DB Provider for ODBC Drivers (0x80040E07)
[Microsoft][ODBC SQL Server Driver][SQL Server]Error converting data type varchar to datetime.
this is the code that i use in page:
strsql="sp_inventory @timee=' " & RecievedDate & " ' "
also i use this one:
but i still get this error
strsql="sp_inventory @timee=' " & cdate(RecievedDate) & " ' "
this is maybe useful that i get this errot on some PCs, not all the computeres.
sometimes work well, & sth not work.
Here is the stored procedure
the data type for the column b_trn_tentdte is decimal(8,0).
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
ALTER Procedure Prc_New_cus_wo_prev_posted
@b_trn_tendte_Beg_Date datetime
,@b_trn_tendte_End_Date datetime
@b_trn_tendte_Beg_Date1 datetime
,@b_trn_tendte_End_Date2 datetime
as
declare @Beg_tentdte datetime
declare @End_tentdte datetime
declare @Beg_tentdte1 datetime
declare @Beg_tentdte2 datetime
set @Beg_tentdte = convert(varchar(10), @b_trn_tendte_Beg_Date, 112)
set @End_tentdte = convert(varchar(10), @b_trn_tendte_End_Date, 112)
set @Beg_tentdte1 = convert(varchar(10), @b_trn_tendte_Beg_Date1, 112)
set @Beg_tentdte2 = convert(varchar(10), @b_trn_tendte_End_Date2, 112)
/* this query looks for customers with posted
transactions but none prior to the date range
in question */
Select distinct b_cus_cname
from
bar_cus_db_rec
,bar_trn_db_rec b
where b.b_trn_instid = ''
and b.b_trn_instid = b_cus_instid
and b.b_trn_actid = b_cus_cusid
and convert(varchar(10), b.b_trn_tentdte) between @Beg_tentdte and @End_tentdte
and not exists (select c.b_trn_actid
from bar_trn_db_rec c
where c.b_trn_instid = b.b_trn_instid
--and c.b_trn_tentdte between 19970901 and 20030229
and convert(varchar(10), c.b_trn_tentdte) between @Beg_tentdte1 and @Beg_tentdte2
and c.b_trn_actid = b.b_trn_actid)
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
every time I run the query, I get this error message:
Error converting data type varchar to datetime.
thanks for your assistance.
Error converting data type varchar to float.
I am getting this error. Any one there to guide me
An application i am using creates an error.
Using SQL-profiler i catch this statement:
exec sp_executesql N'insert into RECORDING_SCHEDULE (programme_oid, capture_source_oid, filename, status, recording_group, recording_type, manual_channel_oid,
manual_start_time, manual_end_time, quality_level, pre_pad_minutes, post_pad_minutes) values (@P1,@P2,@P3,@P4,@P5,@P6,@P7,@P8,@P9,@P10,@P11,@P1 2)',N'@P1 int,@P2 int,@P3
nvarchar(19),@P4 int,@P5 int,@P6 int,@P7 int,@P8 datetime,@P9 datetime,@P10 int,@P11 int,@P12 int',177748,2,N'Lilla röda traktorn',4,40,5,16,''2006-06-21
17:00:00:000'',''2006-06-21 20:00:00:000'',1,1,2
After removing the double ' around the date and time parts i get the following error when running in QA:
Msg 8114, Level 16, State 5, Line 0
Error converting data type varchar to datetime.
If a remove the time portion it works.
The DDL for the table is:
CREATE TABLE [dbo].[RECORDING_SCHEDULE](
[oid] [int] IDENTITY(1,1) NOT NULL,
[programme_oid] [int] NULL,
[capture_source_oid] [int] NULL,
[status] [smallint] NOT NULL,
[filename] [varchar](255) NULL,
[recording_type] [int] NULL,
[recording_group] [int] NULL,
[manual_start_time] [datetime] NULL,
[manual_end_time] [datetime] NULL,
[manual_channel_oid] [int] NULL,
[quality_level] [int] NULL CONSTRAINT [DF__RECORDING__quali__38996AB5] DEFAULT ((0)),
[pre_pad_minutes] [int] NULL CONSTRAINT [DF__RECORDING__pre_p__398D8EEE] DEFAULT ((0)),
[post_pad_minutes] [int] NULL CONSTRAINT [DF__RECORDING__post___3A81B327] DEFAULT ((0)),
CONSTRAINT [aaaaaRECORDING_SCHEDULE_PK] PRIMARY KEY NONCLUSTERED
What am I missing?
I have a sql code that I am having some difficulty with. All I'm trying to do is get each department and sum the amount by month based on the date in the table. I am getting "error converting data type varchar to numeric".
Code:
select DEPARTMENT,
CASE
WHEN ((DATE >= '06/01/2014') AND (DATE <= '06/30/2014')) THEN (cast(sum(Amount) as decimal(10,2)))
ELSE '' END AS 'JUNE',
CASE
WHEN ((DATE >= '07/01/2014') AND (DATE <= '07/30/2014')) THEN (cast(sum(Amount) as decimal(10,2)))
ELSE '' END AS 'JULY'
from L27_PHAROS_DETAIL_DATA
WHERE Department = 'TECHNOLOGY SERVICES'
GROUP BY DEPARTMENT, DATE
ORDER BY DEPARTMENT
Hi Group:
I try to JOin to tables: Enterprise has a decimal(5,3) and COHIER has varchar 5. All I am getting is an error on converting data. How can I fix this problem.
SELECT a.Security_Value, b.Enterprise_Number, b.Enterprise_Description
FROM dbo.COHIER a INNER JOIN
dbo.Enterprise b ON a.Security_Value = b.Enterprise_Number
Thanks!!!!!!
I have a column which is
Data type : varchar(10)
Date format : 8/5/2013
I want to convert that column to the following
Data type varchar: (8)
Date format : 20130805
i have this data:
BookedIDBooked_Date
1141996362013-01-09 14:55:21.740
would like to get the output into txt format with delimited in between columns.
i tried this but getting this error: Error converting data type varchar to bigint.
select BookedID+'|'+Booked_Date from tableA
Select SchoolNumber + ' ' + ShortSchoolName as SchoolList from tblSchoolsSubset
This is my simple sql statement which generates the error in the subject line.
SchoolNumber is type numeric and ShortSchoolName is nvarchar
There are only numbers in SchoolNumber.
Yes again a topic like this, I couldn't get an answer for my problem so I started a new topic.
Im getting this error for these lines:
$registreer = "INSERT INTO Gebruikerstelefoon (gebruikersnaam, telefoonnummer, volgnr) VALUES ('".$_SESSION['gebruikersnaam']."', '".$_POST["telefoonnummer"]."', '')";
mssql_query($registreer) or die("Fout bij toewijzen telefoonnummer.");
'telefoonnummer' (phonenumber) is nummeric, users can insert their phonenumber in a input field (text) and register along.. ( this is a second query for the column phonennumber) though this doesn't work..
Hi guys,
Been a while, also tried doing a search but no go.
Im getting this error in sql analyser:
Server: Msg 8114, Level 16, State 5, Line 1
Error converting data type varchar to numeric
Both queries work fine individually, im trying to append them together, with column columns as below:
SELECT PERIOD.STARTDATE,
PERIOD.DESCRIPTION,
COMMISSION_TXN.PERIODSEQ,
COMMISSION_TXN.SUPER_DISTRIBUTOR_CODE,
COMMISSION_TXN.PREMISE_CODE,
COMMISSION_TXN.DISTRIBUTOR_CODE,
COMMISSION_TXN.INTERNAL_EXTERNAL,
COMMISSION_TXN.SERVICE_ID,
COMMISSION_TXN.ACCOUNT_NUMBER,
COMMISSION_TXN.PROMOTION_CODE,
COMMISSION_TXN.PRODUCTDESCRIPTION,
COMMISSION_TXN.PRODUCTID,
COMMISSION_TXN.PROMOTION_NAME,
COMMISSION_TXN.EVENTTYPEID,
COMMISSION_TXN.MEASUREMENT_NAME,
Mobile_revenue = ('No Mobile revenue for txn level'),
COMMISSION_TXN.COMMISSION_AMOUNT,
product_id = ('No product id for txn level')
FROM COMMISSION_TXN INNER JOIN
PERIOD ON COMMISSION_TXN.PERIODSEQ = PERIOD.PERIODSEQ
union all
SELECT DISTINCT
PERIOD.STARTDATE,
PERIOD.DESCRIPTION,
TC_Mobile_Usage.PERIODSEQ,
Dealer_Hierarchy.SUPER_DISTRIBUTOR_CODE,
TC_Mobile_Usage.GENERICATTRIBUTE1 AS Dealer_premise_code,
Dealer_Hierarchy.DISTRIBUTOR_CODE,
internal_external = ('External'),
TC_Mobile_Usage.GENERICATTRIBUTE2 AS Service_Id,
account_number = ('none for txn file'),
promotion_code = ('Usage'),
Product_description = ('Usage'),
Product_id = ('Usage'),
promotion_name = ('Usage'),
TC_Mobile_Usage.EVENTTYPEID,
measurement_name = ('Usage'),
sum(TC_Mobile_Usage.VT_VALUE) AS Mobile_revenue,
sum(TC_Mobile_Usage.CONTRIBUTIONVALUE) AS Commission_Amount,
TC_Mobile_Usage.PRODUCTID
FROM TC_Mobile_Usage INNER JOIN
PERIOD ON TC_Mobile_Usage.PERIODSEQ = PERIOD.PERIODSEQ INNER JOIN
Dealer_Hierarchy ON TC_Mobile_Usage.GENERICATTRIBUTE1 = Dealer_Hierarchy.PARTICIPANTID
WHERE (NOT (TC_Mobile_Usage.PRODUCTID = 'iddv')) AND (NOT (Dealer_Hierarchy.POSITIONTYPEID LIKE 'MI %')) AND
(TC_Mobile_Usage.PERIODSEQ IN (107))--, 108, 110, 111, 112, 114))
group by PERIOD.STARTDATE,
PERIOD.DESCRIPTION,
TC_Mobile_Usage.PERIODSEQ,
Dealer_Hierarchy.SUPER_DISTRIBUTOR_CODE,
TC_Mobile_Usage.GENERICATTRIBUTE1,
Dealer_Hierarchy.DISTRIBUTOR_CODE,
TC_Mobile_Usage.GENERICATTRIBUTE2,
TC_Mobile_Usage.EVENTTYPEID,
TC_Mobile_Usage.PRODUCTID
Any suggestion on how to fix it?
Champinco
Hi
Please help me out
declare @CID numeric(3,0)
set @CID = 121
SELECT *
from Table_name
where ID Like ''''+@CID+'%'''
I am getting error like
Msg 8114, Level 16, State 5, Line 3
Error converting data type varchar to numeric.
T.I.A
I am having difficulty with my sql and I am not sure what do to. I am new to this. Any help you can give would be helpful.
Here is the statement that is giving me trouble. If I take this out of my Sql query everything runs fine.
CASE clm_att1
WHEN 'NG' THEN (clm_sppo)*(clio_fee04/100)
WHEN 'NA' THEN '0.00'
WHEN 'AF' THEN (clm_sppo)*(clio_fee04/100)*.65
ELSE ''
END as AccessFeeFinal,
Below is my full query.
SELECT
--clms_sku,
--clms_line,
--clm_prod,
clio_type,
clm.clm_id1,
clm.clm_rcvd,
CLM_DOUT,
clm_wkpct,
clm.clm_1a,
clm.clm_12a,
clm.clm_12b,
clm.clm_55d,
clm.clm_clir,
clm.clm_65a,
clm.clm_medb2,
clm.clm_tchg,
clm.clm_base,
clm.clm_stades,
clm.clm_meda4,
CLM_H30 =
Case
When CLM_H30 = 0.00 Then clm_sppo
else clm_H30
End,
clm_prod =
CASE
WHEN CLMS_sku = 'NEGO' THEN 'NEG'
WHEN CLMS_sku = '% OF CHGS' THEN 'NEG'
ELSE clm_prod
End,
"clm_nego" =
CASE
WHEN CLM_ATT1 = 'NA' THEN 0.00
WHEN CLM_ATT2 = 'NA' THEN 0.00
WHEN CLM_ATT3 = 'NA' THEN 0.00
WHEN CLM_ATT4 = 'NA' THEN 0.00
ELSE clm_nego
END,
"clm_sppo" =
CASE
WHEN CLM_ATT1 = 'NA' THEN 0.00
WHEN CLM_ATT2 = 'NA' THEN 0.00
WHEN CLM_ATT3 = 'NA' THEN 0.00
WHEN CLM_ATT4 = 'NA' THEN 0.00
ELSE clm_sppo
END,
clm_1e,
clm.CLM_ATT1 as Note,
CASE clm_att1
WHEN 'NG' THEN (clm_sppo)*(clio_fee04/100)
WHEN 'NA' THEN '0.00'
WHEN 'AF' THEN (clm_sppo)*(clio_fee04/100)*.65
ELSE ''
END as AccessFeeFinal,
CLM_ATT2,
CLM_ATT3,
clio_fee04 as "ACCESSFEEIMPACT",
"(clm_sppo/CLM_TCHG) * 100" =
CASE
WHEN CLM_ATT1 = 'NA' THEN 0.00
WHEN CLM_ATT2 = 'NA' THEN 0.00
WHEN CLM_ATT3 = 'NA' THEN 0.00
WHEN CLM_ATT4 = 'NA' THEN 0.00
ELSE (clm_sppo/CLM_TCHG) * 100
END,
clio_wrk1 as MAS90#
FROM dbo.clm clm Join
dbo.cli cli ON clm.clm_clir = cli.cli_id1 JOIN
dbo.clip clip ON cli.cli_id1 = clip.clip_id1 JOIN
dbo.clio clio ON cli.cli_id1 = clio.clio_id1
INNER JOIN dbo.clms clms ON clms.clms_id = clm.clm_id1
WHERE
(clip.clip_prd IN ('NEG', 'ADV')) AND
(clm_prod = 'ADV' OR clms_sku = 'NEGO' OR clms_sku = '% OF CHGS')AND
(CLM_STADES IN ('DONE','DUPO','DUPL'))and
clm_adjto = '' AND CLM_TCHG > 0. and
(clio.clio_type = 'AC')
I am having problems with a statment that I am writing.
The prop_uac is a alpha field and prop_disc is a numeric field. What I was trying to do is when the alpha field is not populated automatically pull from the numeric field. Then I wanted to put all of the results into one column. Can this be done? below is my statement. I hope you can help me.
Prop_uac =
CASE
WHEN prop_uac = '' THEN prop_disc
else prop_uac
End
hello everyone... i have some problem to with my trigger.. after execute the trigger.. i got this errro "Msg 8114, Level 16, State 5, Procedure trg_InsertPVReadingMeter, Line 14
Error converting data type varchar to numeric."
below is my command sql table PVReadingMeter
quote:USE [PVMC Database]
GO
/****** Object: Table [dbo].[PVReadingMeter] Script Date: 03/25/2008 01:18:06 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[PVReadingMeter](
[PV_reading_id] [uniqueidentifier] NOT NULL CONSTRAINT [DF_PVReadingMeter_PV_reading_id] DEFAULT (newid()),
[PV_reading_date_time] [datetime] NOT NULL,
[PV_reading_meter] [decimal](8, 2) NOT NULL,
[PV_normalized_monthly_yield] [float] NOT NULL,
[PV_normalized_monthly_energy_yield] [float] NOT NULL,
[PV_application_id] [uniqueidentifier] NOT NULL CONSTRAINT [DF_PVReadingMeter_PV_application_id] DEFAULT (newid()),
CONSTRAINT [PK_PVReadingMeter] PRIMARY KEY CLUSTERED
(
[PV_reading_id] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
USE [PVMC Database]
GO
ALTER TABLE [dbo].[PVReadingMeter] WITH CHECK ADD CONSTRAINT [FK_PVReadingMeter_Photovoltaic] FOREIGN KEY([PV_application_id])
REFERENCES [dbo].[Photovoltaic] ([PV_application_id])
ON UPDATE CASCADE
ON DELETE CASCADE
below is my trigger
quote:set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
CREATE Trigger [trg_InsertPVReadingMeter]
on [dbo].[PVReadingMeter]
for insert
as
begin
DECLARE @PVReadingMeter VARCHAR(100)
DECLARE @PVNormalizedMonthlyYield VARCHAR(100)
SELECT @PVReadingMeter = (SELECT PV_reading_meter + '' FROM Inserted)
SELECT @PVNormalizedMonthlyYield = (SELECT PV_normalized_monthly_yield + '' FROM Inserted)
-- Print the name of the new author
PRINT 'The new photovoltaic reading meter"' + @PVReadingMeter +'" is added.'
PRINT 'The photovoltaic normalized monthly yield is "' + @PVNormalizedMonthlyYield +'"'
end;
now, this is command to insert the values into PVReadingMeter table using trigger that has been created....
quote:insert into PVReadingMeter (PV_reading_date_time, PV_reading_meter, PV_normalized_monthly_yield,
PV_normalized_monthly_energy_yield, PV_application_id)
values (getDate(), 1.1, 4234.33, 43.44, '3D23C4FE-F6FC-475E-8CEC-23FE4E738EB9')
so, i got this error... Msg 8114, Level 16, State 5, Procedure trg_InsertPVReadingMeter, Line 14
Error converting data type varchar to numeric.
what should i do???
hello anyone... i got this message "Error converting data type varchar to float" when i was trying to insert values into table using instead of trigger...
below is my table ClimateData
quote:USE [PVMC Database]
GO
/****** Object: Table [dbo].[ClimateData] Script Date: 03/26/2008 03:04:44 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[ClimateData](
[Climate_application_id] [uniqueidentifier] NOT NULL CONSTRAINT [DF_ClimateData_Climate_application_id] DEFAULT (newid()),
[Latitude] [float] NULL,
[Longitude] [float] NULL,
[Altitude] [varchar](50) COLLATE Latin1_General_CI_AI NULL,
[Climate_type] [varchar](100) COLLATE Latin1_General_CI_AI NULL,
[PV_application_id] [uniqueidentifier] NULL,
CONSTRAINT [PK_ClimateData_1] PRIMARY KEY CLUSTERED
(
[Climate_application_id] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
USE [PVMC Database]
GO
ALTER TABLE [dbo].[ClimateData] WITH CHECK ADD CONSTRAINT [FK_ClimateData_Photovoltaic] FOREIGN KEY([PV_application_id])
REFERENCES [dbo].[Photovoltaic] ([PV_application_id])
ON UPDATE CASCADE
ON DELETE CASCADE
Below is photovoltaic table
quote:USE [PVMC Database]
GO
/****** Object: Table [dbo].[Photovoltaic] Script Date: 03/26/2008 03:06:58 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Photovoltaic](
[PV_application_id] [uniqueidentifier] NOT NULL CONSTRAINT [DF_Photovoltaic_PV_application_id] DEFAULT (newid()),
[PV_site] [varchar](50) COLLATE Latin1_General_CI_AI NULL,
[PV_state] [varchar](50) COLLATE Latin1_General_CI_AI NULL,
[PV_type_of_system] [varchar](50) COLLATE Latin1_General_CI_AI NULL,
[PV_nominal_power] [float] NULL,
[PV_module] [varchar](150) COLLATE Latin1_General_CI_AI NULL,
[PV_mounting] [varchar](50) COLLATE Latin1_General_CI_AI NULL,
[PV_building_type] [varchar](50) COLLATE Latin1_General_CI_AI NULL,
[PV_topology] [varchar](50) COLLATE Latin1_General_CI_AI NULL,
[PV_new_or_retrofit] [varchar](50) COLLATE Latin1_General_CI_AI NULL,
[PV_period_of_design] [varchar](50) COLLATE Latin1_General_CI_AI NULL,
[PV_period_of_construction] [varchar](50) COLLATE Latin1_General_CI_AI NULL,
[PV_commissioning_date] [datetime] NULL CONSTRAINT [DF_Photovoltaic_PV_commissioning_date] DEFAULT (getdate()),
[PV_site_photo] [varbinary](max) NULL,
[PV_peak_nominal_rating] [float] NULL,
[User_application_id] [uniqueidentifier] NULL,
[Org_application_id] [uniqueidentifier] NULL,
CONSTRAINT [PK_Photovoltaic_1] PRIMARY KEY CLUSTERED
(
[PV_application_id] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
USE [PVMC Database]
GO
ALTER TABLE [dbo].[Photovoltaic] WITH CHECK ADD CONSTRAINT [FK_Photovoltaic_OrganizationDetail] FOREIGN KEY([Org_application_id])
REFERENCES [dbo].[OrganizationDetail] ([Org_application_id])
ON UPDATE CASCADE
ON DELETE CASCADE
GO
ALTER TABLE [dbo].[Photovoltaic] WITH CHECK ADD CONSTRAINT [FK_Photovoltaic_Users] FOREIGN KEY([User_application_id])
REFERENCES [dbo].[Users] ([User_application_id])
ON UPDATE CASCADE
ON DELETE CASCADE
Below also my command for instead of trigger
quote:set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
CREATE trigger [tr_v_PhotovoltaicClimateData] on [dbo].[v_PhotovoltaicClimateData] instead of insert as
BEGIN
insert Photovoltaic (PV_site, PV_state, PV_type_of_system, PV_nominal_power, PV_module,
PV_mounting)
select distinct inserted.PV_site, inserted.PV_state, inserted.PV_type_of_system,
inserted.PV_nominal_power, inserted.PV_module, inserted.PV_mounting
from inserted left join Photovoltaic on inserted.PV_site = Photovoltaic.PV_site
and inserted.PV_state = Photovoltaic.PV_state
and inserted.PV_type_of_system = Photovoltaic.PV_type_of_system
and inserted.PV_nominal_power = Photovoltaic.PV_nominal_power
and inserted.PV_nominal_power = Photovoltaic.PV_module
and inserted.PV_nominal_power = Photovoltaic.PV_mounting
where Photovoltaic.PV_site IS NULL /*** Exclude Organization Detail already in the table ***/
insert ClimateData (Latitude, Longitude, Altitude, Climate_type, PV_application_id)
select distinct inserted.Latitude, inserted.Longitude, inserted.Altitude, inserted.Climate_type,
Photovoltaic.PV_application_id
from inserted inner join Photovoltaic on inserted.PV_site = Photovoltaic.PV_site
left join ClimateData on inserted.Latitude = ClimateData.Latitude
and inserted.Longitude = ClimateData.Longitude
and inserted.Altitude = ClimateData.Altitude
and inserted.Climate_type = ClimateData.Climate_type
where ClimateData.Latitude IS NULL /*** Exclude Organization Types already in the table ***/
END -- trigger def
and finally, i hav tried using this command to insert into table v_PhotovoltaicClimateData.. this is the command to insert
quote:insert into v_PhotovoltaicClimateData(PV_site, PV_state, PV_type_of_system, PV_nominal_power,
PV_module, PV_mounting, Latitude, Longitude, Altitude, Climate_type)
values ('CETDEM', 'Sequ Inspection', 'Cheras', 34.44, 'ew', 'ewew',
34343.44, 323.32, 'ewew', 'ewewe')
and i got this error...
quote:Msg 8114, Level 16, State 5, Procedure tr_v_PhotovoltaicClimateData, Line 6
Error converting data type varchar to float.
so anyone... plzz help me...
im trying to convert this Code from Varchar to Numeric(CAST(ISNULL(del.[Day 60 AMT],0) AS NUMERIC(6,3))+ CAST(ISNULL(del.[Day 90 AMT],0) AS NUMERIC(6,3)) + CAST(ISNULL(del.[Day 120 AMT],0)AS NUMERIC(6,3)) + CAST(ISNULL(del.[Day 150 AMT],0)AS NUMERIC(6,3)) + CAST(ISNULL(del.[Day 180 AMT],0)AS NUMERIC(6,3)) + CAST(ISNULL(del.[Day 210+ AMT],0)AS NUMERIC(6,3))) > 0
i got an error please help.
Hello everyone... i have some problem with instead of trigger... after insert values into v_PhotovoltaicClimateData, i got this error, Msg 8114, Level 16, State 5, Procedure tr_v_PhotovoltaicClimateData, Line 6
Error converting data type varchar to float.
below is my DDL for Photovoltaic table
quote:CREATE TABLE [dbo].[Photovoltaic](
[PV_application_id] [uniqueidentifier] NOT NULL CONSTRAINT [DF_Photovoltaic_PV_application_id] DEFAULT (newid()),
[PV_site] [varchar](50) COLLATE Latin1_General_CI_AI NULL,
[PV_state] [varchar](50) COLLATE Latin1_General_CI_AI NULL,
[PV_type_of_system] [varchar](50) COLLATE Latin1_General_CI_AI NULL,
[PV_nominal_power] [float] NULL,
[PV_module] [varchar](150) COLLATE Latin1_General_CI_AI NULL,
[PV_mounting] [varchar](50) COLLATE Latin1_General_CI_AI NULL,
[PV_building_type] [varchar](50) COLLATE Latin1_General_CI_AI NULL,
[PV_topology] [varchar](50) COLLATE Latin1_General_CI_AI NULL,
[PV_new_or_retrofit] [varchar](50) COLLATE Latin1_General_CI_AI NULL,
[PV_period_of_design] [varchar](50) COLLATE Latin1_General_CI_AI NULL,
[PV_period_of_construction] [varchar](50) COLLATE Latin1_General_CI_AI NULL,
[PV_commissioning_date] [datetime] NULL CONSTRAINT [DF_Photovoltaic_PV_commissioning_date] DEFAULT (getdate()),
[PV_site_photo] [varbinary](max) NULL,
[PV_peak_nominal_rating] [float] NULL,
[User_application_id] [uniqueidentifier] NULL,
[Org_application_id] [uniqueidentifier] NULL,
CONSTRAINT [PK_Photovoltaic_1] PRIMARY KEY CLUSTERED
(
[PV_application_id] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
Below is my DDL for ClimateData table
quote:CREATE TABLE [dbo].[ClimateData](
[Climate_application_id] [uniqueidentifier] NOT NULL CONSTRAINT [DF_ClimateData_Climate_application_id] DEFAULT (newid()),
[Latitude] [float] NULL,
[Longitude] [float] NULL,
[Altitude] [varchar](50) COLLATE Latin1_General_CI_AI NULL,
[Climate_type] [varchar](100) COLLATE Latin1_General_CI_AI NULL,
[PV_application_id] [uniqueidentifier] NULL,
CONSTRAINT [PK_ClimateData_1] PRIMARY KEY CLUSTERED
(
[Climate_application_id] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
below is my v_PhotovoltaicClimateData
quote:CREATE VIEW [dbo].[v_PhotovoltaicClimateData]
AS
SELECT dbo.Photovoltaic.PV_site, dbo.Photovoltaic.PV_state, dbo.Photovoltaic.PV_type_of_system, dbo.Photovoltaic.PV_nominal_power,
dbo.Photovoltaic.PV_module, dbo.Photovoltaic.PV_mounting, dbo.ClimateData.Latitude, dbo.ClimateData.Longitude, dbo.ClimateData.Altitude,
dbo.ClimateData.Climate_type
FROM dbo.ClimateData INNER JOIN
dbo.Photovoltaic ON dbo.ClimateData.PV_application_id = dbo.Photovoltaic.PV_application_id
below is my instead of trigger command...
quote:CREATE trigger [tr_v_PhotovoltaicClimateData] on [dbo].[v_PhotovoltaicClimateData] instead of insert as
BEGIN
insert Photovoltaic (PV_site, PV_state, PV_type_of_system, PV_nominal_power, PV_module,
PV_mounting)
select distinct inserted.PV_site, inserted.PV_state, inserted.PV_type_of_system,
inserted.PV_nominal_power, inserted.PV_module, inserted.PV_mounting
from inserted left join Photovoltaic on inserted.PV_site = Photovoltaic.PV_site
and inserted.PV_state = Photovoltaic.PV_state
and inserted.PV_type_of_system = Photovoltaic.PV_type_of_system
and inserted.PV_nominal_power = Photovoltaic.PV_nominal_power
and inserted.PV_nominal_power = Photovoltaic.PV_module
and inserted.PV_nominal_power = Photovoltaic.PV_mounting
where Photovoltaic.PV_site IS NULL /*** Exclude Photovoltaic already in the table ***/
insert ClimateData (Latitude, Longitude, Altitude, Climate_type, PV_application_id)
select distinct inserted.Latitude, inserted.Longitude, inserted.Altitude, inserted.Climate_type,
Photovoltaic.PV_application_id
from inserted inner join Photovoltaic on inserted.PV_site = Photovoltaic.PV_site
left join ClimateData on inserted.Latitude = ClimateData.Latitude
and inserted.Longitude = ClimateData.Longitude
and inserted.Altitude = ClimateData.Altitude
and inserted.Climate_type = ClimateData.Climate_type
where ClimateData.Latitude IS NULL /*** Exclude Climate Data already in the table ***/
END -- trigger def
this is my commad insert values using instead of trigger that i've created...
quote:insert into v_PhotovoltaicClimateData(PV_site, PV_state, PV_type_of_system, PV_nominal_power,
PV_module, PV_mounting, Latitude, Longitude, Altitude, Climate_type)
values ('CETDEM', 'Sequ Inspection', 'Cheras', 34.44 , 'ew', 'ewew',
34343.44, 323.32, 'ewew', 'ewewe')
after execute this commad, i got this error..
quote:Msg 8114, Level 16, State 5, Procedure tr_v_PhotovoltaicClimateData, Line 6
Error converting data type varchar to float.
what should i do??? anyone help me.
I have created a stored procedure that contain this field (below) inorder to meet certain criteria. But my problem is when I try to runthe stored procedure I encounter an error "Error converting data typevarchar to float".CASE Final WHEN 0 THEN '--' ELSE Final END AS FinalGradeThe Final field is a float data type.Could anyone teach me how to fix this problem?
View 1 Replies View RelatedTable:Student
Name nvarchar(1000)
Status int
In the stored procedure - I have got a string which contains comma separated Status Values such as
DECLARE @validStatus = '1000,1001,1002'
I want to return the count of students having the status code as in @validStatus
SELECT count(1) FROM Student WHERE Status in (@validStatus)
But the above statement is erroring out with
Conversion failed when converting the varchar value '1000,1001,1002' to data type int.
Any help/suggestion appreciated.
Thanks,
Loonysan
DECLARE @ENTITY nvarchar (100)
set @ENTITY = 'AccidentDimension'
DECLARE @FIELD nvarchar (100)
set @FIELD = 'JurisdictionState'
DECLARE @KEYID nvarchar (100)
SET @KEYID = '1234567890'
DECLARE @VALUE nvarchar (100)
SET @VALUE = 'WI'
DECLARE @WC_TABLE NVARCHAR(100)
SET @WC_TABLE = 'WorkingCopyAdd' + @ENTITY
DECLARE @SQL1 NVARCHAR (1000)
SET @SQL1 = 'INSERT INTO ' + @WC_TABLE+ ' (Claim, '+ @Field +') VALUES ('''+ @KEYID +''', '''+@VALUE+''')'
EXECUTE sp_executesql @SQL1
Can somebody help me. I get this error:
Error converting data type varchar to numeric.
while executing this Dynamic TSQl Command
Hi guys,
My query was working fine until I added the yellow highlighted areas below.
Basically what I'm doing with this is getting the lowest open purchase order number (docnum) and its matching docduedate.
Now when I run it I get
Conversion failed when converting the varchar value 'po_num' to data type int.
Any ideas? Thanks!
SELECT t3.product ,
t7.itemname ,
t2.u_vlgx_plc,
t3.shorted ,
t4.onhand ,
MIN(
CASE
WHEN t8.linestatus = 'O'
THEN t9.docnum
ELSE NULL
END) po_num,
t10.docduedate
FROM
(SELECT t0.product product ,
SUM(
CASE
WHEN t0.qty_topick <> t0.qty_picked
THEN t0.qty_topick - t0.qty_picked
ELSE 0
END) shorted
FROM rbeacon.dbo.shipline2 t0
INNER JOIN rbeacon.dbo.shiphist t1
ON t0.packslip = t1.packslip
WHERE t1.date_upld = CONVERT(VARCHAR(10), GETDATE()-1, 101)
GROUP BY t0.product
) t3
INNER JOIN comparison.dbo.vlgxplc t2
ON t2.itemcode = t3.product COLLATE Latin1_General_CI_AS
LEFT JOIN
(SELECT t0.product AS product,
SUM(t0.quantity) AS onhand
FROM rbeacon.dbo.binlocat t0
GROUP BY t0.product
) t4
ON t3.product = t4.product
INNER JOIN wbau.dbo.oitm t5
ON t3.product = t5.itemcode COLLATE SQL_Latin1_General_CP850_CI_AS
LEFT JOIN wbau.dbo.ocrd t6
ON t5.cardcode = t6.cardcode
INNER JOIN wbau.dbo.oitm t7
ON t3.product = t7.itemcode COLLATE SQL_Latin1_General_CP850_CI_AS
LEFT JOIN wbau.dbo.por1 t8
ON t3.product = t8.itemcode COLLATE SQL_Latin1_General_CP850_CI_AS
LEFT JOIN wbau.dbo.opor t9
ON t8.docentry = t9.docentry
LEFT JOIN comparison.dbo.podatetest('po_num') t10
ON t10.docnum = t9.docnum
WHERE t3.shorted <> 0
GROUP BY t3.product ,
t7.itemname ,
t2.u_vlgx_plc,
t3.shorted ,
t4.onhand ,
t6.cardname,
t10.docduedate
ORDER BY t2.u_vlgx_plc,
t6.cardname ,
t3.product
I have designed a SP where i need to update all the records for a table where ErrorId is not equal to the ones provided.In this stored procedure i am parsing and all the errorids delimited by ',' into a varchar variable which i would be using for updating the table.On the second last line i get the error mentioned in the subject line.any help would be appreciated.
ALTER PROCEDURE [dbo].[sp_ParseAndUpdateDetails]
@NozzleID int,
@ParserString varchar(MAX)
AS
BEGIN
DECLARE @NextPos int
DECLARE @LoopCond tinyint
DECLARE @PreviousPos int
DECLARE @FlgFirst bit
DECLARE @QueryCondition varchar(MAX)
SET @LoopCond=1
SET @NextPos =0
SET @FlgFirst=0
SET @QueryCondition=''
WHILE (@LoopCond=1)
BEGIN
--Retrieving the Position of the delimiter
SET @NextPos =@NextPos + 1
SET @NextPos = CHARINDEX(',',@ParserString, @NextPos)
--Retreiving the last substring
IF(@NextPos=0)
BEGIN
PRINT SUBSTRING(@ParserString,@PreviousPos + 1,(LEN(@ParserString)+1)- @PreviousPos)
SET @QueryCondition= @QueryCondition + ' AND ErrorId <> ' + CAST(SUBSTRING(@ParserString,@PreviousPos + 1,(LEN(@ParserString)+1)- @PreviousPos) AS bigint)
SET @PreviousPos = @NextPos
BREAK
END
--Retrieving the individual substrings
If @FlgFirst=0
--Retreiving the first substring
BEGIN
SET @FlgFirst=1
PRINT SUBSTRING(@ParserString,1, @NextPos-1)
SET @QueryCondition= @QueryCondition + CAST(SUBSTRING(@ParserString,1, @NextPos-1) AS bigint)
SET @PreviousPos = @NextPos
END
ELSE
--Retreiving the internmediate substrings
BEGIN
PRINT SUBSTRING(@ParserString,@PreviousPos + 1,(@NextPos-1)-@PreviousPos)
SET @QueryCondition= @QueryCondition + ' AND ErrorId <> ' + CAST(SUBSTRING(@ParserString,@PreviousPos + 1,(@NextPos-1)-@PreviousPos) AS bigint)
SET @PreviousPos = @NextPos
END
END
print 'ErrorId <>' + @QueryCondition
UPDATE [ESMS2_DBMS].[dbo].[ErrorDetails]
SET ErrorRectifyDateTime=GETDATE()
WHERE (NozzleId = @NozzleId) AND (ErrorRectifyDateTime IS NULL) AND (ErrorId <> @QueryCondition)
END