Implement Logic For Update Or Insert

May 1, 2006

I have a stored procedure that I need to either perform an update if a record exists or an insert if the record doesn't exist.

Here is my procedure:

CREATE PROCEDURE dbo.Insert_Temp_ContactInfo

@sessionid varchar(50),
@FirstName varchar(50),
@LastName varchar(50),
@SchoolName varchar(50),
@address varchar(50),
@City varchar(50),
@State int,
@Zip varchar(5),
@Phone varchar(10),
@Email varchar(50),
@CurrentCustomer varchar(3),
@ImplementationType int,
@ProductType int = null,
@Comment varchar(500)

AS

--check if a current record exists

SET NOCOUNT ON

SELECT FirstName, LastName, SchoolName, Address, City, State, Zip, Phone, Email, CurrentCustomer, ImplementationType, ProductType, Comment
FROM dbo.Temp_ContactInfo
WHERE sessionid = @sessionid


SET NOCOUNT OFF


--if exists update the record

UPDATE dbo.Temp_ContactInfo
SET
FirstName = @FirstName,
LastName = @LastName,
SchoolName = @SchoolName,
Address = @address,
City = @City,
State = @State,
Zip = @Zip,
Phone = @Phone,
Email = @Email,
CurrentCustomer = @CurrentCustomer,
ImplementationType = @ImplementationType,
ProductType = @ProductType,
Comment = @Comment
WHERE sessionid = @sessionid

--if the record does not exist, then insert a new record

INSERT INTO dbo.Temp_ContactInfo (sessionid, FirstName, LastName, SchoolName, address, City, State, Zip, Phone, Email, CurrentCustomer, ImplementationType, ProductType, Comment)
VALUES (@sessionid, @FirstName, @LastName, @SchoolName, @address, @City, @State, @Zip, @Phone, @Email, @CurrentCustomer, @ImplementationType, @ProductType, @Comment)

GO

I think I can use an if statement. Can anyone help me out with this?

Thanks.

View 3 Replies


ADVERTISEMENT

How To Implement The For Loop Logic In Sql Server Reporting Services

Dec 17, 2007

Hi All,

This is the code for calculating a formula field in Crystal Reports.
I want to implement the same in Sql Server Reporting Services..
But it doesn't have the feature of For Loop....
The strings started with @ are formula fields....
Can anyone tell me, how can the below code be implemented in Sql Server Reporting Services

numbervar YR;
for YR := 1 to {@CalcFiscalAge} step 1 do
(
IF YR = {@CalcLifeCode} +1 then
locFactor1 := 1;
exit for;
IF (YR = 1 OR YR = {@CalcFiscalAge}) THEN
HALF_YEAR := 2
ELSE
HALF_YEAR := 1 ;
LINEAR := ROUND(REM/ ({@CalcLifeCode}-YR+1.5)/HALF_YEAR,4);
MACR := ROUND(REM / {@CalcLifeCode}*{@CalcSRate}/HALF_YEAR,4);
IF MACR >= LINEAR then
DEP := MACR
ELSE
DEP := LINEAR;
locFactor1 := locFactor1 + DEP ;
REM := 1 - locFactor1;
locFactor := locFactor1;
);



Thanks in advance

Regards,

View 3 Replies View Related

Is There A Way We Can Implement Business Logic By Storing It In Colums Of A Table And Calling It?

Jul 20, 2005

What is the way we could implement a business logic from a Table bystoring it statemnnets in a colums and defining an execute sql toexecute it.Some legal requirements make it diffcult for us to createmodify stored procdures so Iwant to have a process where we create newrows in a table and execute it to execute business logic.All views are welcome.Havin g one table two tables different approaches to store ststementsand execute them....Case logic how to implement it?Flags in the tble colums in the tablesetcThanksAjay Garg

View 1 Replies View Related

Data Access :: Bulk Fetch Records And Insert / Update Same In Other Table With Some Business Logic

Apr 21, 2015

I am currently working with C and SQL Server 2012. My requirement is to Bulk fetch the records and Insert/Update the same in the other table with some  business logic? How do i do this?

View 14 Replies View Related

How To Implement T-SQL Update Command Using Special Characters

