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.
| Author |
Topic |
|
dannywooly
Starting Member
1 Post |
 Posted - 2006-07-21 : 06:15:54
|
| Hi AllWe 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. RegardsDanny |
|
|
|
|
|
|
|
SQL Express Problem!