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 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 RelatedI 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.
Sir
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
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
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.
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?
Hi,
I've got a table containing calculated values, so i created a field named "archived" (bit datatype) on this table, to prevent the values to be updated if this field is set to true.
Is it possible to created a constraint, to prevent the row to be updated if ARCHIVED=true ? How can i do it ?
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).
Greetings,
Being fairly new to SQL Server, I created a new field with the data type 'bit' so I can store the equivalent of a yes/no response (0 or 1).
When I try to enter either a 0 or 1 into the field via MS SQL Server Management Studio Express table view, I get
"String not recognized as valid Boolean"
I'd appreciate if someone would let me know how I 'noobed' this up.
Thanks very much.
I have a boolean field.
I want the users to be able to select
show all marked YES
show all marked NO
show all.
How can this be accomplished?
Thanks
I have a long query that returns a table like:
ID Name bool
1 Joe 0
2 James 0
3 John 1
When the boolean field is equal to 1 for any of the records I would like to return an empty record set. What is the best way to do this?
I know I could use IF EXISTS but my query is so long that I would prefer not to repeat the query multiple times. Is there an alternative?
Hello,
Can anyone say me how i can make a Report with Parameter Boolean field,and as Default Value true and False. ( Both ).
With Multivalue and in the Query = Field in (@BoolPara) have i a Error in the Query.
Thanks
I wanted to test how SQL server 2005 works with Boolean values.
so I created this table
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[BoolTest](
[ID] [int] IDENTITY(1,1) NOT NULL,
[Operand1] [bit] NULL,
[Operand2] [bit] NULL,
CONSTRAINT [PK_BoolTest] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
I populated it like so
insert dbo.booltest (operand1, operand2) values (1,1)
insert dbo.booltest values (1,0)
insert dbo.booltest values (1,null)
insert dbo.booltest values (0,0)
insert dbo.booltest values (0,null)
Then I tried this
select case when operand1 OR operand2 then 'True' else 'False' End
from dbo.booltest where id=1
thinking that since row 1 has 1 and 1 I'll get 'True' as a string showing in the output window
but instead I got this error
Msg 4145, Level 15, State 1, Line 1
An expression of non-boolean type specified in a context where a condition is expected, near 'OR'.
question:
how do you work with bit value table Fields as booleans in a boolean test
many thanks in advance for your time/help
CarlitoA
Hello,
I am facing a problem in a SELECT clause which i cannot solve.
In my SQL table ("myTable") i have a few columns ("Column1", "Column2", "TypeColumn"). When I select different columns of the table, instead of getting the value of TypeColumn, i would like to get a boolean indicating whether its value is a certain string or not.
For example, the TypeColumn accepts only a number of selected strings: "AAA", "BBB", "CCC".
when i do a select query on the table, instead of asking for TypeColumn i would like to ask a boolean value of 1 if TypeColumn is "AAA" and 0 if TypeColumn is "BBB" or "CCC". Also, i would like to make this query while I am also fetching the other columns. And i would like to use one query to get all that. I thought something like thsi would work:
SELECT Column1 AS Col1, Column2 AS Col2, IF(TypeColumn = "AAA", 1, 0) AS Col3
FROM myTable
but this doesn't work in SQL 2005!
Is it possible to do something similar in SQL 2005 using one query only? i am trying to avoid multiple queries for this.
thanks a lot for your help!
I have a gridview connected to a sqldatasource, and it works pretty good. It gives me the subsets of the information that I need. But, I really want to let them choose all the companies and/or any status. What's the best way to get all the values in the gridview...besides removing the filters :)
I thought the company would be easy, I'd just set the selected value to blank "", and then it'd get them all....but that's not working. And, for the boolean, I have no idea to get the value without having a separate query.
(tabs_done=@tabsdone) and (company like '%' + @company + '%')1 <asp:DropDownList ID="drpdwnProcessingStatus" runat="server">
2 <asp:ListItem Value="0">Open</asp:ListItem>
3 <asp:ListItem Value="1">Completed</asp:ListItem>
4 </asp:DropDownList>
5
6
7 <asp:DropDownList ID="drpdwnCompany" runat="server">
8 <asp:ListItem Value="">All</asp:ListItem>
9 <asp:ListItem Value="cur">Cur District</asp:ListItem>
10 <asp:ListItem Value="jho">Jho District</asp:ListItem>
11 <asp:ListItem Value="sea">Sea District</asp:ListItem>
12 <asp:ListItem Value="san">Net District</asp:ListItem>
13 <asp:ListItem Value="sr">Research District</asp:ListItem>
14 </asp:DropDownList>
15
16
17 <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:HRFormsConnectionString %>"
18 SelectCommand="SELECT DISTINCT [id], [lastname], [company] FROM [hr_term] hr where (tabs_done=@tabsdone) and (company like '%' + @company + '%')">
19 <SelectParameters>
20 <asp:ControlParameter ControlID="drpdwnProcessingStatus" DefaultValue="0" Name="tabsdone" PropertyName="SelectedValue" />
21 <asp:ControlParameter ControlID="drpdwnCompany" DefaultValue="" Name="company" PropertyName="SelectedValue" />
22 </SelectParameters>
23 </asp:SqlDataSource>
24
I have a date held in a varchar field in a temporary sql table and I want to convert it into a sql date and it doesn't work. I can replicate this as below -
So I run
select
cast ('14/02/2014' as date)
and I get a conversion failed error.
I have a web form with a text field that needs to take in as much as the user decides to type and insert it into an nvarchar(max) field in the database behind. I've tried using the new .write() method in my update statement, but it cuts off the text after a while. Is there a way to insert/update in SQL 2005 this without resorting to Bulk Insert? It bloats the transaction log and turning the logging off requires a call to sp_dboptions (or a straight-up ALTER DATABASE), which I'd like to avoid if I can.
View 6 Replies View RelatedI have a table by the name of Info, in this table I have columns with the following names FirstName, LastName, PhoneNumber, CustomerID. In the columns I have the following data
FirstName = Beth
LastName = Riddle
PhoneNumber = 864-555-1212
CustomerID = 4
The problem is with the following statement in the .vb file. The statement keeps produceing a false value, when the desired value is Beth.
TextBox1.Text = SqlDataSource1.SelectCommand = "SELECT 'FirstName' From 'Info' Where(PhoneNumber = '864-555-1212')"
Please help
Thanks for the information
when ever i drop a ole db source or destination control on data flow, upon clicking to edit it complains about -- defaultcodepage is set to false. When i check in the properties and set it to true; i get no error. What is it all about?
kushpaw
Hi,Problem:I need to get the value of auto-incremented field from just insertedrecordIn Oracle this is INSERT .. RETURNING command.In SQL Server there are @@IDENTITY, IDENT_CURRENT, SCOPE_IDENTITY- @@IDENTITY returns the value from the very LAST insert on any tableinvolving in the insert process ( including triggers ) ,so this value may have nothing to do with my table- IDENT_CURRENT returns the last identity value generated for aspecific table in any session and any scope,so this value may come not from my session- SCOPE_IDENTITY returns the last identity value generated for anytable in the current session and the current scope , but from the veryLAST insert command ( that may be some INSERT in the audit tables)so it may have nothing to do with my tableQuestion :- Is there any trusted way I can get the value of auto-incrementedfieldin my table and in the scope of my session?Thanks, Eugene
View 2 Replies View RelatedHi,
Can someone tell me if it is possible to do an SQL insert with a select (to copy specific records) query and specify the value for a specific field to insert in the new records instead of using the value in the field in the select statement.
If so can you provide me with a simple example.
Cheers
Mark :)
What I want to do is: insert into newtable (field1, field2, sequence) select
(fielda, fieldb, #) from oldtable and have the sequence field "re-seed" to 1 every time the value in fielda/field1 changes. This added requirement makes this not an identity field problem, if I understand identity fields. This is a data conversion problem, so I'm converting 1000's of rows from oldtable to newtable and want the sequence to re-seed at every change in value of the fielda/field1 data; in fact fielda/field1 is a simplification as there could be multiple controlling fields forcing a re-seed.
TIA...Al
Hellow, Everyone,
I am having a problem in Inserting a text field into another Text field on another table. each record in the field might be more than 255 charactors.
Any help would be appreciated.
Thanks,
I want to learn if its possible to get ID in trigger and set it to another field.
I can do it by using scope_identity and using update command but I dont want to run another update command.
This is a very heavy loaded system that runs maybe 10-30 transactions on every second and I cannot afford that. I come up with a solution by using sequences but if I could use ID would be better.