Visual Basic

Advice for tracking a recordset change - Hash Value?

17-Apr-06 4:24

Depending on the functionality you want, this is easy enough.

On the SelectedIndexChanged (? from memory) event, get the ValueMember and do another lookup on the database that returns the fields you want based on that UserId number. You can either directly change the Text properties of your TextBox's from the record that is returned or bind the TextBox's Text properties to each field.

Dave Kreskowiak
Microsoft MVP - Visual Basic

Brad6ft414-Apr-06 7:59

Re: Advice for tracking a recordset change - Hash Value?

14-Apr-06 7:59

I am working within a VB.NET [windows forms application] /SQL Server 2000 db. When a user changes the recordset I want to be able to notify the others that reload this recordset that this record has been modified.

A colleague of mine recommended that I add a Hash value to the changed recordset. This way I can compare the hash values to see if the recordset has been modified.

Having never worked with the Hash Algorithm but after reading about it, sounds like the right plan to follow.

However, I cannot find a tutorial on working with hash values with a recordset and wondered if someone had some advice on this subject.

Any help would be greatly appreciated,

Thanks in advice & Happy Easter,

~Brad Confused | :confused:

-- modified at 14:15 Friday 14th April, 2006

Dave Kreskowiak14-Apr-06 8:26

Re: Advice for tracking a recordset change - Hash Value?

14-Apr-06 8:26

There's a problem with this. Your clients would have to have a central server to accept change notifications and relay them to the other clients.

You might want to look at SQL Server's Notification Services[^] first.

Dave Kreskowiak
Microsoft MVP - Visual Basic

Brad6ft414-Apr-06 8:47

Re: Advice for tracking a recordset change - Hash Value?

14-Apr-06 8:47

Dave,

Currently this Application is running on a server. It has 10 or more client machines that all use the same log in to the Application using the same Username found within Active Directory / roles.

Scenerio:

Inside my App. I have a datagrid that I populate using an SQL StoredProc to display records to the user. The problem is because the Application has only 1 log in, I cannot verify which user has changed the recordset. When 1 or more users opens a record to modify it, I allow the first user to have control over the record and display a message to the other users that try to open it. I perform this by setting a different value to the tables record being modified. The problem arise when the user that has modified the recordset and saves the record to the Db. The other users are now seeing the old recordsets in their datagrid because I have no way to automatically REFRESH their screens with the new datagrid recordset information.

A colleague of mine suggested that I add a Hash value to the record uponLOAD of the datagrid then when the user changes the state of the record I can see if the value has been changed. If I know it has been changed then I can notify the users to REFRESH their screens for their datagrid.

I do not have an Audit trail table that I can access to see if a particular user is working on a particualr table. In addition, I think that b/c I am using Active directory with only 1 Log In Username, I really cannot follow what each Client machine is doing to the records in my DB.

Any advice would be greatly appreciated as I am stumped as to how I can fix this issue within my Application.

Thanks in advance,

~Brad Confused | :confused:

Dave Kreskowiak14-Apr-06 9:28

Re: Advice for tracking a recordset change - Hash Value?

14-Apr-06 9:28

None of this has any bearing on the problem if you use Notification Services. Clients register for notifications of changes to the underlying database. If a record is changed and there are clients registered for notifications, then each client will get a change event they can handle to refresh their datasets.

You really don't need a hash table for anything. The only other way to do this is to write a server component that manages access to the database for the clients. The clients communicate with the server component and "check out" blocks of records in the database. Frankly, you'd be writing an alternative to Notification Services.

Dave Kreskowiak
Microsoft MVP - Visual Basic

Brad6ft417-Apr-06 4:42

Re: Advice for tracking a recordset change - Hash Value?

17-Apr-06 4:42

Dave,

SQL Notification is something very cool and something I will definitlly look into it in order to increase my arsenal of design techniques. However for this particular functionanlity it is quite innapropriate (over kill to say the least). From what i read/understand, the notification makes sense when you have data driven behavior, this is not the case with the functionality of my Application.

For my App. I simply want to check if a row has changed or not. Not act when a row has changed. I am not sure if you see the nuance.

All this to say, SQL Notification is nice, however, not for my application. With the research I now know from your help/advice I may apply it when it fits the model or a behaviour that lends itself to it, keyword is "data driven behaviour" and this is not my Application, though I thank you for your advice.

I found an approach that I will implement for my particular situation, called the hash signature <a href="http://en.wikipedia.org/wiki/Hash_function" rel="nofollow">http://en.wikipedia.org/wiki/Hash_function</a>[<a href="http://en.wikipedia.org/wiki/Hash_function" target="_blank" rel="nofollow" title="New Window">^</a>]

Thanks again,

~Brad
Smile | :)

Dave Kreskowiak17-Apr-06 12:29

Re: Advice for tracking a recordset change - Hash Value?

17-Apr-06 12:29

Brad6ft4 wrote:
quite innapropriate (over kill to say the least).

Yeah? Wait till you get done implementing this in your own code. You'll sit back, look at this code, and think, "Hey, maybe it wasn't overkill after all..."

Brad6ft4 wrote:
I simply want to check if a row has changed or not.

Precisely when do you want to do this? Theres a couple of ways of doing this, but what works depends on your requirements. You have a concurrency problem.

You can lock the records so that only one client gets a Read/Write copy of the data, but if all your clients on working on the same set of records, this won't work. The first client to lock the records is the ONLY client that gets to ever attempt to write any changes back.

If that doesn't work for you, you'll have to implement some kind of "checkout" scheme in your SQL. Since each client has to check to see if the record they have is different from the one it has already, you have to read the values from the database BEFORE you can write the changed data back. The record can change between the time any one client reads it to check for a change and when that same client attempts to write the change back out to the database. You'll have to implement some kind of locking mechanism so that when you read a record to check for a change, you're actually "checking it out" like a book from the library. This part MUST be done in a stored procedure in the database.

From a logic point of view, the stored proc has to attempt to check the record out before it checks to see if the record changed, THEN read and pass it to the client. You'll have to implement a "check out" scheme in SQL. pass some kind of ReadOnly flag in the dataset to go with it. The database has to tell any client that attempts to check to see if it can write data back to the database that the record is already checked out.

Now, using Notification Services, if a record changes, it can automatically notify each client to do something very simple. Re-query the database for this one record.

It's your call...

Dave Kreskowiak
Microsoft MVP - Visual Basic

Brad6ft422-Apr-06 6:33

objectdatasource has no values to insert

22-Apr-06 6:33

Dave,

I found an easier solution [Quick & Dirty] but works well to resolve my issue. Instead of using the Hash function and /or your Smile | :)

suggestion of using SQL Server Notification which for this particular case would have been over kill.

I simply used a check on the tables field name DateModified datetime.

Once the user selects a particular record, upon retrieval of that record, I set a field within my table, to the status of EDIT_MODE [EDIT_MODE = 1]. After which I take the datetime inside the recordset and use it as a marker for later use. If that record is changed, upon the Save() of that record, I set the DateModified date = datetime.Now().

With this approach, I can notify the user making he/she aware that the record they are trying to view has been changed by another user and that they should REFRESH their initial Search criteria to obtain the new recordset of that particular record.

Thanks for all your help,

~Brad
Wink | ;)

dr S.A.f.14-Apr-06 2:43

dr S.A.f.

14-Apr-06 2:43

Hi,
I used the example from http://blog.binaryocean.com/PermaLink,guid,a1ff6cab-dc2d-441c-8557-7dce920d4075.aspx
to create an insertable gridview, My code is identical, apart from the language being C# in the example and VB.NET in my project.

Everything works, except the inserting, when I insert data and press the save button I get an InvalidOperationException in the method

Protected Sub gvAgendaPunten_RowCommand(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.GridViewCommandEventArgs) ' Handles gvAgendaPunten.RowCommand
        If e.CommandName = "Save" Then
            Dim agendaPunt As String = CType(gvAgendaPunten.FooterRow.FindControl("txtAgendaPunt"), TextBox).Text
            Dim vergaderingId As Integer = ddlVergaderingen.SelectedValue
            odsAgendaPunten.InsertParameters.Add("agendaPunt", agendaPunt)
            odsAgendaPunten.InsertParameters.Add("vergaderingId", vergaderingId)
            odsAgendaPunten.Insert()
           
        End If
    End Sub

saying that the ObjectDataSource 'odsAgendaPunten' has no values to insert. Check that the 'values' dictionary contains values.
But the funny thing is dat my odsAgendaPunten.insertparameters has count 2 and items 0 and 1 have the values from agendaPunt and vergaderingId.

I also tried with setting the parameters before, in the source code and via the objectdatasource properties window, nothing seems to work.

If anyone could help me out here, or tell me in what direction to look. I'd appreciate it very much.

I don't think it's necessary to post the rest of the code, since the error must be located in this method (I think), but If it would clear things up, just let me know, and I'll post the rest of it too.

thx
davy.

Re: objectdatasource has no values to insert

Steve Pullan14-Apr-06 3:50

Steve Pullan

14-Apr-06 3:50

I have not used this before, but looking at the code, the author of the sample has separated out the Inserting() event and it is here that the calls to InsertParameters are made.

I think you'll find that the call to Insert() in the RowCommand event raises the Inserting() event on the object which you have not provided hence the error.

...Steve

1. quod erat demonstrandum
2. "Give a man a fish and you've fed him for a day. Teach him how to fish and you've fed him for life." I read that somewhere once Smile | :)

Re: objectdatasource has no values to insert

dr S.A.f.14-Apr-06 8:20

dr S.A.f.

14-Apr-06 8:20

Imports System.data
Partial Class VergaderingWijzigen
    Inherits System.Web.UI.Page
    Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
        If Not Page.IsPostBack() Then
            fillDropDownList()
            Dim key() As String = {"agendaPuntId"}
            gvAgendaPunten.DataKeyNames = key
            gvAgendaPunten.Visible = False
        Else
            gvLeden.DataBind()
        End If
    End Sub
    Private Property loadDataEmpty()
        Get
            Return CType(ViewState("loadDataEmpty"), Boolean)
        End Get
        Set(ByVal value)
            ViewState("loadDataEmpty") = value
        End Set
    End Property
    Protected Sub fillDropDownList()
        Dim collectionVergaderingen As IDataReader = DatabaseAgent.selectUsersVergaderingen("ST")
        ddlVergaderingen.DataSource = collectionVergaderingen
        ddlVergaderingen.DataTextField = "titel"
        ddlVergaderingen.DataValueField = "vergaderingid"
        ddlVergaderingen.DataBind()
        collectionVergaderingen.Close()
        If ddlVergaderingen.Items.Count = 0 Then
            ddlVergaderingen.Visible = False
            lblError.Visible = True
            lblError.Text = "Er zijn geen vergaderingen gepland die u kunt wijzigen"
        Else
            lblError.Visible = True
            ddlVergaderingen.Visible = True
            ddlVergaderingen.Items.Add(New ListItem("Selecteer vergadering", -1))
            ddlVergaderingen.SelectedValue = -1
        End If


    End Sub

    Protected Sub DropDownList1_SelectedIndexChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles ddlVergaderingen.SelectedIndexChanged
        gvAgendaPunten.Visible = True
    End Sub

    Protected Sub odsAgendaPunten_Inserting(ByVal sender As Object, ByVal e As ObjectDataSourceMethodEventArgs) Handles odsAgendaPunten.Inserting
        e.InputParameters("agendaPunt") = CType(gvAgendaPunten.FooterRow.FindControl("txtAgendaPunt"), TextBox).Text
        e.InputParameters("vergaderingId") = ddlVergaderingen.SelectedValue
    End Sub

    Protected Sub odsAgendaPunten_Selected(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.ObjectDataSourceStatusEventArgs) Handles odsAgendaPunten.Selected
        If Not e.Exception Is Nothing Then
            Throw e.Exception
        End If
        Dim d As DataTable = CType(e.ReturnValue, DataSet).Tables.Item(0)
        If d.Rows.Count = 0 Then
            d.Rows.Add(d.NewRow())
            loadDataEmpty = True
        Else
            loadDataEmpty = False
        End If
    End Sub

    Protected Sub odsAgendaPunten_Selecting(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.ObjectDataSourceSelectingEventArgs) Handles odsAgendaPunten.Selecting
        Console.Write("hallo")
    End Sub

    Protected Sub gvAgendaPunten_RowCommand(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.GridViewCommandEventArgs) Handles gvAgendaPunten.RowCommand
        If e.CommandName = "Save" Then
            odsAgendaPunten.Insert()
        End If
    End Sub

    Protected Sub gvAgendaPunten_RowCreated(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.GridViewRowEventArgs) Handles gvAgendaPunten.RowCreated
        If (loadDataEmpty) And e.Row.RowType = DataControlRowType.DataRow Then
            e.Row.Visible = False
            e.Row.Controls.Clear()
        End If
    End Sub

    Protected Sub gvAgendaPunten_SelectedIndexChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles gvAgendaPunten.SelectedIndexChanged

    End Sub
  
    Protected Sub btnAddAgendaPunt_Click1(ByVal sender As Object, ByVal e As System.EventArgs)
        DatabaseAgent.insertAgendaPunt(New DAgendaPunt(ddlVergaderingen.SelectedValue, CType(gvAgendaPunten.FindControl("txtAgendaPunt"), TextBox).Text))
    End Sub
End Class

This is the complete codebehind. (the way it looked before I started to try out several things to get it to work.)
I do have code for the inserting event, offcourse I forgot to mention that in my first post Smile | :)

Or do I have to raise that event somewhere manually?

Vb 6 and access 2000 in LAN

arunendra14-Apr-06 2:11

14-Apr-06 2:11

Hi!
I have developed an application using visual basic 6. I have used access 2000 database and crystal report( for reporting). Now, i want the database to stay in one machine of the workgroup so that other machines can connect (can insert/update and view reports), simply i want it to be a client server application. My questions are :
1.How to configure the machine ( where the database will stay) so that others can use the databse?
2.I have used file dsn for crystal report as i want other machines to use the reports, so is it necesary for the reports to stay with each client application and also how do i find the file dsn of the machine where the database stays?
3.what will be the ado conect string for the clients to connect to the database in the lan?
Regads
Arunendra

Dave Kreskowiak14-Apr-06 3:34

14-Apr-06 3:34

arunendra wrote:
1.How to configure the machine

Access is NOT a good shareable database. It was meant to be a Desktop database. It'll work, but you have to maintain (compact and repair) that database religously! Also, maintenance on the database will take it offline until the maintenance is complete. This means that your application cannot run until your done!

This depends on your machine environment. I highly recommend reading up on Windows networking and security first. But, the basic thing is to setup a Share on the server and put your database files on that share. Then, each workstation has to map a drive to that Share. Excatly how this is done depends on if your machines are in a workgroup or domain. How you setup the Share and NTFS security also depends on this configuration.

arunendra wrote:
2.I have used file dsn for crystal report

I don't use Crystal Reports, so I couldn't tell you...

arunendra wrote:
3.what will be the ado conect string for the clients to connect to the database in the lan?

The same as what your using now. Only you'll have to specify the network drive letter you mapped setting up Windows networking, and specify the full path to the .MDB file.

Dave Kreskowiak
Microsoft MVP - Visual Basic

arunendra14-Apr-06 5:15

14-Apr-06 5:15

Thanks Dave for your reply.
I know Access is not for sharing but the requirement is such that i have to use access. I have a code , therefore, in my application which will automatically run the compact database service everyday at a specific time.
I actually need the code to perform what I need. for eg, if the folder in which the database resides is called "x', and the name of the mdb file is "data.mdb", then, how do I make sure that users can open from other locations,
i have set read & execute,list folder contents,read, write permission for everyone and also have set the same permission for the mdb file. have i done something wrong?

also i have tried the following code for connecting from the client :

Connect_String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=\\machine_name\x\data.mdb;Persist Security Info=False;Jet OLEDB:Database Password=bill1234"
con.open connect_string

but when run from another machine i get the following error msg :
( not exact!!) "the \\machine_name\x\data.mdb can not be opened as this may be not permitted or exclusively locked"

Regarding the crystal report problem, i think i need to know only one thing, i.e. how to share the file dsn that i have used for the crystal report. to be specific, how should i configure the file dsn that i already have in the machine where i have the database, i think that will make the reports available in the client machines. so, can you help me for sharing the file dsn in proper manner.

Regadrs
Arunendra

N.B. I will soon want to learn how to connect to a sql server database in a workgroup and also in a domain, i think you can help me out in that also, but currently please get me out of this.

-- modified at 11:16 Friday 14th April, 2006

Dave Kreskowiak14-Apr-06 6:43

14-Apr-06 6:43

arunendra wrote:
if the folder in which the database resides is called "x', and the name of the mdb file is "data.mdb", then, how do I make sure that users can open from other locations,

Have you setup a share on folder X?? A UNC path takes the format \\machineName\shareName\folder\filename. In your case, if you create a Share on folder X and called that share "Data", then the UNC path would be \\machine_name\Data\mydata.mdb.

As for the FileDSN, the default location for the file is C:\Program Files\Common Files\ODBC\Data Sources. I would suggest copying the FileDSN to that folder on each client machine.

Dave Kreskowiak
Microsoft MVP - Visual Basic

arunendra14-Apr-06 7:10

14-Apr-06 7:10

Thanks again for such a quick reply.
First of all what is UNC? I have given permission a set of permission to that folder (x) and the mbd (data) which i have given in the earlier letter.
Mostly i have given all the permissions except for the full control. do u think it will be sufficient?

second, regarding file dsn, will this problem solve, if i just copy the actual dsn (say mydsn.dsn) file and paste them in each location ( the location you have given)? Or may I try using the odbc manager in each pc and just add the dsn from the remote pc? what should i do?

Regards
arunendra

Dave Kreskowiak14-Apr-06 7:14

14-Apr-06 7:14

arunendra wrote:
First of all what is UNC?

UNC stands for Universal Naming Convention. If you sis not create a SHARE, which you have never mentioned you did, then the security setup won't make a difference. You MUST MUMST MUST MUST create a SHARE on the X folder. Right click the X folder, click on "Sharing...", then click on "Share this folder". Give the share a name...

Yes, you can copy the FileDSN file over to the client workstations and it'll work.

Dave Kreskowiak
Microsoft MVP - Visual Basic

arunendra14-Apr-06 23:35

14-Apr-06 23:35

Thanks Dave.
I am out of my setup and so couldnt try it, but I will follow your instructions and will report it back.
In the mean while, can you help me in a very different subject, as I found this to be amazing, I never thought I will find someone who will answer my questions, and so I am bursting with all the queries, I am sorry for that , but i am trying my best to filter them and send you something that is absolutely impossible for me to do.
I have a code for compacting which uses jet engine,(there was a code which was working fine but was in DAO, and I found some errors occuring in my project and also in the crystal report, so i thought of going on with ado only), the code if changed a little works fine, i dont remeber what changes i had done but it can't put the password back to the newly created mdb file, so i am sending the original code i got , please see what I have to do.
''''''''''''''''''''' the code '''''''''''''''''''''''''''''''''''''
Public Function CompactAndRepairDB(sSource As String, _
sDestination As String, _
Optional sSecurity As String, _
Optional sUser As String = "Admin", _
Optional sPassword As String, _
Optional lDestinationVersion As Long) As Boolean

Dim sCompactPart1 As String
Dim sCompactPart2 As String
Dim oJet As JRO.JetEngine

' Put together the provider string for the source database
sCompactPart1 = "Provider=Microsoft.Jet.OLEDB.4.0" & _
";Data Source=" & sSource & _
";User Id=" & sUser & _
";Password=" & sPassword

' If the database has a user-level security file, add the
' details
If sSecurity <> "" Then
sCompactPart1 = sCompactPart1 & _
";Jet OLEDB:System database=" & sSecurity & ";"
' Else
' sCompactPart1 = sCompactPart1 & _
' ";Jet OLEDB:System database=false ;"
End If

' Put together the provider string for the destination
' database
sCompactPart2 = "Provider=Microsoft.Jet.OLEDB.4.0" & _
";Data Source=" & sDestination & ";Persist Security Info=False;Jet OLEDB:Database Password=bill1234"

' The destination database will end up in the latest version
' of jet, unless a specific version has been requested;
' 1 = Jet 1.0, 2 = Jet 1.1, 3 = Jet 2.x, 4 = Jet 3.x,
' 5 = Jet 4.x etc
If lDestinationVersion <> 0 Then
sCompactPart2 = sCompactPart2 & _
";Jet OLEDB:Engine Type=" & lDestinationVersion
End If

' Compact and repair the database
Set oJet = New JRO.JetEngine
oJet.CompactDatabase sCompactPart1, sCompactPart2
Set oJet = Nothing

CompactAndRepairDB = True
End Function

this code produces the following error number :-2147217843
and the following error msg :
"Cannot start your application. The workgroup information file is missing or opened exclusively by another user."
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Regards
arunendra

Dave Kreskowiak15-Apr-06 3:37

15-Apr-06 3:37

First, the database cannot be opened by ANYTHING. Not one of your client machines, not Access, ... NOTHING can have the database open already.

Second, if there is a workgroup file for the database, it has to be in the same folder as the .MDB file.

Next, we'll just simplify your code a bit:

' sSrcPath is the source database file path
' sDstPath is the source database file path
' sUser is the username to use to login to the source database
' sPassword is the sUser's password
' sSystemPassword is the Database Password to use to login to the source
'                 and reset in the destination databases

Public Function CompactAndRepairDB( _
        sSrcPath As String, _
        sDstPath As String, _
        Optional sUser As String = "Admin", _
        Optional sPassword As String, _
        Optional sSystemPassword As String) _
            As Boolean
 
    Dim srcConn As String
    srcConn = String.Format("Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};User Id={1};Password={2};Jet OLEDB:Database Password={3}", sSource, sUser, sPassword, sSystemPassword)
 
    Dim dstConn As String
    dstConn = String.Format("Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Jet OLEDB:Engine Type=5;Jet OLEDB:Database Password={1}", sDestination, sSystemPassword)
 
    Dim JRO As New JRO.JetEngine
    JRO.CompactDatabase(srcConn, dstConn)
 
    Return True
End Function

If you wanted to use this in more applications, I'd rewrite it to be more flexible. Right now, it'll only work on databases that have a Database Password set.

Oh!! Don't forget to rename the source database to a temp name and rename the destination database back to the same name as the original source!!

Dave Kreskowiak
Microsoft MVP - Visual Basic

arunendra15-Apr-06 5:18

15-Apr-06 5:18

Thanks dave!
But in the code you sent string.format part is showing a compile error and the error says : "expected ("
is it problem in my vb? do i have to do something to rectify this error?

and also, i want everyone to open the database, ofcourse through the software so that they can insert/update/delete/select records. for that i have used following code for opening the connection :
connect_string is string type data
Connect_String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=\\Flexnet\TECHNOPACK\technopack.mdb;Persist Security Info=False;Jet OLEDB:Database Password=bill1234"
con.execute str

and for selecting data i have used :
dim str as string
str="
str="select * from Stock"
set rec=nothing
rec.Open str, Con, adOpenStatic, adLockOptimistic, adCmdText

and for any update/delete/insert i have first built the string and then
simply executed the statement by con.execute str where con is the adodb connection.

for that machine where the database will reside i have used the following
string :
'con is the adodb connection
Connect_String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & app.path & "\TECHNOPACK\technopack.mdb;Persist Security Info=False;Jet OLEDB:Database Password=bill1234"
con.execute str

Have i done something wrong? Please note that i want every pc to have the same right, so that everyone can use the database. and for the compacting the database i will provide that option only in the machine where the database will reside.
and also if possible please send the compacting code you were talking about.
Regadrs
Arunendra

-- modified at 12:11 Saturday 15th April, 2006
N.B. just now i have found something!! the code for the client doesnt run until i get into that particular machine where the database stored with a user id and password, so my question is can i integrate that machine user id and password in my code? i tried to get into the machine with the following command from the run -> \\machine_name; it asked for user id and password, i gave them and then the shared resources appeared. then i tried to open the exe and the exe worked!! so is my question! am i right?? and can this be a way of connecting in the workgroup for running my software?

Dave Kreskowiak15-Apr-06 6:47

15-Apr-06 6:47

arunendra wrote:
But in the code you sent string.format part is showing a compile error and the error says : "expected (". Is it problem in my vb? do i have to do something to rectify this error?

I forgot, you're using VB6. The code I posted is for VB.NET. Here's the VB6 translated portions:

    Dim srcConn As String
    srcConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & sSource & _
        ";User Id=" & sUser & _
        ";Password=" & sPassword & _
        ";Jet OLEDB:Database Password=" & sSystemPassword
 
    Dim dstConn As String
    dstConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & sDestination & _
        ";Jet OLEDB:Engine Type=5" & _
        ";Jet OLEDB:Database Password=" & sSystemPassword

arunendra wrote:
N.B. just now i have found something!! the code for the client doesnt run until i get into that particular machine where the database stored with a user id and password, so my question is can i integrate that machine user id and password in my code?

Can it be put into the code? Yes. Is it a good idea?? HELL NO!! Don't even think of doing this!

Map a drive letter to the network share before you try to launch the .EXE. The process of mapping the drive will ask for the users name and password, adding a FAR MORE MANAGEABLE layer of security to your application. If you put it in your code, your code can then NOT be transfered to another environment, being explicitly tied to this exact network setup. Not a good idea...

Then, in your connection string, supply the full path to the database file using the drive letter that was mapped, i.e.: F:\Technopack.mdb.

Dave Kreskowiak
Microsoft MVP - Visual Basic

arunendra16-Apr-06 2:26

16-Apr-06 2:26

Thanks Dave for your continuous support!
1.for the first part (comapcting), the string (source) now looks like the following when it is passed to the function:
Provider=Microsoft.Jet.OLEDB.4.0;Data Source=;User Id=Admin;password= ;Jet OLEDB:Database Password=bill1234
is the formation right?

2. the part : oJet.CompactDatabase(srcConn, dstConn), gives a compiler error
"expected:="; if i remove the brackets, there is no error, will it work?

3. if i try like that (with out bracket and with the string formed above, and when run,when the compactdatabase part comes, the error msg occures :
"object variable or with block variable not set"
what shall i do?

for the second part, can you build a complete connect string, for eg if the machine where the database is present is called "machine one", and the database mdb file is in the d drive of that machine (d:\datafolder\data.mdb), and if that folder is shared as "datafolder_shared", with the permission of "modify/read/execute and write" for a group of users and full control for the administrators, then what should be the string to connect from another machine :
should it be something like the following :

Connect_String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=\\machine one\datafolder_shared\data.mdb;Persist Security Info=False;Jet OLEDB:Database Password=bill1234"

or

Connect_String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=\\machine one\datafolder_shared:\data.mdb;Persist Security Info=False;Jet OLEDB:Database Password=bill1234"

or something else?
you want me to map a drive, but how to do and in which machine i have to do? i have so, given the full situation, plz tell me a bit more. I am sorry as i dont know much of computer administration, so i give you some trouble, i hope you will pull me out of this.
Regards
Arunendra

Dave Kreskowiak16-Apr-06 10:24

16-Apr-06 10:24

arunendra wrote:
1.for the first part (comapcting), the string (source) now looks like the following when it is passed to the function:
Provider=Microsoft.Jet.OLEDB.4.0;Data Source=;User Id=Admin;password= ;Jet OLEDB:Database Password=bill1234
is the formation right?

Nope. You didn't supply a path to the database you want to compact. Notice that Data Source doesn't have anything after it?

arunendra wrote:
2. the part : oJet.CompactDatabase(srcConn, dstConn), gives a compiler error
"expected:="; if i remove the brackets, there is no error, will it work?

Remove the paranthesis and it'll work. It's been about 6 years since I've used VB6. I forgot, if your don't use the return value, you don't supply paranthesis around the arguments your passing to the function.

arunendra wrote:
3. if i try like that (with out bracket and with the string formed above, and when run,when the compactdatabase part comes, the error msg occures :
"object variable or with block variable not set"
what shall i do?

Because you didn't supply the Data Source parameter in question 1. How is it supposed to know which database to compact if that Data Source is blank???

arunendra wrote:
you want me to map a drive, but how to do and in which machine i have to do?

This is why I told you to read up on Windows networking before you tried this! You have to map a drive before you launch this application. This is done through login scripts or you can setup a persistant drive mapping on each client workstation, or map the drive using a batch file before the app is launched, ...

Dave Kreskowiak
Microsoft MVP - Visual Basic

arunendra17-Apr-06 0:02