Database Discussion Boards

Re: Insert into a table that has Foreign Keys

ShabRaza13-Sep-09 23:36

13-Sep-09 23:36

Yes that is true. I have created a query that displays the category name and supplier name, i did this because the FK_CategoryName and FK_SupplierName in the Products table are auto numbers and would not mean anything to the customer. I have linked the Products table, LK_Categories, LK_Suppliers with the ID's however i want to display the category names and supplier names to the user.

Then when the user wants to add a product item i want it to insert a line in the Products table, but where i am going wrong is that the FK_CategoryID and FK_SupplierID are foreign keys in the Products table are that is not referenced in my SQL INSERT statement.

I hope you understand my point.
I am desperate to get this working, please help.

Re: Insert into a table that has Foreign Keys

PIEBALDconsult14-Sep-09 7:04

PIEBALDconsult

14-Sep-09 7:04

Before you insert the product you need to translate the category and supplier to get their IDs.

Re: Insert into a table that has Foreign Keys

Ashfield14-Sep-09 10:03

Ashfield

14-Sep-09 10:03

What you have done to display the nformation is correct, but what you need to do id translate the supplier and category names back to their ID values to insert into the product table. YOU have to do the lookup and supply the correct values for the insert.

Personally I would be doing the insert in a stored procedure and would look up any FK values in the stored proc.

Hopefully this makes sense

Bob
Ashfield Consultants Ltd
Proud to be a 2009 Code Project MVP

which is better when inserting data in sql database

neodeaths13-Sep-09 6:16

neodeaths

13-Sep-09 6:16

hi i was just wondering which is the better way to insert data into database when i got large amount of data to add in.

way 1
do
"insert into db1 () values()"
execute command
while()

or

way 2
do
string = string + "insert into db1 () values()"
while()
execute command

also if lets say way 2 somewhere in the middle of the insert command there is an error would the code before it still be added?

Re: which is better when inserting data in sql database

PIEBALDconsult13-Sep-09 6:56

PIEBALDconsult

13-Sep-09 6:56

The first is better, but neither is as good as:

cmd.CommandText = "INSERT INTO table ( ... ) VALUES ( @Param0 ... )" ;
 
(create and add parameters to cmd)
 
while ( ... )
{
    (set parameter values)
 
    cmd.ExecuteNonQuery() ;
}

And use a transaction if you need to.

Re: which is better when inserting data in sql database

εїзεїзεїз13-Sep-09 7:46

εїзεїзεїз

13-Sep-09 7:46

"in the middle of the insert command there is an error would the code before it still be added?"

You can use sqlTransaction , and can rollback and commit transactions as well as can make commit points

Best Of Regards,
SOFTDEV
If you have knowledge, let others light their candles at it

Re: which is better when inserting data in sql database

N a v a n e e t h13-Sep-09 8:23

N a v a n e e t h

13-Sep-09 8:23

Best way to see which is performing fast is to profile the application. I will go with the first method with the modifications what PIEBALDconsult suggested.

Second method would be good in performance but it is hard to troubleshoot when you get error in one of the inserts.

neodeaths wrote:
also if lets say way 2 somewhere in the middle of the insert command there is an error would the code before it still be added?
Quote Selected Text

It will be added. If you want to ensure all the data gets added, run the queries in a transaction.

Navaneeth

How to use google | Ask smart questions

Differences between SqlServer Express and MySql

Henry Minute13-Sep-09 5:26

Henry Minute

13-Sep-09 5:26

This is not a question, but feel free to reply if you have any opinion about the subject.

I have often seen the subject of this message as a question, here on CP, and having just found this article/blog (link below), I thought that I would post it here in case it is of use to someone.

If you follow the link, take the time to read the comments at the bottom as some of the views differ from those of the author.

Here it is.[^]

Henry Minute

Do not read medical books! You could die of a misprint. - Mark Twain
Girl: (staring) "Why do you need an icy cucumber?"
“I want to report a fraud. The government is lying to us all.”

