ERROR: Subreport: command

Showing posts with label command. Show all posts

Thursday, March 22, 2012

escape characters

Hi,

I need to insert this command into a table, but I can't because.

insert XXX

set column1 = isnull(Title,'') +

case when (case when title is null then 1 else 0 end) = 1 then '' else ' ' end

+ isnull(Last_name,'') +

case when (case when First_name is null then 1 else 0 end) = 1 then '' else ' ' end

+ isnull(First_name,'') +

case when (case when Middle_initial is null then 1 else 0 end)= 1 then '' else ' ' +

isnull(Middle_initial,'') END

I don't know how to use escape sequence, I need insert just once

thanks

i really dont understnd your problem.

does this is what you want

set column1 = isnull(Title,'') +

case when title is null then '' else ' ' end

+ isnull(Last_name,'') +

case when First_name is null then '' else ' ' end

+ isnull(First_name,'') +

case when Middle_initial is null then '' else ' ' end +

isnull(Middle_initial,'') END

if u are facing the quote problem then check this out

SET QUOTED_IDENTIFIER ON

SELECT '"hi"'

SET QUOTED_IDENTIFIER OFF

SELECT """hi"""

SELECT "'hi'"

this might give you the hint of escape sequence.

Regards,

Thanks.

Gurpreet S. Gill

|||

hi,

please give us sample source table
and the output

thanks,

joey

|||

Alessandro,

You could do something like this instead:

update XXX
set Column1 = replace(replace(Title + ' ' + Last_Name + ' ' + First_Name + ' ' + Middle_initial, '@.@.', '@.'), '@.@.', '@.')

NOTE: Use Spaces instead of @. symbols.

Wednesday, March 21, 2012

errors while running ssis packages from the command prompt.

I have created ssis package. It has been successfully running at UI level.

But when i am trying to execute it from the command prompt by using dtexec utility it is showing the following error messages.

Error: 2005-12-23 17:01:57.67
Code: 0xC00470FE
Source: Data Flow Task DTS.Pipeline
Description: The product level is insufficient for component "Flat File Source" (1).
End Error
Error: 2005-12-23 17:01:57.67
Code: 0xC00470FE
Source: Data Flow Task DTS.Pipeline
Description: The product level is insufficient for component "Script Component" (9).
End Error

i have entered the command as follows.

dtexec /f "c:\somefolder\package1.dtsx"

Any points regarding this issue would be helpful.

Take a look here:

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

Same problem is being discussed.

HTH,
Ovidiu

|||

I am receiving a similar error. We are running SS2005 Enterprise Edition. We can connect to SSIS with SS Management Studio so that verifies that it's installed. Here is our error log. Did you solve your probelm, and if so, how?

*************************************Run GPS_EXTRACT_QA.dtsx*********************************************
Microsoft (R) SQL Server Execute Package Utility
Version 9.00.1399.06 for 32-bit
Copyright (C) Microsoft Corp 1984-2005. All rights reserved.

Started: 5:14:34 PM
Progress: 2006-01-26 17:14:36.01
Source: Data Flow Task
Validating: 0% complete
End Progress
Progress: 2006-01-26 17:14:36.03
Source: Data Flow Task
Validating: 50% complete
End Progress
Progress: 2006-01-26 17:14:36.21
Source: Data Flow Task
Validating: 100% complete
End Progress
Progress: 2006-01-26 17:14:36.53
Source: Execute SQL Task
Executing query "truncate table sap_extract_staging
delete from sap".: 100% complete
End Progress
Progress: 2006-01-26 17:14:36.54
Source: Data Flow Task
Validating: 0% complete
End Progress
Progress: 2006-01-26 17:14:36.56
Source: Data Flow Task
Validating: 50% complete
End Progress
Progress: 2006-01-26 17:14:36.60
Source: Data Flow Task
Validating: 100% complete
End Progress
Error: 2006-01-26 17:14:36.60
Code: 0xC00470FE
Source: Data Flow Task DTS.Pipeline
Description: The product level is insufficient for component "Flat File Source" (749).
End Error
Warning: 2006-01-26 17:14:36.60
Code: 0x80019002
Source: GPS_EXTRACT_QA
Description: The Execution method succeeded, but the number of errors raised (1) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors.
End Warning
DTExec: The package execution returned DTSER_FAILURE (1).
Started: 5:14:34 PM
Finished: 5:14:36 PM
Elapsed: 2.562 seconds

