I need a way to test if a convert function will work before I process it. if it fails, I want to intercept the error and return my own error to the front end
ex
if convert(int,@x) is true then do; else do
please email me if anyone has some advice
Mike
I have a cube that has a Dimension set up with several values some of which are bools. While Browsing in Excel or SSMS, two new values, when used as a filter shows (All) (Blank) and (True) for selections instead of (All) (True) and (False).Â
View 2 Replies View RelatedI have a stored procedure that has a boolean (bit) field passed to it (@emailcontract). If a user checks the check box on the webform I would like my where to return only the records where the email_contract column is true. If they don't check the check box I would like it to return records where email_contracts is true or false.
What would my where cluse look lile for this?
All
Can I ask what data type i use for a true false response (Boolean) in my table?
Thanks
Gibbo
Hi,
I need to check the existence of a row in a table.
So i am using an if condition
like
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
ALTER PROCEDURE [dbo].[CheckNOAStages]
@NOAID int,
@StageCode nchar(20)
AS
BEGIN
SET NOCOUNT ON;
Declare @Count int
Select @Count =Count(NOAId) from NOAStages where NOAID=@NOAID and StageCode=@StageCode
if (@Count>0)
Begin
return 1
end
else
begin
return 0
end
END
The stored proc is executing but on the Data Access Layer
I have this
Boolean exists = Convert.ToBoolean (Execute.ExecuteReader(spCollection, dbSQL));
Some how I am always getting false . How can I fix this?
Thanks
Hello
I am exporting an SQL Server table to a comma delimited text file. The values of Columns defined as Bit are exported as "True" or "False", but I would like that in the file appear 1 or 0 instead (with no surrounding double quotes). How can I acomplish that?
I tried using a Transformation and convert to single byte unsigned integer, but True values are exported as "255" and False values as "0". Why?
Thanks a lot.
Hi there, I've tried googling this (and looking at the many questions on the forum :) but I've not managed to find a decent tutorial / guide that describes a method for using checkboxs to insert true/false flags in a MS SQL db. The db field i'm setting has type set to "bit", is this correct? And secondly (its been a long day!) I just cant figure out the code to assign the bit 1 or 0 / true or false. This is what I've got so far but it's not working........Function InsertProduct(ByVal prod_code As String, ByVal prod_name As String, ByVal prod_desc As String, ByVal prod_size As String, ByVal prod_price As String, ByVal prod_category As String, ByVal aspnet As Boolean) As Integer Dim connectionString As String = "server='server'; user id='sa'; password='msde'; Database='dbLD'" Dim sqlConnection As System.Data.SqlClient.SqlConnection = New System.Data.SqlClient.SqlConnection(connectionString) Dim queryString As String = "INSERT INTO [tbl_LdAllProduct] ([prod_code], [prod_name], [prod_desc], [prod_size], [prod_price], [prod_category],[aspnet]) VALUES (@prod_code, @prod_name, @prod_desc, @prod_size, @prod_price, @prod_category, @aspnet)" Dim sqlCommand As System.Data.SqlClient.SqlCommand = New System.Data.SqlClient.SqlCommand(queryString, sqlConnection) sqlCommand.Parameters.Add("@prod_code", System.Data.SqlDbType.VarChar).Value = prod_code sqlCommand.Parameters.Add("@prod_name", System.Data.SqlDbType.VarChar).Value = prod_name sqlCommand.Parameters.Add("@prod_desc", System.Data.SqlDbType.VarChar).Value = prod_desc sqlCommand.Parameters.Add("@prod_size", System.Data.SqlDbType.VarChar).Value = prod_size sqlCommand.Parameters.Add("@prod_price", System.Data.SqlDbType.VarChar).Value = prod_price sqlCommand.Parameters.Add("@prod_category", System.Data.SqlDbType.VarChar).Value = prod_category If chkAspnet.Checked = True Then sqlCommand.Parameters.Add("@aspnet","1") Else sqlCommand.Parameters.Add("@aspnet","0") End If Dim rowsAffected As Integer = 0 sqlConnection.Open Try rowsAffected = sqlCommand.ExecuteNonQuery Finally sqlConnection.Close End Try Return rowsAffected End Function Sub SubmitBtn_Click(sender As Object, e As EventArgs) If Page.IsValid then InsertProduct(txtCode.Text, txtName.Text, txtDesc.Text, ddSize.SelectedItem.value, ddPrice.SelectedItem.value, ddCategory.SelectedItem.value, aspnet.value) Response.Redirect("ListAllProducts.aspx") End If End SubAny help would be appreciated or links to tutorials.ThanksBen
View 2 Replies View RelatedSir
I want to Return 1 and 0 after update , delete , Insert statement
IF Records Effected Return 1 else return 0
Pls help me out .........Sir
Yaman
I want to pass a single char to a query and use that to set two flags.
(
@ID int,
@AssessedID int,
@CompetencyID int,
@Status char,
@Creator int
)
AS
UPDATE P4_Assessment
SET P4_Cancelled_f = (@Status = ('C')),
P4_Competent_f = (@Status = ('P')),
P4_Date = getdate(),
P4_Creator = @Creator
WHERE P4_ID = @ID
I want to set the P4_Cancelled_f to true (if @Status = 'C') or false if it doesn't.
This sort of syntax is fine in C#, but fails in a query. I also tried using IN (@Status IN ('C'))
Is this sort of logic possible in TSQL or should I use two parameters and set them in my code as 1 or 0?
TIA
Actually title should be returns true when should false.
I want to check a table to see if a record already exists, if it doesn't then insert it, else do nothing:
IF NOT EXISTS
(SELECT 1 FROM Table1 WHERE col1 = 'Test')
BEGIN
INSERT INTO Table1 (col1) VALUES ('Test')
END
The value 'Test' is already in the database yet, the code is saying it's not and trying to insert it, resulting in duplicate key errors.
When I enter a a true or false value into a boolean field, what is the proper way to enter it? I'm using:
INSERT INTO tblTable (IsSomething)
VALUES (False)
I've heard that a better way is to use 1 and 0, or something.
Hi everyone.
I've got a Select query that pulls out some data from my database. Two of the columns are both booleans (bit's of size 1) so they come back as TRUE and FALSE - which I thought was fine.
However, the users are wanting to see YES and NO since they find TRUE and FALSE confusing (yes I know how silly that sounds).
Is there any way I can do this?
My query is like this:
SELECT [stuff], [things] FROM [table1], [table2] WHERE [table1].[condition] = [table2].[condition]
Thanks
Andrew
Hai,
I am trying to design a database for storing trule/false questions.
I have no need to store any thing like chapters are topic. I want to just design a bare minimum tables for just storing questions and its responses.
So does the following tables and fields suffice to achieve that?.
Questions_table(id(autogenerated),course_id,Questi on(text type))
id field uniquely identify each question, course_id(ex:CRS235) is used to identify which course a particular question belongs to, and Question filed store actual question.
Response_table(id,response)
Response field is of int type, 1 for correct answer, 0 for wrong answer. I am not storing options for each question because every question has same options that is True/False.
So, will this work. Please advice and I would appreciate if any one can point me to a good resource on the web.
Thanks,
Hi,
I'm using SSIS to update the destination table.
Both servers are running identical SQL Server 2005 versions.
I want to copy data and replacing existing data WITHOUT dropping the destination table first.
The SSIS package works when:
1) DropObjectsFirst = TRUE
2) CopyData = TRUE
3) ExistingData = REPLACE
The SSIS package does not work when:
1) DropObjectsFirst = FALSE
2) CopyData = TRUE
3) ExistingData = REPLACE
The error I get is the "destination table already exists". Of course, it already exists. I just want to copy/update the destination table. So, why does SSIS insist on dropping the table first?
It is my understanding that when having LinkedServers, the option "enable promotion of Distributed Transactions for RPC" should be set to TRUE, so we can rollback , if needed, remote transactions. At least, that's my understanding of that setting.
Having said that, the TRUE setting is affecting this particular TSQL code, inside an sproc, which I would prefer not to alter:
Insert into #TempTable
EXEC ServerB.MyDatabase.MyStoreProcedure
@param1= '',
@param2= ''
When set is set to TRUE (current setting) I get this error:
OLE DB provider "SQLNCLI11" for linked server "ServerB" returned message "The partner transaction manager has disabled its support for remote/network transactions.".
Msg 7391, Level 16, State 2, Line 28
The operation could not be performed because OLE DB provider "SQLNCLI11" for linked server "ServerB" was unable to begin a distributed transaction.
... when set to off, the error goes away.
I am using Naive Bayes, Decision Trees, and Neural Net (SSAS 2005) to predict which of two states each record belongs to.
How can I enforce a different penalty for a false positive versus a false negative ? (I am assuming that in some sense the mining algorithms can then minimize the total penalty).
I have a SQL database with an Access front end. In the database Ihave a read only and a read write role. When a read only user opensthe database I want all the fields on the form to be locked so thatthe user will not try to change data and get an error from the server.Right now I am doing that with a table. But it's a hassle to have tomaintain a table when if I could answer the question is the currentlylogged in user in the read write role?My server is running SQL Server 2000. So I was wondering if I couldwrite a function to do this? The function would take the role beingchecked as a text parameter and return true if the currently logged inuser is in that role or false if he/she isn't.
View 1 Replies View RelatedI have a SQL database that has a function that returns an id value from a table after you pass in a text variable. I would like to test this functionality in Query Analyzer but when I try to do it this way:
exec dbo.fnc_ORGUNIT_GetByName 'Dummy'
It just says 'Query executed successfully' without any resutls in the results pane. What am I doing wrong?
Thanks!
My aim is to write a function that returns a bit determining if a varchar(30) argument is "complex enough" to serve as a password. I am open to suggestions of better heuristics, but the rules I was initially planning to use were:
1) Greater than 8 characters long.
2) Contains at least one upper case, lower case, number, and punctuation (loosely defined as anything not in the first three categories).
I can loop over the characters in the PW and use CHARINDEX to test against these three subsets, but I thought there just might be a more clever way to accomplish this.
I also mention the "meta" problem since I am all ears if anyone has a better idea of how to measure complexity.
Cheers,
Lyman Hurd
PS Only a hash gets stored in the DB. This is to be used in the stored proecdure where people set their passwords. Thanks in advance for any thoughts.
I have this function in access I need to be able to use in ms sql. Having problems trying to get it to work. The function gets rid of the leading zeros if the field being past dosn't have any non number characters.For example:TrimZero("000000001023") > "1023"TrimZero("E1025") > "E1025"TrimZero("000000021021") > "21021"TrimZero("R5545") > "R5545"Here is the function that works in access:Public Function TrimZero(strField As Variant) As String Dim strReturn As String If IsNull(strField) = True Then strReturn = "" Else strReturn = strField Do While Left(strReturn, 1) = "0" strReturn = Mid(strReturn, 2) Loop End If TrimZero = strReturnEnd Function
View 3 Replies View RelatedI am trying to concatenate a string to a currency amount so that I can show both in a drop down list box. However when I create the SQL to do this using the Query tool I get a message to say that I must use the Convert funtion to convert the currency amount.Anyone know the SQL syntax for this function as I can seem to get it to work. ThanksPaul
View 3 Replies View RelatedI'll go right to the point:
I have a textbox in wich the user can seize a "money" value.
But when i'm doing the INSERT command, it fails and says I cannot implicitely convert nvarchar data (mytextbox.text) to money data.
I don't know how to handle this...
objCmd = New SqlCommand("INSERT INTO tbl_appel_service_pieces " & _
"(fld_nom_piece, fld_quantite, fld_prix, fld_description, fld_num_appel) " & _
"VALUES (@fld_nom_piece, @fld_quantite, @fld_prix, @fld_description, @fld_num_appel)", objConn)
objCmd.Parameters.Add("@fld_nom_piece", champsNomPiece.Text)
objCmd.Parameters.Add("@fld_quantite", champsQuantite.Text)
objCmd.Parameters.Add("@fld_prix", champsPrix.Text)
objCmd.Parameters.Add("@fld_description", champsDescription.Text)
objCmd.Parameters.Add("@fld_num_appel", champsNumAppel.Text)
objConn.Open()
objCmd.ExecuteNonQuery()
objConn.Close()
afficherPieces()
How and where to do the conversion ? On the VALUES variable ?
Hi, I have found some ways on using Convert() function for SQL server. But i do not understand some of them.
Example:
CONVERT(VARCHAR(10),column name,108)
What is the 108 for? I see some with 120 and 101. I tried all but only 108 is working for me the way I want..but i do not understand what is that for actually. Can anyone explain it to me? Thank you.
Hi,
Here is the statement I'm trying to run....
declare @A varchar(5)
select @A='123'
select @A=(select convert(money,@A))
select @A
This is the error message I get:
Implicit conversion from data type money to varchar is not allowed. Use the CONVERT function to run this query.
Does anyone know what should I change in the query to make it work?
Thank you very much,
Anastasia.
Hi all,
I have found an interesting problem with Convert function. When you Use this function to Convert Datetime to varchar and if the date happens to be 01/01/2000 or higher it return nothing. here is an Example.
CREATE TABLE ABC (Id int, Datein Datetime NOT NULL Default getdate())
GO
INSERT INTO ABC VALUES(1,'12/31/1999')
INSERT INTO ABC VALUES(2,'12/31/1999')
INSERT INTO ABC VALUES(3,'12/31/1999')
GO
SELECT *
FROM ABC
WHERE CONVERT(varchar,DateIn,101) < '01/01/2000'
-- This query returns Nothing
SELECT *
FROM ABC
WHERE DateIn < '01/01/2000 00:00:00'
AND DateIn > '12/30/1999 23:59:59'
-- this Query return the 3 row that it is suppose to.
Is there a Fix for this problem that any one know of.
Please, Help .. Or else I will have to do this solution for every time I am useing Convert function.
Thank You very much for your time.
Aziz Vahora
Hi all,
I have found an interesting problem with Convert function. When you Use this function to Convert Datetime to varchar and if the date happens to be 01/01/2000 or higher it return nothing. here is an Example.
CREATE TABLE ABC (Id int NOT NULL, Datein Datetime NOT NULL)
GO
INSERT INTO ABC VALUES(3,'12/31/1999 01:00:00')
INSERT INTO ABC VALUES(4,'12/31/1999 02:00:00')
INSERT INTO ABC VALUES(5,'12/31/1999 03:00:00')
INSERT INTO ABC VALUES(6,'01/01/2000 03:00:00')
GO
SELECT *
FROM ABC
WHERE CONVERT(varchar,DateIn,101) < '01/01/2000'
GO
-- This query returns Nothing
SELECT *
FROM ABC
WHERE DateIn < '01/01/2000 00:00:00'
AND DateIn > '12/30/1999 23:59:59'
-- this Query return the 3 row that it is suppose to.
Is there a Fix for this problem that any one know of.
Please, Help .. Or else I will have to do this solution for every time I am using Convert function.
Thank You very much for your time.
Aziz Vahora
Hi, I succesfully transfered my Access database to MS SQL and tried to run my Visual Basic application I got the following error message:
"...Disallowed implicit conversion from data type varchar to data type money, table SALES, column AMOUNT ...Use the CONVERT function to run this query."
Used code looks like:
sSQL = "INSERT INTO sales ([TRIP], [CC], [TRANS], [DATE], [AMOUNT], [GST], [PST], [TOTAL], [NOTE], [CATEG]) VALUES ('" & txtTrip.Text & "', '" & cmbCC.Text & "', '" & txtTrans.Text & "', '" & txtDate.Text & "', '" & txtSubtotal.Text & "', '" & txtGST.Text & "', '" & txtPST.Text & "', '" & txtTotal.Text & "', '" & txtNote.Text & "', '" & cmbCat.Text & "'); "
Set rst = New ADODB.Recordset
rst.Open sSQL, objAccessConnection, adOpenKeyset, adLockOptimistic
Can you please help me fix this error, I am new to MS SQL...
Thanks.
I get this error when I run this query, How do I use the CONVERT function in this scenario
insert into Inventory (Item_ID,
Inventory_ID,Perishable_indicator, Perishable_Date,
Department_ID, Category, Description, Unit_of_Measure,
Quantity_on_Hand, Unit_Cost, Master_Pack_Quantity,
Supplier_ID)
values
('23456','8263458', 'Yes', 'Feb 25 2005
12:00AM','695', 'Produce', 'Apple', 'per lb', '6000',
'0.99', '10000', '893')
Server: Msg 260, Level 16, State 1, Line 1
Disallowed implicit conversion from data type varchar
to data type money, table 'Final
Project.dbo.Inventory', column 'Unit_Cost'. Use the
CONVERT function to run this query.
I write using the SQL ODBC driver from software into a SLQ table called UPSSHIPMENT the format is as followed:
JobNumbervarchar50
Weightreal4
FreightCostvarchar8
TrackingNumbervarchar50
Shipmethodvarchar50
VOIDIDvarchar3
I then have a trigger set to update the PACKAGE table as followed
CREATE TRIGGER [UPS] ON dbo.UPSSHIPMENT
FOR INSERT
AS
BEGIN
UPDATE PACKAGE
SET WEIGHT = inserted.WEIGHT,
FREIGHTCOST = inserted.FREIGHTCOST,
TRACKINGNUMBER = inserted.TRACKINGNUMBER,
COMMENTS = inserted.SHIPMETHOD
FROM PACKAGE
INNER JOIN inserted on PACKAGE.JOBNUMBER = inserted.JOBNUMBER
WHERE inserted.VOIDID = 'N'
UPDATE PACKAGE
SET WEIGHT = '',
FREIGHTCOST = '',
TRACKINGNUMBER = '',
COMMENTS = 'UPS VOID'
FROM PACKAGE
INNER JOIN inserted on PACKAGE.JOBNUMBER = inserted.JOBNUMBER
WHERE inserted.VOIDID = 'Y'
END
The format of the PACKAGE table is as followed
Jobnumbervarchar50
FreightCostmoney8
TrackingNumbervarchar50
Commentsvarchar2000
Weightreal4
When the trigger goes off I am getting the following error
---------------------------
Microsoft SQL-DMO (ODBC SQLState: 42000)
---------------------------
Error 260: Disallowed implicit conversion from data type varchar to data type money, table 'TESTing.dbo.Package', column 'FreightCost'. Use the CONVERT function to run this query.
---------------------------
OK
---------------------------
How do you use the convert function to change the data before the update?Thank You!
Hi
I'm an Access/VB coder by experience and trying to move apps to SQL Server 2000.
I have got to grips with the basics of DTS and tables and views and Stored Procedures (to an extent) but now need to upgrade an app that uses a vb function to produce a phased value for a set of budgets.
The VB function looks like this...
---------------------------------
Function calcPercentOfBudget(datFromDate As Date, datToDate As Date, iMonth As Integer, cBudget As Currency) As Currency
Dim iDuration As Integer
iDuration = DateDiff("d", datFromDate, datToDate) + 1
' if either date not in current year then 0 (both dates should be in same year)
If Year(datFromDate) <> Year(Now) Or Year(datToDate) <> Year(Now) Then
calcPercentOfBudget = 0
End If
Dim sRatio As Single, idaysInMonth As Integer, idaysInYear As Integer
' if passed month outside of period then 0
If Not (iMonth >= Month(datFromDate) And iMonth <= Month(datToDate)) Then
calcPercentOfBudget = 0
Exit Function
End If
idaysInMonth = daysInMonth(iMonth, Year(Now))
'if from date and to date in same month then 100% of budget
If Month(datFromDate) = Month(datToDate) Then
calcPercentOfBudget = cBudget
Exit Function
End If
' if passed month in From month then ratio of passed month (caters for 1st day of month - 100%)
If Month(datFromDate) = iMonth Then
'calcPercentOfBudget = (idaysInMonth + 1 - Day(datFromDate)) / idaysInYear * cBudget
calcPercentOfBudget = (idaysInMonth + 1 - Day(datFromDate)) / iDuration * cBudget
Exit Function
End If
'if passed month in To month then ratio of passed month (caters for last day of month - 100%)
If Month(datToDate) = iMonth Then
'calcPercentOfBudget = Day(datToDate) / idaysInYear * cBudget
calcPercentOfBudget = Day(datToDate) / iDuration * cBudget
Exit Function
End If
' if passed month within period then 100%
If iMonth > Month(datFromDate) And iMonth < Month(datToDate) Then
'calcPercentOfBudget = idaysInMonth / idaysInYear * cBudget
calcPercentOfBudget = idaysInMonth / iDuration * cBudget
Exit Function
End If
End Function
---------------------------------
Function daysInMonth(iMonth As Integer, iYear As Integer) As Integer
Dim datTemp As Date
datTemp = CDate("1/" & CStr(iMonth) & "/" & CStr(iYear))
datTemp = DateAdd("m", 1, datTemp)
datTemp = DateAdd("d", -1, datTemp)
daysInMonth = Day(datTemp)
End Function
---------------------------------
I have a UDF function LastDayInMonth to replace the daysInMonth vb function, and that works fine.
However I'm starting to get frustrated in trying to convert the main VB funciton to a UDF function. This is what I have got to, and as you'll see its totally wrong!...
CREATE FUNCTION [dbo].[calcPercentOfBudget] (@datFrom as datetime, @datTo as datetime , @iMonth as int, @cBudget as money, @GetDate as datetime)
RETURNS money AS
BEGIN
declare @iDuration int
declare @returnvalue money
declare @sRatio decimal
declare @iDaysInMonth int
set @iDuration = datediff(d, @datFrom, @datTo)
set @iDaysinMonth = dbo.LastDayInMonth('1/' + cast(@iMonth as varchar) + '/' + cast(year(@getdate) as varchar))
case
when year(@datFrom) <> year(@getdate) or year(@datTo) <> year(@getdate)
set @returnvalue = 0
when not(@iMonth >= Month(@datFrom) and @iMonth <= month(@datTo)
set @returnvalue 0
when month(@datFrom) = month(@datTo)
@set @returnvalue = @cBudget
when month(@datFrom) = @iMonth
set @returnvalue = (@iDaysInMonth + 1 - day(@datFrom)) / @iDuration * @cBudget
when month(@datTo) = @iMonth
set @returnvalue = Day(datToDate) / iDuration * cBudget
when @iMonth > month(@datFrom) and @iMonth < month(@datTo)
set @returnvalue = @iDaysInMonth / @iDuration * @cBudget
end
return @returnvalue
END
This is my first post to this forum - so any constructive criticism will be welcomed.
Basically, where am I going wrong? - have I got the wrong end of the stick? Have I got the wrong stick? Have I got a stick of dynamite?!
All help greatfully received,
Paul:eek:
Can u help me transform this code into sql function?
/* Append modulus 11 check digit to supplied string of digits. */
function GenMOD11( $base_val )
{
$result = "";
$weight = array( 2, 3, 4, 5, 6, 7,
2, 3, 4, 5, 6, 7,
2, 3, 4, 5, 6, 7,
2, 3, 4, 5, 6, 7 );
/* For convenience, reverse the string and work left to right. */
$reversed_base_val = strrev( $base_val );
for ( $i = 0, $sum = 0; $i < strlen( $reversed_base_val ); $i++ )
{
/* Calculate product and accumulate. */
$sum += substr( $reversed_base_val, $i, 1 ) * $weight[ $i ];
}
/* Determine check digit, and concatenate to base value. */
$remainder = $sum % 11;
switch ( $remainder )
{
case 0:
$result = $base_val . 0;
break;
case 1:
$result = "n/a";
break;
default:
$check_digit = 11 - $remainder;
$result = $base_val . $check_digit;
break;
}
chirag writes "
i want to convert datatype numeric to varchar
my query is
====================================================
SELECT ParameterName+' ('+instancename+')' as Item,
CurrentUse as LastPoll,
Threshold +
case Comparer when 'Greater Than' then '( > )'when 'Less Than' then '( < )'end ,
CASE Status WHEN 'Running' THEN 'N' WHEN 'Stopped' THEN 'Y' END as Breach
FROM PARAMETER
======================================================
please solve this query as soon as possible
at
chiragvm@gmail.com
"
I’m trying to cast and integer type to a varchar and then concatenatetwo varchar and ’01’ and convert it into datetime with the followingcode:CONVERT(datetime, { fn CONCAT({ fn CONCAT(CAST(PeriodYear AS varchar),CAST(PeriodMonth AS varchar)) }, ’01’) }, 112) AS dateIt gives an error saying syntax error converting datetime fromcharacter string.Any help appreciated!!thx..--Posted using the http://www.dbforumz.com interface, at author's requestArticles individually checked for conformance to usenet standardsTopic URL: http://www.dbforumz.com/General-Dis...pict261333.htmlVisit Topic URL to contact author (reg. req'd). Report abuse: http://www.dbforumz.com/eform.php?p=900084
View 4 Replies View Related