Does anybody have a sample SQL script that will select table A and compare it to table B. If a row exists in both table A and table B, it will update the columns in table B with the columns in table A. If the row does not exist in table B, it will insert a row in table B using the row in table A. Is this possible?
Thanks Mitch
I am very new to SQL Server 2005. I have created a package to load data from a flat delimited file to a database table. The initial load has worked. However, in the future, I will have flat files used to update the table. Some of the records will need to be inserted and some will need to update existing rows. I am trying to do this from SSIS. However, I am very lost as to how to do this.
Any suggestions?
I have a SQL script to insert data into a table as below:
INSERT into [SRV1INS2].BB.dbo.Agents2
select * from [SRV2INS14].DD.dbo.Agents
I just want to set a Trigger on Agents2 Table, which could delete all rows in the table , before carry out any Insert operation using above statement.I had below Table Trigger on [SRV1INS2].BB.dbo.Agents2 Table as below: But it did not perform what I intend to do.
USE [BB]
GO
/****** Object: Trigger   Script Date: 24/07/2015 3:41:38 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
[code]....
i am using visual web developer 2005 and SQL Express 2005 with VB as the code behindi have a button and in the button click event i have written codes to INSERT to a database table - it has one primary keyso when i click the button, if there is already a row with primary key fields value as 10 and if i try to INSERT with the same value in the primary key field there will occur primary key constraintso , if i try to INSERT with the already existing primary key fields value, instead of INSERTing it should be UPDATEd without generating any errorplease help me
View 1 Replies View RelatedI have an asp.net project that displays a timesheet based on a fortnightly system.
It has a Y and X axis, i.e. Sun-Mon, Sun-Mon across the top and Categories of hour types accross the Y axis, i.e. Holidays, Overtime, Maintenance.
I was using a datagrid to gather the rows, but I need to have the whole grid in edit mode which is something that requires more coding. So I have swapped to a datalist.
It will take me 240 fields to store all the data in their corresponding fields. So I basically need to know if there is an easier way to store two dimensional array type data into an SQL table using a minimum of fields.
SQLNewbie writes "I have a table 'ImportedListings' that is populated with data external to the database. This table is only used to hold the data until I can move it to the permanent table 'Listings' at which point 'ImportedListings' gets truncated to nothing. Both tables contain almost identical data and structure. There is a listing ID column available for joins.
Basically I need to compare each row in ImportedListing and if it already exists in Listing, UPDATE Listing with the new info. If the row in ImportedListing doesn't exist in Listing, I need to INSERT it.
Physically deleting rows from the listings table is not an option. Do you have any ideas on how I can do this? I initially tried using a temp table to hold the matching listing id's but I could not get figure out the update statement with this scenario.
Thanks for any help! I have been trying to hammer this out all night(I just started programming tsql)"
i am creating a database driven website and i am using a sql database. I have a database called company with fields in it to do with a company, I have created a company.cs file which sets the variables, properties and methods and so on. These work fine. I have also stored procedures and they are right as far as i know. i also have coding behind the buttons of my page when i try and update or insert to the database. I am having the problem of when i enter data into the text boxes and click update, nothing gets inserted or updated and whats worst of all no error message appears. The table is used for storing profile data about a user, when a user logs on they enter their profile data, if i manually enter this data and input the username of a user into the username field within the database then the data appears fine in the textboxes of the update info page but the data will not insert or update. i have checked all the little things and i am stressing out cos i am running out of time and cannot find the problem...........please could someone help me!!!!! thanks
Heres my coding to some of my pages to help you....
the code behind the button
using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using FYPtestsite.Classes;
public partial class Employer_employerprofile : System.Web.UI.Page
{protected void Page_Load(object sender, EventArgs e)
{if (!Roles.IsUserInRole(ConfigurationManager.AppSettings["employerrole"]))
{Response.Redirect("~/Error1.aspx");
}Company objCompany = Company.GetCompany(Profile.UserName);if (objCompany != null)
{
txtCompanyname.Text = objCompany.Companyname;
txtAddress1.Text = objCompany.Address1;
txtAddress2.Text = objCompany.Address2;
txtPostcode.Text = objCompany.Postcode;
txtCity.Text = objCompany.City;
txtPhone.Text = objCompany.Phone;
txtFax.Text = objCompany.Fax;
txtEmail.Text = objCompany.Email;
txtURL.Text = objCompany.URL;
txtProfile.Text = objCompany.Profile;
}
}protected void Button2_Click(object sender, EventArgs e)
{Response.Redirect("~/homepage.aspx");
}protected void Button3_Click(object sender, EventArgs e)
{Company objCompany = new Company();
objCompany.UserName = Profile.UserName;
objCompany.Companyname = txtCompanyname.Text;
objCompany.Address1 = txtAddress1.Text;
objCompany.Address2 = txtAddress2.Text;
objCompany.Postcode = txtPostcode.Text;
objCompany.City = txtCity.Text;
objCompany.Phone = txtPhone.Text;
objCompany.Fax = txtFax.Text;
objCompany.Email = txtEmail.Text;
objCompany.URL = txtURL.Text;
objCompany.Profile = txtProfile.Text;
if (Profile.Employer.CompanyID != -1)
{objCompany.CompanyID = (int)Profile.Employer.CompanyID;Company.Update(objCompany);
}
else
{int i = Company.Insert(objCompany);
Profile.Employer.CompanyID = i;
}labelstatus.Text = "Your profile has been successfully updated";
}
}
Any help would be greatly greatly appreciated!!!!!!
i wanted to ask how to insert values from a single web form into two sql tables, i have been looking and the visual web developer i use doesnt seam to allow me to even atempt it i've tried selecting all the values from two different tables and then adding those two tables to an insert function but it doesnt work likewise the update functioni have values in a table currently a reference number and i want to use this reference number to update the address values in this table so update this field.table1 and thisfield.table2 when ref number = @ refnumber the reference number is present in both tables and is linked PK to FK <asp:SqlDataSource ID="SqlDataSource2" runat="server"
ConnectionString="<%$ ConnectionStrings:Back End DataConnectionString %>"
SelectCommand="SELECT StartDetails.StartDetailsID, StartDetails.ContractIDNo, StartDetails.ContractName, StartDetails.NINO, StartDetails.AnticipatedStartDate, StartDetails.StartDateTime, StartDetails.StartDateLetterSent, StartDetails.StartDate, StartDetails.AnticipatedEndDate, StartDetails.ActualEndDate, StartDetails.ReasonForLeaving, StartDetails.Provider, StartDetails.AdviserReferrer, StartDetails.ProvisionCat, StartDetails.Provision, ClientDetails.NINO AS Expr1, ClientDetails.CentreNo, ClientDetails.FirstName, ClientDetails.SecondName, ClientDetails.AddressLine1, ClientDetails.AddressLine2, ClientDetails.PostCode, ClientDetails.ContactTelephoneNumber, ClientDetails.MobileNo, ClientDetails.Email, ClientDetails.DateOfBirth, ClientDetails.Gender, ClientDetails.PWD, ClientDetails.Ethnicity, ClientDetails.ClientGroup, ClientDetails.RepeatStartDate, ClientDetails.CaseworkerName, ClientDetails.ClientStatus, ClientDetails.PlacementDates, ClientDetails.JobsearchDay, ClientDetails.AchievedILP, ClientDetails.JobDate, ClientDetails.JobDate2, ClientDetails.JobDate3, ClientDetails.EligibleForRolledUpWeeks, ClientDetails.NoOfWeeksClaimed, ClientDetails.MarketingWhere, ClientDetails.Notes, ClientDetails.JobCentre, ClientDetails.JobCentreRep FROM StartDetails INNER JOIN ClientDetails ON StartDetails.NINO = ClientDetails.NINO WHERE (StartDetails.StartDetailsID = @StartDetailsID) AND (StartDetails.NINO = @NINO)"
InsertCommand="INSERT INTO [StartDetails] ([NINO], [StartDate], [AnticipatedEndDate]) VALUES (@NINO, @StartDate, @AnticipatedEndDate)"
UpdateCommand="UPDATE [StartDetails] SET [StartDate] = @StartDate, [AnticipatedEndDate] = @AnticipatedEndDate WHERE [StartDetailsID] = @StartDetailsID,[NINO] = @NINO">
<SelectParameters>
<asp:QueryStringParameter Direction="InputOutput" Name="StartDetailsID"
QueryStringField="StartDetailsID" />
<asp:QueryStringParameter Direction="InputOutput" Name="NINO"
QueryStringField="NINO" />
</SelectParameters>
<UpdateParameters>
<asp:Parameter Name="StartDate" Type="DateTime" />
<asp:Parameter Name="AnticipatedEndDate" Type="DateTime" />
<asp:Parameter Name="StartDetailsID" Type="Int32" />
<asp:Parameter Name="NINO" Type="String" />
</UpdateParameters>
<InsertParameters>
<asp:Parameter Name="NINO" />
<asp:Parameter Name="StartDate" Type="DateTime" />
<asp:Parameter Name="AnticipatedEndDate" Type="DateTime" />
</InsertParameters>
</asp:SqlDataSource> your coinsideration is appreciatedChris
Hi, I'm trying to store large strings to a database, so am using thetext field type (LongText). I have used this before when storing thehtml of a webpage, and was able to store more than 255 characters byusing just a normal update sql statement. Now I'm trying to store thebody of research papers, and must be doing something different, as Ican only store 255 characters.Can someone explain why SQL Server doesn't like what I am doing -should I be using the WriteText / UpdateText function? If so, pleaseexplain by example how I would do that, and why doing that works.Thanks so much,Iain
View 5 Replies View RelatedI have created a single Data Flow Task that reads a set of records from a source table and then makes determinations whether to insert, update or delete from a destination table. The data is basically being copied from one database to another with a small amount of data manipulation and lookups. The problem seems to be that when running the task, even a small amount of records read from the source table seem to take a long time for the task to finish. I feed the records into a Script Component (the brains) that sorts the records to three separate outputs. I use OLE DB Commands to perform the DELETE and UPDATE and an OLE DB Destination for INSERT. I thought that by using three separate database connections would help, but it just appears to be locked while trying to perform these commands against the same table.
Is there a way to control or route these three record sets in such a way as to perform them sequentially?
I know it's a bit of a simple question for some of you, but I'm just learning SSIS (but I like it!).
Thanks!
Jeff Tolman
E&M Electric
I am using the following query to calculate date differences:select ..........DATEDIFF(d, recruitment_advertising.advertising_date, career_details.RTS_Email AS Datetime) AS Ad_to_RTS_days FROM .....I have stored all my dates as NVARCHAR because of the issues with localization.If the value is an empty String my output is eg: -38700. which is way off and incorrect. Some of the values in my table are NULL and they produce the correct result.Is there a T-SQL statement to replace empy Strings with the NULL value in my tables.I'd like to use it as a trigger when inserting or updating to convert empty strings to NULLbefore the values are inserted.Thanks guys.
View 1 Replies View RelatedHello.I've read many topics about this problem but i couldn't figure it out.I use form where user must insert 2 dates using texboxes.-One is required and other is optional.Sql 2000 is inserting either '20061105' or '2006.11.05' on insert update but select query returns 05.11.2006 on my report. Question 1.How do I insert or update dates from my form where date is entered dd.mm.yyyy to sql 2000 table?question 2. What to do if user left optional texbox date empty.I'm using SP and function with arguments (byval texbox1.text as date, byval texbox2.text as date)and parameters @date1, sqldbtype date =texbox1.text
View 2 Replies View RelatedWe've installed the Oracle provider for OLE DB on SQL Server 2005, which has the default collation (SQL_Latin1_General_CP1_CI_AS), and we've created a linked server for the Oracle 9.2.0.5 database, which has AL32UTF8 as the database character set. We can successfully insert strings into VARCHAR2 columns on Oracle from SQL Server via EXEC SP_EXECUTESQL('INSERT OPENQUERY(...) VALUES(...)') -- as long as the strings (whether selected from NVARCHAR columns on SQL Server or specified as literals with the N prefix during testing) only contain Windows-1252 characters.
If the SQL statement contains a character above U+00FF, the string on the Oracle side is incorrectly/doubly encoded; there are nearly (but not exactly) 4 bytes per character instead of the 1 or 2 you'd expect from ASCII/Latin-1 characters encoded as UTF-8.
We've tried reconfiguring the linked server: collation compatible = false, use remote collation = true, and collation name = Latin1_General_BIN2. But that had no effect.
What is the correct way to do this?
Thanks!
Hi Gnite everyone, i once again need help with a T-SQL syntax for Auto Correction for insert and update when client enters the wrong format, i;e, creating a SSN Data type and a User Defined Procedure that auto corrects input format i;e,
user inserts into table authors of the pubs DB 525-477845 column au_id that executes auto correction so user doesn't have to put in dashes for SSN format. Please help me with this syntax .
Hi,
Inside a single transaction I'm inserting/updating multiple records into multiple tables, in this order:
table 1 record 1
table 2 record 1
table 3 record 1
table 1 record 2
table 2 record 1
table 1 record 3
table 2 record 3
table 3 record 3
Now I'm getting an unspecified error on a certain table:
Unspecified error [ 3,-1300704038,-1834095882,activitypointerBase,x lock (x blocks),PAG (idx): 1078 ]
From msdn I see that:
PAG (idx) means a lock on an index page.
x lock means an exclusive lock:
Indicates a data modification, such as an insert, an update, or a deletion. Ensures that multiple updates cannot be made to the same resource at the same time. (I assume that multiple updates within the SAME transaction can be made, only multiple updates from different transaction cannot be made, right?)
I cannot find any reference to this error message and don't know what the numbers mean. Maybe it relates to data that can be found in the sys.lock_information table like explained here, http://technet.microsoft.com/en-us/library/ms172932.aspx, but I'm not sure.
Furthermore, the sys.lock_information table is empty. I haven't been able to reproduce the problem myself. I only received an error log and the database to investigate it.
So, does anybody have an idea what this error message means and what I can do to troubleshoot this?
Thanks,
Jeffry
I have an item table (#Codes) which contains itemIDs, some of which are always used in a subsequent data table (#Data) to hold information about a case+scearnio. These item rows have a standarditem value = 1. Rows in the item table which may be included in the subsequent data table (#Data), but not for every single case+scearnio, are considered nonstandard rows and therefore have a standarditem value = 0. I have been trying to write some code that automatically inserts rows from #Codes which have standarditem = 1 into #Data for each of the caseID+scenarioIDs in #Cases but have not figured it out yet. See "desired result" below to see exactly the output I'm trying to achieve.
Also, is there a way to insert the standarditem = 1 rows from #Codes into #Data when new caseID+scenarioID rows are added to #Cases? Ultimately, I'd like to either create a button for the user to click in the Access interface that inserts these rows when clicked OR when the user enters a new caseID+scenarioID in #Cases the rows just automatically appear for the new case row. This way, the rows will already have the caseID, scenarioID & itemID fields already populated and all the user will have to do is enter the item value and be able to manually add standarditem = 0 rows and their values if needed.
Code Block
create table #Codes (itemID nvarchar(2), standarditem int)
insert into #Codes values (1,1)
insert into #Codes values (2,0)
insert into #Codes values (3,1)
insert into #Codes values (4,1)
insert into #Codes values (5,0)
create table #Cases (caseID nvarchar(5), scenarioID nvarchar(15), createdate datetime)
insert into #Cases values (823, 1, '20071210')
insert into #Cases values (823, 2, '20071211')
insert into #Cases values (824, 1, '20071213')
create table #Data (caseID nvarchar(5), scenarioID nvarchar(15), itemID nvarchar(2), itemvalue decimal (18,0))
data in #Codes:
itemID standarditem
1 1
2 0
3 1
4 1
5 0
data in #Cases:
caseID scenarioID createdate
823 1 2007-12-10 00:00:00.000
823 2 2007-12-11 00:00:00.000
824 1 2007-12-13 00:00:00.000
desired result in #Data:
caseID scenarioID itemID itemvalue
823 1 1 <null>
823 1 3 <null>
823 1 4 <null>
823 2 1 <null>
823 2 3 <null>
823 2 4 <null>
824 1 1 <null>
824 1 3 <null>
824 1 4 <null>
Thanks in advance for your help.
How would I update a table where id = a list of ids?Do I need to parse the string idList? I am being passed a comma seperated string of int values from a flex application.example: 1,4,7,8 Any help much appreciatedBarry 1 [WebMethod]
2 public int updateFirstName(String toUpdate, String idList)
3 {
4 SqlConnection con = new SqlConnection(connString);
5
6 try
7 {
8 con.Open();
9 SqlCommand cmd = new SqlCommand();
10 cmd.Connection = con;
11 cmd.CommandText = "UPDATE tb_staff SET firstName = @firstName, WHERE id = @listOfIDs";
12
13
14
15 SqlParameter firstName = new SqlParameter("@firstName", toUpdate);
16 SqlParameter listOfIDs= new SqlParameter("@listOfIDs", idList);
17
18
19
20 cmd.Parameters.Add(firstName);
21 cmd.Parameters.Add(listOfIDs);
22
23 int i = cmd.ExecuteNonQuery();
24 con.Close();
25 return 1;
26
27 }
28 catch
29 {
30 return 0;
31 }
32 }
33
34
Hi,
I am working on a SQL Server table designed by a partner company and cannot change the data structures. There is a table with a list of people available for calling out to a security system (keyholders).
From a web form, I need to allow my users to change the telephone calling order of the keyholders in the table.
The two important fields are AccountCode nVarChar and CallOrder nVarChar - where AccountCode + CallOrder must be unique.
As an example, the table may contain records with the following data..
1234, 1, Fred
1234, 2, Bert
1234, 3, Bob
If the user wants to make Bob the number 1 keyholder, Fred number 2 and Bert number 3 - what is the best practice for me to approach the update ?
Is this a job for ADO.Net or T-SQL ?
Thanks in advance.
Steve.
I am new here, and I am sure this is a simple query, but im being forcefed database chores from my job, so i have to teach this stuff to myself/get help from places like this,
I need help with a query,
lets say that there are columns a,b,c,d,e,f,g
if columns c,d,e are the same, than I want the info in column g changed to the info in column b in the first record of that group
the reason I am doing this is,
I have like items (sku's) grouped in my database, and i want to create a blanket part number for skus that have matching descriptions which is the information in colums c,d,e,
I want to link them to the part number of the first product with that description, and add that part number in a new column at the end of the grouped SKU's record
this is what i start out with
a b c d e f g
2 4 5 6 9 8
2 5 5 6 9 9
2 7 5 6 0 5
1 2 3 4 5 6
1 3 3 4 5 7
1 4 3 4 5 8
1 5 3 4 5 9
i want to end up with
a b c d e f g
2 4 5 6 9 8 4
2 5 5 6 9 9 4
2 7 5 6 0 5 7
1 2 3 4 5 6 2
1 3 3 4 5 7 2
1 4 3 4 5 8 2
1 5 3 4 5 9 2
I have put this thread on as a follow up discussion to some concepts that surfaced here:http://www.dbforums.com/showthread.php?t=1208316here:http://www.dbforums.com/showthread.php?t=1604936and also here:http://www.dbforums.com/showthread.php?t=1606555 The gist is: Is it ever correct excusable advisable to pass a non-normalised csv list of data to a stored procedure? When I first asked this I thought it was simply an implementation issue. Pat thinks it is but he also holds an ideological objection too. If I seem a little obsessed with this topic it is simply thata) I don't want to hijack yet another threadb) I am Anyway - I PMed Pat about this and he (quite rightly) suggested I post publicly. So here we go: I think I have struggled a little getting my head round the concepts you are using. And here (I think) is why: Normalisation starts with a logical model and ends with the physical implementation of the database (as I see it...). Relational theory pervades these two but extends further to SQL and database objects (views, sprocs etc). I work exclusively (with the exception of some legacy I stuff I simply must update) with the sproc database-API methodology. As such, my databases are abstracted from the other tiers. Now - I think this is the crux of it for me - I typically think of the sproc API as another tier (perhaps tier 1.5). Although the database itself and the objects that manipulate it are housed by a single application I don't actually think of the sprocs as part of the database. They are dependant upon it and inextricably intertwined with it but they are not a part of it in my eyes. As such, I don't worry about producing denormalised output and I have never (up until you got me thinking about it) worried about accepting non-normalised input. The sproc is not the database. The sproc is just a code procedure that acts upon the database. As such - if it accepts a typless, denormalised input but subsequently types (validates) it and normalises it before it makes any changes to the data within the database then I struggle to see the scale of the problem. I am writing as something of a stickler for normalisation myself - I think that rather than querying how closely one should adhere to normalisation principles I am pondering at what point these principles come into play. As a follow up question - if SQL Server sprocs were to accept (for example) a table variable as an argument would this meet your requirement (since the data is verified and normalised before it gets to the sproc) or would you require a solution like this to be an ANSI Standard too? So - that's the nub of it - comments welcome ona) The practicality of passing csv lists to sprocs and subsequently parsing them and b) How "correct" or "incorrect" this is. Blindman and Pat both object on both counts (I think).Thrasmachus didn't have a problem with it (however to be fair that was a very early response to a specific question I asked). I don't hold a particularly strong opinion either way – in fact the “loop through the changes at the client and fire the sproc n times” route is probably much easier to code in any case - I'm just interested in the idea that it violates relational theory in some way.
View 13 Replies View RelatedHi, I need to update column week14 in table PastWeeks with data from Eng_Goal and then result of some calculation from table AverageEngTime in the row Goal and Used respectively. I used the following and was not successful. Please advice. Thank you.
Update Pastweeks
set
week14 = (SELECT Eng_Goal,((Mon_Day + Mon_Night + Tue_Day + Tue_Night + Wed_Day + Wed_Night + Thu_Day + Thu_Night + Fri_Day + Fri_Night + Sat_Day + Sat_Night + Sun_Day + Sun_night)* 100/168) FROM AverageEngTime where Shifts = 'Average')
where Weeks = ('Goal','Used' )
Hi,New to writing sql scriptI get this error in my sql scriptServer: Msg 512, Level 16, State 1, Line 1Subquery returned more than 1 value. This is not permitted when thesubquery follows =, !=, <, <= , >, >= or when the subquery is used asan expression.The statement has been terminated.I want to write a single sql script which will update column 1 (FK)in table A with column 1 (PK) in table BHere's an example of what I need to doTable DATA_A-------------column C1 (Foreign Key)111111222222333333334455Table DATA_B------------column C1 (Primary Key) Column C2 Column C311 ABC NULL12 ABC 2004-12-1222 EFG NULL23 EFG 2003-12-1233 HIJ NULL34 HIJ 2003-12-1244 KLM 2005-02-0255 JJJ NULLI need to update Table DATA_A set column C1 with 11 data to point toTable DATA_B column C1 with 12 data. Currently, the problem is theTable DATA_A Column C1 is pointing to the wrong primary key which hasNULL data in COLUMN C3. I need to point to the correct Primary Key withDate filled in Column 3. The two primary key is tied together bycolumn C3.Here's my SQl scriptUPDATE DATA_A SET C1 =(SELECT C1 FROM DATA_BWHERE C2 in(SELECT B1.C2 FROM DATA_B B1WHERE EXISTS(SELECT * FROM TABLE_B B2 WHERE B2.C3 is NOT NULL)AND EXISTS(SELECT * from TABLE_B B2 WHERE B2.C3 is NULL)AND B2.C2 = B1.C2GROUP BY B1.C2HAVING COUNT(B1.C2) = 2)AND C3 IS NOT NULL)WHERE(SELECT C1 FROM DATA_BWHERE C2 in(SELECT B1.C2 FROM DATA_B B1WHERE EXISTS(SELECT * FROM TABLE_B B2 WHERE B2.C3 is NOT NULL)AND EXISTS(SELECT * from TABLE_B B2 WHERE B2.C3 is NULL)AND B2.C2 = B1.C2GROUP BY B1.C2HAVING COUNT(B1.C2) = 2)AND C3 IS NULL)Thanks - Been struggle at this for a whileMLR
View 1 Replies View RelatedHi,
I am preparing a project that contains multiple tables, because of huge operations to be performed, so i split these into two forms. I used one form for "Inserting Data in the multiple tables" and another form used for "Updating and Deleting Data from the multiple tables". I used a common identification number in all the tables are "License_Number" for inserting, retreving, updating and deleting the data. I used tabcontrol to place lot of controls on it.
The form-1 which is used for Inserting, works fine.
The problem occuring in the second form which i used for updating.
The following code fill the data into the Textboxes and as well as in the datagridview. Below two tables "SetBack_Details" and "Far_Details" are fill the data in datagridview.
Private Sub FillDataFromAllTables(ByVal LicenseNumber As Long)
Try
'Fill Owner Personal Details Data
CheckOwnerID = RetrieveOwnerIDFromPD(LicenseNumber)
Me.Owner_Personal_DetailsTableAdapter.Fill(Me.LicenseFARModifyDataSet.Owner_Personal_Details, CheckOwnerID)
'-------------------------------------------------------------------
'Fill Project Details Data
Me.ProjectDetailsTableAdapter.Fill(Me.LicenseFARModifyDataSet.ProjectDetails, LicenseNumber)
'-------------------------------------------------------------------
'Fill Consultant Details Data
Me.Consultant_DetailsTableAdapter.Fill(Me.LicenseFARModifyDataSet.Consultant_Details, LicenseNumber)
'Fill Applicant Details Data
Me.Applicant_DetailsTableAdapter.Fill(Me.LicenseFARModifyDataSet.Applicant_Details, LicenseNumber)
'-------------------------------------------------------------------
'Fill Property Details Data
Me.Property_DetailsTableAdapter.Fill(Me.LicenseFARModifyDataSet.Property_Details, LicenseNumber)
'-------------------------------------------------------------------
Me.SetBack_DetailsTableAdapter.Fill(Me.LicenseFARModifyDataSet.SetBack_Details, LicenseNumber)
'-------------------------------------------------------------------
Me.FAR_DetailsTableAdapter.Fill(Me.LicenseFARModifyDataSet.FAR_Details, LicenseNumber)
Catch ex As Exception
MessageBox.Show(ex.Message.ToString, "Project Details")
End Try
End
The data which i retrieves in a single record easily updated but problem occurs in DataGridView.
The data which binds to textboxes is easily updating because of a single record. Example is below
UPDATE PROJECTdETAILS SET PNAME=@PNAME, ZONE=@ZONE, ZONEAREA=@ZONEAREA WHERE LicenseNumber=@LicenseNumber
The data which binds to datagridview is problem. I know the following statement is not appropriate to update the data rows in datagridview.
UPDATE SETBACK_DETAILS SET DIRECTION=@DIRECTION, BORDERS=@BORDERS, DIMENSION=@DIMENSION WHERE LICENSENUMBER=@LICENSENUMBER
What happening to the above statement is , all rows with this LICENSENUMBER contains same data. Before updating data contains in different information but after updating which ever last row is updating, it contains the last row values to all records.
Example :
101 xyzcompany X-Road X-Area
101 xyzcompany X-Road X-Area
101 xyzcompany X-Road X-Area
101 xyzcompany X-Road X-Area
101 xyzcompany X-Road X-Area
.
.
The following code i used to save the data
Private Sub SaveData()
Me.ValidateChildren()
Me.ProjectDetailsBindingSource.EndEdit() 'EndEdit for Project Details
Me.Consultant_DetailsBindingSource.EndEdit() 'EndEdit for Consultant Details
Me.Applicant_DetailsBindingSource.EndEdit() 'EndEdit for Applicant Details
Me.Property_DetailsBindingSource.EndEdit() 'EndEdit for Property Details
'Me.SetBack_DetailsBindingSource.EndEdit() 'Endedit for SetBack_Details
Me.ProjectDetailsTableAdapter.Update(Me.LicenseFARModifyDataSet.ProjectDetails) 'Update Project Details
Me.Consultant_DetailsTableAdapter.Update(Me.LicenseFARModifyDataSet.Consultant_Details) 'Update Consultant Details
Me.Applicant_DetailsTableAdapter.Update(Me.LicenseFARModifyDataSet.Applicant_Details) 'Update Applicant Details
Me.Property_DetailsTableAdapter.Update(Me.LicenseFARModifyDataSet.Property_Details)
'Me.SetBack_DetailsTableAdapter.Update(Me.LicenseFARModifyDataSet.SetBack_Details)
End Sub
Please help me in the regard how to update the data in DataGridView which contains multiple recrods with different information for a single LicenseNumber. I try so many times with different sort of techniques but i fails, sometimes i get concurrency violation error and many more.
Hope anybody understand my problem. Please help me in this regard.
Thanks.
Best Regards,
Kashif Chotu
hi everybody,
i have an application that generate a lot rows from 1 mellion to 2 mellions rows
i wana insert this record in MS SQL server in a fast way
i am currentlly loop through this records while it is loaded in dataset
building a command text that generate insert query for each row
and run it against SQL server
but it takes a lot of time to be finished
is there r a way to bulk insert this data?
thanks 4 ur help.
Bolos
Does anybody know how to insert multiple rows into a table with one query string(i'm using sqlServer)?
Thanx,
David
Hi, would like to know if there are any links or sample code to learn how to Insert multiple rows with 1 sql statement.Also, can the inserted values' source be from a table in another database table or from a dataset?I am actually trying to insert about 117 rows of data.Table 1======UID Primary Key TeamCode a code value representing different teams Week will equal to 2Points nullable valuee.g.Table 1======UID TeamCode Week Points1 A1 1 1002 A2 1 99trying to insert into table 1Table 1========UID TeamCode Week Points1 A1 1 1002 A2 1 993 A1 2 null4 A2 2 nulletc...As you can see, UID is primary key, TeamCode may repeat according to week value Week is a constant Points will be nullHow can I do that with a single Insert Command? Thank you for your help. :)
View 5 Replies View RelatedI am using ASP.NET 2.0 WebForms and I was trying to use a DataSet to add rows programatically without adding the actual records to the MS SQL Server Databases. Is this possible or should I be doing this another way?
DataSet myDS = new DataSet();DataTable myTable = new DataTable("table1");myTable.Columns.Add("col1", typeof(string));myDS.Tables.Add(myTable);myTable.Rows.Add("MyValue");
Thanks.
OK, I am trying to update a particular column with a numerical number. Here is the query I am using.
UPDATE viewerblock SET dummycat = (?) WHERE dummyproduct = 0
I am trying to number dummycat row to a certain number for example
dummycat-----------------dummyproduct
1--------------------------0
2--------------------------0
3--------------------------0
4--------------------------0
5--------------------------0
6--------------------------0
do you see what i am trying to do? I am simply trying to number the dummycat column where ever dummyproduct = 0.
is this possible to do?
I need to update a empty column in our SQL database with the login ID for employees of our company.The table is called SY01200 and were I need to put the login ID is column INET5, and the login ID is just me stripping off the company's email address(removing the @company.com), and I need to update the INET5 column only where Master_Type = 'EMP'
And here is the Query that I am using to strip the email select LEFT(convert(varchar(40),EmailToAddress),LEN(convert(varchar(40),EmailToAddress))-14) As LoginName from sy01200 where Master_Type = 'emp'And here is what I thought the Query would be to update however I got and error saying more than 1 arguement returned
UPDATE sy01200
SET INET5 = (select LEFT(convert(varchar(40),EmailToAddress),LEN(convert(varchar(40),EmailToAddress))-14) As LoginName from sy01200 where Master_Type = 'emp')
WHERE Master_Type = 'EMP'
I have a production table with 400 million rows.
I have a staging table which has 48 million rows. This data is the same as the production data, except one column has a different value.
Create Table Production
(
Id Int Identity(1,1),
Code Varchar(20),
ReferenceSequence int
)
-- Staging Table
Create Table Staging
(
Code Varchar(20),
NewSequence int
)
I need to update the production table with the newSequence value from staging to replace the ReferenceSequence. I.e:
Update Production
Set ReferenceSequence = Staging.NewSequence
From Staging
where Production.Code = Staging.CodeHowever, updating 48 million rows at once will generate a lot of logging!
How can I do 1 million rows at a time, commit the changes then do the next million?
I've tried some of the examples on the following page [URL], but they look to just update the tables with the same values.
Hi,
I have a table called "tblProducts" with following fields:-
ProductID(Pk, AutoIncrement), ProductCode(FK), ProdDescr.
So to the above table I have added a new field/column named "ProdLongDescr(varchar, Null)"
So, I need to populate this newly added column with specific values for each row depending on "ProductCode" which is different forevery row. The problem is that I have 25 rows.So instead of Writing 25 individual update scripts, is there a way in which single query will do the same job instead of writing one update query for each row ?. If so can some one guide me how to achieve that OR point to me a good resource.
Below are a couple of Individual update scripts I Wrote. "ProductCode" is different for all 25 rows.
Update tblValAdPackageElement SET ProdLongDescr = 'Slideshows' WHERE ProductCode = 'SLID'
And szElementDescr='Slideshow'
if @@error <> 0
begin
goto ErrPos
end
Update tblValAdPackageElement SET ProdLongDescr = 'CategorySlideshows' WHERE ProductCode = 'SLDC'
And szElementDescr='CategorySlideshow'
if @@error <> 0
begin
goto ErrPos
end
Thanks,
Hi all..
First of all Thanks for all the help, I received over the years from MSDN..
Here is my new problem...
I have a SQL table like following table..
State
City
StartDt
EndDt
1
AK
ANCHORAGE
4/1/2007
12/31/2049
2
AK
ANCHORAGE
4/1/2007
12/31/2049
3
AK
ANCHORAGE
5/1/2006
3/31/2007
4
AK
ANCHORAGE
5/1/2006
3/31/2007
5
AK
ANCHORAGE
6/1/2004
4/30/2006
6
AK
ANCHORAGE
6/1/2004
4/30/2006
7
AK
COLDFOOT
10/1/2006
12/31/2049
8
AK
COLDFOOT
10/1/1999
12/31/2049
Now here is what I want to do€¦
1> Sort the table based on Start Date (the picture shown is already sorted..) for example first 6 rows for AK - Anchorage
2> select the rows with same city (rows 1-6)
3> Select the rows with distinct start date (rows 1-3-5)
4> Change the End Date of the second selected row (row 3 in this case) to I day below the start date of 1 selected row. (4/1/2007 €“ 1 day = 3/31/2007)
5> proceed till end of selected rows.. 1-3-5
6> do the same thing for rows 2 and 4.
7> follow the same procedure for rest of the file.
The selected file should look like this when done..
State
City
StartDt
EndDt
1
AK
ANCHORAGE
4/1/2007
12/31/2049
2
AK
ANCHORAGE
4/1/2007
12/31/2049
3
AK
ANCHORAGE
5/1/2006
3/31/2007
4
AK
ANCHORAGE
5/1/2006
3/31/2007
5
AK
ANCHORAGE
6/1/2004
4/30/2006
6
AK
ANCHORAGE
6/1/2004
4/30/2006
7
AK
COLDFOOT
10/1/2006
12/31/2049
8
AK
COLDFOOT
10/1/1999
9/30/2006
Is there way to do this in SSIS? any recommened appprach>?
Any help with this is highly appreciated..
Thank You..
Why does this code tell me that I inserted 2 rows when I really only inserted one? I am using SQL server 2005 Express. I can open up the table and there is only one record in it. Dim InsertSQL As String = "INSERT INTO dbCG_Disposition ( BouleID, UserName, CG_PFLocation ) VALUES ( @BouleID, @UserName, @CG_PFLocation )"Dim Status As Label = lblStatus
Dim ConnectionString As String = WebConfigurationManager.ConnectionStrings("HTALNBulk").ConnectionString
Dim con As New SqlConnection(ConnectionString)
Dim cmd As New SqlCommand(InsertSQL, con)
cmd.Parameters.AddWithValue("BouleID", BouleID)
cmd.Parameters.AddWithValue("UserName", UserID)
cmd.Parameters.AddWithValue("CG_PFLocation", CG_PFLocation)
Dim added As Integer = 0
Try
con.Open()
added = cmd.ExecuteNonQuery()
Status.Text &= added.ToString() & " records inserted into CG Process Flow Inventory, Located in Boule_Storage."
Catch ex As Exception
Status.Text &= "Error adding to inventory. "
Status.Text &= ex.Message.ToString()
Finally
con.Close()
End Try Anyone have any ideas? Thanks