Solved

Web Service with vb.net and access db

Posted on 2004-10-12
20
366 Views
Last Modified: 2010-04-23
First I'm not positive that this is the correct place to post this question.

What I'm attempting to do is setup a web service to view and update a database.  The final product I want is a regular vb.net windows form as the client that consumes the web service.  The user will be able to view a record in a (for now Microsoft Access) database make changes to it and update the original.  I'm very new to the whole web service concept and programming so take it easy on me.  So really what I need to know is 1) how do is set up the web service to make a connection to the database (ex.  Books.mdb or something like that) and retrieve a record or the entire table.  2) make a connection to the database and update a record or the table.  The main issue I need resolve first is how to make the connection to the database.  For now the database is located on my developing machine so it'lkl be "localhost" something.  Thanks in advance for any and all assistance.  If there is any other information you want or need I'll answer to the best of my ability.  ~pcjunky
0
Comment
Question by:pcjunky
  • 11
  • 7
  • 2
20 Comments
 
LVL 7

Expert Comment

by:LandyJ
ID: 12289010
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
0
 
LVL 7

Accepted Solution

by:
LandyJ earned 500 total points
ID: 12289082
Oops,  Here's a better code snippet:

<WebMethod> Public Function wsGetCompnayContact (ByVal xCustID as Integer) as String
   connString = "Provider=Microsoft.JET.OLEDB.4.0;data source=C:\Northwind.mdb"
   Dim sqlString As String
   sqlString = "SELECT customerId, companyName, contactName FROM customers WHERE cusustomerId = " & xCustID
   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
   ' 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()
   Dim MyXML As New System.IO.StringWriter()
   _dataset.WriteXml(MyXML, System.Data.XmlWriteMode.DiffGram)
   Return MyXML.ToString
End Function


Hope this helps,
Landy
0
 

Author Comment

by:pcjunky
ID: 12289115
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?
0
Live: Real-Time Solutions, Start Here

Receive instant 1:1 support from technology experts, using our real-time conversation and whiteboard interface. Your first 5 minutes are always free.

 
LVL 7

Expert Comment

by:LandyJ
ID: 12289182
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
0
 

Author Comment

by:pcjunky
ID: 12289343
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?
0
 
LVL 7

Expert Comment

by:LandyJ
ID: 12289353
Also, just as an aside, double check permissions on the TestDBservice directory so that no one is able to download your database file.
0
 
LVL 7

Expert Comment

by:LandyJ
ID: 12289487
(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...
0
 

Author Comment

by:pcjunky
ID: 12289591
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
0
 
LVL 7

Expert Comment

by:LandyJ
ID: 12289688
Check your DB path in the data source parameter.  Get rid of the first backslash before the "C:"  
0
 
LVL 2

Expert Comment

by:UncleMidriff
ID: 12289782
Would changing

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

to

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

make any difference?
0
 
LVL 2

Expert Comment

by:UncleMidriff
ID: 12289787
Darn...insta-posted. :)
0
 

Author Comment

by:pcjunky
ID: 12290023
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:?
0
 
LVL 7

Expert Comment

by:LandyJ
ID: 12290092
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
0
 
LVL 7

Expert Comment

by:LandyJ
ID: 12290131
If your network admin is unavailable, in my experience most use either c or c$, so you can try \\servername\c\... or \\servername\c$\...
0
 

Author Comment

by:pcjunky
ID: 12290429
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?
0
 
LVL 7

Expert Comment

by:LandyJ
ID: 12290662
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
0
 

Author Comment

by:pcjunky
ID: 12290787
Followed you until application settings part where is tha?
0
 
LVL 7

Expert Comment

by:LandyJ
ID: 12290925
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.
0
 

Author Comment

by:pcjunky
ID: 12291008
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
0
 
LVL 7

Expert Comment

by:LandyJ
ID: 12291097
We all were newbies at one point. I'm probably not that far in front of you. *G*  Good luck.
0

Featured Post

Live: Real-Time Solutions, Start Here

Receive instant 1:1 support from technology experts, using our real-time conversation and whiteboard interface. Your first 5 minutes are always free.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Programmatically signing Word macros 4 72
scanning dentists xray (the small ones) 3 53
Help with error when uploading excel file 3 26
.NET 2008 VB and C# 6 27
Article by: Kraeven
Introduction Remote Share is a simple remote sharing tool, enabling you to see, add and remove remote or local shares. The application is written in VB.NET targeting the .NET framework 2.0. The source code and the compiled programs have been in…
The ECB site provides FX rates for major currencies since its inception in 1999 in the form of an XML feed. The files have the following format (reducted for brevity) (CODE) There are three files available HERE (http://www.ecb.europa.eu/stats/exch…
Two types of users will appreciate AOMEI Backupper Pro: 1 - Those with PCIe drives (and haven't found cloning software that works on them). 2 - Those who want a fast clone of their boot drive (no re-boots needed) and it can clone your drive wh…
Email security requires an ever evolving service that stays up to date with counter-evolving threats. The Email Laundry perform Research and Development to ensure their email security service evolves faster than cyber criminals. We apply our Threat…

813 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now