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:
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?
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?
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
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
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 :-
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.
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.
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?
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
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
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?
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?
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
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?
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.
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?
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?
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
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
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.
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
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
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
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);
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.
