Database

23-Jul-12 23:07

When I Put SET NOCOUNT ON before sql statement its giving -1 rows effected in sql server 2005.

But in my code i need rows effected 0 . How to do that ?

Eddy Vluggen24-Jul-12 0:18

24-Jul-12 0:18

You told the server not to count, so it returns -1.

Why do you "need" a count of 0?

Bastard Programmer from Hell Suspicious | :suss:

if you can't read my code, try converting it here[^]

Bernhard Hiller24-Jul-12 0:33

Bernhard Hiller

24-Jul-12 0:33

That's an old concept for returning a value when actually no value can be returned (e.g. due to an error).
Here, you told the server that it must not count the rows affected. When you now ask "how many rows were affected" it cannot return the "correct" value, you ought to expect an exception, but for compatibility with legacy applications, it returns a number.
Any whole number from 0 to inifinite would pretend to be the correct result. Hence, the server must not return any value in that range. And therefore, it returns -1: it is not possible that a negative number of rows was affected.
Get used to that old concept, you may encounter it more often.

Member 348763224-Jul-12 0:48

24-Jul-12 0:48

Dim Ssql
Dim dbstatus
Dim rs

'*************

Ssql = " Select * from User_Access A Where A.User_pwd ='1234' "
set rs = Server.CreateObject("ADODB.RecordSet")
rs = DB.execute(Ssql,dbstatus)
Response.Write(dbstatus)
If dbstatus <> 0 Then
'Error code here
Else
'code here
END IF

'*************

Above is the code working ok . Actually this is written in dll. Now code will give error if "dbstatus" is other than 0 . Here I'm getting -1 always in my environment.

Eddy Vluggen24-Jul-12 2:34

24-Jul-12 2:34

..and what's the problem with changing that code to reflect reality? There aren't 0 records affected, but an "uncounted" number;

If dbstatus <> -1 Then
'Error code here
Else
'code here
END IF

Then again, your If-Else wouldn't be very valuable, since it will always return -1 (unless an exception occurs or you raiserror)

Bastard Programmer from Hell Suspicious | :suss:

if you can't read my code, try converting it here[^]

Member 348763224-Jul-12 5:41

24-Jul-12 5:41

I can't do that ...

Eddy Vluggen24-Jul-12 7:31

24-Jul-12 7:31

..add a SELECT 1 WHERE 1 = 0 to your Sql-statement. IIRC and you execute a batch, only the affected rows of the last statement are returned.

Bastard Programmer from Hell Suspicious | :suss:

if you can't read my code, try converting it here[^]

_Amy24-Jul-12 2:57

_Amy

24-Jul-12 2:57

Hi,
Because you are telling to server, "don't count the numbers of rows affected".

If you need the numbers of rows affected then try setting SET NOCOUNT OFF.

All the bset.

Read the article "Table Valued Parameters".

--Amit

Member 348763224-Jul-12 5:44

24-Jul-12 5:44

SET NOCOUNT OFF is working for insert delete update but for select statement it is giving -1 always .

Luc Pattyn24-Jul-12 6:07

Luc Pattyn

24-Jul-12 6:07

Member 3487632 wrote:
for select statement it is giving -1 always

What a shocker. It is what the doc[^] says: For UPDATE, INSERT, and DELETE statements, the return value is the number of rows affected by the command... For all other types of statements, the return value is -1

Hmmm | :|

Luc Pattyn [My Articles] Nil Volentibus Arduum

Member 348763224-Jul-12 6:21

24-Jul-12 6:21

YES..it is

_Amy24-Jul-12 17:36

_Amy

24-Jul-12 17:36

Member 3487632 wrote:
select statement it is giving -1 always

Why you are checking the SET NOCOUNT in the case of select? Instead of that you can check your result-set rows..

Check your result-set rows for the number of rows selected.. Smile | :)

Read the article "Table Valued Parameters".

--Amit

jschell24-Jul-12 7:16

jschell

24-Jul-12 7:16

Based on your other responses.

You have the following code
1. SQL
2. Calling code.

In a previous statement it appears that you cannot modify 2.

So that means you MUST modify 1 such that it returns the value that you want.

Thus for example you can structure a stored proc such that if there are no results, which you check for, then you return zero. (That means you write the code to do just that.)

SET NOCOUNT

Member 348763223-Jul-12 22:15

23-Jul-12 22:15

How to do SET NOCOUNT ON at database level or at server level

Thanks in advance

Wayne Gaylard23-Jul-12 23:06

Wayne Gaylard

23-Jul-12 23:06

There is no option to turn this on for a particular database on the server. You can turn this off for the whole server <var>Properties > Connections > No Count checkbox</var>.

When I was a coder, we worked on algorithms. Today, we memorize APIs for countless libraries — those libraries have the algorithms - Eric Allman

Member 348763223-Jul-12 23:09

Increasing the session existing time in sql server

23-Jul-12 23:09

Hi Even then rows effected i'm getting -1 in select statement .

What i'm observing is that when i put SET NOCOUNT ON before sql statement even then it is returning -1. I guess it shud return 0 .

