ForeignKey Reference With Another Database

Apr 29, 2008



Hi All,

I have a table [Vendor] in first database SAMPLEDB. And i have another table [Contract] in another database TESTDB. I need a foreign key in the [Contract] table (which is in TESTDB database) is reference to the other table [Vendor] which is present in the SAMPLEDB.
When i try to creating them through SQL, i received the following error message :

Msg 1763, Level 16, State 0, Line 1
Cross-database foreign key references are not supported. Foreign key 'SAMPLEDB..VENDOR'.


Is that possible to refer foreign key in external database or not?
If possible, please give solution for this. If not possible, please suggest some alternate way.


Thanks in Advance

View 8 Replies


ADVERTISEMENT

Get All Foreignkey Contraints

Sep 28, 2007



Hi, I want know how can I to build a query to get all the foreignkey contrains exist between tables using the sys tables, for example if the user select this two tables:

dbo.cat_states -> with this fields -> id_state & desc
dbo.cat_universities -> with this fields -> id_state, id_university & desc_university

I want get something like this:

dbo.universities.id_state = dbo.states.id_state


tks 4 help
Leo

View 1 Replies View Related

How To Get Tablename / ForeignKey And Constraints

Sep 19, 2013

I have written the Query in which i am getting TableName,Columns,Precision but how can i get Table related Foreign key and Constraints

SELECT DISTINCT
QUOTENAME(SCHEMA_NAME(tb.[schema_id])) AS 'Schema',
QUOTENAME(OBJECT_NAME(tb.[OBJECT_ID])) AS 'Table',
C.NAME AS 'Column',
T.NAME AS 'DataType',
C.max_length,
C.is_nullable,
c.precision,
c.scale

[code]...

View 4 Replies View Related

ForeignKey Using INFORMATION_SCHEMA.TABLES As PrimaryKeyTable

Oct 12, 2006

Hi,

I have a "master" table that holds the names of data tables (one record in the "master" table for each "data" table).

Can I create a ForeignKey constraint that will prevent the "master" table records from being removed if the cooresponding "data" table exists? Is the way to do this to use INFORMATION_SCHEMA.TABLES as the PrimaryKeyTable for the ForeignKey?

Thanks!

View 1 Replies View Related

ForeignKey Constraint From A Lookup Table

Aug 5, 2007

Hi,

I'm trying to figure out how to setup a Constraint (Foreign Key?) from a lookup table.

For the following example, I want to constrain to constrain so that a Car.ParkingSpotNumber is unique for a given ParkingFacility.

Any thoughts?

Thanks!

Andy

Here's the scenario:
CAR table:
Id (PrimaryKey)
LicensePlateNumber
ParkingSpotNumber

PARKINGFACILITY__CAR__LOOKUP table:
Id (PrimaryKey)
CarId (Foreign Key to Car.Id)
ParkingFacilityId (Foreign Key to ParkingFacility)

PARKINGFACILITY table:
Id (PrimaryKey)

PARKINGLOT table:
Id (Foreign Key to ParkingFacility.Id) (PrimaryKey)
Address

PARKINGGARAGE table:
Id (Foreign Key to ParkingFacility.Id) (PrimaryKey)
Address


Here's the T-SQL code to make a SQLServer2005 database for this scenario:


SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Car]') AND type in (N'U'))

BEGIN

CREATE TABLE [dbo].[Car](

[Id] [bigint] NOT NULL,

[CustomerName] [nvarchar](50) NOT NULL,

[ParkingSpotNumber] [int] NOT NULL,

CONSTRAINT [PK_Customer] 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]

END

GO

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[ParkingFacility]') AND type in (N'U'))

BEGIN

CREATE TABLE [dbo].[ParkingFacility](

[Id] [bigint] NOT NULL,

CONSTRAINT [PK_ParkingFacility] 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]

END

GO

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[ParkingFacility__To__Car_Lookup]') AND type in (N'U'))

BEGIN

