I try to insert VB.NET boolean true values into a SQL2000 table thru a stored procedure. While varchar fields of the table are correctly filled, I'm surprised to find the bit fields all stay 0 after every single insert. This makes me wonder if VB.NET boolean variables can be passed to sp bit parameters and inserted into a SQL2000 table. Or did I miss something? Please advise. Thanks.
View 1 RepliesHelloI use vs2008 and i try to make a simple windows program with add records to a database. The problem is the database don't store the values that i add. When i run my program (in design mode) everythynf looks fine, but if i close vs2008 and restart it again the database is empty.
Why?
Thank you
I've got some records like this:
ID_________Jan Feb...........................Dec
0000030257 0 0 0 0 0 0 1 1 1 1 1 0
where each month field has a 0 or 1, depending on if the person was enrolled that month.
I'm being asked to generate a table like this:
ID_________ Start_Date End_Date
0000030257 July 1, 2014 Nov 30, 2014
Is there some slam dunk way to do this without a bunch of If/Then statements?
The editor compressed all my space fields, so the column headers are off in some places.
the subject pretty much says it all, I want to be able to do the following in in VB.net code):
{[If [table with this name] already exists [in this sql database] then [ don't create another one] else [create it and populate it with these values]}
How would I do this?
Hi,I have boolean values in a table for ex. Federation. And I want toselect followingSelect 'Insert into' + member + 'test' as test1from federationThen I get error messageServer: Msg 403, Level 16, State 1, Line 1Invalid operator for data type. Operator equals add, type equals bit.Someone can help me out of it.Thanks an advance- Loi -
View 3 Replies View RelatedI have a field 'IsActive' which is bit type either 1 or 0. And in my package, the conditional split must be set something like
IsActive==0
And throws exception that condition evaluated as NULL where Boolean was expected. How i can make this work?
Hi all
I have a few questions about writing User Defined Functions in VB.Net for Sql Server 2005.
1.- How can I return a Boolean value from my functions? I want that the next sql stament works:
SELECT * FROM Table_1 WHERE dbo.EMPTY(Column1)
2.- Its possible to use UDF without the €œdbo.€? String? Such as:
SELECT * FROM Table_1 WHERE EMPTY(Column1)
Thanks for all
Alejandro Font
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 was wondering what should be used for a yes/no/ entry identifier . Should it be a simple int , tinyint , or uniqueidentifier?
Thank you
Hi,
I have an ASP.NET form that stores it's data in MSDE but I just added a multi-select ListBox to the form and I'm having a hard time coming up with a way of writing that data to the database. Should I write the values into a column on the same table where I store the rest of the data from the form (values separated by a comma) or shouild I create another table (one to many) and store the data there. I like the second option, but I'm not sure how to loop through each value and write it to the database table.
I grab the values for the selection as follow:
foreach (ListItem lstItem in lbAttendees.Items)
{
if (lstItem.Selected == true)
{
grpList.Add(lstItem.Value.ToString());
}
}
but I'm not sure on what to do next and could use some help.
Thanks
Germano
Hey all:
Right now I have a cursor that makes me want to puke. This is the last cursor in my current project and I want to replace it with a much faster set based operation.
Here is the problem. I have a table with say 1-3 million records. There are fields that get loaded in with date information. These fields are varchar because the date information could very well be mangled data that needs to be reviewed by a user. What I need is to go through these varchar fields and flag the values that cannot convert to smalldatetime.
I have another table that houses the primary key and the field of the record that cannot convert.
Essentially, I have a series of filters that run and flag using set based stored procedures. If there is a record that gets through that contains a value that cannot be converted, I have a cursor that steps through the data and attempts to convert the value. If it is able to be converted, then it continues on until it finds the value that is holding up the conversion.
I guess if I can run a query that will return all records that can convert for the field (or can't convert) I'd be all set. Any help here is appreciated.
--Thanks--
Hi,
using custom tasks. My properties got saved properly the first time the task is added to the workspace. After that, changing values in the CustomUI updates the properties, but the package shows, that It doesn't need to get saved. So my properties don't get changed in the package XML. Moving my custom task on the workspace after changing properties, let the package go into "save me" state and my propertiy values get saved to XML.
So now, Im searching for a method or property I have to call or set to show the package it needs to get saved after closing my CustomUI.
Any hint?
Thanks
PS: When I change the property value in the task properties grid (lower right). The package gets notified about the need to get saved.
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
I have an application that reads a monitoring devices that produces 200 digital outputs every second and I would like to store them in a table. This table would get quite big fairly quickly as ultimately I would like to monitor over a hundred of these devices.
I would like to construct queries against each of the individual digital channels or combinations of them.
M first thought is to set up a table with 200 separate columns (plus others for date stamp, device ID etc) however, I am concerned that a table with 200 boolean (1-bit) fields would be an enormous waste of space if each field takes maybe one to four bytes on the hard disk to store a single bit. However, this would have the advantage of make the SQL queries more natural.
The other alternative is to create a single 200 bit field and use lots of ANDing and ORing to isolate bits to do my queries. This would make my SQL code less readable and may also cause nore hassle in the future if the inputs changed, but it would make the file size smaller.
In essence I am asking (hoping) the following : If I create a table with 200 boolean fields, does SQL server express automatically optimise the storage to make it more compact? This means that the server can mess around at the bit level and leave my higher level SQL code looking cleaner and more logical.
Ok so i've got a database containing a table called Quote.
I need one of the field's datatype to be Boolean?
which option do i choose?
and also is there a way to make the Key Field auto increment?
And is the datatype: ntext, the correct option for a text only field?
thanks
I have 2 identical tables one contains current settings, the other contains all historical settings.I could create a union view to display the current values from table A and all historical values from table B, butthat would also require a Variable to hold the tblid for both select statements.
Q. Can this be done with one joined or conditional select statement?
DECLARE @tblid int = 501
SELECT 1,2,3,4,'CurrentSetting'
FROM TableA ta
WHERE tblid = @tblid
UNION
SELECT 1,2,3,4,'PreviosSetting'
FROM Tableb tb
WHERE tblid = @tblid
This program gets the values of A and B passed in. They are for table columns DXID and CODE. The textbox GET1 is initialized to B when the page is loaded. When I type another value in GET1 and try to save it, the original initialized value gets saved and not the new value I just typed in. A literal value, like "222" saves but the new GET1.TEXT doesn't.
View 1 Replies View RelatedI am relatively new to SQL and as a project I have been asked to create the SQL for a simple database to record train details. I want to implement a check constraint which will prevent data from being inserted into a table if the weight of the train is more than the maximum towing weight of the locomotive. FOr instance, I need to add the unladen weight and maximum capacity of each wagon (located in the wagon type table) and compare it against the locomotive maximum pulling weight (the locomotive class table). I have the following SQL but it will not work:
check((select SUM(fwt.unladen_weight+fwt.maximum_payload) from
hauls as h,freight_wagon as fw,freight_wagon_type as fwt,train as t where
h.freight_wagon_serial_number = fw.freight_wagon_serial_number and
fw.freight_wagon_type = fwt.freight_wagon_type and
h.train_number = t.train_number) <
(select lc.maximum_towing_weight from locomotive_class as lc,locomotive as l,train as t where
lc.locomotive_class = l.locomotive_class and l.locomotive_serial_number = t.locomotive_serial_number))
The hauls table is where the constraint has been placed and is the intermediary table between train and freight wagon.
I may not have explained this very well; but in short, i need to compare the sum of two values in one table against a values located in another table...At present I keep getting a message telling me the sub query cannot return more than one row.
I have two tables  A(uname,address,full_name) and B(uname,full_name). I want to update table A for all matching case of uname in table B.Â
View 5 Replies View RelatedHello:I didn't find any documentation that notes save point names are casesensitive, but I guess they are...Stored Proc to reproduce:/* START CODE SNIPPET */If Exists (Select * From sysobjects Where Type = 'P' and Name ='TestSaveTran')Drop Procedure dbo.TestSaveTranGoCreate Procedure dbo.TestSaveTranAsBeginDeclare@tranCount int--Transaction HandlingSelect @tranCount = @@TRANCOUNTIf (@tranCount=0)Begin Tran localtranElseSave Tran localtranBegin Try--Simulate Error While ProcessingRAISERROR('Something bad happened', 16, 1)/*If this proc started transaction then commit it,otherwise return and let caller handle transaction*/IF (@tranCount=0)Commit Tran localtranEnd TryBegin Catch--Rollback to save pointRollback Tran LOCALTRAN --<< NOTE case change--Log Error--Reraise ErrorEnd CatchEndGo--Execute Stored ProcExec dbo.TestSaveTran/*Should receive the following message:Cannot roll back LOCALTRAN. No transaction or savepoint of that namewas found.*//* END CODE SNIPPET */What is really strange, if there is a transaction open, then no erroris thrown. So if you execute as so:/* START CODE SNIPPET */Begin Tran--Execute Stored ProcExec dbo.TestSaveTran/* END CODE SNIPPET */There is no "Cannot roll back LOCALTRAN...." message.Questions:1-)Can someone confirm save point names are case sensitve and this isnot happening because of a server setting?2-)Is this a logic error that I am not seeing in the example codeabove?We have changed our code to store the save point name in a variable,which will hopefully mitigate this "problem".Thx.
View 4 Replies View Relatedtable2 is intially populated (basically this will serve as historical table for view); temptable and table2 will are similar except that table2 has two extra columns which are insertdt and updatedt
process:
1. get data from an existing view and insert in temptable
2. truncate/delete contents of table1
3. insert data in table1 by comparing temptable vs table2 (values that exists in temptable but not in table2 will be inserted)
4. insert data in table2 which are not yet present (comparing ID in t2 and temptable)
5. UPDATE table2 whose field/column VALUE is not equal with temptable. (meaning UNMATCHED VALUE)
* for #5 if a value from table2 (historical table) has changed compared to temptable (new result of view) this must be updated as well as the updateddt field value.
Hi,
i have a table called fundperformance and i opened that table in design view and deleted a column and added another column to that table and when i try to save the changes i made to it i get the below error.
'Fund' table saved successfully'FundPerformance' table- Unable to create index 'PK_FundPerformance'. The CREATE UNIQUE INDEX statement terminated because a duplicate key was found for the object name 'dbo.FundPerformance' and the index name 'PK_FundPerformance'. The duplicate key value is (1).Could not create constraint. See previous errors.The statement has been terminated.
what should i do to fix it.
Thanks
Karen
I have a asp.net application where i can choose to save any file(could be jpg or dat or txt or xml etc etc) into a table.So far,i've manage to save it into an SQL server table in a column(set to Image datatype).But when i get the data from the table,put it into a byte array and recreate it.I notice that the files can be recreated correctly if they are of type .txt files.But jpg files can't be recreated and trying to open the jpg files gives an error.I don't think it is the file saving that is the problem since any files can be save.Rather it is the file recreating that is the problem.Can anyone help?
View 3 Replies View RelatedI'm rusty on the tsql side. How do you save to a table from a sql statement. I'm testing for sp's.
View 14 Replies View Related1-I want to save records from a table located in my database1 on Server1 to a non existing table in another database2 in another server2. (The destination table doesn t exist, I want it to be created with T-SQL). How can I do that using T-SQL
2- Also, is there an other better practice to back up my table before I do some dammage inintentially and destroy the table or the records forever or does SQL server do that automatically for us.
Thanks a lot for your advice.
Scenario: I have created a dataset from an excel file to display it on a screen. Now I would like to save this same dataset in a SQL Server table.
Any ideas would be greatly appreciated.
Thanks
I have found the node for Triggers in SQL Server 2005's Management Studio and tried to create a 'New Trigger...' but when I save, it saves it to an .sql file but do not attach it to the table.What am I doing wrong here? I can't seem to attach it. And where is this Assisted Editor ...i can't seem to find it anywhere
View 2 Replies View RelatedHi all
I would like to know if its possible to "Save" records when they get deleted.
For example: I have a table, tblUsers, with coulmns, UserID, Name, Surname, etc...
In VWD I've created a GridView which shows everything on a webpage. I've also added a confirm return('Are you sure you want to delete the user?') option in OnClientClick field. What i want to achieve is, have some sort of log file, or log table if you want to call it that, of which users has been deleted by the end user. So, in later stages, i can see who deleted who, when, where, etc... - by building a report or view.
All this should go to a seperate database or seperate table, it doesnt really matter.
My delete query:DELETE FROM [tblUsers] WHERE [UserID] = @UserID
how can i save pictures in sql server and retreive them
View 1 Replies View RelatedHow do I save my query results into new table.... The ORIGINAL COLUMN Of course before parsing--- But the only data I want is in the three no name columns---(NO Column Name),(NO Column Name),(NO Column Name)I don’t want the original column saved back but I think it existing in the final query is blocking my Insert Into---
View 2 Replies View RelatedTo any and all;I have a very large table (16Mil+ records), for which I want to deleteabout 8 Million records. There are a few indexes on this table.Is there a way that I can run either a query or a series of queriesthat will run against each record and delete based on criteria (date)?If I do a single DELETE query, it will take forever, lock the table,and my app that runs against it will stop INSERTING, which is bad.If I do a cursor, I think it locks the table also, so that won't do,right?Any help would be appreciated.Glenn DekhayserContentcatcher.com
View 6 Replies View Relatedhi all,I checked out in google groups but was not able to findthe answer...is it possible to limit size table in sql server?how can I do it?TIA
View 2 Replies View RelatedI am trying to create a runtime temporary table at SQL2000 server in my VB 6.0 program. However, I am keeping getting "object not found" error from my program when I trying to issue a SELECT or INSERT statement in my program. Can anyone shows me how to do it properly.
I thought I can create a temporary table with a # prefix as a table name. Am I correct?
Help is very much appreciated.
John Tsoi