Operation must use an updateable query

Posted on July 29, 2007 by admin

If you’re getting an error when Inserting or Updating an MS Access Database (‘Operation must use an updateable query’), that’s because the ASPNet user account needs the correct permissions on the directory where the database resides
To fix this in Windows 2K:
(Go into Accessories/Computer Management/Local Users & Groups/users
You will see that there’s an ASPNet user. )

Right click on the directory where your database is – choose properties – Then the security tab – then click on permissions.
Add the ASPNet user to the shared section
Then, make sure it has ‘Change’ rights –
Click Apply and you should be working fine then

Sorting/Filtering Fixed DataSet Results in ASP.NET

Posted on July 29, 2007 by admin

OK – here’s the scenario – - you have a stored procedure in place already that returns data in a certain order and you have no time to get a new stored procedure in place to give you what you need and your boss needs it NOW!. What you need is a ‘sub-set’ of these results from the stored procedure – - not the whole thing. So, technically, what you need to do is to somehow, sort, or filter the results this procedure returns to you.

Luckily for you, there is a class with a couple of great properties that helps you do this. Let’s start with a simple scenario from ‘DataSet 101′ – Query/Create and Fill DataSet/Bind to DataGrid for Display:



        Dim strConn as string = "server=(local);uid=yourUID;pwd=yourPWD;database=northwind"
        Dim MySQL as string = "spGetCustomerList"
        Dim MyConn as New SQLConnection(strConn)
        ' (OleDbConnection for Access and other OleDb databases)
        Dim ds as DataSet=New DataSet()
        Dim da as New SQLDataAdapter()
        Dim Cmd as New SQLCommand(MySQL,MyConn)
        ' (OleDbCommand for Access and other OleDb databases)
        Cmd.CommandType=CommandType.StoredProcedure
        MyConn.Open
        da.SelectCommand=cmd
        da.Fill(ds, "Customers")
        myDataGrid.Datasource=ds.Tables("Customers").DefaultView
        myDataGrid.DataBind()

Since you can’t manipulate the SQL query yourself, at first, it would seem that you are ‘up the creek’, so to speak. However, about this time, along comes the ‘DataView’ class, to save the day. The solution is to assign the results returned to a DataView, and using a couple of its built in properties (Sort and RowFilter) to manipulate the data the way you want.
Continues…

Pages: 1 2

Show DB Tables in DropDownList – Select from Table

Posted on July 29, 2007 by admin