CREATE TABLE [dbo].[ParkingFacility__To__Car_Lookup](

[Id] [bigint] NOT NULL,

[CarId] [bigint] NOT NULL,

[ParkingFacilityId] [bigint] NOT NULL,

CONSTRAINT [PK_ParkingFacility__To__Car_Lookup] 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]

END

GO

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[ParkingLot]') AND type in (N'U'))

BEGIN

CREATE TABLE [dbo].[ParkingLot](

[Id] [bigint] NOT NULL,

[Address] [nvarchar](50) NOT NULL,

CONSTRAINT [PK_ParkingLot] 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]

END

GO

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[ParkingGarage]') AND type in (N'U'))

BEGIN

CREATE TABLE [dbo].[ParkingGarage](

[Id] [bigint] NOT NULL,

[Address] [nvarchar](50) NOT NULL,

CONSTRAINT [PK_ParkingGarage] 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]

END

GO

IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_ParkingFacility__To__Car_Lookup_Car]') AND parent_object_id = OBJECT_ID(N'[dbo].[ParkingFacility__To__Car_Lookup]'))

ALTER TABLE [dbo].[ParkingFacility__To__Car_Lookup] WITH CHECK ADD CONSTRAINT [FK_ParkingFacility__To__Car_Lookup_Car] FOREIGN KEY([CarId])

REFERENCES [dbo].[Car] ([Id])

GO

ALTER TABLE [dbo].[ParkingFacility__To__Car_Lookup] CHECK CONSTRAINT [FK_ParkingFacility__To__Car_Lookup_Car]

GO

IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_ParkingFacility__To__Car_Lookup_ParkingFacility]') AND parent_object_id = OBJECT_ID(N'[dbo].[ParkingFacility__To__Car_Lookup]'))

ALTER TABLE [dbo].[ParkingFacility__To__Car_Lookup] WITH CHECK ADD CONSTRAINT [FK_ParkingFacility__To__Car_Lookup_ParkingFacility] FOREIGN KEY([ParkingFacilityId])

REFERENCES [dbo].[ParkingFacility] ([Id])

GO

ALTER TABLE [dbo].[ParkingFacility__To__Car_Lookup] CHECK CONSTRAINT [FK_ParkingFacility__To__Car_Lookup_ParkingFacility]

GO

IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_ParkingLot_ParkingFacility]') AND parent_object_id = OBJECT_ID(N'[dbo].[ParkingLot]'))

ALTER TABLE [dbo].[ParkingLot] WITH CHECK ADD CONSTRAINT [FK_ParkingLot_ParkingFacility] FOREIGN KEY([Id])

REFERENCES [dbo].[ParkingFacility] ([Id])

GO

ALTER TABLE [dbo].[ParkingLot] CHECK CONSTRAINT [FK_ParkingLot_ParkingFacility]

GO

IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_ParkingGarage_ParkingFacility]') AND parent_object_id = OBJECT_ID(N'[dbo].[ParkingGarage]'))

ALTER TABLE [dbo].[ParkingGarage] WITH CHECK ADD CONSTRAINT [FK_ParkingGarage_ParkingFacility] FOREIGN KEY([Id])

REFERENCES [dbo].[ParkingFacility] ([Id])

GO

ALTER TABLE [dbo].[ParkingGarage] CHECK CONSTRAINT [FK_ParkingGarage_ParkingFacility]

View 4 Replies View Related

Overriding ForeignKey Constraints Under Certain Conditions To Perform CASCADE DELETE

Jun 5, 2008

Hi,
We have a DB with a heirarchy of tables each connected via Guids and controlled for Delete Operations by Foreign Key Constraints. However, under certain privileged conditions we would like CERTAIN users to be able to perform a Cascade Delete all the way down.
Thus by example we have tables A,B,C,D - Typically once a record is inserted into Table A then we can add multiple records in Table B referencing that new record in Table A...and so on through records in Table C referencing Table B...records in Table D referencing table C. Now when we try and delete that ONE Record in Table A we 'could' implement CASCADE DELETES which would delete all those records in Table B,C,D.
We decided that was too dangerous to implement as Whooosh....all records could be deleted by a non-privileged user deleteing that ONE records in Table A, so we changed the Cascade DELETE to NO ACTION. However, there are times (hah...particularly in Testing !) when we would like a privilegd user to be able to DO that task...i.e. perform a CASCADE delete.
Has anybody got any suggestions on how this would be best, easily and securely implemented.
I have had a brief look at INSTEAD OF Triggers but am not sure about the pitfalls of using this...
ANy help appreciated,
 Cheers,
