For mange forbindelser til database
Nedenstående kode låser min Access database - sikkert pga. for mange forbindelser (Jeg har prøvet det samme i MySql db - det giver Too many connections fejl)Hvordan får jeg reduceret antallet af forbindelser?? - gerne til een..... :-)
Mvh
Morch
Imports System.Data
Imports System.Data.OleDb
Imports System.Io
Imports System.Web.Mail
Imports System.Text
Public Class accept
Inherits System.Web.UI.Page
#Region " Web Form Designer Generated Code "
'This call is required by the Web Form Designer.
<System.Diagnostics.DebuggerStepThrough()> Private Sub InitializeComponent()
End Sub
Protected WithEvents kurven As System.Web.UI.WebControls.Table
Protected WithEvents LinkButton1 As System.Web.UI.WebControls.LinkButton
Protected WithEvents kunde As System.Web.UI.WebControls.Table
Protected WithEvents Totaltabel As System.Web.UI.WebControls.Table
Protected WithEvents TotalBox As System.Web.UI.WebControls.TextBox
Protected WithEvents gebyrBox As System.Web.UI.WebControls.TextBox
Protected WithEvents gebyrLabel As System.Web.UI.WebControls.Label
Protected WithEvents totalLabel As System.Web.UI.WebControls.Label
Protected WithEvents mvh As System.Web.UI.WebControls.Table
'NOTE: The following placeholder declaration is required by the Web Form Designer.
'Do not delete or move it.
Private designerPlaceholderDeclaration As System.Object
Private Sub Page_Init(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Init
'CODEGEN: This method call is required by the Web Form Designer
'Do not modify it using the code editor.
InitializeComponent()
End Sub
#End Region
Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
If Request.QueryString("orderId") = "" Then Response.Redirect("http://xxxxx.dk")
Dim KID As Integer = Request.QueryString("orderId")
opdaterkunde(KID)
kunden(KID)
Dim strConnection As String
Dim strSQL, xxxxx As String
Dim objConnection As OleDbConnection
Dim objCommand As OleDbCommand
Dim objDataReader As OleDbDataReader
Dim pris, total As Integer
strSQL = "select * from xxxxx where kid=" & KID & ""
strConnection = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=D:\\web\\xxxxx.mdb"
objConnection = New OleDbConnection(strConnection)
objConnection.Open()
objCommand = New OleDbCommand
objCommand.Connection = objConnection
objCommand = New OleDbCommand(strSQL, objConnection)
objDataReader = objCommand.ExecuteReader(CommandBehavior.CloseConnection)
While objDataReader.Read()
...........
..............
total = total + (objDataReader.Item("antal") * (objDataReader.Item("pris") / 100))
End While
Dim raekke4 As New TableRow
Dim celle41 As New TableCell
celle41.Controls.Add(New LiteralControl("<br><br>"))
If Request.QueryString("bet") = "giro" Then
celle41.Controls.Add(New LiteralControl("Du har valgt at betale med giro.<br>Beløbet skal indbetales på <b>girokonto x-xxx-xxxx</b><br><br>Husk at angive ordrenummer <b>" & Request.QueryString("orderid") & "</b> <br><br>Når vi har modtaget betaling sender vi straks varen til dig<br>Der er altid 14 dages returret på varer købt på internettet.<br><br>"))
ElseIf Request.QueryString("bet") = "check" Then
celle41.Controls.Add(New LiteralControl("Du har valgt at betale med check.<br>Checken skal udstedes og sendes til<br><br>xxxxxxxxxxxxxxxxxx APS<br>xxxxxxxxxxxxxx<br>xxxx xxxxxx<br><br>Husk at angive ordrenummer <b>" & Request.QueryString("orderid") & "</b> <br><br>Når vi har modtaget betaling sender vi straks varen til dig<br>Der er altid 14 dages returret på varer købt på internettet.<br><br>"))
Else
celle41.Controls.Add(New LiteralControl("Tak for din Ordre, nummer <b>" & Request.QueryString("orderid") & "</b> <br><br>Der er altid 14 dages returret på varer købt på internettet.<br><br>"))
End If
celle41.Controls.Add(New LiteralControl("<b><font size=+1>Med venlig hilsen<br><br>xxxxxxxxxxxxxxxxxx APS</font></b><br>xxxxxxxxxxxxxx<br>xxxx xxxxxx<br>Telefon: +xxxxxxxx<br>xxx@xxxxxx.dk"))
raekke4.Cells.Add(celle41)
mvh.Rows.Add(raekke4)
TotalBox.Text = FormatNumber(totalen(KID), 2)
totalLabel.Text = FormatNumber(totalen(KID), 2)
gebyrBox.Text = FormatNumber(totalen(KID) - total, 2)
gebyrLabel.Text = FormatNumber(totalen(KID) - total, 2)
objDataReader.Close()
objConnection.Close()
End Sub
Sub kunden(ByVal kid)
Dim strConnection As String
Dim strSQL, navn As String
Dim objConnection As OleDbConnection
Dim objCommand As OleDbCommand
Dim objDataReader As OleDbDataReader
strConnection = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=D:\\web\\xxxxx.mdb"
objConnection = New OleDbConnection(strConnection)
objConnection.Open()
strSQL = "SELECT * FROM kunder WHERE ordreid = " & kid
objCommand = New OleDbCommand
objCommand.Connection = objConnection
objCommand = New OleDbCommand(strSQL, objConnection)
objDataReader = objCommand.ExecuteReader(CommandBehavior.CloseConnection)
While objDataReader.Read()
Dim raekke3 As New TableRow
Dim celle31 As New TableCell
celle31.ColumnSpan = 4
celle31.Controls.Add(New LiteralControl("Kommentar: " & objDataReader.Item("Kommentar") & "<br>"))
raekke3.Cells.Add(celle31)
kunde.Rows.Add(raekke3)
End While
objDataReader.Close()
objConnection.Close()
End Sub
Function checkAntalVareIkurv(ByVal ID)
Dim strConnection As String
Dim strSQL, xxxxx As String
Dim objConnection As OleDbConnection
Dim objCommand As OleDbCommand
Dim objDataReader As OleDbDataReader
Dim antal As Integer
strSQL = "select antal from xxxxx where ID=" & ID
'Response.Write(strSQL)
strConnection = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=D:\\web\\xxxxx.mdb"
objConnection = New OleDbConnection(strConnection)
objConnection.Open()
objCommand = New OleDbCommand
objCommand.Connection = objConnection
objCommand = New OleDbCommand(strSQL, objConnection)
objDataReader = objCommand.ExecuteReader(CommandBehavior.CloseConnection)
While objDataReader.Read()
If objDataReader.HasRows Then
antal = objDataReader.Item("antal")
Return antal
End If
End While
objDataReader.Close()
objConnection.Close()
End Function
Function totalen(ByVal kid)
Dim strConnection As String
Dim strSQL, navn As String
Dim objConnection As OleDbConnection
Dim objCommand As OleDbCommand
Dim objDataReader As OleDbDataReader
strConnection = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=D:\\web\\xxxxx.mdb"
objConnection = New OleDbConnection(strConnection)
objConnection.Open()
strSQL = "SELECT total FROM kurve WHERE kurvid = " & kid
objCommand = New OleDbCommand
objCommand.Connection = objConnection
objCommand = New OleDbCommand(strSQL, objConnection)
objDataReader = objCommand.ExecuteReader(CommandBehavior.CloseConnection)
While objDataReader.Read()
Return objDataReader("total")
End While
objDataReader.Close()
objConnection.Close()
End Function
Function onEmail(ByVal onid)
Dim strConnection As String
Dim strSQL, navn As String
Dim objConnection As OleDbConnection
Dim objCommand As OleDbCommand
Dim objDataReader As OleDbDataReader
strConnection = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=D:\\web\\xxxxx.mdb"
objConnection = New OleDbConnection(strConnection)
objConnection.Open()
strSQL = "SELECT email FROM overnatning WHERE onid = " & onid
objCommand = New OleDbCommand
objCommand.Connection = objConnection
objCommand = New OleDbCommand(strSQL, objConnection)
objDataReader = objCommand.ExecuteReader(CommandBehavior.CloseConnection)
While objDataReader.Read()
Return objDataReader("email")
End While
objDataReader.Close()
objConnection.Close()
End Function
Function kundemail(ByVal kid)
Dim strConnection As String
Dim strSQL, navn As String
Dim objConnection As OleDbConnection
Dim objCommand As OleDbCommand
Dim objDataReader As OleDbDataReader
strConnection = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=D:\\web\\xxxxx.mdb"
objConnection = New OleDbConnection(strConnection)
objConnection.Open()
strSQL = "SELECT email FROM kunder WHERE ordreid = " & kid
objCommand = New OleDbCommand
objCommand.Connection = objConnection
objCommand = New OleDbCommand(strSQL, objConnection)
objDataReader = objCommand.ExecuteReader(CommandBehavior.CloseConnection)
While objDataReader.Read()
Return objDataReader.Item("email")
End While
objDataReader.Close()
objConnection.Close()
End Function
Sub opdaterledige(ByVal ankomst, ByVal afrejse, ByVal onid, ByVal aid)
Dim x As Integer
Dim datoen As Date
For x = 0 To DateDiff(DateInterval.Day, ankomst, afrejse) - 1
datoen = DateAdd("d", x, CDate(ankomst))
'Response.Write(ledig(datoen, onid))
If ledig(datoen, onid) <> -1 Then ' Hvis der findes en dato i ledige
retledig(0, datoen, onid, ledig(datoen, onid) - 1)
If ledig(datoen, onid) - 1 = 0 Then sendmailUdsolgt(onid, datoen)
Else
Dim HL As Array = hojlav(aid)
If datoen >= HL(2) And datoen <= HL(3) Then ' Hvis det er i højsæsonnen
Dim antvaer As Integer = ALLhojlav(onid, "ALLOTMENT") 'Hent Allot - HØJ
retledig(1, datoen, onid, antvaer - 1)
Else
Dim antvaer As Integer = ALLhojlav(onid, "ALLOTLOW") 'Hent Allot - LAV
retledig(1, datoen, onid, antvaer - 1)
End If
End If
Next
End Sub
Function ledig(ByVal datoen, ByVal onid)
Dim datoen1 As Date = Year(datoen) & "/" & Month(datoen) & "/" & Day(datoen)
Dim strConnection As String
Dim strSQL, navn, hpris As String
Dim xx, id As Integer
Dim objConnection As OleDbConnection
Dim objCommand As OleDbCommand
Dim objDataReader As OleDbDataReader
strConnection = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=D:\\web\\xxxxx.mdb"
objConnection = New OleDbConnection(strConnection)
objConnection.Open()
strSQL = "SELECT * FROM ledige WHERE onid = " & onid & " AND dato=#" & datoen1 & "#"
'Response.Write(strSQL & " ledig <br>")
objCommand = New OleDbCommand
objCommand.Connection = objConnection
objCommand = New OleDbCommand(strSQL, objConnection)
objDataReader = objCommand.ExecuteReader(CommandBehavior.CloseConnection)
If objDataReader.HasRows Then
While objDataReader.Read()
Return objDataReader("allotment")
End While
Else
Return -1
End If
objDataReader.Close()
objConnection.Close()
End Function
Function hojlav(ByVal aid)
Dim Sstart, Sslut, Hstart, Hslut As Date
Dim strConnection As String
Dim strSQL As String
'Dim datoer As Array
Dim datoer(4) As Date
'Dim xx, id As Integer
Dim objConnection As OleDbConnection
Dim objCommand As OleDbCommand
Dim objDataReader As OleDbDataReader
strConnection = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=D:\\web\\xxxxx.mdb"
objConnection = New OleDbConnection(strConnection)
objConnection.Open()
strSQL = "SELECT * FROM attraktioner WHERE aid = " & aid 'Request.QueryString("aid")
'Label1.Text = strSQL
objCommand = New OleDbCommand
objCommand.Connection = objConnection
objCommand = New OleDbCommand(strSQL, objConnection)
objDataReader = objCommand.ExecuteReader(CommandBehavior.CloseConnection)
While objDataReader.Read()
Sstart = objDataReader("Sstart")
Sslut = objDataReader("Sslut")
Hstart = objDataReader("Hstart")
Hslut = objDataReader("Hslut")
datoer.SetValue(Sstart, 0)
datoer.SetValue(Sslut, 1)
datoer.SetValue(Hstart, 2)
datoer.SetValue(Hslut, 3)
Return datoer
End While
objDataReader.Close()
objConnection.Close()
End Function
Function ALLhojlav(ByVal onid, ByVal KOLONNE)
Dim strConnection As String
Dim strSQL As String
Dim ALLOT(2) As Integer
Dim objConnection As OleDbConnection
Dim objCommand As OleDbCommand
Dim objDataReader As OleDbDataReader
strConnection = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=D:\\web\\xxxxx.mdb"
objConnection = New OleDbConnection(strConnection)
objConnection.Open()
strSQL = "SELECT " & KOLONNE & " AS ALLOT FROM overnatning WHERE onid = " & onid
'Response.Write(strSQL & " ALLhojlav<br>")
objCommand = New OleDbCommand
objCommand.Connection = objConnection
objCommand = New OleDbCommand(strSQL, objConnection)
objDataReader = objCommand.ExecuteReader(CommandBehavior.CloseConnection)
While objDataReader.Read()
Return objDataReader("ALLOT")
End While
objDataReader.Close()
objConnection.Close()
End Function
Sub retledig(ByVal ny, ByVal dato, ByVal onid, ByVal antal)
Dim strConnectionX As String = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=D:\\web\\xxxxx.mdb"
Dim strSQLX As String
Dim objConnectionX As OleDbConnection
Dim objCommandX As OleDbCommand
objConnectionX = New OleDbConnection(strConnectionX)
objConnectionX.Open()
objCommandX = New OleDbCommand
objCommandX.Connection = objConnectionX
If ny = 1 Then
objCommandX.CommandText = "INSERT INTO ledige (onid,dato,allotment) VALUES (" & onid & ",#" & dato & "#," & antal & ")"
Else
objCommandX.CommandText = "UPDATE ledige set allotment=" & antal & " WHERE onid=" & onid & " AND dato=#" & dato & "#"
End If
Response.Write(objCommandX.CommandText & " retledig<br>")
'objCommandX.ExecuteNonQuery()
objConnectionX.Close()
End Sub
Sub opdaterkunde(ByVal kid)
Dim strConnectionW As String = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=D:\\web\\xxxxx.mdb"
Dim strSQLW As String
Dim objConnectionW As OleDbConnection
Dim objCommandW As OleDbCommand
objConnectionW = New OleDbConnection(strConnectionW)
objConnectionW.Open()
objCommandW = New OleDbCommand
objCommandW.Connection = objConnectionW
Dim transact As Integer
If Request.QueryString("transact") <> "" Then
transact = Request.QueryString("transact")
Else
transact = 0
End If
Dim amount As Integer
If Request.QueryString("amount") <> "" Then
transact = Request.QueryString("amount")
Else
amount = 0
End If
objCommandW.CommandText = "UPDATE kunder set transid=" & transact & ",dato=#" & Now() & "#, pris=" & amount & " WHERE ordreid=" & kid & ""
objCommandW.ExecuteNonQuery()
objConnectionW.Close()
End Sub
Sub sendmailUdsolgt(ByVal onid, ByVal datoen)
Dim newMessage As New System.Web.Mail.MailMessage
Dim objSmtpServer As System.Web.Mail.SmtpMail
Dim strEmail, strBesked, Body As String
newMessage.BodyFormat = MailFormat.Html
newMessage.From = "xxx@xxxxxx.dk"
newMessage.To = "xx@xxxxx.dk"
newMessage.Subject = "Udsolgt"
newMessage.Body = onEmail(onid)
objSmtpServer.SmtpServer = "xxxxx.xxxxxx.dk"
objSmtpServer.Send(newMessage)
End Sub
Sub sendmail(ByVal email, ByVal subject, ByVal msg)
Dim newMessage As New System.Web.Mail.MailMessage
Dim objSmtpServer As System.Web.Mail.SmtpMail
Dim strEmail, strBesked, Body As String
newMessage.BodyFormat = MailFormat.Html
newMessage.From = "xxx@xxxxxx.dk"
newMessage.To = email
newMessage.Subject = subject
newMessage.Body = msg
objSmtpServer.SmtpServer = "xxxxxx.xxxxx.dk"
objSmtpServer.Send(newMessage)
End Sub
Protected Overrides Sub Render(ByVal writer As HtmlTextWriter)
Dim vMessage As String = "message goes here"
'*** Render the page and retrieve into StringBuilder
Dim sb As New StringBuilder
Dim hWriter As New HtmlTextWriter(New StringWriter(sb))
MyBase.Render(hWriter)
' *** store to a string
Dim PageResult As String = sb.ToString()
Dim At As Integer = PageResult.ToLower().LastIndexOf("")
'If At > -1 Then
'PageResult = PageResult.Insert(At, vMessage)
'End If
' PageResult = PageResult.Replace("",vMessage);
' *** Write it back to the server
'sendmail(kundemail(Request.QueryString("orderId")), "Kvittering", PageResult)
Dim sw As StreamWriter
sw = File.CreateText(Server.MapPath("kvitteringer/" & Request.QueryString("orderId") & "temp.html"))
sw.WriteLine(PageResult)
sw.Close()
'Response.WriteFile(Server.MapPath("kvitteringer/" & Request.QueryString("orderId") & "temp.html"))
'Response.Redirect(Request.QueryString("orderId") & "temp.html") 'Server.MapPath()
writer.Write(PageResult & "<br><br>Kvittering er sendt til din e-mail adresse")
Return
End Sub
End Class
