• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 404
  • Last Modified:

Code for Datalist add to SQL

Hi,
I have a datalist that displays Item's in my Database (SQL). In this list I have a button (Text: Add). If the user click's the button I want to add the item they cliked to my SQL database to a table.
My question:
This is my first time doing this and I need Code that when a user Clickes the button it adds that Item to my SQL database. Can someone please help me with a sample? That would be greatly appretiated.
Thanks
0
ALawrence007
Asked:
ALawrence007
  • 19
  • 17
1 Solution
 
DhaestCommented:
ASP.NET Data Controls Part 3: DataList
http://www.ondotnet.com/pub/a/dotnet/2003/03/10/datalist.html

An Introduction to VB.NET and Database Programming - Introduction
http://www.developerfusion.co.uk/show/4286/
0
 
Bob LearnedCommented:
How is the DataList defined?  Did you add an event handler for the button?

Bob
0
 
ALawrence007Author Commented:
I did add an event handler called BTN_Click. I have a label with the item code and a textbox with the QTY to add.
When the user click the button I need to add the Item Code as well as the Textbox QTY to my SQL database.
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
Bob LearnedCommented:
In the button click handler, you can use the SelectedItem.ItemIndex to get the selected item for the DataList:

    Dim index As Integer = Me.DataList1.SelectedItem.ItemIndex

You can also get the value from the DataItem:
 
   Dim name As String = CType(DataList1.SelectedItem.DataItem, DataRowView)("Name")

Bob
0
 
ALawrence007Author Commented:
Bob,

I tried this :
Public Sub BTN_Click(ByVal sender As Object, ByVal e As DataListCommandEventArgs)
In my btn_Click, but I am now getting a error:
 Unable to cast object of type 'System.EventArgs' to type 'System.Web.UI.WebControls.DataListCommandEventArgs'

Any suggestions? I was working on one example that was giving above as well.
Thanks
0
 
Bob LearnedCommented:
Public Sub BTN_Click(ByVal sender As Object, ByVal e As EventArgs)

End Sub

Bob
0
 
ALawrence007Author Commented:
Bob,
Here is all my code now. I know it is rough and some things should not be done like this, but I am trying to not keep you.
I still get an error:
 System.NullReferenceException: Object reference not set to an instance of an object.
at
Dim index As Integer = Me.DataList1.SelectedItem.ItemIndex
Any Ideas?
Public Sub BTN_Click(ByVal sender As Object, ByVal e As EventArgs)
        Dim index As Integer = Me.DataList1.SelectedItem.ItemIndex
        'You can also get the value from the DataItem:

        Dim name As String = CType(DataList1.SelectedItem.DataItem, DataRowView)("Description")
        Dim ItID As String = CType(DataList1.SelectedItem.DataItem, DataRowView)("IDLabel")
        Dim Conn As New SqlConnectionStringBuilder()
        Conn.DataSource = "Server Name"
        Conn.InitialCatalog = "DB Name"
        Conn.UserID = "User Name"
        Conn.Password = "Password"

        Using connection As New SqlConnection(Conn.ConnectionString)
            Dim cmSQL As SqlClient.SqlCommand
            Dim strSQL As String
            connection.Open()
            Try
                strSQL = "INSERT INTO Cart ([Descrip] ,[ItemID]) VALUES ('" & name & "' ,'" & ItID & "')"
                cmSQL = New SqlClient.SqlCommand(strSQL, connection)
                cmSQL.ExecuteNonQuery()
                connection.Close()
                cmSQL.Dispose()
                connection.Dispose()
            Catch Exp As SqlClient.SqlException
                MsgBox(Exp.Message, MsgBoxStyle.Critical, "SQL Error")
            Catch Exp As Exception
                MsgBox(Exp.Message, MsgBoxStyle.Critical, "General Error")
            End Try
        End Using
    End Sub
0
 
Bob LearnedCommented:
Is this the problem?  Does DataItem = Nothing?

     DataList1.SelectedItem.DataItem

Bob
0
 
ALawrence007Author Commented:
I changed it, but it still gives the same error.
What is the use for the index as well? I don't see using it apart from in the first row?

Lawrence
0
 
Bob LearnedCommented:
Are you saying that DataList1.SelectedItem.DataItem is Nothing?

Ignore the line about index, unless you want to look up a row in a DataTable by index.

Bob
0
 
ALawrence007Author Commented:
That's right,
If I comment out
Dim index As Integer = Me.DataList1.SelectedItem.ItemIndex
It just goes to the next line saying that
Dim name As String = CType(DataList1.SelectedItem.DataItem, DataRowView)("Description")
is not set to an instance of an object
System.NullReferenceException: Object reference not set to an instance of an object.
0
 
Bob LearnedCommented:
After you post-back, are you resetting the DataSource for the DataList?

Bob
0
 