Desmond. 
 
 
 

View 2 Replies View Related

How Do I Reference A Table In One Database From A SQL Script Running In Another Database?

Jan 5, 2007

I am construvcting a number of databases, some of which containsensitive data and most of which do not. I am attempting to handle thesecurity issues involved in protecting sensitive data in part byputting it in its own database. If the sensitive data is in a databasecalled d_SensitiveData, and in that database there is a table called't_A' (I know, not very informative, but this is only a triviallysimple example :-), and I have a script running in a database'NotSensitiveData' (i.e. there is a statement at the beginning of thescript "USE NotSensitiveData"), how do I referenece the primary key intable 'A'?I tried a variety of things like:ALTER TABLE t_nsXADD CONSTRAINT FK_PersonID FOREIGN KEY (p_idPerson)REFERENCES SensitiveData.t_A (p_idPerson);The above is, in fact, my latest attempt. Everything I tried hasfailed precisely at the point where I specify a table in a differentdatabase. So what is the trick to refering to a table in one databasewhen using another database?This will be used in a ASP.NET v3 application where one of the things Iwant to do is have the authentication provider be a different databasefrom the one used for the main application data.Does anyone know of an example I can download from the web that doesthe same sort of thing I want to try, with some discussion of securityissues involved (i.e. what I can do to harden the application and dataserver)?NB: I am an application developer, not a DBA nor a systemadministrator.ThanksTed

View 6 Replies View Related

SQL 2012 :: DTSX Giving Errors - Object Reference Not Set To Instance Reference

Sep 10, 2014

I am using vs 2010 to write my dtsx import scripts.I use a script component as a source to create a flat file destination file.Everything have been working fine,but then my development machine crashed and we have to install everything again.Now when i use the execute package utility to test my scripts i get the following error:

Error system.NullReferenceException: Object refrence not set to an instance reference.

In PreExecute section
TextReader = new system.io.streamreader(" file name")
In the CreateNewOutputRows:
dim nextLine as string
nextLine = textReader.ReadLine

[code]...

is there something which i did not install or what can be the error?

View 0 Replies View Related

Any Way To Parameterize A Database Reference??

Mar 13, 2004

