Wrox Programmer Forums
|
ASP.NET 1.0 and 1.1 Basics ASP.NET discussion for users new to coding in ASP.NET 1.0 or 1.1. NOT for the older "classic" ASP 3 or the newer ASP.NET 2.0.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the ASP.NET 1.0 and 1.1 Basics section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old February 4th, 2005, 08:07 PM
Friend of Wrox
 
Join Date: Apr 2004
Posts: 105
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via MSN to RPG SEARCH
Default inserting values into a database

Hi there,

I have a drop down list. I pull information from a database and bind it to the ddl.

        ddlSelectCategories.DataSource = getCategories()
        ddlSelectCategories.DataValueField = "CategoryID"
        ddlSelectCategories.DataTextField = "CategoryName"
        ddlSelectCategories.DataBind()

I then select something in the list and type something into a text box underneath. I the click a button (the purpose is to update the database). Button code...

    'This is the event for the sub category creation.
    Sub btnCreateSubCategory_Click(sender As Object, e As EventArgs)

        'This declares the variables that will connect to the database.
        Dim ConnectionString as string = ConfigurationSettings.AppSettings("ConnectionStrin g")
        Dim dbConnection As New OleDbConnection(ConnectionString)
            dbConnection.Open()
        Dim commandString as String = "INSERT INTO tblSubCategory (SubCategoryName, CategoryID, CategoryName) Values(@SubCategoryName, @CategoryID, @CategoryName)"
        Dim dbCommand as new OleDbCommand(commandString, dbConnection)

        dbCommand.Parameters.Add("@SubCategoryName", OleDbType.VarChar, 50).Value = txtSubCategoryName.Text
        dbCommand.Parameters.Add("@CategoryID", OleDbType.VarChar, 10).Value = ddlSelectCategories.DataValueField
        dbCommand.Parameters.Add("@CategoryName", OleDbType.VarChar, 50).Value = ddlSelectCategories.DataTextField

        dbCommand.ExecuteNonQuery()

        dbConnection.Close()

        'this displays a label to show it created the sub category
        lblCreateSubCategory.Visible = "True"

    End Sub

But i get an error message, i have tried playing around with the code and looking it up in a book or two and dont understand it....

Exception Details: System.Data.OleDb.OleDbException: Data type mismatch in criteria expression.

Source Error:


Line 66: dbCommand.Parameters.Add("@CategoryName", OleDbType.VarChar, 50).Value = ddlSelectCategories.DataTextField
Line 67:
Line 68: dbCommand.ExecuteNonQuery()
Line 69:
Line 70: dbConnection.Close()


Source File: C:\Inetpub\wwwroot\application\admin\categories.as px Line: 68

Stack Trace:


[OleDbException (0x80040e07): Data type mismatch in criteria expression.]
   System.Data.OleDb.OleDbCommand.ExecuteCommandTextE rrorHandling(Int32 hr) +41
   System.Data.OleDb.OleDbCommand.ExecuteCommandTextF orSingleResult(tagDBPARAMS dbParams, Object& executeResult) +122
   System.Data.OleDb.OleDbCommand.ExecuteCommandText( Object& executeResult) +92
   System.Data.OleDb.OleDbCommand.ExecuteCommand(Comm andBehavior behavior, Object& executeResult) +65
   System.Data.OleDb.OleDbCommand.ExecuteReaderIntern al(CommandBehavior behavior, String method) +112
   System.Data.OleDb.OleDbCommand.ExecuteNonQuery() +66
   ASP.categories_aspx.btnCreateSubCategory_Click(Obj ect sender, EventArgs e) in C:\Inetpub\wwwroot\application\admin\categories.as px:68
   System.Web.UI.WebControls.Button.OnClick(EventArgs e) +83
   System.Web.UI.WebControls.Button.System.Web.UI.IPo stBackEventHandler.RaisePostBackEvent(String eventArgument) +57
   System.Web.UI.Page.RaisePostBackEvent(IPostBackEve ntHandler sourceControl, String eventArgument) +18
   System.Web.UI.Page.RaisePostBackEvent(NameValueCol lection postData) +33
   System.Web.UI.Page.ProcessRequestMain() +1277


Any help on this would be appreciated!

David
__________________
David Jenkins
 
Old February 5th, 2005, 01:26 AM
Friend of Wrox
 
Join Date: Nov 2003
Posts: 1,348
Thanks: 0
Thanked 5 Times in 5 Posts
Default

I beleive you need to change these 2 lines:
dbCommand.Parameters.Add("@CategoryID", OleDbType.VarChar, 10).Value = ddlSelectCategories.DataValueField
        dbCommand.Parameters.Add("@CategoryName", OleDbType.VarChar, 50).Value = ddlSelectCategories.DataTextField


To:
dbCommand.Parameters.Add("@CategoryID", OleDbType.VarChar, 10).Value = ddlSelectCategories.SelectedItem.Value
        dbCommand.Parameters.Add("@CategoryName", OleDbType.VarChar, 50).Value = ddlSelectCategories.SelectedItem.Text
 
