Express Problems

Hi All

We recently moved from FW 1.1 to FW 2.0, and 2003 to 2005, also now using SQL Express.

The problem im having is that in code ie from this function:

visual basic code:

---

Protected Function ExecuteNonQuery(ByVal sSql As String, ByVal eDBType As cDBSettings.DatabaseType, Optional ByVal sDatabaseName As String = "", Optional ByVal bUseTransaction As Boolean = True) As Boolean

'Executes a sql statement and returns the result

Dim oCommand As SqlCommand = Nothing

Dim oConnection As SqlConnection = Nothing

Dim oTransaction As SqlTransaction = Nothing

Dim bSuccess As Boolean

Try

oConnection = GetDBConnection(eDBType, sDatabaseName)

oConnection.Open()

If bUseTransaction Then

oTransaction = oConnection.BeginTransaction()

End If

oCommand = New SqlCommand(sSql, oConnection)

With oCommand

If bUseTransaction Then

.Transaction = oTransaction

End If

.ExecuteNonQuery()

If bUseTransaction Then

oTransaction.Commit()

End If

End With

bSuccess = True

Catch e As Exception

If Not oTransaction Is Nothing Then

oTransaction.Rollback()

End If

bSuccess = False

Finally

If Not oTransaction Is Nothing Then oTransaction.Dispose()

If Not oCommand Is Nothing Then oCommand.Dispose()

If Not oConnection Is Nothing Then oConnection.Close() : oConnection.Dispose()

oCommand = Nothing

oConnection = Nothing

oTransaction = Nothing

End Try

Return bSuccess

End Function

---

Running this SQL statement:

visual basic code:

---

if (SELECT DATABASEPROPERTYEX('GateKeeper_1014', 'UserAccess')) = 'MULTI_USER' exec sp_dboption N'GateKeeper_1014', N'dbo use only', N'true'

---

The problem throws an Error that i must declare a scalar variable, but i pasted that into the SQL Manager and it runs.

So i put sql.tolower and i get the problem that it cannot find gatekeeper_1014, but again if i paste this into the SQL Manager it runs fine.

Also the fact that other developers in my office having the same setup some require uppercase SQL and some require them to be lower on non reserved words, as soon as i have reserved words lower case it throughs a general SQL error.

I have found that the colliation might be the issue but i have tried the compatibility mode on the Express install but still no avail!

If anyone can shed some light on this or put me in the direction of some information, i would be grateful.

Regards

Danny

Hi Danny,

There are differences between running a T-SQL command in a query window and trunning it using a SqlCommand object. (I'm guessing you're using a SqlCommand.) I find that it's easier to run single statements in a SqlCommand at a time. Here is a sample that does what you want:

Sub Main()

Dim cnn As SqlConnection

Dim cmd As SqlCommand

cnn = New SqlConnection("Data Source=.\sqlexpress;Database=AdventureWorks;Integrated Security=True")

cnn.Open()

cmd = New SqlCommand("SELECT DATABASEPROPERTYEX('AdventureWorks', 'UserAccess')")

cmd.Connection = cnn

Dim s As String

s = cmd.ExecuteScalar()

If s = "MULTI_USER" Then

cmd.CommandText = "sp_dboption N'AdventureWorks', N'dbo use only', N'true'"

cmd.ExecuteNonQuery()

End If

cnn.Close()

End Sub

Regards,

Mike Wachal
SQL Express team

-
Mark the best posts as Answers!