|||

I am also getting same error.

any sugessions regarding this issue

sql

errors while running ssis packages from the command prompt.

I have created ssis package. It has been successfully running at UI level.

But when i am trying to execute it from the command prompt by using dtexec utility it is showing the following error messages.

i have entered the command as follows.

dtexec /f "c:\somefolder\package1.dtsx"

Any points regarding this issue would be helpful.

Take a look here:

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

Same problem is being discussed.

HTH,
Ovidiu

|||

I am also getting same error.

any sugessions regarding this issue

errors while running ssis packages from the command prompt.

I have created ssis package. It has been successfully running at UI level.

But when i am trying to execute it from the command prompt by using dtexec utility it is showing the following error messages.

i have entered the command as follows.

dtexec /f "c:\somefolder\package1.dtsx"

Any points regarding this issue would be helpful.

Take a look here:

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

Same problem is being discussed.

HTH,
Ovidiu

|||

I am also getting same error.

any sugessions regarding this issue

Errors when running DBCC CheckCatalog command

One of my users reported an error when testing against one of our Development
Servers, against a user databases. The error the application generated
contained the following:
Could not find row in sysindexes for database ID 9, object ID 1284406774,
index ID -1. Run DBCC CHECKTABLE on sysindexes.
I ran DBCC CHECKTABLE (sysindexes) and it came back clean.
I then ran DBCC CHECKDB, which came back clean.
Then, I ran DBCC CHECKALLOC, which came back clean.
Next, I ran DBCC CHECKCATALOG, which gave the following errors:
Server: Msg 2513, Level 16, State 2, Line 2
Table error: Object ID 823165107 (object '823165107') does not match between
'SYSCOLUMNS' and 'SYSOBJECTS'.
Server: Msg 2513, Level 16, State 1, Line 2
Table error: Object ID 823165107 (object '823165107') does not match between
'SYSCOLUMNS' and 'SYSOBJECTS'.
Server: Msg 2513, Level 16, State 1, Line 2
Table error: Object ID 823165107 (object '823165107') does not match between
'SYSCOLUMNS' and 'SYSOBJECTS'.
Server: Msg 2513, Level 16, State 1, Line 2
Table error: Object ID 823165107 (object '823165107') does not match between
'SYSCOLUMNS' and 'SYSOBJECTS'.
Server: Msg 2513, Level 16, State 1, Line 2
Table error: Object ID 823165107 (object '823165107') does not match between
'SYSCOLUMNS' and 'SYSOBJECTS'.
Server: Msg 2513, Level 16, State 1, Line 2
Table error: Object ID 823165107 (object '823165107') does not match between
'SYSCOMMENTS' and 'SYSOBJECTS'.
DBCC results for 'current database'.
DBCC execution completed. If DBCC printed error messages, contact your
system administrator.
I was not too worried, since this was a DEV server, but then I ran the DBCC
CHECKCATAOG command against our QA and then our Production server, and all
of them gave me the same error for the DBCC CHECKCATAOG command.
Any ideas on what I can try next? I don't think this is a corrupt index and
I can't run DBCC DBREINDEX against a system table.
Thank you in advance.
Sam
Are you on 7.0? If so, search KB for 2513, I found a few articles. If you are on 2000, I suggest you
open a case with MS Support for this.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Sam" <Sam@.discussions.microsoft.com> wrote in message
news:9FF30A6F-9DC1-4DE6-838E-8BA9D1FD15F4@.microsoft.com...
> One of my users reported an error when testing against one of our Development
> Servers, against a user databases. The error the application generated
> contained the following:
> Could not find row in sysindexes for database ID 9, object ID 1284406774,
> index ID -1. Run DBCC CHECKTABLE on sysindexes.
> I ran DBCC CHECKTABLE (sysindexes) and it came back clean.
> I then ran DBCC CHECKDB, which came back clean.
> Then, I ran DBCC CHECKALLOC, which came back clean.
> Next, I ran DBCC CHECKCATALOG, which gave the following errors:
> Server: Msg 2513, Level 16, State 2, Line 2
> Table error: Object ID 823165107 (object '823165107') does not match between
> 'SYSCOLUMNS' and 'SYSOBJECTS'.
> Server: Msg 2513, Level 16, State 1, Line 2
> Table error: Object ID 823165107 (object '823165107') does not match between
> 'SYSCOLUMNS' and 'SYSOBJECTS'.
> Server: Msg 2513, Level 16, State 1, Line 2
> Table error: Object ID 823165107 (object '823165107') does not match between
> 'SYSCOLUMNS' and 'SYSOBJECTS'.
> Server: Msg 2513, Level 16, State 1, Line 2
> Table error: Object ID 823165107 (object '823165107') does not match between
> 'SYSCOLUMNS' and 'SYSOBJECTS'.
> Server: Msg 2513, Level 16, State 1, Line 2
> Table error: Object ID 823165107 (object '823165107') does not match between
> 'SYSCOLUMNS' and 'SYSOBJECTS'.
> Server: Msg 2513, Level 16, State 1, Line 2
> Table error: Object ID 823165107 (object '823165107') does not match between
> 'SYSCOMMENTS' and 'SYSOBJECTS'.
> DBCC results for 'current database'.
> DBCC execution completed. If DBCC printed error messages, contact your
> system administrator.
> I was not too worried, since this was a DEV server, but then I ran the DBCC
> CHECKCATAOG command against our QA and then our Production server, and all
> of them gave me the same error for the DBCC CHECKCATAOG command.
> Any ideas on what I can try next? I don't think this is a corrupt index and
> I can't run DBCC DBREINDEX against a system table.
> Thank you in advance.
> Sam
>
>
sql