ALawrence007Author Commented:
No,
Here is my code for my Datalistview1:
<asp:DataList ID="DataList1" runat="server" DataSourceID="DS1"
        RepeatColumns="3" RepeatDirection="Horizontal">
        <ItemTemplate>
            &nbsp;<asp:Label ID="ItemLookupCodeLabel" runat="server"
                Text='<%# Eval("ItemLookupCode") %>' />
            <br />
            Item Description:
            <br />
            <asp:Label ID="DescriptionLabel" runat="server"
                Text='<%# Eval("Description") %>' />
            <br />
            Item Price:
            <br />
            <asp:Label ID="PriceLabel" runat="server" Text='<%# Eval("Price") %>' />
            <br />
            <asp:Label ID="IDLabel" runat="server" Text='<%# Eval("ID") %>'
                Visible="False" />
            <br />
            QTY:<br />
            <asp:TextBox ID="TextBox1" runat="server" Height="22px" Width="73px">1</asp:TextBox>
            <br />
            <br />
            <br />
            <asp:Button ID="Button1" runat="server" onclick="BTN_Click"
                Text="Add To Cart" Width="106px" />
            <br />
        </ItemTemplate>
    </asp:DataList>
    <asp:SqlDataSource ID="DS1" runat="server"
        ConnectionString="<%$ ConnectionStrings:2STConn %>" SelectCommand="SELECT [ItemLookupCode], [Description], [Price], [ID] FROM [Item]
Where DepartmentID = @DepartmentID">
        <SelectParameters>
            <asp:parameter DefaultValue="24" Name="DepartmentID" />
        </SelectParameters>
    </asp:SqlDataSource>
0
 
Bob LearnedCommented:
I am curious what is Nothing?  DataList1.SelectedItem?  DataList1.SelectedItem.DataItem?

Bob
0
 
ALawrence007Author Commented:
Here is the full error with Line 10 in red:

Object reference not set to an instance of an object.
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.NullReferenceException: Object reference not set to an instance of an object.

Source Error:


Line 8:      Public Sub BTN_Click(ByVal sender As Object, ByVal e As EventArgs)
Line 9:  
Line 10:         Dim index As Integer = DataList1.SelectedItem.ItemIndex
Line 11:
Line 12:         'You can also get the value from the DataItem:
 
0
 
Bob LearnedCommented:
That sounds like SelectedItem is Nothing.  When you clicked the button was something selected in the DataList?

Bob
0
 
ALawrence007Author Commented:
Well, I thought that if you click the button that means that Item is selected....
Again.... My first datalist.....
0
 
Bob LearnedCommented:
Nope, SelectedItem needs to be set before the button click.

Bob
0
 
ALawrence007Author Commented:
How do I do that?
Going to be away for a while, will answer when I get back.
0
 
Bob LearnedCommented:
When you click on an item in the DataList, SelectedItem should be set, and to check this, you can put an event handler for the DataList.SelectedIndexChanged event.  The page needs to post back for this event to occur.  You can also set the SelectedIndex property to a value.

Bob
0
 
ALawrence007Author Commented:
Bob,

Thank you very much for your patience this far. How do I set the selectedIndexChanged event and how do I point to what we have done so far?

This is what I have now Plus all we have done on top:
Protected Sub DTIndex(ByVal sender As Object, ByVal e As System.EventArgs) Handles DataList1.SelectedIndexChanged


    End Sub

Thanks Bob
0
 
Bob LearnedCommented:
Protected Sub DTIndex(ByVal sender As Object, ByVal e As System.EventArgs) Handles DataList1.SelectedIndexChanged

    Dim isEmpty As Boolean = DataList1.SelectedItem Is Nothing

    End Sub

Put a breakpoint on the 'End Sub' line, and then evaluate what 'isEmpty' equals.

Bob
0
 
ALawrence007Author Commented:
Bob,

I did what you said, but nothing happens.... Put a breakpoint on End sub and when I click on my button in the detailsview I just get a postback. No breakpoint...... I really hope that I am not making a simple mistake...
0
 
Bob LearnedCommented:
Click on something in the DataList, and see if the SelectedIndexChanged is fired.

Bob
0
 
ALawrence007Author Commented:
It is not fired.... My page just does a reload and then displays all the items on the page again. No Selected index change is fired.....
0
 
ALawrence007Author Commented:
Bob,

I restarted my project completely. I added a button to my datalist and then added the following code that you provided at the button click event:
Dim isEmpty As Boolean = DataList1.SelectedItem Is Nothing

isEmpty = to false now... Any suggestions?
0
 
Bob LearnedCommented:
I ran out of time to come up with a sample, so did you find a solution?

Bob
0
 
ALawrence007Author Commented:
No,

I placed that on the back burner for now, because I started looking at another solution for my issue.

I would appretiate if you have more suggestions.

Thanks
0
 
Bob LearnedCommented:
Here is a small example:

