Listbox entry in Database acces 2003

BramuS · May 17th, 2005, 02:33 AM

Hi i'm making a page wich has acces to a access 2003 database. Everything is displayed well, and you can also enter some things but now i want a droplist and when you select something in there it is not transferred to the database. I don't know what the problem is hope you can help me!

LISTBOX CODE

<tr>
 <td valign="top">Niveau</td>
 <td valign="top">
 <asp:dropdownlist id="tbNiveau" runat="server" AutoPostBack="true">
 <asp:ListItem value="1">Minder Dringen</asp:ListItem>
 <asp:ListItem value="2">Dringend</asp:ListItem>
 <asp:ListItem value="3">Zeer Dringend</asp:ListItem>
 </asp:DropDownList>
 &nbsp Kies tussen Minder Dringend, Dringend of Zeer Dringend
 </td>
 </tr>

and if you need anymore code please ask. Because i'm kinda n00b in this and doing this by a book, but there is nothing listed about the "listbox" so i did make it with some other tut's in the book

thx
BramuS

--------------------------------------------------
"Want more trouble in you're life?? Buy a PC"

Imar · May 17th, 2005, 08:33 AM

Hi Bram,

How are you retrieving the value of the ListBox on the server?

You should do something like

myValue = tbNiveau.SelectedValue

to get at the value of the selected item. Then you can use myValue in your data access code.

HtH,

Imar

BramuS · May 17th, 2005, 09:15 AM

I don't know how i'm doing that, i just used a book but i think i won't retrieve the data from the list good. So can you help me with the code??

Code:

        dim strSQL as string = "UPDATE storingen SET " & _
            "KlantID = '" & params(0) & "'," & _
            "Catagorie = '" & params(1) & "'," & _
            "Omschrijving = '" & params(2) & "'," & _
            "Tijd = '" & params(3) & "'," & _
            "Aangemeld = '" & params(4) & "'," & _
            "Niveau = '" & params(5) & "'" & _
            "WHERE UserID = " & Ctype(e.Item.Cells(0). _
                Controls(1), Label).text

This wil be wrong i think and maybe this:

Code:

        dim strSQL as string = "INSERT INTO storingen " & _
            "(KlantID, Catagorie, Omschrijving, " & _
            "Tijd, Aangemeld, Niveau) VALUES (" & _
            "'" & params(0) & "'," & _
            "'" & params(1) & "'," & _
            "'" & params(2) & "'," & _
            "'" & params(3) & "'," & _
            "'" & params(4) & "'," & _
            "'" & params(5) & "')"

And this also

Code:

        dim strSQL as string = "UPDATE storingen SET " & _
            "KlantID = '" & params(0) & "'," & _
            "Catagorie = '" & params(1) & "'," & _
            "Omschrijving = '" & params(2) & "'," & _
            "Tijd = '" & params(3) & "'," & _
            "Aangemeld = '" & params(4) & "'," & _
            "Niveau = '" & params(5) & "'" & _
            "WHERE UserID = " & Ctype(e.Item.Cells(0). _
                Controls(1), Label).text

Hope you can help me with this piece of code :) I can also mail the compleet code, it is about 270 sentences so a bit large to post it all here or can i just post?

--------------------------------------------------
"Want more trouble in you're life?? Buy a PC"

Imar · May 17th, 2005, 02:44 PM

Where does params() come from?

Usually, when you build up a Sql statement, you need to add the fieldname and then insert the values, enclosed in single quotes for text and date datatypes, like this:

INSERT INTO MyTable (SomeNumber, SomeText) VALUES (" & myInt & ", '" & myStringValue & "'")

In your case, you need to replace myStringValue with tbNiveau.SelectedValue, like this:

INSERT INTO MyTable (SomeNumber, SomeText) VALUES (' & myInt & ", '" & tbNiveau.SelectedValue & "'")

Does this help at all?

If not, can you show us where in the code the params array params() is build up?

Cheers,

Imar
---------------------------------------
Imar Spaanjaars
Everyone is unique, except for me.

BramuS · May 18th, 2005, 05:30 AM

Well i'm posting the compleet code because i don't really know what you are saying by that params. I'm just a beginner in ASP.NET so i don't know what you mean. Hope you can help me out with this guys.

Thanks
Bram

Code:

<%@ Page Language="VB" Debug="true" %>
<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Data.OleDb" %>

<script runat="server">
    'declare connection 
    dim Conn as new OleDbConnection( _
        "Provider=Microsoft.Jet.OLEDB.4.0;" & _
        "Data Source=C:\Inetpub\wwwroot\test\login.mdb")

    sub Page_Load(obj as Object, e as EventArgs)
        if Not Page.IsPostBack then
            FillDataGrid()
        end if
    end sub



    sub Submit(obj as Object, e as eventargs)
        'insert new data
        dim i, j as integer
        dim params(8) as string
        dim strText as string
        dim blnGo as boolean = true

        j = 0

        for i = 0 to AddPanel.Controls.Count - 1
            if AddPanel.controls(i).GetType Is _
                GetType(TextBox) then
                strText = Ctype(AddPanel.Controls(i), _
                    TextBox).Text
                if strText <> "" then
                    params(j) = strText
                else
                    blnGo = false
                    lblMessage.Text = lblMessage.Text & _
                        "You forgot to enter a value for " & _
                        AddPanel.Controls(i).ID & "<p>"
                    lblMessage.Style("ForeColor") = "Red"
                end if
                j = j + 1
            end if
        next

        if not blnGo then
            exit sub
        end if

        dim strSQL as string = "INSERT INTO storingen " & _
            "(KlantID, Catagorie, Omschrijving, " & _
            "Tijd, Aangemeld, Niveau) VALUES (" & _
            "'" & params(0) & "'," & _
            "'" & params(1) & "'," & _
            "'" & params(2) & "'," & _
            "'" & params(3) & "'," & _
            "'" & params(4) & "'," & _
            "'" & params(5) & "')"

        ExecuteStatement(strSQL)

        FillDataGrid()
    end sub

    sub dgData_Edit(obj as object, e as DataGridCommandEventArgs)
        FillDataGrid(e.Item.ItemIndex)
    end sub

'DELETE FUNCTIE WELKE NIET ACTIEF IS
'DELETE FUNCTIE WELKE NIET ACTIEF IS
'DELETE FUNCTIE WELKE NIET ACTIEF IS
'DELETE FUNCTIE WELKE NIET ACTIEF IS
'DELETE FUNCTIE WELKE NIET ACTIEF IS
'DELETE FUNCTIE WELKE NIET ACTIEF IS
'DELETE FUNCTIE WELKE NIET ACTIEF IS
'DELETE FUNCTIE WELKE NIET ACTIEF IS

    sub dgData_Update(obj as object, e as DataGridCommandEventArgs)
        if UpdateDataStore(e) then
            FillDataGrid (-1)
        end if
    end sub

    sub dgData_Cancel(obj as object, e as DataGridCommandEventArgs)
        FillDataGrid(-1)
    end sub

    sub dgData_PageIndexChanged(obj as Object, e as DataGridPageChangedEventArgs)
        dgData.DataBind()
    end sub

    function UpdateDataStore(e as DataGridCommandEventArgs) as boolean

        dim i,j as integer
        dim params(8) as string
        dim strText as string
        dim blnGo as boolean = true

        j = 0

        for i = 1 to e.Item.Cells.Count - 3
            strText = Ctype(e.Item.Cells(i).Controls(0), _
                TextBox).Text
            if strText <> "" then
                params(j) = strText
                j = j + 1 
            else
                blnGo = false
                lblMessage.Text = lblMessage.Text & _
                    "You forgot to enter a value<p>"
            end if
        next

        if not blnGo then
            return false    
            exit function
        end if

        dim strSQL as string = "UPDATE storingen SET " & _
            "KlantID = '" & params(0) & "'," & _
            "Catagorie = '" & params(1) & "'," & _
            "Omschrijving = '" & params(2) & "'," & _
            "Tijd = '" & params(3) & "'," & _
            "Aangemeld = '" & params(4) & "'," & _
            "Niveau = '" & params(5) & "'" & _
            "WHERE UserID = " & Ctype(e.Item.Cells(0). _
                Controls(1), Label).text

        ExecuteStatement(strSQL)
        return blnGo
    end function

    sub FillDataGrid(optional EditIndex as integer=-1)
    'open connection
    dim objCmd as new OleDbCommand _
        ("select * from storingen", Conn)
    dim objReader as OleDbDataReader

    try
        objCmd.Connection.Open()
        objReader = objCmd.ExecuteReader()
    catch ex as Exception
        lblMessage.Text = "Error retrieving from the " & _
            "database."
    end try

    dgData.DataSource = objReader
    if not EditIndex.Equals(Nothing) then
        dgData.EditItemIndex = EditIndex
    end if

    dgData.DataBind()

    objReader.Close 
    objCmd.Connection.Close()

    end sub

    function ExecuteStatement(strSQL)
        dim objCmd as new OleDbCommand(strSQL, Conn)

        try
            objCmd.Connection.Open()
            objCmd.ExecuteNonQuery()
        catch ex as Exception
            lblMessage.Text = "Error updating the database!"
        end try

        objCmd.Connection.Close()
    end function
</script>

<html><title>Storing Meldings Formulier - [Theta-Comm]</title><body>
    <asp:label id="lblMessage" runat="server"/>

    <form runat="server">
        <asp:DataGrid id="dgData" runat="server"
            BorderColor="black" GridLines="vertical, horizontal"
            cellpadding="4" cellspacing="0" width="100%"
            AutoGenerateColumns="False"
            OnEditCommand="dgData_Edit"
            OnCancelCommand="dgData_Cancel"
            OnUpdateCommand="dgData_Update"
            OnPageIndexChanged="dgData_PageIndexChanged" >

            <Columns>
                <asp:TemplateColumn HeaderText="ID">
                    <ItemTemplate>
                        <asp:Label id="Name" runat="server"
                        Text='<%# Container.DataItem ("StoringsID") %>' />
                    </ItemTemplate>
                </asp:TemplateColumn>


                <asp:BoundColumn HeaderText="KlantID"
                    DataField="KlantID" />
                <asp:BoundColumn HeaderText="Catagorie"
                    DataField="Catagorie" />
                <asp:BoundColumn HeaderText="Omschrijving Storing"
                    DataField="Omschrijving" />
                <asp:BoundColumn HeaderText="Tijd Aanmelding"
                    DataField="Tijd" />
                <asp:BoundColumn HeaderText="Aangemeld door"
                    DataField="Aangemeld" />
                <asp:BoundColumn HeaderText="Niveau"
                    DataField="Niveau" />

                <asp:EditCommandColumn
                    EditText="Edit"
                    CancelText="Cancel"
                    UpdateText="Update"
                    HeaderText="Edit" />

            </Columns>
      </asp:DataGrid><p>



        <asp:Panel id="AddPanel" runat="server">
            <table>
            <tr>
                <td width="100" valign="top">KlantNaam</td>
                <td width="300" valign="top">
                    <asp:TextBox id="tbKlantID" runat="server"/>
                </td>
            </tr>
            <tr>
                <td valign="top">Catagorie</td>
                <td valign="top">
                    <asp:textBox id= "tbCatagorie" runat="server" />
                </td>
            </tr>
            <tr>
                <td valign="top">Omschrijving storing</td>
                <td valign="top">
                    <asp:TextBox id="tbOmschrijving"
                        runat="server" />
                </td>
            </tr>
            <tr>                
                <td valign="top">Tijd en datum Aanmelding</td>
                <td valign="top">
                    <asp:TextBox id="tbTijd" runat="server" />

                </td>
            </tr>
            <tr>                
                <td valign="top">Aangemeld door</td>
                <td valign="top">
                    <asp:TextBox id="tbAangemeld" runat="server" />
                </td>
            </tr>
            <tr>                
                <td valign="top">Niveau</td>
                <td valign="top">
                  <asp:DropDownList id="tbNiveau" runat="server" AutoPostBack="False">
                    <asp:ListItem value="1">Minder Dringend</asp:ListItem>
                            <asp:ListItem value="2">Dringend</asp:ListItem>
              <asp:ListItem value="3">Zeer Dringend</asp:ListItem>
          </asp:DropDownList>
                    &nbsp Kies tussen <i><b>Minder Dringend, Dringend </i></b>of<b><i> Zeer Dringend</b></i><p>    
                </td>
            </tr>

            <tr>
                <td colspan="2" valign="top" align="left">
                    <asp:button id="btSubmit" runat="server"
                        text="Toevoegen" onclick="Submit" />
                </td>
            </tr>
            </table>
        </ASP:Panel>
    </form>

</body></html>

--------------------------------------------------
"Want more trouble in you're life?? Buy a PC"

Imar · May 19th, 2005, 01:05 PM

Zin wel, tijd helaas bijna niet.... ;)

Anyway, I think the technique you (or the author) have chosen for this example makes things pretty difficult. From what I can digest from your code, this is a generic page that loops through all the controls in your page, and if it's a text box, tries to get its text and store it in an array. Not necessarily the setup I would choose.

You could change the For loop

for i = 0 to AddPanel.Controls.Count - 1
            if AddPanel.controls(i).GetType Is _
                GetType(TextBox) then
....
                end if
                j = j + 1
            end if
        next

so that it not only checks for TextBox controls but also for DropDownList controls of which you can get the SelectedValue.

However, IMO, it's probably better to start all over, and create a page that gets values from controls directly, e.g.:

Dim firstName As String = txtFirstName.Text
Dim state As String = lstState.SelectedValue

That's much easier to read, understand and maintain. I guess you'll need to do some reading to get acquainted with concepts like Data Access, form controls, postbacks and so on. Unfortunately, there is too much involved here to simply explain in a forum post.
Recommended sites: www.asp.net, www.4guysfromrolla.com, msdn.microsoft.com and, of course www.google.com

Maybe not the answer you were hoping for, but it's the best I can give at this moment.

Cheers,

Imar
---------------------------------------
Imar Spaanjaars
Everyone is unique, except for me.
While typing this post, I was listening to: Spanish Castles In Space by The Orb (Track 5 from the album: The Orb's Adventures Beyond The Ultraworld - Disc 1) What's This?

BramuS · May 23rd, 2005, 03:13 AM

is there not just an easier way to fix this problem as totally rewrite the code? Because i'm not that good in ASP.NET is just wrote this code with a book as example!

--------------------------------------------------
"Want more trouble in you're life?? Buy a PC"

Imar · May 23rd, 2005, 01:01 PM

Like I said earlier: there is. You need to rewrite the loop somehow. I haven't tested this, but something like this may work:

for i = 0 to AddPanel.Controls.Count - 1
 strText = ""
 if AddPanel.controls(i).GetType Is GetType(TextBox) Then
 strText = Ctype(AddPanel.Controls(i), TextBox).Text
 end if
if AddPanel.controls(i).GetType Is GetType(ListBox) Then
 strText = Ctype(AddPanel.Controls(i), ListBox).SelectedValue
 end if
 if strText <> "" Then
 params(j) = strText
 else
 blnGo = false
 lblMessage.Text = lblMessage.Text & "You forgot to enter a
 value for " & _
 AddPanel.Controls(i).ID & ""
 lblMessage.Style("ForeColor") = "Red"
 end if
 j = j + 1
next

However, like I also said earlier, this is far from ideal. You're making it much harder on yourself. You could rewrite the entire form in a few hours, or even less, even when you're not very experienced. That also allows you to implement "best practices for ASP.NET, like the validator controls, instead of relying on custom error message building.

What book is this from??

Imar
---------------------------------------
Imar Spaanjaars
Everyone is unique, except for me.

BramuS · May 24th, 2005, 05:20 AM

It is "ASP.NET in 21 dagen"

I've added the code and when submitting something i get an error:

Exception Details: System.IndexOutOfRangeException: Index was outside the bounds of the array.

Source Error:

Line 36: end if
Line 37: if strText <> "" Then
Line 38: params(j) = strText <-- This is wrong <--
Line 39: else
Line 40: blnGo = false

Anyway thx for you're help so far!

--------------------------------------------------
"Want more trouble in you're life?? Buy a PC"

Imar · May 24th, 2005, 11:32 AM

You probably need to resize the array, so it can hold more variables:

Dim params(8) As String

If you expect the array to hold more fields, create a larger array:

Dim params(9) As String

You should also move the J = J + 1 code inside the If statement, so J is only incremented when you actually found a useful control.

Imar
---------------------------------------
Imar Spaanjaars
Everyone is unique, except for me.