Return object from VB.net to VB6 (Object not defined)

Hi,

I was working on a project where there was a change needed in VB6 application.
This change was implemented in VB.net but there were few things involved in returning values from .net to VB6 which caused little challenge.
Without realizing, I thought that the class object I m creating in .net would be understood by VB6 but, I was wrong.

I was keep getting error when I was calling .net function in VB6 that was returning object in vb.net.
The error was "object not defined".

To fix this problem, what I did, I decomposed my single class into 2 classes; say ClassA and ClassB.
Then I created objects of each class in VB6 and then used properties to pass values from ClassA object to ClassB.

In the constructor of ClassA, I initialized the property which was ReadOnly property in my class.
Then I passed this property by assigning to ClassB object's property.

What I was doing wrong returning ClassB object from .net function to VB6 which wasn't working. So  I guess the correct way to create separate classes and objects and then pass values between them using properties.

HTH.

Access SQL Server in ODBC in different domain

Hi,

Recently, in my company, we added a new domain with sql servers. Ofcourse, a trust was setup between existing and new domain.
There was a need to access these sql servers from prev domain.
The way you access it would be as:

<sqlservername>.domainName\instanceName

Example: If sqlserver name is: DevServer 
and domain is: company.internal.com 
and instance name is: PostTesting

Then I will access it as: devserver.company.internal.com\posttesting

For default instance, it would be just: devserver.company.internal.com

HTH

Encrypt / Decrypt password in Sql Server Database

I know this solution is not a recommended solution by many DBAs but it is available to use. Sometimes you have to get things going and implementing each step with detail can take weeks to finish but as a quick solution, this method has helped me to store password in the database with encryption and decryption methods.

I will not suggest this implementation but it is a fast way to store passwords in database.

CREATE TABLE LoginDetails (
 UserName VARCHAR(50)
      , [Password] VARBINARY(100)
)

INSERT INTO LoginDetails VALUES ('John', ENCRYPTBYPASSPHRASE('secretkey', 'johnPassword'))
INSERT INTO LoginDetails VALUES ('Shaw', ENCRYPTBYPASSPHRASE('secretkey', 'shawPassword'))
INSERT INTO LoginDetails VALUES ('Mike', ENCRYPTBYPASSPHRASE('secretkey', 'mikePassword'))

When I select from table, I see binary data in Password column


Now to read password, you need to decrypt it with the same key along convert function to read binary data as readable text.

SELECT username, CONVERT(VARCHAR(100), DECRYPTBYPASSPHRASE('secretkey', [password])) AS Password FROM LoginDetails

Here is the result

How to find start date of month in SQL Server?

SELECT DATEADD(month, DATEDIFF(month, 0, @date), 0) AS StartOfMonth

0 in date functions means 1/1/1900.
The above function first find the difference of total months from 1/1/1900.
It then adds the difference in 0 (1/1/1900) again which will land to the beginning date of month.

Why I dont see Network drives inside Open Dialog box?

If you like me that u didn't see network drives then you have to right place.
You need to visit the drive by typing the drive letter in the FileName field.
Once you visit, next time you open dialog box, you will see the drive listed in the dialog box.

HTH

SET vs SELECT SQL Server

Many times we wonder how set and select are different and mostly we think that select is used to return data set back to user and set is used to set the value of variables.

This is exactly right but sometimes people also use to set variable value using select statement.

Lets try some statements and learn some behaviors as well:

-- create couple temp tables
create table #Test (BusinessDate datetime)
create table #TestNoRows (BusinessDate datetime)

-- insert null row in #test table

insert into #test values (null)

-- declare variable
declare @businessDate as datetime

select @businessDate = businessdate from #Test
select @businessDate

You will get Null.  A row was returned from table with Null. To make it more clear, we will now set some default value to variable so that variable has some value.

-- Now set some default value.
set @businessDate = '01/01/1970'

select @businessDate =  isnull(businessdate, '01/01/2000') from #Test
select @businessDate

You will get '01/01/2000'. Again a row was returned and isnull function was able to work on this value and replaced the default value.

Now we will test how select behave with empty table.

-- declare variable
declare @businessDateNoRow as datetime

select @businessDateNoRow = businessdate from #TestNoRows
select @businessDateNoRow

No row returned. You will get Null as default value of variable.

-- Now set some default value.
set @businessDateNoRow = '01/01/1970'
select @businessDateNoRow =  isnull(businessdate, '01/01/2000') from #TestNoRows
select @businessDateNoRow

You will get '01/01/1970' because no rows returned that would be checked for null or not null. So same default value stored in variable is returned.

Now we will test how set/select works.

--using set

set @businessDateNoRow = (select  isnull(businessdate, '01/01/2000') from #TestNoRows )

select @businessDateNoRow

You will get null.

--using select

select @businessDateNoRow = (select  isnull(businessdate, '01/01/2000') from #TestNoRows )
select @businessDateNoRow

You will get null too.

When no rows are returned and if set is used, you would write subquery to set value. Nothing to do with set or select.

Multiple valid OR conditions in select statement SQL Server

If you have a query that has OR condition,  you will not get multiple rows just because there are multiple conditions satisfied the result.
However many rows exist in table that many rows will be returned.

Also all the operators are evaluated before the query result is returned.
This can result in multiple rows to be returned.

Lets work with an example:

Create table #person (name varchar(50), age int)

-- insert one row

insert into #person values ('tom', 40)

-- select person whose name is either 'tom' or age is 40.

select * from #person 

where name = 'tom' or age = 40

One row will be returned.

-- insert second row 

insert into #person values ('monica', 41)

-- Now select with multiple conditions 

select * from #person 

where (name = 'tom' or age = 40 or age > 40)

2 rows will be returned which tells all the ORs are evaluated and based on filters, rows will be returned.

HTH