jxharding
asked on
crossover from vb6 to vb.net - 4 questions on recordsets & databases - how do i do it in .net
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.OL EDB.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
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.OL
"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.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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Thanks!
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
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(strConnect
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