connection's difficulty 1

[231166]

Hi,
I have to use a lot of times the connection i have created in my project, but i can see that a lot of times, when i debug the project, the connection does not open even if i have coded it to be opened.

I have declared the connection string one at the beginning of the project , like this
Private objConn as sqlConnection

strConnString = "Initial Catalog = THESAURUS; integrated security = SSPI"
objConn = New sqlConnection(strConnString)


and after i do like this to open or close it

objConn.Open or objConn.close

Thanks a lot for your help.

Best regards.
Nathalie

 

[TipGiver]

. Private objConn as sqlConnection = Nothing

.

http://www.connectionstrings.com

to see if you wrote correctly the connection string

. Before trying to open or close the connection, check:
1. if the objConn is nothing
2. the state property of the connection object

 

[231166]

Hi and thanks for your help.

The fact that even if the connection's state is 'closed' and i want to open it, coding objConn.open, it does not open.

Perhaps there is a problem with the memory.
How can i do to minimize this problem if it is the reason why it does not work well.
Rzegards.

Nathalie

 

[TipGiver]

You say that the connection do not open. How do you check this? Is there any exception?

 

[231166]

Hi,

When i debug i can see this, in the spy window if i write objConn.State i can see it is soemtimes closed whereas i have coded objConn.Open .

I did't put any exception.
Do you have some code to propose with this?

Thanks for your help.
Nathalie

 

[TipGiver]

Please check the

http://www.connectionstrings.com

You have missing information in your connection string

 

[RonRepp]

TipGiver is correct, check

http://www.connectionstrings.com.

TG, a star for giving the right answer.

Now, 231166, try this:

''I almost always open my connections one at a time, using a private Dim statement within the procedure.  Sometimes it is due to habit, and others a necessity, like if I'm slinging variables about into other connections to come up with a certain answer.

''Before your sub, you can add a public connection string like this:  (Connection to SQL Server)

Public sCon As String = "Provider=SQLOLEDB.1;Password=sa;Persist Security Info=True;User ID=sa;Initial Catalog=Nothwind;Data Source=Server"

Public Sub GetCustomer()
        Dim Con As ADODB.Connection = New ADODB.Connection
        Dim RS As ADODB.Recordset = New ADODB.Recordset
        Dim SQL As String
        Dim i As Int16
        
        Con.ConnectionString = sCon
        [b]Con.Open(sCon)[/b]

        SQL = "SELECT dbo.Orders.* FROM dbo.Orders WHERE (OrderID = 10248)"

        RS.Open(SQL, Con, ADODB.CursorTypeEnum.adOpenDynamic, ADODB.LockTypeEnum.adLockBatchOptimistic, ADODB.CommandTypeEnum.adCmdText)
        Do While Not RS.EOF
            
            ''Do Something to find one of the values of the recordset

            RS.MoveNext()
        Loop
        
        'Make sure you close the recordset
        RS.Close()

        'Make sure you close the connection
        Con.Close()

        'Reset Variables to nothing
        RS = Nothing
        Con = Nothing

        'Collect the variables
        GC.Collect
    End Sub

I hope this helps you understand it a little better. The connection string syntax is extremely important, which is why TG gave you the web site.



Ron Repp

If gray hair is a sign of wisdom, then I'm a genius.

 

[pmegan]

Hi Nathalie,

Put the open command in an error trap. This is a function I use for testing. All it does is set create the connection, open it and close it. If there are any problems it displays a message. When I release the program I add a parameter for the connection string and pass it in. I also change the error reporting from pop-ups to event viewer entries.

Hope this helps,
Pat

private strConn as String = [red]Put your connection string here[/red]
private g_dbConn as SqlClient.SqlConnection

Function dbConnect() As Boolean

	[green]' make a success flag & set the default as false[/green]
	dim blFlag as Boolean = false
	
	Try
		[green]' create the database connection, open it and close it[/green]

		g_dbConn = New SqlClient.SqlConnection(strConn)
		g_dbConn.Open() 
		g_dbConn.Close()

		blFlag = true

	Catch ex As SqlClient.SqlException
		MessageBox.Show("Error connecting to PH database")
		MessageBox.Show(ex.Message)
		MessageBox.Show(ex.InnerException.Message)
		Return False
	Finally
		[green]' make sure the connection is closed[/green]
		If g_dbConn.State = ConnectionState.Open Then
			g_dbConn.Close()
		End If
	End Try
End Function

 

[pmegan]

I forgot this in my last response. Here's a quick way to generate a connection string if you're connecting to SQL Server.

1) Right click your desktop and create a new text file. Call it test.udl
2) Double click it and a database connection wizard will open. Create a connection to the database you want to use and test it. Close the wizard.
3) Right click test.udl and open it with notepad. You'll get something like this:

   [oledb]
   ; Everything after this line is an OLE DB initstring
   Provider=SQLOLEDB.1;Persist Security Info=False;User ID=sa;Initial Catalog=Northwind;Data Source=BRG1-NYC

4) Copy everything after "Provider=SQLOLEDB.1;". That's your connection string. Paste it into your code and test it out.

 

[TipGiver]

Thanks Ron