I have
table T2
id amount ss
1142
2151
3162
4172
table T3
idamount ss
131
142
251
362
472
481
491
I run
UPDATE t2, t3 SET t2.amount = t3.amount
WHERE t2.id1=t3.id and t3.ss=1;
T2 becomes
ID1amount ss
132
251
3162
492
but I want t2 to be
ID1amount ss
132
251
3162
4 82
how can I do that?
Thanks
Hi. I have a SQL statement currently ending with "ORDER BY sc.TypeID, sc.ObjetID DESC"where the TypeID can be of value 1,2,3,4,5,6,7,8, 9 and 10. What I would like to achieve is to selectively order the datas by TypeID, meaning in this case that I want ALL the TypeID of value "1" to come first and then all the rest ("2" and higher) un-ordered. This is important that the rest remains unordered by their TypeID as I only want them to be ordered by their ID (ObjetID DESC). Is that possible? If yes how?Thank you.Francis
View 4 Replies View RelatedHello all,
Is there a way to do a selective restore? I need to create a dev db and the prod backup is 17 gig and I have a space crunch on the server. My idea was to restore the complete backup and subsequently shrink the db size after truncating data in tables which are not frequently used? Is that a good idea. Any help will be appreciated.
Thanks in advance!
Hi,
I need to be able to export from an existing database, all fields with a certain column id. I have been pointed in the direction of bcp, however I am having difficulty finding the right syntax.
Thanks,
D.
(P.S. MSSQL 2000 Server)
Is it possible to use replication for inserts and updates only for some tables and inserts updates and deletes on others. I want to be able to delete data from the source database but not my replicated database. Does anyone know of a way to use replication to facilite this?
Thanks for any input you can provide.
I'm in the current mode of migration from WIn2k and I 'm trying to setup user where they are only allowed access to one table.
If I grant thenm read /write access to the db they can obviously see all of the db due to the size of the db I do not wish to go down the root of denying every table as there is over 100. Is there an easier way??
I was trying to use the case statement in where clause so that the comparison (> , <, =) can be made depending upon the value of the local variable @fl.
PLease tell me how to do that. Or suggest me how can I make selective comparison (<,>,=) in where clause depending upon a local variable. Please note that I donot want to use if/else satement and write select statement everytime based on the local variable as in that case the code length will be huge. Please suggest me a coensized query.
SQL that I queried:
use pubs
go
declare @fl int
select fl = 3
select * from titles
where case fl when 1 then price > 90
when 2 then price < 90
when 3 then price = 90
end
The following error is given
Server: Msg 170, Level 15, State 1, Line 7
Line 7: Incorrect syntax near '>'.
Can I transfer data from one database to the other for only selected records
pls help
thanks
sunthar
HI,
Is it possible in SQL Server to restore Databases partially?
In the sense, I want to backup just 2 tables from a database & restore them, instead of having to take a backup of the entire database & restore the entire database.
One way to do would be to export data & then importing data for only those selective tables, but I dont want to do that, bcause that would probably take more time & can be error prone.
Thanks
Raman.
I need to 'copy' selective (filtered) rows from table 'A' to table 'B' on a regular basis, but only if they do not exist in table 'B'.
If rows get deleted from table 'A' they must remain table 'B'. If rows change in table 'A' (unlikely) they should be update in table 'B'
What feature of SQL should I be using?
- triggerering a stored procedure on Insert
- replication
- SSIS
Hi.
I am using Visual Studio 2005 professional and SQL Server 2005 Express Edition.
I am having a bit of an issue at the moment with my project. I have a large SQL server database (over 600,000 rows of data decoded from a text datafile and stored by my C# program) where I need to select a series of distinct rows.
I have a datacolumn of varchar(10) called UID which is assigned to every row in my database. It's not a unique code so it can happen several times. By selecting a specific UID code, I can narrow down my selection to a specific range of rows (which are incremental in order). This range will contain many names.
What I want to do is find a UID which will have one of two names. I have so far done this:
SELECT DISTINCT F1.UID FROM MyDataBase F1
INNER JOIN MyDataBase F2 ON
F1.LineIndex < F2.LineIndex AND
F1.UID = F2.UID AND
F1.Names IN (Bob', 'John', 'Peter', 'Sarah', 'Anne', 'Stewart', 'David', 'Alan', 'Linda') AND
F2.Names IN ('Bob', 'John', 'Peter', 'Sarah', 'Anne', 'Stewart', 'David', 'Alan', 'Linda')
This will return any UID which contains at least two of these names. The LineIndex ensures that the comparison doesn't happen on the same row of data (it's a column set to type int with IDENTITY(1,1) which is also my PRIMARY KEY). This is partially what I want, the selection of UID rows which will have at least two names from the list. Also, the same name will never appear twice in the same UID list.
Now, the problem I am facing is that I want a select a UID range with names in the order I have specified, so if I want a UID list where the names I want are Peter and Linda, I only want to return a set of UID rows where Peter appears earlier in the returned rows than Linda.
It is possible that other UIDs have the same names, but in reverse order, starting Linda, Alan, David etc...John, Bob. I would want to discard these entries.
Can anyone please give me any hints as to how I could get around this?
Thanks in advance,
Sean
I've got a report linking to a SSAS cube. It has 2 row groups Region and Country. The Country group is a subset of the Region group and works fine, it collapses and expands ok.
Now, due to a boss who doesn't like the way this looks, I have to find another way to display it. I was wondering if it is possible to have a report parameter that could be selected and the appropriate column grouping be displayed depending on the value.
i.e. User selects Report Parameter value 'Region' and the Region row group becomes visible, the Country group becomes invisible. The exact opposite happening if the user selects Country.
I can't find any obvious way to do this. Any ideas?
Rob
Hi,
I am trying to do selective updates for rows where a column matches a column in another table. I want to do something like this, only 'this' does not work, and nothing else I could think of (I tried joins also) worked. What am I missing? I hope this explanation makes sense.
UPDATE queryresultsmodel SET queryresultsmodel.tableforcedoutdate = getdate()
Where Exists (Select tablename from queryresultsmodel q inner join orphanul o on q.tablename = o.name)
Thanks for any help,
Judith
We have a Table that contains a schedule. The schedule has certain pieces of information that are required to be updated by certain members of the
organization, specifically Shift, Start Date, and Line. Each record also has a column for the customer that it is for.
The schedule has a Column, [CSR] which lists the name of the person who is responsible for updating it. Format of that is domainnamejohndoe
What we would like to do is block domainnamejohndoe from being able to update or insert on any of the records that he is not responsible for.
So if he attempts to change the shift for example, it denies the change and possible pops up an error indicating that the change was blocked because he is not the responsible party.
Input in this is through a query which views the table data in MS Access. It cannot be moved to a Form without some serious redesign, so I cannot put the block in there. I assumed that this could be blocked with a Trigger that checks the [CSR] column, matches it to the logged in user through the suser_sname(suser_sid()) and then either allows or denies the update.
So is this a viable idea, or should I explore the move to an input form and make the change there in MS Access via VBA.
At first we started with just an audit trail, but I started to think that the audit trail would be needed only for records that should be allowed to be changed and that we should deny changes to those who are not allowed to make them.
Below is the audit trail trigger, I figure that the change should occur somewhere within that first IF statement, or make the first IF the second, and the first should be the permission check.
At this point I am stuck as to how to block the updates. I tried using Deny but that was not working. Filtering the results by only showing the logged in user their records is not an option as they need visibility to the entire schedule.
This is for SQL 2000 SP4/Windows 2000 SP4
CREATE TRIGGER audit_mschange
ON dbo.T_PP_Table_2
FOR update AS
IF (UPDATE([Start Dt]) OR Update(shift) or Update
(comments)
or Update(status) or Update (line))
BEGIN
INSERT INTO T_MS_RB_AUDIT
(ord_no,line_tank_old,line_tank_new,shift_old,shif t_new,comments_old,comments_new,status_old,status_ new,trx_dt,trx_username,
start_dt_old,start_dt_new)
SELECT ins.[PP Ord No],
del.line,ins.line,
del.shift,ins.shift,
del.comments,ins.comments,
del.status, ins.status,
getdate(),
suser_sname(suser_sid()),
del.[Start Dt],
ins.[Start Dt]
FROM inserted ins, deleted del
WHERE ins.[PP Ord No]=del.[PP Ord No] AND
ins.autonumber=del.autonumber AND
(ins.status <> del.status OR ins.[Start Dt] <>
del.[Start Dt] or ins.status <> del.status
or ins.comments <> del.comments or ins.line <>
del.line )
END
Say I have a report with 3 pages, each page has a different dataset and takes some processing time.
is it possible to give the user the option to just run the first page say if thats all they need?
Reduce the processing time of the report to only that that is necessary?
Hi,
I have a problem that I would like help with.
I have to write an SQL Server 2000 stored procedure that returns rows from a table (a SELECT with an ORDER BY). A front end system calls the stored procedure and displays the returned rows. If there is more than one screen's worth of data (ie more than 20 rows returned from the table) then there is a requirement that the stored procedure only returns the rows for the screen ie, for screen 1 I need to return rows 1-20, for screen2 I need to return rows 21-40, screen 3 = rows 41-60.
The screen number will be passed into the stored procedure so I can work out what rows to return, the only problem is how can I tell SQL Server to only select the required rows (ie, say rows 21 to 40 from the returned rows). I can't see any arguments that allow selective rows to be returned (apart from TOP).
Any ideas??
Regards,
Chris
Dear netters,We are looking to build a SQL Server database that will be hooked up toa Web server (WebLogic or a .NET).The database will house data for multiple customers, and therequirement is to have no customer see other customer data. Web serverwill be responsible for authenticating users (ids and passwords will bemaintained there/possibly stored in the database, but will not beactual database logins) -- and establishing pooled connections to thedatabase, using some sort of a proxy login.Ideally, we do not want to have the database itself visible to "theworld", it should reside behind a firewall, and be accessible throughthe Web server, which will do all authentication and routing.We want to have either tables or views defined in such a way that:1. Users can only see their own data and NEVER can see anybody else's.2. Users' access to the database is logged (this includes updates aswell as queries).3. We would like to provide some sort of a reporting mechanism -- whereusers can form their own queries. So, something like a Crystal or anAccess front end, that users are typically familiar with... The problemis that this requires users having direct access to the database (?),and opens up tables (unless, there is a way to create parameterizedviews that will always restrict access to a subset of records).Any advice on how to best implement this is greatly appreciated.Thank you in advance!
View 4 Replies View RelatedI am beginning design for a project to maintain an on-line archive of historical data for a production database, and then truncate old, rarely-used data from the production database. I am considering a method whereby we (A) replicate data changes from the production database to an archive database and (B) truncate old data from the production database according to predetermined business rules.
I am trying to determine whether SQL Server merge or transactional replication can be used to accomplish the replication portion of this requirement.
Given this scenario, there are two types of modifications that are made to the production database: (1) application inserts, deletes and updates which should be replicated, and (2) truncation deletes (as described in "B", above) which should not be replicated.
My question is: Will I be able to control the replication process such that application deletes are replicated, but "truncation" deletes are not? Could static row filters somehow be used to accomplish this?
Hi all.
I have been banging my head against a brick wall with this for a couple of days now. I am wrighting a custom front end for one of our SQLServer DB's and need to run a select agains the DateTime Field. The field Format is [DD/MM/YYYY HH:MM:SS]. I am tring to get the select to pull all the records on a set date only (keeping the time available but not selecting against it) so that the brass can see at what time on a given day an action was performed. Problem is, the way the programing software works, I can only build the SQL using string information. I have tried CONVERT and CAST both ways (converting the date to varchar and converting the string to date and also converting them both at the same time). Everything that I have tried returns an empty data set when used with the WHERE LIKE criteria (oh yeah, I'm also using LIKE % wildecards to retrive all the times against a specific date) but does populate when no filter is applied.
This is where my SQL is at just now:
SELECT Files.FileID, Useres.UserName, FileStatusLog.StatusCode, FileStatusLog.StatusDate
FROM (Files INNER JOIN FileStatusLog ON Files.FileID = FileStatusLog.FileID) INNER JOIN Users ON FileStatusLog.UserID = Users.UserID
WHERE (((CONVERT(varchar(10), FileStatusLog.StatusDate,103) LIKE '%{variable}%'));
This also returns an emtpy data set when the LIKE is replaced with ='{exact date entry in format: DD/MM/YYYY}', but does return the full table when the WHERE is removed and also when the LIKE is set to just a bare '%' with no other filter values.
Because of the fact that the results are returned when LIKE '%' is used I am not even sure if it is the CONVERT that is causing the problem. I have even tried assigning the escape character manualy to '+' incase the / values of the date in the LIKE variable where escaping other characters. Needless to say this did not make life any better.
As you may have noticed already SQL is not exactly my strong suit, but no one else here even knows what it is, never mind what it's used for, so I got droped with the task. Also - The DB was created by a third party so I have no write permissions to the DB.
Any help with this would be greatly appreciated as it's gotten personal now
Thaks in advance for any assistance
I have a filetable that contains a binary file. I need to do a selective read of the file stored in the file table. I can write a C# CLR function that will open the file, read n bytes the from a starting byte. Or I can write a SQL statement that reads the stream in the filetable into a VARBINARY variable using SUBSTRING beginning at the starting byte (offset from 1) for the same n bytes.
Both give me the same result. However, the SQL statement takes considerably longer to read. I know there is overhead in reading through SQL (interpreted language), but the difference in performance is substantial, and I can only attribute this performance degradation if SQL first tries to "load" the entire stream before it identifies the portion of the stream that it needs to read beginning at the starting byte offset.
I wonder if this is the case or if there is another option to read a stream from a filetable directly through SQL queries that is more efficient.
What's up with this?
This takes like 0 secs to complete:
update xxx_TableName_xxx
set d_50 = 'DE',modify_timestamp = getdate(),modified_by = 1159
where enc_id in
('C24E6640-D2CC-45C6-8C74-74F6466FA262',
'762E6B26-AE4A-4FDB-A6FB-77B4782566C3',
'D7FBD152-F7AE-449C-A875-C85B5F6BB462')
but From linked server this takes 8 minutes????!!!??!:
update [xxx_servername_xxxx].xxx_DatabaseName_xxx.dbo.xxx_TableName_xxx
set d_50 = 'DE',modify_timestamp = getdate(),modified_by = 1159
where enc_id in
('C24E6640-D2CC-45C6-8C74-74F6466FA262',
'762E6B26-AE4A-4FDB-A6FB-77B4782566C3',
'D7FBD152-F7AE-449C-A875-C85B5F6BB462')
What settings or whatever would cause this to take so much longer from the linked server?
Edit:
Note) Other queries from the linked server do not have this behavior. From the stored procedure where we have examined how long each query/update takes... this particular query is the culprit for the time eating. I thought it was to do specefically with this table. However as stated when a query window is opened directly onto that server the update takes no time at all.
2nd Edit:
Could it be to do with this linked server setting?
Collation Compatible
right now it is set to false? I also asked this question in a message below, but figured I should put it up here.
I am hoping someone can shed light on this odd behavior I am seeing running a simple UPDATE statement on a table in SQL Server 2000. I have 2 tables - call them Table1 and Table2 for now (among many) that need to have certain columns updated as part of a single transaction process. Each of the tables has many columns. I have purposely limited the target column for updating to only ONE of the columns in trying to isolate the issue. In one case the UPDATE runs fine against Table1... at runtime in code and as a manual query when run in QueryAnalyzer or in the Query window of SSManagementStudio - either way it works fine.
However, when I run the UPDATE statement against Table2 - at runtime I get rowsaffected = 0 which of course forces the code to throw an Exception (logically). When I take out the SQL stmt and run it manually in Query Analyzer, it runs BUT this is the output seen in the results pane...
(0 row(s) affected)
(1 row(s) affected)
How does on get 2 answers for one query like this...I have never seen such behavior and it is a real frustration ... makes no sense. There is only ONE row in the table that contains the key field passed in and it is the same key field value on the other table Table1 where the SQL returns only ONE message (the one you expect)
(1 row(s) affected)
If anyone has any ideas where to look next, I'd appreciate it.
Thanks
Hi SQL fans,I realized that I often encounter the same situation in a relationdatabase context, where I really don't know what to do. Here is anexample, where I have 2 tables as follow:__________________________________________ | PortfolioTitle|| Portfolio |+----------------------------------------++-----------------------------+ | tfolio_id (int)|| folio_id (int) |<<-PK----FK--| tfolio_idfolio (int)|| folio_name (varchar) | | tfolio_idtitle (int)|--FK----PK->>[ Titles]+-----------------------------+ | tfolio_weight(decimal(6,5)) |+-----------------------------------------+Note that I also have a "Titles" tables (hence the tfolio_idtitlelink).My problem is : When I update a portfolio, I must update all theassociated titles in it. That means that titles can be either removedfrom the portfolio (a folio does not support the title anymore), addedto it (a new title is supported by the folio) or simply updated (atitle stays in the portfolio, but has its weight changed)For example, if the portfolio #2 would contain :[ PortfolioTitle ]id | idFolio | idTitre | poids1 2 1 102 2 2 203 2 3 30and I must update the PortfolioTitle based on these values :idFolio | idTitre | poids2 2 202 3 352 4 40then I should1 ) remove the title #1 from the folio by deleting its entry in thePortfolioTitle table2 ) update the title #2 (weight from 30 to 35)3 ) add the title #4 to the folioFor now, the only way I've found to do this is delete all the entriesof the related folio (e.g.: DELETE TitrePortefeuille WHERE idFolio =2), and then insert new values for each entry based on the new givenvalues.Is there a way to better manage this by detecting which value has to beinserted/updated/deleted?And this applies to many situation :(If you need other examples, I can give you.thanks a lot!ibiza
View 8 Replies View RelatedThe Folowing code is not working anymore. (500 error)
Set objRS = strSQL1.Execute
strSQL1 = "SELECT * FROM BannerRotor where BannerID=" & cstr(BannerID)
objRS.Open strSQL1, objConn , 2 , 3 , adCmdText
If not (objRS.BOF and objRS.EOF) Then
objRS.Fields("Exposures").Value =objRS.Fields("Exposures").Value + 1
objRS.update
End If
objRS.Close
The .execute Method works fine
strSQL1 = "UPDATE BannerRotor SET Exposures=Exposures+1 WHERE BannerID=" & cstr(BannerID)
objConn.Execute strSQL1
W2003 + IIS6.0
Pls advice?
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
hi need help how to send an email from database mail on row update
from stored PROCEDURE multi update
but i need to send a personal email evry employee get an email on row update
like send one after one email
i use FUNCTION i get on this forum to use split from multi update
how to loop for evry update send an single eamil to evry employee ID send one email
i update like this
Code Snippet
:
DECLARE @id nvarchar(1000)
set @id= '16703, 16704, 16757, 16924, 17041, 17077, 17084, 17103, 17129, 17134, 17186, 17190, 17203, 17205, 17289, 17294, 17295, 17296, 17309, 17316, 17317, 17322, 17325, 17337, 17338, 17339, 17348, 17349, 17350, 17357, 17360, 17361, 17362, 17366, 17367, 17370, 17372, 17373, 17374, 17377, 17380, 17382, 17383, 17385, 17386, 17391, 17392, 17393, 17394, 17395, 17396, 17397, 17398, 17400, 17401, 17402, 17407, 17408, 17409, 17410, 17411, 17412, 17413, 17414, 17415, 17417, 17418, 17419, 17420, 17422, 17423, 17424, 17425, 17426, 17427, 17428, 17430, 17431, 17432, 17442, 17443, 17444, 17447, 17448, 17449, 17450, 17451'
UPDATE s SET fld5 = 2
FROM Snha s
JOIN dbo.udf_SplitList(@id, ',') split
ON split.value = s.na
WHERE fld5 = 3
now
how to send an EMAIL for evry ROW update but "personal email" to the employee
Code Snippet
DECLARE @xml NVARCHAR(MAX)DECLARE @body NVARCHAR(MAX)
SET @xml =CAST(( SELECT
FirstName AS 'td','',
LastName AS 'td','' ,
SET @body = @body + @xml +'</table></body></html>'
EXEC msdb.dbo.sp_send_dbmail
@recipients =''
@copy_recipients='www@iec.com',
@body = @body,
@body_format ='HTML',
@subject ='test',
@profile_name ='bob'
END
ELSE
print 'no email today'
TNX
Hello,I am trying to update records in my database from excel data using vbaeditor within excel.In order to launch a query, I use SQL langage in ADO as follwing:------------------------------------------------------------Dim adoConn As ADODB.ConnectionDim adoRs As ADODB.RecordsetDim sConn As StringDim sSql As StringDim sOutput As StringsConn = "DSN=MS Access Database;" & _"DBQ=MyDatabasePath;" & _"DefaultDir=MyPathDirectory;" & _"DriverId=25;FIL=MS Access;MaxBufferSize=2048;PageTimeout=5;" &_"PWD=xxxxxx;UID=admin;"ID, A, B C.. are my table fieldssSql = "SELECT ID, `A`, B, `C being a date`, D, E, `F`, `H`, I, J,`K`, L" & _" FROM MyTblName" & _" WHERE (`A`='MyA')" & _" AND (`C`>{ts '" & Format(Date, "yyyy-mm-dd hh:mm:ss") & "'})"& _" ORDER BY `C` DESC"Set adoConn = New ADODB.ConnectionadoConn.Open sConnSet adoRs = New ADODB.RecordsetadoRs.Open Source:=sSql, _ActiveConnection:=adoConnadoRs.MoveFirstSheets("Sheet1").Range("a2").CopyFromRecordset adoRsSet adoRs = NothingSet adoConn = Nothing---------------------------------------------------------------Does Anyone know How I can use the UPDATE, DELETE INSERT SQL statementsin this environement? Copying SQL statements from access does not workas I would have to reference Access Object in my project which I do notwant if I can avoid. Ideally I would like to use only ADO system andSQL approach.Thank you very muchNono
View 1 Replies View RelatedIt appears to update only the first qualifying row. The trace shows a row count of one when there are multiple qualifying rows in the table. This problem does not exist in JDBC 2000.
View 5 Replies View RelatedI'm having a strange problem that I can't figure out. I have an SQL stored procedure that updates a small database table. When testing the Stored Procedure from the Server Explorer, it works fine. However, when I run the C# code that's supposed to use it, the data doesn't get saved. The C# code seems to run correctly and the parameters that are passed to the SP seem to be okay. No exceptions are thrown.
The C# code:
SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["touristsConnectionString"].ConnectionString);
SqlCommand cmd = new SqlCommand("fort_SaveRedirectURL", conn);
cmd.CommandType = CommandType.StoredProcedure;
Label accomIdLabel = (Label)DetailsView1.FindControl("lblID");
int accomId = Convert.ToInt32(accomIdLabel.Text);
cmd.Parameters.Add("@accomId", SqlDbType.Int).Value = accomId;
cmd.Parameters.Add("@path", SqlDbType.VarChar, 250).Value = GeneratePath();
try
{
conn.Open();
cmd.ExecuteNonQuery();
}
catch(Exception ex)
{
throw ex;
}
finally
{
conn.Close();
}
The Stored Procedure:
ALTER PROCEDURE developers.fort_SaveRedirectURL
(
@accomId int,
@path varchar(250)
)
AS
DECLARE
@enabled bit,
@oldpath varchar(250)
/* Ensure that the accommodation has been enabled */
SELECT @enabled = enabled FROM Experimental_Accommodation
WHERE Experimental_Accommodation.id = @accomId
IF (@enabled = 1)
BEGIN
/* Now check if a path already exists */
SELECT @oldpath = oldpath FROM Experimental_Adpages_Redirect
WHERE Experimental_Adpages_Redirect.accom_id = @accomId
IF @oldpath IS NULL
BEGIN
/* If Path already exists then we should keep the existing URL */
/* Otherwise, we need to insert a new one */
INSERT INTO Experimental_Adpages_Redirect
(oldpath, accom_id)
VALUES (@path,@accomId)
END
END
RETURN
I'm new to this forum.
This 'problem' has occured many times, but I've always found a way around it.
I have pages with datagrids, in which a user can edit a certain fields and then update the tables with new data. Lets say when a user edit a Name field and a money field. If he/she left those two fields blank, the table is automatically updated with a <null> (for the name field) and a 0 (for the money field.) Both these columns were set up to allow Null values.
Anyone has an idea why they were updated that way? And is there like a standard on how the data types are updated if a field is left blank?
Thank you very much.
hi,friends
we show record from multiple table using single 'selectcommand'.
like....
---------
select *
from cust_detail,vend_detail
---------
i want to insert value in multiple database table(more than one) using single 'insert command'.
is it possible?
give any idea or solution.
i want to update value in multiple database table(more than one) using single 'update command'
i want to delete value in multiple database table(more than one) using singl 'delete command'
it is possible?
give any idea or solution.
it's urgent.
thanks in advance.
I do realize that his could be posted in a few spots but I think the answer is in the SQL.
I have a ASP.NET page, with a SqlDataSource, Text Box and Calendar Controls. I have the textbox and calendar controls eval'ed to the same sql data source DateTime Field. The text box is formatted eval to small time and the calendars eval has no formatting.
ex:
<asp:TextBox ID="START_TIME" Text='<%# Eval("EVENT_START","{0:t}") %>' runat=server Width=200></asp:TextBox>
I want to merge the two controls; one has the date the other has the time when I update the pages data to the SqlDataSource field EVENT_START. I've tried a couple of methods, but I would like some other opinions. As Sql server only supports date and time together I am storing the two together.
I could merge the two together in the code behind on the update button's event handler or merge the two during the update query using parameters.
Not that I could get an illegal date for the calendar control, but I could get garbage from the textfield time. So I still would have to do validation on the text field before the SQL server could do the update.
There's a few ways to go about this, so I was wondering if anyone else has figured out an elegant way to handle it.
wbochar
I want to update two tables in one page. So I created two FormView bound on two SqlDataSource controls, and I create a Update button on the bottom of page. And I writen some codes as below:
btnUpate_Click(object sender, EventArgs e){
sqlDataSource1.Update();
sqlDateSource2.Update();}
But, the records haven't updated.
In SqlDataSource2_Updating() function, I found all the parameters is null.
So, how to modify my code to do it.
Zhang