Old February 5th, 2005, 09:38 AM
Friend of Wrox
 
Join Date: Apr 2004
Posts: 105
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via MSN to RPG SEARCH
Default

Cool thanks for the help! It works great now.

David Jenkins
-------------------------------------------------------------
Do you want to make extra money around your commitments?
Credit cards, bills, loans and a mortgage - all getting you down?
Is your pension going to be enough when you retire?
There is a solution visit http://www.1stmillion.co.uk
or call 01772 489521
 
Old February 5th, 2005, 07:56 PM
Friend of Wrox
 
Join Date: Apr 2004
Posts: 105
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via MSN to RPG SEARCH
Default

Now i am trying to create an edit field... i want to be able to alter the sub cat value and also select the main cat froma ddl (dont know if that is possible in an edit field)....

my code is not working, is there an easy way of doing this.,,,


Sub Page_Load()

'This checks to see if their is a cookie on the viewers system.
If Request.Cookies("AdminNameCookie") is nothing Then

'If there is not one then it sends the person back to the login page.
Response.Redirect("default.aspx")
End If

If Not Page.IsPostBack then
    'This binds the data to the data grids.
    dgCategories.DataSource() = getCategories()
    dgCategories.DataBind()

    dgSubCategories.DataSource() = getSubCategories()
    dgSubCategories.DataBind()

    'This binds the categories data to the drop down list to create sub categories
    ddlSelectCategories.DataSource = getCategories()
    ddlSelectCategories.DataValueField = "CategoryID"
    ddlSelectCategories.DataTextField = "CategoryName"
    ddlSelectCategories.DataBind()
End if

End Sub


'This is the event for the category creation.
Sub btnCreateCategory_Click(sender As Object, e As EventArgs)

    'This declares the variables that will connect to the database.
    Dim ConnectionString as string = ConfigurationSettings.AppSettings("ConnectionStrin g")
    Dim dbConnection As New OleDbConnection(ConnectionString)
        dbConnection.Open()
    Dim commandString as String = "INSERT INTO tblCategories (categoryName) Values(@categoryName)"
    Dim dbCommand as new OleDbCommand(commandString, dbConnection)

    dbCommand.Parameters.Add("@categoryName", OleDbType.VarChar, 10).Value = txtCategoryName.Text

    dbCommand.ExecuteNonQuery()

    dbConnection.Close()

    'this displays a label to show the category has been created.
    lblCreateCategory.Visible = "True"
    txtCategoryName.text = ""

End Sub


'This is the event for the sub category creation.
Sub btnCreateSubCategory_Click(sender As Object, e As EventArgs)

    'This declares the variables that will connect to the database.
    Dim ConnectionString as string = ConfigurationSettings.AppSettings("ConnectionStrin g")
    Dim dbConnection As New OleDbConnection(ConnectionString)
        dbConnection.Open()
    Dim commandString as String = "INSERT INTO tblSubCategory (SubCategoryName, CategoryID, CategoryName) Values(@SubCategoryName, @CategoryID, @CategoryName)"
    Dim dbCommand as new OleDbCommand(commandString, dbConnection)

    dbCommand.Parameters.Add("@SubCategoryName", OleDbType.VarChar, 50).Value = txtSubCategoryName.Text
    dbCommand.Parameters.Add("@CategoryID", OleDbType.VarChar, 10).Value = ddlSelectCategories.SelectedItem.Value
    dbCommand.Parameters.Add("@CategoryName", OleDbType.VarChar, 50).Value = ddlSelectCategories.SelectedItem.Text

    dbCommand.ExecuteNonQuery()

    dbConnection.Close()

    'this displays a label to show it created the sub category
    lblCreateSubCategory.Visible = "True"
    txtSubCategoryName.text = ""

End Sub

'This sub is to select a row to be edited.
Sub dgSubCategories_Edit(Sender as Object, E as DataGridCommandEventArgs)

    dgSubCategories.EditItemIndex = E.Item.ItemIndex

End Sub

'This resets the edited row when the cancel button is selected.
Sub dgSubCategories_Cancel(Sender as Object, E as DataGridCommandEventArgs)

    dgSubCategories.EditItemIndex = -1

End Sub

