ASP.NET

20-Jul-07 5:23

Hi
I was wondering if anyone knows how to write a method

Which will extract all the values set in an mySQL enum field and populate them into a drop down box
e.g.

order_placed | enum('Yes','No')

i would want my drop down box to be populated with the values yes/no

cheers
boyinde

Fred_Smith20-Jul-07 5:43

20-Jul-07 5:43

As far as I am aware, the only way to retrieve enum values is like this:

SHOW COLUMNS FROM table_name LIKE 'enum_column_name'

This would return a data set like this:

column 0: enumColumn (the column name)
column 1: enum('yes','no') (the enum values)
column 2: yes or no, dependong on whether NULL is allowed
...other stuff

So you would need to read the SQL call into a datareader, or something, extract extract column 1 (ie the second column) and parse the value to get what ytou want- then write these to a dataset and bind that to your drowpdown...

Oh what fun!

boyindie20-Jul-07 5:48

20-Jul-07 5:48

sounds like a lot of borin work lol

is it possible to hardcode the values into the <asp:dropdownlist> tag?

Fred_Smith20-Jul-07 5:51

20-Jul-07 5:51

What do you mean? You can hard-code dropdown values, of course....

boyindie20-Jul-07 5:52

20-Jul-07 5:52

Well the values that i am extracting would only be a yes/no

could i hard code these in and pass them into my enum field?

Fred_Smith20-Jul-07 5:57

20-Jul-07 5:57

well of course:

<asp:DropdownList runat="server" id="ddlYesNo">
<asp:ListItem value="Yes">Yes</asp:listItem>
<asp:ListItem value="No">No</asp:listItem>
</asp:DropdownList>

Then just read the postback value and update the database as usual...

boyindie20-Jul-07 5:58

20-Jul-07 5:58

wot a tube forgettin bout list item

would i need to cast them to an enum value or is a string acceptable?

Fred_Smith20-Jul-07 6:01

20-Jul-07 6:01

it's just a string (in your case) - eg:

update table_name set enum_column_name = 'yes'

boyindie20-Jul-07 6:19

20-Jul-07 6:19

Do you know if its possible to rewrite the following code for a textbox in a datagrid to a drop down list

<asp:TextBox runat="server" ID="txtUpdate" Text='<%#convert.tostring(databinder.eval(container.dataitem,"groups")) %>'></asp:TextBox>

<EditItemTemplate>
<asp:DropDownList runat=server id="listUpdate">
<asp:ListItem Value="Admin">Admin</asp:ListItem>
<asp:ListItem Value ="User" >User</asp:ListItem>
</asp:DropDownList>
</EditItemTemplate>

i don't know where i can set the databinder field, so that i can send it to my update statement

Fred_Smith20-Jul-07 6:31

20-Jul-07 6:31

You need to bind it in the ItemDataBound avent of the databgrid.

if your table has a column called "AccessLevel" with (enum?) values "Admin" or "User", then you could use something like the below in this (ItemDataBound) event:

If e.Item.ItemType = ListItemType.EditItem Then
Dim lstUpdate As ListBox = e.Item.FindControl("lstUpdate")
Dim dr As DataRow = e.Item.DataItem.Row, DataRowView
lstUpdate.Items.FindByValue(dr("AccessLevel")).Selected = True
End If

Fred_Smith20-Jul-07 6:37

20-Jul-07 6:37

Previous posty is how you set it, of course, to the value it already has. You read it back and updaate the database in the UpdateCommand event of the datagrid, using:

Dim SelectedID As String = datGridID.DataKeys(e.Item.ItemIndex).ToString

to get teh row ID of teh row in question

Dim lstUpdate As DropdownList = e.Item.FindControl("lstUpdate")

to get the dropdown, then buidl your sql statemetn by it's SelectedValue property

