|
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.
|
|
|
|
|
Ok so I figured it out  . I wasn't setting the bindingcontext in the correct place. Not quite positive WHY it works but not really concerned about that at this time.
-Dave W.
|
|
|
|
|
Hi!
I’m writing an application that manages a price list database in MS Access and does some calculations.
I have a global connection opened like this:
sConnectionString = "Provider=" + sProvider + ";"<br />
+ "Password=" + sPassword + ";"<br />
+ "User ID=" + sUserID + ";"<br />
+ "Data Source=" + sDataSource + ";"<br />
+ "Mode=" + sMode + ";";<br />
m_Connection = new System.Data.OleDb.OleDbConnection(sConnectionString);
Then I have some class that does some calculations to which I pass this connection
MyCalculator mc = new MyCalculator(m_Connection);
which has a method called DoCalcs(). This method instanciates some middle layer objects passing the same connection to all. Those objects get some data from the data layer (each one has it’s own) to be used in the calcs. In my data layer object I have a method called GetData similar to this:
public void GetData(out DataTable dtTarget)<br />
{<br />
OleDbCommand oleCmd = new OleDbCommand("select * from " + TableName + " " +, m_Connection);<br />
OleDbDataAdapter myDataAdapter = new OleDbDataAdapter(oleCmd);<br />
OleDbCommandBuilder myCommandBuilder = new OleDbCommandBuilder(m_DataAdapter);<br />
DataTable dt = new DataTable();<br />
myDataAdapter.Fill(dt);<br />
dtTarget = dt;<br />
}
Ok, this works fine, but i want show the user some progress bar. So, I have something like this on my Form:
private void btnDoCalculations_Click(object sender, System.EventArgs e)<br />
{<br />
System.Threading.ThreadStart threadStart = new System.Threading.ThreadStart(m_MyCalculator.DoCalcs);<br />
threadCalc = new System.Threading.Thread(threadStart);<br />
threadCalc.Priority = System.Threading.ThreadPriority.Normal;<br />
threadCalc.Start();<br />
}
(threadCalc is global)
I have a timer and from time to time I inspect some attributes from MyCalculator such as a percent. When percent reaches 100 I know the calculations are over and I call threadCalc.Abort() and show some window with the results.
This works fine too. The user can press the button for calcs again and they are recalculated, but when the user waits some time (something like 10 ou 15 mins) and press the button again I get an exception error “Specified cast is not allowed” when executing myDataAdapter.Fill(dt) from the GetData method. My call stack shows it happened inside the msdata.dll!IOpenRowset. If i call the DoCalcs method without using the thread it works fine every time. It seems like the connection gets corrupted when I use the thread. Does anyone have a hint on what’s happening? 
Sorry about the extension of the text.
Thanks in advance
jmocas
|
|
|
|
|
Hi,
I guess your connection is getting closed due to either timeout or some network blockade is breaking the connection with the database.
The best way would be to close the connection after extraction and make nw connection on the next click of calcs.
If the data in the database is static and no update is occuring from any other place then you may go for dataset which helps you work disconnected and such problems won't appear.
regards,
Aryadip.
Cheers !! and have a Funky day !!
|
|
|
|
|
Yeah, I know. My solution was to work disconnected as you said, but I’m curious why it only happened when I used the thread.
Cheers,
jm
|
|
|
|
|
Hi, 
I need to know how to insert all the records to a table which are fail to be inserted when executes a DTS package. The table is different with the destination or source table.
I realize there are many scenarios. One of them is using the text file - as the source in DTS package - that is generated when exception occured in execution process. But i think that this is a complicated and not the best way.
I also need to insert the current date to the table along with the failed records.
So, please help to solve this problem 
Thanks.
|
|
|
|
|
Could you try :
<br />
INSERT INTO table1 VALUES (SELECT * FROM table2)<br />
or something similar
Perhaps even
<br />
CREATE TABLE table2 AS (SELECT * FROM table1)<br />
Can't remember exact syntax though (as you might have guessed)
"Now I guess I'll sit back and watch people misinterpret what I just said......"
Christian Graus At The Soapbox
|
|
|
|
|
If anybody has ever had the need to provide progress while a lengthy stored procedure is executing, I've written up an article with code and a sample project, that illustrates how it can be done. Even though I used SQL Server and Visual Basic 6.0 in the example code, the mechanism can be used with any database engine and any development environment, including internet applications.
The full article can be found at http://punctualgraphics.com/Articles/A20030711/A20030711_p01.htm
-----------------
Hans De Schrijver
-----------------
|
|
|
|
|
|
Hi,
I'm in desperate need of someone that can help me import data from http://www.asp-shareware.org/pad/padlib.asp into my sql database from xoops.
Is there anyone who can help me setup a script of some kind to do this?
Any help/info is really appreciated
thanks
andy.grootaert@pandora.be
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
