Solved

Web Service with vb.net and access db

Posted on 2004-10-12
20
362 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
 
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
Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

 
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

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

Suggested Solutions

This tutorial demonstrates one way to create an application that runs without any Forms but still has a GUI presence via an Icon in the System Tray. The magic lies in Inheriting from the ApplicationContext Class and passing that to Application.Ru…
1.0 - Introduction Converting Visual Basic 6.0 (VB6) to Visual Basic 2008+ (VB.NET). If ever there was a subject full of murkiness and bad decisions, it is this one!   The first problem seems to be that people considering this task of converting…
It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.
This video explains how to create simple products associated to Magento configurable product and offers fast way of their generation with Store Manager for Magento tool.

758 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

21 Experts available now in Live!

Get 1:1 Help Now