Errors when running DBCC CheckCatalog command

One of my users reported an error when testing against one of our Development
Servers, against a user databases. The error the application generated
contained the following:
Could not find row in sysindexes for database ID 9, object ID 1284406774,
index ID -1. Run DBCC CHECKTABLE on sysindexes.
I ran DBCC CHECKTABLE (sysindexes) and it came back clean.
I then ran DBCC CHECKDB, which came back clean.
Then, I ran DBCC CHECKALLOC, which came back clean.
Next, I ran DBCC CHECKCATALOG, which gave the following errors:
Server: Msg 2513, Level 16, State 2, Line 2
Table error: Object ID 823165107 (object '823165107') does not match between
'SYSCOLUMNS' and 'SYSOBJECTS'.
Server: Msg 2513, Level 16, State 1, Line 2
Table error: Object ID 823165107 (object '823165107') does not match between
'SYSCOLUMNS' and 'SYSOBJECTS'.
Server: Msg 2513, Level 16, State 1, Line 2
Table error: Object ID 823165107 (object '823165107') does not match between
'SYSCOLUMNS' and 'SYSOBJECTS'.
Server: Msg 2513, Level 16, State 1, Line 2
Table error: Object ID 823165107 (object '823165107') does not match between
'SYSCOLUMNS' and 'SYSOBJECTS'.
Server: Msg 2513, Level 16, State 1, Line 2
Table error: Object ID 823165107 (object '823165107') does not match between
'SYSCOLUMNS' and 'SYSOBJECTS'.
Server: Msg 2513, Level 16, State 1, Line 2
Table error: Object ID 823165107 (object '823165107') does not match between
'SYSCOMMENTS' and 'SYSOBJECTS'.
DBCC results for 'current database'.
DBCC execution completed. If DBCC printed error messages, contact your
system administrator.
I was not too worried, since this was a DEV server, but then I ran the DBCC
CHECKCATAOG command against our QA and then our Production server, and all
of them gave me the same error for the DBCC CHECKCATAOG command.
Any ideas on what I can try next? I don't think this is a corrupt index and
I can't run DBCC DBREINDEX against a system table.
Thank you in advance.
SamAre you on 7.0? If so, search KB for 2513, I found a few articles. If you are on 2000, I suggest you
open a case with MS Support for this.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Sam" <Sam@.discussions.microsoft.com> wrote in message
news:9FF30A6F-9DC1-4DE6-838E-8BA9D1FD15F4@.microsoft.com...
> One of my users reported an error when testing against one of our Development
> Servers, against a user databases. The error the application generated
> contained the following:
> Could not find row in sysindexes for database ID 9, object ID 1284406774,
> index ID -1. Run DBCC CHECKTABLE on sysindexes.
> I ran DBCC CHECKTABLE (sysindexes) and it came back clean.
> I then ran DBCC CHECKDB, which came back clean.
> Then, I ran DBCC CHECKALLOC, which came back clean.
> Next, I ran DBCC CHECKCATALOG, which gave the following errors:
> Server: Msg 2513, Level 16, State 2, Line 2
> Table error: Object ID 823165107 (object '823165107') does not match between
> 'SYSCOLUMNS' and 'SYSOBJECTS'.
> Server: Msg 2513, Level 16, State 1, Line 2
> Table error: Object ID 823165107 (object '823165107') does not match between
> 'SYSCOLUMNS' and 'SYSOBJECTS'.
> Server: Msg 2513, Level 16, State 1, Line 2
> Table error: Object ID 823165107 (object '823165107') does not match between
> 'SYSCOLUMNS' and 'SYSOBJECTS'.
> Server: Msg 2513, Level 16, State 1, Line 2
> Table error: Object ID 823165107 (object '823165107') does not match between
> 'SYSCOLUMNS' and 'SYSOBJECTS'.
> Server: Msg 2513, Level 16, State 1, Line 2
> Table error: Object ID 823165107 (object '823165107') does not match between
> 'SYSCOLUMNS' and 'SYSOBJECTS'.
> Server: Msg 2513, Level 16, State 1, Line 2
> Table error: Object ID 823165107 (object '823165107') does not match between
> 'SYSCOMMENTS' and 'SYSOBJECTS'.
> DBCC results for 'current database'.
> DBCC execution completed. If DBCC printed error messages, contact your
> system administrator.
> I was not too worried, since this was a DEV server, but then I ran the DBCC
> CHECKCATAOG command against our QA and then our Production server, and all
> of them gave me the same error for the DBCC CHECKCATAOG command.
> Any ideas on what I can try next? I don't think this is a corrupt index and
> I can't run DBCC DBREINDEX against a system table.
> Thank you in advance.
> Sam
>
>

