Changed database context to 'dbname'

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

Changed database context to 'dbname'

Author

Topic

mclaugh2004
Starting Member

3 Posts

Posted - 2011-06-24 : 15:30:38

Using SQL Server 2008.
Using classic ASP. (upgrading the db on a company's site for them)

My conn string:
ConnectionString = "Driver={SQL Server Native Client 10.0};" & _
"Server=localhost;" & _
"Database=dbname;" & _
"Uid=dbuser;" & _
"Pwd=dbpass;"

So I am doing a simple update and inserts on the site and I am getting the following error:

[Microsoft][SQL Server Native Client 10.0][SQL Server]Changed database context to 'dbname'. (Microsoft OLE DB Provider for ODBC Drivers)

Any help would be appreciated. Thanks.

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2011-06-24 : 15:42:01

1. Use the command object instead of connection object.
2. If this is from Stored Procedures, put SET NOCOUNT ON at the top of the procs.

mclaugh2004
Starting Member

3 Posts

Posted - 2011-06-24 : 15:56:36

The site is using Server.CreateObject("ADODB.Connection") now to connect.

I would have to change quite a bit of code to change it to use the command object.

Is there not another way to hide that message?

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2011-06-24 : 17:42:03

Not sure. Can you show the offending code?

mclaugh2004
Starting Member

3 Posts

Posted - 2011-06-24 : 17:50:42

Here is the conn string.


    Private Sub Class_Initialize()
        ConnectionString = "Driver={SQL Server Native Client 10.0};" & _
						   "Server=localhost;" & _
						   "Database=infotrak;" & _
						   "Uid=usertest;" & _
						   "Pwd=12345;"

        Set Converter = New clsConverter
        Converter.DateFormat = Array("yyyy", "-", "mm", "-", "dd")
        Converter.BooleanFormat = Array("true", "false", Empty)
        Set objConnection = Server.CreateObject("ADODB.Connection")
        Set Errors = New clsErrors
    End Sub

And here is the actual Update code:


    Sub Update(Cmd)
        CCSEventResult = CCRaiseEvent(CCSEvents, "BeforeBuildUpdate", Me)
        Set Cmd.Connection = Connection
        Cmd.CommandOperation = cmdExec
        Cmd.CommandType = dsTable
        Cmd.CommandParameters = Empty
        Cmd.Prepared = True
        BuildTableWhere
        If NOT AllParamsSet Then
            Errors.AddError(CCSRunTimeMessages.GetMessage("CustomOperationError_MissingParameters", Empty))
            Exit Sub
        End If
        Cmd.SQL = "UPDATE Transactions SET " & _
            "SocialSecurityNumber=" & Connection.ToSQL(IIf(TypeName(SocialSecurityNumber) = "clsSQLParameter", SocialSecurityNumber, SocialSecurityNumber.SQLText), SocialSecurityNumber.DataType) & ", " & _
            "FirstName=" & Connection.ToSQL(IIf(TypeName(FirstName) = "clsSQLParameter", FirstName, FirstName.SQLText), FirstName.DataType) & ", " & _
            "LastName=" & Connection.ToSQL(IIf(TypeName(LastName) = "clsSQLParameter", LastName, LastName.SQLText), LastName.DataType) & ", " & _
            "TransactionAmount=" & Connection.ToSQL(IIf(TypeName(TransactionAmount) = "clsSQLParameter", TransactionAmount, TransactionAmount.SQLText), TransactionAmount.DataType) & ", " & _
            "DueDate=" & Connection.ToSQL(IIf(TypeName(DueDate) = "clsSQLParameter", DueDate, DueDate.SQLText), DueDate.DataType) & ", " & _
            "StoreName=" & Connection.ToSQL(IIf(TypeName(StoreName) = "clsSQLParameter", StoreName, StoreName.SQLText), StoreName.DataType) & ", " & _
            "status_id=" & Connection.ToSQL(IIf(TypeName(status_id) = "clsSQLParameter", status_id, status_id.SQLText), status_id.DataType) & ", " & _
            "Notes=?" & ", " & _
            "user_id_add=" & Connection.ToSQL(IIf(TypeName(user_id_add) = "clsSQLParameter", user_id_add, user_id_add.SQLText), user_id_add.DataType) & ", " & _
            "TransactionDate=" & Connection.ToSQL(IIf(TypeName(date_created) = "clsSQLParameter", date_created, date_created.SQLText), date_created.DataType) & _
            IIf(Len(Where) > 0, " WHERE " & Where, "")
        Cmd.CommandParameters = Array( _
            Array("Notes", adLongVarChar, adParamInput, 2147483647, Notes.Value))
        CCSEventResult = CCRaiseEvent(CCSEvents, "BeforeExecuteUpdate", Me)
        Cmd.Exec(Errors)
        CCSEventResult = CCRaiseEvent(CCSEvents, "AfterExecuteUpdate", Me)
    End Sub

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2011-06-24 : 19:45:23

Right before this line: Cmd.Exec(Errors)

If you add: Response.Write Cmd.SQL

we may be able to help troubleshoot.

Problem now is that Connection is one of your custom classes that we don't see the code for (for example, we have no idea what Connection.ToSQL does). I'm guessing it's likely that Cmd is also one of your custom classes. No idea what dsTable represents.

Interesting that you have done a great job by building classes, then build your SQL string dynamically and with global variables.

Let me ask you this: does the update actually work?
And why aren't you trapping errors when trying to connect and/or write to the database?

If the update works, you just need a little error handling


On Error Resume Next
Cmd.Exec(Errors)
If Instr(Err.Description, "Changed database") > 0 Then
	'ignore it
Else
	'handle the error
End If
On Error Goto 0

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources