Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

crossover from vb6 to vb.net - 4 questions on recordsets & databases - how do i do it in .net

Posted on 2003-11-26
3
Medium Priority
?
363 Views
Last Modified: 2008-03-17
basic recordsets & recordsets update from vb6 to vb.net help needed


good day
i am an vb6 user and i want to make the cross over to vb.net
i am scared
could someone please help me with this because it will save me loads of time & frustration,
i think a lot of people in the future will gain from this

all the following code is from VB6sp5,using access 2000

this is how i connect to an access database:
____________________________________________
Private oConn As ADODB.Connection
Set oConn = New ADODB.Connection
'Assign the connection string
  oConn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
           "Data Source=.\tapeus.mdb;" & _
           "User ID=admin;" & _
           "Password=;"


this is how i set & open the recordset from the database i connected to
_______________________________________________________________________
    SQLQ = "SELECT * FROM PRODUCTS"
    Dim rsProducts As New ADODB.Recordset
    rsProducts.Open SQLQ, oConn, adOpenStatic, adLockBatchOptimistic, adCmdText


this is how i set the values from a recordset into a listview
_____________________________________________________________
      for i = 1 to rsproducts.recordcount
             Set li = ListView1.ListItems.Add(i, , rsProducts![TableName])
               rsProducts.MoveNext
      next i


and this is how i updated the database from user input
______________________________________________________
 AddCust.Open SQLQ, oConn, adOpenDynamic, adLockOptimistic, adCmdText
        AddCust.AddNew
            AddCust![CustomerSurname] = txt1.Text
        AddCust.Update


could someone give me this in vb.net code,
obviously just "skeleton code" if i may call it that.
i havent tried but i heard of some people having big problems connecting via vb.net  

0
Comment
Question by:jxharding
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
3 Comments
 
LVL 12

Expert Comment

by:roverm
ID: 9825004
You can use a general function:

    Public Function ReadDataSet(ByVal strSQL As String, Optional ByVal NothingWhenEmpty As Boolean = True) As DataSet
        Try
            Dim objDA As New SqlDataAdapter(strSQL, strConnection)
            Dim objDS As New DataSet
            objDA.Fill(objDS)

            If (objDS.Tables.Count > 0 AndAlso objDS.Tables(0).Rows.Count > 0) Or (Not NothingAlsLeeg) Then
                Return objDS
            Else
                Return Nothing
            End If
        Catch ex As Exception
            MsgBox ex.Message
            Return Nothing
        End Try
    End Function

(You need to set the strConnection variable yourself. It's the same as in VB6.)

Then declare a dataset for general use:

Dim ds As DataSet

Then to read a 'recordset' = dataset:

ds = ReadDataSet("select * from mytable")

Nothing will be returned if no records are found.

I would recommend to use a datagrid to show all data. Simpy bind to it:

DataGrid1.DataSource = ds

That's ALL! ;-)

To add a new record to the db, use this function:

    Public Function ExecuteStatement(ByVal strSQL As String) As Int32
        Dim intRecCount As Int32 = -1
        Try
            Dim objConn As New OleDBConnection(strConnection)
            objConn.Open()
            Dim objCommand As New OleDBCommand
            With objCommand
                .CommandType = CommandType.Text
                .CommandText = strSQL
                .Connection = objConn
                intRecCount = .ExecuteNonQuery()
            End With
            objConn.Close()
            Return intRecCount
        Catch ex As Exception
            MsgBox ex.Message
            Return -1
        End Try
    End Function

To insert:

MsgBox ExecuteStatement("insert into mytable (idfield) values 1")

The returned value is the number of affected records.

D'Mzz!
RoverM
0
 
LVL 12

Accepted Solution

by:
roverm earned 1000 total points
ID: 9825009
Whoops, change this line:
            If (objDS.Tables.Count > 0 AndAlso objDS.Tables(0).Rows.Count > 0) Or (Not NothingAlsLeeg) Then

To

            If (objDS.Tables.Count > 0 AndAlso objDS.Tables(0).Rows.Count > 0) Or (Not NothingWhenEmpty) Then
0
 
LVL 12

Expert Comment

by:roverm
ID: 9836380
Thanks!
0

Featured Post

Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

Question has a verified solution.

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

It seems a simple enough task, yet I see repeated questions asking how to do it: how to pass data between two forms. In this article, I will show you the different mechanisms available for you to do just that. This article is directed towards the .N…
A long time ago (May 2011), I have written an article showing you how to create a DLL using Visual Studio 2005 to be hosted in SQL Server 2005. That was valid at that time and it is still valid if you are still using these versions. You can still re…
In this video, Percona Director of Solution Engineering Jon Tobin discusses the function and features of Percona Server for MongoDB. How Percona can help Percona can help you determine if Percona Server for MongoDB is the right solution for …
How to fix incompatible JVM issue while installing Eclipse While installing Eclipse in windows, got one error like above and unable to proceed with the installation. This video describes how to successfully install Eclipse. How to solve incompa…

609 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