Quick Update/Insert TSQL?
Aug 14, 2007
Hi all!
I have a quick question...
'UPLOAD / INSERT EXISTING CLIENT DATA INTO SQL SERVER FROM CLIENT
INSERT INTO ProductLocal
(tblID,SQLkey, CreateDateTime, Alias, ProductNumber, ProductMfgID, ProductDesc, ProductAlt1Number, ProductAlt2Number, ProductCost,
ProductListPrice, ProductVendorID, ProductHier1, ProductHier2, ProductHier3, ProductHier4, ProductHier5, ProductHier6, ProductHier7, ProductHier8,
ProductHier9, ProductCategory, ProductSubCategory, ProductLocalAdd, ProductAddDesc, create_timestamp, update_timestamp, update_originator_id,
create_date)
SELECT tblID, SQLkey, CreateDateTime, Alias, ProductNumber, ProductMfgID, ProductDesc, ProductAlt1Number, ProductAlt2Number, ProductCost,
ProductListPrice, ProductVendorID, ProductHier1, ProductHier2, ProductHier3, ProductHier4, ProductHier5, ProductHier6, ProductHier7, ProductHier8,
ProductHier9, ProductCategory, ProductSubCategory, ProductLocalAdd, ProductAddDesc, create_timestamp, update_timestamp, update_originator_id,
create_date
FROM Product
WHERE (Alias = 'me')
'DOWNLOAD / UPDATE-INSERT EXISTING LOCAL DB (PRODUCT TABLE) FROM SQL SERVER PRODUCTLOCAL TABLE
What would be the best and least expensive way to UPDATE/INSERT the local client db from the SQL Server?
-- LOCAL DB (PRODUCT TABLE) FROM SQL SERVER PRODUCTLOCAL TABLE
Any help would be appreciated.... thanks.
Kind regards,
billb
View 2 Replies
ADVERTISEMENT
Jul 31, 2007
Hey all... I have a few tables that I am joining and need to know how to set a value from the return to a different column:
example:
SELECT
Prospect.ProspectName AS P1, AccountShipTo.ShipToName AS [Account Name], ProposalHeader.PropHCity AS City, ProposalHeader.PropHState AS State,
ProposalHeader.PropHNumb AS [Proposal ID], ProposalHeader.PropHRevNumb AS Rev, ProposalHeader.PropHCreateDate AS [Creation Date],
ProposalHeader.update_timestamp AS [Last Edit Date]
FROM ProposalHeader LEFT OUTER JOIN
AccountShipTo ON ProposalHeader.PropHShipTo = LTRIM(AccountShipTo.ShipToCust) LEFT OUTER JOIN
Prospect ON ProposalHeader.PropHBillTo = LTRIM(Prospect.ProspectNumb)
WHERE (ProposalHeader.Alias = N'billb')
GROUP BY ProposalHeader.PropHRevNumb, AccountShipTo.ShipToName, ProposalHeader.PropHCity, ProposalHeader.PropHState,
ProposalHeader.PropHNumb, ProposalHeader.PropHCreateDate, ProposalHeader.update_timestamp, Prospect.ProspectName
ORDER BY [Proposal ID]
I need P1 value (Test - Timberline Corp)to be in the Account Name column (NULL)... any ideas?
P1 Account Name City State Proposal ID Rev Creation Date Last Edit Date
NULL Samples, Inc. High Point NC Samples1 1 2007-07-25 2007-07-30
Test - Timberline Corp NULL Rapid City SD test1 1 2007-07-31 2007-07-31
(2 row(s) affected)
Any help would be appreciated... thanks!
View 3 Replies
View Related
Apr 20, 1999
Does any one know a quicker way to get data in from TableA to TableB.
Both tables have the same structure(Acct# varchar(9); Type varchar(12);
Date1 datetime; Date2 datetime; Date3 datetime; Date4 datetime; and Date5 datetime
No Nulls allow in Acct & Type field.
TableA has 5.5 Million Rows and TableB has about 1 million rows , I wrote a Stored Proc
using cursor to select from TableA insert into TableB if the Acct# and Type doesn't exists.
I didn't want to use bcp because I only wanted rows(Acct#'s) that exists in TableA that where not in
TableB. My procedure is working but at a rate of 100 records per minute. Any suggestions?
View 2 Replies
View Related
May 12, 2006
On a Friday afternoon, no less.
Anyway, in a nutshell, I have a flat file (tab-delimited, row terminator is ":0D0A" (or '
'). I want to bulk import it into a table that is defined EXACTLY like the table (on a remote system) that the data comes from (I stole the remote tables DDL by scripting the table definition).
Some of the columns in the table are BIT data types.
In the flat (text) file that I get, those column values are present as the literal words "true" and "false".
When I do the bulk insertBULK INSERT dbo.Staging_Funds
FROM 'D:TradeAnalysisImportedFilesMutualFunds.txt '
WITH (FIELDTERMINATOR = ' ',
ROWTERMINATOR = '
',
TABLOCK) it fails saying there is a data type mismatch between the file and the local table column that corresponds to the first BIT data type in the file.
Thinking (uh-oh) there has got to be an easier way, I modify my bulk insert and add a DATAFILETYPE = 'native' to the bulk insert. This gets me past the error (or not) but results in a DIFFERENT error. Bulk Insert fails. Column is too long in the data file for row 1, column 1. Make sure the field terminator and row terminator are specified correctly.I do, they are.
Is this an either/or situation? Meaning, if I use "native" format, does it ignore the fieldterminator for some reason?
I am tempted to modify my staging table to just pull in the flag (BIT) columns as char(5) columns, and be done with it (we don't use the flag bits at present anyway) but there is also a part of me that says this should be doable, for crissake.
Any quick guidance or thoughts (s'OK, the thoughts can be slower...it's Friday, I know)
View 4 Replies
View Related
Nov 14, 2007
I am new to Sql, so I think this is a really easy question.
But I am working on a 2005 MSSQL database.
When I add columns to an existing application with data and tables already being used, and the new column will be set to Database Null.
What is an easy way to quickly add the data to all the rows in the table.
For example if I am adding a checkbox, I've been doing it manually, add the column, change all the rows to False, one by one.
And then I can change it to Disallow DBNull.
As I get more and more users this could be a very time consuming process.
So the name of the Table is classifeds_Ads and let's say the column I want to add is Bonus and it needs to be filled with False.
How do I do this???
Thank you in advance
Daniel Meis
View 2 Replies
View Related
Mar 7, 2008
I need to update a column(X) in database A.tableA with a column(Y) from database B.tableB where the value in
another column(Z) in A.tableA = the value in a column(W) in B.tableB
Both databases A & B are on the same SQL server.
Any help with the syntax will be appreciated
Thanks
View 6 Replies
View Related
Nov 11, 2006
I've been staring at two scripts for hours now. Save a few key strokes they are identical. One works perfectly and the other errors out.
The first is:
USE BLADeS_DW
TRUNCATE TABLE ST_BLDS_TRNS
GO
DECLARE @Address varchar (500)
DECLARE @fileName varchar (500)
DECLARE @Command nvarchar(1000)
SET @Address = '\CRPSFOMSQ47IncomingBLADeS'
SET @fileName = 'FNIISA8N.'
SET @fileName = @fileName + CONVERT(char(4),YEAR(getdate()))
SET @fileName = @fileName + CONVERT(char(2),MONTH(getdate()))
SET @fileName = @fileName + CONVERT(char(2),DAY(getdate()))
SET @Command = 'BULK INSERT BLADeS_DW.dbo.ST_BLDS_TRNS
FROM ''' + @Address + @fileName + '''
WITH
(
FORMATFILE = ''\crpsfomsq47BulkInsert-FormattingBLADeS_TRANS_IMPORT.fmt''
)
'
EXEC sp_executesql @Command
The second is:
USE BLADeS_DW
TRUNCATE TABLE ST_BLDS_APPS
GO
DECLARE @Address varchar (500)
DECLARE @fileName varchar (500)
DECLARE @Command nvarchar(1000)
SET @Address = '\CRPSFOMSQ47IncomingBLADeS'
SET @fileName = 'FNIISA9N.'
SET @fileName = @fileName + CONVERT(char(4), YEAR(getdate()))
SET @fileName = @fileName + CONVERT(char(2),MONTH(getdate()))
SET @fileName = @fileName + CONVERT(char(2), DAY(getdate()))
SET @Command = 'BULK INSERT BLADeS_DW.dbo.ST_BLDS_APPS
FROM ''' + @Address + @fileName + '''
WITH
(
FORMATFILE = ''\crpsfomsq47BulkInsert-FormattingBLADeS_APP_IMPORT.fmt''
)
'
EXEC sp_executesql @Command
The error generated by the second is:
Server: Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'from'.
Any ideas would be appreciated.
View 1 Replies
View Related
Aug 20, 2001
Hello -
I am looking for a way to allow users to create and edit their own stored procedures, saved under DBO (not under their own schema).
Any help would be greatly appreciated!
Paul
View 2 Replies
View Related
Apr 16, 2008
Per my client, when it creates the qualifier for 'HCPS-DEN"' it needs to create the qualifier as 'AD' not 'HC' . Currently, with the present update script its inserting 'HC' on all. I need it to say when the 'HCPS-DEN' is used, insert the 'AD' not 'HC'. Everything else should be the HC.
I tried using a CASE statement:
CASE WHEN Description = 'HCPC - DEN' THEN 'AD' ELSE 'HC' END
however I get this back:
Msg 207, Level 16, State 1, Line 10
Invalid column name 'Description'.
I was trying to plug this part in where HC is being entered. I think its missing an obvious Join or maybe there is a better way to write this. Any help is greatly appreciated!!
declare @qualid int
if 1=1
BEGIN
if not exists (select * from medlists where tablename='ProcedureCodeQualifier' and Description = 'HCPC - DEN')
begin
declare @listorder int
select @listorder=max(listorder)+1 from medlists where tablename='ProcedureCodeQualifier'
insert into medlists
select 'ProcedureCodeQualifier',NULL,'HC','HCPC - DEN',@listorder,NULL,NULL,NULL,NULL,NULL,NULL,NULL,@listorder,getdate(),'CYSScript',getdate(),dbo.GetLogonId()
update medlists set dotid=medlistsid where medlistsid=scope_identity()
end
select @qualid=medlistsid from medlists where tablename='ProcedureCodeQualifier' and Description = 'HCPC - DEN'
update procedures set CPTProcedureCodeQualifierMId=@qualid where proceduresid in (8634)
END
ELSE
BEGIN
select @qualid =(select top 1 medlistsid from medlists where tablename='ProcedureCodeQualifier' and Description like 'Health Care Financing Administration Common Procedural Coding System (HCPCS) Codes%')
update procedures set CPTProcedureCodeQualifierMId=@qualid where proceduresid in (8634)
END
View 2 Replies
View Related
Apr 16, 2008
Per my client, when it creates the qualifier for 'HCPS-DEN"' it needs to create the qualifier as 'AD' not 'HC'. Currently, with the present update script its inserting 'HC' on all. I need it to say when the 'HCPS-DEN' is used, insert the 'AD' not 'HC'. Everything else should be the HC.
I tried using a CASE statement:
CASE WHEN Description = 'HCPC - DEN' THEN 'AD' ELSE 'HC' END
however I get this back:
Msg 207, Level 16, State 1, Line 10
Invalid column name 'Description'.
I was trying to plug this part in where HC is being entered. I think its missing an obvious Join or maybe there is a better way to write this. Description is being used after the first BEGIN. I know it needs to also go into my insert somehow, but I'm not sure how or if there is an easier way to do this. Any help is greatly appreciated!!
declare @qualid int
if 1=1
BEGIN
if not exists (select * from medlists where tablename='ProcedureCodeQualifier' and Description = 'HCPC - DEN')
begin
declare @listorder int
select @listorder=max(listorder)+1 from medlists where tablename='ProcedureCodeQualifier'
insert into medlists
select 'ProcedureCodeQualifier',NULL,'HC','HCPC - DEN',@listorder,NULL,NULL,NULL,NULL,NULL,NULL,NULL,@listorder,getdate(),'CYSScript',getdate(),dbo.GetLogonId()
update medlists set dotid=medlistsid where medlistsid=scope_identity()
end
select @qualid=medlistsid from medlists where tablename='ProcedureCodeQualifier' and Description = 'HCPC - DEN'
update procedures set CPTProcedureCodeQualifierMId=@qualid where proceduresid in (8634)
END
ELSE
BEGIN
select @qualid =(select top 1 medlistsid from medlists where tablename='ProcedureCodeQualifier' and Description like 'Health Care Financing Administration Common Procedural Coding System (HCPCS) Codes%')
update procedures set CPTProcedureCodeQualifierMId=@qualid where proceduresid in (8634)
END
View 1 Replies
View Related
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
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
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
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
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
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
Nov 19, 2007
Can anyone please give me the equivalent tsql for sql server 2000 for the following two queries which works fine in sql server 2005
1
-- Full Table Structure
select t.object_id, t.name as 'tablename', c.name as 'columnname', y.name as 'typename', case y.namewhen 'varchar' then convert(varchar, c.max_length)when 'decimal' then convert(varchar, c.precision) + ', ' + convert(varchar, c.scale)else ''end attrib,y.*from sys.tables t, sys.columns c, sys.types ywhere t.object_id = c.object_idand t.name not in ('sysdiagrams')and c.system_type_id = y.system_type_idand c.system_type_id = y.user_type_idorder by t.name, c.column_id
2
-- PK and Index
select t.name as 'tablename', i.name as 'indexname', c.name as 'columnname' , i.is_unique, i.is_primary_key, ic.is_descending_keyfrom sys.indexes i, sys.tables t, sys.index_columns ic, sys.columns cwhere t.object_id = i.object_idand t.object_id = ic.object_idand t.object_id = c.object_idand i.index_id = ic.index_idand c.column_id = ic.column_idand t.name not in ('sysdiagrams')order by t.name, i.index_id, ic.index_column_id
This sql is extracting some sort of the information about the structure of the sql server database[2005]
I need a sql whihc will return the same result for sql server 2000
View 1 Replies
View Related
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
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
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
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
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
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
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
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
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
Feb 19, 2004
I need a query to make an insert or and update in the same query
thanks
View 6 Replies
View Related
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
Jul 20, 2005
I'm new to DTS. I read some docs before adventuring into this matter.I still haven't found in all the docs I read if there is some "built-in" DTStask or function or wathever, to do a mixed "insert/update" import from asource, giving a unique field as primary key.I'll try to be more specific. The problem I would like to solve is this:I have a source file (csv) but it could be any source. I must check thisfile for all the records and compare them with the ones in the db (giving aunique field as a way of checking), so that all the records that alreadyexist, are UPDATED, and the others are INSERTED.I guess this is one most common task to accomplish, when you have a localbased application that you regularly update and then you want to export thedata to another "slave" application without using replication. But even ifthis sounds to me like a common task, I found no "buil-in" function for thatin DTS. I read something about "lookups" but it don't know if it's relatedsomehow.. it wasn't very clean.Thanks in advance for suggestions.--:: Massimiliano Mattei:: Project Leader:: E.xtranet V.irtual A.pplication:: www.evagroup.net :: www.commy.biz
View 3 Replies
View Related
May 22, 2008
Hi Team,
I would like to import a .csv file into my existing table. On the DTS, I would like to check if the record already there then updated, on the other hand, if the record wasn't there then add it. How can I do that using DTS. Please help and thanks.
View 3 Replies
View Related
Apr 30, 2015
table2 is intially populated (basically this will serve as historical table for view); temptable and table2 will are similar except that table2 has two extra columns which are insertdt and updatedt
process:
1. get data from an existing view and insert in temptable
2. truncate/delete contents of table1
3. insert data in table1 by comparing temptable vs table2 (values that exists in temptable but not in table2 will be inserted)
4. insert data in table2 which are not yet present (comparing ID in t2 and temptable)
5. UPDATE table2 whose field/column VALUE is not equal with temptable. (meaning UNMATCHED VALUE)
* for #5 if a value from table2 (historical table) has changed compared to temptable (new result of view) this must be updated as well as the updateddt field value.
View 2 Replies
View Related
Sep 28, 2006
I am having an issue with this SQL Statement i am trying to write. i want to insert the values (Test Facility) from CCID table INTO CCFD table where CCID.id = CCFD.id this is what i have.UPDATE CCFD.id SET TEST_FACILITY = (SELECT CCID.id.TEST_FACILITYFROM CCID.idWHERE CCID.id = CCFD.id)orINSERT INTO CCFD.id(Test_Facility)SELECT TEST_FACILITYFROM CCID.idWHERE (CCFD.id.INDEX_ID = CCID.id.INDEX_ID) thanks in advanced
View 1 Replies
View Related
May 1, 2007
Is there a way I can stop a form from inserting or updating a record when there is an error. I have an sql 2000 DB. I have noticed that if the db field can handle 50 characters and the form field has no limit on the number of characters no errors are displayed to the user if they try to use more than the 50 characters in the textbox. The record is not saved. None of the fields are saved. I do notice the autonumber generated is skipped by the db. That is, the next autonumber for a successful insert skips the logical next number. How do I capture this error, or any save error and return the user back to the form? Yes, I have limited the number of characters a user can type on the textbox now, but I would really like to catch save or insert errors. I use asp.net 2.0 and VB. I don't know C#. thanksMilton
View 5 Replies
View Related
