Web Service with vb.net and access db

Webservice are nothing really that 'new.'  Think of it as calling a DLL in VB6, because that is really what you are doing.  And pretty much code it the same way.  You just use "<WebMethod>" in front of your "Public Function ..." statement.  

Just as you would make the connection to your database from a standard VB6 DLL, write it the same way for your webservice.  Your webservice calls are just calls to the DLL Methods with different syntax.  The concept is pretty much the same.

If you were looking for specifics instead of help on the concept,

      connString = "Provider=Microsoft.JET.OLEDB.4.0;data source=C:\Northwind.mdb"
      Dim sqlString As String
      sqlString = "SELECT customerId, companyName, contactName FROM customers"
      Dim dataAdapter As OleDbDataAdapter
      dataAdapter = Nothing
      Dim _dataSet As DataSet
      _dataSet = Nothing
      ' Create a table style that will hold the new column style
      ' that we set and also tie it to our customer's table from our DB
      Try
        ' Connection object
        Dim connection As OleDbConnection
        connection = New OleDbConnection(connString)
        ' Create data adapter object
        dataAdapter = New OleDbDataAdapter(sqlString, connection)
        ' Create a dataset object and fill with data using data adapter's Fill method
        _dataSet = New DataSet()
        dataAdapter.Fill(_dataSet, "customers")
        connection.Close()
      Catch ex As Exception
        MessageBox.Show(ex.ToString))
        Me.Close()
        Return
      End Try


Hope this helps,
Landy

Landy that looks more or less like what I'm going for, but my db isn't in the c drive as I said for now it's being hosted on my local machine so it's in the wwwroot directory  under inetpub.  When running the webservice the "web site" is http://localhost/TestDBservice/ and the db would be in that directory.  So how do I use that as the data source?

You just need the physical location of the MDB file.

for testing: "C:\whatever directory\whatever subdirectory\Northwind.mdb"
On the server: "C:\inetpub\wwwroot\TestDBservice\Northwind.mdb" or "\\servername\rootdirectory\inetpub\wwwroot\TestDBservice\Northwind.mdb"

Those should do it.  Remember, the webservice is going ot be accessing the database from its location, not the client's, so using a "data source=c:\...." should work for you when the app is deployed.

Hope this helps,
Landy

Landy, when I try to run the code I get this error:

System.Data.OleDb.OleDbException: Not a valid file name.
   at System.Data.OleDb.OleDbConnection.ProcessResults(Int32 hr)
   at System.Data.OleDb.OleDbConnection.InitializeProvider()
   at System.Data.OleDb.OleDbConnection.Open()
   at System.Data.Common.DbDataAdapter.QuietOpen(IDbConnection connection, ConnectionState& originalState)
   at System.Data.Common.DbDataAdapter.FillFromCommand(Object data, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior)
   at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior)
   at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, String srcTable)
   at TestDBService.Service1.GetDataSet() in C:\Inetpub\wwwroot\TestDBService\Service1.asmx.vb:line 63

It's the line that corresponds to "dataAdapter.Fill(_dataSet, "customers")" just for me it's dataAdapter.Fill(_dataSet, "Vehicles") where Vehicles is the table name.  Any ideas?

Also, just as an aside, double check permissions on the TestDBservice directory so that no one is able to download your database file.

(Sorry, posted that last reply before I refreshed the question)

Is your connection string "connString = "Provider=Microsoft.JET.OLEDB.4.0;data source=C:\intepub\wwwroot\DBName.mdb" ?

Not sure why that would pop up on the .Fill function.  What is the entire block of code? Let's take a look at that...

Here is the web methid
<WebMethod()> _
    Public Function GetDataSet() As String
        Dim ds As DataSet = Nothing
        Dim connString As String = "Provider=Microsoft.JET.OLEDB.4.0;data source=\C:\Inetpub\wwwroot\TestDBService\Changed.mdb"
        Dim sqlString As String = "SELECT * FROM Vehicles"
        Dim dataAdapter As OleDb.OleDbDataAdapter = Nothing

        Dim connection As OleDb.OleDbConnection
        connection = New OleDb.OleDbConnection(connString)
        dataAdapter = New OleDb.OleDbDataAdapter(sqlString, connection)
        ds = New Data.DataSet
        dataAdapter.Fill(ds, "Vehicles")
        connection.Close()
        Dim myxml As New System.IO.StringWriter
        ds.WriteXml(myxml, System.Data.XmlWriteMode.DiffGram)

        Return myxml.ToString

    End Function

Check your DB path in the data source parameter.  Get rid of the first backslash before the "C:"  

Would changing

"...data source=\C:\Inetpub\wwwroot\TestDBService\Changed.mdb"

to

"...data source=C:\Inetpub\wwwroot\TestDBService\Changed.mdb"

make any difference?

Darn...insta-posted. :)

Thanks both Landy ans UncleMidriff that worked.  I have one final question regarding the same line of code.  Landy you metioned earlier that I could use "\\servername\rootdirectory\inetpub\wwwroot\TestDBservice\Northwind.mdb" as well for the data source, that however causes the same problem as before.  Since you said that the server will be making the access so the "c:\..." line will work under deployment as well I guess it doesn't matter, but I was just wondering if you had any ideas about that.  Whast should I be replacing rootdirectory with I used c:?

It most likely is \\severname\c\inetpub\wwwroot\...

It would be whatever you use to map to the C: drive on that server if you were going through Windows Explorer.  Check with your network admin as to what that directory is shared as.

Hope this helps,
Landy

If your network admin is unavailable, in my experience most use either c or c$, so you can try \\servername\c\... or \\servername\c$\...

I got a different error refrencing that the jet driver could open it  because it's already open or need permission.  It's not already open, so any clue what permissions I need to set?

OK, have you configured IIS on your local machine to allow access to the TestDBservice directory?  Are you using XP?  Are you familiar with configuring IIS?

If your answers are No, Yes and No (in that order), I'll try to walk you through it, but I will warn you, you'd be better off getting your network admin to help you out.  I'm sure I will mess something up...

Start/All Programs/Administrative Tools/Internet Information Services
You should see your "<computer name> (local computer)", expand this tree until you find the TestDBService directory
Right Click and go to Properties
Click Read & Write (you might not need Write permissions so you can access the DB file.  These may need to be set through File permissions)
Under Application Settings, Click Create
Click OK

This should enable you to acces the directory and the database file.  PLEASE check with your network admin before deploying.

Hope I didn't mess you up too much, *S*
Landy

Followed you until application settings part where is tha?

Is there a button that says "remove" ?  If so, web access has already been set for this directory.  Setting Write access should set you up.

That didn't work, but that's okay.  I'll keep working on it as you said the other way should be fine.  Thanks for all your help,  as a note I'll probably need some help with consuming the dataset the service returns so keep an eye out for my next question.  Again thank you for being easy on a newbie.
~pcjunky

We all were newbies at one point. I'm probably not that far in front of you. *G*  Good luck.