|
|
kindman_nb wrote: I used Stored Procedure ..
Or you can go with parameterized queries
|
|
|
|
|
Dear All,
I am using sql2005.
i am writing stored procedure to save various master data . I recognize master by @type (input verible) and assign it to @mst veriable then based on @mst no I perform saving task . I created sp . now I am trying to execute then I am getting error
“Invalid object name 'SizeMst'. “ I am running this query from its database.
Stored perocedure is
Alter procedure [dbo].[Sp_RestMst_Save]
@type int ,
@id varchar(20),
@shortdesp varchar(50),
@longdesp varchar(100),
@userid varchar(50),
@ipadd varchar(50)
as
declare @mst int
set @mst = @type
if ( @mst = 1)
begin ;
-- saving data in sizemst
update SizeMst set sshortdesp = @shortdesp , sdesp = @longdesp,smodifiedby = @userid , smodifieddt = getdate() ,ipadd = @ipadd where sizeid = @id;
if(@@rowcount = 0)
insert into SizeMst(sizeid,sshortdesp,sdesp,screatedby,screateddt,ipadd) values(@id,@shortdesp,@longdesp,@userid, getdate(),@ipadd);
end ;
else
if(@mst = 2)
begin ;
-- saving data in brandMst
update BrandMst set bshortdesp = @shortdesp ,bdesp = @longdesp, bmodifiedby = @userid , bmodifieddt = getdate() ,ipadd = @ipadd where brandid = @id;
if(@@rowcount = 0)
insert into brandMst(brandid,bshortdesp,bdesp,bcreatedby,bcreateddt,ipadd) values(@id,@shortdesp,@longdesp,@userid, getdate(),@ipadd);
end ;
else
if(@mst = 3)
begin ;
-- saving data in metalktmst
update MetalKtMst set mshortdesp = @shortdesp ,mdesp = @longdesp, mmodifiedby = @userid , mmodifieddt = getdate() ,ipadd = @ipadd where metalktid = @id;
if(@@rowcount = 0)
insert into MetalKtMst(metalktid,mshortdesp,mdesp,mcreatedby,mcreateddt,ipadd) values(@id,@shortdesp,@longdesp,@userid, getdate(),@ipadd);
end ;
Please guide me in doing so or give me some useful link.
Thanks
regards
imran khan
|
|
|
|
|
I am passing the month value and year value only...,
With that how to display that month all dates?,
Example:
If pass month value=6 and year value =2007
Then it want to show,
'06/01/2007' to '06/30/2007'
Like that i need query...,
Hlp me...,
Regards,
Magi
|
|
|
|
|
Constructing a date is actually pretty tricky in SQL Server. It's easiest to start from 1 January of the desired year, using a string literal:
DECLARE @startDate datetime
DECLARE @endDate datetime
-- note use of ISO8601 yyyyMMdd format - unambiguous
SET @startDate = CAST( CAST( @year AS varchar(4) ) + '0101' AS datetime ) Then you can add the month information:
-- Month is 1-based but we don't want to add anything for January
-- so we subtract 1 from the supplied month number
SET @startDate = DATEADD( m, @month - 1, @startDate ) In the results, we want anything up to 30 June 2007 23:59:59, so I set the end date to be the start date plus one month and then select values greater than or equal to the start date, and less than the end date.
SET @endDate = DATEADD( m, 1, @startDate ) If you really want the last day of the month, you can subtract one day with DATEADD( d, -1, @endDate ).
|
|
|
|
|
Hi if i use this following procedure...,
I am getting only two dates...,
But i want 06/1/2007 to 06/30/2007...,
Given procedure...,
DECLARE @startDate datetime
DECLARE @endDate datetime
-- note use of ISO8601 yyyyMMdd format - unambiguous
SET @startDate = CAST( CAST( 2007 AS varchar(4) ) + '0101' AS datetime )
SET @endDate = CAST( CAST( 2007 AS varchar(4) ) + '0101' AS datetime )
SET @startDate = DATEADD( m, 6-1, @startDate )
SET @endDate = DATEADD( m, 1, @startdate )
if @startdate<@enddate
begin
set @enddate=dateadd(d,-1,@enddate)
select @startdate,@enddate
end
I want output:
06/01/2007
06/02/2007
06/03/2007
.
.
.
06/30/2007 Like this i need output...,
Regards,
Magi
|
|
|
|
|
Try this:
DECLARE @startDate datetime
declare @nextdate datetime
DECLARE @endDate datetime
declare @tbl_date table (mydate datetime)
set nocount on
SET @startDate = CAST( CAST( 2007 AS varchar(4) ) + '0101' AS datetime )
SET @startDate = DATEADD( m, 6 - 1, @startDate )
set @enddate=dateadd(m,1,@startdate)
while @startdate < @enddate
begin
--SET @nextdate = DATEADD( d, 0, @startdate )
insert into @tbl_date values (@startdate)
set @startdate=dateadd(d,1,@startdate)
end
select * from @tbl_date BTW - It is pretty bad form to post the same question in multiple locations, and multiple times (so close to each other).
|
|
|
|
|
please help me ..
i am using ms access database, and c#.net , i want to list the data in grid datewise.
after runnig the query . it is returning the null value.
i may wrong in the formating the date in query or in table.
the data type of column "calltime" is date/time
public void SetReload()
{
string mydate;
CompilationDataSet myDataSet = new CompilationDataSet();
DataViewManager dsView;
mydate = dtp_date.Value.ToShortDateString();
string sSQL = "select * from tblCall where format(calltime,'MM/dd/yyyy')= " + mydate + " ";
sCommands.setOledbCommand(myDataSet, sVariables.sOleDbDataAdapter, sSQL, "tblCall");
dsView = myDataSet.DefaultViewManager;
myDataSet.Tables["tblCall"].Columns["Colid"].ColumnMapping = MappingType.Hidden;
grd_view_call_list.DataSource = dsView;
grd_view_call_list.DataMember = "tblCall";
}
Harry
|
|
|
|
|
Hi,
I am trying to connect to a DB2/MVS V5R1 dtabase from a sample .Net application. I am using MS Oledb provider for DB2 for connection.
The connection.open throws a network error -
Could not connect to data source 'testdb2':
An internal network library error has occurred. A network level syntax error has occurred. SQLSTATE: HY000, SQLCODE: -379
Connection String -
Provider=DB2OLEDB; DataSource=10.10.100.5; Persist Security Info=True ; Host CCSID=
20285 ; PC Code Page=1252 ; Network Transport Library=TCPIP ; Network Address=10.10.
100.5;Network Port=23;Initial Catalog=BOGA721; Package Collection=C066705;Default
Schema=C066705;User ID=C066705; Password=******;DBMS Platform=DB2/MVS
Am I doing anything wrong? Please advise.
|
|
|
|
|
|
thanks, this remedy doesn't apply to my situation as I am giving the uid and pwd both.
I have also tried the data access wizard which comes with the MS oledb driver for db2, but that comes with the same error.
I am not sure if this driver supports db2/mvs as it is not mentioned in the supported databases.
Any idea whic driver can I use to connect to db2/mvs v5r1 from .net ?
|
|
|
|
|
Hi
Please help me. I used the following code in my site. but it didn't work.
public string InsertMailDetails()
{
string strResult=string.Empty;
sbQuery=new StringBuilder();
try
{
sbQuery.Append("Declare @Toid int ");
sbQuery.Append("if exists(select intUser_Id from tbl_User_info where txtEmail='" + strMailToId + "') ");
sbQuery.Append("Begin ");
sbQuery.Append("Set @Toid=(select intUser_Id from tbl_User_info where txtEmail='" + strMailToId + "') ");
sbQuery.Append("Insert into tbl_Mail_info(intUserId,intFromId,Subject,Message,intRead,Maildate) ");
sbQuery.Append("values( @Toid," + strMailFromId + ",'" + strSubject + "','" + strMessage + "',0,'" + DateTime.Today.ToShortDateString() + "')");
sbQuery.Append("End ");
sbQuery.Append("else ");
sbQuery.Append("Begin ");
sbQuery.Append("Select 0 ");
sbQuery.Append("End");
strResult=csDataAccess.ExecuteNonQuery(sbQuery.ToString()).ToString();
// strResult = csDataAccess.ExecuteScalar(sbQuery.ToString()).ToString();
}
catch
{
}
return strResult;
}
After above code executes, strResult is "". Why?
Declare @Toid int if exists(select intUser_Id from tbl_User_info where txtEmail='rgadi_iginc@yahoo.com') Begin Set @Toid=(select intUser_Id from tbl_User_info where txtEmail='rgadi_iginc@yahoo.com') Insert into tbl_Mail_info(intUserId,intFromId,Subject,Message,intRead,Maildate) values( @Toid,226,'test','test mail',0,'7/6/2007')End else Begin Select 0 End
Laxmi
laxmi
|
|
|
|
|
I am not sure as I am pure .NET guy..can you check what arguments you are passing to function???
Vishu
|
|
|
|
|
In the IF part, you don't SELECT anything, so (as there's no result set) you simply get a null back.
|
|
|
|
|
Hi,
Thank you for your reply. How can I modify my code. Please help me.
Thanks & Regards
laxmi
|
|
|
|
|
Why don't you port this into a stored procedures which gives more flexibility to write PL/SQL statements.
|
|
|
|
|
Hey everyone
There are loads of advice on when and why to use SQL triggers, but nowhere can I find an example of how to nest them.
Basically, what I want to do is the following: When a new row is inserted into a table, I want to check if the EventID is a specific value (in this case 456). If true, I want to get the ID (identity value) of that row and for all subsequent inserts into that table, write the ID values of these rows that have that ID as a BaseID into another table.
My table looks like this
ID ... BaseID ... EventID
1 ... 0 ... 456
2 ... 1 ... 457
3 ... 1 ... 457
Here is the code that explains what must be done:
--------------------------------------------------
CREATE TRIGGER Trigger456
ON fcEventHist
FOR INSERT
AS
IF ((SELECT TOP 1 EventID FROM fcEventHist ORDER BY ID DESC) = 456)
BEGIN
DECLARE @base numeric
SET @base = (SELECT TOP 1 ID FROM fcEventHist WHERE EventID = 456 ORDER BY ID DESC)
CREATE TRIGGER eventTrigger
ON fcEventHist
FOR INSERT
AS
IF ((SELECT TOP 1 BaseID FROM fcEventHist ORDER BY ID DESC) = @base)
BEGIN
DECLARE @eventHistID numeric
SET @eventHistID = (SELECT TOP 1 EH.ID FROM fcEventHist AS EH ORDER BY EH.ID DESC)
UPDATE tbl_ProjectOutstandingProcesses
SET fcEventHistID = @eventHistID
WHERE OutstandingID = (SELECT TOP 1 OutstandingID FROM tbl_ProjectOutstandingProcesses WHERE fcEventHistID IS NULL)
END
ELSE
BEGIN
DROP TRIGGER eventTrigger
END
GO
END
GO
--------------------------------------------------
Obviously this doesn't work or I wouldn't be posting a question here 
The problem occurs where I create a new trigger inside the first trigger, but I haven't been able to find example syntax on the net that shows how to trigger a new trigger inside a trigger 
Any ideas and help would be appreciated.
Thanks in advance.
|
|
|
|
|
nr1b wrote: how to trigger a new trigger inside a trigger
I think you won't be able to do this. As you know triggers will be fired for insert,update and delete operations. Then how a trigger can trigger another one ?
Inside your trigger, if you update another table which has an update trigger defined, will it be considered as nesting of triggers ? I am confused
|
|
|
|
|
Hi i have some records in my table with date..,
Now my question i will explain with example:
june month have 30 days...,
But in my table,i have 1 to 22 date and 25 to 30 date records...,
Those days using query, i can get from my table...,
Remaining 23 to 24 dates also want to show...,
How to do that...,
Example:(my table look like this.........)
Date m1 m2 m3
06/01/2007 78 67 56
06/02/2007 45 89 76
.
..
06/22/2007 45 23 87
06/25/2007 77 89 45
.
..
06/30/2007 78 56 89
Now it want to show...,
Date m1 m2 m3
06/01/2007 78 67 56
06/02/2007 45 89 76
.
..
06/22/2007 45 23 87
06/23/2007
06/24/2007
06/25/2007 77 89 45
.
..
06/30/2007 78 56 89
How to do taht plz tell me......,
Magesh
|
|
|
|
|
Well, one way to do this is
declare @tempdate table(myDate datetime)
declare @date datetime
declare @enddate datetime
set nocount on
set @date = '01-Jun-2007'
set @enddate = dateadd(mm, 1, @date)
while (@date < @enddate)
begin
insert into @tempdate values (@date)
set @date = dateadd(d, 1, @date)
end
select myDate, m1, m2, m3
from test
right join @tempdate
on myDate = [date]
|
|
|
|
|
ya if i use ur query i am getting all dates...,
But dates are repeating...,
so i am getting totally more than 600 records...,
See i like to pass my month value and year value only...,
for the june month value=6 and year value=2007...,
With that i want to show my table dates and missing dates...,
Like that i need a query...,
Regards,
Magi
|
|
|
|
|
plz said me that what it statement means or what is output
SELECT Max(dispcode) AS Expr1 FROM EsiDisp;
|
|
|
|
|
it selects maximum value from column dispcode of table EsiDisp.
rahul
|
|
|
|
|
thanx for your reply but plz differetiate
SELECT Max(dispcode) AS Expr1 FROM EsiDisp
SELECT Max(dispcode) FROM EsiDisp
|
|
|
|
|
SELECT Max(dispcode) AS Expr1 FROM EsiDisp
will display selected maximum value with column name Expr1
it is nothing just giving alias name to original column name
SELECT Max(dispcode) FROM EsiDisp
will display selected maximum value with column name dispcode
rahul
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
