sql server and visual studio - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

sql server and visual studio

Author

Topic

sebastian11c
Posting Yak Master

129 Posts

Posted - 2011-10-31 : 20:36:41

ihve got a project in vs 2010, en ive got a list of items to chose an option from a dropdownlist
example

information )

do you have
options (dropdownlist)
1. grandfather 0
1
2

2. grandmother 0
1
2
(send button)

this is an example
if i choose

1. grandfather 0
2. grandmother 2

in my table of my database sql server 2008 the information will save this

column
id item selection
1 grandfather 0
2 grandmother 2

but i need your help because i want that if i choose the option(0) in dropdownlist the information wont be save in the table , like this

column
id item selection

1 grandmother 2

could you help me to do some restriction ( now th egranfather item doesnt appear

thanks a lot

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-01 : 04:32:22

sorry you explanation doesnt make any sense. please provide required information in below format

http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

sebastian11c
Posting Yak Master

129 Posts

Posted - 2011-11-01 : 10:43:21

1. State the question

if i insert a specific string 'Na' in a specific row in a table ; how can i do to dont save that information ??

2. Please post the DDL of your tables (Including Indexes, and constraints)
CREATE TABLE [dbo].[example1](
[id] [int] IDENTITY(1,1) NOT NULL,
[item] [varchar](50) NULL,
[selection] [varchar](50) NULL
) ON [PRIMARY]

GO

GO3. Post some sample data in the form of DML

" the selection values come from a asp.net code a drop down list with this options ('na', 'excellent', 'good, 'bad')
INSERT INTO example1(item , selection)
VALUES ('door', 'excellent')
INSERT INTO example1(item , selection)
VALUES ('bed', 'good')
INSERT INTO example1(item , selection)
VALUES ('bath', 'na')

currently ive got this in my table example1

select * from example1

id item selection
1 door excellent
2 bed good
3 bath na

----5. Post the expected results

but id like when the selection ='na' the entire data row wont be saved
like this ( row number 3 doesnt appear , beacuse selection = 'na'

select * from example1
id item selection
1 door excellent
2 bed good

how can i do that

i really need your help, thanks in advanced

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-01 : 11:40:52

sounds like this


declare @yourstring varchar(100)
set @yourstring = 'na'
select * from example1 where selection <> @yourstring

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

sebastian11c
Posting Yak Master

129 Posts

Posted - 2011-11-01 : 11:56:54

i get what you say,
but I want is to have a clause that does not allow information to be saved if i try to insert in a selection = 'na',
that if I run an insert where selection = 'na' the insert statement is not completed and continue to the next item

example

INSERT INTO example1(item , selection)
VALUES ('door', 'excellent')
INSERT INTO example1(item , selection)
VALUES ('bed', 'na')
INSERT INTO example1(item , selection)
VALUES ('bath', 'na')
INSERT INTO example1(item , selection)
VALUES ('spoon', 'good')

id like my result be this ( bath 'na' and bed 'na' dont exist , didnt be saved)

id item selection
1 door excellent
2 spoon good

thanks in advanced, you are very patient and kind

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-01 : 12:00:13

oh ok. then what you want is instead of insert trigger like this


CREATE TRIGGER InsertValidate_trigger
ON example1
INSTEAD OF INSERT
AS
BEGIN
INSERT INTO example1
SELECT columns
FROM INSERTED
WHERE selection <> 'na'
END

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

sebastian11c
Posting Yak Master

129 Posts

Posted - 2011-11-01 : 12:21:01

hi
i paste the query and execute

CREATE TRIGGER InsertValidate_trigger
ON example1
INSTEAD OF INSERT
AS
BEGIN
INSERT INTO example1
SELECT columns
FROM INSERTED
WHERE selection <> 'na'
END

and i have these errors
Msg 207, Level 16, State 1, Procedure InsertValidate_trigger, Line 7
Invalid column name 'columns'.
Msg 213, Level 16, State 1, Procedure InsertValidate_trigger, Line 6
Column name or number of supplied values does not match table definition

im sorry im a really amateur on sql, could you say me whats wrong?

thanks again

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-01 : 12:23:20

quote:
Originally posted by sebastian11c

hi
i paste the query and execute

CREATE TRIGGER InsertValidate_trigger
ON example1
INSTEAD OF INSERT
AS
BEGIN
INSERT INTO example1
SELECT columns
FROM INSERTED
WHERE selection <> 'na'
END

and i have these errors
Msg 207, Level 16, State 1, Procedure InsertValidate_trigger, Line 7
Invalid column name 'columns'.
Msg 213, Level 16, State 1, Procedure InsertValidate_trigger, Line 6
Column name or number of supplied values does not match table definition

im sorry im a really amateur on sql, could you say me whats wrong?

thanks again

you need to put actual column names there. i just put a place holder there for you

as I dont know full columns present in yourtable

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

sebastian11c
Posting Yak Master

129 Posts

Posted - 2011-11-01 : 12:36:06

ooo that works

you are a genius

thanks a lot

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-01 : 12:37:47

quote:
Originally posted by sebastian11c

ooo that works

you are a genius

thanks a lot

welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2011-11-01 : 13:37:38

Depending on your goal it might make more sense to put a check constraint on that column.

sebastian11c
Posting Yak Master

129 Posts

Posted - 2011-11-01 : 14:24:18

hi lamprey,

im interested in everything i could learn,

could you explain to me what are you talking about check constarint and give me an example

i ll really apreciate your help

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-01 : 14:39:41

quote:
Originally posted by sebastian11c

hi lamprey,

im interested in everything i could learn,

could you explain to me what are you talking about check constarint and give me an example

i ll really apreciate your help

what he was suggesting was something like this


ALTER TABLE example1 ADD CONSTRAINT Chk_Data CHECK( selection <> 'na')

it will also avoid addition of any data with na value in selection

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

sebastian11c
Posting Yak Master

129 Posts

Posted - 2011-11-01 : 15:00:34

well done again visakh16
thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-02 : 05:45:25

wc

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

sebastian11c
Posting Yak Master

129 Posts

Posted - 2011-11-03 : 09:41:18

hi visakh16
i need youur help one more time,
1. State the question

I have to update a table based on a weekly excel file
how can i update the table without inserting duplicate information and updating the state if the info exist in the table and doesnt exist anymore in the excel file ??
i know , the first step is impot the excel file to a table in sql
please follow the example below

2. Please post the DDL of your tables

**principal table
CREATE TABLE [dbo].[bill](
[idbill] [int] NULL,
[name] [nchar](10) NULL,
[debt] [int] NULL,
[state] [varchar](50) NULL
) ON [PRIMARY]

GO

EXAMPLE

IN MY PRINCIPAL TABLE IVE GOT THIS

idbill name debt State
11 peter 1000 open
16 samanta 1200 open
18 nicolas 800 open
23 jhon 200 open
32 sean 5000 open

FOR EXAMPLE , IN MY WEEKLY EXCEL IMPORT TABLE , IVE GOT THIS
(check that nicolas and sean dont appear anymore, and have 3 new rows (arnold, andy , michael)
idbill name debt state
11 peter 1000 open
16 samanta 1200 open
23 jhon 200 open
33 ARNOLD 10000 open
47 andy 7000 open
53 michael 3000 open

Expécted Result
I NEED MY PRINCIPAL TABLE UPATED THIS WAY

idbill name debt state
11 peter 1000 open
16 samanta 1200 open
18 nicolas 800 CLOSED
23 jhon 200 open
32 sean 5000 CLOSED
33 ARNOLD 10000 open
47 andy 7000 open
53 michael 3000 open

THAT MEANS
I NEED TO INSERT NEW DATA IN MY PRINCIPAL TABLE FROM MY WEEKLY EXCEL TABLE WITHOUT DUPLICATE INFORMATION, AND I NEED TO UPDATE THE STATE IN MY PRINCIPAL TABLE FROM TTHE WEEKLY EXCEL TABLE OF THE INFO THAT DONT APPEAR ANYMORE ( LIKE NICOLAS AND SEAN WHICH STATE WAS UPDATED FROM OPEN TO CLOSED)

THESE ARE AN EXAMPLE TABLE BUT MY REAL TABLES HAVE SEVERAL ROWS THOUSANDS of them

im thinking running a loop statement , but i dont know how to do it
or any other idea its cool

your help will be appreciated

thanks in advanced

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-03 : 10:38:47

use OPENROWSET to connect to excel

see

http://www.mssqltips.com/sqlservertip/1202/export-data-from-sql-server-to-excel/

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

sebastian11c
Posting Yak Master

129 Posts

Posted - 2011-11-03 : 10:52:53

hi

its simple to import excel tables to sql with export and import data in sql server 2008 r2, ive already done

my problem is,

EXAMPLE

IN MY PRINCIPAL TABLE "bills" IVE GOT THIS (this is an existing table in sql )

idbill name debt State
11 peter 1000 open
16 samanta 1200 open
18 nicolas 800 open
23 jhon 200 open
32 sean 5000 open

FOR EXAMPLE , IN MY WEEKLY EXCEL IMPORT TABLE "weekly" ( ive alreadi imported to sql), IVE GOT THIS
(check that nicolas and sean dont appear anymore, and have 3 new rows (arnold, andy , michael)
idbill name debt state
11 peter 1000 open
16 samanta 1200 open
23 jhon 200 open
33 ARNOLD 10000 open
47 andy 7000 open
53 michael 3000 open

Expécted Result
I NEED MY PRINCIPAL TABLE "bills" UPATED THIS WAY

idbill name debt state
11 peter 1000 open
16 samanta 1200 open
18 nicolas 800 CLOSED
23 jhon 200 open
32 sean 5000 CLOSED
33 ARNOLD 10000 open
47 andy 7000 open
53 michael 3000 open

THAT MEANS
I NEED TO INSERT NEW DATA IN MY PRINCIPAL TABLE "bills" FROM MY WEEKLY EXCEL TABLE "weekly" WITHOUT DUPLICATE INFORMATION, AND I NEED TO UPDATE THE STATE IN MY PRINCIPAL TABLE FROM THE TABLE "weekly" OF THE INFO THAT DONT APPEAR ANYMORE ( LIKE NICOLAS AND SEAN WHICH STATE WAS UPDATED FROM OPEN TO CLOSED)

THESE ARE AN EXAMPLE TABLE BUT MY REAL TABLES HAVE SEVERAL ROWS THOUSANDS of them

im thinking running a loop statement , but i dont know how to do it
or any other idea its cool

your help will be appreciated

thanks in advanced

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-03 : 11:01:44

nope. just use OPENROWSET and excel will be considered like a table
then you can use LEFT JOIN and check for NULLs to identify new data and INNER JOIN to identify existing data

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

sebastian11c
Posting Yak Master

129 Posts

Posted - 2011-11-03 : 11:25:25

lets put in other way

i have 2 differents tables "bill" and "Check" i need to compare these 2 tables and update one of them
and thats what i need

EXAMPLE

table "bill" ( columns : idbill, name, debt, state)

idbill name debt State
11 peter 1000 open
16 samanta 1200 open
18 nicolas 800 open
23 jhon 200 open
32 Sean 5000 open

and the other table "Check" ( columns : idbill, name, debt, state)

(check that nicolas and sean dont appear anymore, and have 3 new rows (arnold, andy , michael)
idbill name debt state
11 peter 1000 open
16 samanta 1200 open
23 jhon 200 open
33 ARNOLD 10000 open
47 andy 7000 open
53 michael 3000 open

Expécted Result
after comparing 2 tables i need the TABLE "bill" will be UPATED THIS WAY

idbill name debt state
11 peter 1000 open
16 samanta 1200 open
18 nicolas 800 CLOSED
23 jhon 200 open
32 sean 5000 CLOSED
33 ARNOLD 10000 open
47 andy 7000 open
53 michael 3000 open

THAT MEANS
I NEED TO INSERT NEW DATA IN TABLE "bill" FROM TABLE "Check" WITHOUT DUPLICATE INFORMATION, AND I NEED TO UPDATE THE STATE IN MY TABLE "bill" FROM THE TABLE "Check" OF THE INFO THAT DONT APPEAR ANYMORE ( LIKE NICOLAS AND SEAN WHICH STATE WAS UPDATED FROM OPEN TO CLOSED in table "bill")

THESE ARE AN EXAMPLE TABLE BUT MY REAL TABLES HAVE SEVERAL ROWS THOUSANDS of them

im thinking running a loop statement , but i dont know how to do it
or any other idea its cool

your help will be appreciated

thanks in advanced
sorry if im not very clear

thanks again

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-03 : 11:47:52

[code]
insert into bill
select c.columns...
from check c
left join bill b
on b.idbill = c.idbill
where b.idbill is null

update b
set b.state='closed'
from bill b
left join check c
on c.idbill = b.idbill
where c.idbill is null
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources