0
Good Afternoon.

I am using ms VWD 2010 Express with vb.net 2010.  I have an Access 2007
database with a table called Members.  Members has 10 columns.  The primary
key is HouseNumber & StreetName.

The problem is using Parameters in the Sql Update command.  I use parameters
to read the table and to Insert records.

The EditAccount program, shown below, reads the Members table and gets the
column values and puts them on my aspx.net page.

When I change some/all the data in the textboxes and click button1 I get
the exception: Parameter @HouseNumber has no default value.

If I take out the Where statement the record is updated and no error occurs.
Of course, without the Where clause all the records get updated and I don't
want that.  The problem seems to be with the Where clause.

I've tried the ms Access ? approach and get the same thing.

I would appreciate any help.  Thank you.  
tfj
---------------------------------------------------------
Imports System.Text.RegularExpressions

Public Class EditAccount
    Inherits System.Web.UI.Page

    '---------- OLE ---------- 
    Dim OLEdb As String = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\OurDocuments\Visual Studio 2010\Projects\SunriseFarms\SunriseFarms\App_Data\SunriseFarms.accdb;Persist Security Info=False;"
    Dim OLEcon As OleDb.OleDbConnection = New OleDb.OleDbConnection(OleDb)
    Dim OLEcmd As OleDb.OleDbCommand
    '------------------------- 

    Dim CookieHouseNumber As String
    Dim CookieStreetName As String
    Dim sqlcount As Integer = 0

    Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load

        ListBox1.Visible = False

    End Sub

    Protected Sub Page_PreRenderComplete(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.PreRenderComplete

        If Not IsPostBack Then
            GetMembersRecord()
        End If

    End Sub

    Protected Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click

        ListBox1.Items.Clear()

        If txtho1FirstName.Text = "" Then
            ListBox1.Items.Add("Home Owner 1 First Name is required")
        End If

        If txtho1LastName.Text = "" Then
            ListBox1.Items.Add("Home Owner 1 Last Name is required")
        End If

        Dim pattern As String = "^([0-9a-zA-Z]([-\.\w]*[0-9a-zA-Z])*@([0-9a-zA-Z][-\w]*[0-9a-zA-Z]\.)+[a-zA-Z]{2,9})$"

        If Regex.IsMatch(txtho1Email.Text, pattern) Then
            'continue
        Else
            ListBox1.Items.Add("Home Owner 1 Email is required")
        End If

        If ListBox1.Items.Count = 0 Then
            UpdateMembersRecord()
        Else
            ListBox1.Visible = True
        End If

    End Sub

    Protected Sub UpdateMembersRecord()

        'Dim mysql As String = "Update Members Set " &
        '                      "ho1FirstName=?, " &
        '                      "ho1LastName=?, " &
        '                      "ho1Email=?, " &
        '                      "ho2FirstName=?, " &
        '                      "ho2LastName=?, " &
        '                      "ho2Email=? " &
        '                      "Where HouseNumber=? And " &
        '                      "StreetName=?"



        Dim mysql As String = "Update Members Set " &
                              "ho1FirstName = @ho1FirstName, " &
                              "ho1LastName = @ho1LastName, " &
                              "ho1Email = @ho1Email, " &
                              "ho2FirstName = @ho2FirstName, " &
                              "ho2LastName = @ho2LastName, " &
                              "ho2Email = @ho2Email " &
                              "Where HouseNumber = @HouseNumber And StreetName = @StreetName"

        OLEcmd = New OleDb.OleDbCommand(mysql, OLEcon)

        OLEcmd.Parameters.Clear()
        OLEcmd.Parameters.AddWithValue("@ho1FirstName", txtho1FirstName.Text)
        OLEcmd.Parameters.AddWithValue("@ho1LastName", txtho1LastName.Text)
        OLEcmd.Parameters.AddWithValue("@ho1Email", txtho1Email.Text)
        OLEcmd.Parameters.AddWithValue("@ho2FirstName", txtho2FirstName.Text)
        OLEcmd.Parameters.AddWithValue("@ho2LastName", txtho2LastName.Text)
        OLEcmd.Parameters.AddWithValue("@ho2Email", txtho2Email.Text)

        OLEcmd.Parameters.AddWithValue("@HouseNumber", CookieHouseNumber)
        OLEcmd.Parameters.AddWithValue("@StreetName", CookieStreetName)

        If OLEcon.State = ConnectionState.Closed Then
            OLEcon.Open()
        End If

        Try
            OLEcmd.ExecuteNonQuery()
        Catch ex As Exception
            MsgBox(ex.Message)
        End Try

        OLEcon.Close()

    End Sub

    Protected Sub GetMembersRecord()

        If Not Request.Cookies("housenumber") Is Nothing Then
            CookieHouseNumber = Server.HtmlEncode(Request.Cookies("housenumber").Value)
            CookieStreetName = Server.HtmlEncode(Request.Cookies("streetname").Value)
        End If

        Label1.Text = CookieHouseNumber & " " & CookieStreetName

        Dim mysql As String = "SELECT * FROM Members where HouseNumber = @HouseNumber And StreetName = @StreetName"

        OLEcmd = New OleDb.OleDbCommand(mysql, OLEcon)

        OLEcmd.Parameters.AddWithValue("@HouseNumber", CookieHouseNumber)
        OLEcmd.Parameters.AddWithValue("@StreeName", CookieStreetName)

        If OLEcon.State = ConnectionState.Closed Then
            OLEcon.Open()
        End If

        Try
            Dim OLEdr As OleDb.OleDbDataReader = OLEcmd.ExecuteReader()
            If OLEdr.HasRows Then
                'continue
            Else
                ListBox1.Items.Add("House Number/Street Name does not exist")
                Exit Sub
            End If
            Do Until OLEdr.Read = False
                txtho1FirstName.Text = OLEdr.Item("ho1FirstName")
                txtho1LastName.Text = OLEdr.Item("ho1LastName")
                txtho1Email.Text = OLEdr.Item("ho1Email")
                txtho2FirstName.Text = OLEdr.Item("ho2FirstName")
                txtho2LastName.Text = OLEdr.Item("ho2LastName")
                txtho2Email.Text = OLEdr.Item("ho2Email")
            Loop
            OLEcon.Close()
        Catch ex As Exception
            MsgBox(ex.Message)
        End Try

    End Sub

End Class
2
Contributors
1
Reply
9
Views
3 Years
Discussion Span
Last Post by wazz
0

it looks like house number might not be set, even though you checked for 'if not is nothing' on the cookie. before setting the cmd parameter, check for a value (check it while debugging); and/or when you 'dim' the variable at the top of the page, give it a default value (= "", for ex.).

This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.