(just realised I used Listbox in earlier post, not DropdownList so you'll need ot amend that)

boyindie21-Jul-07 0:50

21-Jul-07 0:50

HI Fred

I still can't get this bit of code to work

I have this so far

If e.Item.ItemType = ListItemType.EditItem Then
Dim lstUpdate As DropDownList = e.Item.FindControl("lstUpdate")
Dim dr As DataRow = e.Item.DataItem.row
lstUpdate.Items.FindByValue(dr("groups")).Selected = True
End If

I get this error message for "dim dr As DataRow = e.Item.DataItem.row"
Object variable or With block variable not set

You have said to put it as datagridview, but it doesn't seem to like that I am guessin it should be my datagrid variable that goes in here and it still doesn't work Frown | :(

Fred_Smith21-Jul-07 4:23

21-Jul-07 4:23

hm.. we could probably run round forever on this - if you want to email your complete page (html and code-behind) I'll have a look at it for you... blank out your db connectionstring (esp if it's on a public server)

boyindie21-Jul-07 5:45

21-Jul-07 5:45

Heres my code here fred
<code>
Public Sub onEdit(ByVal source As Object, ByVal e As DataGridCommandEventArgs) Handles dgPriv.EditCommand
dgPriv.EditItemIndex = e.Item.ItemIndex
bindGrid()

End Sub

Public Sub oncancel(ByVal source As Object, ByVal e As DataGridCommandEventArgs) Handles dgPriv.CancelCommand
dgPriv.EditItemIndex = -1
bindGrid()

End Sub

Public Sub onUpdate(ByVal source As Object, ByVal e As DataGridCommandEventArgs) Handles dgPriv.UpdateCommand
Dim err As New LiteralControl

If e.Item.ItemType = ListItemType.EditItem Then
Dim lstUpdate As DropDownList = e.Item.FindControl("lstUpdate")
Dim dr As DataRow = e.Item.DataItem

lstUpdate.Items.FindByValue(dr("groups")).Selected = True
End If

Dim SelectedID As String = dgPriv.DataKeys(e.Item.ItemIndex).ToString

Try
Dim cnString As String = "Server=;"

Dim cn As New MySqlConnection(cnString)

'Create mySql command string for passing query or SPROC(Stored Procedure)
Dim cmdString As New MySqlCommand
'Set Command to equal mySql connection, so can pass SQL query
cmdString.Connection = cn
'Set command string to equal SPROC
cmdString.CommandText = "sp_update"
'ONLY PLACE THIS IF SPROC, sets the command to a SPROC
cmdString.CommandType = CommandType.StoredProcedure

Dim param As MySqlParameter

param = cmdString.Parameters.Add("?p_userid", MySqlDbType.Int64)
param.Direction = ParameterDirection.Input
param.Value = dgPriv.DataKeys(e.Item.ItemIndex)

param = cmdString.Parameters.Add("?p_groups", MySqlDbType.VarChar)
param.Direction = ParameterDirection.Input
param.Value = SelectedID

cn.Open()
cmdString.ExecuteNonQuery()
cn.Close()

Catch ex As Exception
err.Text = ex.Message
MsgBox(ex.Message)

End Try

dgPriv.EditItemIndex = -1
bindGrid()

End Sub

</script>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//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">
<div>
<asp:datagrid id="dgPriv" runat="server" BorderWidth="0"
CellPadding="4" Width="100%"
Font-Size="Smaller"
HeaderStyle-HorizontalAlign="Center"
HeaderStyle-Font-Bold="True"
HeaderStyle-BackColor="Navy"
HeaderStyle-ForeColor="White"
datakeyfield ="userId"
AlternatingItemStyle-BackColor="ActiveBorder"
AllowPaging="True"
AllowSorting="True"
AutoGenerateColumns="False" >

<Columns>
<asp:BoundColumn DataField ="userId" HeaderText="User ID" ReadOnly="true"></asp:BoundColumn>
<asp:BoundColumn DataField ="username" HeaderText="User Name" ReadOnly="true"></asp:BoundColumn>
<asp:BoundColumn DataField ="groups" HeaderText="Privelages" ReadOnly="true"></asp:BoundColumn>

<asp:TemplateColumn HeaderText="Update Privelages">
<EditItemTemplate>

<asp:DropDownList runat=server id="listUpdate" DataMember='<%#convert.tostring(databinder.eval(container.dataitem,"groups")) %>' >
<asp:ListItem Value="Admin">Admin</asp:ListItem>
<asp:ListItem Value ="User" >User</asp:ListItem>
</asp:DropDownList>
</EditItemTemplate>
</asp:TemplateColumn>

<asp:EditCommandColumn ButtonType="PushButton" EditText="Change Privelage" UpdateText="Change" CancelText ="cancel" >
</asp:EditCommandColumn>

</Columns>
<PagerStyle HorizontalAlign="Left" />
<AlternatingItemStyle BackColor="ActiveBorder" />
<HeaderStyle BackColor="Navy" Font-Bold="True" ForeColor="White" HorizontalAlign="Center" />

</asp:datagrid>></code>

Fred_Smith21-Jul-07 6:15