Mar 24, 2008

Hi!
I wanted to execute an update command in T-SQL using some special characters like single quote ('). when i try with this it fails to accept the command. For example the text which i wanted to update is something like this "<Choice index="3" value="Don't know"/>", here i have a single quote('), less/greater than signs (<>), double quotes and forward slash (/). when i try with this type of text it fails to update, the field data type is NChar. I need help on how to successfully execute with this type of data.

Need help.


Thanks In Advance

Devloper
Anil Kumar Dwivedi

View 8 Replies View Related

Too Busy To Implement A Trigger? Nor A Conditional Update Query?

Dec 20, 2004

Hi:

In a very busy SQL2000 enterprise edition server with 8GB memory and 6 cpus sp3, I could not install a update trigger, unless all the appl connections are dropped. For this 24 HR running svr, could do it.

then I try to run a query as follows:

if exists (select rfABC.* A
from rfABC inner join remoteSvr.XYZDB.dbo.vwIP L
on A.Address = L.address and
A.metro <> L.metro
begin
print ' ---- Yes metroID <> LAMetro, start job exec.... -----'

insert into tempCatchMetroIDGPRS
select rfABC.*, metro, getdate()
from rfABC inner join remoteSvr.XYZDB.dbo.vwIP L on rfABC.Address = L.address and rfABC.metro <> L.metro

update rfABC A
set A.metro = L.metro
from rfABC A inner join remoteSvr.XYZDB.dbo.vwIP L on A.Address = L.address and A.metro <> L.metro
end
else
begin
print ' ---- no metroID <> LAMetro, skip job exec.... -----'
end

------------------------------
this query hang there could not execute. When I took off the if ... else condition, it run with like 0 second. Wondered if a 'busy' (which updates the IP address continueously) could cause above issues...

thanks
David

View 4 Replies View Related

How To Implement Alter Database Implement Restrictions On SQL2K ?

Dec 28, 2007

Hi Guyz

it is taken from SQL2K5 SP2 readme.txt. Anyone have idea what to do to implement this ?
Our sp2 is failing. we suspect the above problem and researching it.we are running on default instance of SQL2K5 on win2003 ent sp2

"When you apply SP2, Setup upgrades system databases. If you have implemented restrictions on the ALTER DATABASE syntax, this upgrade may fail. Restrictions to ALTER DATABASE may include the following:

Explicitly denying the ALTER DATABASE statement.


A data definition language (DDL) trigger on ALTER DATABASE that rolls back the transaction containing the ALTER DATABASE statement.


If you have restrictions on ALTER DATABASE, and Setup fails to upgrade system databases to SP2, you must disable these restrictions and then re-run Setup."

thanks in advance.

View 4 Replies View Related

Update Trigger Logic

Nov 8, 2004

Posts: 20
Joined: Aug 2003
Mon November 08, 2004 9:42 AM



I have a table where from 1 to 3 fields will be update (lastname,firstname,birthdate). Any combination of the 3 can be updated. I need a trigger to handle the updates which update some other db tables. I can handle the update trigger part, the ? is how to best approach the logic. I can do thee separate statements to handle an update for each of the columns, but that could cause 3 separate updates to fire. I was looking at doing something like

If UPDATE(last_name)
and UPDATE(first_name)
and update(date_of_birth)
BEGIN
...
end
else if
If UPDATE(last_name)
and UPDATE(first_name)
begin
...
end

View 1 Replies View Related

Logic On UPDATE Query

Jan 25, 2006

I am dealing with two tables and I am trying to take one column from a table and match the records with another table and append the data of that column.

I used an update query that looks like this:

UPDATE Acct_table Set Acct_table.Score =
(Select Score_tbl.Score from Score_tbl
Where Acct_table.Acctnb = Score_tbl.Acctnb

This process has been running for over an hour and a half and is building a large log file. I am curious to know if there is a better command that I can use in order to join the tables and then just drop the column from one to the other. Both tables are indexed on Acctnb.

Any insight would truly help.
Thanks!

View 4 Replies View Related

Update Logic In Warehouse ...

Feb 5, 2008

Hi,

I am facing a small problem while updating the warehouse using SSIS package.

Here is an sample scenario.
I have a staging table T1 with some important columns as stateid,ename etc ..
and target table as Trg with only two columns stateid and statecnt.
The Rule is, each state will be sending an excel file which basically contains state and employees belonging to the state information.
For the first time when i execute the SSIS package, i need to load statetid and count_of_employees for that particular state.
say for example.

SELECT stateid,
count(ename)
from T1
where stateid = ?

For the next time if the same state information is received then we need to update the count to the existing record in the Dataware house rather than creating a brand new record once again.

I am little bit confused of implementig this logic.
Can anyone help me out in accomplishing this task.
What can done or what steps is involved in achiving the task.

Thanks in advance.

View 2 Replies View Related

Update Statement Logic

May 9, 2008

Hi guys

We have a table X with a gender column taking values M(male) or F(Female).There are 52 rows in the table X.We want to update all the rows with Gender M as F and update all remaining F as males (M) in a single Update Query.Im looking for the exact logic we would write in the single update statement.
Please help me out.

Thanks in advance

Regards
ARvind L

View 2 Replies View Related

Please Help With Complex Update Statement Logic

Nov 8, 2006

hi.I am having probelms with an update statement. every timei run it, "every" row updates, not just the one(s) intended.so, here is what i have. i have tried this with both AND and ORand neither seem to work.i dont know why this is elluding me, but i'd appreciate help with thesolution.thanks.UPDATE addSET add_s = 1WHERE add.add_status = 0 and add.add_email = 'mags23@rice.edu'or add_s in(SELECT a.add_sFROM add a, edit eWHERE a.email_address = e.email_addressand e.public_name = 'professor')

View 22 Replies View Related

Please Need Rescue- Complex Update Logic Swap

May 14, 2008

please need rescue- complex update logic
this is my table







1

2

3

4

5

EMPID
fld1
fld11
fld111
fld2
fld22
fld222
fld3
fld33
fld4
fld44

fld444
fld5
fld55
fld555


















1111

A

B

C

7

8

9

G

H

I

J

K

L

M

N


















2222

N

M

L

K

J

I

H

G

F

E

D

C

B

A


















3333

1

2

3

A

B

C

C

E

Y

I

O

W

Y

P




















i need to update for example the eployee 1111 with employee 3333
but with swap ( take the value of employee 1111 in field- fld2,fld22,fld222 and swap value between employee 3333
in field- fld2,fld22,fld222 )



Code Snippet
---update eployee 1111 with employee 3333
-so
if i put the value 2
than ------------------ swap value between 2 employee
set empid1= 1111
set empid2=3333
value_swap=2
if value_swap=2
than
update fld2,fld22,fld222
with fld2,fld22,fld222
------------------- take the value of employee 1111 in field- fld2,fld22,fld222 and swap value between employee 3333
--------------------in field- fld2,fld22,fld222








value_swap

=1

=2

=3

=4

=5

EMPID
fld1
fld11
fld111
fld2
fld22
fld222
fld3
fld33
fld4
fld44

fld444
fld5
fld55
fld555


















1111

A

B

C

A

B

C

G

H

I

J

K

L

M

N


















2222

N

M

L

K

J

I

H

G

F

E

D

C

B

A


















3333

1

2

3

7

8

9

C

E

Y

I

O

W

Y

P



















Code Snippet
---update eployee 2222 with employee 1111
-so
if i put the value 5
than ------------------ swap value between 2 employees
set empid1= 1111
set empid2=2222
value_swap=5
if value_swap=5
than
update fld5,fld55,fld555
with fld5,fld55,fld555
------------------- take the value of employee 1111 in field- fld5,fld55,fld555 and swap value between employee 3333
--------------------in field- fld5,fld55,fld555













=1

=2

=3

=4

=5

EMPID
fld1
fld11
fld111
fld2
fld22
fld222
fld3
fld33
fld4
fld44

fld444
fld5
fld55
fld555


















1111

A

B

C

7

8

9

G

H

I

J

K

W

Y

P


















2222

N

M

L

K

J

I

H

G

F

E

D

C

B

A


















3333

1

2

3

A

B

C

C

E

Y

I

O

L

M

N















TNX FOR ALL THE HELP I GET IN THIS Forum

View 7 Replies View Related

Having Major Problems With My Insert Query Logic

Aug 20, 2004

I have a perl program that is looping through a hash of a hash. I need to Update any existing records but also insert any new records in the table using collected data in the hash.

Life would be very simple if it was possible to use a Where Clause in an Insert statement but not does not work.

Here is some example code from my program:
sub Test{
foreach my $table(keys %$HoH){
foreach my $field(keys %{$HoH->{$table}}){
if($table eq "CPU"){
my $CPUstatement = "INSERT INTO CPU(CPUNumber, Name, MaxClockSpeed, SystemNetName)
Values ('$field',
'$HoH->{CPU}{$field}{Name}',
'$HoH->{CPU}{$field}{MaxClockSpeed}' ,
'$HoH->{Host}{SystemNetName}')";
print "$CPUstatement";
if ($db->Sql($CPUstatement))
{
print "Error on SQL Statement";
Win32::ODBC::DumpError();
}
else
{
print "successful";
}
}
}


}
}

Thanks,
Laura

View 5 Replies View Related

Merge Replication – Bug In Business Logic Update Handler

Nov 17, 2006

I have got a business logic update conflict handler working, but I have had to work round what appears to be a bug.

Please can someone confirm if this is indeed a bug €“ and if it is a known bug?

My conflict handler needs to take some columns from the publisher row and some from the subscriber row in the event of conflict.

I can quite happily generate a custom dataset which contains the winning row that I want €“ I can see that because I can step through the conflict handler with debug when a conflict occurs.

However, just returning ActionOnUpdateConflict.AcceptCustomConflictData from the UpdateConflictsHandler method does not set the publisher and subscriber columns correctly. I end up with different values on the two databases.

I have found that the only way to get the correct rows on both publisher and subscriber is to create a new ADO connection to the publisher and actually perform an update €“ updating all the modified columns. This now works reliably in my testing.

Fortunately, due to business rules the frequency of update conflicts are likely to be very infrequent, but I would very much like to avoid having to do the €˜unnecessary€™ update.

Notes:

I am using column level tracking €“ but I have seen the problem with row level tracking too
I have mainly been using SP1 but have repeated the test on a configuration using the SP2 CTP €“ and the problem occurs there too
The problem is not due to complex logic in my code. If the method just sets customDataSet = publisherDataSet.Copy and then returns ActionOnUpdateConflict.AcceptCustomConflictData, the changed and winning publisher values are not sent to the subscriber

Any thoughts would be much appreciated

View 5 Replies View Related

SQL Server 2012 :: Replacing CASE Statement In Update With Table-driven Logic

Oct 20, 2014

I have a stored proc that contains an update which utilizes a case statement to populate values in a particular column in a table, based on values found in other columns within the same table. The existing update looks like this (object names and values have been changed to protect the innocent):

UPDATE dbo.target_table
set target_column =
case
when source_column_1= 'ABC'then 'XYZ'
when source_column_2= '123'then 'PDQ'

[Code] ....

The powers that be would like to replace this case statement with some sort of table-driven structure, so that the mapping rules defined above can be maintained in the database by the business owner, rather than having it embedded in code and thus requiring developer intervention to perform changes/additions to the rules.

The rules defined in the case statement are in a pre-defined sequence which reflects the order of precedence in which the rules are to be applied (in other words, if a matching value in source_column_1 is found, this trumps a conflicting matching value in source_column_2, etc). A case statement handles this nicely, of course, because the case statement will stop when it finds the first "hit" amongst the WHEN clauses, testing each in the order in which they are coded in the proc logic.

What I'm struggling with is how to replicate this using a lookup table of some sort and joins from the target table to the lookup to replace the above case statement. I'm thinking that I would need a lookup table that has column name/value pairings, with a sequence number on each row that designates the row's placement in the precedence hierarchy. I'd then join to the lookup table somehow based on column names and values and return the match with the lowest sequence number, or something to that effect.

View 9 Replies View Related

SQL Server 2008 :: Update Null Enabled Field Without Interfering With Rest Of INSERT / UPDATE

Apr 16, 2015

If I have a table with 1 or more Nullable fields and I want to make sure that when an INSERT or UPDATE occurs and one or more of these fields are left to NULL either explicitly or implicitly is there I can set these to non-null values without interfering with the INSERT or UPDATE in as far as the other fields in the table?

EXAMPLE:

CREATE TABLE dbo.MYTABLE(
ID NUMERIC(18,0) IDENTITY(1,1) NOT NULL,
FirstName VARCHAR(50) NULL,
LastName VARCHAR(50) NULL,

[Code] ....

If an INSERT looks like any of the following what can I do to change the NULL being assigned to DateAdded to a real date, preferable the value of GetDate() at the time of the insert? I've heard of INSTEAD of Triggers but I'm not trying tto over rise the entire INSERT or update just the on (maybe 2) fields that are being left as null or explicitly set to null. The same would apply for any UPDATE where DateModified is not specified or explicitly set to NULL. I would want to change it so that DateModified is not null on any UPDATE.

INSERT INTO dbo.MYTABLE( FirstName, LastName, DateAdded)
VALUES('John','Smith',NULL)

INSERT INTO dbo.MYTABLE( FirstName, LastName)
VALUES('John','Smith')

INSERT INTO dbo.MYTABLE( FirstName, LastName, DateAdded)
SELECT FirstName, LastName, NULL
FROM MYOTHERTABLE

View 9 Replies View Related

Can I Insert/Update Large Text Field To Database Without Bulk Insert?

Nov 14, 2007

I have a web form with a text field that needs to take in as much as the user decides to type and insert it into an nvarchar(max) field in the database behind.  I've tried using the new .write() method in my update statement, but it cuts off the text after a while.  Is there a way to insert/update in SQL 2005 this without resorting to Bulk Insert? It bloats the transaction log and turning the logging off requires a call to sp_dboptions (or a straight-up ALTER DATABASE), which I'd like to avoid if I can.

View 6 Replies View Related

T-SQL (SS2K8) :: Insert / Update Triggers When Insert Run Via Script

Oct 23, 2014

I'm working on inserting data into a table in a database. The table has two separate triggers, one for insert and one for update (I don't like it this way, but that's how it's been for years). When there is a normal insert, done via a program, it looks like the triggers work fine. When I run an insert manually via a script, the first insert trigger will run, but the update trigger will fail. I narrowed down the issue to a root cause.

This root issue is due to both triggers using the same temporary table name. When the second trigger runs, there's an error stating that a few columns don't exist. I went to my test server and test db and changed the update trigger so that the temporary table is different than the insert trigger temporary table, the triggers work fine. The weird thing is that if the temporary table already exists, when the second trigger tries to create the temporary table, I would expect it to fail and say that it already exists.I'm probably just going to update the trigger tonight and change the temporary table name.

View 1 Replies View Related

Trigger To Update A Table On Insert Or Update

Feb 15, 2008



Hello

I've to write an trigger for the following action

When a entry is done in the table Adoscat79 having in the index field Statut_tiers the valeur 1 and a date in data_cloture for a customer xyz

all the entries in the same table where the no_tiers is the same as the one entered (many entriers) should have those both field updated

statut_tiers to 1
and date_cloture to the same date as entered

the same action has to be done when an update is done and the valeur is set to 1 for the statut_tiers and a date entered in the field date_clture

thank you for your help
I've never done a trigger before

View 14 Replies View Related

Single Complex INSERT Or INSERT Plus UPDATE

Jul 23, 2005

Hello,I am writing a stored procedure that will take data from severaldifferent tables and will combine the data into a single table for ourdata warehouse. It is mostly pretty straightforward stuff, but there isone issue that I am not sure how to handle.The resulting table has a column that is an ugly concatenation fromseveral columns in the source. I didn't design this and I can't huntdown and kill the person who did, so that option is out. Here is asimplified version of what I'm trying to do:CREATE TABLE Source (grp_id INT NOT NULL,mbr_id DECIMAL(18, 0) NOT NULL,birth_date DATETIME NULL,gender_code CHAR(1) NOT NULL,ssn CHAR(9) NOT NULL )GOALTER TABLE SourceADD CONSTRAINT PK_SourcePRIMARY KEY CLUSTERED (grp_id, mbr_id)GOCREATE TABLE Destination (grp_id INT NOT NULL,mbr_id DECIMAL(18, 0) NOT NULL,birth_date DATETIME NULL,gender_code CHAR(1) NOT NULL,member_ssn CHAR(9) NOT NULL,subscriber_ssn CHAR(9) NOT NULL )GOALTER TABLE DestinationADD CONSTRAINT PK_DestinationPRIMARY KEY CLUSTERED (grp_id, mbr_id)GOThe member_ssn is the ssn for the row being imported. Each member alsohas a subscriber (think of it as a parent-child kind of relationship)where the first 9 characters of the mbr_id (as a zero-padded string)match and the last two are "00". For example, given the followingmbr_id values:1234567890012345678901123456789021111111110022222222200They would have the following subscribers:mbr_id subscriber mbr_id12345678900 1234567890012345678901 1234567890012345678902 1234567890011111111100 1111111110022222222200 22222222200So, for the subscriber_ssn I need to find the subscriber using theabove rule and fill in that ssn.I have a couple of ideas on how I might do this, but I'm wondering ifanyone has tackled a similar situation and how you solved it.The current system does an insert with an additional column for thesubscriber mbr_id then it updates the table using that column to joinback to the source. I could also join the source to itself in the firstplace to fill it in without the extra update, but I'm not sure if theextra complexity of the insert statement would offset any gains fromputting it all into one statement. I plan to test that on Monday.Thanks for any ideas that you might have.-Tom.

View 4 Replies View Related

Can I Roll Back Certain Query(insert/update) Execution In One Page If Query (insert/update) In Other Page Execution Fails In Asp.net

Mar 1, 2007

Can I roll back certain query(insert/update) execution in one page if  query (insert/update) in other page  execution fails in asp.net.( I am using sqlserver 2000 as back end)
 scenario
In a webpage1, I have insert query  into master table and Page2 I have insert query to store data in sub table.
 I need to rollback the insert command execution for sub table ,if insert command to master table in web page1 is failed. (Query in webpage2 executes first, then only the query in webpage1) Can I use System. Transaction to solve this? Thanks in advance

View 2 Replies View Related

SQL Insert/Update

Jul 7, 2006

Hi,
Can anyone explain what the difference is and the advantages or disadvantages of using the below statements in my SQL paramaters please. Is there a performance hit if I use the second option?
If myCustomer.Phone2 IsNot Nothing Then
versus
If myCustomer.Phone2.Length > 0 Then
 
Thanks

View 2 Replies View Related

DTS Insert Or Update

Dec 8, 2006

Is there an easy way with DTS to pump data from one table to another so that it will update the row if it exists (the source and destination have the same value for the ID colum) or insert it if it doesn't.
 I know this can be done with stored procedures/sql by doing IF EXISTS UPDATE ELSE INSERT but there are many tables and columns and this will be very tiime consuming.

View 1 Replies View Related

When To Use Sql Update And Sql Insert

Feb 2, 2008

I have a page where the user can update stock records. it has 5 x 5 text boxes. If the user has already entered stock before that stock will show up and they can change it and clicking the button it will update, however if they have just entered new data i would assume they would need to insert it, so how do i go about doing this? do i need to use both insert and update in the same sql string?

View 7 Replies View Related

Insert Then Update...

Jul 15, 2005

greetings

I am developing an application for the marketing dept at my company.
Basically users can build the content of an email to be sent to our
subscriber database.

I am wanting the application to initailly save the content into a database, the update the most recently inserted row.

The save button uses the following SQL command:
        Dim SqlMethod As String =
"INSERT INTO CZC_email (Offer, SendDate, Destinations, Copy, BannerURL)
VALUES ('" & txtCampaignName.Text & "','" &
calCampaignDate.SelectedDate.ToString("yy/dd/MM") & "','" &
DestinationsSelected & "','" & FreeTextBox2.Text & "', '"
& txtBannerPath.Text & "')SELECT @@IDENTITY AS 'CZ_ID'"

And my update button has this SQL command:

        Dim SqlMethod As String =
"UPDATE CZC_email SET SendDate = '" &
calCampaignDate.SelectedDate.ToString("yy/dd/MM") & "', Offer = '"
& txtCampaignName.Text & "',Destinations = '" &
DestinationsSelected & "', BannerURL = '" & txtBannerPath.Text
& "'  WHERE  CZ_ID = @@IDENTITY "

but it doesnt seem to be updating. anyone know what I'm doing wrong?

Cheers

View 7 Replies View Related

Insert Before Update?

Nov 24, 2005

I have a GridView on a page, It contains data from 2 joined tables and a command column to Edit/Update.  I want to update the data in only one of the tables.  If the data exists in the table to be updated there seems to be no problem (obviously).  But I get an error when trying to update a record that does not exist (I should think so).  Is there a way of Inserting the record before the update is fired?  I have tried to do an insert contained in a 'Try' in the GridView1_RowUpdating, but this does not seem to work. as I still get the same error. Object cannot be cast from DBNull to other types. 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.InvalidCastException: Object cannot be cast from DBNull to other types.
Source Error:
An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below. 
Stack Trace:
[InvalidCastException: Object cannot be cast from DBNull to other types.]   System.DBNull.System.IConvertible.ToInt32(IFormatProvider provider) +54   System.Convert.ChangeType(Object value, TypeCode typeCode, IFormatProvider provider) +293   System.Web.UI.WebControls.SqlDataSourceView.AddParameters(DbCommand command, ParameterCollection reference, IDictionary parameters, IDictionary exclusionList, String oldValuesParameterFormatString) +577   System.Web.UI.WebControls.SqlDataSourceView.ExecuteUpdate(IDictionary keys, IDictionary values, IDictionary oldValues) +400   System.Web.UI.DataSourceView.Update(IDictionary keys, IDictionary values, IDictionary oldValues, DataSourceViewOperationCallback callback) +78   System.Web.UI.WebControls.GridView.HandleUpdate(GridViewRow row, Int32 rowIndex, Boolean causesValidation) +1173   System.Web.UI.WebControls.GridView.HandleEvent(EventArgs e, Boolean causesValidation, String validationGroup) +1084   System.Web.UI.WebControls.GridView.OnBubbleEvent(Object source, EventArgs e) +88   System.Web.UI.Control.RaiseBubbleEvent(Object source, EventArgs args) +35   System.Web.UI.WebControls.GridViewRow.OnBubbleEvent(Object source, EventArgs e) +117   System.Web.UI.Control.RaiseBubbleEvent(Object source, EventArgs args) +35   System.Web.UI.WebControls.LinkButton.OnCommand(CommandEventArgs e) +83   System.Web.UI.WebControls.LinkButton.RaisePostBackEvent(String eventArgument) +136   System.Web.UI.WebControls.LinkButton.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) +172   System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +4839
 
Protected Sub GridView1_RowUpdating(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.GridViewUpdateEventArgs) Handles GridView1.RowUpdating
Try
Dim eventid As Integer = CInt(GridView1.Rows(GridView1.EditIndex).Cells(7).ToString)
Dim userid As Integer = CInt(hfID.Value)
Dim guests As Integer = CInt(GridView1.Rows(GridView1.EditIndex).Cells(4).ToString)
Dim attending As Boolean = CBool(GridView1.Rows(GridView1.EditIndex).Cells(5).ToString)
Dim extra As String = Server.HtmlEncode(GridView1.Rows(GridView1.EditIndex).Cells(6).ToString)
InsertRecord(eventid, userid, guests, attending, extra)
Catch ex As Exception
End Try
End Sub






Function InsertRecord(ByVal eventID As Integer, ByVal userID As Integer, ByVal guests As Integer, ByVal attending As Boolean, ByVal extra As String) As Integer
Dim connectionString As String = "server='localhost'; trusted_connection=true; Database='AFRA'"
Dim sqlConnection As System.Data.SqlClient.SqlConnection = New System.Data.SqlClient.SqlConnection(connectionString)
Dim queryString As String = "INSERT INTO [AFRAAttendance] ([EventID], [UserID], [Guests], [Attending], [Extra]) VALUES (@EventID, @UserID, @Guests, @Attending, @Extra)"
Dim sqlCommand As System.Data.SqlClient.SqlCommand = New System.Data.SqlClient.SqlCommand(queryString, sqlConnection)
sqlCommand.Parameters.Add("@EventID", System.Data.SqlDbType.Int).Value = eventID
sqlCommand.Parameters.Add("@UserID", System.Data.SqlDbType.Int).Value = userID
sqlCommand.Parameters.Add("@Guests", System.Data.SqlDbType.Int).Value = guests
sqlCommand.Parameters.Add("@Attending", System.Data.SqlDbType.Bit).Value = attending
sqlCommand.Parameters.Add("@Extra", System.Data.SqlDbType.VarChar).Value = extra
Dim rowsAffected As Integer = 0
sqlConnection.Open()
Try
rowsAffected = sqlCommand.ExecuteNonQuery
Finally
sqlConnection.Close()
End Try
Return rowsAffected
End FunctionThank you

View 1 Replies View Related

DTS To Update Rather Than Insert

Jan 6, 2006

Is it possible to run a DTS package so that it just updates existing rows rather than a full on insert?

I have an HR db as a source and a health and safety db as destination. records of peoples H+S acheivments and assessments are maintained in the H+S db but i need to keep this topped up with the newly recruited staff details and the existing staff change of details. The DTS need to know to insert where its a new record and to update where its just a change of name or work location etc.

if DTS wont do this, what are my options?

TIA

View 2 Replies View Related

There Has To Be A Better Way To INSERT/UPDATE

Aug 22, 2004

I have a form that pulls existing information from a database and allows users to edit it. Every field can be left blank if the user wishes. I am having an issue with determining which SQL statement to run. There has to be an easier way to do this then what I am doing. For each filed I process the data this way:

SQL2="select * from 1985ClassList where MailingListID =" & Session("EID")
set aData = oConn.execute(SQL2)


SQL = "Update 1985ClassList SET "

if aData.Fields("FirstName") > " " and Request.QueryString("FirstName") > " " then
SQL = SQL & "'FirstName' = '" & Request.QueryString("FirstName") & "', "
end if

if aData.Fields("FirstName") = " " and Request.QueryString("FirstName") > " " then
strFSQL = strFSQL & "FirstName, "
strVSQL = strVSQL & Request.QueryString("FirstName") & ", "
end if


SQL = SQL & "where MailingListID =" & Session("EID")
set mData = oConn.execute(SQL)

if strFSQL > " " then
SQL1 = "Insert into 1985ClassList " & strFSQL & "VALUES " & strVSQL
end if
set mData = oConn.execute(SQL1)

It works fine for the UPDATE, but not for the INSERT.

View 9 Replies View Related

Insert/Update

Sep 1, 2004

Hi,
Is it possible to Read data From a Table while Insertion/Update is happening on the table??
In case if this is not possible,Is there anyway to do that??

Thanks,
Karthik

View 2 Replies View Related

Insert/Update

Dec 6, 2004

Is there a way to insert / update in SQL Server 2000 with one command..

I know with Oracle there is a merge command....

View 1 Replies View Related

Need Help Update And Insert

Feb 19, 2004

I need a query to make an insert or and update in the same query

thanks

View 6 Replies View Related

Insert Or Update

Jun 4, 2008

Scenario :

MS SQL 2005

Tables:
DETAIL --Here are all the detailed transactions
Customer
Item
Amount
...MoreFields

TOTAL --Here are the accumulated values (1 record by Customer-Item
Customer
Item
Accumulated
...MoreFields

What I want to know is the general logic to
-Read all records from table DETAIL
-If relationship Customer-Item exists in TOTAL table then UPDATE the TOTAL.Accumulated field adding the DETAIL.Amount field
-If relationship Customer-Item doesn't exists in TOTAL table then INSERT a new ecord with the values from DETAIL table

Because of my experience in VB and ADO my first try is to use a Cursor and loop record by record

But I know that is not the only and better solution

May You post some hint or some sample querie on how could be done

Thanks

JG

View 2 Replies View Related







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