<%@ Page Language="VB" AutoEventWireup="false" CodeFile="Default.aspx.vb" Inherits="_Default" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title>Untitled Page</title>
</head>
<body>
    <form id="form1" runat="server">
        <asp:DataList ID="DataList1" runat="server" Style="z-index: 100; left: 32px; position: absolute;
            top: 40px">
            <ItemTemplate>
                <asp:LinkButton ID="Linkbutton1" CommandName="Select" runat="server" Text="Select" />
                <asp:TextBox ID="t1" runat="server" Text='<%# Eval("City") %>' />
            </ItemTemplate>
            <SelectedItemTemplate>
                <asp:TextBox ID="t2" runat="server" Text='<%# Eval("City") %>' Font-Bold="true" />
            </SelectedItemTemplate>
        </asp:DataList>
        <asp:Button ID="Button1" runat="server" Style="z-index: 102; left: 228px; position: absolute;
            top: 72px" Text="Button" />
    </form>
</body>
</html>
0
 
Bob LearnedCommented:
Imports System.Data
Imports System.Data.SqlClient
Imports System.Collections.Generic
Imports System.Diagnostics
Imports System.Drawing

Partial Class _Default
  Inherits System.Web.UI.Page

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

    Dim dt As DataTable = FillDataTable()

    If Not Me.Page.IsPostBack Then
      Me.DataList1.DataSource = dt
      Me.DataList1.DataBind()
    End If

  End Sub

  Private Function FillDataTable() As DataTable
    Dim dt As New DataTable()
    dt.Columns.Add("City")
    dt.Columns.Add("Rating", GetType(Integer))

    Dim dr As DataRow = dt.NewRow()
    dr("City") = "Boston"
    dr("Rating") = 80
    dt.Rows.Add(dr)

    dr = dt.NewRow()
    dr("City") = "New York"
    dr("Rating") = 65
    dt.Rows.Add(dr)

    dr = dt.NewRow()
    dr("City") = "Los Angeles"
    dr("Rating") = 65
    dt.Rows.Add(dr)

    Return dt

  End Function

  Protected Sub Button1_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button1.Click
    If Me.DataList1.SelectedItem IsNot Nothing Then
      Dim index As Integer = Me.DataList1.SelectedIndex
    End If
  End Sub

End Class
0
 
Bob LearnedCommented:
I don't use the DataList very much anymore, so I had to think about this one.  You will notice that there is a <Select> LinkButton that changes the SelectedItem and SelectedIndex for the DataList.

Bob
0
 
ALawrence007Author Commented:
Bob,
This is what I have done with what you gave me. I now get an error: Object Reference not set to an instance of an object.

Protected Sub DataList2_ItemCommand(ByVal source As Object, ByVal e As System.Web.UI.WebControls.DataListCommandEventArgs) Handles DataList2.ItemCommand
        If Me.DataList2.SelectedItem IsNot Nothing Then
            Dim index As Integer = Me.DataList2.SelectedIndex
        End If
        Dim ItemLookupCode As TextBox = e.Item.FindControl("ItemLookupCode")
        Dim ItemPrice As Label = e.Item.FindControl("ItemPrice")
        Try
            Dim SQLString As String = "INSERT INTO DatalistItemEnter (ItemLookupCode ,Price) VALUES (" & _
                     "'" & ItemLookupCode.Text & "', " & _
                     "'" & ItemPrice.Text & "',)"
            SQLDS1.InsertCommand = SQLString
            SQLDS1.Insert()
        Catch ex As Exception
            MsgBox(ex.Message)
        End Try
    End Sub
Any Ideas?
0
 
Bob LearnedCommented:
Is it on one of these 2 lines?

        Dim ItemLookupCode As TextBox = e.Item.FindControl("ItemLookupCode")
        Dim ItemPrice As Label = e.Item.FindControl("ItemPrice")

Bob
0
 
ALawrence007Author Commented:
Dim ItemLookupCode As TextBox = e.Item.FindControl("ItemLookupCode")

This makes me think.........
0
 
ALawrence007Author Commented:
No, I am sorry: it is this line:

Dim SQLString As String = "INSERT INTO DatalistItemEnter (ItemLookupCode ,Price) VALUES (" & _
                     "'" & ItemLookupCode.Text & "', " & _
                     "'" & ItemPrice.Text & "',)"
            SQLDS1.InsertCommand = SQLString
            SQLDS1.Insert()
0
 
ALawrence007Author Commented:
Both these 2 lines where = nothing

Dim ItemLookupCode As TextBox = e.Item.FindControl("ItemLookupCode")
Dim ItemPrice As Label = e.Item.FindControl("ItemPrice")
Me.DataList2.SelectedIndex = -1
0
 
ALawrence007Author Commented:
Bob,
You are not just the TheLearnedOne, but you are the patientone as well. I found my issue:
I took the name of this part of the code: '<%# Eval("City") %>'  and not the actual Label name.
Dim ItemLookupCode As Label = CType(e.Item.FindControl("ItemLookupCodeLabel"), Label)
Dim ItemPrice As Label = CType(e.Item.FindControl("PriceLabel"), Label)

Thank you SO MUCH for helping me with this!!!
0

Featured Post

Vote for the Most Valuable Expert

It’s time to recognize experts that go above and beyond with helpful solutions and engagement on site. Choose from the top experts in the Hall of Fame or on the right rail of your favorite topic page. Look for the blue “Nominate” button on their profile to vote.

  • 19
  • 17
Tackle projects and never again get stuck behind a technical roadblock.
Join Now