Sometimes, we have the need to merely look at the tables from a database, choosing the actual table dynamically. This example shows how to list the Table Names from a SQL Server, in a DropDownList, select from the list, and then shows the results of the table in a DataGrid. (Since these tables are not in separate db in this system, it will show up with ‘NW’ as a prefix to each tablename – naturally, I don’t want to show all of my tables .
For a reference, it uses the NorthWind database, so to use it on your page, just copy the code into a new page and change the connection properties.

<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Data.SQLClient" %>

Sub getstuff(Source as Object, E as EventArgs)
Dim strConn as string = "server=YourServer;uid=UID;pwd=PWD;database=Northwind"
sql = "Select * from [" & ddl.selecteditem.text & "]"
Dim conn as New SQLConnection(strConn)
Dim Cmd as New SQLCommand(sql, conn)
conn.Open()
MyDataGrid.DataSource = Cmd.ExecuteReader(system.data.CommandBehavior.CloseConnection)
MyDataGrid.DataBind()
conn.close
label1.text=""
label1.text=sql
End Sub
</script>

Clear All The Textbox Values (Reset Function)

Posted on July 24, 2007 by admin

In Classic ASP, to clear all the textboxes in a form, to start over, we just had to use a simple html ‘Reset’ button in the form. Sometimes that works in ASP.Net;sometimes it doesn’t.

Here are a couple of ways to do this, iterating through the ASP.Net TextBox controls in a form —
Just create a Reset type subroutine – in that routine, use the following code:
Dim myForm As Control = Page.FindControl(“Form1″)
Dim ctl As Control
For Each ctl In myForm.Controls
If ctl.GetType().ToString().Equals(“System.Web.UI.WebControls.TextBox”) Then
CType(ctl, TextBox).Text = “”
End If
Next ctl

in C# – it would be:
Control myForm = Page.FindControl(“Form1″);
foreach (Control ctl in myForm.Controls)
if(ctl.GetType().ToString().Equals(“System.Web.UI.WebControls.TextBox”))
((TextBox)ctl).Text = “”;

This will clear EVERYTHING from the textboxes – even if you had them pre-populated with data. A VERY simple way to just reset it to the condition at Page_Load time, just do this in the Reset SubRoutine:
Server.Transfer(“YourPageName.aspx”)

Thanks to Andre Colbiornsen and Ian Payne in the ASPFriends Lists.

The Basics of Using SQL in ASP.NET

Posted on July 24, 2007 by admin

As you can see from the title, what we’re exploring here are the basics of SQL – Structured Query Language. Naturally, this won’t, then, be an exhaustive tutorial on SQL – that would require a book – and there are many already out there.

With SQL, we can query databases. It doesn’t matter whether the database is SQL Server, MS Access, MySQL, Oracle or any other. It just requires that the database is sql compliant, which, naturally, most commercially available databases these days, are.

The recommended methd for querying databases, in order to stop injection attacks, is parameterized queries….we won’t go into that in this tutorial, since a tutorial on that subject already exists.

The 4 basic types of queries we’re going to explore are:
Select
Insert
Update
Delete
Along with these 4 types, we’ll address a couple of clause types (Where and Order by)

Select Statement
I’d say that arguably, the most common use of SQL on the web is the SELECT statement. With the SELECT statement, we select data from the database and return the results to the user. The two most common forms are what I call the pure Select statement, and the qualified Select Statement. Here’s an example of a Select Statement in it’s purest, or most simple form:

Select FirstName, LastName, Phone from TableName

This query will return all the Firstname, LastName and Phone number for each record in the database, no matter how many records are in the database.

The other Select Statement I listed is the ‘qualified’ Select Statement. An example of this would use a ‘Where’ clause, like this:

Select FirstName, LastName, Phone from TableName’ Where LastName=’Jones’

As you probably expected, this will only return the FirstName, LastName and Phone for those records where the LastName is ‘Jones’.

Where clauses can contain multiple filtering sections also, using either ‘OR’ or ‘AND’. An example would be

Select FirstName, LastName, Phone from TableName’ Where LastName=’Jones’ OR LastName=’Smith’ OR FirstName=’Jim’

Another example would be:

Select FirstName, LastName, Phone from TableName’ Where LastName=’Jones’ AND FirstName=’Jim’

Insert Statement
Here, we would take user input and INSERT it into the table. We’ve all filled out forms with TextBoxes, RadioButtons, Checkboxes, etc.
Continues…

Pages: 1 2

HashTable – Binding to DropDownList/ListBox

Posted on July 24, 2007 by admin

This example shows how to load a HashTable manually, and then bind it to both a DropDownList and a Listbox. Although binding methods for both these controls are exactly the same, sometimes it helps to actually see it in action.

If you don’t know what a HashTable is, try thinking of it as a multi-dimensional array.

<html>
<head>
<meta name=”GENERATOR” Content=”ASP Express 2.1″>
<title>Untitled</title>
<script language=”VB” runat=”server”>
Sub Page_Load(Source as Object, E as EventArgs)

if not Page.IsPostBack then
Dim myHash As New Hashtable()
myHash.Add(“David”, 1)
myHash.Add(“Christopher”, 2)
myHash.Add(“Lisa”, 3)
myHash.Add(“Shannon”, 4)
myHash.Add(“James”, 5)

ddl.DataSource = myHash
lb1.DataSource = myHash
ddl.DataTextField = “Key”
lb1.DataTextField = “Key”
ddl.DataValueField = “Value”
lb1.DataValueField = “Value”
ddl.DataBind()
lb1.DataBind()
lb1.selectedIndex=0
end if
End Sub

Sub getDDL(Source as Object, E as EventArgs)
lblDDL.text=”DropDownList Text = ” & ddl.selectedItem.text & “<br>DropDownList Value = ” & ddl.selectedItem.value
End Sub

Sub getLB(Source as Object, E as EventArgs)
lblLB.text=”ListBox Text = ” & lb1.selectedItem.text & “<br>ListBoxValue = ” & lb1.selectedItem.value
End Sub
</script>
</head>
<body>
<Form id=”form1″ runat=”server”>
<div align=”center”>
<table width=”75%”> <tr>
<td align=”center” valign=”Top”>
<asp:DropDownList id=”ddl” runat=”server” /> <p>
<asp:Button id=”button1″ Text=”Get Selected” onclick=”getDDL” runat=”server” /><br>
<asp:Label ID=”lblDDL” runat=”server” />
</td>
<td align=”center” valign=”Top”>
<asp:ListBox id=”lb1″ runat=”server” /><p>
<asp:Button id=”button2″ Text=”Get Selected” onclick=”getLB” runat=”server” /><br>
<asp:Label ID=”lblLB” runat=”server” />
</td>
</tr>
</table></div>
</Form>
</body>

Force Button Click by Pressing Enter Key

Posted on July 19, 2007 by admin

Sometimes, you will notice, that, in an ASP.Net form, depending on the circumstances, pressing the ‘Enter’ key to submit the form does not work.

To force this to happen for a particular button on your page, just put this in the Page_Load routine:

Page.RegisterHiddenField(“__EVENTTARGET”, “button1″)

Then, change ‘button1′ to the ID of your particular button. Understand, of course, if your cursor is inside of a MultiLine textbox, the default action of the enter key is to create a new line in the textbox, so, if this basically works anywhere outside of that scenario.

Querystring Results and SQL Statements in ASP.NET

Posted on July 19, 2007 by admin

The basis of using querystrings really hasn’t changed from the days of Classic ASP to ASP.Net, however the way you access them, in ASP.Net, has changed a little. This tutorial will show a simple way to use querystrings, receive them in an ASP.Net page, and then use the received results in an SQL statement.

Let’s start out with a scenario:
We have two pages: Page1.aspx (‘sending’ page) and Page2.aspx (‘receiving’ page). In the ‘sending’ page, we’ll create a querystring, which is assigned to a link to the second page. In this scenario, we’re assigning a querystring that includes a last name, so that, on the ‘receiving’ page , we can create a query based on whatever name is used in the querystring. In this case, we’ll be searching the fictitional ‘Customers’ database for the name passed in the querystring.

There are too many possibilities, in the ‘sending’ page, on how to arrive at the variable, so we won’t go into a scenario concerning that particular item. So we’ll just start out by creating the link on the ‘sending’ page, which will include the querystring :
<a href=”Page2.aspx?lname=Wilson”>Go to Page2.aspx, and search for Customer Details</a>.
Here, you create the name of the querystring any way you want – - we’re using ‘lname’

Next, in Page2.aspx, within the SCRIPT tags, but OUTSIDE of any subroutine or function, you will need to dimension a variable, to which the querystring will be assigned in the Page_Load event:
Dim sLName as String

Continues…

Pages: 1 2

DropDownLists with Current Date and Month

Posted on July 19, 2007 by admin

This code sample shows how to use a for/next loop to populate two dropdownlists – one with 1-however many days in the month, for the date of the month, and one with the Months (value field=1-12). Also, it picks the current of each on Page_load

<html>
<head>
<meta name="GENERATOR" Content="ASP Express 2.1">
<title>DropDownLists with Date and Day</title>
<script language="vb" runat="server">
Sub Page_Load(sender As Object, e As EventArgs)
Dim x As Integer
Dim intDay, intMnth as integer
intDay=Day(now)
intMnth = Month(Now)
Dim MyDate as DateTime
MyDate=DateTime.Now
If Not IsPostBack Then
For x = 1 To 12
ddMonth.Items.Add(New ListItem(MonthName(x), x))
Next
‘ddDay.Items.Clear()
For x = 1 To MyDate.DaysInMonth(MyDate.Year, MyDate.Month)
ddDay.Items.Add(x)
Next
End If
ddDay.selectedIndex=intDay-1
ddMonth.selectedIndex=intMnth-1
end sub
</script>
</head>
<HTML>
<body>
<form id="Form1" method="post" runat="server">
<table>
<tr>
<td align="Left" valign="Top"><b>Day:</b></td><td align="Left" valign="Top"><b>Month:</b</td>
</tr>
<tr>
<td align="Left" valign="Top"><asp:DropDownList ID="ddDay" Runat="server"></asp:DropDownList></td>
<td align="Left" valign="Top"><asp:DropDownList ID="ddMonth" Runat="server" AutoPostBack="True"></asp:DropDownList> </td>
</tr>

</table>
</form>
</body>
</HTML>

Customizing HTML output in ASP.NET

Posted on July 15, 2007 by admin

In This Article:

Specifying Display Properties in the DataGrid and DataList

Customizing Data-Binding Output in Templates

On the Web

We have used the data Web controls a numberof times in code examples in the past two chapters. However, theappearance of the output has left a lot to be desired. Fortunately,making the DataGrid and DataList output more visually pleasing is quitesimple, even for artistically challenged developers like myself!

As we will see in this chapter, both theDataGrid and DataList expose a number of properties that makespecifying these details a breeze. Additionally, we’ll look at howeditors like Visual Studio .NET and the Web Matrix Project makespecifying the appearance of the DataGrid and DataList as easy clickinga few buttons. (Note that the Repeater control does not contain anysort of stylistic properties; the developer is responsible forspecifying any aesthetic properties directly in the HTML markup of theRepeater’s templates.)

Continues…

Pages: 1 2

ASP.NET 101

ASP.NET and MVC Tutorials

Monthly Archives: July 2007

Operation must use an updateable query

Sorting/Filtering Fixed DataSet Results in ASP.NET

Show DB Tables in DropDownList – Select from Table

Clear All The Textbox Values (Reset Function)

The Basics of Using SQL in ASP.NET

HashTable – Binding to DropDownList/ListBox

Force Button Click by Pressing Enter Key

Querystring Results and SQL Statements in ASP.NET

DropDownLists with Current Date and Month

Customizing HTML output in ASP.NET