GridView Based On SQLServerDataSource Using A Select Union Statement, Impacts On Update And Insert?
Jan 3, 2006
I have a GridView dispalying from a SQLServerDataSource that is using a SQL Select Union statement (like the following):
SELECT
FirstName,
LastName
FROM
Master
UNION ALL
SELECT
FirstName,
LastName
FROM
Custom
ORDER BY
LastName,
FirstName
I am wondering how to create Update and Insert statements for this SQLServerDataSource since the select is actually driving from two different tables (Master and Custom). Any ideas if or how this can be done? Specifically, I want the Custom table to be editable, but not the Master table. Any examples or ideas would be very much appreciated!
Thanks,
Randy
View 5 Replies
ADVERTISEMENT
Apr 23, 2008
I have built an Advanced Search page which allows users to select which columns to return (via checkbox) and to enter search criteria for any of the selected columns (into textboxes). I build the SQL statement from the properties of the controls. Works great.
My problem is getting my gridview control to play nicely. At first I used a SqlDataReader and bound the gridview to it, thus giving me the ability to run new SQL statements through it (with different columns each time). Worked nicely. But, per Microsoft, sorting can only be done if the gridview is bound to a datasource control like the SqlDataSource. So I wrote the code to handle sorting. No big deal; worked nicely. But I could not adjust the column widths programmatically unless bound to a datasource control like the SqlDataSource. And could not figure out a work around.
So, I decided to use the SqlDataSource. Works great. Except, I cannot figure out how to run a new SELECT statement through the SQLDataSource and have the gridview respond accordingly. If I try to return anything other than the exact same columns defined declaratively in the html, it pukes. But I need to be able to return a new selection of columns each time. For example, first time through the user selects columns 1,2,3,4 – the gridview should show those 4 columns. The second time the user selects columns 2,5,7 – the gridview should those 3 columns (and ONLY those 3 columns). Plus support selection and sorting.
I am desperate on this. I've burned 2.5 days researching and testing. Does anyone have any suggestions?
Thanks,
Brad
View 9 Replies
View Related
Jan 9, 2015
Ok I have a query "SELECT ColumnNames FROM tbl1" let's say the values returned are "age,sex,race".
Now I want to be able to create an "update" statement like "UPATE tbl2 SET Col2 = age + sex + race" dynamically and execute this UPDATE statement. So, if the next select statement returns "age, sex, race, gender" then the script should create "UPDATE tbl2 SET Col2 = age + sex + race + gender" and execute it.
View 4 Replies
View Related
Aug 11, 2007
here is my code:
Dim cn As New System.Data.SqlClient.SqlConnection(ConfigurationManager.ConnectionStrings("LocalSqlServer").ToString())
cn.Open()
Dim adapter1 As New System.Data.SqlClient.SqlDataAdapter()
adapter1.SelectCommand = New Data.SqlClient.SqlCommand("update aspnet_Membership_BasicAccess.Products
set id = '" & textid.Text & "', name = '" & textname.Text & "', price = '" & textprice.Text & "', description = '" &
textdescription.Text & "', count = '" & textcount.Text & "', pictureadd = '" & textpictureadd.Text & "', artist = '" &textartist.Text & "', catergory = '" & textcategory.text & "' where id = " & Request.Item("id") & ";", cn)
cn.Close()
Response.Redirect("database.aspx")
it posts and the page loads but the data is still the same in my datagrid. what could be wrong with this simple statement... i've tried testing the statement above with constant values of the correct type but i don't think that matters because the SqlCommand() accepts a string only anyways.. doesn't it?
View 5 Replies
View Related
May 6, 2007
Hello All
I have had asked the same question in another post, i didnt get answer to it i might have had asked it wrongfully
Soo the question is: When creating a SQLDataSource in the wizard you get to the pont where you select the option . It says that by using this datasource you can select to update delete and insert. So my question is if i am creating a select statement to reterieve the data from the Table, then what does it do it do if my intention is to only reterie the data. Or what is the other way that it could be helpful to me ??
thanks all I hope it make sence, if not I wrill write another post to bring step by step info into it.
View 1 Replies
View Related
May 5, 2008
Hello everyone,
I have a view, NAS_vPosition that has a coloumn vLogin_Acting and I want to use the user.identity.name to select the row from this table that matches.
So far i have tried:
SelectCommand = "Select * FROM NAS_vPosition WHERE vLogin_Acting = ' <%=User.Identity.Name %> ' "
with no success.
Any help is appreciated
View 6 Replies
View Related
Mar 31, 2006
I have 3 tables One table is the order Table, Bill to table and ship to table
I have to Views created as followed
This query uses the Ship to table to pull the ship to information to the shipping system.
SELECT Cust_address.NAME, Cust_address.ADDR_1, Cust_address.ADDR_2, Cust_address.ADDR_3, Cust_address.CITY, Cust_address.STATE, Cust_address.ZIPCODE, Cust_address.COUNTRY, Cust_address.SHIP_VIA, customer_order.ID
FROM Cust_address INNER JOIN customer_order ON (Cust_address.CUSTOMER_ID = customer_order.CUSTOMER_ID) AND (Cust_address.ADDR_NO = customer_order.SHIP_TO_ADDR_NO);
This query uss the Bill to as the ship to inforamtion
SELECT CUSTOMER.ID, CUSTOMER.SHIPTO_ID, CUSTOMER.NAME, CUSTOMER.ADDR_1, CUSTOMER.ADDR_2, CUSTOMER.ADDR_3, CUSTOMER.CITY, CUSTOMER.STATE, CUSTOMER.ZIPCODE, CUSTOMER.COUNTRY, CUSTOMER.SHIP_VIA, customer_order.ID, customer_order.SHIP_TO_ADDR_NO
FROM CUSTOMER INNER JOIN customer_order ON CUSTOMER.ID = customer_order.CUSTOMER_ID;
I need this infroamtion in one table which I have done in the UNION statement as followed:
SELECT Cust_address.NAME, Cust_address.ADDR_1, Cust_address.ADDR_2, Cust_address.ADDR_3, Cust_address.CITY, Cust_address.STATE, Cust_address.ZIPCODE, Cust_address.COUNTRY, Cust_address.SHIP_VIA, customer_order.ID
FROM Cust_address INNER JOIN customer_order ON (Cust_address.CUSTOMER_ID = customer_order.CUSTOMER_ID) AND (Cust_address.ADDR_NO = customer_order.SHIP_TO_ADDR_NO)
UNION ALL
SELECT CUSTOMER.NAME, CUSTOMER.ADDR_1, CUSTOMER.ADDR_2, CUSTOMER.ADDR_3, CUSTOMER.CITY, CUSTOMER.STATE, CUSTOMER.ZIPCODE, CUSTOMER.COUNTRY, CUSTOMER.SHIP_VIA, customer_order.ID
FROM CUSTOMER INNER JOIN customer_order ON CUSTOMER.ID=customer_order.CUSTOMER_ID;
Here is the problem when I pull information out of the ship to table I get 2 results as followed My key field to pull this information is the Last field Custoemr ID this custoemr ID exist in both tables but contains different information I want to ONLY pull in the info that I need in this case it would be the first line that is the Correct shipping information.
NAMEADDR_1ADDR_2ADDR_3CITYSTATEZIPCODECOUNTRYSHIP_VIAID
DIEBOLD INC (4076A)ATTN: RANCE AARON343 MANOR DRPACIFICACA9404418932
DIEBOLD, INCOHUPS #88X08X18932
MY POINT: Is there a way to select a over all DISTINCT order ID.
Thank you for any help hope this make sense!
View 4 Replies
View Related
Aug 9, 2007
I am not completly sure if I have this posted in the right forum so if I don't just let me know and I will move it. Here is my problem. I need to be able to use the WHERE keyword more than once in one SELECT statement and have not been able to figure this out. I need to be able to first search for information under one column with the WHERE keyword like usual and then I need to be able to search the returned results with another WHERE keyword to narrow down the returned results. I tried writing two SELECT statements and joining them with a UNION ALL keyword like this:
"SELECT LI.ID, LI.CNID, CD.ID, LI.FDName, LI.FDR " & _
"FROM FinalDrive AS LI INNER JOIN CarData AS CD " & _
"ON LI.CNID = CD.ID WHERE LI.CNID = '1'" & _
"UNION ALL" & _
"SELECT LI.ID, LI.CNID, CD.ID, LI.FDName, LI.FDR " & _
"FROM FinalDrive AS LI INNER JOIN CarData AS CD " & _
"ON LI.CNID = CD.ID WHERE LI.FDName = 'Car1"
This hasn't worked and I didn't expect it to. Everytime I run this code I get an Unhandled SqlExecption:
Invalid column name 'Towing'.
Can anyone help me with figuring out how to use the WHERE keyword more than once. I am using Visual Basic.Net with ADO.Net. Thanks!
View 5 Replies
View Related
Jun 4, 2007
i have a case : i'm using a select statement with SqlCommand and save the results in the SQLDataAdapterand using the data table I post the result to the gridview and show it there....my question is what should I do to append string to the SQL Command??
View 2 Replies
View Related
May 21, 2008
if there are 2 insert statement
insert ... select * from table1 union all select * from table2
or
insert ... select * from table1
insert ... select * from table2
pls advise which 1 is faster ...
View 14 Replies
View Related
Aug 29, 2006
I am currently having this problem with gridview and detailview. When I drag either onto the page and set my select statement to pick from one table and then update that data through the gridview (lets say), the update works perfectly. My problem is that the table I am pulling data from is mainly foreign keys. So in order to hide the number values of the foreign keys, I select the string value columns from the tables that contain the primary keys. I then use INNER JOIN in my SELECT so that I only get the data that pertains to the user I am looking to list and edit. I run the "test query" and everything I need shows up as I want it. I then go back to the gridview and change the fields which are foreign keys to templates. When I edit the templates I bind the field that contains the string value of the given foreign key to the template. This works great, because now the user will see string representation instead of the ID numbers that coinside with the string value. So I run my webpage and everything show up as I want it to, all the data is correct and I get no errors. I then click edit (as I have checked the "enable editing" box) and the gridview changes to edit mode. I make my changes and then select "update." When the page refreshes, and the gridview returns, the data is not updated and the original data is shown. I am sorry for so much typing, but I want to be as clear as possible with what I am doing. The only thing I can see being the issue is that when I setup my SELECT and FROM to contain fields from multiple tables, the UPDATE then does not work. When I remove all of my JOIN's and go back to foreign keys and one table the update works again. Below is what I have for my SQL statements:------------------------------------------------------------------------------------------------------------------------------------- SELECT:SELECT People.FirstName, People.LastName, People.FullName, People.PropertyID, People.InviteTypeID, People.RSVP, People.Wheelchair, Property.[House/Day Hab], InviteType.InviteTypeName FROM (InviteType INNER JOIN (Property INNER JOIN People ON Property.PropertyID = People.PropertyID) ON InviteType.InviteTypeID = People.InviteTypeID) WHERE (People.PersonID = ?)UPDATE:UPDATE [People] SET [FirstName] = ?, [LastName] = ?, [FullName] = ?, [PropertyID] = ?, [InviteTypeID] = ?, [RSVP] = ?, [Wheelchair] = ? WHERE [PersonID] = ? ---------------------------------------------------------------------------------------------------------------------------------------The only fields I want to update are in [People]. My WHERE is based on a control that I use to select a person from a drop down list. If I run the test query for the update while setting up my data source the query will update the record in the database. It is when I try to make the update from the gridview that the data is not changed. If anything is not clear please let me know and I will clarify as much as I can. This is my first project using ASP and working with databases so I am completely learning as I go. I took some database courses in college but I have never interacted with them with a web based front end. Any help will be greatly appreciated.Thank you in advance for any time, help, and/or advice you can give.Brian
View 5 Replies
View Related
Jul 20, 2005
Hello,I have 2 ways of updating data I'm using often1) via a cursor on TABLE1 update fields in TABLE22) via an some of variables ...SELECT @var1=FLD1, @var2=FLD2 FROM TABLE1 WHERE FLD-ID = @inputVARUPDATE TABLE2SET FLDx = @var1, FLDy = @var2WHERE ...Now I have a system with 2 databases and I need to update table DB2.TABbased on data in DB1.TAB. Instead of using 1 of the 2 ways I normally use,I thought it would be much easier to get the required data immediately fromDB1.TAB in the update-statement of DB2.TAB ... but the way to do thatconfuses me. I've checked books online and a lot of newsgrouppostingsgiving good information but still I keep getting errors like this ...The column prefix 'x.ADS' does not match with a table name or alias nameused in the query.while executing the following statement ...UPDATE DB2.dbo.TABSETFLD1 = x.FLD1,FLD2 = x.FLD2,...FROM DB1.dbo.TAB x, DB2.dbo.ADSWHERE DB2.dbo.TAB.REFID = x.IDOFTAB1 AND DB2.dbo.TAB.IDOFTAB2 =@InputParameterSo in DB2.TAB I have a field REFID reffering to the keyfield IDOFTAB1 oftable DB1.TABAND I only want to update the row in DB2.TAB with the unique keyfieldIDOFTAB2 equal to variable @InputParameterDo you see what I'm doing wrong?--Thank you,Kind regards,Perre Van Wilrijk,Remove capitals to get my real email address,
View 8 Replies
View Related
Feb 19, 2004
i am not getting a result back when i run the query below.
select * from users where DateCreated = '2004-02-19'
so i went into the table and looked at the record. for DateCreated field i have both date and time. ex: 2004-02-19 08:40:00
how can i select this record with out using the time in the select statement. what i want to see is how many users signed up for a day. any ideas?
View 5 Replies
View Related
May 5, 2015
I am attempting to run update statements within a SELECT CASE statement.
Select case x.field
WHEN 'XXX' THEN
UPDATE TABLE1
SET TABLE1.FIELD2 = 1
ELSE
UPDATE TABLE2
SET TABLE2.FIELD1 = 2
END
FROM OuterTable x
I get incorrect syntax near the keyword 'update'.
View 7 Replies
View Related
Jun 21, 2007
the following criteria.
i have the selection all done but am trying to figure out how to do the following:
if column4 < 0 then add column4 to column3, move 0 to column4;
if column3 < 0 then add column3 to column2, move 0 to column3;
if column2 < 0 then add column2 to column1, move 0 to column2;
add column3 to column4;
move column2 to column3;
move column1 to column2;
if column0 > 0 move column0 to column1, move 0 to column0 else move 0 to column1;
these are all numeric data types.
View 7 Replies
View Related
Jun 7, 2006
I am new to triggers and surely could use some help.
I can create a trigger to insert related records based on the main tables ID and insert that value into other related tables fine... but...
How do I create a trigger that can insert a record into one table for a columns given value and then insert a record into another table for another given value?
For instance:
New row...
Table1, Column1 (PK) has a value of 101
Table1, Column2 has a value of 'Blue'.
// When a new row is created in Table1 and Column2 has a value of 'Blue'...
I want to insert a new row into Table2 - with Table1 Column1's value.
// Now if Table1, Column2 has a value of 'Red' when the new row was created...
I want to insert a new row into Table3 - with Table1 Column1's value. Not Table2
This has to be inserted into one or the other tables based on column2's value, not both.
Then I want to populate the other related tables (Table4, Table5) with the regular insert statements based on Table1 Column1's value.
This (the conditional part above) has to work with an update to Table1 also.
So if someone came back to that record and changed Column2's value from 'Blue' to 'Red', it would have to delete the appropriate record in Table2 and then insert the new row into Table3 and visa-versa.
Can I do this with one trigger?
Thanks
View 8 Replies
View Related
Sep 20, 2005
Hi,I have a SQL query:select products.name, products.notes, products.purchase_date,products.serial_no, products.total_value, products.product_code,products.quantity, products.product_group, products.lhs1_name,lhs1.department, lhs1.address1, lhs1.city, lhs1.countryfrom products, lhs1where products.lhs1_id = lhs1.idI want to insert the results of this query into a table called 'temp' in thedatabase. I used to copy and paste this into excel then import it but itdoesn't always work.Is there a way to do it all in a SQL script. Please be aware that myknowledge of SQL is fairly basic so please explain things clearly.Thanks,Darren
View 4 Replies
View Related
Jul 9, 2015
I basically want to select all GRNID's from one table but they have to be between dates in another table.So I want all GRN's between two dates found in the ABSPeriodEndDate table. To find out the start date for the between clause I need to find the MAX Period then minus 1 and the max year. To find the end date of the between clause I want I need to find both the max period and year. But I want the DateStamp column to return the results for the between clause. My query is below:
SELECT tblGRNItem.GRNID
FROM tblGRNItem
INNER JOIN ABSPeriodEndDates ON tblGRNItem.DateCreated = ABSPeriodEndDates.DateStamp
WHERE tblGRNItem.DateCreated BETWEEN
(SELECT ABSPeriodEndDates.DateStamp FROM ABSPeriodEndDates WHERE ABSPeriodEndDates.DateStamp = (SELECT
[code]....
View 6 Replies
View Related
Jul 30, 2007
ID DATE(dd/mm/yy) TYPE QTYIN COST_IN_AMT COST_OUT_AMT(MOVING AVERAGE)
1 01/01/2007 PURCHASE 10 1000
2 01/01/2007 PURCHAES 5 1100
3 01/01/2007 SALES -5 *TobeCalculated
4 02/01/2007 Purchase 20 9000
5 02/01/2007 SALES -10 *TobeCalculated
5 02/01/2007 purchase 50 8000
6 03/01/2007 Sales -10 *TobeCalculate
7 01/01/2007 Purchase 20 12000
I have a table when user add new sales or puchase will be added to this table ITEM_TXNS. The above date is part of the table for a ProductID . (The field is removed here)
In order to calculate the balance amount using moving average, I must calculated the cost_out_amt first on the fly.
When user add new sales I also need to determine the cost/unit for a product id using moving average. The problem is I can not just use sum, because i need to determine cost_out_amt for each sales first which will be calculated on the fly.
The reason i dont store the cost_out_amt (instead calculate on the fly) because User could Edit the previous sales/purchase txn or Insert new sales for a previous date. Example THe record with ID 9. By Adding this txn with ID 9, would cause all the cost_out_amt will be incorrect (Using moving Average) if i store the cost_amout_out on entrying txn and need to be recalculated.
Instead I just want to calculate on the fly and able to determine the cost avr for a specific point of time.
Should I just use Cursor and loop all the record and calculate the cost or maybe I can just use on Select Statement?
View 20 Replies
View Related
Jul 24, 2015
Update statement based on the results from this SELECT:
SELECT RELQ_REL_VERSION_NM,
RELQ_RELEASE_Q_ID,
RELQ_STATUS_CD,
RELSP_RELEASE_STEP_ID,
RELSP_STEP_TYPE_CD,
RELSP_STATUS_CD
FROM RELEASE_QUEUE WITH (NOLOCK)
[Code] ....
I need to update all records where the
RELEASE_STEPS.RELSP_STATUS_CD = 'SKPD'TORELEASE_STEPS.RELSP_STATUS_CD = 'CMPS'
I imagine that the UPDATE statement will be something like:
UPDATE RELEASE_STEPS SET dbo.RELEASE_STEPS.RELSP_STATUS_CD = 'CMPS'
WHERE (
SELECT RELQ_REL_VERSION_NM,
RELQ_RELEASE_Q_ID,
RELQ_STATUS_CD,
RELSP_RELEASE_STEP_ID,
RELSP_STEP_TYPE_CD,
[Code] .....
View 4 Replies
View Related
Jul 18, 2014
I would like to change a value after the table updates inserts a new row based on the value of that column
Table Name is: MPanel
Col Names: RID, FPID.
I want to:
If column RID='16'
Then UPDATE FPID='1'
How can I trigger that update? Would this work:???
CREATE TRIGGER dbo.MyTrigLI
ON dbo.MPanel
FOR INSERT, UPDATE
AS
BEGIN
UPDATE [MPanel]
SET
FPID='1'
WHERE (UPDATE(RID='16')
END
View 9 Replies
View Related
May 27, 2015
I want to change Set clause of Update Statement dynamically based on some condition.
Basically i have 2 Update statments having same FROM clause and same JOIN clause.
Only diff is SET clause and 1 Where condition.
So i am trying to combine 2 Update statements into 1 and trying to avoid visit to same table twice.
Update t
Set CASE **WHEN Isnull(td.IsPosted, 0) = 0
THEN t.AODYD = td.ODYD**
*ELSE t.DAODYD = td.ODYD*
END
From #ReportData As t
Join @CIR AS tmp On t.RowId = tmp.Max_RowId
[Code] ....
But CASE statement is not working...
View 7 Replies
View Related
Oct 20, 2014
In the below code i want to use select statement for getting customer
address1,customeraddress2,customerphone,customercity,customerstate,customercountry,customerfirstname,customerlastname
from customer table.Rest of the things will be as it is in the following code.How do i do this?
INSERT INTO EMImportListing ("
sql += " CustId,Title,Description,JobCity,JobState,JobPostalCode,JobCountry,URL,Requirements, "
sql += " IsDraft,IsFeatured,IsApproved,"
sql += " Email,OrgName,customerAddress1,customerAddress2,customerCity,customerState,customerPostalCode,
[code]....
View 1 Replies
View Related
Oct 21, 2015
I have the below script to create directories based on my select statement.
create table students
(
f_name varchar(100),
l_name varchar(100)
);
insert into students values ('John','Smith'),
[Code] ...
The result I am getting is truncating the directory names as in the below:
View 4 Replies
View Related
Jul 14, 2014
I have a table that holds notes for item's. I'm want to do a select statement where one of my columns assigns a sequential value to each row based on the item number. Would like data to look like this where doc_no would be my row_number function:
item_no seq_no note doc_no
ABC 1 blah 1
ABC 2 blahh 1
ABC 3 bla3 1
XYZ 1 more n 2
XYZ 2 another 2
EFG 1 blahhh 3
View 2 Replies
View Related
Aug 25, 2006
I am having issues getting this to work. I have the user login to a page to
put in a request for vacation. When they login, I have a label that isn't
visible that is equal to their User.Identity.Name.
I select the user from the employee table where the username = the label User
Identity Name and pull in the emp_id which is the primary key that identifies
the user.
I need to insert the request into the request table with the emp_id from the
select statement, without showing the em_id on the screen. I tried using a
hidden field and assigning the emp_id as the value, but it isn't working.
Not sure if this is the best way to do this. Really new to ASP.NET 2.0 so I
really appreciate any help.
Thank you!
View 12 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
Dec 7, 2007
What I am attempting to do here is check to see if a record exists in a table, if so, I will update some fields, if not, I will insert a new record into the table. This is what I have so far, I was hoping someone could let me know if any of these elements are unnecessary. SELECT [IP] FROM [Table]if (strIP == @IP){ UPDATE [Table] SET [Column = value++]} else{ INSERT INTO [Table] (IP) VALUES (@IP) } I obviously left out parameters and whatnot, I am mainly concerned with the logic here. My apologies if this is something simple.
View 9 Replies
View Related
Jan 23, 2013
In one statement (or whatever is most efficient if not possible), how do I update the rows in table A (based on a where clause), and insert into table B, the data from those same number of rows.
For example,
Table A: columns: id , lastName, department, branch
Table B: columns: id, errorMessage, lastName
UPDATE table A by settting branch = 'ETC' where department IS NULL
INSERT INTO table b the rows found in the above UPDATE statement
Note: 1.To find the rows UPDATED in the first statement, it is not sufficient to search by branch='ETC', as that will encompass more rows.
2. I do not want to use triggers as these statements will be executed once in the entire process
View 11 Replies
View Related
Feb 8, 2008
How to Use select statement when using Update statement.
Eg: Update program_video set program_id=(select id from program where recordname='122'), stopnumber=stopnumber*-1 where stopnumber < 0
Thanks & Regards
Jagadeesh
View 7 Replies
View Related
Mar 6, 2008
Is it possible to use CASE statement with INSERT /UPDATE statement?this is what i am trying to do
i have a table like this
Field1 Field2 Field3 FieldType1 FieldType2 FieldType3
1-when there is no data for a given combination of Field2 and Field3,i need to do "insert".i.e the very first time and only time
2-Second time,when there is already a row created for a given combination of Field2 and Field3,i would do the update onwards from there on.
At a time value can be present for only one of the FieldType1,FieldType2,FieldType3) for insert or update
I am passing a parameter to the stored procedure,which needs to be evaluated and wud determine,which field out of (FieldType1,FieldType2,FieldType3)has a value for insert or update .this is what i am trying to do in a stored procedure
CREATE PROCEDURE dbo.StoredProcedure
( @intField1 int, @intField2 int, @intField3 int, @intFieldValue int , @evalFieldName varchar(4) )So i am trying something like
CaseWHEN @evalFieldName ="Fld1" THENINSERT INTO TABLE1 (Field2,Field3,fieldType1,FieldType2,FieldType3)values (@intField1,@intField2,@intField3,@intFieldValue,cast(null as int) fld2 ,cast(null as int) fld3)
CaseWHEN @evalFieldName ="Fld2" THENINSERT INTO TABLE1 (Field2,Field3,fieldType1,FieldType2,FieldType3)values (@intField1,@intField2,@intField3,cast(null as int) fld1 ,@intFieldValue,cast(null as int) fld3)
CaseWHEN @evalFieldName ="Fld3" THENINSERT INTO TABLE1 (Field2,Field3,fieldType1,FieldType2,FieldType3)values (@intField1,@intField2,@intField3,cast(null as int) fld1 ,cast(null as int) fld2,@intFieldValue)
END
similar trend needs to be followed for UPDATE as well..obiviousely its not working,gives me synatax error at case,when,then everywher.so can someone suggest me the alternative way?..i am trying to avoid writing stored procedure to insert/update for each individual fields..thanks a lot
View 8 Replies
View Related
Jun 26, 2006
Using ms sql 2000I have 2 tables.I have a table which has information regarding a computer scan. Eachrecord in this table has a column called MAC which is the unique ID foreach Scan. The table in question holds the various scan results ofevery scan from different computers. I have an insert statement thatworks however I am having troulbe getting and update statement out ofit, not sure if I'm using the correct method to insert and thats why orif I'm just missing something. Anyway the scan results is stored as anXML document(@iTree) so I have a temp table that holds the releventinfo from that. Here is my Insert statement for the temporary table.INSERT INTO #tempSELECT * FROM openxml(@iTree,'ComputerScan/scans/scan/scanattributes/scanattribute', 1)WITH(ID nvarchar(50) './@ID',ParentID nvarchar(50) './@ParentID',Name nvarchar(50) './@Name',scanattribute nvarchar(50) '.')Now here is the insert statement for the table I am having troublewith.INSERT INTO tblScanDetail (MAC, GUIID, GUIParentID, ScanAttributeID,ScanID, AttributeValue, DateCreated, LastModified)SELECT @MAC, #temp.ID, #temp.ParentID,tblScanAttribute.ScanAttributeID, tblScan.ScanID,#temp.scanattribute, DateCreated = getdate(),LastModified =getdate()FROM tblScan, tblScanAttribute JOIN #temp ONtblScanAttribute.Name =#temp.NameIf there is a way to do this without the temporary table that would begreat, but I haven't figured a way around it yet, if anyone has anyideas that would be great, thanks.
View 3 Replies
View Related
Jul 20, 2005
In MS Access I can do in one SQL statement a update if exists else ainsert.Assuming my source staging table is called - SOURCE and my targettable is called - DEST and both of them have the same structure asfollowsKeycolumns==========MaterialCustomerYearNonKeyColumns=============SalesIn Access I can do a update if the record exists else do a insert inone update SQL statement as follows:UPDATE DEST SET DEST.SALES = SOURCE.SALESfrom DEST RIGHT OUTER JOIN SOURCEON (DEST.MATERIAL = SOURCE.MATERIAL ANDDEST.CUSTOMER = SOURCE.CUSTOMER ANDDEST.YEAR = SOURCE.YEAR)This query will add a record in SOURCE into DEST if that record doesnot exist in DEST else it does a update. This query however does notwork on SQL 2000Am I missing something please share your views how I can do this inSQL 2000.ThanksKaren
View 6 Replies
View Related
