Results 1 to 14 of 14
  1. #1
    Join Date
    Nov 2010
    Posts
    10

    Unanswered: No value given for one or more parameters

    I am working in ASP and keep getting a No value given for one or more parameters error. The problem is I'm passing txtHotelID as a string or something when in the textbox I entered a number. I've tried several times putting single quotes around it and keep getting the same error. In my access database I declared the Hotel_ID field as Text. Please help.


    Protected Sub btnUpdate_Click(ByVal sender As Object, ByVal e As EventArgs) Handles btnUpdate.Click

    Dim sSQLUpdate As String

    Dim sConnectionString As String = "Provider=Microsoft.jet.oledb.4.0;" & _
    "Data source=e:\Hotels.mdb"

    sSQLUpdate = "Update Hotels set Hotel_Name = @HotelName where Hotel_ID = '" & txtHotelID.Text & "'"
    Dim conUpdate As New OleDbConnection(sConnectionString)

    Dim cmdUpdate As New OleDbCommand(sSQLUpdate, conUpdate)

    conUpdate.Open()
    cmdUpdate.ExecuteNonQuery()
    conUpdate.Close()
    End Sub

    screenshot of error: http://i54.tinypic.com/2mgl4i0.jpg

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    The error is exactly what it says, but not what you think. It is not Hotel_ID that is the problem but @HotelName: you do not set a value for this parameter.

    You have effectively mixed parameters and concatenating literals into your SQL Statement - really you should parametrise the whole thing.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Nov 2010
    Posts
    10

    well

    I already have parameters for everything but don't know how to implement them into this SQL query to update the record. Do I need to show you all my code?

    sSQLUpdate = "Update Hotels set Hotel_Name = " & txtHotelName.Text & " where Hotel_ID = " & txtHotelID.Text & "" looks like it makes sense but doesn't work.

  4. #4
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    No, you don't need to show any more code.

    The command object has a parameters collection. You can use the Add method of this collection to add the parameter @HotelName plus the corresponding value.

    Before we get in to that, do you understand now why your code is failing?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  5. #5
    Join Date
    Nov 2010
    Posts
    10

    asdf

    my teacher was telling me that SQL can't recognize/pass a string that has numbers in it without single quotes around it. all he did was confuse me and I still don't understand why the code doesn't work. If I could just get this update button working I would have finished the entire program by now.

  6. #6
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Your teacher is wrong.

    here is your code changed. it will illicit exactly the same error.
    Code:
    Protected Sub btnUpdate_Click(ByVal sender As Object, ByVal e As EventArgs) Handles btnUpdate.Click
    
            Dim sSQLUpdate As String
    
            Dim sConnectionString As String = "Provider=Microsoft.jet.oledb.4.0;" & _
            "Data source=e:\Hotels.mdb"
    
            sSQLUpdate = "Update Hotels set Hotel_Name = @HotelName"
            Dim conUpdate As New OleDbConnection(sConnectionString)
    
            Dim cmdUpdate As New OleDbCommand(sSQLUpdate, conUpdate)
    
            conUpdate.Open()
            cmdUpdate.ExecuteNonQuery()
            conUpdate.Close()
        End Sub
    I have removed the Hotel_ID bit. The error is because @HotelName is a parameter, but you have not defined it anywhere. Understand now?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  7. #7
    Join Date
    Nov 2010
    Posts
    10

    asdf

    Further up in my code I associated parameter variables and added assignment statements. But yeah I see that @HotelName doesn't associate with what I modified in the txtHotelName textbox

  8. #8
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Ok - so we now know the problem.

    Your parameters need to be added to the parameters collection of the OleDbCommand object. e.g. OleDbParameterCollection.Add Method
    Testimonial:
    pootle flump
    ur codings are working excelent.

  9. #9
    Join Date
    Nov 2010
    Posts
    10

    asdf

    I think I already did that, I highlighted in the code where its at:

    Code:
    Imports System.Data
    Imports System.Data.OleDb
    
    Partial Public Class HotelInfo
        Inherits System.Web.UI.Page
        Dim inc As Integer
        Dim MaxRows As Integer
        Dim con As New OleDb.OleDbConnection
        Dim ds As New DataSet
        Dim ds2 As New DataSet
        Dim da As New OleDb.OleDbDataAdapter
        Dim da2 As New OleDb.OleDbDataAdapter
        Dim dr As OleDb.OleDbDataReader 'sets up an object to read one tuple at a time for the find function
        Dim sql As String
    
    #Region "Web Form Designer Generated Code "
        'This call is required by the Web Form Designer.
    
        <System.Diagnostics.DebuggerStepThrough()> Private Sub InitializeComponent()
    
        End Sub
    
        Private designerPlaceholderDeclaration As System.Object
    
        Private Sub Page_Init(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Init
            InitializeComponent()
        End Sub
    
    #End Region
    
        Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
            con.ConnectionString = "provider=microsoft.jet.oledb.4.0;data source = e:\Hotels.mdb"
            con.Open()
            sql = "Select * from HOTELS"
            da = New OleDb.OleDbDataAdapter(sql, con)
            da.Fill(ds, "HotelInfo")
            con.Close()
            MaxRows = ds.Tables("HotelInfo").Rows.Count
            inc = -1
        End Sub
    
        Protected Sub btnFind_Click(ByVal sender As Object, ByVal e As EventArgs) Handles btnFind.Click
            Dim HotelNameIn As String
            Dim FindFlag As Boolean
            FindFlag = False
            HotelNameIn = InputBox("Enter name of Hotel to find:")
            For inc = 0 To MaxRows - 1
                If HotelNameIn = ds.Tables("HotelInfo").Rows(inc).Item(1) Then
                    txtHotelID.Text = ds.Tables("HotelInfo").Rows(inc).Item(0)
                    txtHotelName.Text = ds.Tables("HotelInfo").Rows(inc).Item(1)
                    txtHotelCity.Text = ds.Tables("HotelInfo").Rows(inc).Item(2)
                    txtHotelState.Text = ds.Tables("HotelInfo").Rows(inc).Item(3)
                    txtHotelAddress.Text = ds.Tables("HotelInfo").Rows(inc).Item(4)
                    txtHotelZipCode.Text = ds.Tables("HotelInfo").Rows(inc).Item(5)
                    txtHotelManager.Text = ds.Tables("HotelInfo").Rows(inc).Item(6)
                    txtHotelPhoneNumber.Text = ds.Tables("HotelInfo").Rows(inc).Item(7)
                    FindFlag = True
                    Exit For
                End If
            Next
    
            If FindFlag = False Then
                MsgBox("Name not found in database")
                MaxRows += 1
            End If
        End Sub
    
        Protected Sub btnAdd_Click(ByVal sender As Object, ByVal e As EventArgs) Handles btnAdd.Click
            'Check to see that all input is valid before processing
            If Page.IsValid Then
                'declare connection string variable and cmdInsert as a oledbcommand data type
                Dim cmdInsert As New OleDbCommand
    
                Dim sConnectionString As String = "Provider=Microsoft.jet.oledb.4.0;" & _
                "Data source=e:\Hotels.mdb;"
    
                'setup conStudent as connection object for the database
                Dim conInsert As New OleDbConnection(sConnectionString)
    
                'set conStudent as connection to be used for cmdInsert
                cmdInsert.Connection = conInsert
    
                'load commandtext property with proper SQL syntax with parameters to be passed
                cmdInsert.CommandText = "Insert into Hotels (Hotel_ID, Hotel_Name, City, State, Address, Zip_Code, Manager, Phone_Number) Values (@HotelID, @HotelName, @HotelCity, @HotelState, @HotelAddress, @HotelZipCode, @HotelManager, @HotelPhoneNumber)"
    
                'created parameter variables and attach to parameters to be passed to SQL command
                Dim prmHotelID As New OleDbParameter("@HotelID", OleDbType.VarChar)
                Dim prmHotelName As New OleDbParameter("@HotelName", OleDbType.VarChar)
                Dim prmHotelCity As New OleDbParameter("@HotelCity", OleDbType.VarChar)
                Dim prmHotelState As New OleDbParameter("@HotelState", OleDbType.VarChar)
                Dim prmHotelAddress As New OleDbParameter("@HotelAddress", OleDbType.VarChar)
                Dim prmHotelZipCode As New OleDbParameter("@HotelZipCode", OleDbType.VarChar)
                Dim prmHotelManager As New OleDbParameter("@HotelManager", OleDbType.VarChar)
                Dim prmHotelPhoneNumber As New OleDbParameter("@HotelPhoneNumber", OleDbType.VarChar)
    
                'Associate parameter variables to comdInsert
                cmdInsert.Parameters.Add(prmHotelID)
                cmdInsert.Parameters.Add(prmHotelName)
                cmdInsert.Parameters.Add(prmHotelCity)
                cmdInsert.Parameters.Add(prmHotelState)
                cmdInsert.Parameters.Add(prmHotelAddress)
                cmdInsert.Parameters.Add(prmHotelZipCode)
                cmdInsert.Parameters.Add(prmHotelManager)
                cmdInsert.Parameters.Add(prmHotelPhoneNumber)
    
    
                'Assignment statements to get information from web page text boxes to parameter variables
                prmHotelID.Value = txtHotelID.Text
                prmHotelName.Value = txtHotelName.Text
                prmHotelCity.Value = txtHotelCity.Text
                prmHotelState.Value = txtHotelState.Text
                prmHotelAddress.Value = txtHotelAddress.Text
                prmHotelZipCode.Value = txtHotelZipCode.Text
                prmHotelManager.Value = txtHotelManager.Text
                prmHotelPhoneNumber.Value = txtHotelPhoneNumber.Text
    
                'open the connection
                conInsert.Open()
    
                'execute the insert SQL query
                cmdInsert.ExecuteNonQuery()
    
                'close the connection
                conInsert.Close()
            End If
        End Sub
    
        Protected Sub btnClear_Click(ByVal sender As Object, ByVal e As EventArgs) Handles btnClear.Click
            txtHotelAddress.Text = ""
            txtHotelCity.Text = ""
            txtHotelID.Text = ""
            txtHotelManager.Text = ""
            txtHotelName.Text = ""
            txtHotelPhoneNumber.Text = ""
            txtHotelState.Text = ""
            txtHotelZipCode.Text = ""
        End Sub
    
        Protected Sub btnDelete_Click(ByVal sender As Object, ByVal e As EventArgs) Handles btnDelete.Click
            Dim sSQLDelete As String
    
            If txtHotelID.Text <> "" Then
                sSQLDelete = "delete from hotels where hotel_id ='" & txtHotelID.Text & "'"
            Else
                sSQLDelete = "delete from hotels where hotel_name ='" & txtHotelName.Text & "'"
            End If
    
            Dim sConnectionString As String = "Provider=Microsoft.jet.oledb.4.0;" & _
            "Data source=e:\Hotels.mdb;"
    
            Dim conDelete As New OleDbConnection(sConnectionString)
    
            Dim cmdDelete As New OleDbCommand(sSQLDelete, conDelete)
    
            conDelete.Open()
            cmdDelete.ExecuteNonQuery()
            conDelete.Close()
        End Sub
    
        Protected Sub btnUpdate_Click(ByVal sender As Object, ByVal e As EventArgs) Handles btnUpdate.Click
    
            Dim sSQLUpdate As String
    
            Dim sConnectionString As String = "Provider=Microsoft.jet.oledb.4.0;" & _
            "Data source=e:\Hotels.mdb"
    
            sSQLUpdate = "Update Hotels set Hotel_Name = " & txtHotelName.Text & " where Hotel_ID = " & txtHotelID.Text & ""
            Dim conUpdate As New OleDbConnection(sConnectionString)
    
            Dim cmdUpdate As New OleDbCommand(sSQLUpdate, conUpdate)
    
            conUpdate.Open()
            cmdUpdate.ExecuteNonQuery()
            conUpdate.Close()
        End Sub
    End Class
    Last edited by ch4d; 11-02-10 at 18:25.

  10. #10
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    See my post #6? Please can you wrap your code up in the code tags too? It is hard to read like that. Code tags can be found in the "Go Advanced" view
    Testimonial:
    pootle flump
    ur codings are working excelent.

  11. #11
    Join Date
    Nov 2010
    Posts
    10

    asdf

    Sorry, thanks for helping me so far. I wrapped up my code in the previous post. I've already added the parameters right?

  12. #12
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    I assume you haven't covered scope on your course.

    The parameters are added in a procedure, and scoped to that procedure. The original code you posted is in another procedure entirely, so the parameters collection you are highlighting does not exist.

    You need to apply the same logic, and much of the same code, to the original procedure you posted above.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  13. #13
    Join Date
    Nov 2010
    Posts
    10

    asdf

    am I getting closer?

    Code:
    Imports System.Data
    Imports System.Data.OleDb
    
    Partial Public Class HotelInfo
        Inherits System.Web.UI.Page
        Dim inc As Integer
        Dim MaxRows As Integer
        Dim con As New OleDb.OleDbConnection
        Dim ds As New DataSet
        Dim ds2 As New DataSet
        Dim da As New OleDb.OleDbDataAdapter
        Dim da2 As New OleDb.OleDbDataAdapter
        Dim dr As OleDb.OleDbDataReader 'sets up an object to read one tuple at a time for the find function
        Dim sql As String
    
    #Region "Web Form Designer Generated Code "
        'This call is required by the Web Form Designer.
    
        <System.Diagnostics.DebuggerStepThrough()> Private Sub InitializeComponent()
    
        End Sub
    
        Private designerPlaceholderDeclaration As System.Object
    
        Private Sub Page_Init(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Init
            InitializeComponent()
        End Sub
    
    #End Region
    
        Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
            con.ConnectionString = "provider=microsoft.jet.oledb.4.0;data source = e:\Hotels.mdb"
            con.Open()
            sql = "Select * from HOTELS"
            da = New OleDb.OleDbDataAdapter(sql, con)
            da.Fill(ds, "HotelInfo")
            con.Close()
            MaxRows = ds.Tables("HotelInfo").Rows.Count
            inc = -1
        End Sub
    
        Protected Sub btnFind_Click(ByVal sender As Object, ByVal e As EventArgs) Handles btnFind.Click
            Dim HotelNameIn As String
            Dim FindFlag As Boolean
            FindFlag = False
            HotelNameIn = InputBox("Enter name of Hotel to find:")
            For inc = 0 To MaxRows - 1
                If HotelNameIn = ds.Tables("HotelInfo").Rows(inc).Item(1) Then
                    txtHotelID.Text = ds.Tables("HotelInfo").Rows(inc).Item(0)
                    txtHotelName.Text = ds.Tables("HotelInfo").Rows(inc).Item(1)
                    txtHotelCity.Text = ds.Tables("HotelInfo").Rows(inc).Item(2)
                    txtHotelState.Text = ds.Tables("HotelInfo").Rows(inc).Item(3)
                    txtHotelAddress.Text = ds.Tables("HotelInfo").Rows(inc).Item(4)
                    txtHotelZipCode.Text = ds.Tables("HotelInfo").Rows(inc).Item(5)
                    txtHotelManager.Text = ds.Tables("HotelInfo").Rows(inc).Item(6)
                    txtHotelPhoneNumber.Text = ds.Tables("HotelInfo").Rows(inc).Item(7)
                    FindFlag = True
                    Exit For
                End If
            Next
    
            If FindFlag = False Then
                MsgBox("Name not found in database")
                MaxRows += 1
            End If
        End Sub
    
        Protected Sub btnAdd_Click(ByVal sender As Object, ByVal e As EventArgs) Handles btnAdd.Click
            'Check to see that all input is valid before processing
            If Page.IsValid Then
                'declare connection string variable and cmdInsert as a oledbcommand data type
                Dim cmdInsert As New OleDbCommand
    
                Dim sConnectionString As String = "Provider=Microsoft.jet.oledb.4.0;" & _
                "Data source=e:\Hotels.mdb;"
    
                'setup conStudent as connection object for the database
                Dim conInsert As New OleDbConnection(sConnectionString)
    
                'set conStudent as connection to be used for cmdInsert
                cmdInsert.Connection = conInsert
    
                'load commandtext property with proper SQL syntax with parameters to be passed
                cmdInsert.CommandText = "Insert into Hotels (Hotel_ID, Hotel_Name, City, State, Address, Zip_Code, Manager, Phone_Number) Values (@HotelID, @HotelName, @HotelCity, @HotelState, @HotelAddress, @HotelZipCode, @HotelManager, @HotelPhoneNumber)"
    
                'created parameter variables and attach to parameters to be passed to SQL command
                Dim prmHotelID As New OleDbParameter("@HotelID", OleDbType.VarChar)
                Dim prmHotelName As New OleDbParameter("@HotelName", OleDbType.VarChar)
                Dim prmHotelCity As New OleDbParameter("@HotelCity", OleDbType.VarChar)
                Dim prmHotelState As New OleDbParameter("@HotelState", OleDbType.VarChar)
                Dim prmHotelAddress As New OleDbParameter("@HotelAddress", OleDbType.VarChar)
                Dim prmHotelZipCode As New OleDbParameter("@HotelZipCode", OleDbType.VarChar)
                Dim prmHotelManager As New OleDbParameter("@HotelManager", OleDbType.VarChar)
                Dim prmHotelPhoneNumber As New OleDbParameter("@HotelPhoneNumber", OleDbType.VarChar)
    
                'Associate parameter variables to comdInsert
                cmdInsert.Parameters.Add(prmHotelID)
                cmdInsert.Parameters.Add(prmHotelName)
                cmdInsert.Parameters.Add(prmHotelCity)
                cmdInsert.Parameters.Add(prmHotelState)
                cmdInsert.Parameters.Add(prmHotelAddress)
                cmdInsert.Parameters.Add(prmHotelZipCode)
                cmdInsert.Parameters.Add(prmHotelManager)
                cmdInsert.Parameters.Add(prmHotelPhoneNumber)
    
    
                'Assignment statements to get information from web page text boxes to parameter variables
                prmHotelID.Value = txtHotelID.Text
                prmHotelName.Value = txtHotelName.Text
                prmHotelCity.Value = txtHotelCity.Text
                prmHotelState.Value = txtHotelState.Text
                prmHotelAddress.Value = txtHotelAddress.Text
                prmHotelZipCode.Value = txtHotelZipCode.Text
                prmHotelManager.Value = txtHotelManager.Text
                prmHotelPhoneNumber.Value = txtHotelPhoneNumber.Text
    
                'open the connection
                conInsert.Open()
    
                'execute the insert SQL query
                cmdInsert.ExecuteNonQuery()
    
                'close the connection
                conInsert.Close()
            End If
        End Sub
    
        Protected Sub btnClear_Click(ByVal sender As Object, ByVal e As EventArgs) Handles btnClear.Click
            txtHotelAddress.Text = ""
            txtHotelCity.Text = ""
            txtHotelID.Text = ""
            txtHotelManager.Text = ""
            txtHotelName.Text = ""
            txtHotelPhoneNumber.Text = ""
            txtHotelState.Text = ""
            txtHotelZipCode.Text = ""
        End Sub
    
        Protected Sub btnDelete_Click(ByVal sender As Object, ByVal e As EventArgs) Handles btnDelete.Click
            Dim sSQLDelete As String
    
            If txtHotelID.Text <> "" Then
                sSQLDelete = "delete from hotels where hotel_id ='" & txtHotelID.Text & "'"
            Else
                sSQLDelete = "delete from hotels where hotel_name ='" & txtHotelName.Text & "'"
            End If
    
            Dim sConnectionString As String = "Provider=Microsoft.jet.oledb.4.0;" & _
            "Data source=e:\Hotels.mdb;"
    
            Dim conDelete As New OleDbConnection(sConnectionString)
    
            Dim cmdDelete As New OleDbCommand(sSQLDelete, conDelete)
    
            conDelete.Open()
            cmdDelete.ExecuteNonQuery()
            conDelete.Close()
        End Sub
    
        Protected Sub btnUpdate_Click(ByVal sender As Object, ByVal e As EventArgs) Handles btnUpdate.Click
            'Check to see that all input is valid before processing
            'declare connection string variable and cmdInsert as a oledbcommand data type
    
            Dim sConnectionString As String = "Provider=Microsoft.jet.oledb.4.0;" & _
            "Data source=e:\Hotels.mdb;"
    
    
    
            'created parameter variables and attach to parameters to be passed to SQL command
            Dim prmHotelID As New OleDbParameter("@HotelID", OleDbType.VarChar)
            Dim prmHotelName As New OleDbParameter("@HotelName", OleDbType.VarChar)
            Dim prmHotelCity As New OleDbParameter("@HotelCity", OleDbType.VarChar)
            Dim prmHotelState As New OleDbParameter("@HotelState", OleDbType.VarChar)
            Dim prmHotelAddress As New OleDbParameter("@HotelAddress", OleDbType.VarChar)
            Dim prmHotelZipCode As New OleDbParameter("@HotelZipCode", OleDbType.VarChar)
            Dim prmHotelManager As New OleDbParameter("@HotelManager", OleDbType.VarChar)
            Dim prmHotelPhoneNumber As New OleDbParameter("@HotelPhoneNumber", OleDbType.VarChar)
    
    
            'Assignment statements to get information from web page text boxes to parameter variables
            prmHotelID.Value = txtHotelID.Text
            prmHotelName.Value = txtHotelName.Text
            prmHotelCity.Value = txtHotelCity.Text
            prmHotelState.Value = txtHotelState.Text
            prmHotelAddress.Value = txtHotelAddress.Text
            prmHotelZipCode.Value = txtHotelZipCode.Text
            prmHotelManager.Value = txtHotelManager.Text
            prmHotelPhoneNumber.Value = txtHotelPhoneNumber.Text
    
    
            Dim sSQLUpdate As String
    
            sSQLUpdate = "Update Hotels set Hotel_Name = @HotelName where Hotel_ID = @HotelID"
            Dim conUpdate As New OleDbConnection(sConnectionString)
    
            Dim cmdUpdate As New OleDbCommand(sSQLUpdate, conUpdate)
    
            conUpdate.Open()
            cmdUpdate.ExecuteNonQuery()
            conUpdate.Close()
        End Sub
    End Class

  14. #14
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Closer.

    You only need to define the parameters used in the SQL statement, so in the update click event handler you don't need to define city, state, manager etc. parameters.

    However, that is just extra, unnecessary stuff. You also need to add the parameters to the command object. Also, I believe that (according to the link I posted) you will need to change the way you define your parameters in the SQL statement from "@HotelName" to "?", however I do not use .NET and Access together so am not certain. "@HotelName" would be fine for SQL Server though.

    TBH this is really a .NET question rather than an Access one - you might get better joy asking a .NET community.
    Testimonial:
    pootle flump
    ur codings are working excelent.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •