On Fri, 28 May 2004 13:54:12 -0500, "Chad A. Beckner"
<Ch*********@Pr ospectiveLink.c om> wrote:
Ok, I have added it to my global.asa file, but when I try to view/edit the
sql statement, it says that I don't have an active connection. Ideas?
Chad
You still need to create a connection when you need to use the
database, the above simply sets up a global connection string.
Here's what i do. I have created a class called data that i can used
anywhere in the project, this is for a oledb but it is easy enough to
change to SQL. I have a bunch of functions that i use regularly for my
database work:
<code>
Option Strict On
Imports System.Data.Ole Db
Public Class Data
#Region "Retrevial"
Public Shared Function GetRow(ByVal Table As String, ByVal Key As
Guid) As DataRow
Dim DataSet As DataSet = GetDataSet("SEL ECT * FROM " & Table &
" WHERE ID = {" & Key.ToString & "}")
If DataSet.Tables( 0).Rows.Count > 0 Then Return
DataSet.Tables( 0).Rows(0)
End Function
Public Shared Function GetRow(ByVal Table As String, ByVal Key As
String, ByVal KeyField As String) As DataRow
Dim DataSet As DataSet = GetDataSet("SEL ECT * FROM " & Table &
" WHERE " & KeyField & " = " & Key)
If DataSet.Tables( 0).Rows.Count > 0 Then Return
DataSet.Tables( 0).Rows(0)
End Function
Public Shared Function GetValue(ByVal Table As String, ByVal Field
As String, ByVal Key As Guid) As String
Dim DataSet As DataSet = GetDataSet("SEL ECT " & Field & " FROM
" & Table & " WHERE ID = {" & Key.ToString & "}")
If DataSet.Tables( 0).Rows.Count > 0 Then Return
DataSet.Tables( 0).Rows(0).Item (0).ToString
End Function
Public Shared Function GetValue(ByVal Table As String, ByVal Field
As String, ByVal Key As String, ByVal KeyField As String) As String
Dim DataSet As DataSet = GetDataSet("SEL ECT " & Field & " FROM
" & Table & " WHERE " & KeyField & " = " & Key)
If DataSet.Tables( 0).Rows.Count > 0 Then Return
DataSet.Tables( 0).Rows(0).Item (0).ToString
End Function
Public Shared Function GetID(ByVal Table As String, ByVal Key As
String, ByVal KeyField As String) As Guid
Dim DataSet As DataSet = GetDataSet("SEL ECT ID FROM " & Table
& " WHERE " & KeyField & " = " & Key)
If DataSet.Tables( 0).Rows.Count > 0 Then Return
CType(DataSet.T ables(0).Rows(0 ).Item(0), Guid)
End Function
Public Shared Function GetCount(ByVal Table As String, ByVal Key
As Guid) As Integer
Dim DataSet As DataSet = GetDataSet("SEL ECT COUNT(ID) FROM " &
Table & " WHERE ID = {" & Key.ToString & "}")
Try
Return CInt(DataSet.Ta bles(0).Rows(0) .Item(0))
Catch ex As Exception
Return 0
End Try
End Function
Public Shared Function GetCount(ByVal Table As String, ByVal
Filter As String) As Integer
Dim DataSet As DataSet = GetDataSet("SEL ECT COUNT(ID) FROM " &
Table & " WHERE " & Filter)
Try
Return CInt(DataSet.Ta bles(0).Rows(0) .Item(0))
Catch ex As Exception
Return 0
End Try
End Function
Public Shared Function GetCount(ByVal Query As String) As Integer
Dim DataSet As DataSet = GetDataSet(Quer y)
Try
Return DataSet.Tables( 0).Rows.Count()
Catch ex As Exception
Return 0
End Try
End Function
Public Shared Function GetDataSet(ByVa l Query As String) As
DataSet
Dim DataAdapter As New OleDbDataAdapte r(Query,
Global.DataSour ce)
GetDataSet = New DataSet
DataAdapter.Fil l(GetDataSet)
Return GetDataSet
End Function
#End Region
#Region "Append"
Public Shared Function Execute(ByVal Query As String) As String
Dim Connection As New OleDbConnection (Global.DataSou rce)
Connection.Open ()
Dim Command As OleDbCommand = New OleDbCommand(Qu ery,
Connection)
Try
Command.Execute NonQuery()
Catch ex As Exception
Execute = ex.Message & vbLf & vbLf & Query
End Try
Connection.Clos e()
End Function
#End Region
End Class
</code>
This is all very specific to my needs, but have a look at the
GetDataSet function, i think this is pretty much what you are after.
To create a dataset in the project all i have to do is the following
call:
Dim MyDataSet as DataSet = Data.GetDataSet ("SELECT * FROM MyTable")
Hope this helps
Blu
