D
dirk van waes
Hello everyone,
Being complete newbie in asp.net I am trying to make an example which works
with a very simple database.
First I made my project in VS- vb.net, draging an oledbconnection and an
oledbdataadapter from the toolbox into my form.
Everything worked fine on my local computer. I was able to search, update,
delete and insert into my klanten.mdb database.
When I tried then to upload my project to a hostserver, nothing worked
anymore! After some debugging, I found the reason why: the connectionstring
was
still using the original path, leading to a map on my local computer. I
didn't
find a possibility to change that 'static' path in a more 'dynamic' one,
until I deleted the oledbconnection and the oledbdataadapter, and made them
again without the toolbox, i.o.w. I coded the 2 objects in the
default.aspx.vb. file
The connection seems to be fine now.( using server.mappath in the
connectionstring.)
Now I can search a record without difficulties. However, my update, insert
and deletebuttons work only on my local computer,NOT on the hostserver.
So,my questions are: 1)What am I doing wrong (the error-message is to
cryptical for me!), and 2)isn't it possible to make a dynamic path via the
oledbconnection in the toolbar? (maybe via a key in the web.config?)
Thanks in advance!
Dirk
The error-message I get is :
No value given for one or more required parameters.
Description: An unhandled exception occurred during the execution of the
current web request. Please review the stack trace for more information
about the error and where it originated in the code.
Exception Details: System.Data.OleDb.OleDbException: No value given for one
or more required parameters.
Source Error:
An unhandled exception was generated during the execution of the
current web request. Information regarding the origin and location of the
exception can be identified using the exception stack trace below.
Stack Trace:
[OleDbException (0x80040e10): No value given for one or more required
parameters.]
System.Data.Common.DbDataAdapter.Update(DataRow[] dataRows,
DataTableMapping tableMapping) +1662
System.Data.Common.DbDataAdapter.Update(DataSet dataSet, String srcTable)
+152
nieuwdatabase.WebForm1.Button2_Click(Object sender, EventArgs e) in
C:\Inetpub\wwwroot\nieuwdatabase\default.aspx.vb:146 PATH ON MY LOCAL
COMPUTER!!!!
System.Web.UI.WebControls.Button.OnClick(EventArgs e) +108
System.Web.UI.WebControls.Button.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String
eventArgument) +57
System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler
sourceControl, String eventArgument) +18
System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData) +33
System.Web.UI.Page.ProcessRequestMain() +1292
My code in the default.aspx.vb file is as follows:
Imports System
Imports System.Data
Imports System.Data.OleDb
Imports Microsoft.VisualBasic
Public Class WebForm1
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 lblboodschap As System.Web.UI.WebControls.Label
Protected WithEvents Button5 As System.Web.UI.WebControls.Button
Protected WithEvents Button4 As System.Web.UI.WebControls.Button
Protected WithEvents Button3 As System.Web.UI.WebControls.Button
Protected WithEvents Button2 As System.Web.UI.WebControls.Button
Protected WithEvents Button1 As System.Web.UI.WebControls.Button
Protected WithEvents Label5 As System.Web.UI.WebControls.Label
Protected WithEvents Label4 As System.Web.UI.WebControls.Label
Protected WithEvents Label3 As System.Web.UI.WebControls.Label
Protected WithEvents Label2 As System.Web.UI.WebControls.Label
Protected WithEvents Label1 As System.Web.UI.WebControls.Label
Protected WithEvents txtnummer As System.Web.UI.WebControls.TextBox
Protected WithEvents txtnaam As System.Web.UI.WebControls.TextBox
Protected WithEvents txtadres As System.Web.UI.WebControls.TextBox
Protected WithEvents txtwoonplaats As System.Web.UI.WebControls.TextBox
Protected WithEvents txtpostcode As System.Web.UI.WebControls.TextBox
'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
Dim conklanten As OleDbConnection
Dim dadklanten As New OleDbDataAdapter
Dim selklanten As IDbCommand
Dim upklanten As IDbCommand
Dim delklanten As IDbCommand
Dim inklanten As IDbCommand
Dim dasklanten As New DataSet
Private Sub Page_Load(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles MyBase.Load
Dim strcon As String
strcon = "PROVIDER=Microsoft.jet.OLEDB.4.0;DATA SOURCE =" &
Server.MapPath("klanten.mdb") & ";"
conklanten = New OleDbConnection(strcon)
selklanten = conklanten.CreateCommand
selklanten.Connection = conklanten
upklanten = conklanten.CreateCommand
upklanten.Connection = conklanten
delklanten = conklanten.CreateCommand
delklanten.Connection = conklanten
inklanten = conklanten.CreateCommand
inklanten.Connection = conklanten
selklanten.CommandText = "Select * from tblklanten" '
selklanten.CommandType = CommandType.Text
dadklanten.SelectCommand = selklanten
upklanten.CommandText = "update tblklanten set adres = ?, naam = ?, nummer =
?, postcode = ?, woonplaats = ? where (nummer =?) and (adres = ? or ? is
Null and adres is null) and (naam =? or ? is null and naam is null) and
(postcode = ? or ? is null and postcode is null) and (woonplaats =? or ? is
null and woonplaats is null)"
upklanten.Parameters.Add(New System.Data.OleDb.OleDbParameter("adres",
System.Data.OleDb.OleDbType.VarWChar, 50, "adres"))
upklanten.Parameters.Add(New System.Data.OleDb.OleDbParameter("naam",
System.Data.OleDb.OleDbType.VarWChar, 50, "naam"))
upklanten.Parameters.Add(New System.Data.OleDb.OleDbParameter("nummer",
System.Data.OleDb.OleDbType.Integer, 0, "nummer"))
upklanten.Parameters.Add(New System.Data.OleDb.OleDbParameter("postcode",
System.Data.OleDb.OleDbType.VarWChar, 50, "postcode"))
upklanten.Parameters.Add(New System.Data.OleDb.OleDbParameter("woonplaats",
System.Data.OleDb.OleDbType.VarWChar, 50, "woonplaats"))
upklanten.Parameters.Add(New
System.Data.OleDb.OleDbParameter("Original_nummer",
System.Data.OleDb.OleDbType.Integer, 0,
System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0, Byte),
"nummer", System.Data.DataRowVersion.Original, Nothing))
upklanten.Parameters.Add(New
System.Data.OleDb.OleDbParameter("Original_adres",
System.Data.OleDb.OleDbType.VarWChar, 50,
System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0, Byte),
"adres", System.Data.DataRowVersion.Original, Nothing))
upklanten.Parameters.Add(New
System.Data.OleDb.OleDbParameter("Original_adres1",
System.Data.OleDb.OleDbType.VarWChar, 50,
System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0, Byte),
"adres", System.Data.DataRowVersion.Original, Nothing))
upklanten.Parameters.Add(New
System.Data.OleDb.OleDbParameter("Original_naam",
System.Data.OleDb.OleDbType.VarWChar, 50,
System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0, Byte),
"naam", System.Data.DataRowVersion.Original, Nothing))
upklanten.Parameters.Add(New
System.Data.OleDb.OleDbParameter("Original_naam1",
System.Data.OleDb.OleDbType.VarWChar, 50,
System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0, Byte),
"naam", System.Data.DataRowVersion.Original, Nothing))
upklanten.Parameters.Add(New
System.Data.OleDb.OleDbParameter("Original_postcode",
System.Data.OleDb.OleDbType.VarWChar, 50,
System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0, Byte),
"postcode", System.Data.DataRowVersion.Original, Nothing))
upklanten.Parameters.Add(New
System.Data.OleDb.OleDbParameter("Original_postcode1",
System.Data.OleDb.OleDbType.VarWChar, 50,
System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0, Byte),
"postcode", System.Data.DataRowVersion.Original, Nothing))
upklanten.Parameters.Add(New
System.Data.OleDb.OleDbParameter("Original_woonplaats",
System.Data.OleDb.OleDbType.VarWChar, 50,
System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0, Byte),
"woonplaats", System.Data.DataRowVersion.Original, Nothing))
upklanten.Parameters.Add(New
System.Data.OleDb.OleDbParameter("Original_woonplaats1",
System.Data.OleDb.OleDbType.VarWChar, 50,
System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0, Byte),
"woonplaats", System.Data.DataRowVersion.Original, Nothing))
upklanten.CommandType = CommandType.Text
dadklanten.UpdateCommand = upklanten
delklanten.CommandText = "Delete from tblklanten where (nummer =?)"
delklanten.CommandType = CommandType.Text
delklanten.Parameters.Add(New System.Data.OleDb.OleDbParameter("nummer",
System.Data.OleDb.OleDbType.Integer, 0, "nummer"))
dadklanten.DeleteCommand = delklanten
inklanten.CommandText = "insert into tblklanten(adres, naam, nummer,
postcode, woonplaats) values (?, ?, ?, ?, ?)"
inklanten.CommandType = CommandType.Text
inklanten.Connection = Me.conklanten
inklanten.Parameters.Add(New System.Data.OleDb.OleDbParameter("adres",
System.Data.OleDb.OleDbType.VarWChar, 50, "adres"))
inklanten.Parameters.Add(New System.Data.OleDb.OleDbParameter("naam",
System.Data.OleDb.OleDbType.VarWChar, 50, "naam"))
inklanten.Parameters.Add(New System.Data.OleDb.OleDbParameter("nummer",
System.Data.OleDb.OleDbType.Integer, 0, "nummer"))
inklanten.Parameters.Add(New System.Data.OleDb.OleDbParameter("postcode",
System.Data.OleDb.OleDbType.VarWChar, 50, "postcode"))
inklanten.Parameters.Add(New System.Data.OleDb.OleDbParameter("woonplaats",
System.Data.OleDb.OleDbType.VarWChar, 50, "woonplaats"))
dadklanten.InsertCommand = inklanten
dadklanten.Fill(dasklanten, "tblklanten")
End Sub
Protected Overrides Sub finalize()
dadklanten.Dispose()
MyBase.Finalize()
End Sub
Private Sub Button5_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles Button5.Click
subclear(True)
End Sub
Private Sub subclear(ByVal clearnummer As Boolean)
If clearnummer Then txtnummer.Text = ""
txtnaam.Text = ""
txtadres.Text = ""
txtpostcode.Text = ""
txtwoonplaats.Text = ""
lblboodschap.Text = ""
End Sub
Private Function funcontroleoke() As Boolean
If IsNumeric(txtnummer.Text) AndAlso txtnaam.Text.Trim <> "" AndAlso
txtadres.Text.Trim <> "" AndAlso txtpostcode.Text.Trim <> "" AndAlso
txtwoonplaats.Text.Trim <> "" Then
Return True
End If
End Function
Private Function funzoekrij() As Long
funzoekrij = -1
If IsNumeric(txtnummer.Text) Then
Dim wrij As Long
For wrij = 0 To dasklanten.Tables(0).Rows.Count - 1
If dasklanten.Tables(0).Rows(wrij)("nummer") = CLng(txtnummer.Text)
Then
funzoekrij = wrij
End If
Next
End If
End Function
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles Button1.Click ' =search
Dim wrij As Long = funzoekrij()
subclear(False)
If wrij < 0 Then
lblboodschap.Text = "Niet gevonden"
Else
With dasklanten.Tables(0)
txtnaam.Text = .Rows(wrij)("naam").ToString
txtadres.Text = .Rows(wrij)("adres").ToString
txtpostcode.Text = .Rows(wrij)("postcode").ToString
txtwoonplaats.Text = .Rows(wrij)("woonplaats").ToString
End With
End If
End Sub
Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles Button2.Click 'update
Dim wrij As Long = funzoekrij()
If wrij < 0 Then
lblboodschap.Text = "Niet gevonden"
Else
If Not funcontroleoke() Then
lblboodschap.Text = "Onvolledig"
Exit Sub
End If
With dasklanten.Tables(0)
.Rows(wrij)("naam") = txtnaam.Text
.Rows(wrij)("adres") = txtadres.Text
.Rows(wrij)("postcode") = txtpostcode.Text
.. Rows(wrij)("woonplaats") = txtwoonplaats.Text
End With
dadklanten.Update(dasklanten, "tblklanten")
subclear(True)
lblboodschap.Text = "Wijzigingen doorgevoerd"
End If
End Sub
Private Sub Button3_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles Button3.Click
Dim wrij As Long = funzoekrij()
If wrij >= 0 Then
lblboodschap.Text = "Nummer Bestaat al"
Else
If Not funcontroleoke() Then
lblboodschap.Text = "Onvolledig"
Exit Sub
End If
Dim newrij As DataRow = dasklanten.Tables("tblklanten").NewRow
newrij("nummer") = txtnummer.Text
newrij("naam") = txtnaam.Text
newrij("adres") = txtadres.Text
newrij("woonplaats") = txtwoonplaats.Text
newrij("postcode") = txtpostcode.Text
dasklanten.Tables(0).Rows.Add(newrij)
dadklanten.Update(dasklanten, "tblklanten")
subclear(True)
lblboodschap.Text = "gegevens ingevoerd"
End If
End Sub
Private Sub Button4_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles Button4.Click 'delete
Dim wrij As Long = funzoekrij()
If wrij < 0 Then
lblboodschap.Text = "Record met dit nr bestaat niet"
Else
dasklanten.Tables("tblklanten").Rows(wrij).Delete()
dadklanten.Update(dasklanten, "tblklanten")
subclear(True)
lblboodschap.Text = "gegevens verwijderd"
End If
End Sub
End Class
Hello everyone,
Being complete newbie in asp.net I am trying to make an example which works
with a very simple database.
First I made my project in VS- vb.net, draging an oledbconnection and an
oledbdataadapter from the toolbox into my form.
Everything worked fine on my local computer. I was able to search, update,
delete and insert into my klanten.mdb database.
When I tried then to upload my project to a hostserver, nothing worked
anymore! After some debugging, I found the reason: the connectionstring was
still using the original path, leading to a map on my local computer. I
didn't
find a possibility to change that 'static' path in a more 'dynamic' one,
until I deleted the oledbconnection and the oledbdataadapter, and made them
again without the toolbox, i.o.w. I coded the 2 objects in the
default.aspx.vb. file
The connection seems to be fine now.( using server.mappath in the
connectionstring.)
Now I can search a record without difficulties. However, my update, insert
and deletebuttons work only on my local computer,NOT on the hostserver.
So,my questions are: 1)What am I doing wrong (the error-message is to
cryptical for me!), and 2)isn't it possible to make a dynamic path via the
oledbconnection in the toolbar? (maybe via a key in the web.config?)
Thanks in advance!
Dirk
The error-message I get is :
No value given for one or more required parameters.
Description: An unhandled exception occurred during the execution of the
current web request. Please review the stack trace for more information
about the error and where it originated in the code.
Exception Details: System.Data.OleDb.OleDbException: No value given for one
or more required parameters.
Source Error:
An unhandled exception was generated during the execution of the
current web request. Information regarding the origin and location of the
exception can be identified using the exception stack trace below.
Stack Trace:
[OleDbException (0x80040e10): No value given for one or more required
parameters.]
System.Data.Common.DbDataAdapter.Update(DataRow[] dataRows,
DataTableMapping tableMapping) +1662
System.Data.Common.DbDataAdapter.Update(DataSet dataSet, String srcTable)
+152
nieuwdatabase.WebForm1.Button2_Click(Object sender, EventArgs e) in
C:\Inetpub\wwwroot\nieuwdatabase\default.aspx.vb:146
System.Web.UI.WebControls.Button.OnClick(EventArgs e) +108
System.Web.UI.WebControls.Button.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String
eventArgument) +57
System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler
sourceControl, String eventArgument) +18
System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData) +33
System.Web.UI.Page.ProcessRequestMain() +1292
My code in the default.aspx.vb file is as follows:
Imports System
Imports System.Data
Imports System.Data.OleDb
Imports Microsoft.VisualBasic
Public Class WebForm1
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 lblboodschap As System.Web.UI.WebControls.Label
Protected WithEvents Button5 As System.Web.UI.WebControls.Button
Protected WithEvents Button4 As System.Web.UI.WebControls.Button
Protected WithEvents Button3 As System.Web.UI.WebControls.Button
Protected WithEvents Button2 As System.Web.UI.WebControls.Button
Protected WithEvents Button1 As System.Web.UI.WebControls.Button
Protected WithEvents Label5 As System.Web.UI.WebControls.Label
Protected WithEvents Label4 As System.Web.UI.WebControls.Label
Protected WithEvents Label3 As System.Web.UI.WebControls.Label
Protected WithEvents Label2 As System.Web.UI.WebControls.Label
Protected WithEvents Label1 As System.Web.UI.WebControls.Label
Protected WithEvents txtnummer As System.Web.UI.WebControls.TextBox
Protected WithEvents txtnaam As System.Web.UI.WebControls.TextBox
Protected WithEvents txtadres As System.Web.UI.WebControls.TextBox
Protected WithEvents txtwoonplaats As System.Web.UI.WebControls.TextBox
Protected WithEvents txtpostcode As System.Web.UI.WebControls.TextBox
'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
Dim conklanten As OleDbConnection
Dim dadklanten As New OleDbDataAdapter
Dim selklanten As IDbCommand
Dim upklanten As IDbCommand
Dim delklanten As IDbCommand
Dim inklanten As IDbCommand
Dim dasklanten As New DataSet
Private Sub Page_Load(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles MyBase.Load
Dim strcon As String
strcon = "PROVIDER=Microsoft.jet.OLEDB.4.0;DATA SOURCE =" &
Server.MapPath("klanten.mdb") & ";"
conklanten = New OleDbConnection(strcon)
selklanten = conklanten.CreateCommand
selklanten.Connection = conklanten
upklanten = conklanten.CreateCommand
upklanten.Connection = conklanten
delklanten = conklanten.CreateCommand
delklanten.Connection = conklanten
inklanten = conklanten.CreateCommand
inklanten.Connection = conklanten
selklanten.CommandText = "Select * from tblklanten" '
selklanten.CommandType = CommandType.Text
dadklanten.SelectCommand = selklanten
upklanten.CommandText = "update tblklanten set adres = ?, naam = ?, nummer =
?, postcode = ?, woonplaats = ? where (nummer =?) and (adres = ? or ? is
Null and adres is null) and (naam =? or ? is null and naam is null) and
(postcode = ? or ? is null and postcode is null) and (woonplaats =? or ? is
null and woonplaats is null)"
upklanten.Parameters.Add(New System.Data.OleDb.OleDbParameter("adres",
System.Data.OleDb.OleDbType.VarWChar, 50, "adres"))
upklanten.Parameters.Add(New System.Data.OleDb.OleDbParameter("naam",
System.Data.OleDb.OleDbType.VarWChar, 50, "naam"))
upklanten.Parameters.Add(New System.Data.OleDb.OleDbParameter("nummer",
System.Data.OleDb.OleDbType.Integer, 0, "nummer"))
upklanten.Parameters.Add(New System.Data.OleDb.OleDbParameter("postcode",
System.Data.OleDb.OleDbType.VarWChar, 50, "postcode"))
upklanten.Parameters.Add(New System.Data.OleDb.OleDbParameter("woonplaats",
System.Data.OleDb.OleDbType.VarWChar, 50, "woonplaats"))
upklanten.Parameters.Add(New
System.Data.OleDb.OleDbParameter("Original_nummer",
System.Data.OleDb.OleDbType.Integer, 0,
System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0, Byte),
"nummer", System.Data.DataRowVersion.Original, Nothing))
upklanten.Parameters.Add(New
System.Data.OleDb.OleDbParameter("Original_adres",
System.Data.OleDb.OleDbType.VarWChar, 50,
System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0, Byte),
"adres", System.Data.DataRowVersion.Original, Nothing))
upklanten.Parameters.Add(New
System.Data.OleDb.OleDbParameter("Original_adres1",
System.Data.OleDb.OleDbType.VarWChar, 50,
System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0, Byte),
"adres", System.Data.DataRowVersion.Original, Nothing))
upklanten.Parameters.Add(New
System.Data.OleDb.OleDbParameter("Original_naam",
System.Data.OleDb.OleDbType.VarWChar, 50,
System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0, Byte),
"naam", System.Data.DataRowVersion.Original, Nothing))
upklanten.Parameters.Add(New
System.Data.OleDb.OleDbParameter("Original_naam1",
System.Data.OleDb.OleDbType.VarWChar, 50,
System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0, Byte),
"naam", System.Data.DataRowVersion.Original, Nothing))
upklanten.Parameters.Add(New
System.Data.OleDb.OleDbParameter("Original_postcode",
System.Data.OleDb.OleDbType.VarWChar, 50,
System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0, Byte),
"postcode", System.Data.DataRowVersion.Original, Nothing))
upklanten.Parameters.Add(New
System.Data.OleDb.OleDbParameter("Original_postcode1",
System.Data.OleDb.OleDbType.VarWChar, 50,
System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0, Byte),
"postcode", System.Data.DataRowVersion.Original, Nothing))
upklanten.Parameters.Add(New
System.Data.OleDb.OleDbParameter("Original_woonplaats",
System.Data.OleDb.OleDbType.VarWChar, 50,
System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0, Byte),
"woonplaats", System.Data.DataRowVersion.Original, Nothing))
upklanten.Parameters.Add(New
System.Data.OleDb.OleDbParameter("Original_woonplaats1",
System.Data.OleDb.OleDbType.VarWChar, 50,
System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0, Byte),
"woonplaats", System.Data.DataRowVersion.Original, Nothing))
upklanten.CommandType = CommandType.Text
dadklanten.UpdateCommand = upklanten
delklanten.CommandText = "Delete from tblklanten where (nummer =?)"
delklanten.CommandType = CommandType.Text
delklanten.Parameters.Add(New System.Data.OleDb.OleDbParameter("nummer",
System.Data.OleDb.OleDbType.Integer, 0, "nummer"))
dadklanten.DeleteCommand = delklanten
inklanten.CommandText = "insert into tblklanten(adres, naam, nummer,
postcode, woonplaats) values (?, ?, ?, ?, ?)"
inklanten.CommandType = CommandType.Text
inklanten.Connection = Me.conklanten
inklanten.Parameters.Add(New System.Data.OleDb.OleDbParameter("adres",
System.Data.OleDb.OleDbType.VarWChar, 50, "adres"))
inklanten.Parameters.Add(New System.Data.OleDb.OleDbParameter("naam",
System.Data.OleDb.OleDbType.VarWChar, 50, "naam"))
inklanten.Parameters.Add(New System.Data.OleDb.OleDbParameter("nummer",
System.Data.OleDb.OleDbType.Integer, 0, "nummer"))
inklanten.Parameters.Add(New System.Data.OleDb.OleDbParameter("postcode",
System.Data.OleDb.OleDbType.VarWChar, 50, "postcode"))
inklanten.Parameters.Add(New System.Data.OleDb.OleDbParameter("woonplaats",
System.Data.OleDb.OleDbType.VarWChar, 50, "woonplaats"))
dadklanten.InsertCommand = inklanten
dadklanten.Fill(dasklanten, "tblklanten")
End Sub
Protected Overrides Sub finalize()
dadklanten.Dispose()
MyBase.Finalize()
End Sub
Private Sub Button5_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles Button5.Click
subclear(True)
End Sub
Private Sub subclear(ByVal clearnummer As Boolean)
If clearnummer Then txtnummer.Text = ""
txtnaam.Text = ""
txtadres.Text = ""
txtpostcode.Text = ""
txtwoonplaats.Text = ""
lblboodschap.Text = ""
End Sub
Private Function funcontroleoke() As Boolean
If IsNumeric(txtnummer.Text) AndAlso txtnaam.Text.Trim <> "" AndAlso
txtadres.Text.Trim <> "" AndAlso txtpostcode.Text.Trim <> "" AndAlso
txtwoonplaats.Text.Trim <> "" Then
Return True
End If
End Function
Private Function funzoekrij() As Long
funzoekrij = -1
If IsNumeric(txtnummer.Text) Then
Dim wrij As Long
For wrij = 0 To dasklanten.Tables(0).Rows.Count - 1
If dasklanten.Tables(0).Rows(wrij)("nummer") = CLng(txtnummer.Text)
Then
funzoekrij = wrij
End If
Next
End If
End Function
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles Button1.Click ' =search
Dim wrij As Long = funzoekrij()
subclear(False)
If wrij < 0 Then
lblboodschap.Text = "Niet gevonden"
Else
With dasklanten.Tables(0)
txtnaam.Text = .Rows(wrij)("naam").ToString
txtadres.Text = .Rows(wrij)("adres").ToString
txtpostcode.Text = .Rows(wrij)("postcode").ToString
txtwoonplaats.Text = .Rows(wrij)("woonplaats").ToString
End With
End If
End Sub
Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles Button2.Click 'update
Dim wrij As Long = funzoekrij()
If wrij < 0 Then
lblboodschap.Text = "Niet gevonden"
Else
If Not funcontroleoke() Then
lblboodschap.Text = "Onvolledig"
Exit Sub
End If
With dasklanten.Tables(0)
.Rows(wrij)("naam") = txtnaam.Text
.Rows(wrij)("adres") = txtadres.Text
.Rows(wrij)("postcode") = txtpostcode.Text
.. Rows(wrij)("woonplaats") = txtwoonplaats.Text
End With
dadklanten.Update(dasklanten, "tblklanten")
subclear(True)
lblboodschap.Text = "Wijzigingen doorgevoerd"
End If
End Sub
Private Sub Button3_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles Button3.Click
Dim wrij As Long = funzoekrij()
If wrij >= 0 Then
lblboodschap.Text = "Nummer Bestaat al"
Else
If Not funcontroleoke() Then
lblboodschap.Text = "Onvolledig"
Exit Sub
End If
Dim newrij As DataRow = dasklanten.Tables("tblklanten").NewRow
newrij("nummer") = txtnummer.Text
newrij("naam") = txtnaam.Text
newrij("adres") = txtadres.Text
newrij("woonplaats") = txtwoonplaats.Text
newrij("postcode") = txtpostcode.Text
dasklanten.Tables(0).Rows.Add(newrij)
dadklanten.Update(dasklanten, "tblklanten")
subclear(True)
lblboodschap.Text = "gegevens ingevoerd"
End If
End Sub
Private Sub Button4_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles Button4.Click 'delete
Dim wrij As Long = funzoekrij()
If wrij < 0 Then
lblboodschap.Text = "Record met dit nr bestaat niet"
Else
dasklanten.Tables("tblklanten").Rows(wrij).Delete()
dadklanten.Update(dasklanten, "tblklanten")
subclear(True)
lblboodschap.Text = "gegevens verwijderd"
End If
End Sub
End Class
Being complete newbie in asp.net I am trying to make an example which works
with a very simple database.
First I made my project in VS- vb.net, draging an oledbconnection and an
oledbdataadapter from the toolbox into my form.
Everything worked fine on my local computer. I was able to search, update,
delete and insert into my klanten.mdb database.
When I tried then to upload my project to a hostserver, nothing worked
anymore! After some debugging, I found the reason why: the connectionstring
was
still using the original path, leading to a map on my local computer. I
didn't
find a possibility to change that 'static' path in a more 'dynamic' one,
until I deleted the oledbconnection and the oledbdataadapter, and made them
again without the toolbox, i.o.w. I coded the 2 objects in the
default.aspx.vb. file
The connection seems to be fine now.( using server.mappath in the
connectionstring.)
Now I can search a record without difficulties. However, my update, insert
and deletebuttons work only on my local computer,NOT on the hostserver.
So,my questions are: 1)What am I doing wrong (the error-message is to
cryptical for me!), and 2)isn't it possible to make a dynamic path via the
oledbconnection in the toolbar? (maybe via a key in the web.config?)
Thanks in advance!
Dirk
The error-message I get is :
No value given for one or more required parameters.
Description: An unhandled exception occurred during the execution of the
current web request. Please review the stack trace for more information
about the error and where it originated in the code.
Exception Details: System.Data.OleDb.OleDbException: No value given for one
or more required parameters.
Source Error:
An unhandled exception was generated during the execution of the
current web request. Information regarding the origin and location of the
exception can be identified using the exception stack trace below.
Stack Trace:
[OleDbException (0x80040e10): No value given for one or more required
parameters.]
System.Data.Common.DbDataAdapter.Update(DataRow[] dataRows,
DataTableMapping tableMapping) +1662
System.Data.Common.DbDataAdapter.Update(DataSet dataSet, String srcTable)
+152
nieuwdatabase.WebForm1.Button2_Click(Object sender, EventArgs e) in
C:\Inetpub\wwwroot\nieuwdatabase\default.aspx.vb:146 PATH ON MY LOCAL
COMPUTER!!!!
System.Web.UI.WebControls.Button.OnClick(EventArgs e) +108
System.Web.UI.WebControls.Button.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String
eventArgument) +57
System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler
sourceControl, String eventArgument) +18
System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData) +33
System.Web.UI.Page.ProcessRequestMain() +1292
My code in the default.aspx.vb file is as follows:
Imports System
Imports System.Data
Imports System.Data.OleDb
Imports Microsoft.VisualBasic
Public Class WebForm1
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 lblboodschap As System.Web.UI.WebControls.Label
Protected WithEvents Button5 As System.Web.UI.WebControls.Button
Protected WithEvents Button4 As System.Web.UI.WebControls.Button
Protected WithEvents Button3 As System.Web.UI.WebControls.Button
Protected WithEvents Button2 As System.Web.UI.WebControls.Button
Protected WithEvents Button1 As System.Web.UI.WebControls.Button
Protected WithEvents Label5 As System.Web.UI.WebControls.Label
Protected WithEvents Label4 As System.Web.UI.WebControls.Label
Protected WithEvents Label3 As System.Web.UI.WebControls.Label
Protected WithEvents Label2 As System.Web.UI.WebControls.Label
Protected WithEvents Label1 As System.Web.UI.WebControls.Label
Protected WithEvents txtnummer As System.Web.UI.WebControls.TextBox
Protected WithEvents txtnaam As System.Web.UI.WebControls.TextBox
Protected WithEvents txtadres As System.Web.UI.WebControls.TextBox
Protected WithEvents txtwoonplaats As System.Web.UI.WebControls.TextBox
Protected WithEvents txtpostcode As System.Web.UI.WebControls.TextBox
'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
Dim conklanten As OleDbConnection
Dim dadklanten As New OleDbDataAdapter
Dim selklanten As IDbCommand
Dim upklanten As IDbCommand
Dim delklanten As IDbCommand
Dim inklanten As IDbCommand
Dim dasklanten As New DataSet
Private Sub Page_Load(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles MyBase.Load
Dim strcon As String
strcon = "PROVIDER=Microsoft.jet.OLEDB.4.0;DATA SOURCE =" &
Server.MapPath("klanten.mdb") & ";"
conklanten = New OleDbConnection(strcon)
selklanten = conklanten.CreateCommand
selklanten.Connection = conklanten
upklanten = conklanten.CreateCommand
upklanten.Connection = conklanten
delklanten = conklanten.CreateCommand
delklanten.Connection = conklanten
inklanten = conklanten.CreateCommand
inklanten.Connection = conklanten
selklanten.CommandText = "Select * from tblklanten" '
selklanten.CommandType = CommandType.Text
dadklanten.SelectCommand = selklanten
upklanten.CommandText = "update tblklanten set adres = ?, naam = ?, nummer =
?, postcode = ?, woonplaats = ? where (nummer =?) and (adres = ? or ? is
Null and adres is null) and (naam =? or ? is null and naam is null) and
(postcode = ? or ? is null and postcode is null) and (woonplaats =? or ? is
null and woonplaats is null)"
upklanten.Parameters.Add(New System.Data.OleDb.OleDbParameter("adres",
System.Data.OleDb.OleDbType.VarWChar, 50, "adres"))
upklanten.Parameters.Add(New System.Data.OleDb.OleDbParameter("naam",
System.Data.OleDb.OleDbType.VarWChar, 50, "naam"))
upklanten.Parameters.Add(New System.Data.OleDb.OleDbParameter("nummer",
System.Data.OleDb.OleDbType.Integer, 0, "nummer"))
upklanten.Parameters.Add(New System.Data.OleDb.OleDbParameter("postcode",
System.Data.OleDb.OleDbType.VarWChar, 50, "postcode"))
upklanten.Parameters.Add(New System.Data.OleDb.OleDbParameter("woonplaats",
System.Data.OleDb.OleDbType.VarWChar, 50, "woonplaats"))
upklanten.Parameters.Add(New
System.Data.OleDb.OleDbParameter("Original_nummer",
System.Data.OleDb.OleDbType.Integer, 0,
System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0, Byte),
"nummer", System.Data.DataRowVersion.Original, Nothing))
upklanten.Parameters.Add(New
System.Data.OleDb.OleDbParameter("Original_adres",
System.Data.OleDb.OleDbType.VarWChar, 50,
System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0, Byte),
"adres", System.Data.DataRowVersion.Original, Nothing))
upklanten.Parameters.Add(New
System.Data.OleDb.OleDbParameter("Original_adres1",
System.Data.OleDb.OleDbType.VarWChar, 50,
System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0, Byte),
"adres", System.Data.DataRowVersion.Original, Nothing))
upklanten.Parameters.Add(New
System.Data.OleDb.OleDbParameter("Original_naam",
System.Data.OleDb.OleDbType.VarWChar, 50,
System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0, Byte),
"naam", System.Data.DataRowVersion.Original, Nothing))
upklanten.Parameters.Add(New
System.Data.OleDb.OleDbParameter("Original_naam1",
System.Data.OleDb.OleDbType.VarWChar, 50,
System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0, Byte),
"naam", System.Data.DataRowVersion.Original, Nothing))
upklanten.Parameters.Add(New
System.Data.OleDb.OleDbParameter("Original_postcode",
System.Data.OleDb.OleDbType.VarWChar, 50,
System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0, Byte),
"postcode", System.Data.DataRowVersion.Original, Nothing))
upklanten.Parameters.Add(New
System.Data.OleDb.OleDbParameter("Original_postcode1",
System.Data.OleDb.OleDbType.VarWChar, 50,
System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0, Byte),
"postcode", System.Data.DataRowVersion.Original, Nothing))
upklanten.Parameters.Add(New
System.Data.OleDb.OleDbParameter("Original_woonplaats",
System.Data.OleDb.OleDbType.VarWChar, 50,
System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0, Byte),
"woonplaats", System.Data.DataRowVersion.Original, Nothing))
upklanten.Parameters.Add(New
System.Data.OleDb.OleDbParameter("Original_woonplaats1",
System.Data.OleDb.OleDbType.VarWChar, 50,
System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0, Byte),
"woonplaats", System.Data.DataRowVersion.Original, Nothing))
upklanten.CommandType = CommandType.Text
dadklanten.UpdateCommand = upklanten
delklanten.CommandText = "Delete from tblklanten where (nummer =?)"
delklanten.CommandType = CommandType.Text
delklanten.Parameters.Add(New System.Data.OleDb.OleDbParameter("nummer",
System.Data.OleDb.OleDbType.Integer, 0, "nummer"))
dadklanten.DeleteCommand = delklanten
inklanten.CommandText = "insert into tblklanten(adres, naam, nummer,
postcode, woonplaats) values (?, ?, ?, ?, ?)"
inklanten.CommandType = CommandType.Text
inklanten.Connection = Me.conklanten
inklanten.Parameters.Add(New System.Data.OleDb.OleDbParameter("adres",
System.Data.OleDb.OleDbType.VarWChar, 50, "adres"))
inklanten.Parameters.Add(New System.Data.OleDb.OleDbParameter("naam",
System.Data.OleDb.OleDbType.VarWChar, 50, "naam"))
inklanten.Parameters.Add(New System.Data.OleDb.OleDbParameter("nummer",
System.Data.OleDb.OleDbType.Integer, 0, "nummer"))
inklanten.Parameters.Add(New System.Data.OleDb.OleDbParameter("postcode",
System.Data.OleDb.OleDbType.VarWChar, 50, "postcode"))
inklanten.Parameters.Add(New System.Data.OleDb.OleDbParameter("woonplaats",
System.Data.OleDb.OleDbType.VarWChar, 50, "woonplaats"))
dadklanten.InsertCommand = inklanten
dadklanten.Fill(dasklanten, "tblklanten")
End Sub
Protected Overrides Sub finalize()
dadklanten.Dispose()
MyBase.Finalize()
End Sub
Private Sub Button5_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles Button5.Click
subclear(True)
End Sub
Private Sub subclear(ByVal clearnummer As Boolean)
If clearnummer Then txtnummer.Text = ""
txtnaam.Text = ""
txtadres.Text = ""
txtpostcode.Text = ""
txtwoonplaats.Text = ""
lblboodschap.Text = ""
End Sub
Private Function funcontroleoke() As Boolean
If IsNumeric(txtnummer.Text) AndAlso txtnaam.Text.Trim <> "" AndAlso
txtadres.Text.Trim <> "" AndAlso txtpostcode.Text.Trim <> "" AndAlso
txtwoonplaats.Text.Trim <> "" Then
Return True
End If
End Function
Private Function funzoekrij() As Long
funzoekrij = -1
If IsNumeric(txtnummer.Text) Then
Dim wrij As Long
For wrij = 0 To dasklanten.Tables(0).Rows.Count - 1
If dasklanten.Tables(0).Rows(wrij)("nummer") = CLng(txtnummer.Text)
Then
funzoekrij = wrij
End If
Next
End If
End Function
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles Button1.Click ' =search
Dim wrij As Long = funzoekrij()
subclear(False)
If wrij < 0 Then
lblboodschap.Text = "Niet gevonden"
Else
With dasklanten.Tables(0)
txtnaam.Text = .Rows(wrij)("naam").ToString
txtadres.Text = .Rows(wrij)("adres").ToString
txtpostcode.Text = .Rows(wrij)("postcode").ToString
txtwoonplaats.Text = .Rows(wrij)("woonplaats").ToString
End With
End If
End Sub
Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles Button2.Click 'update
Dim wrij As Long = funzoekrij()
If wrij < 0 Then
lblboodschap.Text = "Niet gevonden"
Else
If Not funcontroleoke() Then
lblboodschap.Text = "Onvolledig"
Exit Sub
End If
With dasklanten.Tables(0)
.Rows(wrij)("naam") = txtnaam.Text
.Rows(wrij)("adres") = txtadres.Text
.Rows(wrij)("postcode") = txtpostcode.Text
.. Rows(wrij)("woonplaats") = txtwoonplaats.Text
End With
dadklanten.Update(dasklanten, "tblklanten")
subclear(True)
lblboodschap.Text = "Wijzigingen doorgevoerd"
End If
End Sub
Private Sub Button3_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles Button3.Click
Dim wrij As Long = funzoekrij()
If wrij >= 0 Then
lblboodschap.Text = "Nummer Bestaat al"
Else
If Not funcontroleoke() Then
lblboodschap.Text = "Onvolledig"
Exit Sub
End If
Dim newrij As DataRow = dasklanten.Tables("tblklanten").NewRow
newrij("nummer") = txtnummer.Text
newrij("naam") = txtnaam.Text
newrij("adres") = txtadres.Text
newrij("woonplaats") = txtwoonplaats.Text
newrij("postcode") = txtpostcode.Text
dasklanten.Tables(0).Rows.Add(newrij)
dadklanten.Update(dasklanten, "tblklanten")
subclear(True)
lblboodschap.Text = "gegevens ingevoerd"
End If
End Sub
Private Sub Button4_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles Button4.Click 'delete
Dim wrij As Long = funzoekrij()
If wrij < 0 Then
lblboodschap.Text = "Record met dit nr bestaat niet"
Else
dasklanten.Tables("tblklanten").Rows(wrij).Delete()
dadklanten.Update(dasklanten, "tblklanten")
subclear(True)
lblboodschap.Text = "gegevens verwijderd"
End If
End Sub
End Class
Hello everyone,
Being complete newbie in asp.net I am trying to make an example which works
with a very simple database.
First I made my project in VS- vb.net, draging an oledbconnection and an
oledbdataadapter from the toolbox into my form.
Everything worked fine on my local computer. I was able to search, update,
delete and insert into my klanten.mdb database.
When I tried then to upload my project to a hostserver, nothing worked
anymore! After some debugging, I found the reason: the connectionstring was
still using the original path, leading to a map on my local computer. I
didn't
find a possibility to change that 'static' path in a more 'dynamic' one,
until I deleted the oledbconnection and the oledbdataadapter, and made them
again without the toolbox, i.o.w. I coded the 2 objects in the
default.aspx.vb. file
The connection seems to be fine now.( using server.mappath in the
connectionstring.)
Now I can search a record without difficulties. However, my update, insert
and deletebuttons work only on my local computer,NOT on the hostserver.
So,my questions are: 1)What am I doing wrong (the error-message is to
cryptical for me!), and 2)isn't it possible to make a dynamic path via the
oledbconnection in the toolbar? (maybe via a key in the web.config?)
Thanks in advance!
Dirk
The error-message I get is :
No value given for one or more required parameters.
Description: An unhandled exception occurred during the execution of the
current web request. Please review the stack trace for more information
about the error and where it originated in the code.
Exception Details: System.Data.OleDb.OleDbException: No value given for one
or more required parameters.
Source Error:
An unhandled exception was generated during the execution of the
current web request. Information regarding the origin and location of the
exception can be identified using the exception stack trace below.
Stack Trace:
[OleDbException (0x80040e10): No value given for one or more required
parameters.]
System.Data.Common.DbDataAdapter.Update(DataRow[] dataRows,
DataTableMapping tableMapping) +1662
System.Data.Common.DbDataAdapter.Update(DataSet dataSet, String srcTable)
+152
nieuwdatabase.WebForm1.Button2_Click(Object sender, EventArgs e) in
C:\Inetpub\wwwroot\nieuwdatabase\default.aspx.vb:146
System.Web.UI.WebControls.Button.OnClick(EventArgs e) +108
System.Web.UI.WebControls.Button.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String
eventArgument) +57
System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler
sourceControl, String eventArgument) +18
System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData) +33
System.Web.UI.Page.ProcessRequestMain() +1292
My code in the default.aspx.vb file is as follows:
Imports System
Imports System.Data
Imports System.Data.OleDb
Imports Microsoft.VisualBasic
Public Class WebForm1
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 lblboodschap As System.Web.UI.WebControls.Label
Protected WithEvents Button5 As System.Web.UI.WebControls.Button
Protected WithEvents Button4 As System.Web.UI.WebControls.Button
Protected WithEvents Button3 As System.Web.UI.WebControls.Button
Protected WithEvents Button2 As System.Web.UI.WebControls.Button
Protected WithEvents Button1 As System.Web.UI.WebControls.Button
Protected WithEvents Label5 As System.Web.UI.WebControls.Label
Protected WithEvents Label4 As System.Web.UI.WebControls.Label
Protected WithEvents Label3 As System.Web.UI.WebControls.Label
Protected WithEvents Label2 As System.Web.UI.WebControls.Label
Protected WithEvents Label1 As System.Web.UI.WebControls.Label
Protected WithEvents txtnummer As System.Web.UI.WebControls.TextBox
Protected WithEvents txtnaam As System.Web.UI.WebControls.TextBox
Protected WithEvents txtadres As System.Web.UI.WebControls.TextBox
Protected WithEvents txtwoonplaats As System.Web.UI.WebControls.TextBox
Protected WithEvents txtpostcode As System.Web.UI.WebControls.TextBox
'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
Dim conklanten As OleDbConnection
Dim dadklanten As New OleDbDataAdapter
Dim selklanten As IDbCommand
Dim upklanten As IDbCommand
Dim delklanten As IDbCommand
Dim inklanten As IDbCommand
Dim dasklanten As New DataSet
Private Sub Page_Load(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles MyBase.Load
Dim strcon As String
strcon = "PROVIDER=Microsoft.jet.OLEDB.4.0;DATA SOURCE =" &
Server.MapPath("klanten.mdb") & ";"
conklanten = New OleDbConnection(strcon)
selklanten = conklanten.CreateCommand
selklanten.Connection = conklanten
upklanten = conklanten.CreateCommand
upklanten.Connection = conklanten
delklanten = conklanten.CreateCommand
delklanten.Connection = conklanten
inklanten = conklanten.CreateCommand
inklanten.Connection = conklanten
selklanten.CommandText = "Select * from tblklanten" '
selklanten.CommandType = CommandType.Text
dadklanten.SelectCommand = selklanten
upklanten.CommandText = "update tblklanten set adres = ?, naam = ?, nummer =
?, postcode = ?, woonplaats = ? where (nummer =?) and (adres = ? or ? is
Null and adres is null) and (naam =? or ? is null and naam is null) and
(postcode = ? or ? is null and postcode is null) and (woonplaats =? or ? is
null and woonplaats is null)"
upklanten.Parameters.Add(New System.Data.OleDb.OleDbParameter("adres",
System.Data.OleDb.OleDbType.VarWChar, 50, "adres"))
upklanten.Parameters.Add(New System.Data.OleDb.OleDbParameter("naam",
System.Data.OleDb.OleDbType.VarWChar, 50, "naam"))
upklanten.Parameters.Add(New System.Data.OleDb.OleDbParameter("nummer",
System.Data.OleDb.OleDbType.Integer, 0, "nummer"))
upklanten.Parameters.Add(New System.Data.OleDb.OleDbParameter("postcode",
System.Data.OleDb.OleDbType.VarWChar, 50, "postcode"))
upklanten.Parameters.Add(New System.Data.OleDb.OleDbParameter("woonplaats",
System.Data.OleDb.OleDbType.VarWChar, 50, "woonplaats"))
upklanten.Parameters.Add(New
System.Data.OleDb.OleDbParameter("Original_nummer",
System.Data.OleDb.OleDbType.Integer, 0,
System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0, Byte),
"nummer", System.Data.DataRowVersion.Original, Nothing))
upklanten.Parameters.Add(New
System.Data.OleDb.OleDbParameter("Original_adres",
System.Data.OleDb.OleDbType.VarWChar, 50,
System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0, Byte),
"adres", System.Data.DataRowVersion.Original, Nothing))
upklanten.Parameters.Add(New
System.Data.OleDb.OleDbParameter("Original_adres1",
System.Data.OleDb.OleDbType.VarWChar, 50,
System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0, Byte),
"adres", System.Data.DataRowVersion.Original, Nothing))
upklanten.Parameters.Add(New
System.Data.OleDb.OleDbParameter("Original_naam",
System.Data.OleDb.OleDbType.VarWChar, 50,
System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0, Byte),
"naam", System.Data.DataRowVersion.Original, Nothing))
upklanten.Parameters.Add(New
System.Data.OleDb.OleDbParameter("Original_naam1",
System.Data.OleDb.OleDbType.VarWChar, 50,
System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0, Byte),
"naam", System.Data.DataRowVersion.Original, Nothing))
upklanten.Parameters.Add(New
System.Data.OleDb.OleDbParameter("Original_postcode",
System.Data.OleDb.OleDbType.VarWChar, 50,
System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0, Byte),
"postcode", System.Data.DataRowVersion.Original, Nothing))
upklanten.Parameters.Add(New
System.Data.OleDb.OleDbParameter("Original_postcode1",
System.Data.OleDb.OleDbType.VarWChar, 50,
System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0, Byte),
"postcode", System.Data.DataRowVersion.Original, Nothing))
upklanten.Parameters.Add(New
System.Data.OleDb.OleDbParameter("Original_woonplaats",
System.Data.OleDb.OleDbType.VarWChar, 50,
System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0, Byte),
"woonplaats", System.Data.DataRowVersion.Original, Nothing))
upklanten.Parameters.Add(New
System.Data.OleDb.OleDbParameter("Original_woonplaats1",
System.Data.OleDb.OleDbType.VarWChar, 50,
System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0, Byte),
"woonplaats", System.Data.DataRowVersion.Original, Nothing))
upklanten.CommandType = CommandType.Text
dadklanten.UpdateCommand = upklanten
delklanten.CommandText = "Delete from tblklanten where (nummer =?)"
delklanten.CommandType = CommandType.Text
delklanten.Parameters.Add(New System.Data.OleDb.OleDbParameter("nummer",
System.Data.OleDb.OleDbType.Integer, 0, "nummer"))
dadklanten.DeleteCommand = delklanten
inklanten.CommandText = "insert into tblklanten(adres, naam, nummer,
postcode, woonplaats) values (?, ?, ?, ?, ?)"
inklanten.CommandType = CommandType.Text
inklanten.Connection = Me.conklanten
inklanten.Parameters.Add(New System.Data.OleDb.OleDbParameter("adres",
System.Data.OleDb.OleDbType.VarWChar, 50, "adres"))
inklanten.Parameters.Add(New System.Data.OleDb.OleDbParameter("naam",
System.Data.OleDb.OleDbType.VarWChar, 50, "naam"))
inklanten.Parameters.Add(New System.Data.OleDb.OleDbParameter("nummer",
System.Data.OleDb.OleDbType.Integer, 0, "nummer"))
inklanten.Parameters.Add(New System.Data.OleDb.OleDbParameter("postcode",
System.Data.OleDb.OleDbType.VarWChar, 50, "postcode"))
inklanten.Parameters.Add(New System.Data.OleDb.OleDbParameter("woonplaats",
System.Data.OleDb.OleDbType.VarWChar, 50, "woonplaats"))
dadklanten.InsertCommand = inklanten
dadklanten.Fill(dasklanten, "tblklanten")
End Sub
Protected Overrides Sub finalize()
dadklanten.Dispose()
MyBase.Finalize()
End Sub
Private Sub Button5_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles Button5.Click
subclear(True)
End Sub
Private Sub subclear(ByVal clearnummer As Boolean)
If clearnummer Then txtnummer.Text = ""
txtnaam.Text = ""
txtadres.Text = ""
txtpostcode.Text = ""
txtwoonplaats.Text = ""
lblboodschap.Text = ""
End Sub
Private Function funcontroleoke() As Boolean
If IsNumeric(txtnummer.Text) AndAlso txtnaam.Text.Trim <> "" AndAlso
txtadres.Text.Trim <> "" AndAlso txtpostcode.Text.Trim <> "" AndAlso
txtwoonplaats.Text.Trim <> "" Then
Return True
End If
End Function
Private Function funzoekrij() As Long
funzoekrij = -1
If IsNumeric(txtnummer.Text) Then
Dim wrij As Long
For wrij = 0 To dasklanten.Tables(0).Rows.Count - 1
If dasklanten.Tables(0).Rows(wrij)("nummer") = CLng(txtnummer.Text)
Then
funzoekrij = wrij
End If
Next
End If
End Function
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles Button1.Click ' =search
Dim wrij As Long = funzoekrij()
subclear(False)
If wrij < 0 Then
lblboodschap.Text = "Niet gevonden"
Else
With dasklanten.Tables(0)
txtnaam.Text = .Rows(wrij)("naam").ToString
txtadres.Text = .Rows(wrij)("adres").ToString
txtpostcode.Text = .Rows(wrij)("postcode").ToString
txtwoonplaats.Text = .Rows(wrij)("woonplaats").ToString
End With
End If
End Sub
Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles Button2.Click 'update
Dim wrij As Long = funzoekrij()
If wrij < 0 Then
lblboodschap.Text = "Niet gevonden"
Else
If Not funcontroleoke() Then
lblboodschap.Text = "Onvolledig"
Exit Sub
End If
With dasklanten.Tables(0)
.Rows(wrij)("naam") = txtnaam.Text
.Rows(wrij)("adres") = txtadres.Text
.Rows(wrij)("postcode") = txtpostcode.Text
.. Rows(wrij)("woonplaats") = txtwoonplaats.Text
End With
dadklanten.Update(dasklanten, "tblklanten")
subclear(True)
lblboodschap.Text = "Wijzigingen doorgevoerd"
End If
End Sub
Private Sub Button3_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles Button3.Click
Dim wrij As Long = funzoekrij()
If wrij >= 0 Then
lblboodschap.Text = "Nummer Bestaat al"
Else
If Not funcontroleoke() Then
lblboodschap.Text = "Onvolledig"
Exit Sub
End If
Dim newrij As DataRow = dasklanten.Tables("tblklanten").NewRow
newrij("nummer") = txtnummer.Text
newrij("naam") = txtnaam.Text
newrij("adres") = txtadres.Text
newrij("woonplaats") = txtwoonplaats.Text
newrij("postcode") = txtpostcode.Text
dasklanten.Tables(0).Rows.Add(newrij)
dadklanten.Update(dasklanten, "tblklanten")
subclear(True)
lblboodschap.Text = "gegevens ingevoerd"
End If
End Sub
Private Sub Button4_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles Button4.Click 'delete
Dim wrij As Long = funzoekrij()
If wrij < 0 Then
lblboodschap.Text = "Record met dit nr bestaat niet"
Else
dasklanten.Tables("tblklanten").Rows(wrij).Delete()
dadklanten.Update(dasklanten, "tblklanten")
subclear(True)
lblboodschap.Text = "gegevens verwijderd"
End If
End Sub
End Class