begin tran
exec("storeprocedure1('2')")
if @.@.error=0
begin
exec("storeprocedure2")
if @.@.error=0
begin
exec("storeprocedure3(122)")
if @.error=0
commit tran
else
rollback tran
end
else
rollback tran
end
else
rollback transure, exec statements will work,
but your syntax is not correct, it should be like this:
begin tran
statement1if (@.@.error <> 0)
begin
rollback tran
return
endstatement2
if (@.@.error <> 0)
begin
rollback tran
return
endcommit tran
Can I connect to remote SQL 2005 server through SQL Server Management Studio Express?
I always get a error code 18456 when I try to connect to SQL 2005 server through SQL Server Management Studio Express. I'm sure I enter correct username and password!
The answer is yes its possible but it depends on the security configuration of the remote server.
Hope it helps,
Joe
|||oh wait. I don't have the Express version so I'm not sure if it is limited to local connections, it might be.|||
mycwcgr:
Can I connect to remote SQL 2005 server through SQL Server Management Studio Express?
I always get a error code 18456 when I try to connect to SQL 2005 server through SQL Server Management Studio Express. I'm sure I enter correct username and password!
Try the thread below everything you need and alternatives is covered in it. Post again if you still have questions. Hope this helps.
http://forums.asp.net/thread/1379211.aspx
Hi all,
I have written a custom assembly that works fine in preview mode, but when I deploy the report I get the '#Error' message instead of the output.
I have copied the assembly to the bin folder of Reportserver, placed it in the GAC and even edited the policyfiles.
Any help would be very appriciated,
Thanks in advance
public static class SimCardBuyer
{
public static string ReturnBuyer(string Seller, string PeriodeCode, string ComDevice)
{
string connstring = "PROVIDER=MSOLAP;DATA SOURCE=srv03-gc-10;INITIAL CATALOG=Invoicing";
AdomdConnection conn = new Microsoft.AnalysisServices.AdomdClient.AdomdConnection(connstring);
//Create a new string builder to store the results
System.Text.StringBuilder result = new System.Text.StringBuilder();
//Connect to the local server
using (conn)
{
conn.Open();
//Create a command, using this connection
AdomdCommand cmd = conn.CreateCommand();
cmd.CommandText = @."SELECT NON EMPTY { [Measures].[InvoiceAmount] } ON COLUMNS, NON EMPTY { ([Buyer].[Company].[Company].ALLMEMBERS ) }
DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS FROM
( SELECT ( { [Com Device].[Com Device].[" + ComDevice + @."] } ) ON COLUMNS FROM
( SELECT ( { [Invoice].[Period Code].[" + PeriodeCode + @."] } ) ON COLUMNS FROM
( SELECT ( { [Seller].[Company].[" + Seller + @."] } ) ON COLUMNS FROM [Invoicing])))
WHERE ( [Seller].[Company].[" + Seller + @."], [Invoice].[Period Code].[" + PeriodeCode + @."], [Com Device].[Com Device].[" + ComDevice + @."] )";
//Execute the query, returning a cellset
CellSet cs = cmd.ExecuteCellSet();
//Output the column captions from the first axis
//Note that this procedure assumes a single member exists per column.
TupleCollection tuplesOnRows = cs.Axes[1].Set.Tuples;
if (tuplesOnRows.Count > 1)
{
for (int row = 0; row < tuplesOnRows.Count - 1; row++)
{
result.Append(tuplesOnRows[row].Members[0].Caption);
result.Append(" / ");
}
result.Append(tuplesOnRows[tuplesOnRows.Count - 1].Members[0].Caption);
}
else if (tuplesOnRows.Count == 1)
{result.Append(tuplesOnRows[0].Members[0].Caption);}
else{result.Append(Seller);}
conn.Close();
return result.ToString();
}
}
Dear readers,
I have solved the problem above, but now I get an error with the Connectionstring.
I'm getting the next error now:
Either the user, NL01\SRV03-GC-10$, does not have access to the Invoicing database, or the database does not exist.
NL01\SRV03-GC-10$ is not a user in my system, so I'm stunned with this strange error.
I would appriciate any reply on this.
regards.
Hi,
I tried entering this value "8/24/2006 1:35:00.127 PM" with 127 as the milliseconds in a datetime field, but encountered error saying inconsistent datatype ...
Anyone knows how to store datetime value with milliseconds in the SQL database?
Thanks
All --
FYI, I found the issue that was causing me trouble.
It is a simple matter of display in Enterprise Manager.
When viewing data in Enterprise Manager using the GUI by right-clicking on a Table name and choosing "Return all rows" we see the data...
11/09/2006 15:38:14
...but, when we view the same data using Query Analyzer we see the data...
2006-11-09 15:38:13.907
...which is something quite different.
So, the value is stored with milliseconds but the value is NOT displayed in the same way using the GUI table-browser and Query Analyzer.
That was the crux of my issue.
(Imagine my surprise when I found out my code was actually working.)
:-)
Thank you.
-- Mark Kamoski
|||
If you want to store milliseconds you cannot use SmallDateTime because of limited resolution. The link below should take you in the right direction. Post again if you still have questions. Hope this helps.
http://msdn2.microsoft.com/en-us/library/ms187819.aspx
|||Hi Caddre,
The datatype i am using is datetime and It's doesn't seem to allow me to add milliseconds to it. I am using sql 2000, but i don't think this have to do with SQL version.
|||There are some rules with DateTime maybe you are doing something wrong so I hsve included the link for DateTime guide in SQL Server read that then try using the DatePart function with MS which means millisecond. Post again if you still have questions.
http://msdn2.microsoft.com/en-us/library/ms186724.aspx
http://www.karaszi.com/SQLServer/info_datetime.asp
thanks,
I managed to store milliseconds
INSERT INTO Table VALUES ({ts '2003-11-05 13:02:43.296'})
The PM was your problem. The format that accepts milliseconds uses a 24-hour clock.
|||
The PM was your problem. The format that accepts milliseconds uses a 24-hour clock.
I see that milliseconds can be inserted using SQL; but, what about inserting that value from .NET code into a SQL Server database, using ADO.NET?
I know that a SQL Sever 2000 datetime column can store 3 places of time accuracy, such as 12/30/2006 12:01:03.123, as noted here...
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_da-db_9xut.asp
However, what is not clear is how to get a VS.NET 2003 DateTime variable value to insert into SQL Server datetime column with 3-places of accuracy.
I have tried several things, such as...
DateTime myDateTime = Convert.ToDateTime(DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss.fffffff"));
...but SQL Server always seems to trim the milliseconds.
What is the way to accomplish this?
Please advise.
Thank you.
-- Mark Kamoski
There are some rules with DateTime maybe you are doing something wrong so I hsve included the link for DateTime guide in SQL Server read that then try using the DatePart function with MS which means millisecond. Post again if you still have questions.
http://msdn2.microsoft.com/en-us/library/ms186724.aspx
http://www.karaszi.com/SQLServer/info_datetime.asp
I see that milliseconds can be inserted using SQL; but, what about inserting that value from .NET code into a SQL Server database, using ADO.NET?
The .NET data type DateTime has milliseconds but after I insert them into SQL Server the datetime column does not contain the milliseconds.
I am using ADO.NET, and OLEDB DataAdapter, and simply call Update passing a DataTable.Therefore, I just set the value in the DataTable as a .NET DateTime datatype with high millisecond precision and then call update. Unfortunately, the milliseconds are trimmed.
How can this be done?
|||
It is in the same area with the link you gave Motley. Read carefully and spend time with Katy kam’s blog she does this stuff for the BCL(base class library) team. Hope this helps.
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/acdata/ac_8_con_03_1ckz.asp
http://blogs.msdn.com/kathykam/archive/2006/09/29/773041.aspx
|||Mark,
If you tried the first post the link is to the general topic because the urls did not change, I have the SQL Server 2005 version but run a search for the topic in SQL Server 2000 BOL(books online) . Hope this helps.
http://msdn2.microsoft.com/en-us/library/ms190234.aspx
|||Dim conn as new SqlConnection(ConfigurationManager.ConnectionStrings("...").ConnectString)
Dim cmd as new SqlCommand("INSERT INTO Table1(MyCol1) VALUES (@.dt)",conn)
cmd.Parameters.Add("@.dt",SqlDbType.DateTime).Value=now
conn.open
cmd.executenonquery
conn.close
|||
Dim conn as new SqlConnection(ConfigurationManager.ConnectionStrings("...").ConnectString)
Dim cmd as new SqlCommand("INSERT INTO Table1(MyCol1) VALUES (@.dt)",conn)
cmd.Parameters.Add("@.dt",SqlDbType.DateTime).Value=now
conn.open
cmd.executenonquery
conn.close
Motley --
OK. that might work.
I appreciate the clarification.
However, now that I think about it, I want to have as much resolution as possible to the date-time value being stored.
As such, if I want to store the full 7-digits to the right of the decimal point, (the resolution that .NET provides), then I think that I need to store the date as an nvarchar in some known format that can easily be parsed. When retrieving such values, I will have to cast from the SqlServer nvarchar to a .NET DateTime. That will make SQL-based comparisons/mining tricky. However, once the data is in-memory, in .NET code, and properly cast back to a DateTime, such comparison/mining will be simple. That seems to be a decent solution, at least in my case. And so on.
If anyone has a better solution, then please post it.
Thank you.
-- Mark Kamoski
|||
Dim conn as new SqlConnection(ConfigurationManager.ConnectionStrings("...").ConnectString)
Dim cmd as new SqlCommand("INSERT INTO Table1(MyCol1) VALUES (@.dt)",conn)
cmd.Parameters.Add("@.dt",SqlDbType.DateTime).Value=now
conn.open
cmd.executenonquery
conn.close
Please help.
When I go to SQL Server 2000 Enterprise Manager, select a table, and choose "Return All Rows", and then try to enter...
01/01/2001 01:01:01.123
...I get the following error...
The value you entered is not consistent with the data type or length of the column, or over grid buffer limit
...which is strange.
I can enter the data...
01/01/2001 01:01:01.123
...but it will NOT allow entry of milliseconds.
Why?
Oddly enough, it will allow...
update ProgramLog set DateTimeLogged = '01/01/2001 01:02:03.456'
...and it enters the milliseconds but (another interesting point) it rounded-up ".456" to ".457".
SideBar -- Note that SQL Server 2000 Books Online says: "datetime - Date and time data from January 1, 1753 through December 31, 9999, to an accuracy of one three-hundredth of a second (equivalent to 3.33 milliseconds or 0.00333 seconds). Values are rounded to increments of .000, .003, or .007 seconds, as shown in the table."
Please advise.
Thank you.
-- Mark Kamoski
cnStr = @."Data Source=server\SQLEXPRESS;Initial Catalog=sqldata;User ID=blah\me";
same with this one
cnStr = @."Data Source=STOCKYARD\SQLEXPRESS;Initial Catalog=DB;Trusted_Connection=Yes;";
i figured this was all permission based so i kept dorking around with security adding my user name to anything i could find, and also went through and dozen or so connection strings and found when i take out the DB name that i can open a connection.... as the code below works...
cnStr = @."Data Source=Server\SQLEXPRESS;Trusted_Connection=Yes;";
cn.Open();
I plan on importing another database so how will i later on choose between the two, i notice i can create logins only at the security for the sqlexpress and not for my database? did i mess up how i created the database?
i have added my username and given permissions to the security for the database i wish to connect to, but is there another step im missing? i would keep trucking but i would like to have multiple DB on the sql express and pick and choose at the code level, maybe im misunderstanding what the catalog should include or i can change the connections database at another point?
You need to have specific permissions in a database given to a Database User, and that user needs to be Associated with a Server Login. My guess is that you don't quite have things connected together correctly yet. Check ou this BOL topic that helps lay out how Windows Users, SQL Logins and Database Users all fit together.
Once you have things associated together, you should be able to work in a database.
Mike
|||You cannot use "User ID=blah\me". If you are trying to use your domain account, just use "Trusted_Connection=Yes". User ID is used for SQL Authentication, and you cannot specify a domain account.
You must have login permission for the "Initial Catalog=DB", otherwise, you won't be able to login. Logins are for server and user id is for database. You can more inside here: http://builder.com.com/5100-6388-5055543.html
some quote:
<<<<While logins belong to servers, users belong to databases. A user ID identifies a particular user of
a particular database. Also, users are specific to databases—that is, user Fred in the Northwind database
is not the same as user Fred in the pubs database, although both Freds may be associated with the same login.>>>>>
When setting an output's "IsErrorOut" property to true, is it also possible to add additional columns to that error output?
I'd like to add a message beyond the standard errorCode and errorColumn columns, a column which is the "specific error message", not just a lookup on the errorCode.
IDTSOutput90 outError = ComponentMetaData.OutputCollection.New();
outError.Name = "Error Output";
outError.IsErrorOut = true;
// Add extra column here, e.g. ErrorMessage
Answering my own question. The answer is 'YES', you can enhance the error output.
You can add the columns as follows in ProvideComponentProperties. The part I was missing was since the error output was synchronous, the column index needed to be looked in in the input buffer, not the output buffer. Native SQL ADO destination adapter error codes are much more convenient then the next to useless error codes produced by the OLEDB destination adapter.
===============================================================================
// In ProvideComponentProperties()
// Add error message to error output column collection
// do so after the call to .IsErrorOut, to ensure that ErrorCode and ErrorColumn
// are added first for consistency
IDTSOutputColumnCollection90 outputColumnCollection =
outError.OutputColumnCollection;
IDTSOutputColumn90 outputColumn = outputColumnCollection.New();
outputColumn.Name = ERR_MESSAGE_COLUMN_NAME;
outputColumn.SetDataTypeProperties(DataType.DT_WSTR, 250, 0, 0, 0);
===============================================================================
// In PreExecute()
// Get the input and the external column collection
IDTSInput90 input = ComponentMetaData.InputCollection[0];
IDTSExternalMetadataColumnCollection90 externalcols =
input.ExternalMetadataColumnCollection;
// Deterine index of error Message column
IDTSOutput90 output = ComponentMetaData.OutputCollection["Error Output"];
IDTSOutputColumnCollection90 outputColumnCollection =
output.OutputColumnCollection;
errMessageColumnIndex = BufferManager.FindColumnByLineageID(
input.Buffer, outputColumnCollection[ERR_MESSAGE_COLUMN_NAME].LineageID);
===============================================================================
// In ProcessInput(int inputID, PipelineBuffer buffer)
if (m_rowdisp == DTSRowDisposition.RD_RedirectRow)
{
#region set native error code and message
SqlException sqlEx = (e as SqlException);
if (sqlEx != null) {
// Retrieve the native SqlException error code
errorCode = sqlEx.Number;
}
if (String.IsNullOrEmpty(sqlEx.Message))
buffer.SetNull(errMessageColumnIndex);
else
errorMessage = sqlEx.Message;
// Retrieve and load the native SqlException message
buffer[errMessageColumnIndex] = (errorMessage.Length <= 250 ?
errorMessage : errorMessage.Substring(0, 250));
#endregion
buffer.DirectErrorRow(errorOutputID, errorCode, iCol);
}
===============================================================================
|||
Can you give me more details on this. I will wait to see if you reply beofre I elaborate..
It sounds like this is something I am looking to do based on my post:
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1509511&SiteID=1
|||
Rob,
The code above works for a custom component. If you want to implement your functionality as a custom component then it will work. In the other thread that you linked to you said you were attempting this i a script component - and that is a slightly different kettle of fish.
-Jamie
|||
Wow, you are everywhere Jamie. Yes, now that I see what he was doing, you are correct. Not sure where to go now, but we will continue to develop this process. and I will check back here and some other places.
Say, when you submit these SSIS enhancment requests, (Connect) how long does it take - or what does it take to see these implemented?
|||ronemac wrote:
Say, when you submit these SSIS enhancment requests, (Connect) how long does it take - or what does it take to see these implemented?
If they were to do it (and that's a huge if) then the earliest you could expect it is in the next release of SQL Server. That is due to be Summer 2008.
-Jamie
When setting an output's "IsErrorOut" property to true, is it also possible to add additional columns to that error output?
I'd like to add a message beyond the standard errorCode and errorColumn columns, a column which is the "specific error message", not just a lookup on the errorCode.
IDTSOutput90 outError = ComponentMetaData.OutputCollection.New();
outError.Name = "Error Output";
outError.IsErrorOut = true;
// Add extra column here, e.g. ErrorMessage
Answering my own question. The answer is 'YES', you can enhance the error output.
You can add the columns as follows in ProvideComponentProperties. The part I was missing was since the error output was synchronous, the column index needed to be looked in in the input buffer, not the output buffer. Native SQL ADO destination adapter error codes are much more convenient then the next to useless error codes produced by the OLEDB destination adapter.
===============================================================================
// In ProvideComponentProperties()
// Add error message to error output column collection
// do so after the call to .IsErrorOut, to ensure that ErrorCode and ErrorColumn
// are added first for consistency
IDTSOutputColumnCollection90 outputColumnCollection =
outError.OutputColumnCollection;
IDTSOutputColumn90 outputColumn = outputColumnCollection.New();
outputColumn.Name = ERR_MESSAGE_COLUMN_NAME;
outputColumn.SetDataTypeProperties(DataType.DT_WSTR, 250, 0, 0, 0);
===============================================================================
// In PreExecute()
// Get the input and the external column collection
IDTSInput90 input = ComponentMetaData.InputCollection[0];
IDTSExternalMetadataColumnCollection90 externalcols =
input.ExternalMetadataColumnCollection;
// Deterine index of error Message column
IDTSOutput90 output = ComponentMetaData.OutputCollection["Error Output"];
IDTSOutputColumnCollection90 outputColumnCollection =
output.OutputColumnCollection;
errMessageColumnIndex = BufferManager.FindColumnByLineageID(
input.Buffer, outputColumnCollection[ERR_MESSAGE_COLUMN_NAME].LineageID);
===============================================================================
// In ProcessInput(int inputID, PipelineBuffer buffer)
if (m_rowdisp == DTSRowDisposition.RD_RedirectRow)
{
#region set native error code and message
SqlException sqlEx = (e as SqlException);
if (sqlEx != null) {
// Retrieve the native SqlException error code
errorCode = sqlEx.Number;
}
if (String.IsNullOrEmpty(sqlEx.Message))
buffer.SetNull(errMessageColumnIndex);
else
errorMessage = sqlEx.Message;
// Retrieve and load the native SqlException message
buffer[errMessageColumnIndex] = (errorMessage.Length <= 250 ?
errorMessage : errorMessage.Substring(0, 250));
#endregion
buffer.DirectErrorRow(errorOutputID, errorCode, iCol);
}
===============================================================================
|||
Can you give me more details on this. I will wait to see if you reply beofre I elaborate..
It sounds like this is something I am looking to do based on my post:
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1509511&SiteID=1
|||Rob,
The code above works for a custom component. If you want to implement your functionality as a custom component then it will work. In the other thread that you linked to you said you were attempting this i a script component - and that is a slightly different kettle of fish.
-Jamie
|||Wow, you are everywhere Jamie. Yes, now that I see what he was doing, you are correct. Not sure where to go now, but we will continue to develop this process. and I will check back here and some other places.
Say, when you submit these SSIS enhancment requests, (Connect) how long does it take - or what does it take to see these implemented?
|||
ronemac wrote:
Say, when you submit these SSIS enhancment requests, (Connect) how long does it take - or what does it take to see these implemented?
If they were to do it (and that's a huge if) then the earliest you could expect it is in the next release of SQL Server. That is due to be Summer 2008.
-Jamie
I got this error when i try to see a webpage on my server. What should i do ?
Failed to generate a user instance of SQL Server due to failure in retrieving the user's local application data path. Please make sure the user has a local user profile on the computer. The connection will be closed.
Hi,
It seems this thread is discussing this issue with solutions.
server. (provider: TCP Provider, error: 0 - An existing connection was forcibly
closed by the remote host.)
Description: An unhandled
exception occurred during the execution of the current web request. Please
review the stack trace for more information about the error and where it
originated in the code.
Exception Details: System.Data.SqlClient.SqlException: A transport-level
error has occurred when sending the request to the server. (provider: TCP
Provider, error: 0 - An existing connection was forcibly closed by the remote
host.)
Source Error:
exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below.An unhandled
Stack Trace:
Version Information: Microsoft .NET Framework Version:2.0.50727.42;
ASP.NET Version:2.0.50727.210
Hi,
this could be security issue if you are using WIndows 2003 Server --> http://download.microsoft.com/download/f/1/0/f10c4f60-630e-4153-bd53-c3010e4c513b/ReadmeSQLEXP2005.htm#sse_dbengine
HTH, Jens K. Suessmeyer.
http://www.sqlserver2005.de
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
In my .NET programs, sometimes it's easier to let the user select a
group of people to give access to eben if some of them may already have
it.
Of course this throws an exception an an error message. Now I could
catch and ignore the message in .NET for this operation but then I'm
stuck if something is genuinely wrong.
So is there a way to do this? :
In my stored procedure determine if an error occured because of a
duplicate key and somehow not cause an exception to be returned to
ADO.NET in that case?Can't you just change the INSERT statement so that it won't insert duplicate
rows? For Example:
INSERT INTO foo (user, ...)
SELECT 'Smith', ...
WHERE NOT EXISTS
(SELECT *
FROM foo
WHERE user = 'Smith') ;
or
INSERT INTO foo (user, ...)
SELECT user, ...
FROM bar
LEFT JOIN foo
ON foo.user = bar.user
WHERE foo.user IS NULL
AND ... ;
--
David Portas
SQL Server MVP
--|||Yes, I could check for the records existance before hand. But I wanted
to know if you can detect when an error occurs in T-SQL, and handle it
w/o causing an exception to be thrown in ADO.NET.|||<wackyphill@.yahoo.com> wrote in message
news:1104775581.290491.75140@.f14g2000cwb.googlegro ups.com...
>I have a table I insert a record into to give access to a user. It uses
> primary keys so duplicates are not allowed, so trying to add a record
> for a user more than once is not allowed.
> In my .NET programs, sometimes it's easier to let the user select a
> group of people to give access to eben if some of them may already have
> it.
> Of course this throws an exception an an error message. Now I could
> catch and ignore the message in .NET for this operation but then I'm
> stuck if something is genuinely wrong.
> So is there a way to do this? :
> In my stored procedure determine if an error occured because of a
> duplicate key and somehow not cause an exception to be returned to
> ADO.NET in that case?
Unfortunately, error handling in MSSQL (at least up to version 2000) is
somewhat limited - see these articles for more details:
http://www.sommarskog.se/error-handling-I.html
http://www.sommarskog.se/error-handling-II.html
These sections in particular may be useful for you:
http://www.sommarskog.se/error-hand...tml#client-code
http://www.sommarskog.se/error-handling-I.html#ADO.Net
Simon|||> Yes, I could check for the records existance before hand.
Not beforehand - in the INSERT statement itself.
> I wanted
> to know if you can detect when an error occurs in T-SQL, and handle it
> w/o causing an exception to be thrown in ADO.NET.
See the articles that Simon posted but IMO a stored procedure that requires
you to ignore an error for correct inputs is not a good stored procedure -
it won't fail safe and real problems may go undetected.
--
David Portas
SQL Server MVP
--|||(wackyphill@.yahoo.com) writes:
> I have a table I insert a record into to give access to a user. It uses
> primary keys so duplicates are not allowed, so trying to add a record
> for a user more than once is not allowed.
> In my .NET programs, sometimes it's easier to let the user select a
> group of people to give access to eben if some of them may already have
> it.
> Of course this throws an exception an an error message. Now I could
> catch and ignore the message in .NET for this operation but then I'm
> stuck if something is genuinely wrong.
> So is there a way to do this? :
> In my stored procedure determine if an error occured because of a
> duplicate key and somehow not cause an exception to be returned to
> ADO.NET in that case?
In SQL 2000, no. In the next version of SQL Server, SQL 2005 currently
in beta, yes.
But there is really not that big difference between catching the error in
SQL or in .Net. In the .Net excrption you ignore if the error number 2627
or else you rethrow. But admittedly, it's nicer to do this in the SQL
code, since you keep the error-handling logic closer to the test.
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp
