How to get VB6 Recordset functionality from within .Net

[JohnSavage]

This is how I did it in VB6 with Access how do I do these things in VB 2005 with CE Compact?

‘OPEN CONNECTION, CREATE RECORDSET and GET VALUES FROM DATABASE

Set Connect = New ADODB.Connection
Connect.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & App.Path & "\maxi.mdb;Jet OLEDB:Database Password=IO34peration;"
Connect.Open
Set RS = New ADODB.Recordset
RS.Open "PetT1", Connect, adOpenKeyset, adLockPessimistic, adCmdTable
RS.MoveFirst
NF = CDbl(Encrypt(RS.Fields("8").Value, KeyJs))
RS.Close
Set RS = Nothing

SAVE DATA

Set RS = New ADODB.Recordset RS.Open "PurejetT1", Connect, adOpenKeyset, adLockPessimistic, adCmdTable
        RS.MoveFirst
        RS.Fields("6").Value = Encrypt((CStr(JF)), KeyJs)
RS.Update
RS.Close
Set RS = Nothing

[RobDog888]

You shouldnt use ADO in .NET as its depreciated. You should use ADO.NET. See mendhaks intro to ADO.NET tutorial for a good walk through.

http://vbforums.com/showthread.php?t=466658

[RhinoBull]

You may want to read tutorials in DB FAQ - it's under VB.Net subtitle.

[JohnSavage]

Many thanks for that but

Using this tutorial http://vbforums.com/showthread.php?t=466658

I have downloaded and reviewed this example, can I use this same code systax for a MS SQL Compact database ? as this example uses an access database

[RhinoBull]

The beauty of using ADO is its flexibility - most often all you'll have to do is to change connection sting, provider.
For proper connection string check "connection strings" link in my signature.

[JohnSavage]

Thanks for that but now I have an error message MicroSoft.SQLSERVER.MOBILE.OLEDB.3.0 provider is not registered on the local machine

[Hack]

What is the current code that you are running?

[JohnSavage]

http://vbforums.com/showthread.php?t=466658

But I have changed the connection string for a connection to a SQL CE Compact Database

[RobDog888]

http://vbforums.com/showthread.php?t=466658

Edit:
If you changed your connectionstring for sql ce you shouldnt get errors. Can you post what code you are using?

[JohnSavage]

This is the connection string

conn.ConnectionString = "Provider=Microsoft.SQLSERVER.MOBILE.OLEDB.3.0;Data Source=C:\Users\John Savage\Database\MyDatabase#1.sdf;User Id=admin;Password=;SSCE:Encrypt Database=True;"

and if it helps this is the error detail

System.InvalidOperationException was unhandled
Message="The 'Microsoft.SQLSERVER.MOBILE.OLEDB.3.0' provider is not registered on the local machine."
Source="System.Data"
StackTrace:
at System.Data.OleDb.OleDbServicesWrapper.GetDataSource(OleDbConnectionString constr, DataSourceWrapper& datasrcWrapper)
at System.Data.OleDb.OleDbConnectionInternal..ctor(OleDbConnectionString constr, OleDbConnection connection)
at System.Data.OleDb.OleDbConnectionFactory.CreateConnection(DbConnectionOptions options, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningObject)
at System.Data.ProviderBase.DbConnectionFactory.CreateNonPooledConnection(DbConnection owningConnection, DbConnectionPoolGroup poolGroup)
at System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection)
at System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory)
at System.Data.OleDb.OleDbConnection.Open()
at System.Data.Common.DbDataAdapter.FillInternal(DataSet dataset, DataTable[] datatables, 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)
at ADONetTutorial1.Form2.Form2_Load(Object sender, EventArgs e)
at System.EventHandler.Invoke(Object sender, EventArgs e)
at System.Windows.Forms.Form.OnLoad(EventArgs e)
at System.Windows.Forms.Form.OnCreateControl()
at System.Windows.Forms.Control.CreateControl(Boolean fIgnoreVisible)
at System.Windows.Forms.Control.CreateControl()
at System.Windows.Forms.Control.WmShowWindow(Message& m)
at System.Windows.Forms.Control.WndProc(Message& m)
at System.Windows.Forms.ScrollableControl.WndProc(Message& m)
at System.Windows.Forms.ContainerControl.WndProc(Message& m)
at System.Windows.Forms.Form.WmShowWindow(Message& m)
at System.Windows.Forms.Form.WndProc(Message& m)
at System.Windows.Forms.Control.ControlNativeWindow.OnMessage(Message& m)
at System.Windows.Forms.Control.ControlNativeWindow.WndProc(Message& m)
at System.Windows.Forms.NativeWindow.DebuggableCallback(IntPtr hWnd, Int32 msg, IntPtr wparam, IntPtr lparam)
at System.Windows.Forms.SafeNativeMethods.ShowWindow(HandleRef hWnd, Int32 nCmdShow)
at System.Windows.Forms.Control.SetVisibleCore(Boolean value)
at System.Windows.Forms.Form.SetVisibleCore(Boolean value)
at System.Windows.Forms.Control.set_Visible(Boolean value)
at System.Windows.Forms.Application.ThreadContext.RunMessageLoopInner(Int32 reason, ApplicationContext context)
at System.Windows.Forms.Application.ThreadContext.RunMessageLoop(Int32 reason, ApplicationContext context)
at System.Windows.Forms.Application.Run(ApplicationContext context)
at Microsoft.VisualBasic.ApplicationServices.WindowsFormsApplicationBase.OnRun()
at Microsoft.VisualBasic.ApplicationServices.WindowsFormsApplicationBase.DoApplicationModel()
at Microsoft.VisualBasic.ApplicationServices.WindowsFormsApplicationBase.Run(String[] commandLine)
at ADONetTutorial1.My.MyApplication.Main(String[] Args)
at System.AppDomain.nExecuteAssembly(Assembly assembly, String[] args)
at System.AppDomain.ExecuteAssembly(String assemblyFile, Evidence assemblySecurity, String[] args)
at Microsoft.VisualStudio.HostingProcess.HostProc.RunUsersAssembly()
at System.Threading.ThreadHelper.ThreadStart_Context(Object state)
at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state)
at System.Threading.ThreadHelper.ThreadStart()

[szlamany]

btw - it seems a DATASET is close to the old VB6 read-write recordset. You can read and write to it.

The DATAREADER appears to be a forward-only type of structure.

See this discussion...

http://www.vbforums.com/showthread.php?t=482228

[JohnSavage]

I am making progress but it's slow, these are the changes I have made to the tutorial

Imports System.Data.SqlServerCe
Imports System.Data.SqlClient

Dim da As New SqlCeDataAdapter()
Dim conn As New SqlCeConnection()

SqlCeCommand used to replace OLBCommand in the code systax

Now I get this error

An error occurred creating the form. See Exception.InnerException for details. The error is: Unable to load DLL 'sqlceme30.dll': The specified module could not be found. (Exception from HRESULT: 0x8007007E)

Any takers

[JohnSavage]

I also just changed the connection string to

conn.ConnectionString ="Data Source=C:\Users\John Savage\Database\MyDatabase#1.sdf;Persist Security Info=False;"

As per connectionstrings.com

But I still get the error message

[szlamany]

Google found that error message - check out the links...

http://www.google.com/search?hl=en&q...=Google+Search

[JohnSavage]

Hi

Thanks I have followed that link and found the solution was to add sqlceme30.dll and all the other dll's that begin with sqlce to the project files.

However having done this I still get the error message above that 'sqlceme30.dll' cannot be found