Errors when running DBCC CheckCatalog command

One of my users reported an error when testing against one of our Developmen
t
Servers, against a user databases. The error the application generated
contained the following:
Could not find row in sysindexes for database ID 9, object ID 1284406774,
index ID -1. Run DBCC CHECKTABLE on sysindexes.
I ran DBCC CHECKTABLE (sysindexes) and it came back clean.
I then ran DBCC CHECKDB, which came back clean.
Then, I ran DBCC CHECKALLOC, which came back clean.
Next, I ran DBCC CHECKCATALOG, which gave the following errors:
Server: Msg 2513, Level 16, State 2, Line 2
Table error: Object ID 823165107 (object '823165107') does not match between
'SYSCOLUMNS' and 'SYSOBJECTS'.
Server: Msg 2513, Level 16, State 1, Line 2
Table error: Object ID 823165107 (object '823165107') does not match between
'SYSCOLUMNS' and 'SYSOBJECTS'.
Server: Msg 2513, Level 16, State 1, Line 2
Table error: Object ID 823165107 (object '823165107') does not match between
'SYSCOLUMNS' and 'SYSOBJECTS'.
Server: Msg 2513, Level 16, State 1, Line 2
Table error: Object ID 823165107 (object '823165107') does not match between
'SYSCOLUMNS' and 'SYSOBJECTS'.
Server: Msg 2513, Level 16, State 1, Line 2
Table error: Object ID 823165107 (object '823165107') does not match between
'SYSCOLUMNS' and 'SYSOBJECTS'.
Server: Msg 2513, Level 16, State 1, Line 2
Table error: Object ID 823165107 (object '823165107') does not match between
'SYSCOMMENTS' and 'SYSOBJECTS'.
DBCC results for 'current database'.
DBCC execution completed. If DBCC printed error messages, contact your
system administrator.
I was not too worried, since this was a DEV server, but then I ran the DBCC
CHECKCATAOG command against our QA and then our Production server, and all
of them gave me the same error for the DBCC CHECKCATAOG command.
Any ideas on what I can try next? I don't think this is a corrupt index and
I can't run DBCC DBREINDEX against a system table.
Thank you in advance.
SamAre you on 7.0? If so, search KB for 2513, I found a few articles. If you ar
e on 2000, I suggest you
open a case with MS Support for this.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Sam" <Sam@.discussions.microsoft.com> wrote in message
news:9FF30A6F-9DC1-4DE6-838E-8BA9D1FD15F4@.microsoft.com...
> One of my users reported an error when testing against one of our Developm
ent
> Servers, against a user databases. The error the application generated
> contained the following:
> Could not find row in sysindexes for database ID 9, object ID 1284406774,
> index ID -1. Run DBCC CHECKTABLE on sysindexes.
> I ran DBCC CHECKTABLE (sysindexes) and it came back clean.
> I then ran DBCC CHECKDB, which came back clean.
> Then, I ran DBCC CHECKALLOC, which came back clean.
> Next, I ran DBCC CHECKCATALOG, which gave the following errors:
> Server: Msg 2513, Level 16, State 2, Line 2
> Table error: Object ID 823165107 (object '823165107') does not match betwe
en
> 'SYSCOLUMNS' and 'SYSOBJECTS'.
> Server: Msg 2513, Level 16, State 1, Line 2
> Table error: Object ID 823165107 (object '823165107') does not match betwe
en
> 'SYSCOLUMNS' and 'SYSOBJECTS'.
> Server: Msg 2513, Level 16, State 1, Line 2
> Table error: Object ID 823165107 (object '823165107') does not match betwe
en
> 'SYSCOLUMNS' and 'SYSOBJECTS'.
> Server: Msg 2513, Level 16, State 1, Line 2
> Table error: Object ID 823165107 (object '823165107') does not match betwe
en
> 'SYSCOLUMNS' and 'SYSOBJECTS'.
> Server: Msg 2513, Level 16, State 1, Line 2
> Table error: Object ID 823165107 (object '823165107') does not match betwe
en
> 'SYSCOLUMNS' and 'SYSOBJECTS'.
> Server: Msg 2513, Level 16, State 1, Line 2
> Table error: Object ID 823165107 (object '823165107') does not match betwe
en
> 'SYSCOMMENTS' and 'SYSOBJECTS'.
> DBCC results for 'current database'.
> DBCC execution completed. If DBCC printed error messages, contact your
> system administrator.
> I was not too worried, since this was a DEV server, but then I ran the DBC
C
> CHECKCATAOG command against our QA and then our Production server, and al
l
> of them gave me the same error for the DBCC CHECKCATAOG command.
> Any ideas on what I can try next? I don't think this is a corrupt index an
d
> I can't run DBCC DBREINDEX against a system table.
> Thank you in advance.
> Sam
>
>

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: 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

Sunday, February 19, 2012

Error: The count aggregate operation cannot take a void type data type as an argument.

TITLE: Microsoft Visual Studio

An error occurred while executing a command.
Message: The count aggregate operation cannot take a void type data type as an argument.
The count-unique aggregate operation cannot take a void type data type as an argument.
The count aggregate operation cannot take a void type data type as an argument.
The count-unique aggregate operation cannot take a void type data type as an argument.

I'm getting this error message when trying to create a model for an entire database. Anyone know there is a specific reason why? The data type in the database is not void. Each of the items that were referanced in the error message have valid data types.

Thanks in advance.

What datatype causes this error?

Thursday, March 22, 2012

escape characters

Wednesday, March 21, 2012

errors while running ssis packages from the command prompt.

errors while running ssis packages from the command prompt.

errors while running ssis packages from the command prompt.

Errors when running DBCC CheckCatalog command

Errors when running DBCC CheckCatalog command

Errors when running DBCC CheckCatalog command

Sunday, February 26, 2012

Error:SqlConnection does not support parallel transactions"

Friday, February 24, 2012

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

Sunday, February 19, 2012

Error: The count aggregate operation cannot take a void type data type as an argument.

ERROR: Subreport

Blog Archive

About Me