How To Add Columns And Use Them At Run Time Using VB 2005
Sep 11, 2007
In order to allow my customers to use their existing data and install updated application, I want to be able to add columns to existing CE tables at startup time, and allow the user to enter data into the new column(s) immediately. I used the ALTER TABLE command as shown in the example below:
cmd.CommandText = "ALTER TABLE tblStores ADD dtDemoDate datetime, dtDemoMode int"
cmdexe = cmd.ExecuteNonQuery()
To add the column, But this does not set the columns in the attached dataset. The program sees the columns and data is able to be added, but is not then in the underlying table? What else do I need to do?
Thank you - b2bMan
View 1 Replies
ADVERTISEMENT
Aug 27, 2007
how can i retrieve two columns from sqltable with - seperating the results and a common column name
someth like this
select id ,name from user
id name
1 a
2 b
3 c
i need the result set to be
my_reports
1-a
2-b
3-c
is that possible, if so, could anyone tell me how to do?
View 3 Replies
View Related
Jun 18, 2002
Good morning,
I want to update two columns in the same time by making a select from another table. Some think like the following;
Update TBL1 o set (o.COL1,o.COL2) =
(select f.COL1,f.COL2 from TBL2 f where f.PKY = o.PKY)
where o.COL3 = 100;
Thinks to helping me .
View 1 Replies
View Related
Jul 3, 2007
Hi please help me out !!
I have to update the incidents table.
The initiator and initdept are two corresponding columns in incidents table whoes value I have to get from another table Employee where (initiator means Empname and initdept means title)
if i just do
select Empname,title from employee
it gives me correct output while
select initiator,initdept from incidents
It gives the initiator correct but instead of giving the initdept
it gives output as initaiator
The Output is given below
initiator initdept
Jeff C. Taylor Jeff C. Taylor
Randy S. Jonas Randy S. Jonas
Mike lewis Mike lewis
it should give the out put like
initiator initdept
Jeff C. Taylor Software Engg
Randy S. Jonas Tester
I hope you got my question!
now I hav to update incidents table such that it should give initiator as well as its corrosponding initdept
thanks
View 5 Replies
View Related
May 26, 2006
I am using an ODBC Datareader to connect to a proprietary DB (Epex) and I need help on column mappings.
The Epex database has a number of time columns which we successfully imported using SQL 2000 DTS by defining these as varchar(5).
When using SSIS (Service Pack 1) these fields are shown as 'eight-byte signed integer [DT_I8]'.
I have attempted to these columns to both [DT_WSTR] and [DT_STR]' without success.
I can change the External Column type to [DT_WSTR] but I am unable to alter the Output Coulmns, when doing so I get the following error message:
Error at Data Flow Task [DataReader Source [1]]: The data type of output columns on the component "DataReader Source" (1) cannot be changed.
Does anyone know how to resolve this issue?
Regards
JLF
View 11 Replies
View Related
Mar 11, 2002
Hi
I have a 2 Tables EMP, STU
In EMP Table there is a Column "Country"
In STU Table there is a Column "City"
Where the EMPID = STUID
on this conditon how can i update those 2 columns
This is for one Table one column update how can i do 2 at a time
I don't want to do in 2 seperate UPDATE statements
UPDATE EMP
SET EMP.Country = 'USA'
WHERE EMP.EMPID = STU.STUID
Can some one can help me to fix this issue..
From
Madhavi
View 2 Replies
View Related
May 14, 2004
HI,
I AM HAVING A TABLE WHICH HAS INCREMENTAL COLUMNS,WHERE COLUMNS GETS ADDED EVERY MONTH TO THE TABLE AND THE TABLE THEN CONTAINS PREVIOUS MONTH AND PRESENT MONTH DATA ABOUT CUSTOMERS ,DETAILS AND TRANSACTIONS.
THE PROBLEM WITH THIS DATA IS ,IF THE CUSTOMER IS NEW ,THEN IN PREVIOUS MONTHS HIS INFORMATION IS NULL,WHICH HAVE TO BE CODED HAS "NOT PRESENT".
NOW,
HOW DO WE CONVERT ALL THE PREVIOUS COLUMNS FOR A PARTICULAR CUSTOMER HAS NULL AT THE SAME TIME ?.
HERE IS HOW THE PROC WRITTENED FOR IT GOES :-
DROP PROCEDURE DE_NAT
CREATE PROCEDURE DE_NAT
AS
BEGIN
DECLARE @MONMIN1 NVARCHAR(100),MON NVARCHAR(100),@YEAR NVARCHAR(100) , @MONYEAR NVARCHAR(100)
SET @MONMIN1 = DATENAME((MONTH),DATEADD(MONTH,-1,GETDATE()))
SET @MON = MONTH(GETDATE())
SET @YEAR = YEAR(GETDATE())
SET @MONYEAR = @MON + @YEAR
EXEC('select A.CUSTOMERS,B.*,CAST(A.RFM_40D AS FLOAT) AS R40
INTO TSD_' + @MONYEAR
+ ' from TSD_20 A
LEFT OUTER JOIN SD20 ' + @MONMIN1 + ' B
ON A.CUSTOMERS = B.CUSTOMER')
END
THIS PROC JUST ADDS THE PRESENT MONTHS DATA TILL LAST MONTHS DATA.
BUT IF A CUSTOMER IS NEW, THEN HOW DO I REPLACE THE NULL VALUES FOR THE PREVIOUS DATA TO 'NOT PRESENT'
FOR EG :- IF THERE IS A NEW CUSTOMER ,HOW DO WE CHANGE :-
CUSTOMERS ERTYYTRE RTYUUYTR TYUIIUYT QWERREWQ DFGHHGFD
----------- ---------- ---------- ---------- ----------- ----------
101023 <NULL> <NULL> <NULL> <NULL> 1.0
102022 1.0 1.62.3 3.4 4.5
NOW, AS YOU CAN SEE, THAT FOR CUSTOMERS = '101023'.
THE COLUMN DFGHHGFD IS, THIS MONTHS DATA , I WANT TO CHANGE ALL NULL VALUES PRESIDING IT AS "INACTIVE"
CAN I CHANGE , ALL COLUMNS FROM NULL TO "INACTIVE" , AT THE SAME TIME. ?
AS NEXT MONTH, AGAIN THE COLUMNS IS GONNA INCREASE WHICH WILL
AGAIN CAUSE A PROBLEM .
PLS TELL ME A METHOD , SO THAT I CAN DO THE NEEDFUL.
View 6 Replies
View Related
Oct 29, 2014
I have three different columns, hour(s), min(s), sec(s)
I can add it up, but will like to convert it into. hrs, mins and sec.
this is how, i am adding it up into seconds.
SELECT ((TotalTimeSpentHrs*60*60)+(TotalTimeSpentMin*60)+(TotalTimeSpentSec))AS totaltime
FROM EST1
How can I convert the total seconds, so that i can input the result in a new column.
View 1 Replies
View Related
Dec 4, 2006
Hi all
ALTER TABLE <Table_Name> ALTER COLUMN <column_name> <Data_type>
above query alters the sigle column but how to alter multiple columns at time
saivyshnav
Junior DBA
View 7 Replies
View Related
Aug 28, 2007
adate atime
08-21-2007 11:09
08-20-2007 16:49
08-03-2007 00:39
I would like to combine adate with atime to get adatetime
View 5 Replies
View Related
Sep 12, 2007
I have Table1 with 2 columns Label_ID and Athlete_ID, I have another Table2 with 3 columns Label_ID, Athlete_ID, Data.
I need join this tables so the result table will have the same number of rows as Table1 and have extra column add Data which will correspond to Data in Table2 if Label_ID an Athlete_ID are matched and NULL if no matches found.
I have following query which does not produce desired result
SELECT Table1.label_id, Table1.athlete_id, data FROM Table1 LEFT OUTER JOIN Table2 on (Table1.label_id = Table2.label_id AND Table1.athlete_id = Table2.athlete_id)
The end result of this is table with only rows where label_id and athlete_id are matched between tables but no results when they are not. I expected OUTER JOIN to have those result but it's not working for whatever reason.
I'm pretty sure it's simple solution but can not figure out myself.
View 4 Replies
View Related
May 17, 2005
Hi all!
If I have some mdx I'm using in reporting services like this:
select
{ [TimeByMinute].[All TimeByMinute].[2005].[May].[1] : [TimeByMinute].[All TimeByMinute].[2005].[May].[6] } on columns,
{A_list_of_measures } on rows
from ACD_Calls
The column names are unique to the day of month- which means when I use a table to display this in reporting services, the field names change dynamically when the date parameters change which means the table stops working.
I'll post this in reporting services too but I thought maybe I could alias the column names in mdx shielding the reporting services table from changes in dates.
What do you think? Would a matrix be more flexible in this case?
Richard
View 7 Replies
View Related
Mar 26, 2012
I have 2 tables:
TransactionsImport (which is the destination table)
TransactionsImportDelta
I need to do the following:
Get the records with the latest date and time in the destination table TransactionsImport
Get the records with the latest date and time in the destination table TransactionsImportDelta table
Insert the records from the TransactionsImportDelta table into TransactionsImport that have a greater date & time than the current records in TransactionsImport table.
Problem is date & time are in separate columns:
Table structure:
Date Time ID
2011121305154107142201008300100
2011121305154122B1L13ZY0000A07YD
2011121304504735142201090002600
2011121304504737142201095008300
2011121304504737142201090002600
View 2 Replies
View Related
Mar 10, 2014
I have a temperature table with a column that shows the how many hours old a newborn was when his/her temperature was taken.
Example lets say once per hour.
I want to join to a table called Weight that records the newborns weight at any given time.
Example lets say 3x during the day.
1 @ 8:45am
2 @ 11:15am
3 @ 4:30pm
I want to figure out which weight recording is the closest to a given temperature recording and return that one row.
View 2 Replies
View Related
May 20, 2015
I have a simple table as shown:

I want to have values on the last column to represent the time interval between the 2 date columns (visits); i.e for event-ID 2 for example, I will have
entry(EventID = 2) Â - exit(EventID = 1), and so on
View 7 Replies
View Related
May 15, 2008
Hi,
In my DB, many of my tables have a column named upsize_ts I have already been told that it is not linked in any way with the data in the DB. The data type of these columns is timestamp and every record in those colum is this : "<Binary>"
Now maybe it's because the whole DB comes from an export from an Access DB, or maybe not. I just had a few questions:
1/ Does anyone know why that column got generated and what it means? (optional)
2/Now the real question: is there any statement allowing me to "mass-drop" all those columns named "upsize_ts" in every table of my DB at the same time? Or at least any way not to do it one drop table at a time?
Thanks.
View 8 Replies
View Related
Jul 5, 2006
Hi.
I'm working on an application that will use SQL Server 2005 Everywhere Edition for single user installations (i.e. notebooks) and SQL Server 2005 Express Edition for network installations. The single user databases will synchronize with the network databases.
The databases will have to store a large number of documents of different kinds, most of them will be less than 200 KB in size. According to the SQL Server 2005 Express Edition documentation, I should avoid using the IMAGE data type and opt for VARBINARY(MAX) instead. The current version CTP of SQL Server 2005 Everywhere Edition does not support VARBINARY(MAX) (the maximum size is 8000 bytes).
Will the final version of SQL Server 2005 Everywhere Edition support VARBINARY(MAX)? Or should I use the IMAGE data type in both schemas or IMAGE in the SQL Server 2005 Everywhere Edition database and VARBINARY(MAX) in the SQL Server 2005 Express Edition database?
Do I have to consider anything special when the databases are synchronized via replication because of the large number of rows with binary fields?
Regards,
Gerrit
View 1 Replies
View Related
Aug 7, 2007
Hi all,
I have created a report in SSRS 2005 which is being viewed by users from different Time Zones.
I have a dataset which has a field of type datetime (UTC). Now I would like to display this Date according to the User Time Zone.
For example if the date is August 07, 2007 10:00 AM UTC,
then I would like to display it as August 07, 2007 03:30 PM IST if the user Time Zone is IST.
Similarly for other Time Zones it should display the time accordingly.
Is this possible in SSRS 2005?
Any pointers will be usefull...
Thanks in advance
sudheer racha.
View 5 Replies
View Related
Dec 13, 2007
Hello:
I am running into an issue with RS2k PDF export.
Case: Exporting Report to PDF/Printing/TIFF
Report: Contains 1 table with 19 Columns. 1 column is static, the other 18 are visible at the users descretion. Report when printed/exported to pdf spans 2 pages naturally, 16 on the first page, 3 on the second, and the column widths have been adjusted to provide a perfect page span .
User A elects to hide two of the columns, and show the rest. The report complies and the viewable version is perfect, the excel export is perfect.. the PDF export on the first page causes every fith column, starting with the last column that was hidden to be expanded to take up additional width. On the spanned page, it renders the first column on that page correctly, then there is a white space gap equal to the width of the hidden columns and then the rest of the cells show with the last column expanded to take up the same width that the original 2 columns were going to take up, plus its width.
We have tried several different settings to see if it helps this issue or makes it worse. So far cangrow/canshrink/keep together have made no impact. It is not possible to increase the page size due to limited page size selection availablility for the client. There are far too many combinations of what the user can elect to show or hide to put together different tables to show and hide on the same report to remove this effect.
Any help or suggestion on this issue would be appreciated
View 1 Replies
View Related
Sep 24, 2007
Hi
I want to enter rows into a table having more number of columns
For example : I have one employee table having columns (name ,address,salary etc )
then, how can i enter 100 employees data at a time ?
Suppose i am having my data in .txt file (or ) in .xls
( SQL Server 2005)
View 1 Replies
View Related
Mar 15, 2008
ok. Here we go again. they got my Servers built yesterday and here I am on a saturday doing the installs
I ran SQL SERVER 2005 Standard Edition Setup on my Windows 2003 Enterprise Server with SP2
it ran through and installed Client tools, BOL and such.. but did not install an actual Instance. I never got the Advanced button to choose which components to install. What did I do wrong????????
I need to install a Named Instance on Both Server.. one will be DEV one Production. help?
View 3 Replies
View Related
Feb 4, 2008
I need help with the following field:
'Age @ TOS' = DATEDIFF (YY, patientprofile.birthdate, patientvisitprocs.dateofservicefrom),
PatientProfile.Birthdate - 01/01/2001
PatientVisitProcs.dateofservicefrom = 07/01/2001
In my report, I get a '0' as my Age @ TOS. What I would like to have is 7 months vs the '0' it is reporting. I would like to try accomplishing this without doing a function if possible. I know I used YY in my DATEDIFF. I only noticed this today because of a few patients in my DB were under 1 yr at the TOS.
View 1 Replies
View Related
Jul 2, 2006
I understand that SQL 2005 doesn´t support TIME ( which is, for me,
incredibly weird). Now, could anyone please help me solve my problem. I
want to make it possible to insert into database a start-time for the
first runner in a team. And when he finishes, a finish-time. This
finish-time I will also use as a start-time for the second
runner....a.s.o. Furthermore I want to be able to get the results from
the database to present them in several ways. This also means I can't
simply insert the time as a string since it won't change hour at every
60 minutes. I have read somewhere that it should be possible to create
own datatypes, but I have absolutely no knowledge to that. Could anyone
please help me?
View 14 Replies
View Related
Mar 6, 2007
Is there a way to pull data from XML columns with SSIS? I mean parsing the XML and pulling only data from XML column without putting it into an XML file first. I have been using xpath queries (XML value method) to do this. Is there any other way?
Thanks
View 2 Replies
View Related
Dec 3, 2007
hi all,
in my sql server 2005 , i 've a table gg containing a column 'date'.its data type is datetime ,null
now i want to insert only the date to this field from vb.net page. i dont want "time" to insert in this page. one more thing ,i know varchar will work for what i need but i want to use date time. If anyone who knows how this is possible pls do send me the code.
thanks swapna
View 10 Replies
View Related
Feb 12, 2008
I will be installing SQL Server 2005 enterprise edition for the first time and was looking for some hints...
unfortuntely, I don't know alot about what kind of applications they will be using the databases for.. but I do know that I have 400GB to allocate. My manager suggested a seperate volume for the SQL Server Binaries.. not sure why... ANY OPINIONS ON THAT???
This is what he suggested
C: OS
D:SQL Server application install
E:datafiles
F:Logfile
e:tempdb
any hints, suggestions, opinions, things to watch out for... ???????
I was thinking more along the lines of
c:OS and application installs (sql server etc..)
E:datafiles and TempDB
F:Logsfiles
View 6 Replies
View Related
Apr 24, 2006
I have a problem installing sp 1 for sql 2005 express. How can I check if I have sql server or the sql 2005 express edtion and if it is a beta or not. I want ot install the SP1 OF SQL 2005 When I go to the add/remove program section I see sql 2005 edition and not sql 2005 express edition, very confusing because it indicates to look for sql 2005 express edition in the add/remove program and remove it. I don't even know if I have a beta release or not at this point anymore. Is sql 2005 express packaged with VWD 2005 express edition.
View 3 Replies
View Related
May 6, 2008
Hello,
We are using Reporting Services 2005 with large amount of data.
Our reports displays the data in various presentation (charts, tables, Images and matrixes).
Sometimes the underline queries takes a lot of time (few algorithms implemented by SQL and few by web services).
We tried to run the reports in various scenarios and we are getting a timeout n each of the scenarios:
1. Running the report with windows application (based on Report Viewer control)
2. Running the report via subscription.
We noticed than the timeout occurs after about 20 minutes.
WE changed the following parameters:
1. The system timeout in the report server database: 60000
2. Reports execution timeout (site level): Do not timeout report execution
3. Reports execution timeout (report level):
a. Do not timeout report execution
b. A very big number
View 1 Replies
View Related
Apr 2, 2008
Hi, I know sys.tables and sys.columns gives me a list of tables andcolumns in a SQL 2005 database.How can I list Columns in a specific Table please?Thanks in advance,Ronny
View 3 Replies
View Related
Mar 17, 2008
Calculating time between two date In Sql Server 2005help me ..
View 3 Replies
View Related
May 2, 2007
saw my AS400 has this time stamp: 2007-05-02-11.29.56.811195
is SQL2005 able to have time stamp to this details?
View 1 Replies
View Related
Oct 13, 2007
Just placed the SQL Server 2005 - Developer Edition into my Drive...
I get prompted with the following options..
This DVD contains 32-bit (x86) and 64-bit versions of this edition of SQL Server 2005. Click the link that matches your environment to begin
x86-based operating systems
x64-based operating systems
Itanuim-based operating systems
I'm new to SQL Server... I just need to start learning how to use it... because it is being used at work... Don't need the all the features installed, just the normal table and query for the moment...
I will be installing it on my PC... i
Microsoft Windows XP - Home Edition - Version 2 - Service Pack 2
My local Hard Drive remaining capacity is 12.6GB
External Hard Drive remaining capacity is 11.0GB
Tried looking everywhere for the answer... lol (even looked at YouTube for a simple installation video....
Thanks for your help
View 14 Replies
View Related
Oct 18, 2007
Any help would be appreciated.
We have a C++ program that connects to a remote SQL Server database. Recently the system we pull data from upgraded to SQL Server 2005 and my program can no longer connect to the database. We're getting TimeOut expired errors (not login timeout expired) within 15 - 20 seconds after attempting to open the connection. I have confirmed with this other system that my id does not show up in any logs on their end.
Here is some background:
Nothing has changed on my end. We have another system that my team supports that is able to connect, and they have changed nothing. They're using a different connection method. We're using oledb and they use odbc and a different provider. I confirmed with the SQL Server team that this id made a connection.
Connect String:
Provider=SQLOLEDB.1;;User ID=XXX;Password=xxxx;Data Source=serverdb instance;Initial Catalog=xxxx;
From what i've read, etc this connect string should work still
Here is an example of the code and context of this connect string
'Set connection object
pTEST_HR(pConnect.CreateInstance(__uuidof(Connection)));
pConnect->ConnectionString = OurConnectString;
pConnect->Open("","","",adConnectUnspecified);
'Create and open recordset
TESTHR(pRstEmployee.CreateInstance(__uuidof(Recordset)));
pRstEmployee->Open("Employee", This is the point at which we get a timeout.
_variant_t((IDispatch *)pConnection,true), adOpenKeyset,
adLockOptimistic, adCmdTable);
pCmd->CommandText = SomeString;
pRstEmployee = pCmd->Execute(NULL,NULL,adCmdText);
Server Info:
I have succesfully pinged the server
The client is not in the same domain as the db server
Error:
Appl: A database error has occured ->
Error
Code = 80040e31
Code meaning = IDispatch error #3121
Source = Microsoft OLE DB Provider for SQL Server
Description = Timeout expired
Other info:
Not sure about my client database provider.
This is coded in Visual C++ 6.
I'm not knowledgeable on SQL Server configuration, etc.
Things i've tried so far
Change provider from SQLOLEDB.1 to SQLOLEDB
Change provider to SQLNCLI
Had SQL server query time out set to never time out. The remote login timeout changed to 600 seconds from 30 seconds. However we are getting a timeout in only a few seconds, so i find that interesting.
View 9 Replies
View Related