ADO in a Windows Service

[shawne]

Anyone have an idea why the following code does not work? It just hangs when trying to connect to the database via trusted connection.

Private Sub Timer1_Elapsed(ByVal sender As Object, ByVal e As System.Timers.ElapsedEventArgs) Handles Timer1.Elapsed
Dim Mydata As ADODB.Connection
Dim objRS As ADODB.Recordset

Mydata = New ADODB.Connection
Mydata.ConnectionString = "{SQL Server};Server=myserver;Database=mydb"
Mydata.Open()

objRS = Mydata.Execute("SELECT * FROM mytable")

The application is actually a windows service which is suppose to run at user controlled intervals and update a database. It's really a simple application, i'm just stuck on this.

 

[IfYouSaySo]

One thing you could try is to run the service as a particular user (not the default 'local system' account). This can be done on the service's property page on the service control panel.

 

[shawne]

Yup, tried that. I thought maybe it had something to do with authentication. That some how when the service ran as local system, mssql saw it as a non-trusted account and denied accesss. I also tried using SA credentials, but still got the same thing.

 

[TechGnome]

1) You really should be using ADO.NET... not ADO.
2) The connection string is incomplete. You gave it a server & database but no authentication credentials.

-tg

 

[shawne]

Ok..
1) It is the adodb .NET reference that i am using
2) I saw that after my initial post. I just didn't copy the entire connection string. Should've read Trusted_Connection=Yes.

I've since tried sa accounts on two different servers and other accounts, but I still gotta believe it's something with authentication, I just can't figure it out and it's driving me batty!

 

[TechGnome]

Sorry, but my BS alarm is sounding.... this is NOT ADO.NET code...
Dim Mydata As ADODB.Connection
Dim objRS As ADODB.Recordset

No matter how hard you try that IS not and WILL not be ADO.NET code...

It's still the older classic ADO objects.

Since you are using SQL Server, you'll want the SQLClient namespace.
System.Data.SQLClient

I've got a couple of ADO.NET tutorials in my sig that you can check out if you want.

-tg

 

[shawne]

Your BS alarm? Sorry if you think i'm just pulling stuff out of my a$$, but i'm still learning. I added the adodb reference under the .NET Add Reference tab. My previous code wouldn't even begin to work prior to me doing that. So the code itself may not have been .NET, but the component I tried to use definitly came out of the .NET references tab. Please excuse me for not understanding the difference.

At any rate, I talked to one of the guys that do some of our custom apps here at work and he said the same thing as far as using sql instead of ado. So here is my updated code and it still hangs on connecting to the database. We created a UDL, tested the connection, it worked, copied the connection string out of the UDL file and pasted it into my code and it still hangs on connecting.

Here is the updated code as it pertains to the db connection:

Imports System.Data.SqlClient
Public Class myservice
Inherits System.ServiceProcess.ServiceBase
Public Mydata As SqlConnection
Public Mycommand As SqlCommand
Public dr As SqlDataReader

Public Sub Timer1_Elapsed(ByVal sender As Object, ByVal e As System.Timers.ElapsedEventArgs) Handles Timer1.Elapsed


Mydata.ConnectionString = "Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=mycatalog;Data Source=myserver"

NotifyIcon1.Visible = True
Mydata.Open()

dr = Mycommand.ExecuteReader("SELECT * FROM mydata")

NotifyIcon1.Visible = False
End Sub

I'm not that great at debugging services so I created a notification icon, made it not visible by default and set it to go visible directly before and after the mydata.open statement. It shows up before, but when I move it to after I get nothing. And yes, I set the "Allow service to interact with desktop" on all occasions. Thats how I narrowed down where the problem was. The above code is obviously set for before just to clarify.

One other note. It does seem to eventually time out because if at the end of the sub i tell the notify icon not to be visible again, after a period of time the icon will go away. So the connection attempt does seem to time out.

 

[shawne]

Ok, I went through one of the tutorials and created the following code based on it. It hangs when filling the dataset. Which if I understand things properly, opens the database connection, executes my query and closes the connection. So it's my guess it's still hanging on opening the connection.......

Here is the clip:

Try
NotifyIcon1.Text = "1"
Dim Mydata As New SqlClient.SqlConnection(MyCONNECTIONSTRING)
NotifyIcon1.Text = "2"
Dim MyRecords As New SqlClient.SqlCommand("SELECT * FROM mydata")
NotifyIcon1.Text = "3"
Dim records As New SqlClient.SqlDataAdapter(MyRecords)
NotifyIcon1.Text = "4"
records.SelectCommand.Connection = Mydata
NotifyIcon1.Text = "5"
Dim ds As New System.Data.DataSet
NotifyIcon1.Text = "6"
records.Fill(ds, "mydata")
NotifyIcon1.Text = "COMPLETE!"
Catch err As Exception
MsgBox(err)

This last update to the notifyicon is 6 ...

 

[shawne]

Got it .... i'm not sure if it was the problem the entire time, but I did have an authentication problem which took an adjustment to the sql server. Now I just need to finish the rest of the code.

Thanks for getting me on track TG! /bow

 

[TechGnome]

Glad you got it going.... the authentication would have been my next suggestion...

-tg
BTW: I didn't think you were pulling it out of your arse, but thought you might have been misguided.... which, after reading your additional posts, sounds like you are converting an existing service to .NET......

A further explanation on the ADO vs ADO.NET references. When a reference is made to ANY COM component (such as ADO in this case), a .NET wrapper component is created to act as an interface. This wrapper is then referenced via .NET references.... this is why it looks like a .NET component reference when in fact it's just a patsy for the COM component.