Re: Differences between SqlServer Express and MySql

εїзεїзεїз13-Sep-09 6:26

εїзεїзεїз

13-Sep-09 6:26

In my opinion both Pinal Dave (well i really appreciates his efforts He is not doubt a SQL Guru ) and on the other hand users are right on their respective end.

i will also use Sql server if i am in Microsoft env. and tools while will def. go for mysql in case of php and linux/unix platform

Best Of Regards,
SOFTDEV
If you have knowledge, let others light their candles at it

Re: Differences between SqlServer Express and MySql

Henry Minute13-Sep-09 7:41

Henry Minute

13-Sep-09 7:41

softdev_sup wrote:
i will also use Sql server if i am in Microsoft env. and tools while will def. go for mysql in case of php and linux/unix platform

I think that that is the right way to think about it too. Smile | :)

Creating a report selecting information from two databases on the same server

mlemon1413-Sep-09 3:06

mlemon14

13-Sep-09 3:06

Does anyone know how I can create a report on reporting services that selects information from two databases on the same sql server?

Any help would be grateful

Michael

Re: Creating a report selecting information from two databases on the same server

Eddy Vluggen13-Sep-09 3:56

Eddy Vluggen

13-Sep-09 3:56

Create a linked server[^] Smile | :)

I are Troll Smile | :)

Re: Creating a report selecting information from two databases on the same server

Md. Marufuzzaman13-Sep-09 4:46

Md. Marufuzzaman

13-Sep-09 4:46

If both database are is same server then why use linked server.. Confused | :confused:

I will not say I have failed 1000 times; I will say that I have discovered 1000 ways that can cause failure – Thomas Edison.

Don't forget to click [Vote] / [Good Answer] on the post(s) that helped you.

Thanks
Md. Marufuzzaman

Re: Creating a report selecting information from two databases on the same server

Eddy Vluggen13-Sep-09 7:08

Eddy Vluggen

13-Sep-09 7:08

Aw, my aplogies, there's no need to create a linked server. You can just prefix the entities that you need to denote the database;

SELECT *
  FROM [database].[schema].[table]

E.g.;

SELECT *
  FROM Northwind.dbo.Employees

I are Troll Smile | :)

Re: Creating a report selecting information from two databases on the same server

εїзεїзεїз13-Sep-09 6:09

εїзεїзεїз

13-Sep-09 6:09

check the following link http://msdn.microsoft.com/en-us/library/ms345292.aspx[^]

or

SQL Server 2000 Reporting Services: Creating a report from scratch[^]

or

Creating Reports with SQL Reporting Service and Visual Studio .NET[^]

and can also use/try schema.db.dbo.tbl name

Best Of Regards,
SOFTDEV
If you have knowledge, let others light their candles at it

problem with sql server 2008 - cant change field type

E_Gold12-Sep-09 22:19

E_Gold

12-Sep-09 22:19

hi
i have table that i want to change any field type (from int - to - varchar)

i get this error:

saving changes is not permitted........

Re: problem with sql server 2008 - cant change field type

Md. Marufuzzaman13-Sep-09 4:38

Md. Marufuzzaman

13-Sep-09 4:38

Probably you get this error while you alter your table in design mode.. If so then you need to change setting form option... set allow auto generate sql script true.. If this issue still not resolve then please let me know... Smile | :)

I will not say I have failed 1000 times; I will say that I have discovered 1000 ways that can cause failure – Thomas Edison.

Don't forget to click [Vote] / [Good Answer] on the post(s) that helped you.

Thanks
Md. Marufuzzaman

Re: problem with sql server 2008 - cant change field type

E_Gold13-Sep-09 9:21

E_Gold

13-Sep-09 9:21

thank's for the help
yes, i work in design mode.
but, where i can find this ...option... set allow auto generate sql script true ?

Re: problem with sql server 2008 - cant change field type

