|
Dear all,
I get a stupid error while executing this update procedure and can't find the mistake.
The SQL statement works fine in the Enterprise Manager:
"UPDATE tb_cont_SmartSell_orders SET OR_state = N'printing' WHERE (OR_state = N'E-Mail')"
But when I'm using the procedure I get the error message:
..ADODB.Recordset (0x800A0E78)
Operation is not allowed when the object is closed....
If I remove "rs1.close" from the procedure it works but I don't understand why.
I call the procedure with "UpdateOrderState "E-Mail", "printing"
======
Sub UpdateOrderState(ByVal strFirstState, ByVal strNextState)
Dim strUpdate, rs1, rsconnect1
Set rsConnect1 = Server.CreateObject("ADODB.Connection")
Set rs1 = Server.CreateObject("ADODB.Recordset")
'rs1.CursorLocation = adUseServer
'rs1.CursorType = adOpenKeyset
'rs1.LockType = adLockOptimistic
strUpdate = "UPDATE tb_cont_SmartSell_orders SET OR_state = N'" & strNextState & "' WHERE (OR_state = N'" & strFirstState & "')"
rs1.open strUpdate, gDBCONSTRING
rs1.close
set rs1 = nothing
set rsconnect1 = nothing
end Sub
======
Many Thanks in advance!
Pete
|
|
|
|
|
The 'problem' is you do not open a recordset, but you are executing a command. Therefore you cannot close the recordset...
What you can (should) do is this:
<br />
Sub UpdateOrderState(ByVal strFirstState, ByVal strNextState)<br />
<div style="margin-left:3em"><br />
Dim strUpdate, cn<br />
Set cn = Server.CreateObject("ADODB.Connection")<br />
cn.Open gDBCONSTRING<br />
<br />
strUpdate = "UPDATE tb_cont_SmartSell_orders SET OR_state = N'" & strNextState & "' WHERE (OR_state = N'" & strFirstState & "')"<br />
<br />
cn.Execute strUpdate<br />
cn.Close <br />
set Cn = nothing<br />
</div><br />
end Sub<br />
Wout Louwers
|
|
|
|
|
Many thanks Wout!
It's working fine now.
br Pete
|
|
|
|
|
Hi, 
Can i use activexscript in transform data task to determine whether the transformation of current table field failed or succeed? This is because i need to insert the failed field to another table. I don't want to validate the dtssource to do this because the transformation still can be failed.
This is the program logic :
Function Main
DTSDestination("Destination") = DTSSource("Source")
Main = DTSTransformStat_OK
(Validate current transformation here)
IF (TRANSFORMATION FAILED) THEN
(INSERT FAILED FIELD VALUE TO A TABLE)
END IF
END FUNCTION
Can i do this in the script? Or i need to use other task like data driven task, activex task, etc.
Thanks for the help.
|
|
|
|
|
i was some how able to get a resultset wanted by my boss. But can this query be optimized further.
<br />
DECLARE @name nvarchar(50)<br />
<br />
DECLARE cur_name CURSOR FOR<br />
SELECT url <br />
FROM urltable<br />
<br />
OPEN cur_url<br />
FETCH NEXT FROM cur_name INTO @name<br />
<br />
WHILE @@FETCH_STATUS = 0<br />
BEGIN <br />
select name from mytable where name like '%' + @name+ '%'<br />
<br />
FETCH NEXT FROM cur_name INTO @name<br />
END<br />
CLOSE cur_name <br />
DEALLOCATE cur_name
It's not a bug, it's an undocumented feature.
suhredayan@omniquad.com
messenger :suhredayan@hotmail.com
|
|
|
|
|
You could try
<br />
Select name<br />
From MyTable<br />
Inner Join urlTable on MyTable.name like ''% + urlTable.url + '%'<br />
but I'm not sure if it will work...
Wout Louwers
|
|
|
|
|
thx, but query is verry resource and time consuming. any suggestion to optimize the resource usage also..
It's not a bug, it's an undocumented feature.
suhredayan@omniquad.com
messenger :suhredayan@hotmail.com
|
|
|
|
|
The resource and time consuming part is like '%' + urlTable.url + '%'.
As far as I known you cannot solve this because of the first '%'.
But you could try:
<br />
Select name<br />
From MyTable, urlTable<br />
Where MyTable.name like ''% + urlTable.url + '%'<br />
Or
<br />
Select name<br />
From MyTable, urlTable<br />
Where CharIndex(MyTable.name, urlTable.url) > 0<br />
or create a full-text catalog on urlTable and use Contains... You can read about full-text catalogs in the SQL Server Books Online
Wout Louwers
|
|
|
|
|
Hi
I'm new to C# and have a little problem inserting Data into a table!
I want to crate a little CD Database:
The table has the following rows: CDID, Artist and Album!
The Code I wrote looks like this:
private void button1_Click(object sender, System.EventArgs e)
{
System.Data.SqlClient.SqlConnection myConnection =
new System.Data.SqlClient.SqlConnection("Data Source=AUTUMN/NetSDK; Initial Catalog=mediadb; User Id=sa;Password=mypwd;");
string myInsertQuery = "INSERT INTO CD (CDID, Artist, Album) Values(" + txtCDID.Text.ToString() + ",'" + txtArtist.Text + "','" + txtAlbum.Text +"')";
System.Data.SqlClient.SqlCommand myCommand = new System.Data.SqlClient.SqlCommand(myInsertQuery);
myCommand.Connection = myConnection;
myConnection.Open();
myCommand.ExecuteNonQuery();
myCommand.Connection.Close();
}
This code doesen't work and I'm wondering why? Everytime i hit the button, i gen an Error message. Something like SQl Exception.... and Systemerror....
Can someone help me out?
Thank
Autumn
|
|
|
|
|
Can you please specify what error message you got when you run the code?
Thanks.
Edbert P.
Sydney, Australia.
|
|
|
|
|
Hi
I got a Systemerror.... SQL Exception when I tried t insert the Data! But I found out, that I made some basic mistakes while coding...
However... it works now!
Thanks
Autumn
|
|
|
|
|
I want to retrieve the Autoinc-ID of the last inserted record.
With sqlserver the command for that is: "select scope_identity()".
But that only seems to work if I use an insert string that includes the values, like:
"insert into Testtable (Name) values ('Charlie')";
In that case "select scope_identity()" returns the ID of the last inserted record. But I prefer using parameters (because of double quotes, date-formatting,...):
"insert into Testtable (Name) values (@Name)";
cmd.Parameters.Add("@Name",SqlDbType.Char, 25).Value = "Charlie";
If I use parameters like in the sample above, I get a null returned on "select scope_identity()".
Any ideas?
Thanks, Bruno.
|
|
|
|
|
SCOPE_IDENTITY only returns values inserted within the current scope (i.e. when you're doing insert and you're asking for the identity). Use @@IDENTITY instead to return teh last identity value for the table.
Hope it helps,
Edbert P.
Sydney, Australia.
|
|
|
|
|
Thats right, @@IDENTITY returns the last identity value for the table. But that might be a different value, depending if a different user on another machine or even a different process on the same machine have entered a record in the meantime. You can verify that , if you open 2 instances of queryanalzer and insert records.
For retrieving the ID of the record just inserted one definitely has to use scope_identity.
thanks anyway, Bruno.
|
|
|
|
|
Create a stored procedure to do the insert, and return @scope_identity from the sproc. This will work since you are in the 'scope' of the insert and saves a round trip as well...
Power corrupts and PowerPoint corrupts absolutely. - Vint Cerf
|
|
|
|
|
I know, but I want to stay away from SP, as the program runs with MySQL and ProgreSQL as well.
Anyway, I figured it out meanwhile.
The reason why I get a NULL return on "select scope_identity" is, because the parametrized INSERT is done via sp_executesql, which is executed in a different scope, whereas INSERT without parameters is executed directly (wihtin the same scope).
So what I do now is as follows:
cmd.CommandText = "INSERT ...; SELECT @id = SCOPE_IDENTITY()";
cmd.Parameters.Add(new SqlParameter("@id", SqlDbType.Int));
cmd.Parameters["@id"].Direction = ParameterDirection.Output;
cmd.ExecuteNonQuery();
Console.WriteLine("ID: {0}", cmd.Parameters["@id"].Value);
Bruno.
|
|
|
|
|
I initally posted this on the ASP.Net board but probably should of been posted here.
In my web app, I have a page that uses a stored procedure that returns multiple results. However, if 2 users request data on this page at the same time, I receive the following error, "The SqlCommand is currently busy Open, Fetching." In my connect string I have "pooling=true;". Is there another option to retrieve multiple recordset in a single trip to the database? Why doesn't ADO.Net simply open up another connection to the database?
Also to note, I am executing a cross-tab query in the procedure so the number of columns returned are not defined. Therefore using a dataset is probably out of the question.
Any help would be appreciated.
Michael
I firmly believe that any man's finest hour, the greatest fulfillment of all that he holds dear, is that moment when he has worked his heart out in a good cause and lies exhausted on the field of battle - victorious.
Vince Lombardi (1913-1970)
|
|
|
|
|
hi,
Try using SQL Transaction object and try to run the stored proc from the transaction object... this might solve ytour problem.
regards,
Aryadip.
Cheers !! and have a Funky day !!
|
|
|
|
|
The connection is pooled, but the SqlCommand object or your connection instance is not. You need to create (or clone) a new SQlCommand and dataReader object for each user, and hand it a new connection (not a shared instance).
Power corrupts and PowerPoint corrupts absolutely. - Vint Cerf
|
|
|
|
|
I tossed out the sqlDataReader and ended up using dataAdapter.Fill(DataSet) instead. For some reason I was under the impression that the dataAdapter.Fill method was only for type datasets. However, using a regular 'Dim ds As New DataSet' and the Fill method will populate all the resultsets from the stored procedure as tables in the dataset.
Thanks,
Michael
I firmly believe that any man's finest hour, the greatest fulfillment of all that he holds dear, is that moment when he has worked his heart out in a good cause and lies exhausted on the field of battle - victorious.
Vince Lombardi (1913-1970)
|
|
|
|
|
Friends,
I've a table in SQL server database. One field in this table is of type "smalldatetime". When i run any query i get the date in the form : 5/6/2004
But i want to get the result in "descriptive" form i.e "Saturday, May 6, 2004". How can i get it with a query ?
Imtiaz
|
|
|
|
|
You can query it like this (just replace GETDATE() with your column):
<br />
SELECT <br />
DATENAME("dw", GETDATE()) + ', ' + <br />
DATENAME("m", GETDATE()) + ' ' + <br />
CAST(DAY(GETDATE()) AS VARCHAR) + ', ' + <br />
CAST(YEAR(GETDATE()) AS VARCHAR)<br />
I'd suggest you just query out the DATETIME field and process it in your platform of choice. The .NET framework has, amongst others, very good localisation support for this that you could use to get the proper string for the proper locale setting. The use of DATENAME is dependant on how SQL Server processes it and thus it cannot be formatted to the users' locale/preferences (at least not to my knowledge).
Hope this helps.
--
Henrik Stuart (http://www.unprompted.com/hstuart/[^])
|
|
|
|
|
Hi All,
Currently in my stored procedure I need to build a table with all dates starting 1st Jan to the date passed (YTD). Cuurently the way I do is, I start from first date of year and loop till @Datepassed, ignoring Sat and Sun.
SELECT @tdate = @YEAR_start<br />
<br />
WHILE @tdate <= @Datepassed<br />
BEGIN<br />
if DATEPART (dw, @tdate) not in (1,7)<br />
INSERT INTO #DATES (tdate)<br />
VALUES (@tdate)<br />
<br />
SELECT @tdate = dateadd (dd,1,@tdate)<br />
END
Foreg:- if dec 12 2003 is passed, looping becomes inefficient.
Does any body know a better way.
Thanks
Ruchi
|
|
|
|
|
Why rebuild something that is static? If you don't specify many various years, build either a global temp table or add a new table with incremental dates, ignoring Saturday and Sunday. To get the dates, then, just use something like:
select Date into #DATES
from DateCache
where Date between @dateStart and @dateEnd Another way would be to use an extended stored proc. Programming something like this in C/C++ would be very efficient. What's questionable is how much more quickly you could build or fill a table.
Microsoft MVP, Visual C#
My Articles
|
|
|
|
|
I have an application that has two forms, the main form displays info about my dataset in a datagrid (uses relationships), the second form I want to display the details of the selected data.
What do I need to insure that these two forms are sync'd. I've tried passing the DataViewManager over and setting the BindingContext of the second form to the first but this doesn't seem to work.
Any suggestions, comments, links are greatly appriciated.
Thanks!
-Dave W.
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
