Hi
I have two database as Malathi,Indoo
In Malathi database i have
Employee Table AS
----------------------
Eid Int (PK)
Enam varchar(50)
In Indoo Database I have
Job table as
Eid int (FK)
Jid Int (PK)
Now how can i create relation b/w two tables of different database
Any One can help for this query
Malathi Rao
Hi, I am trying to create a create for two table A and table B which have no relationship each time. For TableA, there are 3 columns like ID, APoints1, APoint2. For Table B, there are also 3 columns as ID, Qty, BPoints. There is no internal relationship for these two tables. But there may be same ID inside A and B for some records. Now I want to create a datagrid for displaying the information as :
ID, Sum(A.APoints1 + A.APoints2) - Sum(B.Qty * B.BPoints) WHERE A.ID = B.ID
Please Notice that I can't use directly SQL script as following from table A and table B because there is no relationship for Table A and Table B, otherwise the recult set would be wrong:
Select A.ID, Sum(A.APoints1 + A.APoints2) - Sum(B.Qty * B.BPoints) WHERE A.ID = B.ID group by A.ID
May I know is there solution for it?
Thank you very much!
I have two tables, Inspections and InspectionDetails. The InspectionDetails only contains information if an inspection found violations. Some inspections don't look for violations so inspectiondetails would have no entry.How can I create a 0-Many relationship between these two tables?Inspection Table has : InspectionID, InspectionDate, TypeofInspection, Inspector..InspectionDetail Table has : DetailID (an auto-incrementing identity column), InspectionID, SectionViolated, ViolationDescription, InspectorNotes.
View 6 Replies View RelatedI need to create a relationship between a local table and tables on alinked server. I used the design table wizard and selected therelationship property wizard. In the reslationship property wizard,the tables that I need to get the keys from in the linked server do notshow up. Is there a way to do this, or I simply don't have enoughpermission to tables in the linked server. On the local server, theSecurity tab of linked server property has Local Loging "sa", RemoteUser "sa" and Remote Password "****". Thanks for your help.
View 1 Replies View RelatedI would like to create a auto relationship between tables.
Currently I am using Northwind DB with tables (Orders, OrderDetails, Customers)
Orders ( OrderId, Customerid)
OrderDetails(OrderId)
Customers(CustomerID)
Now, if the user wants to generate a relationship automatically based on SAME FIELD Names.
What is the approach?
I have two table studenTtable and courseTable which is each student take more than one course . 1:M...for example Student1 take 2 courses (C1 , C2). Student2 take 3 courses (C1,C2, C3).I need to create a table/View that contain student information from StudentTable plus all the courses and the score for each course from CoursTable in one row.
for example Row1= Student1_Id ,C1_code ,C1_name ,C1_Score ,C2_code,C2_name ,C2_Score Row2= Student2_Id,C1_code, C1_name,C1_Score,C2_code ,C2_name ,C2_Score , C3_code,C3_name,C3_Score
and since Student one just have two courses , I should enter NULL in 'Course 3 fields'.My Struggle is in the insert statement I tried the following but it show an error
Insert Into Newtable ( St_ID, C1_code,c1_name, C1_Score ,C2_code ,C2_name,C2_score,C3_code ,C3_name,C3_score)
Select (Select St_ID from StudentTable) , (Select C_code,c_name,c_Score from Coursetable,SudentTable where course.Stid =Studet.stid) , (Select C_code,c_name,c_Score from course ,student where course.Stid =Studet.stid ), (Select C_code,c_name,c_Score from course ,student where course.Stid =Studet.stid );
I'm fully aware that the New table/View will break the rules of normalization ,but I need it for specifc purpose.I tried also the PIVOT BY functionality but no luck with it .I also tried writing a code using Matlab (because it is high level sw that it is easy to learn for people not expret in programming as me) but didn't know how to combine the Student and Courses Matrices in my loop.
I want to create a table with primary key , and put relationship with second table.
View 5 Replies View RelatedHow can I create a one-to-one relationship in a SQL Server Management Studio Express Relationship diagram?
For example:
I have 2 tables, tbl1 and tbl2.
tbl1 has the following columns:
id {uniqueidentifier} as PK
name {nvarchar(50)}
tbl2 has the following columns:
id {uniqueidentifier} as PK
name {nvarchar(50)}
tbl1_id {uniqueidentifier} as FK linked to tbl1.id
If I drag and drop the tbl1.id column to tbl2 I end up with a one-to-many relationship. How do I create a one-to-one relationship instead?
mradlmaier
Hi all,
I use the following 3 sets of sql code in SQL Server Management Studio Express (SSMSE) to import the csv data/files to 3 dbo.Tables via CREATE TABLE & BUKL INSERT operations:
-- ImportCSVprojects.sql --
USE ChemDatabase
GO
CREATE TABLE Projects
(
ProjectID int,
ProjectName nvarchar(25),
LabName nvarchar(25)
);
BULK INSERT dbo.Projects
FROM 'c:myfileProjects.csv'
WITH
(
FIELDTERMINATOR = ',',
ROWTERMINATOR = ''
)
GO
=======================================
-- ImportCSVsamples.sql --
USE ChemDatabase
GO
CREATE TABLE Samples
(
SampleID int,
SampleName nvarchar(25),
Matrix nvarchar(25),
SampleType nvarchar(25),
ChemGroup nvarchar(25),
ProjectID int
);
BULK INSERT dbo.Samples
FROM 'c:myfileSamples.csv'
WITH
(
FIELDTERMINATOR = ',',
ROWTERMINATOR = ''
)
GO
=========================================
-- ImportCSVtestResult.sql --
USE ChemDatabase
GO
CREATE TABLE TestResults
(
AnalyteID int,
AnalyteName nvarchar(25),
Result decimal(9,3),
UnitForConc nvarchar(25),
SampleID int
);
BULK INSERT dbo.TestResults
FROM 'c:myfileLabTests.csv'
WITH
(
FIELDTERMINATOR = ',',
ROWTERMINATOR = ''
)
GO
========================================
The 3 csv files were successfully imported into the ChemDatabase of my SSMSE.
2 questions to ask:
(1) How can I designate the Primary and Foreign Keys to these 3 dbo Tables?
Should I do this "designate" thing after the 3 dbo Tables are done or during the "Importing" period?
(2) How can I set up the relationships among these 3 dbo Tables?
Please help and advise.
Thanks in advance,
Scott Chang
Here is the error I am getting'role' table saved successfully'users' table- Unable to create relationship 'FK_users_role'. ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server]ALTER TABLE statement conflicted with COLUMN FOREIGN KEY constraint 'FK_users_role'. The conflict occurred in database 'raintranet', table 'role', column 'role_id'.table rolerole_id intname varchar 50table usersusers_id introle_id inTrying to get table.role_id to be related to role.role_idAny help would be appreciated
View 1 Replies View Relatedhello all,
I am new to SQL server and dont have a clue on how to create a one-to-one relationship
in sql server2000. Say, I have a table 'A' with a PK <customer_id> and another table 'B' with a PK <order_id>. Now to define a one-to-one relation between them how to do that?
thanks in advance.
-tanveer
Hi - SQL beginer here....
SQL2005
I have a table dbo.server
Compid - 1
Name PK - Server1
Make - HP
etc...
I have just created a new table dbo.ProcessorInfo with the following columns:
Name
BrandName
ProcessorCoreCount
etc....
This table will have more than 1 record for each name:
Server1 Intel 2
Server1 Intel 2
Server2 Intel 1
Server2 Intel 1
and so on.
What is the best way to relate the name in dbo.server and name in dbo.ProcessorInfo so this is a one to many? Obviously I can't set the name column as a PK as I have more than 1 record of the same value in it.
Help much appreciated!
How do I create a relationship between two tables when the two columns that should connect each table have different names?
i.e. Table Person Column Name < -- > Table Employee Column Person Name
The two columns actually contain the same data type but different field name and size. I know this involves a series of steps. What are they? Could you please include some sample code.
Hi, I come back again.
Can anyone help me to create table with many-to-many relationship. Here is my three tables
tbl_Networks
(
NID int identity(1,1) primary key,
NetworkName nvarchar(256)
)
tbl_Categories
(
CID int identity(1,1) primary key,
CateName nvarchar(256),
NID int
)
tbl_Sim
(
SID int identity(1,1) primary key,
NID int,
CID int,
NameOfSim nvarchar(256)
)
My problem is 1 value in tbl_Sim may have multiple values in table tbl_Categories and vice versal. And I don't know how to organise them
So I need some help...
Hi.
I get this error when i try to create a relationship in a db diagram (sql 2005)
"'tblActivedir' table saved successfully
'tblClient' table
- Unable to create relationship 'FK_tblClient_tblActivedir1'.
Introducing FOREIGN KEY constraint 'FK_tblClient_tblActivedir1' on table 'tblClient' may cause cycles or multiple cascade paths. Specify ON DELETE NO ACTION or ON UPDATE NO ACTION, or modify other FOREIGN KEY constraints.
Could not create constraint. See previous errors."
What i have is 2 tables.
1 named client
1 named activedir
In the client table the columns i want to bind with activedirtable are FR1 and DC1
I want to bind them in the ID of the activedir table (both, in different fk relationships) so that they get the id of activedir.
Fr1 has an fk relationship with activedir (pk is activedir' id)
and DC1 exactly the same in another fk.
So i want both columns to comunicate with activedir.
If p.e. activedir has 3 elements (a,b,c) when i delete element a then werever FR1 or DC1 have this element(binded to it's id) then the element will also be deleted (id of the element) from both FR1 and DC1
I don't want to set Delete and Update action to none because i want the element changed or deleted from activedir, to do the same on Fr1 or DC1 or both.
Any help?
Thanks.
Hi Folks,
When I try to create a Relationship between two Tables I find the Relationship is always created from the Table I start with to itself.
I seem to have to select a Table to enable the Create Relationship option, and then when I 'Add' it creates a recursive Relationship to that Table.
I guess it must be possible to do what I am trying to do ?
I'd appreciate any advice.
Thanks.
Barry
Hi,
I have metadata that stored my table structure and relationship. I would like to know is it possible to create table relationship programatically? Any sample?
Thank you
I have got a table as follow:
CREATE TABLE [dbo].[NozzleAllTbl] (
[Id] INT IDENTITY (1, 1) NOT NULL,
[nBDId] INT NULL,
[nShellId] INT NULL,
[nTDId] INT NULL,
CONSTRAINT [PK_dbo.NozzleAllTbl] PRIMARY KEY CLUSTERED ([Id] ASC),
CONSTRAINT [FK_dbo.NozzleAllTbl_dbo.NozzleTbl_nBDId] FOREIGN KEY ([nBDId]) REFERENCES [dbo].[NozzleTbl] ([Id]),
CONSTRAINT [FK_dbo.NozzleAllTbl_dbo.NozzleTbl_nShellId] FOREIGN KEY ([nShellId]) REFERENCES [dbo].[NozzleTbl] ([Id]),
CONSTRAINT [FK_dbo.NozzleAllTbl_dbo.NozzleTbl_nTDId] FOREIGN KEY ([nTDId]) REFERENCES [dbo].[NozzleTbl] ([Id])
);
NozzleTbl is another Table.At the moment I am referencing only one NozzleTbl thrice. But how can I reference a collection of NozzleTbl thrice?So that in the c# code I could access the collections as:
NozzleTbl_1 = NozzleAllTbl.NozzleTbls[0]
NozzleTbl_2 = NozzleAllTbl.NozzleTbls[1]
...
NozzleTbl1_1 = NozzleAllTbl.NozzleTbls1[0]
NozzleTbl2_1 = NozzleAllTbl.NozzleTbls2[0]
....
Hi,
I need help in creating a trigger before delete. The trigger should be in such a way that it should display a message, if there is any relationship with other table.
For example I have a table with employee details which have empid as primary key. I have another table with employee salary details where empid is foreign key. The trigger should check the relationship with these two tables. If I try to delete an emploeyee from employee details table and if there is a relationship of that employee with the salary table then the trigger should print a message. If there is no relationship then the trigger should perform the deletion.
I want to create a trigger like this.
Please help!!!!!!!!!!!!!!!!
Hi,
Is it possible to create a relationship between to tables in a deferent databases and How to do it from SQL Server Management Server!?
Thanks.
hi..
I would like to create a view for two tables which have a one-to-many relationship.
Code:
Table: Supplier
Supp_ID
1
Table:Supplier_category
Supp_ID,StockCategoryID
1,56
1,57
1,90
How can i create a view that has columns like below:
Supp_ID, Stock
1,[56,57,90]
Thanks in advance.
Dear sir or madam
I have a problem related to create or show relationship digram in sql server 2005. Especially, I want to show relationship diagram that I established in sqlserver 2000 in sql server 2005 but I can't and I don't how to do it.
I look forward to hearing.
Thank you in advance!
Best regard,
seyha moth
I am fairly new to SQL and I am currently trying to createa SQL table (using Microsoft SQL) that has a recursiverelationship, let me try to explain:I have a piece of Data let's call it "Item" wich may again contain onemore "Items". Now how would I design a set of SQL Tables that arecapable of storing this information?I tried the following two approaches:1.) create a Table "Item" with Column "ItemID" as primary key, somecolums for the Data an Item can store and a Column "ParentItemID". Iset a foreign key for ParentItemID wich links to the primarykey"ItemID" of the same table.2.) create separate Table "Item_ParentItem" that storesItemID-ParentItemID-pairs. Each column has a foreign key linked toprimary key of the "Item" Column "ItemID".In both approaches when I try to delete an Item I get an Exceptionsaying that the DELETE command could not be executed because itviolates a COLUMN REFERENCE constraint. The goal behind these FK_PKrelations is is that when an Item gets deleted, all childItems shouldautomatically be deleted recursively.How is this "standard-problem" usually solved in sql? Or do I inned toimplement the recursive deletion myself using storedprocedures or something ?
View 3 Replies View RelatedI am bring two fact tables and a Date Dim into my Power Pivot model. I try to create relationship where but Power Pivot just reverses them. When I create the relationship I get an info icon warning me it will be changed.Why can't I create a one to many relationship with fact table on many side and dimension on one side?Power Pivot will not let me mark my dimCalnder as a date table either. My date dim has surrogate key, year, month and monthlongname columns only...
View 3 Replies View Relatedi have a table where the feilds are
1.fromtable
2.fromfeild
3.fromcategory
4.totable
5.tofeild
6.tocategory.
i have write a stored proceduer for creating 2 relationship between fromfeild & tofeild from the same value in from category & in tocategory.
there r around 465 records in a table.
so anyone can comeout for solution of this.
hope soon i get a solution for this.
:rolleyes: can u send me deails for establish relationship of tables
i know namw of then but i need them by examples ans defination
as 1 simple
2 complex
3 muliple
u can send me ink if u get fron google also thanks
bye
<----------I
have 2 tables are: 'customers (parent)' and 'open_ac (child)'
<--------I
have tried to insert and update data into sql database by using textboxes
(don't use datagrid)
<--------My
tables details are below
<-------this
table uses for keeping user data
customers
fields:
Column
name
type
length
Description
cu_id
int
4
Primary key
Identifiers
cu_fname
nvarchar
20
allow null
first name
cu_lname
nvarchar
40 allow null
last name
cu_nat
nvarchar
20 allow
null
nationality
cu_add
nvarchar
40 allow null
address
cu_wplace
nvarchar
40 allow
null
workplace
cu_tel
nvarchar
10
allow
null
telephone
cu_fax
nvarchar
10
allow null
fax
cu_email
nvarchar
10 allow
null
email
<----the
open_ac uses for keeping register date/time of customers
open_ac
fields:
Column
name type
length
Description
cu_id
int
4
link key
op_date
date/time
8
register date
<----------my
code
Imports
System.Data.SqlClient
Public Class cus_reg
Inherits System.Web.UI.Page
Dim DS As DataSet
Dim iRec As Integer 'Current Record
Dim m_Error As String = ""
Public Property MyError() As String
Get
Return
m_Error
End Get
Set(ByVal Value As String)
m_Error =
Value
If
Trim(Value) = "" Then
Label3.Visible = False
Else
Label3.Text = Value
Label3.Visible = True
End If
End Set
End Property
Private Sub Page_Load(ByVal sender As System.Object,
ByVal e As System.EventArgs) Handles MyBase.Load
If Not Page.IsPostBack Then
Dim C1 As
New MISSQL
'DS =
C1.GetDataset("select * from customers;select * from open_ac;select * from
accounts")
DS =
C1.GetDataset("select * from customers;select * from open_ac")
Session("data") = DS
iRec = 0
Viewstate("iRec") = iRec
Me.MyDataBind()
Dim Dtr As
DataRow = DS.Tables(0).NewRow
DS.Tables(0).Rows.Add(Dtr)
iRec =
DS.Tables(0).Rows.Count - 1
viewstate("iRec") = iRec
Me.Label2.Text = DateTime.Now
Me.MyDataBind()
Else
DS =
Session("data")
iRec =
ViewState("iRec")
End If
Me.MyError = ""
End Sub
Public Function BindField(ByVal FieldName As String) As
String
Dim DT As DataTable = DS.Tables(0)
Return DT.Rows(iRec)(FieldName)
& ""
End Function
Public Sub MyDataBind()
Label1.Text = "Record : "
& iRec + 1 & " of " & DS.Tables(0).Rows.Count
txtcu_id.DataBind()
txtcu_fname.DataBind()
txtcu_lname.DataBind()
txtcu_add.DataBind()
txtcu_occ.DataBind()
txtcu_wplace.DataBind()
txtcu_nat.DataBind()
txtcu_tel.DataBind()
txtcu_fax.DataBind()
txtcu_email.DataBind()
End Sub
Here is
update code
Private Sub bUpdate_Click(ByVal sender As System.Object,
ByVal e As System.EventArgs) Handles bUpdate.Click
Dim DT As DataTable = DS.Tables(0)
Dim DR As DataRow = DT.Rows(iRec)
'Dim DR1 As DataRow = DT1.Rows(iRec)
If DR.RowState = DataRowState.Added
Then
If
txtcu_id.Text.Trim = "" Then
Me.MyError = "please enter your id"
Exit Sub
End If
DR("cu_id")
= txtcu_id.Text
End If
If txtcu_fname.Text.Trim =
"" Then
Me.MyError =
"please enter your name"
Exit Sub
Else
DR("cu_fname") = txtcu_fname.Text
End If
If txtcu_lname.Text.Trim =
"" Then
Me.MyError =
"please enter your last name"
Exit Sub
Else
DR("cu_lname") = txtcu_lname.Text
End If
If txtcu_add.Text.Trim =
"" Then
Me.MyError =
"please enter your address"
Exit Sub
Else
DR("cu_add") = txtcu_add.Text
End If
If txtcu_occ.Text.Trim =
"" Then
Me.MyError =
"please enter your occupation"
Exit Sub
Else
DR("cu_occ") = txtcu_occ.Text
End If
If txtcu_wplace.Text.Trim =
"" Then
Me.MyError =
"please enter your workplace"
Exit Sub
Else
DR("cu_wplace") = txtcu_wplace.Text
End If
If txtcu_nat.Text.Trim =
"" Then
Me.MyError =
"Please enter your nationality"
Exit Sub
Else
DR("cu_nat") = txtcu_nat.Text
End If
If txtcu_tel.Text.Trim =
"" Then
DR("cu_tel") = DBNull.Value
Else
DR("cu_tel") = txtcu_tel.Text
End If
If txtcu_tel.Text.Trim =
"" Then
DR("cu_fax") = DBNull.Value
Else
DR("cu_fax") = txtcu_fax.Text
End If
If txtcu_email.Text.Trim =
"" Then
DR("cu_email") = DBNull.Value
Else
DR("cu_email") = txtcu_email.Text
End If
Dim Strsql As String
If DR.RowState = DataRowState.Added
Then
Strsql =
"insert into customers (cu_id,cu_fname,cu_lname,cu_add,cu_occ,cu_wplace,cu_nat,cu_tel,cu_fax,cu_email)
values (@P1,@P2,@P3,@P4,@P5,@P6,@P7,@P8,@P9,@P10)"
Else
Strsql =
"update customers set
cu_fname=@P2,cu_lname=@P3,cu_add=@P4,cu_occ=@P5,cu_wplace=@P6,cu_nat=@P7,cu_tel=@P8,cu_fax=@P9,cu_email=@P10
where cu_id =@P1"
End If
Dim C1 As New MISSQL
Dim cmd As SqlCommand =
C1.CreateCommand(Strsql)
C1.CreateParam(cmd,
"ITTTTTTTTT")
cmd.Parameters("@P1").Value = DR("cu_id")
cmd.Parameters("@P2").Value
= DR("cu_fname")
cmd.Parameters("@P3").Value = DR("cu_lname")
cmd.Parameters("@P4").Value = DR("cu_add")
cmd.Parameters("@P5").Value = DR("cu_occ")
cmd.Parameters("@P6").Value = DR("cu_wplace")
cmd.Parameters("@P7").Value
= DR("cu_nat")
cmd.Parameters("@P8").Value = DR("cu_tel")
cmd.Parameters("@P9").Value = DR("cu_fax")
cmd.Parameters("@P10").Value = DR("cu_email")
Dim Y As Integer = C1.Execute(cmd)
If Y > 0 Then
DR.AcceptChanges()
Else
Me.MyError =
"Can not register"
End If
<---------code above in this sub it can update only customers tables and when I tried to coding below<------------it alerts can not update
Dim DT1 As DataTable = DS.Tables(1)
Dim DR1 As DataRow = DT1.Rows(iRec)
If DR1.RowState = DataRowState.Added
Then
If
txtcu_id.Text.Trim = "" Then
Me.MyError = "Please enter id"
Exit Sub
End If
DR1("cu_id")
= txtcu_id.Text
End If
If Label2.Text.Trim = ""
Then
DR1("op_date") = Label2.Text
End If
Dim StrSql1 As String
If DR1.RowState =
DataRowState.Deleted Then
StrSql1 =
"insert into open_ac (cu_id,op_date) values (@P13,@P14)"
Else
StrSql1 =
"update open_ac set op_date=@P14 where cu_id=@P13"
End If
Dim C2 As New MISSQL
Dim cmd2 As SqlCommand =
C2.CreateCommand(StrSql1)
C2.CreateParam(cmd2, "ID")
cmd2.Parameters("@P1").Value = DR1("cu_id")
cmd2.Parameters("@P2").Value = DR1("op_date")
Dim Y1 As Integer = C2.Execute(cmd2)
If Y1 > 0 Then
DR1.AcceptChanges()
Else
Me.MyError =
"Can not register"
End If
End Sub
End Class
<------this
is class I use for connecting to database and call parameters....
MISSQL
class
Imports
System.Data.SqlClient
Public Class MISSQL
Dim PV As String =
"Server=web_proj;uid=sa;pwd=sqldb;"
Dim m_Database As String = "c1_itc"
Public Strcon As String
Public Sub New()
Strcon = PV &
"database=" & m_Database
End Sub
Public Sub New(ByVal DBName As String)
m_Database = DBName
Strcon = PV &
"database=" & m_Database
End Sub
Public Property Database() As String
Get
Return
m_Database
End Get
Set(ByVal Value As String)
m_Database =
Value
Strcon = PV
& "database=" & m_Database
End Set
End Property
Public Function GetDataset(ByVal Strsql As String, _
Optional ByVal DatasetName As String
= "Dataset1", _
Optional ByVal TableName As String =
"Table") As DataSet
Dim DA As New SqlDataAdapter(Strsql,
Strcon)
Dim DS As New DataSet(DatasetName)
Try
DA.Fill(DS,
TableName)
Catch x1 As Exception
Err.Raise(60002, , x1.Message)
End Try
Return DS
End Function
Public Function GetDataTable(ByVal Strsql As String, _
Optional ByVal TableName As
String = "Table") As DataTable
Dim DA As New SqlDataAdapter(Strsql,
Strcon)
Dim DT As New DataTable(TableName)
Try
DA.Fill(DT)
Catch x1 As Exception
Err.Raise(60002, , x1.Message)
End Try
Return DT
End Function
Public Function CreateCommand(ByVal Strsql As String) As
SqlCommand
Dim cmd As New SqlCommand(Strsql)
Return cmd
End Function
Public Function Execute(ByVal Strsql As String) As
Integer
Dim cmd As New SqlCommand(Strsql)
Dim X As Integer = Me.Execute(cmd)
Return X
End Function
Public Function Execute(ByRef Cmd As SqlCommand) As
Integer
Dim Cn As New SqlConnection(Strcon)
Cmd.Connection = Cn
Dim X As Integer
Try
Cn.Open()
X =
Cmd.ExecuteNonQuery()
Catch
X = -1
Finally
Cn.Close()
End Try
Return X
End Function
Public Sub CreateParam(ByRef Cmd As SqlCommand, ByVal
StrType As String)
'T:Text, M:Memo, Y:Currency,
D:Datetime, I:Integer, S:Single, B:Boolean, P: Picture
Dim i As Integer
Dim j As String
For i = 1 To Len(StrType)
j =
UCase(Mid(StrType, i, 1))
Dim P1 As
New SqlParameter
P1.ParameterName = "@P" & i
Select Case
j
Case "T"
P1.SqlDbType = SqlDbType.NVarChar
Case "M"
P1.SqlDbType = SqlDbType.Text
Case "Y"
P1.SqlDbType = SqlDbType.Money
Case "D"
P1.SqlDbType = SqlDbType.DateTime
Case "I"
P1.SqlDbType = SqlDbType.Int
Case "S"
P1.SqlDbType = SqlDbType.Decimal
Case "B"
P1.SqlDbType = SqlDbType.Bit
Case "P"
P1.SqlDbType = SqlDbType.Image
End Select
Cmd.Parameters.Add(P1)
Next
End Sub
End Class
<-------Thank you in advance<-------and Thank you very much for all help
I am running SQL Server CE on Visual Studio 2005.
I have created 2 tables and wish to establish a relationship between the two tables.
However, I could not find a proper way to establish the connection.
Anyone can provide some help on this?
thank you.
I'm building a Search Function with different pull down options. I have 9 Tables and 6 of them are not related.
How do I build an effecient SELECT?
Thanks I'm somewhat new to MSSQL.
Which way to go?
Have two separate programs(Visual Studio-Coded in Visual Basic) that share the same sql 2005 database(called Night Audit)
The tables are called RoomSettlement and Settlement.
They were separate tables and separate programs in the past because it was just exported in excel to a visual integrator that dumped the amounts into our accounting software.
Now needing to combine the two tables(that have no link) on to a single excel sheet daily where the tables have the same date:
Table: Room Settlement – Field: RoomSettlementDatetxt =
Table: Settlement – Field: SettlementDatetxt
Any Ideas on the best way to go? (Link the tables with a foreign key and primary key, maybe union based on date, or any easier suggestion with out having to change a lot of the program )
Room Settlement Table
PrimaryKey
Availabilitytxt
Ratetxt
Othertxt
Arrivalstxt
departurestxt
GuestCounttxt
HotelOccupancytxt
AverageDailyRatetxt
RevPahrtxt
HotelRevenuetxt
ownerrentaltxt
hoteltransienttxt
hotelcorptxt
hotelgovtxt
hotelpacktxt
hotelexpediatxt
hotelsynxistxt
hotelothertxt
rentalpooltxt
employeetxt
Hotelnightstxt
activitestxt
Bonustxt
Golftxt
Tvinternettxt
laundrytxt
misctxt
petstaytxt
Telephonetxt
Purchaseunittxt
Damageincometxt
resalerevtxt
latefeestxt
IntrestIncometxt
Taxestxt
RoomSettlementDatetxt
sitetxt
senddatetxt
Settlement Table:
PrimaryKeyintUn
PreviousGuesttxt
TransferCityLedgertxt
ActivityGuesttxt
transferfromadvdepositguesttxt
transfromguesttoguesttxt
GuestchagefromInctxt
Guestnetcashtxt
Guestcheckstxt
guestpaidout
guestlockboxtxt
guestaetxt
GuestDinerstxt
GuestDiscovertxt
GuestMastercardtxt
GuestVisatxt
guestdepositchangecashtxt
guestnetchangetxt
guestnewbalancetxt
guestsystembalancetxt
guestvariance
PreviousCity
ActivityCitytxt
Citynetcash
Citychecks
citypaidouttxt
citylockboxtxt
cityaetxt
CityDinerstxt
CityDiscovertxt
CityMastercardtxt
CityVisatxt
citydepositchangecashtxt
citynetchangetxt
citynewbalancetxt
citysystembalancetxt
cityvariance
Sitetxt
DateStamptxt
SettlementDatetxt
Thanks JK
Inner join vs outer join:
Inner join will remove rows from the first table if there is not a corresponding row in the 2nd table. Outer join will keep the rows, and use all NULL values to fill in the gaps in the 2nd table.
In general outer joins are more likely to grow a result set and inner joins are more likely to shrink one (though that will depend on the data and the query). For this reason, it is usually better when building complex queries to try to do your inner joins before your outer joins, because then you will keep your result set smaller for longer and thus the later joins can run faster.
I am relatively new to MS SQL (not a novice, but hardly a master).
I am working on a content management application for a magazine publisher. It’s written in ASP (VB Script) and being created on Dreamweaver 8 with an MS SQL 2000 database. Now, I’m trying to decide the best and fastest approach to coding a complex INSERT and UPDATE function. My question isn’t as much about the SQL (although that will probably come up after I decide how to do this), but about the procedural steps and approach I shuold be taking to do this.
Reporters will use an online form to enter their story into the system. It collects the usual data: Headline, byline, story content, and the story category (feature, opinion, entertainment, business, sports, etc.). Each story may belong to MULTIPLE categories (feature & business, for example).
So, I’ve created three tables to support this many-to-many realtionship:
Story
Category
StoryCat (a junction table with the IDs from both the other tables).
The online form has a dropdown menu which pulls the available categories from the Categories table. When the reporter has entered the data I use ASP to performs the insert just as you would expect it to.
The next step needs to be to update the StoryCat table so that it creates a new record with the StoryID of the record it just inserted, along with the CategoryID that was in that record.
------------------
As I said, I’m not sure of the best way to do this.
Should I just pull back the last record inserted and then create a procedure that would insert into the StoryCat table (which is what I’m thinking of doing on the confirmation page), or is there another approach I should take (perhaps some sort of temporary table or stored procedure?).
Any and all help will be greatly appreciated.
Hi!
It's been always said that it is best to put index on commonly joined fields in the table. But putting too much index on the table would cause the table to be slow on insert and update.
My question is, how do you deal with your fields that uses look up tables? Like for example for these fields
- CountryID(smallint)
- CreatedBy(int)
- ModifiedBy(int)
- Status(tinyint)
Those fields don't come a big part in the table, though when I query the table I always join them with their respective primary table to get their respective text value. Do I still need to put Index & FK relationships to these fields?
What fields are normally good candidates for index or fk relationships?
Thanks,
Enzo