ERROR: Subreport: update

Showing posts with label update. Show all posts

Monday, March 26, 2012

escaping data for update query

I have some code (C#) that runs an SQL update query that sets the
value of a column to what the user passes. So, this causes an error
when anything the user passes in has a ' character in it. I'm sure
there's other characters that'll break it too. So, I was wondering,
how do I get around this? Is there some commonly accepted regex
pattern that will make the value safe to run in an SQL query? How can
I take care of any values that need to be escaped?
I'm not using any fancy ado.net objects:
string sql= [whatever the user passes in]
SqlConnection connection = new
SqlConnection(ConfigurationManager.ConnectionStrin gs[Utils.GetConnectionString].ToString());
connection.Open();
SqlCommand command = connection.CreateCommand();
command.CommandType = CommandType.Text;
command.CommandText = sql;
try
{
int result = command.ExecuteNonQuery();
if (result != 1)
{
Response.StatusCode = 500;
Response.Write("The file has been uploaded, but we
could not update the DB");
Response.End();
}
}
catch (InvalidOperationException)
{
Response.Clear();
Response.Write("error");
Response.StatusCode = 500;
Response.End();
}
connection.Close();
On 6 4 , 8 48 , eggie5 <egg...@.gmail.com> wrote:
> I have some code (C#) that runs an SQL update query that sets the
> value of a column to what the user passes. So, this causes an error
> when anything the user passes in has a ' character in it. I'm sure
> there's other characters that'll break it too. So, I was wondering,
> how do I get around this? Is there some commonly accepted regex
> pattern that will make the value safe to run in an SQL query? How can
> I take care of any values that need to be escaped?
> I'm not using any fancy ado.net objects:
> string sql= [whatever the user passes in]
> SqlConnection connection = new
> SqlConnection(ConfigurationManager.ConnectionStrin gs[Utils.GetConnectionStrXing].ToString());
> connection.Open();
> SqlCommand command = connection.CreateCommand();
> command.CommandType = CommandType.Text;
> command.CommandText = sql;
> try
> {
> int result = command.ExecuteNonQuery();
> if (result != 1)
> {
> Response.StatusCode = 500;
> Response.Write("The file has been uploaded, but we
> could not update the DB");
> Response.End();
> }
> }
> catch (InvalidOperationException)
> {
> Response.Clear();
> Response.Write("error");
> Response.StatusCode = 500;
> Response.End();
> }
> connection.Close();
You can string.replace() method to escape
charater ' by ''(double single quotes).
|||If you post the same question to multiple groups, send the message once and
specify all groups (crosspost) rather than post independent messages. This
courtesy allows everyone involved to track the responses and prevents
duplication of effort.

> Is there some commonly accepted regex
> pattern that will make the value safe to run in an SQL query? How can
> I take care of any values that need to be escaped?
The Best Practice is to use parameters rather than build a SQL statement
string. Not only does this eliminate the need to escape quotes, it's much
more secure because it's not vulnerable to SQL injection. Simple example
below.
command.CommandText = "INSERT INTO dbo.MyTable VALUES(@.UserParameter)";
SqlParameter param = new SqlParameter("@.UserParameter",
userSuppliedValue);
command.Parameters.Add(param);
command.ExecuteNonQuery();
Hope this helps.
Dan Guzman
SQL Server MVP
"eggie5" <eggie5@.gmail.com> wrote in message
news:1180918088.976008.41270@.q75g2000hsh.googlegro ups.com...
>I have some code (C#) that runs an SQL update query that sets the
> value of a column to what the user passes. So, this causes an error
> when anything the user passes in has a ' character in it. I'm sure
> there's other characters that'll break it too. So, I was wondering,
> how do I get around this? Is there some commonly accepted regex
> pattern that will make the value safe to run in an SQL query? How can
> I take care of any values that need to be escaped?
> I'm not using any fancy ado.net objects:
> string sql= [whatever the user passes in]
> SqlConnection connection = new
> SqlConnection(ConfigurationManager.ConnectionStrin gs[Utils.GetConnectionString].ToString());
> connection.Open();
> SqlCommand command = connection.CreateCommand();
> command.CommandType = CommandType.Text;
> command.CommandText = sql;
>
> try
> {
> int result = command.ExecuteNonQuery();
> if (result != 1)
> {
> Response.StatusCode = 500;
> Response.Write("The file has been uploaded, but we
> could not update the DB");
> Response.End();
> }
> }
> catch (InvalidOperationException)
> {
> Response.Clear();
> Response.Write("error");
> Response.StatusCode = 500;
> Response.End();
> }
> connection.Close();
>

escaping data for update query

I have some code (C#) that runs an SQL update query that sets the
value of a column to what the user passes. So, this causes an error
when anything the user passes in has a ' character in it. I'm sure
there's other characters that'll break it too. So, I was wondering,
how do I get around this? Is there some commonly accepted regex
pattern that will make the value safe to run in an SQL query? How can
I take care of any values that need to be escaped?
I'm not using any fancy ado.net objects:
string sql= [whatever the user passes in]
SqlConnection connection = new
SqlConnection(ConfigurationManager.ConnectionStrings[Utils.GetConnectionString].ToString());
connection.Open();
SqlCommand command = connection.CreateCommand();
command.CommandType = CommandType.Text;
command.CommandText = sql;
try
{
int result = command.ExecuteNonQuery();
if (result != 1)
{
Response.StatusCode = 500;
Response.Write("The file has been uploaded, but we
could not update the DB");
Response.End();
}
}
catch (InvalidOperationException)
{
Response.Clear();
Response.Write("error");
Response.StatusCode = 500;
Response.End();
}
connection.Close();On 6 4 , 8 48 , eggie5 <egg...@.gmail.com> wrote:
> I have some code (C#) that runs an SQL update query that sets the
> value of a column to what the user passes. So, this causes an error
> when anything the user passes in has a ' character in it. I'm sure
> there's other characters that'll break it too. So, I was wondering,
> how do I get around this? Is there some commonly accepted regex
> pattern that will make the value safe to run in an SQL query? How can
> I take care of any values that need to be escaped?
> I'm not using any fancy ado.net objects:
> string sql=3D [whatever the user passes in]
> SqlConnection connection =3D new
> SqlConnection(ConfigurationManager.ConnectionStrings[Utils.GetConnectionS=tr=ADing].ToString());
> connection.Open();
> SqlCommand command =3D connection.CreateCommand();
> command.CommandType =3D CommandType.Text;
> command.CommandText =3D sql;
> try
> {
> int result =3D command.ExecuteNonQuery();
> if (result !=3D 1)
> {
> Response.StatusCode =3D 500;
> Response.Write("The file has been uploaded, but we
> could not update the DB");
> Response.End();
> }
> }
> catch (InvalidOperationException)
> {
> Response.Clear();
> Response.Write("error");
> Response.StatusCode =3D 500;
> Response.End();
> }
> connection.Close();
You can string.replace() method to escape
charater ' by ''(double single quotes).|||If you post the same question to multiple groups, send the message once and
specify all groups (crosspost) rather than post independent messages. This
courtesy allows everyone involved to track the responses and prevents
duplication of effort.
> Is there some commonly accepted regex
> pattern that will make the value safe to run in an SQL query? How can
> I take care of any values that need to be escaped?
The Best Practice is to use parameters rather than build a SQL statement
string. Not only does this eliminate the need to escape quotes, it's much
more secure because it's not vulnerable to SQL injection. Simple example
below.
command.CommandText = "INSERT INTO dbo.MyTable VALUES(@.UserParameter)";
SqlParameter param = new SqlParameter("@.UserParameter",
userSuppliedValue);
command.Parameters.Add(param);
command.ExecuteNonQuery();
--
Hope this helps.
Dan Guzman
SQL Server MVP
"eggie5" <eggie5@.gmail.com> wrote in message
news:1180918088.976008.41270@.q75g2000hsh.googlegroups.com...
>I have some code (C#) that runs an SQL update query that sets the
> value of a column to what the user passes. So, this causes an error
> when anything the user passes in has a ' character in it. I'm sure
> there's other characters that'll break it too. So, I was wondering,
> how do I get around this? Is there some commonly accepted regex
> pattern that will make the value safe to run in an SQL query? How can
> I take care of any values that need to be escaped?
> I'm not using any fancy ado.net objects:
> string sql= [whatever the user passes in]
> SqlConnection connection = new
> SqlConnection(ConfigurationManager.ConnectionStrings[Utils.GetConnectionString].ToString());
> connection.Open();
> SqlCommand command = connection.CreateCommand();
> command.CommandType = CommandType.Text;
> command.CommandText = sql;
>
> try
> {
> int result = command.ExecuteNonQuery();
> if (result != 1)
> {
> Response.StatusCode = 500;
> Response.Write("The file has been uploaded, but we
> could not update the DB");
> Response.End();
> }
> }
> catch (InvalidOperationException)
> {
> Response.Clear();
> Response.Write("error");
> Response.StatusCode = 500;
> Response.End();
> }
> connection.Close();
>

escaping data for update query

I have some code (C#) that runs an SQL update query that sets the
value of a column to what the user passes. So, this causes an error
when anything the user passes in has a ' character in it. I'm sure
there's other characters that'll break it too. So, I was wondering,
how do I get around this? Is there some commonly accepted regex
pattern that will make the value safe to run in an SQL query? How can
I take care of any values that need to be escaped?
I'm not using any fancy ado.net objects:
string sql= [whatever the user passes in]
SqlConnection connection = new
SqlConnection(ConfigurationManager.ConnectionStrings[Utils.GetConnection
String].ToString());
connection.Open();
SqlCommand command = connection.CreateCommand();
command.CommandType = CommandType.Text;
command.CommandText = sql;
try
{
int result = command.ExecuteNonQuery();
if (result != 1)
{
Response.StatusCode = 500;
Response.Write("The file has been uploaded, but we
could not update the DB");
Response.End();
}
}
catch (InvalidOperationException)
{
Response.Clear();
Response.Write("error");
Response.StatusCode = 500;
Response.End();
}
connection.Close();On 6 4 , 8 48 , eggie5 <egg...@.gmail.com> wrote:
> I have some code (C#) that runs an SQL update query that sets the
> value of a column to what the user passes. So, this causes an error
> when anything the user passes in has a ' character in it. I'm sure
> there's other characters that'll break it too. So, I was wondering,
> how do I get around this? Is there some commonly accepted regex
> pattern that will make the value safe to run in an SQL query? How can
> I take care of any values that need to be escaped?
> I'm not using any fancy ado.net objects:
> string sql=3D [whatever the user passes in]
> SqlConnection connection =3D new
> SqlConnection(ConfigurationManager.ConnectionStrings[Utils.GetConnectionS=[/vb
col]
tr=ADing].ToString());[vbcol=seagreen]
> connection.Open();
> SqlCommand command =3D connection.CreateCommand();
> command.CommandType =3D CommandType.Text;
> command.CommandText =3D sql;
> try
> {
> int result =3D command.ExecuteNonQuery();
> if (result !=3D 1)
> {
> Response.StatusCode =3D 500;
> Response.Write("The file has been uploaded, but we
> could not update the DB");
> Response.End();
> }
> }
> catch (InvalidOperationException)
> {
> Response.Clear();
> Response.Write("error");
> Response.StatusCode =3D 500;
> Response.End();
> }
> connection.Close();
You can string.replace() method to escape
charater ' by ''(double single quotes).|||If you post the same question to multiple groups, send the message once and
specify all groups (crosspost) rather than post independent messages. This
courtesy allows everyone involved to track the responses and prevents
duplication of effort.

> Is there some commonly accepted regex
> pattern that will make the value safe to run in an SQL query? How can
> I take care of any values that need to be escaped?
The Best Practice is to use parameters rather than build a SQL statement
string. Not only does this eliminate the need to escape quotes, it's much
more secure because it's not vulnerable to SQL injection. Simple example
below.
command.CommandText = "INSERT INTO dbo.MyTable VALUES(@.UserParameter)";
SqlParameter param = new SqlParameter("@.UserParameter",
userSuppliedValue);
command.Parameters.Add(param);
command.ExecuteNonQuery();
Hope this helps.
Dan Guzman
SQL Server MVP
"eggie5" <eggie5@.gmail.com> wrote in message
news:1180918088.976008.41270@.q75g2000hsh.googlegroups.com...
>I have some code (C#) that runs an SQL update query that sets the
> value of a column to what the user passes. So, this causes an error
> when anything the user passes in has a ' character in it. I'm sure
> there's other characters that'll break it too. So, I was wondering,
> how do I get around this? Is there some commonly accepted regex
> pattern that will make the value safe to run in an SQL query? How can
> I take care of any values that need to be escaped?
> I'm not using any fancy ado.net objects:
> string sql= [whatever the user passes in]
> SqlConnection connection = new
> SqlConnection(ConfigurationManager.ConnectionStrings[Utils.GetConnecti
onString].ToString());
> connection.Open();
> SqlCommand command = connection.CreateCommand();
> command.CommandType = CommandType.Text;
> command.CommandText = sql;
>
> try
> {
> int result = command.ExecuteNonQuery();
> if (result != 1)
> {
> Response.StatusCode = 500;
> Response.Write("The file has been uploaded, but we
> could not update the DB");
> Response.End();
> }
> }
> catch (InvalidOperationException)
> {
> Response.Clear();
> Response.Write("error");
> Response.StatusCode = 500;
> Response.End();
> }
> connection.Close();
>

escaping data for update query

I'm not using any fancy ado.net objects:

string sql= [whatever the user passes in]

SqlConnection connection = new
SqlConnection(ConfigurationManager.ConnectionStrin gs[Utils.GetConnectionString].ToString());
connection.Open();

SqlCommand command = connection.CreateCommand();
command.CommandType = CommandType.Text;
command.CommandText = sql;

try
{
int result = command.ExecuteNonQuery();

if (result != 1)
{
Response.StatusCode = 500;
Response.Write("The file has been uploaded, but we
could not update the DB");
Response.End();
}
}
catch (InvalidOperationException)
{
Response.Clear();
Response.Write("error");
Response.StatusCode = 500;
Response.End();
}

connection.Close();If you post the same question to multiple groups, send the message once and
specify all groups (crosspost) rather than post independent messages. This
courtesy allows everyone involved to track the responses and prevents
duplication of effort.

This question has been answered in both microsoft.public.sqlserver.server
and microsoft.public.sqlserver.programming.

--
Hope this helps.

Dan Guzman
SQL Server MVP

"eggie5" <eggie5@.gmail.comwrote in message
news:1180917930.810194.38600@.q75g2000hsh.googlegro ups.com...

Quote:

Originally Posted by

>I have some code (C#) that runs an SQL update query that sets the
value of a column to what the user passes. So, this causes an error
when anything the user passes in has a ' character in it. I'm sure
there's other characters that'll break it too. So, I was wondering,
how do I get around this? Is there some commonly accepted regex
pattern that will make the value safe to run in an SQL query? How can
I take care of any values that need to be escaped?
>
I'm not using any fancy ado.net objects:
>
string sql= [whatever the user passes in]
>
SqlConnection connection = new
SqlConnection(ConfigurationManager.ConnectionStrin gs[Utils.GetConnectionString].ToString());
connection.Open();
>
SqlCommand command = connection.CreateCommand();
command.CommandType = CommandType.Text;
command.CommandText = sql;
>
>
try
{
int result = command.ExecuteNonQuery();
>
if (result != 1)
{
Response.StatusCode = 500;
Response.Write("The file has been uploaded, but we
could not update the DB");
Response.End();
}
}
catch (InvalidOperationException)
{
Response.Clear();
Response.Write("error");
Response.StatusCode = 500;
Response.End();
}
>
connection.Close();
>

|||oops sorry!

On Jun 3, 8:18 pm, "Dan Guzman" <guzma...@.nospam-online.sbcglobal.net>
wrote:

Quote:

Originally Posted by

If you post the same question to multiple groups, send the message once and
specify all groups (crosspost) rather than post independent messages. This
courtesy allows everyone involved to track the responses and prevents
duplication of effort.
>
This question has been answered in both microsoft.public.sqlserver.server
and microsoft.public.sqlserver.programming.
>
--
Hope this helps.
>
Dan Guzman
SQL Server MVP
>
"eggie5" <egg...@.gmail.comwrote in message
>
news:1180917930.810194.38600@.q75g2000hsh.googlegro ups.com...
>

Quote:

Originally Posted by

Quote:

Originally Posted by

I'm not using any fancy ado.net objects:

Quote:

Originally Posted by

string sql= [whatever the user passes in]

Quote:

Originally Posted by

SqlConnection connection = new
SqlConnection(ConfigurationManager.ConnectionStrin gs[Utils.GetConnectionStr ing].ToString());
connection.Open();

Quote:

Originally Posted by

SqlCommand command = connection.CreateCommand();
command.CommandType = CommandType.Text;
command.CommandText = sql;

Quote:

Originally Posted by

try
{
int result = command.ExecuteNonQuery();

Quote:

Originally Posted by

Quote:

Originally Posted by

connection.Close();

Monday, March 19, 2012

ERRORS on Insert/Update DB2 from SQL Enterprise Manager (linked server)

I've set up an AS400/DB2 as a linked server, all standard configs (V4R5) on a SQL server 2000 machine and can select all day long. When I go to update or insert it throws an error referencing my driver. I'm using the HIS/OLEDB2 driver. Any ideas? Someone mentioned turning on Journaling on the 400?

Anything at this point would helpRE:
Q1 I'm using the HIS/OLEDB2 driver and can select all day long. When I go to update or insert it throws an error referencing my driver. Any ideas? Anything at this point would help

A1 Perhaps DB2 Connect provides a better odbc driver? Per IBM, DB2 Connect: "Provides extensive application programming tools for developing client-server and web applications using industry standard APIs such as ODBC, ADO, OLE DB, JDBC, SQLJ, DB2 CLI and Embedded SQL."

See: http://www-3.ibm.com/software/data/db2/db2connect/|||I found an answer that worked from a colleague. Thanks.

Wednesday, March 7, 2012

error-checking all in one place

In my SQL 2000 stored proc I have a few dozen 'UPDATE mytable...'
statements, all updating the same table. I'd like to do error-checking
for these UPDATE statements all in one place, and I figured I'd create a
separate proc. I found out quickly that this is a bad idea, but my plan
was to pass two args to the other proc: (1) the column name and (2) the
desired new value. For all sorts of reasons, this fails badly. What's
the best way to do this? Thanks.Rick Charnes (rickxyz--nospam.zyxcharnes@.thehartford.com) writes:
> In my SQL 2000 stored proc I have a few dozen 'UPDATE mytable...'
> statements, all updating the same table. I'd like to do error-checking
> for these UPDATE statements all in one place, and I figured I'd create a
> separate proc. I found out quickly that this is a bad idea, but my plan
> was to pass two args to the other proc: (1) the column name and (2) the
> desired new value. For all sorts of reasons, this fails badly. What's
> the best way to do this? Thanks.
Error-check after each statement. Error-checking in SQL 2000 is a tedious
task. It's a lot better in SQL 2005.
For some more detailed tips I have an article on my web site:
http://www.sommarskog.se/error-handling-II.html.
And don't pass column or table names as parameters. That will leave you
in a quagmire of dynamic SQL.
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|||>> I have a few dozen 'UPDATE mytable...' <<
Try to combine the UPDATEs into one statement (or as few as possible)
using CASE expressions, subqueries, etc. Constraints on the base table
should be doing the data scrubbing for you.|||Thanks for this excellent article. So there's no way (other than using
dynamic SQL) that will allow me to put my UPDATE statements in a
separate object where I can then do my error-checking? (Sorry if I'm
being repetitive!)
In article <Xns97D35E5686AD1Yazorman@.127.0.0.1>, esquel@.sommarskog.se
says...
> Rick Charnes (rickxyz--nospam.zyxcharnes@.thehartford.com) writes:
> Error-check after each statement. Error-checking in SQL 2000 is a tedious
> task. It's a lot better in SQL 2005.
> For some more detailed tips I have an article on my web site:
> http://www.sommarskog.se/error-handling-II.html.
> And don't pass column or table names as parameters. That will leave you
> in a quagmire of dynamic SQL.
>
>|||Rick Charnes (rickxyz--nospam.zyxcharnes@.thehartford.com) writes:
> Thanks for this excellent article. So there's no way (other than using
> dynamic SQL) that will allow me to put my UPDATE statements in a
> separate object where I can then do my error-checking? (Sorry if I'm
> being repetitive!)
Well,
UPDATE tbl
SET col1 = CASE @.column WHEN 'col1' THEN @.value ELSE col1 END,
col2 = CASE @.column WHEN 'col1' THEN @.value ELSE col2 END,
..
But if the columns are different data type, you will have to deal
with that in the CASE statement. You could pass @.value as sql_variant,
and have an explicit convert in each CASE.
But by now, having multiplied error-checking is starting to sound really
palatable.
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

Sunday, February 26, 2012

Error:SqlConnection does not support parallel transactions"

This is my code in vb.net with Sql transaction
I am using insertcommand and update command for executing the sqlquery
in consecutive transactions as follows.
How can I achive parallel transactions in sql
------start of code-------
try
bID = Convert.ToInt32(Session("batchID"))
strSQL = ""
strSQL = "Insert into sessiondelayed (batchid,ActualEndDate) values (" & bID & ",'" & Format(d1, "MM/dd/yyyy") & "')"

sqlCon = New System.Data.SqlClient.SqlConnection(ConfigurationSettings.AppSettings("conString"))

Dim s1 As String = sqlCon.ConnectionString.ToString
sqlDaEndDate = New System.Data.SqlClient.SqlDataAdapter("Select * from sessiondelayed", sqlCon)
dsEndDate = New DataSet
sqlDaEndDate.Fill(dsEndDate)

dbcommandBuilder = New SqlClient.SqlCommandBuilder(sqlDaEndDate)

'sqlCon.BeginTransaction()
'sqlDaEndDate.InsertCommand.Transaction = tr
If sqlCon.State = ConnectionState.Closed Then
sqlCon.Open()
End If
sqlDaEndDate.InsertCommand = sqlCon.CreateCommand()
tr = sqlCon.BeginTransaction(IsolationLevel.ReadCommitted)
sqlDaEndDate.InsertCommand.Connection = sqlCon
sqlDaEndDate.InsertCommand.Transaction = tr
sqlDaEndDate.InsertCommand.CommandText = strSQL
sqlDaEndDate.InsertCommand.CommandType = CommandType.Text

sqlDaEndDate.InsertCommand.ExecuteNonQuery()
tr.Commit()
sqlDaEndDate.Update(dsEndDate)
sqlCon.Close()
End If
Catch es As Exception

Dim s2 As String = es.Message
If sqlCon.State = ConnectionState.Closed Then
sqlCon.Open()
End If
strSQL = " update SessionDelayed set ActualEndDate= '" & Format(d1, "MM/dd/yyyy") & "' where batchid=" & bID & ""
sqlDaEndDate.UpdateCommand = sqlCon.CreateCommand()
tr1 = sqlCon.BeginTransaction(IsolationLevel.ReadCommitted)
sqlDaEndDate.UpdateCommand.Connection = sqlCon
sqlDaEndDate.UpdateCommand.Transaction = tr1
sqlDaEndDate.UpdateCommand.CommandText = strSQL
sqlDaEndDate.UpdateCommand.CommandType = CommandType.Text
sqlDaEndDate.UpdateCommand.ExecuteNonQuery()
tr1.Commit()
sqlDaEndDate.Update(dsEndDate)
sqlCon.Close()

End Try

'----End------

You can't since connection is basically tied to a transaction and there can be one at a time.

You either:
- use the same connection and share the same transaction
- use separate connections and therefore separate transactions

If you use v2.0 TransactionScope class might help your task.

Friday, February 24, 2012

Error: Timeout Expired

I have an Update stored procedure that is used to update four tables at the same time. The issue is that it works perfect when i run the application in local server,but when i upload the application on to the server that is located in U.S, it gives an error "System.Data.SqlClient.SqlException: Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding."

I think the sqlCommand is timing out and the value is not returned. Is there a workaround to this issue? What could be the reason for this?

Any ideas.. Please help..

Do any of the tables you are tying to update have indexes on them? If not, adding indexes might help.

Ryan

|||How long will it takes to complete update on the local? Try to set the SqlCommand.CommandTimeout (default to 30 secs) to a larger value, as well as the SqlConnection.ConnectionTimeout.|||I haven't given any indexes.. But usually indexes are helpful when you perform a search on the data , rt? Will it help if while i update a table.. Im not sure.. could you give me more info on this? pls.. Smile

|||I tried specifying the CommandTimeout to a larger value (4 minutes) and it still times out.. Is there a diff approach?|||

Developer_.NET:
I haven't given any indexes.. But usually indexes are helpful when you perform a search on the data , rt? Will it help if while i update a table.. Im not sure.. could you give me more info on this? pls..

Seehttp://www.odetocode.com/Articles/70.aspx.

Ryan

Error: There is already an open DataReader associated with this Command

I'm trying to loop through all the records in a recordset and perform a database update within the loop. The problem is that you can't have more than one datareader open at the same time. How should I be doing this?

cmdPhoto =New SqlCommand("select AuthorityID,AuthorityName,PREF From qryStaffSearch where AuthorityType='User' Order by AuthorityName", conWhitt)

conWhitt.Open()

dtrPhoto = cmdPhoto.ExecuteReader

While dtrPhoto.Read()

IfNot File.Exists("D:WhittNetLive\Web\images\staffphotos\pat_images_resize\" & dtrPhoto("PRef") &".jpg")Then

cmdUpdate =New SqlCommand("Update tblAuthority Set NoPhoto = 1 Where AuthorityID =" & dtrPhoto("AuthorityID"), conWhitt)

cmdUpdate.ExecuteNonQuery()

EndIf

EndWhile

Thanks

What you could do is create a update sub that you just passed the ID of the photo to, which then set the NoPhoto to 1

i.e.

While dtrPhoto.Read()

IfNot File.Exists("D:WhittNetLive\Web\images\staffphotos\pat_images_resize\" & dtrPhoto("PRef") &".jpg")Then

SetPhoto(dtrPhoto("AuthorityID"))

cmdUpdate.ExecuteNonQuery()

Then create a sub

Sub SetPhoto(Byval id as Integer)

' Your database stuff

cmdUpdate =New SqlCommand("Update tblAuthority Set NoPhoto = 1 Where AuthorityID =" & id, conWhitt)

End Sub

Hope that helps Smile

|||

Thanks, I thought that would work but I get the same error Sad

PrivateSub Page_Load(ByVal senderAs System.Object, _

ByVal eAs System.EventArgs)HandlesMyBase.Load

Dim cmdPhotoAs SqlCommand

Dim dtrPhotoAs SqlDataReader

cmdPhoto =New SqlCommand("select AuthorityID,AuthorityName,PREF From qryStaffSearch where AuthorityType='User' Order by AuthorityName", conWhitt)

conWhitt.Open()

dtrPhoto = cmdPhoto.ExecuteReader

While dtrPhoto.Read()

IfNot File.Exists("D:WhittNetLive\Web\images\staffphotos\pat_images_resize\" & dtrPhoto("Pref") &".jpg")And dtrPhoto("Pref") <>""Then

NoPhotoUpdate(dtrPhoto("Pref"))

EndIf

EndWhile

conWhitt.Close()

dtrPhoto.Close()

EndSub

Sub NoPhotoUpdate(ByVal PrefAsInteger)

Dim cmdUpdateAs SqlCommand

cmdUpdate =New SqlCommand("Update tblAuthority Set NoPhoto = 1 Where Pref =" & Pref, conWhitt)

cmdUpdate.ExecuteNonQuery()

EndSub

|||

Hi there

Sorry i didnt reply sooner, i was moving house!

The problem your having is that the NoPhotoUpdate sub cannot access the datareader of the page_load sub.

(its a bit of a git to get your head around at first, then when you do you will wonder how you ever managed without it!)

You might want to get a book on OOP for asp.net - The dummies book one is quite good (and written by a comedian!), but covers only vb1.1 (AFAIK).

Anyways that said..

Private Sub Page_Load(ByVal sender As System.Object, _

ByVal e As System.EventArgs) Handles MyBase.Load
SelectPhoto()

End Sub

Sub SelectPhoto()
Dim cmdPhoto As SqlCommand
Dim dtrPhoto As SqlDataReader

cmdPhoto = New SqlCommand("select AuthorityID,AuthorityName,PREF From qryStaffSearch where AuthorityType='User' Order by AuthorityName", conWhitt)

conWhitt.Open()
dtrPhoto = cmdPhoto.ExecuteReader

While dtrPhoto.Read()

If Not File.Exists("D:WhittNetLive\Web\images\staffphotos\pat_images_resize\" & dtrPhoto("Pref") & ".jpg") And dtrPhoto("Pref") <> "" Then

NoPhotoUpdate(dtrPhoto("Pref"))

End If

End While

conWhitt.Close()

dtrPhoto.Close()

End Sub

Sub NoPhotoUpdate(ByVal Pref As Integer)

Dim strConnectionString As String
strConnectionString = ' Your connection String

Dim DBConn As New SqlConnection(strConnectionString)
Dim DBCmd As New SqlCommand
Dim DBAdap As New SqlDataAdapter
DBConn.Open()

DBCmd = New SqlCommand("Update tblAuthority Set NoPhoto = 1 Where Pref = @.Pref", DBConn)
DBCmd.Parameters.Add("@.Pref", SqlDbType.NVarChar).Value = Pref
DBCmd.ExecuteNonQuery()
DBCmd.Dispose()
DBAdap.Dispose()
DBConn.Close()
DBConn = Nothing

End Sub

The whole @. thing is a safer way to pass paramaters to your SQL

http://weblogs.asp.net/scottgu/archive/2006/09/30/Tip_2F00_Trick_3A00_-Guard-Against-SQL-Injection-Attacks.aspx for a explanation

Notice how each sub is a seperate little program, with one activating the other whilst passing data.

I hope ive been of some help

|||

Thanks that works. Thanks also for the heads up about sql injection attacks. I do normally pass my parameters the safe way but was not aware of the dangers of not doing so.

Enjoy your new house Smile

|||

I'm having the same problem and i have to believe there is a better solution then creating a sep sub and then creating another connection. Based on your solution you should problably just create a second connection in the first sub and just use that, no need for the whole extra nophotoupdate sub.

Having said that there needs to be a solution for activly using the same connection at the same time. In good old ADO days we were able to open many recordsets and execute sql commands all at the same time with one DB connection. There is a way to do this here.

RageMonkey:

Sub NoPhotoUpdate(ByVal Pref As Integer)

End Sub

|||

RageMonkey:

Sub NoPhotoUpdate(ByVal Pref As Integer)

End Sub

|||

I found the solution on the msdn forums. Seems to only work for SQL 2005

"This is due to a change in the default setting for MARs. It used to be on by default and we changed it to off by default post RC1. So just change your connection string to add it back (add MultipleActiveResultSets=True to connection string)."

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=123691&SiteID=1

Wednesday, February 15, 2012

Error: Server: Msg 1101, Level 17, State 10 when I run script

Hi All,
I can't not run the query to update data but it raice an error as below:
"Server: Msg 1101, Level 17, State 10, Line 1
Could not allocate new page for database 'TEMPDB'. There are no more pages
available in filegroup DEFAULT. Space can be created by dropping objects,
adding additional files, or allowing file growth."
HDD free space: 5GB
RAM: 1GB
CPU: 2.8MHz
SQL server 2000
Windows 2000 server
What should I do?
Please advice me on this.
Thanks in advance,
TNAs per the error message: Increate the size for your tempdb database (data, not log).
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"TN" <TN@.discussions.microsoft.com> wrote in message
news:72E79234-7E66-4C0F-9198-A2564A99BEE4@.microsoft.com...
> Hi All,
> I can't not run the query to update data but it raice an error as below:
> "Server: Msg 1101, Level 17, State 10, Line 1
> Could not allocate new page for database 'TEMPDB'. There are no more pages
> available in filegroup DEFAULT. Space can be created by dropping objects,
> adding additional files, or allowing file growth."
> HDD free space: 5GB
> RAM: 1GB
> CPU: 2.8MHz
> SQL server 2000
> Windows 2000 server
> What should I do?
> Please advice me on this.
> Thanks in advance,
> TN
>|||My tempdb is umlimited file growth.
"Tibor Karaszi" wrote:
> As per the error message: Increate the size for your tempdb database (data, not log).
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "TN" <TN@.discussions.microsoft.com> wrote in message
> news:72E79234-7E66-4C0F-9198-A2564A99BEE4@.microsoft.com...
> > Hi All,
> >
> > I can't not run the query to update data but it raice an error as below:
> >
> > "Server: Msg 1101, Level 17, State 10, Line 1
> > Could not allocate new page for database 'TEMPDB'. There are no more pages
> > available in filegroup DEFAULT. Space can be created by dropping objects,
> > adding additional files, or allowing file growth."
> >
> > HDD free space: 5GB
> > RAM: 1GB
> > CPU: 2.8MHz
> > SQL server 2000
> > Windows 2000 server
> >
> > What should I do?
> > Please advice me on this.
> >
> > Thanks in advance,
> > TN
> >
>
>|||Sometimes autogrow doesn't grow the files fast enough for the operation that needs the storage.
Solution is to pre-allocate storage.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"TN" <TN@.discussions.microsoft.com> wrote in message
news:473EFAC4-F9E0-4CE8-A895-C146BCE11C6C@.microsoft.com...
> My tempdb is umlimited file growth.
> "Tibor Karaszi" wrote:
>> As per the error message: Increate the size for your tempdb database (data, not log).
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>>
>> "TN" <TN@.discussions.microsoft.com> wrote in message
>> news:72E79234-7E66-4C0F-9198-A2564A99BEE4@.microsoft.com...
>> > Hi All,
>> >
>> > I can't not run the query to update data but it raice an error as below:
>> >
>> > "Server: Msg 1101, Level 17, State 10, Line 1
>> > Could not allocate new page for database 'TEMPDB'. There are no more pages
>> > available in filegroup DEFAULT. Space can be created by dropping objects,
>> > adding additional files, or allowing file growth."
>> >
>> > HDD free space: 5GB
>> > RAM: 1GB
>> > CPU: 2.8MHz
>> > SQL server 2000
>> > Windows 2000 server
>> >
>> > What should I do?
>> > Please advice me on this.
>> >
>> > Thanks in advance,
>> > TN
>> >
>>|||Dear All,
I have the same problem with HP insight Manager 5.1
I have System Errors 17052, 17055 which related to storage full or file log full , I don't know
I'd like to say that I don't know how to deal with SQL Server as I'm not a programmer
I'm a system and infrastructure engineer, but I stucked in that situation, and I will appreciate it much to some one to tell me how to do what u have said ( i.e increase file size, or change location or file growth increase)
thanks alo
From http://www.developmentnow.com/g/118_2005_3_0_0_482538/Error-Server-Msg-1101-Level-17-State-10-when-I-run-script.ht
Posted via DevelopmentNow.com Group
http://www.developmentnow.com|||On Oct 6, 9:25 pm, Egyptian Mind<msaleh.m...@.gmail.com> wrote:
> Dear All,
> I have the same problem with HP insight Manager 5.1
> I have System Errors 17052, 17055 which related to storage full or file log full , I don't know
> I'd like to say that I don't know how to deal with SQL Server as I'm not a programmer
> I'm a system and infrastructure engineer, but I stucked in that situation, and I will appreciate it much to some one to tell me how to do what u have said ( i.e increase file size, or change location or file growth increase)
> thanks alot
> Fromhttp://www.developmentnow.com/g/118_2005_3_0_0_482538/Error-Server-Ms...
> Posted via DevelopmentNow.com Groupshttp://www.developmentnow.com
Does it help if you set the recovery mode to simple? Also you may want
to truncate the log file.

Monday, March 26, 2012

escaping data for update query

escaping data for update query

escaping data for update query

escaping data for update query

Monday, March 19, 2012

ERRORS on Insert/Update DB2 from SQL Enterprise Manager (linked server)

Wednesday, March 7, 2012

error-checking all in one place

Sunday, February 26, 2012

Error:SqlConnection does not support parallel transactions"

Friday, February 24, 2012

Error: Timeout Expired

Error: There is already an open DataReader associated with this Command

Wednesday, February 15, 2012

Error: Server: Msg 1101, Level 17, State 10 when I run script

Error: Server: Msg 1101, Level 17, State 10 when I run script

Error: Server: Msg 1101, Level 17, State 10 when I run script

ERROR: Subreport

Blog Archive

About Me