Hi,
I have the need to group records in an ador.recordset.
Is this possible and in that case how?
When I hear recordset, I have to assume that you are referring to classic ADO rather than ADO.Net. So I think it best to take this question up in the SQL Server Data Access forum. Moving the thread there.
- Danny
I think not, at least I couldn't figure out a way to do so.
Does someone have a suggestion on how I can see the values?
I did try to do something like
INSERT INTO TABLE1(NAME)
SELECT NAME FROM INSERTED
but this didn't work. When the trigger completed and I
went to see the TABLE1, there were no records in it.
Are there any documents, web links that describe ways
of debugging the trigger's INSERTED and DELETED?
Thank youOn Wed, 25 Jan 2006 13:29:59 -0500, serge wrote:
>When i debug a trigger is it possible to add a WATCH
>on the INSERTED or DELETED?
Hi Serge,
No. During debugging, it is (unfortunately) not possible to see the
contents of ANY tables.
>I think not, at least I couldn't figure out a way to do so.
>Does someone have a suggestion on how I can see the values?
You could add a SELECT to the trigger code, then test your code from
Query Analyzer. The values in the inserted and deleted pseudo-table
would go to the Query Analyzer results pane.
Or you could use SELECT INTO or INSERT ... SELECT to store the values in
a persistant table.
>I did try to do something like
>INSERT INTO TABLE1(NAME)
>SELECT NAME FROM INSERTED
>but this didn't work. When the trigger completed and I
>went to see the TABLE1, there were no records in it.
Hey, that's just what I suggested! <g
This should work. Some potential reasons for why it didn't work for you
are:
- Maybe the code never even reached the insert into statement? This
might be the case if the table TABLE1 didn;t exist at all after trigger
execution.
- Did you check that the table TABLE1 did not exist before the trigger
was executed? If it did, the command above would result in an error (and
you should have gotten an error message).
- Did you run the trigger with a zero-row operation? (I.e. an UPDATE or
DELETE, or an INSERT .. SELECT that affected 0 rows)
- Don't use a temp table for this. It will be removed when the trigger
execution finishes, as it only exists in the scope of the trigger.
- In a DELETTE trigger, the inserted table is ALWAYS empty.
All the above are just guesses, of course. I'd have to see the actual
code to help you further.
>Are there any documents, web links that describe ways
>of debugging the trigger's INSERTED and DELETED?
>Thank you
--
Hugo Kornelis, SQL Server MVP|||serge (sergea@.nospam.ehmail.com) writes:
> When i debug a trigger is it possible to add a WATCH
> on the INSERTED or DELETED?
> I think not, at least I couldn't figure out a way to do so.
> Does someone have a suggestion on how I can see the values?
> I did try to do something like
> INSERT INTO TABLE1(NAME)
> SELECT NAME FROM INSERTED
> but this didn't work. When the trigger completed and I
> went to see the TABLE1, there were no records in it.
In additions to Hugo's suggestions, keep in mind that if the trigger
fails, then the statement will be rolled back, and that includs the
data insertedvinto Table1
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||On Wed, 25 Jan 2006 22:27:44 +0000 (UTC), Erland Sommarskog wrote:
>serge (sergea@.nospam.ehmail.com) writes:
>> When i debug a trigger is it possible to add a WATCH
>> on the INSERTED or DELETED?
>>
>> I think not, at least I couldn't figure out a way to do so.
>> Does someone have a suggestion on how I can see the values?
>>
>> I did try to do something like
>>
>> INSERT INTO TABLE1(NAME)
>> SELECT NAME FROM INSERTED
>>
>> but this didn't work. When the trigger completed and I
>> went to see the TABLE1, there were no records in it.
>In additions to Hugo's suggestions, keep in mind that if the trigger
>fails, then the statement will be rolled back, and that includs the
>data insertedvinto Table1
Ah, of course. How could I forget it?
Time for bed, I guess :-)
Thanks, Erland!
--
Hugo Kornelis, SQL Server MVP|||Thanks Hugo, Erland.
Hugo,
>This should work. Some potential reasons for why it didn't work for you
>are:
>- Did you run the trigger with a zero-row operation? (I.e. an UPDATE or
>DELETE, or an INSERT .. SELECT that affected 0 rows)
I believe I was running an UPDATE statement with a zero-row operation.
But let's ignore that statement as now I've tested it again using a new
UPDATE statement that updates rows for sure. After testing the last 30
minutes I now understand things better.
I am able to INSERT the records from the trigger's INSERTED table
to the permanent table I created before running the update statement.
I also realized that when running in DEBUG mode I should make sure
to uncheck the DEBUG PROCEDURE's AUTO ROLL BACK check
box. This problem until I realized it kept me puzzled for 10 minutes.
>You could add a SELECT to the trigger code, then test your code from
>Query Analyzer. The values in the inserted and deleted pseudo-table
>would go to the Query Analyzer results pane.
Too bad ADD WATCH isn't available.
Anyone knows if SQL 2005 allows to add watches and monitor the
contents of the inserted and deleted when debugging triggers?
Thank you|||serge (sergea@.nospam.ehmail.com) writes:
> Anyone knows if SQL 2005 allows to add watches and monitor the
> contents of the inserted and deleted when debugging triggers?
I haven't tried debugging in SQL 2005, as it you only can debug from
Visual Studio. But I would not really expect so.
Personally, I have more or less stopped using the debugger. It usually
works when you want to debug your local server, but when connecting to
another, there is so much red tape. Debug PRINTs and SELECTs are easier
to handle.
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||> Personally, I have more or less stopped using the debugger. It usually
> works when you want to debug your local server, but when connecting to
> another, there is so much red tape.
Sometimes I do debug remotely (maybe often). Are you saying it is not always
a
safe approach to debug remotely? "There is so much red tape": these
are known issues that happen frequently or in the very rare cases?
Would you know if there are MS KB on these problems or personal
experiences led you to stop debugging remotely?
Can you please list some situations where debugging remotely is not safe
or the problems you ran into are complicated to explain?
If this is the case then I should avoid debugging remotely and end up
using Terminal Services to connect to the SQL Server and debug locally?
> Debug PRINTs and SELECTs are easier to handle.
I just tried "SELECT * FROM INSERTED" and I saw the result
in the results pane so I am not sure why I had not tried this before.
Well, at least now I know and I won't need to create a table if I am
only interested in seeing the result during the debugging only.
Thanks Erland.|||On Thu, 26 Jan 2006 01:19:30 -0500, serge wrote:
(snip)
>>You could add a SELECT to the trigger code, then test your code from
>>Query Analyzer. The values in the inserted and deleted pseudo-table
>>would go to the Query Analyzer results pane.
>Too bad ADD WATCH isn't available.
>Anyone knows if SQL 2005 allows to add watches and monitor the
>contents of the inserted and deleted when debugging triggers?
Hi Serge,
I don't know. I haven't seen the debugging capabilities in SQL 2005 yet.
In SQL 2000, the debugger was integral part of the product. But that has
been removed from SQL 2005 - you can now only debug triggers and stored
procedures if you also have Visual Studio installed.
http://lab.msdn.microsoft.com/Produ...06-f50123f6d235
--
Hugo Kornelis, SQL Server MVP|||serge (sergea@.nospam.ehmail.com) writes:
> Sometimes I do debug remotely (maybe often). Are you saying it is not
> always a safe approach to debug remotely? "There is so much red tape":
> these are known issues that happen frequently or in the very rare cases?
> Would you know if there are MS KB on these problems or personal
> experiences led you to stop debugging remotely?
Unsafe? Yes, a little, although that was not really what I meant with
red tape. What I mean is simply that there are so many things have to
be aligned for it to work, that I don't find it worth the hassle.
Some time back, we found that debugging did not work when you had Windows
XP SP2 installed. I did some investigation, and found that hotfix
8.00.944 addressed this problem. (This hotfix is included in SP4.) I
installed hotfix on client and server. I also had to open port 135 in
Windows firewall. Now, port 135 is not any port: this is RPC, and a
prime attack surfaces for viruses. So opening port 135 is a little unsafe,
so there is all reason to only open it for the SQL Servers you want to
debug. (If is possible to open a port only for a certain IP address in
Windows firewall.) Eventually I got it working.
Then some months later, I felt like debugging again, but now I was out
of luck again. I did some inquires, and apparently our Windows admin had
decided to cut the number of permissions for the SQL Server service
account. I don't know exactly what permissions that are required, but
as it writes back to the client, it needs more than plain-user rights.
At this point, I just gave it up. These are not the only thing that
can stop debugging from working. And after all, what you can dig out
from the debugger can easily be achieved in other ways. Of course,
code that uses iterative approaches can be painful to debug that
way. But good SQL should not have much such code anyway. :-)
And, oh, there is one more possible issue with the debugger. Single-
stepping through a transaction is not that friendly if other users
needs to access the data.
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||> Unsafe? Yes, a little, although that was not really what I meant with
> red tape. What I mean is simply that there are so many things have to
> be aligned for it to work, that I don't find it worth the hassle.
> Some time back, we found that debugging did not work when you had Windows
> XP SP2 installed. I did some investigation, and found that hotfix
> 8.00.944 addressed this problem. (This hotfix is included in SP4.) I
> installed hotfix on client and server. I also had to open port 135 in
> Windows firewall. Now, port 135 is not any port: this is RPC, and a
> prime attack surfaces for viruses. So opening port 135 is a little unsafe,
> so there is all reason to only open it for the SQL Servers you want to
> debug. (If is possible to open a port only for a certain IP address in
> Windows firewall.) Eventually I got it working.
> Then some months later, I felt like debugging again, but now I was out
> of luck again. I did some inquires, and apparently our Windows admin had
> decided to cut the number of permissions for the SQL Server service
> account. I don't know exactly what permissions that are required, but
> as it writes back to the client, it needs more than plain-user rights.
> At this point, I just gave it up. These are not the only thing that
> can stop debugging from working. And after all, what you can dig out
> from the debugger can easily be achieved in other ways. Of course,
> code that uses iterative approaches can be painful to debug that
> way. But good SQL should not have much such code anyway. :-)
> And, oh, there is one more possible issue with the debugger. Single-
> stepping through a transaction is not that friendly if other users
> needs to access the data.
Thanks for the detailed explanation. Some interesting information
that I'll keep in mind.|||> In SQL 2000, the debugger was integral part of the product. But that has
> been removed from SQL 2005 - you can now only debug triggers and stored
> procedures if you also have Visual Studio installed.
> http://lab.msdn.microsoft.com/Produ...06-f50123f6d235
Then I presume SQL 2005 Studio Management that comes with SQL 2005
is not a flavor of Visual Studio that can debug triggers.
Thanks Hugo.|||On Thu, 26 Jan 2006 22:57:53 -0500, serge wrote:
>> In SQL 2000, the debugger was integral part of the product. But that has
>> been removed from SQL 2005 - you can now only debug triggers and stored
>> procedures if you also have Visual Studio installed.
>>
>> http://lab.msdn.microsoft.com/Produ...06-f50123f6d235
>Then I presume SQL 2005 Studio Management that comes with SQL 2005
>is not a flavor of Visual Studio that can debug triggers.
Hi Serge,
That's correct. Management Studio is replacement for Enterprise Manager
plus Query Analyzer, but doesn't have the debugger.
--
Hugo Kornelis, SQL Server MVP
@.Names is a query string passed in, I need to count the number of records as a result of the below query/
Dim
testAsStringDim sqlConnection3AsNew SqlConnection("data Source=EQ-520-WEB\SQLEXPRESS;Initial Catalog=CRDB.MDF;Integrated Security=True")Dim cmdAsNew SqlCommandDim returnValueAsObject
cmd.CommandText =
"SELECT COUNT(ReqID) AS Expr1, LineManager FROM TblReqMain GROUP BY LineManager HAVING (LineManager = @.Names)"cmd.CommandType = Data.CommandType.Text
cmd.Connection = sqlConnection3
cmd.Parameters.Add(
"@.Names", Data.SqlDbType.NVarChar)sqlConnection3.Open()
cmd.Parameters(
"@.Names").Value = testIf test =""ThenResponse.Write("An error occured")ExitSubElsereturnValue = cmd.ExecuteScalar()sqlConnection3.Close()
Label6.Text =
"Number " & returnValueHi.
your steps is right but you must to be sure the (test) value that have the correct value.
also you must be sure if there is data in the specific value.for example if you trace that test=7 check if there is data must retrive in the query
SELECT COUNT(ReqID) AS Expr1, LineManager FROM TblReqMain GROUP BY LineManager HAVING (LineManager =7)
Hopes that help.
um.. this is unreadable. Are you sure you're returning any data?|||yes it does read the data out of the sql but it doesn´t uppdate the records
<%@. Import Namespace="System.Data" %>
<%@. Import Namespace="System.Data.SqlClient" %>
<html>
<head>
<title>Updating a Row of Data with Validation</title>
<style type="text/css">
#editPanel {
width:90%; padding:10; background:khaki;
border:thin outset}
#posted {
background:lightgreen; border:thin inset}
</style
<script language="C#" runat="server">
/* *********** Data base config ************ */string ServerName = "xxx"; // SQL server name
string Database = "xxx"; // SQL Database Name
string user = "xxx"; // SQL User name
string Pass = "xxx"; // SQL Password/* *********** define DataBase Sql/Access data ************ */
string TheID;
protected void Page_Load ( object src, EventArgs e )
{
string ServerName = "xxx"; // SQL server name
string Database = "xxx"; // SQL Database Name
string user = "xxx"; // SQL User name
string Pass = "xxx"; // SQL PasswordSqlConnection myConn = new SqlConnection("server=" + ServerName + ";database=" + Database + ";UID=" + user + ";PWD=" + Pass + ";");
SqlCommand myCmd = new SqlCommand ("SELECT * FROM cars ORDER BY MessageDate desc",myConn);
myCmd.Connection = myConn;
TheID = Request.QueryString [ "TheID" ] ;
if ( !IsPostBack )
{
if ( TheID == null )
{
bindGrid ( );
loadPanel.Visible = true;
editPanel.Visible = false;
postPanel.Visible = false;
}
else
{
getMessage ( Request.QueryString [ "TheID" ] );
loadPanel.Visible = false;
editPanel.Visible = true;
postPanel.Visible = false;
}
}
else
{
loadPanel.Visible = false;
editPanel.Visible = false;
postPanel.Visible = true;
}
}public void bindGrid ( )
{
string ServerName = "xxx"; // SQL server name
string Database = "xxx"; // SQL Database Name
string user = "xxx"; // SQL User name
string Pass = "xxx"; // SQL PasswordSqlConnection myConn = new SqlConnection("server=" + ServerName + ";database=" + Database + ";UID=" + user + ";PWD=" + Pass + ";");
SqlCommand myCmd = new SqlCommand ("SELECT TOP 10 * FROM cars",myConn);
SqlDataReader rdr;myCmd.CommandText = "SELECT TOP 10 * FROM cars";
myConn.Open();
rdr = myCmd.ExecuteReader ( );
myGrid.DataSource = rdr;
myGrid.DataBind ( );
rdr.Close();
myConn.Close();/*
myCmd.CommandText = "SELECT * FROM cars";
myConn.Open();myGrid.DataSource=myCmd.ExecuteReader ( );
myGrid.DataSource = myCmd.ExecuteReader ( CommandBehavior.CloseConnection );
myGrid.DataBind ( );
*/
}public void getMessage ( String TheID )
{
string ServerName = "xxx"; // SQL server name
string Database = "xxx"; // SQL Database Name
string user = "xxx"; // SQL User name
string Pass = "xxx"; // SQL PasswordSqlConnection myConn = new SqlConnection("server=" + ServerName + ";database=" + Database + ";UID=" + user + ";PWD=" + Pass + ";");
SqlCommand myCmd = new SqlCommand ( );
SqlDataAdapter myAdapter = new SqlDataAdapter ("select * from cars where ID=" + TheID, myConn );
DataTable msgDetails = new DataTable ( );
myAdapter.Fill ( msgDetails );
DataRowView myRow = msgDetails.DefaultView [ 0 ] ;msgFastnumer.Value = myRow [ "Fastnumer" ].ToString ( );
msgTegund.Value = myRow [ "Tegund" ].ToString ( );
editPanel.DataBind ( );
}public void updateMessage ( object src, EventArgs e )
{
string ServerName = "xxx"; // SQL server name
string Database = "xxx"; // SQL Database Name
string user = "xxx"; // SQL User name
string Pass = "xxx"; // SQL PasswordSqlConnection myConn = new SqlConnection("server=" + ServerName + ";database=" + Database + ";UID=" + user + ";PWD=" + Pass + ";");
SqlCommand myCmd = new SqlCommand ( );myCmd.Connection = myConn;
SqlDataAdapter myAdapter = new SqlDataAdapter ("select * from cars where ID=" + TheID, myConn );
DataTable msgDetails = new DataTable ( );
myAdapter.Fill ( msgDetails );
DataRowView myRow = msgDetails.DefaultView [ 0 ] ;SqlDataReader rdr;
if ( Page.IsValid )
{
myCmd.CommandText = "UPDATE cars SET Fastnumer=@.Fastnumer, Tegund=@.Tegund where id=" + TheID;msgFastnumer.Value = myRow [ "Fastnumer" ].ToString ( );
msgTegund.Value = myRow [ "Tegund" ].ToString ( );myCmd.Parameters.Add ( "@.Fastnumer", SqlDbType.NVarChar ).Value = msgFastnumer.Value;
myCmd.Parameters.Add ( "@.Tegund", SqlDbType.NVarChar ).Value = msgTegund.Value;myConn.Open ( );
rdr = myCmd.ExecuteReader ( );
myGrid.DataSource = rdr;
myGrid.DataBind ( );
rdr.Close();
myConn.Close ( );}
bindPostPanel ( TheID );
}public void bindPostPanel ( String TheID )
{
string ServerName = "xxx"; // SQL server name
string Database = "xxx"; // SQL Database Name
string user = "xxx"; // SQL User name
string Pass = "xxx"; // SQL PasswordSqlConnection myConn = new SqlConnection("server=" + ServerName + ";database=" + Database + ";UID=" + user + ";PWD=" + Pass + ";");
SqlCommand myCmd = new SqlCommand ( );
SqlDataReader rdr;
myCmd.CommandText = "SELECT * FROM cars WHERE Id='" + TheID + "'";myCmd.Connection = myConn;
myConn.Open();
rdr = myCmd.ExecuteReader ( CommandBehavior.SingleRow );
listDetails.DataSource = rdr;
listDetails.DataBind ( );
rdr.Close();
myConn.Close();/*
myConn.Open ( );
listDetails.DataSource = myCmd.ExecuteReader ( CommandBehavior.SingleRow );
listDetails.DataBind ( );
myConn.Dispose ( );
*/}
</script>
</head>
<body>
<div class="header"><h3>ADO.NET Primer:
<span class="hilite">Editing an Existing Record</span></h3>
</div>
<hr size="1" width="90%">
<br>
<center>
<asp:panel id="loadPanel" runat="server">
<h5>Select a Record to Edit</h5>
<asp:datagrid id="myGrid" runat="server" width="90%" cellpadding="5" gridlines="vertical" bordercolor="black"
borderwidth="1" font-size="8pt" backcolor="ghostwhite" alternatingitemstyle-backcolor="lightgray"
autogeneratecolumns="false">
<headerstyle backcolor="darkslategray" forecolor="khaki" height="25" font-bold />
<columns>
<asp:boundcolumn headertext="Date" datafield="Innsett" dataformatstring="{0:d}" />
<asp:hyperlinkcolumn headertext="Fastnumer" datanavigateurlfield="id" datanavigateurlformatstring="aspxtest.aspx?TheID={0}"
datatextfield="Fastnumer" />
<asp:boundcolumn headertext="Tegund" datafield="Tegund" />
</columns>
</asp:datagrid>
<p align="center">Back to top</p>
</asp:panel
<asp:panel id="editPanel" runat="server">
<h5>To save changes to the database, press Update.</h5>
<form runat="server">
<input type="hidden" id='msgDate' runat="server">
<table width="85%" cellspacing="1" cellpadding="3" border="0">
<col width="40%" align="right">
<col width="60%">
<tr>
<td>Fastnumer:</td>
<td><input id="msgFastnumer" runat="server">
<asp:requiredfieldvalidator runat="server" controltovalidate="msgFastnumer" errormessage="Sender's name cannot be blank. "
display="none" /></td>
</tr>
<tr>
<td>Your email address:</td>
<td><input id="msgEmail" runat="server">
<asp:regularexpressionvalidator runat="server" controltovalidate="msgEmail" validationexpression="^[\w-]+@.[\w-]+\.(com|net|org|edu|mil)$"
errormessage="Please enter a valid e-mail address. " display="none" /></td>
</tr>
<tr>
<td>Tegund:</td>
<td><input id="msgTegund" runat="server"></td>
</tr>
<tr>
<td>Your message:</td>
<td><textarea id="msgBody" rows="5" cols="35" runat="server"></textarea></td>
</tr>
</table>
<p>
<input type="submit" value="Update" runat="server" onServerClick="updateMessage">
<input type="reset" value="Cancel" onClick="self.location.replace ( 'aspxtest.aspx' ) "></p>
<asp:validationsummary runat="server" displaymode="SingleParagraph" showmessagebox="true" showsummary="false" />
</form>
</asp:panel>
<asp:panel id="postPanel" runat="server">
<h5>Thank you for editing your comments. This record has been updated.</h5>
<asp:datalist id="listDetails" width="85%" runat="server">
<itemtemplate>
<table id="posted" width="100%" cellspacing="1" cellpadding="5" border="0">
<col width="35%" align="right">
<tr>
<td>Date:</td>
<td><%# DataBinder.Eval ( Container.DataItem, "Innsett", "{0:d}" ) %></td>
</tr>
<tr>
<td>Name:</td>
<td><%# ( ( IDataRecord ) Container.DataItem ) [ "Fastnumer" ] %></td>
</tr>
<tr>
<td>Tegund:</td>
<td><%# ( ( IDataRecord ) Container.DataItem ) [ "Tegund" ] %></td>
</tr>
</table>
</itemtemplate>
</asp:datalist>
<p>
Back to Edit View
Select Another Record
</p>
</asp:panel>
</center>
<br>
<hr size="1" width="90%">
</body>
</html>
here you can see the code in action
http://demo.xodus.is/xman-manager/aspxTest.aspx|||why are you doing a "select * from ..." and then an update...why not just an update and call the bindgrid sub at the end..
hth|||i am very new in asp.net can you show me how that is done|||just build your sql stmt use parameterized query and execute it..
sample code:
dim mysql as string
'set up your connection string
Dim oCmd As New OledbCommand(sSQL, oCnn)
mysql="update <table> set col1=@.colval,col2=@.colval where Id=@.Id"
'Add up the parameter, associated it with its value
oCmd.Parameters.Add("col1",value1 )
oCmd.Parameters.Add("col2",value2 )
oCmd.Parameters.Add("id",Idvalue )
oCnn.Open()
oCmd.ExecutenonQuery()
oCnn.Close()
you need to modify the above code with your table names, col names and values from textboxes ..etc and as i said earlier call the bindgrid() again when done.
hth|||thx allot it does work now
I have a stored procedure in a SQL Server database I use it to add new records to a table. I assign values to parameters and ExecuteNonQuery();
I'm getting an error input string invalid. I'm sure its due to null values which I can troubleshoot on my own.
I know how to view the contents of an inline sql statement in classic ASP such as mySQL = "Select authors from pubs" and then Response.Write mySQL. But how do I do this for a stored procedure in .NET?
Tracing for the page is on. I've tried Trace.Write (sqlCommand1.CommandText); but I don't see the command text.
What am I missing?OK Got It. Went into SQL Server and used SQL Server Profiler.