εїзεїзεїз13-Sep-09 5:59

εїзεїзεїз

13-Sep-09 5:59

what are the data field sizes you are using to convert the field type you can use script as

GO
BEGIN
ALTER TABLE tbl ALTER COLUMN [fld] [varchar](18, 8) NOT NULL
END
GO

or create another field as varchar and update your data of numeric fld to it and then drop old column
you can also use casting

Best Of Regards,
SOFTDEV
If you have knowledge, let others light their candles at it

Regaring Establishing Connection with sqlserver.

dayakar_dn12-Sep-09 8:35

dayakar_dn

12-Sep-09 8:35

Hi All,

I am getting a strange error while establishing connection with sqlserver.

i written the following code to establish the connection
string sqlStatement = "select * from Student_Table";
String dataBaseConnection = "Data Source=DREDDY9/SQLEXPRESS;Initial Catalog=SampleDataBase;Integrated Security=SSPI";
SqlConnection myConnection = new SqlConnection(dataBaseConnection);

SqlCommand myCommand = new SqlCommand(sqlStatement, myConnection);
SqlDataReader dr;

myConnection.Open();
dr = myCommand.ExecuteReader();
if(dr!=null)
{
Response.Write("Successfully Executed");
}
myConnection.Close();

To resolve this i tried with enabling local and remote connections and i enabled sql server browser.

but no use
any body is having any solution. please shere it.

Re: Regaring Establishing Connection with sqlserver.

dan!sh 12-Sep-09 8:56

dan!sh

12-Sep-09 8:56

What is the error?

It's not necessary to be so stupid, either, but people manage it. - Christian Graus, 2009 AD

Re: Regaring Establishing Connection with sqlserver.

εїзεїзεїз12-Sep-09 10:20

εїзεїзεїз

12-Sep-09 10:20

what kind of error you are facing are you enable to connect if so then try

Data Source=./SQLEXPRESS  instead off Data Source=DREDDY9/SQLEXPRESS

also check your protocols you are connecting with tcp/ip or named pipes and the user from which you are going to connect should have rights to connect to the db.

Best Of Regards,
SOFTDEV
If you have knowledge, let others light their candles at it

Re: Regaring Establishing Connection with sqlserver.

εїзεїзεїз12-Sep-09 12:00

εїзεїзεїз

12-Sep-09 12:00

look out for slash it should be \ not /

Data Source=DREDDY9/SQLEXPRESS;

Data Source=DREDDY9\SQLEXPRESS; D'Oh! | :doh:

Best Of Regards,
SOFTDEV
If you have knowledge, let others light their candles at it

Whats wrong with this query

AndreFratelli12-Sep-09 7:47

AndreFratelli

12-Sep-09 7:47

I have the following table (ccpoc)

poc_cod varchar(10)
poc_cod_asc varchar(10)
poc_desc varchar(40)

With the following data

poc_cod poc_cod_asc poc_desc
1           12         Coiso
1           13         Coisa
1           14         Coisinhas

And I'm trying the following query

UPDATE ccpoc SET poc_cod='133', poc_cod_asc='13', poc_desc='Coiso 133' WHERE poc_cod='13' AND poc_desc='Coiso 13' AND poc_cod_asc='1'

I'm querying the server with an ExecuteNonQuery in C#, and everything seems to go fine. No errors, no exceptions, everything runs well. The problem is that data in the table does not change!

Anybody has any idea what might be happening?

Best regards

Fratelli

Re: Whats wrong with this query

dan!sh 12-Sep-09 7:58

dan!sh

12-Sep-09 7:58

I guess you should give a try to these:

1. Run SQL profiler to see what query is actually run on your table.
2. Run a select statement with same where clause to check if there are any rows matching the criteria.
3. If you are using transactions, check if you have committed it or not.
4. Does the same query when run through Management studio works?

It's not necessary to be so stupid, either, but people manage it. - Christian Graus, 2009 AD

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.