Connected but Recordsaffected=-1

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Connected but Recordsaffected=-1

Author

Topic

Paulktl
Starting Member

5 Posts

Posted - 2010-03-20 : 14:42:19

Dear All, I am a fool in this, I been working for days in trying to get this work but failed. Kindly help anyone, pls!

Here is my code:
//Affected Code Start//
MSSQL_Con.ConnectionString = "Data Source=.\" & SQLName & ";Initial
Catalog=" & TempDBStr & ";Integrated
Security=SSPI;Trusted_Connection=Yes;Connect Timeout=30;User
Instance=False;"

SqlStr = "SELECT * FROM Results INNER JOIN Points ON
Results.Point_ID = Points.ID " & _
"WHERE Points.Name='PENT-GE1A_10Min' and Results.Epoch BETWEEN "
& DataExtTime.ToOADate & _
" AND " & Now().ToOADate & " ORDER BY Points.Name, Results.Epoch
ASC"

sqlCmd = New SqlCommand(SqlStr, MSSQL_Con)
MSSQL_Con.Open()
MSSQL_Reader = sqlCmd.ExecuteReader

If MSSQL_Reader.RecordsAffected > 0 Then
'XXXX
End If
//Code End//

I manage to connect as "MSSQL_Con.Open()" passed during debug with no error, and the "MSSQL_Reader = sqlCmd.ExecuteReader" also executed with error, but it gave me 'MSSQL_Reader.RecordsAffected=-1' at the "If" statement.

I had tested the sql query statement with MS SQL Server Management Studio Express (Sql query String copied while my software on debug) and knew that the sql statement produce the right result.

Please! All the pro's and experts, kindly help.

Yours,

paul

Paulktl

RobertKaucher
Posting Yak Master

169 Posts

Posted - 2010-03-20 : 22:30:28

Have you printed the query to debug console so you can view it? I do some basic stuff in C# and PowerShell so your code is almost like reading Chaucer in the original, but I would bet there is something funky with your dates. Maybe a lack of single quotes?

===
http://www.ElementalSQL.com/

Paulktl
Starting Member

5 Posts

Posted - 2010-03-21 : 07:36:56

Dear Robert,
Thanks for your response!
Yes! As I said:
I had tested the sql query statement with MS SQL Server Management Studio Express (Sql query String copied while my software on debug) and knew that the sql statement works.
I guess is the connection string, but as a novice, I don't know what is wrong.

Another question I ahve here is, the Database is actually update by another software, my software should just access to read and not write, but I don't know how can I access it, because whenever the software is running (which need to be ON while I am accessing the database), I will get access denied error, HOW can I solve this problem.

Thank you All the pro's and experts, kindly help.

Paulktl

Sachin.Nand

2937 Posts

Posted - 2010-03-21 : 07:53:23

Just try using a SQLadapater.See if it makes any difference?
Also with the current statements remove the date condition & see.

PBUH

RobertKaucher
Posting Yak Master

169 Posts

Posted - 2010-03-21 : 11:36:21

quote:
Originally posted by Paulktl

Dear Robert,
Thanks for your response!
Yes! As I said:
I had tested the sql query statement with MS SQL Server Management Studio Express (Sql query String copied while my software on debug) and knew that the sql statement works.
I guess is the connection string, but as a novice, I don't know what is wrong.

Another question I ahve here is, the Database is actually update by another software, my software should just access to read and not write, but I don't know how can I access it, because whenever the software is running (which need to be ON while I am accessing the database), I will get access denied error, HOW can I solve this problem.

Thank you All the pro's and experts, kindly help.

Paulktl

No, you misunderstood me. I believe the issue is with the query as it is being issued by your code. When you issue the statement in SSMS it might work, but then when you alter the query adding in the application code variables something is wrong.

===
http://www.ElementalSQL.com/

Paulktl
Starting Member

5 Posts

Posted - 2010-03-21 : 11:58:22

Dear Idera,
I had tried with the most basic query "Select * From Results" it produce the same result "MSSQL_Reader.RecordsAffected=-1"
I don't understand what you meant by "Just try using a SQLadapater.See if it makes any difference?"

Dear Robert,
I understood what you said, in VB.net I can run in debug mode and break after where the sql query is defined, then go into the immediate window to output the sql query as string, the copy and paste into SSMS. Thanks, it works in SSMS but not in the program.

Thanks and regards,

paul

Paulktl

Sachin.Nand

2937 Posts

Posted - 2010-03-21 : 12:29:07

Hi,
What I meant was try using a SQLdataadapter instead of datareader.
dim cmd as sqlcommand
dim da as sqldataadapter
dim dt as new datatable
cmd=new sqlcommand("Your Query",connection)
da=new sqldataadapter(cmd)
da.fill(dt)
& then check for rows in dt

PBUH

Paulktl
Starting Member

5 Posts

Posted - 2010-03-22 : 01:18:26

Dear Idera,

Thank you very much it work with your method.
Now come to my second question, I decide to use Reader because I just want to have read access to the Database, as the Database is Manage (update) by another commercial software.
Now with your method I am able to run my own software by it self, but when that the Software that manages the Database is running, I will get the following error:

//Cannot open database "C:\Users\Public\Documents\Leica Geosystems\GeoMoS\Data\PentaOcean_GPS_PP_2010-03-08.mdf" requested by the login. The login failed. Login failed for user 'KTLim-Acer\KT Lim'//

How can I access the have read access to the same Database while the other program is running?

Thank you so much for your professional advice, by the way, where are you from?

Paulktl

Paulktl
Starting Member

5 Posts

Posted - 2010-03-22 : 07:01:48

Dear All,
Can anyone help, I been playing with the codes for the whole day and still get no way, is there any setting that I have to do to the SQL Server or the database to enable multiple user access to this database?
I had even tried with Excel to import external data and I got the same error message as well.
Please all the pro's and experts, please help.

Paulktl

Sachin.Nand

2937 Posts

Posted - 2010-03-22 : 13:10:40

Well your problem is an open one.There can be many reasons.Try setting "User instance = False" in ur connection string or set impersonate = false or run ur .net application with the same user credentials as ur other software that manages ur database.

PBUH

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources