NCSO
asked on
Connecting to SQL from a vb.net service...
Hi,
I am banging my head into the keyboard... please help!
I have a program which does some local and remote calls to SQL databases... It works great when it is in a regular program.
I am trying to port it over to run in a service, but I get the error I have listed below... and below the error is the code I am running in the service...
The database I am trying to connect to is a local install of SQL-EXPRESS.
If it is a rights issue how do I get around that? (i figured it might not be because of the file not found error)
tnx
~j
-------- ERROR FROM EVENTLOG -------------
Service cannot be started. System.IO.FileNotFoundExce
File name: 'Interop.ADODB, Version=2.8.0.0, Culture=neutral, PublicKeyToken=null'
at CrimeManager.Service1.SQLS
at CrimeManager.Service1.OnSt
at System.ServiceProcess.Serv
WRN: Assembly binding logging is turned OFF.
To enable assembly bind failure logging, set the registry value [HKLM\Software\Microsoft\F
Note: There is some performance penalty associated with assembly bind failure logging.
To turn this feature off, remove the registry value [HKLM\Software\Microsoft\F
------------ ERROR END ----------------
----------- CODE RUNNING IN SERVICE -----------------
Dim cn As New ADODB.Connection()
Dim rs As New ADODB.Recordset()
Dim cnStr As String
cnStr = "Provider=SQLOLEDB;Initial
cn.Open(cnStr) 'Open SQL connection
rs.Open("select * from tblAuthor", cnStr)
While Not rs.EOF
My.Computer.FileSystem.Wri
End While
cn.Close()
rs.Close()
------------ END CODE -------------------
I am banging my head into the keyboard... please help!
I have a program which does some local and remote calls to SQL databases... It works great when it is in a regular program.
I am trying to port it over to run in a service, but I get the error I have listed below... and below the error is the code I am running in the service...
The database I am trying to connect to is a local install of SQL-EXPRESS.
If it is a rights issue how do I get around that? (i figured it might not be because of the file not found error)
tnx
~j
-------- ERROR FROM EVENTLOG -------------
Service cannot be started. System.IO.FileNotFoundExce
File name: 'Interop.ADODB, Version=2.8.0.0, Culture=neutral, PublicKeyToken=null'
at CrimeManager.Service1.SQLS
at CrimeManager.Service1.OnSt
at System.ServiceProcess.Serv
WRN: Assembly binding logging is turned OFF.
To enable assembly bind failure logging, set the registry value [HKLM\Software\Microsoft\F
Note: There is some performance penalty associated with assembly bind failure logging.
To turn this feature off, remove the registry value [HKLM\Software\Microsoft\F
------------ ERROR END ----------------
----------- CODE RUNNING IN SERVICE -----------------
Dim cn As New ADODB.Connection()
Dim rs As New ADODB.Recordset()
Dim cnStr As String
cnStr = "Provider=SQLOLEDB;Initial
cn.Open(cnStr) 'Open SQL connection
rs.Open("select * from tblAuthor", cnStr)
While Not rs.EOF
My.Computer.FileSystem.Wri
End While
cn.Close()
rs.Close()
------------ END CODE -------------------
From the error it looks like the type of connection is needing a driver that isn't there maybe in a regular program there is a different driver being called for SQLOLEDB than for the service have you tried the SQLOLEDB.1
There are more tips here:
http://www.connectionstrings.com/
There are more tips here:
http://www.connectionstrings.com/
ASKER
tried the .1 but that didn't do it, I'll look through the url in the morning... time to go home :)
tnx
~j
tnx
~j
ASKER
Hi,
I doesn't even get to the connection string. It complains in the first line (Dim cn As New ADODB.Connection())...
I am thinking it's a permisson issue (it is working in a non-service) but ... how do I specify what user context to use?
The properties for the ServiceProcessInstaller gives me a way of specifying the account to use (LocalService, Network Service, LocalSystem and User), I've tried all four with varions non-working result.
tnx
~j
I doesn't even get to the connection string. It complains in the first line (Dim cn As New ADODB.Connection())...
I am thinking it's a permisson issue (it is working in a non-service) but ... how do I specify what user context to use?
The properties for the ServiceProcessInstaller gives me a way of specifying the account to use (LocalService, Network Service, LocalSystem and User), I've tried all four with varions non-working result.
tnx
~j
Try this to import first the driver I changed loopback to local - no reason
Imports System.Data.SqlClient
Dim oSQLConn As SqlConnection = New SqlConnection()
oSQLConn.ConnectionString=
oSQLConn.Open()
Imports System.Data.SqlClient
Dim oSQLConn As SqlConnection = New SqlConnection()
oSQLConn.ConnectionString=
oSQLConn.Open()
ASKER
That does work but if I the open a recordset connection with adodb it crashes...
I guess I'll have to rewrite all my code to use the datareader since that seems to work better...
tnx
~j
I guess I'll have to rewrite all my code to use the datareader since that seems to work better...
tnx
~j
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Well, it is not the connection string I am worried about, it's the code...
Here's the problem:
We have a bunch of pc's in cars that write data to a local db where they are stored until their 3G cards connect to the central database back home and then a process (the service I'm trying to write) will write it back to the central database. So once the connection is established, I read the local rows in the db and write them up to the central db.
The old way I did it, I can do this: strSQL = "INSERT INTO tblAuthor (AuthorID, FullName, FirstName, LastName, Other) VALUES (rs.fields.item("AuthorID"
With the datareader i'd have to do this:
strSQL = "INSERT INTO tblAuthor (AuthorID, FullName, FirstName, LastName, Other) VALUES (dr(1).ToString(), dr(2).ToString(), dr(3).ToString(), dr(4).ToString(), dr(5).ToString(), connection)
And although this seem like heaven for some people it makes for ugly unreadable code. The line above is a simplified example, some of our tables are huge (one has 231 fields) which would make this approach near impossible to read and maintain. (imagine having to remove a certain field?)
I'll obviously write a piece of code to create the insert statement, but I am not going to want to look at that string again. Is there a way to write the field-names instead if their index?
Anyway, you got me on the right path to get it working, so enjoy the points and have a great day... I appreciate your help!
tnx
~j
Here's the problem:
We have a bunch of pc's in cars that write data to a local db where they are stored until their 3G cards connect to the central database back home and then a process (the service I'm trying to write) will write it back to the central database. So once the connection is established, I read the local rows in the db and write them up to the central db.
The old way I did it, I can do this: strSQL = "INSERT INTO tblAuthor (AuthorID, FullName, FirstName, LastName, Other) VALUES (rs.fields.item("AuthorID"
With the datareader i'd have to do this:
strSQL = "INSERT INTO tblAuthor (AuthorID, FullName, FirstName, LastName, Other) VALUES (dr(1).ToString(), dr(2).ToString(), dr(3).ToString(), dr(4).ToString(), dr(5).ToString(), connection)
And although this seem like heaven for some people it makes for ugly unreadable code. The line above is a simplified example, some of our tables are huge (one has 231 fields) which would make this approach near impossible to read and maintain. (imagine having to remove a certain field?)
I'll obviously write a piece of code to create the insert statement, but I am not going to want to look at that string again. Is there a way to write the field-names instead if their index?
Anyway, you got me on the right path to get it working, so enjoy the points and have a great day... I appreciate your help!
tnx
~j
ASKER
I tried launching the service with both my user-account AND the local admin account to no avail...
~j