Sub dgSubCategories_Update(sender as object, E as DataGridCommandEventArgs)

    'Read the values of the updated row.
    Dim iSubCatID as Integer = E.Item.Cells(1).Text
    Dim strSubCat as string = CType(E.Item.Cells(2).Controls(0), TextBox).Text


    'Construct the sql statement
    Dim strSQL as String = _
    "UPDATE [tblSubCategories] SET [SubCategoryName] = @SubCategoryName WHERE [SubCategoryID] = @SubCategoryID"

    Const strConnString as String = "Provider=Microsoft.Jet.OLEDB.4.0; Ole DB Services=-4; Data Source=C:\Inetpub\wwwroot\application\admin\db1.md b"
    Dim objConn as New OleDbConnection(strConnString)
    objConn.Open()

    Dim myCommand as OleDbCommand = new OleDbCommand(strSQL, objConn)
    myCommand.CommandType = CommandType.Text

    'Add Parameters here
    Dim parameterSubCategoryName as OleDbParameter = _
    new OleDbParameter("@SubCategoryName", OleDbType.VarWChar, 75) parameterSubCategoryName.Value = strSubCat

    myCommand.ExecuteNonQuery()

    objConn.Close

    dgSubCategories.EditItemIndex = -1

End Sub

    'The following two functions get all the details from the database to be displayed on the page!
    Function getCategories() As System.Data.DataSet
        Dim ConnectionString As String = ConfigurationSettings.AppSettings("ConnectionStrin g")
        Dim dbConnection As System.Data.IDbConnection = New System.Data.OleDb.OleDbConnection(connectionString )

        Dim queryString As String = "SELECT [tblCategories].* FROM [tblCategories]"
        Dim dbCommand As System.Data.IDbCommand = New System.Data.OleDb.OleDbCommand
        dbCommand.CommandText = queryString
        dbCommand.Connection = dbConnection

        Dim dataAdapter As System.Data.IDbDataAdapter = New System.Data.OleDb.OleDbDataAdapter
        dataAdapter.SelectCommand = dbCommand
        Dim dataSet As System.Data.DataSet = New System.Data.DataSet
        dataAdapter.Fill(dataSet)

        Return dataSet
    End Function


    Function getSubCategories() As System.Data.IDataReader
        Dim connectionString As String = ConfigurationSettings.AppSettings("ConnectionStrin g")
        Dim dbConnection As System.Data.IDbConnection = New System.Data.OleDb.OleDbConnection(connectionString )

        Dim queryString As String = "SELECT [tblSubCategory].* FROM [tblSubCategory]"
        Dim dbCommand As System.Data.IDbCommand = New System.Data.OleDb.OleDbCommand
        dbCommand.CommandText = queryString
        dbCommand.Connection = dbConnection

        dbConnection.Open
        Dim dataReader As System.Data.IDataReader = dbCommand.ExecuteReader(System.Data.CommandBehavio r.CloseConnection)

        Return dataReader
    End Function

Sub dgSubCategories_SelectedIndexChanged(sender As Object, e As EventArgs)

End Sub
'



 <p>
            <asp:DataGrid id="dgCategories" runat="server" AutoGenerateColumns="False" CellPadding="3">
                <Columns>
                    <asp:BoundColumn DataField="CategoryID" HeaderText="Cat ID" ItemStyle-HorizontalAlign="Center" />
                    <asp:BoundColumn DataField="CategoryName" HeaderText="Category Name" ItemStyle-HorizontalAlign="Center" />
                </Columns>
            </asp:DataGrid>
        </p>
        <p>
            <asp:DataGrid id="dgSubCategories" runat="server" AutoGenerateColumns="False" CellPadding="3" OnEditCommand="dgSubCategories_Edit" OnSelectedIndexChanged="dgSubCategories_SelectedIn dexChanged" OnCancelCommand="dgSubCategories_Cancel" OnUpdateCommand="dgSubCategories_Update">
                <Columns>
                    <asp:EditCommandColumn EditText="Edit" ButtonType="PushButton" UpdateText="Update" CancelText="Cancel" />
                    <asp:BoundColumn DataField="SubCategoryID" HeaderText="Sub Cat ID" ItemStyle-HorizontalAlign="Center" ReadOnly="True" />
                    <asp:BoundColumn DataField="SubCategoryName" HeaderText="Sub Category Name" ItemStyle-HorizontalAlign="Center" />
                    <asp:BoundColumn DataField="CategoryName" HeaderText="Main Category" ItemStyle-HorizontalAlign="Center" />
                </Columns>
            </asp:DataGrid>
        </p>


thanks for any help...

David Jenkins
-------------------------------------------------------------
Do you want to make extra money around your commitments?
Credit cards, bills, loans and a mortgage - all getting you down?
Is your pension going to be enough when you retire?
There is a solution visit http://www.1stmillion.co.uk
or call 01772 489521





Similar Threads
Thread Thread Starter Forum Replies Last Post
Inserting values from other tables TheAndruu SQL Language 7 May 27th, 2007 10:47 AM
inserting values sspr SQL Server 2000 2 February 15th, 2006 03:20 PM
Error while inserting values into database table pothireddy_s SQL Server 2000 2 August 3rd, 2005 03:01 AM
Inserting Values in Database OracleCommand VB.NET narendra_patil BOOK: Beginning ASP.NET 1.0 0 April 22nd, 2005 07:35 AM
inserting values error - Please help gtmm9 Classic ASP Databases 1 October 6th, 2003 11:43 AM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.