|
Actually nice though..... any way if u need my sp code here it is.but i guess thr is no prob in sp.any way it's upto u chk it and give me solution.
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
/*AMAL FERNANDO ON 7 FEB 2005 */
ALTER PROC [USP_AEDMasterCode]
(@szCompCode VARCHAR(5),
@szMasterCode VARCHAR(5),
@szDesc VARCHAR(20),
@szCategory VARCHAR(10),
@szCategoryID VARCHAR(2),
@opType VARCHAR(6),
@type VARCHAR(6) OUTPUT,
@errDesc VARCHAR(20) OUTPUT)
AS
DECLARE @@dtLastLogin DATETIME
DECLARE @error INT
DECLARE @rowcount INT
DECLARE @errorMsg NVARCHAR(512)
DECLARE @spName SYSNAME
SELECT @error = @@error,
@rowcount = @@rowcount,
@spName = Object_Name(@@ProcID)
SET NOCOUNT ON
BEGIN TRAN
IF @opType = 'DELALL'
BEGIN
DELETE FROM [DBEPGlobal].[dbo].[EPG_tblMasterCodes]
SET @type = 'DELALL'
END
ELSE IF @opType = 'DEL'
BEGIN
DELETE FROM [DBEPGlobal].[dbo].[EPG_tblMasterCodes]
WHERE MC_szCompCode = @szCompCode
AND MC_szMasterCode = @szMasterCode
AND MC_szCategoryID = @szCategoryID
SET @type = 'DEL'
END
ELSE
IF EXISTS(SELECT * FROM EPG_tblMasterCodes
WHERE MC_szCompCode = @szCompCode
AND MC_szMasterCode = @szMasterCode
AND MC_szCategoryID = @szCategoryID)
BEGIN
UPDATE [DBEPGlobal].[dbo].[EPG_tblMasterCodes]
SET [MC_szCompCode] = @szCompCode,
[MC_szMasterCode] = @szMasterCode,
[MC_szDesc] = @szDesc,
[MC_szCategory] = @szCategory,
[MC_szCategoryID] = @szCategoryID
WHERE MC_szCompCode = @szCompCode
AND MC_szMasterCode = @szMasterCode
AND MC_szCategoryID = @szCategoryID
SET @type = 'UPDATE'
END
ELSE
BEGIN
INSERT INTO EPG_tblMasterCodes
(MC_szCompCode,
MC_szMasterCode,
MC_szDesc,
MC_szCategory,
MC_szCategoryID)
VALUES(@szCompCode,
@szMasterCode,
@szDesc,
@szCategory,
@szCategoryID)
SET @type = 'SAVE'
END
IF @@ERROR <> 0
BEGIN
ROLLBACK TRAN
RAISERROR('Error %d inserting into Region', 10, 1,@spName, @error,@errorMsg)
SET @errDesc = @error
RETURN(@error)
END
ELSE IF @rowcount <> 1
BEGIN
ROLLBACK TRAN
RAISERROR('Rowcount %d inserting into Region', 10, 1,@spName, @rowcount,@errorMsg)
WITH SETERROR
SET @errDesc = @@error
RETURN(@@error)
END
ELSE
BEGIN
COMMIT TRAN
RETURN(0)
END
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
Thanks
|
|
|
|
|
Your code sets a local variable @rowcount - different from @@ROWCOUNT - to whatever the last row count was when the procedure was called - i.e. how many rows were affected/returned by the previous statement executed on that connection.
If you need to check the row count or error code, you should use a SET statement just before you need to check it.
Stability. What an interesting concept. -- Chris Maunder
|
|
|
|
|
Dear mike thanks for your reply.any way i 'm new to sql server 2000 and i want follow best practises.so can u give me guide sp sample which handle all the eroors and return values.
Thanks
|
|
|
|
|
The scenario is that i have a webservice returning a strongly typed dataset to a client app with ostensibly the same typed dataset. Unfortunately they are not recognised as being of the same type and get the error message "cannot convert type 'CAClient.WSCA.AdminStaff to CAClient.AdminStaff'. CAClient is my client. WSCA is the reference to my webservice.
Calling Data from Webservice on The client:
currentUserInformation.myAdminStaff=(AdminStaff) m_WsData.GetAdminStaff(myTicket,currentUserInformation.userGroupId);
webservice:
[WebMethod]
AdminStaff typedDataSet = new AdminStaff();
Param = new SqlParameter("@id",Id);
string [] tableList= {typedDataSet.Tables[0].TableName};
SqlHelper.FillDataset(dbConn, CommandType.StoredProcedure," GetStaff",typedDataSet,tableList, Param );
return typedDataSet;
dazzler
|
|
|
|
|
Im replying to my own cry for help as i have managed to resolve it. I think this is a problem that only newbies like myself are likely to encounter. It stems entirely from a lack of understanding. What i had done was to create a Typed Dataset in a Webservice and then duplicate it entirely on my client. I then stupidly assumed that i could assign the typed dataset returned by a call to the webservice to the one created in my client. These are obviously recognised as two distinct types even though structurally they are the same.
The way to resolve this issue is to create a reference to the webservices typed dataset in the client and not to duplicate it in the client. (sensible really)
cheers
dazzler
dazzler
|
|
|
|
|
Dear guy's i'm using Enterprise library 2005 for my asp.net project.in there i want read return value of my Stored procedure.my how can i edit my code..
Database db=DatabaseFactory.CreateDatabase();
string strsqlcmd="USP_AddEditResourceMaster";
DBCommandWrapper dbcmdwr=db.GetStoredProcCommandWrapper(strsqlcmd);
dbcmdwr.AddInParameter("@szCompCode",DbType.String, Resource.EPGRScompcode);
dbcmdwr.AddInParameter("@szResourceID",DbType.String,
Resource.EPGRSresID);
dbcmdwr.AddInParameter("@szResName",DbType.String, Resource.EPGRSresName);
dbcmdwr.AddInParameter("@szResType",DbType.String,
Resource.EPGRSresType);
dbcmdwr.AddInParameter("@szResMesureType",DbType.String, Resource.EPGRSresMType);
dbcmdwr.AddInParameter("@dResMinCost",DbType.Double,
Resource.EPGRSMincost);
dbcmdwr.AddInParameter("@dResMaxCost",DbType.Double,Resource.EPGRSMaxcost)db.ExecuteNonQuery(dbcmdwr);
Thanks
|
|
|
|
|
Use dbcmdwr.AddParameter.
The 4th parameter is 'direction', and you use ParameterDirection.ReturnValue for the value. After the stored procedure is run, just get the value like you would any other output parameter:
dbcmdwr.GetParameterValue("@MyRetValParameter")
I don't use return values in my stored procedures because not all db support return values, so I code my stored procedures to use output parameters for all values returned. I think this is mentioned in the documentation for the enterise library.
|
|
|
|
|
Thanks Scott....now i knoe how to read return values.u mentioned about the out parameter and i don't know the advantages use outpara than para.any way now i'm use pot para's to read return values from sp.
Finally i have another prob with exception handling in Enterprise library.cas
in my application every time it gives common error called "access denied to registry and all that".can u give me a sample application how to handle exception in better way.
Thank you
Amal
|
|
|
|
|
I have not used the exception handling enterprise code yet, so I do not know about the error. I just started using the data enterprise application block about 2 weeks ago.
I get errors like these in the application log whenever I run code with the data application block:
"Failed to create instances of performance counter 'Total Command Executions' - The requested Performance Counter is not a custom counter, it has to be initialized as ReadOnly.."
I will eventually go through the code and find where this is happening in the enterprise application block code, but for now I just ignore it.
|
|
|
|
|
OK man,thanks for the reply.any way if get a solution pls let me know.
|
|
|
|
|
What happen or how can I prevent some error if i do an update to a table and there is nothing in the table to begin with?
In my code:
<br />
try{<br />
CString mySelect;<br />
mySelect.Format("UPDATE TSK2 SET TSK2_DATE_TIME = CURRENT_TIMESTAMP");<br />
m_pConnection->Execute(_bstr_t(mySelect),NULL, NULL);<br />
}<br />
catch (_com_error &e)<br />
{<br />
_bstr_t bstrError(e.ErrorMessage());<br />
CString strError = (char*)bstrError;<br />
AfxMessageBox(strError);<br />
}<br />
catch (...)<br />
{<br />
AfxMessageBox("Unknown Error!");<br />
Where m_pConnection is a pointer to my _ConnectionPtr. Will I get some thing back from "Execute" that will tell me, that I have to do an INSERT instead of an UPDATE if there is nothing in the table to begin with?
Also I got this section of code from the MSDN site:
<br />
_bstr_t bstrTitle;<br />
_bstr_t bstrType;<br />
<br />
while(!(pRstTemp->EndOfFile))<br />
{<br />
bstrTitle = pRstTemp->Fields->GetItem("Title")->Value;<br />
bstrType = pRstTemp->Fields->GetItem("Type")->Value;<br />
printf("\t%s, %s \n", <br />
(LPCSTR) bstrTitle,<br />
(LPCSTR) bstrType);<br />
<br />
pRstTemp->MoveNext();<br />
If I do not know what type of data that I'm receving from a table can I do what is being done in this code? Or will this only work for string values?
Here is the link to the MSDN website where I found this:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/ado270/htm/mdmthexecutexvc.asp
Thanks
Tom Wright
tawright915@yahoo.com
|
|
|
|
|
The answer to your first question is no, it will gladly do nothing gracefully, and if there are any records in the table, it will try to update the TSK2_DATE_TIME field in all of them (failing if they have a unique index or constraint or ar key fields) since there is no "where" clause to select a unique record. I seem to recall that the Execute method does return a long containing the number of records affected, you could check this for 0...
The second should work for most data types, but might fail if the fields are null. There is a nice wrapper class for ADO Here[^] that takes some of the pain away...
Anger is the most impotent of passions. It effects nothing it goes about, and hurts the one who is possessed by it more than the one against whom it is directed.
Carl Sandburg
|
|
|
|
|
So basically there is no way of knowing whether to do an update (thinking that the table has something all ready in it) or to do an insert (thinking that the table is empty).
Here is what I'm thinking. When a person first creates a table, there is nothing in it. So after the table is created and a programmer goes and write a program that will update a record in the table, their program will error out since there is no record to update. So how would the programmer know whether to do an insert or not?
Thanks
Tom Wright
tawright915@yahoo.com
|
|
|
|
|
"Select Count(*) as Records from MYtable"
if Records == 0, then insert, else update.
Actually, I am baffeled that yo want to update records without knowing which record or how many records.
Since Update statements are intended for changing the values of some fields in some specific existing records, it doesn't usually make much sense to blindly update everything (all records), which is what your query does since it has no 'where' clause to selecect specific records to update. Your query won't generate an error if there are no records, it will just gracefully return a rows effected variable containing 0.
If there are 1 million rows, it will change them all if it can, again without returning an error.
If there is a question as toi whether or not a specific record exists, one would normalkly do something like:
strQuery = "If NOT Exists(select x from MyTable where x = 23) Inset into MyTable (x,y) values(23,'this record was missing') ELSE update Mytable set y ='the record was there' where x = 23"
Myconnection.Execute(strQuery....
An even better practice is to make queries like this into stored procedures with appropriate parameters, and execute the sp, passing ony the parameters needed (23 in this example)
Anger is the most impotent of passions. It effects nothing it goes about, and hurts the one who is possessed by it more than the one against whom it is directed.
Carl Sandburg
|
|
|
|
|
Well Rob. After looking at my post, I realized that and UPDATE is not what I wnated to do. This was before I read your last post. I realized that I wanted to do an insert.
I guess my question pertained to a thought I had. Here it is, when I create a new table, all fields are blank. Let say that there should be records in this table but they are not in there yet. (This is based on the fact that we do a lot of conversion from C-ISAM databases into SQL database) Should I always be checking to see if there are records in the table before I do an update? That was the question. Because if there where no records to update then it would error out.
I guess in my try, catch I could do a try update and if error catch exception and do insert. But that is assuming that it errored based on the idea that there where no records to update. Bad form. So hence my post, is there a way to determine when to do an update or an insert.
In my case I found out that i should always be doing an insert.
Thanks for the help
Tom Wright
tawright915@yahoo.com
|
|
|
|
|
Is there a way to capture the sql server messages? I have some reporting software that only works if rows are returned and I have some Sp that I want to run from this software that return no rows just server messages. I can turn off the check for rows in the software (just an if statement) but can’t figure out how to capture server messages.
Thanks for any help
Ronald Hahn, CNT - Computer Engineering Technologist
New Technologies Analyst
HahnTech Affiliated With Code Constructors
Edmonton, Alberta, Canada
Email: rhahn82@telus.net
|
|
|
|
|
http://www.588188.com/netbook/sqlserver2000/adosql/adoprg03_59pr.htm
|
|
|
|
|
I have a VB.net standalone application. I want a method of merging a table from the database application with a master database. Replication is not an option.
I have tried dumping the table from db1 into a dataset (dataset1), and then the same table from db2 into a dataset (dataset2). Will using the dataset merge method combine the data to enable me to update the master.
IE.
dataset1.Merge(dataset2)
dataset1.acceptchanges
dataAdapter1.Update(dataset1, "Table1")
It seems that dataset1 gets dataset2's data appended, but the datasource does not update.
Please advise a method to do this.
|
|
|
|
|
The datasource is not getting updated becasue you called dataset1.AcceptChanges
When AcceptChanges is called all the rows in the dataset are marked as unmodified so when the data adapter assesses which rows to update, insert and delete it finds all rows are unmodified and thus thinks it does not need save them.
|
|
|
|
|
OK,
Rem'd this line out. To enable some debugging I load the page with 4 data grids. the first data grid with 1st dataset, second with 2nd dataset, third with the merge results and a fourth with any changes to 1st dataset if any.
Here's my code:
'Merge data
dstMBR.Merge(dstMBR1)
'Get any changes encountered in original dataset
dstMBR2 = dstMBR1.GetChanges
'dstMBR.acceptchanges
'Bind merged dataset to datagrid
dgrd2.DataSource = dstMBR1
dgrd2.DataBind()
'Bind changes(if any)to datagrid
dgrd3.DataSource = dstMBR2
dgrd3.DataBind()
'Update data source
dadMBR.Update(dstMBR, "users")
'More checks for modified data
If not dstMBR.GetChanges(DataRowState.Modified) Is Nothing Then
lblStatus.Text = "Modified"
End If
If not dstMBR.GetChanges(DataRowState.Added) Is Nothing Then
lblStatus.Text = "Add"
End If
If not dstMBR.GetChanges(DataRowState.Deleted) Is Nothing Then
lblStatus.Text = "Del"
End If
End Sub
You'll notice that acceptchanges is remarked out
|
|
|
|
|
From reading the MS Help, it looks like the rows of the merged datasets retain their DataRowState from before the merge, so that the "new" datarows would not show as "Added" they probably are showing "Unchanged" since you haven't changed anything in the rows. Then when the Update is done, nothing is changed in the datasource because all the rows in the merged dataset are "Unchanged."
|
|
|
|
|
Anybody know of a work around?
|
|
|
|
|
Copying the changes/additions from the second dataset into the first dataset and then updating the data source.
Fill datasets
if dataset2 only has additions to dataset1
...for each row
.....read row in dataset2
.....create new row in dataset1
.....fill new row with row from dataset2
Update datasource
if dataset2 contains changes to dataset1
...for each row
.....compare row in dataset1 with row in dataset2
.....If there are changes
.......make the changes in dataset1
Update datasource
|
|
|
|
|
Thanks,
Seem to have the additions working, but problems with comparing rows. What is the correct syntax for comparing datarows. Do I have to compare the value of each column in the row, or can I compare the row as a whole.
|
|
|
|
|
I'm just learning all this myself and I would have to compare each column in the rows. Maybe someone else knows of a compare method. A new thread with that question as the topic might get more notice, either here or under the language you are using.
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