I need to refer to a second database from within the database that I login to. Can any one help me with how to abstract this reference? I would like to be able to change a parameter somewhere (i don't really care where) and have it effect any Stored Porcedure which references the second database.

View 1 Replies View Related

How To Reference A Table In A Different Database?

Apr 5, 2006

I have 2 databases in sql server. let us say database1 = db1 and database2 = db2
Now both the databases have a same table called table1 with the same fields.

IF data in db1.table1 is updated then data in db2.table2 should be updated automatically.

There are many ways we can do this. one way is to create a INSERT trigger on db1.table1.

But i would like to avoid trigger
Is there something in SQL server where I can just link table1 of db1 to db2 and delete the table1 in db2. That means db2 is using the same table that of db1.

Thanks

View 2 Replies View Related

How To Make Database For Reference

Jun 9, 2014

I am making a project where I have to add city, agentname, rankofagent, referenceagent name the problem is that who can I make a table where suppose I add member a,b,c,d,e,f,g,h,i,j,k,l,m,n,o,p like that

b is joined by a
c is joined by b
d by c, e by d and so on,

What I want here is that how can I get the result suppose i want to see that chain of p. I want result to be printed as a,b,c,d,e,f,g,h,i,j,k,l,m,n,o either result should be in horizontal form or vertical. with or without comma. I am doing this in SQL....

View 2 Replies View Related

How To Reference Items From Different Database?

Jul 20, 2005

Hi all,I have two databases DB_External and DB_Internal.I am writing some stored procedures (in DB_Internal) that reads fromthe tables in DB_External. I execute my stored procedures fromDB_Internal.Everytime I want to read the tables in DB_External, I have to refer tothe table as DB_External.dbo.tableName.Is there a better way of declaring the database DB_External up frontin the stored procedure so I don't have to pre-fix "DB_External.dbo."in all the table names?Thanks in advance,June Moore.

View 1 Replies View Related

Invalid Object Name When Trying To Reference A Database

Mar 19, 2006

Hi There
Let me start by explaining what I am trying to achieve as there may be a better way to perform this. I have two databases and I want to refer to four tables - one in one database and three in the other in a sql select statement.
When I use the query builder in VWD I can only see tables for the database that is defined in the connection string - so therefore how do I add tables from another database? After reading a few posts I realized that all I have to do is use the syntax: database.owner.table (or [database].[owner].[table]). Unfortunately this didn't work and came up with the error "Invalid Object Name".
Assuming that I must have got the owner incorrect I used the Query Tool in SQL Server Express Edition and it worked perfectly! So my sql must be correct, but why will it not work inside VWD?
The two databases are named assessment and aspnetdb - the assessment database contains the table reviews and the aspnetdb database contains the tables TutorDetails, EmployerDetails and StudentDetails.
Here is the connection string and sql:
Data Source=.SQLEXPRESS;AttachDbFilename=|DataDirectory|Assessment.mdf;Integrated Security=True;User Instance=True
SELECT Reviews.Programme, aspnetdb.dbo.TutorDetails.Tutor_DisplayName AS [Tutor Name],
aspnetdb.dbo.EmployerDetails.DisplayName AS [Employer Name], aspnetdb.dbo.StudentDetails.Student_DisplayName AS [Student Name]
FROM Reviews INNER JOIN
aspnetdb.dbo.StudentDetails ON Reviews.StudentID = aspnetdb.dbo.StudentDetails.Username INNER JOIN
aspnetdb.dbo.EmployerDetails ON Reviews.EmployerID = aspnetdb.dbo.EmployerDetails.UserName INNER JOIN
aspnetdb.dbo.TutorDetails ON Reviews.TutorID = aspnetdb.dbo.TutorDetails.UserName
WHERE (Reviews.ReviewID = 15)
Disregard the value of '15' in the WHERE statement as this was just put in to ensure I got some valid data back, this will be replaced with a variable eventually.
I know the sql statement works properly, I think the problem may be something to do with either the connection string - any ideas would be much appreciated.
Regards
Stuart

View 7 Replies View Related

How Do You Reference A Table In Another (SQL) Database In TSQL

Jan 9, 2001

Good afternoon one and all,

I am trying to write a query that will use tables in more than one SQL database (on the same server). How do refer to the table? I have tried dbasename.tablename.fieldname but I don't think that's working.

TIA for any and all help

Gurmi

View 3 Replies View Related

Trying To Reference Another Database In Update Command On Same SQL Server

Aug 15, 2006

I'm trying to update a table in MyDB1 with a value in MyDB2 on the same SQL server (2000)

     UPDATE MyDB1.dbo.Users
     SET MyDB1.dbo.Users.InstantASP_UserID = MyDB2.dbo.InstantASP_Users.UserID
     FROM MyDB2
     INNER JOIN dbo.aspnet_Membership a ON a.UserId = MyDB1.dbo.Users.UserID
     WHERE MyDB2.dbo.InstantASP_Users.EmailAddress = a.Email

I don't want to have to create a linked server.  It's on the same box.

I'm getting Invalid object name 'MyDB2'.

View 1 Replies View Related

Connecting To Database - Object Reference Not Set Error Message

Feb 11, 2005

I've got code that on my own machine (visual studio .net 2003 and iis and sql server all exist on this box) works great. I display my test page, which loads a dropdownlist on page_load. This works great on my own machine.

When I move the code to our dev environment. Web server and database servers on separate boxes. I get the error "Object reference not set to an instance of an object" when i try to open the connection to the database. I thought it had to do with permissions and I check all through sql server and everything looks great. I then thought it was the connection string. I still haven't ruled this out, but I wrote a quick vbscript app to attach to the database and try to pull a record. The vbscript worked fine. So, the code works fine on my computer, but not on the web server. The connection does work with vbscript, but through my asp.net web app I get the "object reference" error.

All code below...

ASP.NET webpage:
private void Page_Load(object sender, System.EventArgs e)
{

if( !IsPostBack )
{
// Create a command object for the query
string mySelectQuery = "select id from vw_abcd order by ID";

SqlCommand myCommand = new SqlCommand(mySelectQuery,sqlConnection1);
sqlConnection1.Open();

DropDownList1.DataSource = myCommand.ExecuteReader(CommandBehavior.CloseConnection);
DropDownList1.DataBind();
sqlConnection1.Close();

RangeValidator1.MinimumValue = "1";
RangeValidator1.MaximumValue = DropDownList1.Items.Count.ToString();
}
}

Connection string on my machine:
workstation id=wXXXXXX01;packet size=4096;user id=testuser;password=testpass;integrated security=SSPI;data source=wXXXXXX01;persist security info=False;initial catalog=TestDB"/>

Connection string on web server:
network library=DBMSSOCN;data source=xxx.xx.xxx.xxxinstance1;user id=testuser;password=testpass;initial catalog=TestDB


vbscript:
Dim connConnection, rstresultset
strConnectionString = "driver={Sql Server};server=xxx.xx.xxx.xxxinstance1;user id=testuser;password=testpass;database=TestDB"

Set connConnection = CreateObject("ADODB.Connection")

connConnection.Open strConnectionString
Set rstresultset = createobject("ADODB.Recordset")

rstresultset.ActiveConnection = connConnection

rstresultset.Open "select id, comment from testtable", connConnection

msgbox rstresultset(0)
msgbox rstresultset.Fields(0).Value



Error message and stack trace:
Object reference not set to an instance of an object.

at System.Data.SqlClient.ConnectionPool.GetConnection(Boolean& isInTransaction)
at System.Data.SqlClient.SqlConnectionPoolManager.GetPooledConnection(SqlConnectionString options, Boolean& isInTransaction)
at System.Data.SqlClient.SqlConnection.Open()
at Web2.Drilldown.Page_Load(object sender, System.EventArgs e)




Any ideas what's going on?????

View 1 Replies View Related

SQL 2012 :: Unable To Add Service Reference In A CLR Database Project?

Apr 17, 2014

I created a CLR procedure with a service reference in Visual Studio 2010 that is being used in SQL Server 2008 R2. We recently tried to deploy it to a 2012 SQL Server, but were unable to due to compatibility issues. When I tried to upgrade the package to Visual Studio 2013 I got this error message:

Unable to cast object of type 'Microsoft.VisualStudio.Data.Tools.Package.Project.DatabaseFileNode' to type 'Microsoft.VisualStudio.Data.Tools.Package.Project.Internal.FolderNode'

I tried to create this using the 2012 Data Tools, but was unable to add a service reference with the GUI. It appears Microsoft has removed this functionality. My company has a third party app that we have limited control over, which is why we need this functionality.

[URL]

View 0 Replies View Related

SQL 2012 :: Suppress SSDT Database Reference Error

Aug 21, 2015

In database projects, VS2015, I want to create some views for me database that will reference another database table using 3 part naming reference.

Works fine in SSMS but when I try and build me project I is throwing up a reference error.

I can't import the other database into this project, so is there a way to suppress the error? I don't really want to exclude these view from the project.

View 5 Replies View Related

Transact SQL :: Database Constraint Check On Table With Reference Key

Jun 11, 2015

CREATE TABLE PRODUCT_SALES
(
Salesmanid BIGINT,
Productid BIGINT
)

INSERT INTO PRODUCT_SALES (Salesmanid,Productid) VALUES (1,1)
INSERT INTO PRODUCT_SALES (Salesmanid,Productid) VALUES (1,2)
INSERT INTO PRODUCT_SALES (Salesmanid,Productid) VALUES (1,3)

SELECT * FROM PRODUCT_SALES

/* SalesmanID is reference key from Sales Master and ProductID is reference key from Product Master. How should i restrict user through DB with any constraint check, if user tries to enter

INSERT INTO PRODUCT_SALES (Salesmanid,Productid) VALUES (1,2),

It should throw error , if possible user defined message would be more useful.

View 7 Replies View Related

Is A Foreignkey Column Can Refer Two Primarykey Column From Two Different Tables?

May 13, 2008

Hi,


I need to create a table (Named as C) with a foreignkey column. That column should references with a primarykey column in table A and a primarykey column in table B. Is this possible?

Thanks in advance.

ramesh.p

View 1 Replies View Related

Server Error: Object Reference Not Set To An Instance Of An Object. Trying To Upload Image In Database

Dec 17, 2007

Does any one has any clue for this error ? I did went through a lot of articles on this error but none helped . I am working in Visual studie 2005 and trying to upload image in sql database through a simple form. Here is the code
 
using System;
using System.Data;
using System.Configuration;
using System.Collections;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Data.SqlClient;
using System.Web.Configuration;
using System.IO;
public partial class Binary_frmUpload : System.Web.UI.Page
{protected void Page_Load(object sender, EventArgs e)
{
}protected void btnUpload_Click(object sender, EventArgs e)
{if (FileUpload.HasFile == false)
{
// No file uploaded!lblUploadDetails.Text = "Please first select a file to upload...";
}
else
{string str1 = FileUpload.PostedFile.FileName;
 string str2 = FileUpload.PostedFile.ContentType; string connectionString = WebConfigurationManager.ConnectionStrings["GSGA"].ConnectionString;
//Initialize SQL Server Connection SqlConnection con = new SqlConnection(connectionString);
//Set insert query string qry = "insert into Officers (Picture,PictureType ,PicttureTitle) values(@ImageData, @PictureType, @PictureTitle)";
//Initialize SqlCommand object for insert. SqlCommand cmd = new SqlCommand(qry, con);
//We are passing Original Image Path and Image byte data as sql parameters. cmd.Parameters.Add(new SqlParameter("@PictureTitle", str1));
cmd.Parameters.Add(new SqlParameter("@PictureType", str2));Stream imgStream = FileUpload.PostedFile.InputStream;
int imgLen = FileUpload.PostedFile.ContentLength;byte[] ImageBytes = new byte[imgLen]; cmd.Parameters.Add(new SqlParameter("@ImageData", ImageBytes));
//Open connection and execute insert query.
con.Open();
cmd.ExecuteNonQuery();
con.Close(); //Close form and return to list or images.
 
}
}
}
 
Object reference not set to an instance of an object.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. Exception Details: System.NullReferenceException: Object reference not set to an instance of an object.Source Error:



Line 32:
Line 33: string str2 = FileUpload.PostedFile.ContentType;
Line 34: string connectionString = WebConfigurationManager.ConnectionStrings["GSGA"].ConnectionString;
Line 35:
Line 36: //Initialize SQL Server Connection Source File: c:UsersManojDocumentsVisual Studio 2005WebSitesGSGABinaryfrmUpload.aspx.cs    Line: 34  
Stack Trace:




[NullReferenceException: Object reference not set to an instance of an object.]
Binary_frmUpload.btnUpload_Click(Object sender, EventArgs e) in c:UsersManojDocumentsVisual Studio 2005WebSitesGSGABinaryfrmUpload.aspx.cs:34
System.Web.UI.WebControls.Button.OnClick(EventArgs e) +105
System.Web.UI.WebControls.Button.RaisePostBackEvent(String eventArgument) +107
System.Web.UI.WebControls.Button.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument) +7
System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument) +11
System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData) +33
System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +5102

View 2 Replies View Related

Object Reference Not Set To An Instance Of An Object (Inserting Data Into Database)

Dec 28, 2004

Each time I press submit to insert data into the database I receive the following message. I use the same code on another page and it works fine. Here is the error:


Object reference not set to an instance of an object.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.NullReferenceException: Object reference not set to an instance of an object.

Source Error:


Line 125: MyCommand.Parameters("@Balance").Value = txtBalance.Text
Line 126:
Line 127: MyCommand.Connection.Open()
Line 128:
Line 129: Try


Source File: c:inetpubwwwrootCreditRepairCreditor_Default.aspx.vb Line: 127

Stack Trace:


[NullReferenceException: Object reference not set to an instance of an object.]
CreditRepair.CreditRepair.Vb.Creditor_Default.btnSaveAdd_Click(Object sender, EventArgs e) in c:inetpubwwwrootCreditRepairCreditor_Default.aspx.vb:127
System.Web.UI.WebControls.Button.OnClick(EventArgs e)
System.Web.UI.WebControls.Button.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument)
System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument)
System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData)
System.Web.UI.Page.ProcessRequestMain()



Private Sub btnSave_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSave.Click

If (Page.IsValid) Then

Dim DS As DataSet
Dim MyCommand As SqlCommand

Dim AddAccount As String = "insert into AccountDetails (Account_ID, Report_ID, Balance) values (@Account_ID, @Report_ID, @Balance)"

MyCommand = New SqlCommand(AddAccount, MyConnection)

MyCommand.Parameters.Add(New SqlParameter("@Account_ID", SqlDbType.Char, 50))
MyCommand.Parameters("@Account_ID").Value = txtAccount_ID.Text

MyCommand.Parameters.Add(New SqlParameter("@Report_ID", SqlDbType.Char, 50))
MyCommand.Parameters("@Report_ID").Value = txtReport_ID.Text

MyCommand.Parameters.Add(New SqlParameter("@Balance", SqlDbType.Char, 50))
MyCommand.Parameters("@Balance").Value = txtBalance.Text

MyCommand.Connection.Open()

MyCommand.ExecuteNonQuery()

Response.Redirect("Customer_Details.aspx?SS='CustHeadGrid.Columns[0].Item.lblSS.Text)")

MyCommand.Connection.Close()
End If

View 2 Replies View Related

Add New Database Error Object Reference Not Set To An Instance Of An Object

Oct 18, 2006

Hi,

I'm new to SQL database.

When I add a new item to project in VS2005: Project-->Add New Item --> Sql Database I got a error msg "object reference not set to an instance of an object" after click "Add" button. The SQL Server Express is running. How do I fix this problem?

Any help would be appreceiated.

CO22006

View 4 Replies View Related

T-SQL Reference?

Feb 6, 2004

Hi y'all

Does anybody know reference or pdf file or free e-learning on the web?

View 1 Replies View Related

Reference

Jun 18, 2001

DECLARE Loan_cursor CURSOR FOR
SELECT Loan_No,store
FROM loan
WHERE maturity_date > '2001-04-30' and loan_no like 'ABL%'

OPEN Loan_cursor

-- Perform the first fetch.
FETCH NEXT FROM Loan_cursor

-- Check @@FETCH_STATUS to see if there are any more rows to fetch.
WHILE @@FETCH_STATUS = 0
BEGIN
Declare @LoanNo varchar(12)
** Set @LoanNo = Loan_No

-- This is executed as long as the previous fetch succeeds.
FETCH NEXT FROM Loan_cursor
END

CLOSE Loan_cursor
DEALLOCATE Loan_cursor


when l run the cursor l get the error
Server: Msg 207, Level 16, State 3, Line 15
Invalid column name 'Loan_No'.
.
If l reference it as Set @LoanNo = LoanTable.Loan_No l get the error

Server: Msg 107, Level 16, State 3, Line 15
The column prefix 'loan' does not match with a table name or alias name used in the query.
All l'm trying to do is to compare the loan number that l get from the cursor to the value in the loans table. Maybe reference is a better word

View 1 Replies View Related

Old Sp Reference

Jun 6, 2008

Hi friends,

Just i modified one sub stored procedures which is not getting affected in the main stored procedures

Create procedure main
as
begin
set nocount on

--First sp
exec Data_transfer_sp

--Second sp
Exec Clearance_sp

set nocount off
end

In data_transfer_sp,i have commented the select statement
but still iam use to the select result while execution the main sp.
Note:I have compiled the data_transfer_sp after making comment
.Txs in advance

View 3 Replies View Related

Reference CTE More Than Once In A SP

Sep 11, 2007



Hi All ...

WITH myCTE (x,y,z) AS (SELECT x,y,z, from myTable)

SELECT x,y,sum(z) from myCTE

SELECT y,z,sum(x) from myCTE

the second SELECT fails, and says invalid object name. does the CTE go out of scope after i reference it once?

never mind the semantics of what I am SELECTing, I just want to be able to reference the CTE more than once in my SP

am I trying to use the CTE in a way that was not intended?

View 5 Replies View Related

Reference CTE More Than Once In A SP

Sep 11, 2007



Hi All ...

WITH myCTE (x,y,z) AS (SELECT x,y,z, from myTable)

SELECT x,y,sum(z) from myCTE

SELECT y,z,sum(x) from myCTE

the second SELECT fails, and says invalid object name. does the CTE go out of scope after i reference it once?

never mind the semantics of what I am SELECTing, I just want to be able to reference the CTW more than once in my SP

am I trying to use the CTE in a way that was not intended?

View 1 Replies View Related

How To Get Reference Of .net Dll

Oct 15, 2007

hi ,

can any body help me that how can i use my .net dll into my rdl file.
thanks

Gorla

View 1 Replies View Related

The Reference

Feb 3, 2008

in this code ,whtat is the references of
RelationalDataSourceView
and also
what are the references of these?

OleDbConnection

OleDbCommand

OleDbDataAdapter

View 1 Replies View Related

Object Reference Not Set

Nov 16, 2006

Can anyone see why I would get the 'Object Referenece not set to an instance of an object error in the following code?
It happens on this line:
MyAdapter.SelectCommand = New SqlCommand(SelectStatement, MyConnection) 'Populate the text boxes from database for alumni fields.
Dim MyAdapter As SqlDataAdapter
Dim MyCommandBuilder As SqlCommandBuilder
Dim DetailsDS As DataSet
Dim Row As DataRow
Dim SelectStatement As String = "Select ClientID,ClassYear,HouseName,CampusName,EducationMajor,GraduationDate FROM tblclient Where [ClientID]=" & _
ClientIDSent
Dim ConnectStr As String = _
ConfigurationManager.ConnectionStrings("TestConnectionString").ConnectionString
Dim MyConnection As SqlConnection = New SqlConnection(ConnectStr)
MyAdapter.SelectCommand = New SqlCommand(SelectStatement, MyConnection)
MyCommandBuilder = New SqlCommandBuilder(MyAdapter)
MyAdapter.Fill(DetailsDS, "tblClient")

Row = DetailsDS.Tables("tblClient").Rows(0)
ClassYearText.Text = Row.Item("Classyear").ToString()
HouseNameText.Text = Row.Item("HouseName").ToString()
CampusNameText.Text = Row.Item("CampusName").ToString()
EducationMajorText.Text = Row.Item("EducationMajor").ToString()
GraduationDateText.Text = Row.Item("GraduationDate").ToString() 

View 2 Replies View Related

SQL Reference Online?

Jan 21, 2004

Hi,

I'm looking for a good reference guide online as I am more used to mysql (and stil quite limited vocab at that)

I create table outside of a database by accident and I'm now looking for the sql syntaxt for moving tables, but I can't find it anywhere?

most simple guides don't seem to provide the syntax to do this. :(

View 11 Replies View Related

Reference Material

Feb 23, 2004

does any one know any good pages to get some sql commands
and explainations thanks

View 2 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved