Hi,
I am new to sql and is working with sql server managment 2005 +c# 2005.
My application needs to create a blockdiagram sort of thing say
if in my database i got a table 'Addition' with 'a', 'b', 'c',and the primary key addition_id, and c is related to a and b as c = a+ b.
there is stored procedure name usp_addition which contains this relation. Each time any insert or update is done this sp is executed and all the values are updated for accordingly.
My problem starts in the front end where i need to draw the graphical representation of table addition.
In this graphical representation, I need to draw the labels a, b, c and the arrows from a and b which will connect to c, showing that c has a, b as inputs.
I got the label using dataset and datacolumns but hte problem is how to create the arrows the name of labels (i.e my column names from which the arrow should start and end)
How does I get the information that c as two inputs a, b. I dont need the values since i just want to view the columns in table and which column is input to another column.
Since I need to do this dynamically because my tablename, and the number and name of column would differ does any body knows how to do this.
How can I create a Table whose one field will be 'tableid INT IDENTITY(1,1)' and other fields will be the fields from the table "ashu". can this be possible in SQL Server without explicitly writing the"ashu" table's fields name.
Lets sat that i have 3 tables that i want to relate together manually (no wizards) by using SQL statements.I am using Microsoft SQL Server 2005 in Visual Web Developer 2008 using VB programming (ADO.NET)The 3 tables are as follows: FloorRoomLocation Fields inside each of the tables can be found below: Floor Table(PK) ID(Required) NameDisplayOrder Room Table(PK) ID(Required) Name(PK, FK) FloorIDExchangeIDExtensionIPDeviceNameRemarks(Required) Equipment(Required) Floor2IDFloorLocationXFloorLocationY Location Table(PK,FK) FloorID (Required) CountryName(Required) CityName(Required) BuildingName My Intended relationships between the 3 tables are as follows: ID field in Floor table is referenced by the field named FloorID in the Room Table ID field in Floor table is referenced by the field named FloorID in the Location table NOTE: Location Table serves only as an extension to Floor table thus they have a 1 to 1 relationship, due to My Projects Lead's instructions I am not allowed to consolidate the Floor Table and the Location Table as 1 whole table. Floor Table has a 1 to Many Relationship with the Room Table. I Have already done the physical relationships between each of the 3 tables in the Database Diagram. All i want is the SQL statement that does the link/relationship between this 3 tables, that's all :)
Hi all, Lets say that i have 3 tables, they are already related together, information on each of the 3 tables and their relationship between eachother can be found below: I am using Microsoft SQL Server 2005 Database in Visual Web Developer 2008 using VB programming (ADO.NET) The 3 tables are as follows:
Floor Room Location
Fields inside each of the tables can be found below:
Floor Table (PK) ID (Required) Name DisplayOrder
Room Table (PK) ID (Required) Name (PK, FK) FloorID ExchangeID Extension IP DeviceName Remarks (Required) Equipment (Required) Floor2ID FloorLocationX FloorLocationY
The Relationships between the 3 tables are as follows:
ID field in Floor table is referenced by the field named FloorID in the Room Table
ID field in Floor table is referenced by the field named FloorID in the Location table
NOTE: Location Table serves only as an extension to Floor table thus they have a 1 to 1 relationship, due to My Projects Lead's instructions I am not allowed to consolidate the Floor Table and the Location Table as 1 whole table.
Floor Table has a 1 to Many Relationship with the Room Table.
I Have already done the physical relationships between each of the 3 tables in the Database Diagram, in Visual Web Developer 2008 Express Edition under the Database Explorer
So i have created this form to accept user input:
this form is in a aspx page called AddRooms.aspx
Country Name:______________ (data sent to location table)
City Name:_________________ (data sent to location table)
Building Name:______________ (data sent to location table)
FloorID:[This is a DropDownList which is databinded to the Floor Table]
Room Name:_______________ (data sent to room table)
*Just imagine that the above is a form to accept user input
*With a hit of a submit button all data will be sent to their respective tables in the database
Now i know that i have to JOIN tables in my database in order for my application to work, but i have no experience performing JOIN, i need great guys like you to help me apply JOIN to the 3 tables mentioned above, do let me know if you have any doubts in what i am trying to do.
Do give me that code that i can either apply in the (VB environment Visual Web Developer 2008) and/or in Microsoft SQL Server 2005 environment
Just need the SQL Statment that i will need to construct to perform the JOIN i need to.
I have a contact table and a customer table. The two tables will contain columns like First name Last Name, Date of Birth Post Code, House Number Street Name etc.
I would like to find the different combinations in which I can relate the customer and contact data. Like its is possible that the first name and last name are same but date of birth is different. This indicates that the contact and customer is the same. Now I do not know these combinations and I would like to have this set generated for me. From Integration Service (Sql Server 2005) I get the data and I would like to know the patterns in which data will differ. Is there any way of achieving this?
I am very new to Data Mining and would like to have some direction as to how to progress with this.
Please stick with me for a second... If someone dealt with tables related to US State/County and ZIP. We found a company on the Web which have ALL US counties, states, zips and cities in one big table. We need somehow to break this table( about 76K records) into multiple related tables and estabsish Some sort of relationship among these tables. 1.State table(design) ID State_Name 2.County table( design) ID County_Name Relationship_to_State For example if state = 'NY'; then all Counties falls in NY should have relationship '01'. For NJ '02' and so on. 3.Zipcode table( design-same as for county)
If someone has any idea how to solve this problem that would be really appreciated. Thanks a lot in advance.
Iterating through Running packages as in this code from MSDN:
RunningPackages pkgs = app.GetRunningPackages("yourserver"); // Enumerate through each package in the collection and display some data. foreach(RunningPackage package in pkgs) { Console.WriteLine("InstanceID: "+package.InstanceID); Console.WriteLine("PackageDescription: "+package.PackageDescription); Console.WriteLine("PackageID: "+package.PackageID); Console.WriteLine("PackageName: "+package.PackageName); Console.WriteLine("UserName: "+package.UserName); }
How would I find any rows in sysdtslog90 related to that instance of RunningPackage (i.e. the 'package' variable)? The InstanceID does not seem to relate to the executionID that is in the log table.
So I have a question and I will give a related example. Say I have a list of records in a table:
Product Cost Active
hat 1.00 false
coat 2.00 true
I have a DataGridView that shows purchases from a purchase table: Date, ProductID, Buyer
Is there a way to have a DataGridViewComboBoxColumn show only active items so that a user can only select active items in the puchases datagridview, but when they click a purchase from long ago when an item was active they have the ability to pick that old(unactive) item or a new one for an update.
Basically if I just have the ComboBoxColumn bound to a dataset with just active items, the datagrid will fail to load cause old purchases are not in its item list, but I also don't want all the items to be in the list.
I have a list of items in one table and a field (pageName) in another table that may contain one of the aforementioned items somewhere within that field. There is no fixed position within the field where the itemNo may be so I can't just use SUBSTRING(pageName,2,5) in(select itemNo from tblItem).
Logically, it's like I need to combine IN and LIKE: select pageName where pageName LIKE IN %select itemNo from tblitemNo%..LIKE can only handle one comparison string.
Hello,I'm using a shape query, but instead of using a simple clause "RELATEfield1 to field2" (relates the parent to the child), i wan't to use 2relates. somthing like "RELATE field1 to field2 AND field3 to field4".I want to receive in the children RS only the records who apply bothconditions.How do i do that ?Thanks !
Is it possible to creates fields of the table dynamically?. I have this situation in my project. This is just a small sample. I have row of length 140. I don't wan't to declare all this fields manually using the create table command.
The description of table is as, in this table all the field are of type varchar only, there are like 140 columns.
Table: Dummy ================================================== == field1 field2 field3.......... Empid Empname empaage1 sam 23........... 2 rai 22............ . . . n raj 45............. ================================================== == Now I want to create another table as "EMP" , with proper data type fields too..
Table: EMP ================================================== == Empid Empname empaage............ 1 sam 23............... 2 rai 22................ . . . n raj 45................. ================================================== ==
I want to do this dynamically..... Some how I need to extract those field from table[dummy]; the first row acts as a column header for the table[Emp] and the subsequent row acts as a record for the table[Emp]
A small rough snippet of the code will be appreciated....
All- Supposing I have table_a and table_b. Table_a has fields FIRST_A and LAST_A for people's first and last names. Similarly, table_b has FIRST_B and LAST_B for people's first and last names.
How would I create a record in table_b for each record in table_a, such that for each added record, the values of FIRST_A is copied to FIRST_B and LAST_A is copied to LAST_B. (Table_b will have other fields that are left unfilled.)
I want to let a user a fields and select the data type and size from a short list. For example: nvarchar(?), Money, Int, or Bit. These are most likely the only choices for now. There will be a web page that lets them enter a field name and select a data type. Can someone suggest how the sproc might look? Here is what I have so far. CREATE PROCEDURE [dbo].AddCustomField ASALTER TABLE MyDataSQL.dbo.tblCustomFields ADD @FieldName @FieldDataType NULLGO Thank you,
I want to put fields from four tables into one table. I created a new table, but how do i get the same fields from the other tables to this table along with primary and foreign keys.
I need to get the field values of a table (name will be dynamic).Then assign those values to properties in a class.Let's say I will get the table name dynamically.dim tblName as string = "tablea"The 2 tables can each have 25 fields or so.I need a way to select the amt and email field values from tblName. Without saying "select job_amt, job_email from ...Is there someway to get the values based on the column name. So if the column name has amt and email, then give me those values.Maybe loop through the datatable - then for each column -- if col.ColumnName.IndexOf("Amt") = 0 or col.ColumnName.IndexOf("email") = 0 thenthen drop that column from the datatable.ex of table structure<u>tablea</u>job_idjob_amtjob_email<u>tableb</u>dance_idamt_dancedance_email
I would like to store all information on them in this table but that amounts to quite a lot of fields (about 50) and some of these will store a lot of HTML text in them.
Is it best to split the table up for performance reasons or will it make little difference?
I have maked this little code but dont know the way to insertt new table and fileds in database DATABASE01...
Private Sub Command1_Click()
Dim objConn As ADODB.Connection Set objConn = New ADODB.Connection objConn.ConnectionString = "driver={SQL Server};server=DVD377-14D9E48CSQLEXPRESS;uid=;pwd=;database=master" objConn.Open objConn.Execute "CREATE DATABASE DATABASE01"
'''''''''''''''''' '"CREATE filed1" in text format '"CREATE filed2" in text format '"CREATE filed3" in text format '"CREATE filed4" in date format(DD/MM/YYYY)
I am new to SQL Server 2005 and I am trying to add two fields to an existing table. The table has 15 Million records in it and the save is not completing. How do I add the new fields?
I have two name columns in my table, NAME1 & NAME2 that I want to compare to see if they match. Only problem is that the order of the first, last, middle name can be either same or different between the two fields.
For example NAME1 = JAY JOHN SMITH NAME2 = JOHN SMITH JAY or SMITH JOHN JAY
Is there a way to somehow reorder these fields and then compare using SQL?
I'm kind of new to SQL and would appreciate some help with this. I have a table named Company. In this table there are fields called CompanyID, CompanyName, Address, and Year. (There are other fields too, but they are not relevant for this post.) There are multiple companies in the table and each company has a unique ID number. However, each company can have up to three records in the table. (One for the year 2005, one for the year 2006, and one for the year 2007.) Currently, only a company's 2007 record has data for the address. It's 2005 and 2006 records have addresses that are blank. I want to be able to update the 2005 and 2006 records with address from the 2007 record. Obviously, a very simple update won't work because there are multiple companies and multiple records that need updating. Can anyone fill me in on the SQL statement(s) I would need to make this happen? I would be extremely appreciative.
i want to make a table in order to keep the data of a hard disk. e.g. create table disk{ capacity integer, name varchar(50), ports integer, buffer char(1));
For example i want this hard disk to have these fields and i would like from the user to type me only the capacity because it is going to be a required field and the other fields(name,ports,buffer) to be optional!
How do i declare optional fields in a create table command? and then how do i declare/define these fields in insert too?
Hello,I would like to create a stored procedure that would compare the fieldsof two tables and their types. If they are different the user iswarned.How can I do that ?thx
I have a table that display large amounts of text in some fields. The table only has two columns (Field Name and Field Data). If the Field data is too large to fit on the same page as the field above it, it pushes to the next page, and then starts printing at the top of that page (the next page down).
I tried setting the "Keep Together" property of the table = True, but this was of no use.
Has anyone found a way to work around this, and if so could you let me know what you had to do. It may just be a SQL Server default setting that cannot be changed. I just want to research all possibilities before reporting back to the users.
I have 240 sensors to sample one time per second,then store the data in sql server.
Is it reasonable that the table has 240 fields?If do,it will generate about 70,000 records every day.One month records will be 2,100,000.
If I divide the 240 sampling data into 10 records,every record has 24 fields.Then every day 700,000 records will generate.One month the record number is 21,000,000.It is terrible.
Which method is better,or who can give me another better method or some suggestions.
I am working on a project which requires having a form that must be filled by the user. The number of the fields on this form is arround 150. Is it a good idea to have a database table with 150 fields (columns)? If its not what would be a better approach for this case? Handling this many fields on a windows form is another issue but first I would like to know about how to deal with the data storage.
Ha all again. I have this issue: I have 2 tables: t1 (id,field1,field2,field3) and t2 (id,field_name,field_value)
is possible with trigger to insert only changed fields on t1 to t2 like
(changing field1 on t1 with id 21 cause a new row in t2 with fields id=21,field_name="field1", field_value=field1.value)
i hope you understand what i look for.
Is this trigger solution ?
CREATE TRIGGER MAP_CHANGESON t1AFTER INSERT,UPDATEBEGIN DECLARE @ID SOMEDATA;DECLARE @FIELD1 SOMEDATA;DECLARE @FIELD2 SOMEDATA;DECLARE @FIELD3 SOMEDATA;SET @ID=(SELECT ID FROM INSERTED)SET @FIELD1=(SELECT FIELD1 FROM INSERTED AS T1,DELETED T2 WHERE T1.ID=T2.ID AND T1.FIELD1<>T2.FIELD1)SET @FIELD2=(SELECT FIELD2 FROM INSERTED AS T1,DELETED T2 WHERE T1.ID=T2.ID AND T1.FIELD2<>T2.FIELD2)SET @FIELD3=(SELECT FIELD1 FROM INSERTED AS T1,DELETED T2 WHERE T1.ID=T2.ID AND T1.FIELD3<>T2.FIELD3)IF @FIELD1<>NULL INSERT INTO T2 (ID,FIELD_NAME,FIELD_VALUE) VALUES (@ID,'FIELD1','@FIELD1')IF @FIELD2<>NULL INSERT INTO T2 (ID,FIELD_NAME,FIELD_VALUE) VALUES (@ID,'FIELD2','@FIELD2')IF @FIELD3<>NULL INSERT INTO T2 (ID,FIELD_NAME,FIELD_VALUE) VALUES (@ID,'FIELD1','@FIELD3')RETURNEND
I'm basically new using reporting services, I hope someone can help me
I'm using two datasets in the same table this is because I that was the only way to obtain the data that I need, my problem is that when I try to use the folowing statment =Fields!Total.Value/Fields!Total2.Value,"seconddataset" I got an error because the scope, the expression edtior give me the Sum(Fields!Total2.Value,"seconddataset") wich it works but that is not what I want, I need to divide by the single value not the total Sum
the table is using the firsdataset but I need to use some fields from the seconddataset