I'm having sql server 2005 . Please help.
Thanks

yousefshokati20-Jul-12 22:29

yousefshokati

20-Jul-12 22:29

Hi .
I want to know how can i increase the session existence time in SQL server. I have a software that uses a connection to SQL server . when it remains idle for some minutes then the new session will be created to SQL server from the application side . This produces me me a big problem . Because i use the connection_Id for auditing the user . in my login table i have applicationID that refers to connection_Id in sys.dm_exec_connections .
when the software authenticates the user , It updates the connection_ID in login table and through this column i can get my user's username in my triggers .
here is the problem
when my software becomes idle , then another connection_id is created and i can not trace my user because i can not reach my user in logins table
this is my login table

CREATE TABLE [dbo].[Logins](
	[LoginUser] [nvarchar](50) NOT NULL ,
	[Password] [nvarchar](50) NOT NULL,
	[IsDefaultPassword] [int] NULL,
	[AppID] [uniqueidentifier] NULL,
 CONSTRAINT [PK_Logins] PRIMARY KEY CLUSTERED 
(
	[LoginUser] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

and this is my authenticate procedure

CREATE PROCEDURE [dbo].[Authenticate] (@username nvarchar(30) , @password nvarchar(30))
AS
   declare @Authenticated int 
    set @Authenticated = 0
 
   
   select  @Authenticated = 1  
   from Logins l
   where l.LoginUser =@username and l.Password =@password 
   
   if @Authenticated = 1 
     begin;
        declare @AppID uniqueidentifier 
        select @AppID=p.connection_id from sys.dm_exec_connections p 
        where p.session_id = @@SPID 
        update Logins 
        set AppID = @AppID 
        where Logins.LoginUser = @username and Logins.Password = @password
     end; 

   select @Authenticated ,@AppID

Re: Increasing the session existing time in sql server

Eddy Vluggen21-Jul-12 7:57

Re: Increasing the session existing time in sql server

21-Jul-12 7:57

yousefshokati wrote:
when my software becomes idle , then another connection_id is created

Easiest way out is to not create a new connection, but re-use the existing one. Otherwise, keep the Id around in a variable after your first connect, and pass it as a param on the idle-connection.

Bastard Programmer from Hell Suspicious | :suss:

if you can't read my code, try converting it here[^]

jschell21-Jul-12 11:46

jschell

21-Jul-12 11:46

yousefshokati wrote:
This produces me me a big problem

Increasing session timeout so you can track users is unlikely to ever be a good idea.

As one example exactly what do you think is going to happen to your system if a 'user' goes on vacation for two weeks but that user remains logged in?

Also it appears that you are attempting to manage permissions by user rather than role. And that is going to become a problem with more than a couple of users.

Problem saving file to MySQL

Jassim Rahma20-Jul-12 21:27

Jassim Rahma

20-Jul-12 21:27

I am using this code to save file to MySQL but when I try to open the file after saving it I get an error saying:

Word was unable to read this document. It may be corrupt.

here is the code to write:

file_name = Path.GetFileName(uploadResume.PostedFile.FileName);
file_extension = Path.GetExtension(uploadResume.PostedFile.FileName);

switch (file_extension)
{
case ".pdf": document_type = "application/pdf"; break;
case ".doc": document_type = "application/vnd.ms-word"; break;
case ".docx": document_type = "application/vnd.ms-word"; break;
case ".gif": document_type = "image/gif"; break;
case ".png": document_type = "image/png"; break;
case ".jpg": document_type = "image/jpg"; break;
case ".jpeg": document_type = "image/jpg"; break;
}

// calculate size of file;
int file_size = uploadResume.PostedFile.ContentLength;

// create array to read the file into it;
byte[] document_binary = new byte[file_size];
uploadResume.PostedFile.InputStream.Read(document_binary, 0, file_size);

and then passing it as parameters:

sql_command.Parameters.AddWithValue("param_resume_format", document_type).MySqlDbType = MySqlDbType.VarChar;
sql_command.Parameters.Add("param_resume_data", MySqlDbType.Blob, file_size).Value = document_binary;

and here is how I am retrieving it:

sql_connection = new MySqlConnection(ConfigurationManager.ConnectionStrings["SQLdb"].ConnectionString);
sql_connection.Open();
sql_command = new MySqlCommand("sp_get_resume_by_id", sql_connection);
sql_command.CommandType = CommandType.StoredProcedure;
sql_command.Parameters.Add("param_resume_id", MySqlDbType.Int32).Value = resume_id;
sql_reader = sql_command.ExecuteReader();
sql_reader.Read();

if (sql_reader.HasRows)
{
file_name = sql_reader["resume_id"].ToString() + sql_reader["resume_ext"].ToString();
byte[] document_binary = (byte[])sql_reader["resume_data"];

FileStream file_stream = new FileStream(@"C:\Temp\" + file_name, FileMode.Create);
file_stream.Write(document_binary, 0, document_binary.Length);
file_stream.Close();
file_stream.Dispose();

txtResume.Visible = true;
}

Re: Problem saving file to MySQL

Eddy Vluggen20-Jul-12 23:22