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.OleDb
Public Class Data
#Region "Retrevial"
Public Shared Function GetRow(ByVal Table As String, ByVal Key As
Guid) As DataRow
Dim DataSet As DataSet = GetDataSet("SELECT * 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("SELECT * 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("SELECT " & 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("SELECT " & 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("SELECT ID FROM " & Table
& " WHERE " & KeyField & " = " & Key)
If DataSet.Tables(0).Rows.Count > 0 Then Return
CType(DataSet.Tables(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("SELECT COUNT(ID) FROM " &
Table & " WHERE ID = {" & Key.ToString & "}")
Try
Return CInt(DataSet.Tables(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("SELECT COUNT(ID) FROM " &
Table & " WHERE " & Filter)
Try
Return CInt(DataSet.Tables(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(Query)
Try
Return DataSet.Tables(0).Rows.Count()
Catch ex As Exception
Return 0
End Try
End Function
Public Shared Function GetDataSet(ByVal Query As String) As
DataSet
Dim DataAdapter As New OleDbDataAdapter(Query,
Global.DataSource)
GetDataSet = New DataSet
DataAdapter.Fill(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.DataSource)
Connection.Open()
Dim Command As OleDbCommand = New OleDbCommand(Query,
Connection)
Try
Command.ExecuteNonQuery()
Catch ex As Exception
Execute = ex.Message & vbLf & vbLf & Query
End Try
Connection.Close()
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