Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > ASP.NET and ASP > ASP.NET 1.0 and 1.1 > ASP.NET 1.0 and 1.1 Basics
Password Reminder
Register
Register | FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read
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 tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
 
 
Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old February 4th, 2005, 08:07 PM
Friend of Wrox
 
Join Date: Apr 2004
Location: Preston, Lancashire, United Kingdom.
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
  #2 (permalink)  
Old February 5th, 2005, 01:26 AM
Friend of Wrox
Points: 4,332, Level: 27
Points: 4,332, Level: 27 Points: 4,332, Level: 27 Points: 4,332, Level: 27
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Nov 2003
Location: , NJ, USA.
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
  #3 (permalink)  
Old February 5th, 2005, 09:38 AM
Friend of Wrox
 
Join Date: Apr 2004
Location: Preston, Lancashire, United Kingdom.
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
  #4 (permalink)  
Old February 5th, 2005, 07:56 PM
Friend of Wrox
 
Join Date: Apr 2004
Location: Preston, Lancashire, United Kingdom.
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
 


Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off


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



All times are GMT -4. The time now is 02:17 PM.


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.