Newbie help!

Hi,
I am reading a book on ASP.NET and the following code is from the book
(Teach Yourself ASP.NET in 21 Days by Chris Payne). I have typed the
code in notepad and I am having a problem with it that I hope someone
can help. Basically there is a Submit button which is supposed to
update an Access database, but for some reason it keeps on erroring
out. I have it in a try-catch block and I cannot get the update or
delete to work without error. The following is the code from the book.
Thank you in advance for your help!!!
<%@dotnet.itags.org. Page language="VB" %>
<%@dotnet.itags.org. import namespace="system.data" %>
<%@dotnet.itags.org. 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\Day8\banking.mdb")
sub page_load(sender as object, e as eventargs)
if not page.ispostback then
FillDataGrid()
end if
end sub
sub submit(sender as object, e as eventargs)
'insert new data
dim i, j as integer
dim params(7) 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 add a
value for " & addpanel.controls(i).id & "<p>"
lblmessage.style("Forecolor") = "red"
end if
j = j + 1
end if
next i
if not blngo then
exit sub
end if
dim strsql as string = "INSERT INTO Users (FirstName, LastName,
Address, City, State, Zip, Phone) VALUES (" & _
"'" & params(0) & "'," & _
"'" & params(1) & "'," & _
"'" & params(2) & "'," & _
"'" & params(3) & "'," & _
"'" & params(4) & "'," & _
"'" & params(5) & "'," & _
"'" & params(6) & "')"
executestatement(strsql)
filldatagrid()
end sub
sub dgData_Edit(sender as object, e as datagridcommandeventargs)
filldatagrid(e.item.itemindex)
end sub
sub dgdata_delete(sender as object, e as datagridcommandeventargs)
dim strsql = "DELETE FROM Users WHERE ID = " & e.item.itemindex
executestatement(strsql)
filldatagrid()
end sub
sub dgdata_update(sender as object, e as datagridcommandeventargs)
if updatedatastore(e) then
filldatagrid(-1)
end if
end sub
sub dgdata_cancel(sender as object, e as datagridcommandeventargs)
filldatagrid(-1)
end sub
sub dgdata_pageindexchanged(sender as object, e as
datagridpagechangedeventargs)
dgdata.databind()
end sub
function updatedatastore(e as datagridcommandeventargs) as boolean
dim i,j as integer
dim params(7) 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
else
blngo = false
lblmessage.text = lblmessage.text & "You forgot to enter a
value <p>"
end if
next i
if not blngo then
return false
exit function
end if
dim strsql as string = "UPDATE Users SET " & _
"FirstName = '" & params(0) & "'," & _
"LastName = '" & params(1) & "'," & _
"Address = '" & params(2) & "'," & _
"City = '" & params(3) & "'," & _
"State = '" & params(4) & "'," & _
"Zip = '" & params(5) & "'," & _
"Phone = '" & params(6) & "' " & _
"WHERE ID = " &
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 Users", 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 as string)
'open connection
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><body>
<asp:Label id="lblMessage" runat="server"/>
<form runat="server">
<asp:Datagrid id="dgData" runat="server"
BorderColor="Black" gridlines="vertical"
cellpadding="4" cellspacing="0" width="100%"
autogeneratecolumns="false"
ondeletecommand="dgData_Delete"
onEditcommand="dgData_Edit"
onCancelcommand="dgData_Cancel"
onUpdatecommand="dgData_Update"
onpageindexchanged="dgData_PageIndexChanged">
<columns>
<asp:TemplateColumn headertext="ID">
<itemtemplate>
<asp:Label id="Name" text='<%#
container.dataitem("ID") %>' runat="server"/>
</itemtemplate>
</asp:templatecolumn>
<asp:boundcolumn headertext="First Name"
datafield="FirstName"/>
<asp:boundcolumn headertext="Last Name"
datafield="LastName"/>
<asp:boundcolumn headertext="Address" datafield="Address"/>
<asp:boundcolumn headertext="City" datafield="City"/>
<asp:boundcolumn headertext="State" datafield="State"/>
<asp:boundcolumn headertext="Zip" datafield="Zip"/>
<asp:boundcolumn headertext="Phone" datafield="Phone"/>
<asp:editcommandcolumn edittext="Edit" canceltext="Cancel"
updatetext="Update" headertext="Edit"/>
<asp:buttoncolumn headertext="" text="Delete"
commandname="Delete"/>
</columns>
</asp:datagrid><p>
<asp:panel id="AddPanel" runat="server">
<table>
<tr>
<td width="100" valign="top">First and last name: </td>
<td width="300" valign="top">
<asp:textbox id="tbFName" runat="server"/>
<asp:textbox id="tbLName" runat="server"/>
</td>
</tr>
<tr>
<td width="100" valign="top">Address: </td>
<td width="300" valign="top">
<asp:textbox id="tbAddress" runat="server"/>
</td>
</tr>
<tr>
<td width="100" valign="top">City, State, Zip: </td>
<td width="300" valign="top">
<asp:textbox id="tbCity" runat="server"/>,
<asp:textbox id="tbState" size=2
runat="server"/>
<asp:textbox id="tbZip" size=5 runat="server"/>
</td>
</tr>
<tr>
<td width="100" valign="top">Phone: </td>
<td width="300" valign="top">
<asp:textbox id="tbPhone" size=11 runat="server"/>
</td>
</tr>
<tr>
<td colspan=2 valign="top">
<asp:Button id="btSubmit" text="Add" onclick="Submit"
runat="server"/>
</td>
</tr>
</table>
</asp:panel>
</form>
</body></html>Two things. !
1.) Go and buy visual studio, you really wont go far using notepad (
Although you can get by without it, it will be a nightmare )
2.) You dont say what the error is.?
( OHM ) - One Handed Man
AKA Terry Burns - http://TrainingOn.net
<MPotok@.gmail.com> wrote in message
news:1145338918.804159.312510@.v46g2000cwv.googlegroups.com...
> Hi,
> I am reading a book on ASP.NET and the following code is from the book
> (Teach Yourself ASP.NET in 21 Days by Chris Payne). I have typed the
> code in notepad and I am having a problem with it that I hope someone
> can help. Basically there is a Submit button which is supposed to
> update an Access database, but for some reason it keeps on erroring
> out. I have it in a try-catch block and I cannot get the update or
> delete to work without error. The following is the code from the book.
> Thank you in advance for your help!!!
> <%@. Page language="VB" %>
> <%@. 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\Day8\banking.mdb")
> sub page_load(sender as object, e as eventargs)
> if not page.ispostback then
> FillDataGrid()
> end if
> end sub
> sub submit(sender as object, e as eventargs)
> 'insert new data
> dim i, j as integer
> dim params(7) 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 add a
> value for " & addpanel.controls(i).id & "<p>"
> lblmessage.style("Forecolor") = "red"
> end if
> j = j + 1
> end if
> next i
> if not blngo then
> exit sub
> end if
> dim strsql as string = "INSERT INTO Users (FirstName, LastName,
> Address, City, State, Zip, Phone) VALUES (" & _
> "'" & params(0) & "'," & _
> "'" & params(1) & "'," & _
> "'" & params(2) & "'," & _
> "'" & params(3) & "'," & _
> "'" & params(4) & "'," & _
> "'" & params(5) & "'," & _
> "'" & params(6) & "')"
> executestatement(strsql)
> filldatagrid()
> end sub
> sub dgData_Edit(sender as object, e as datagridcommandeventargs)
> filldatagrid(e.item.itemindex)
> end sub
> sub dgdata_delete(sender as object, e as datagridcommandeventargs)
> dim strsql = "DELETE FROM Users WHERE ID = " & e.item.itemindex
> executestatement(strsql)
> filldatagrid()
> end sub
> sub dgdata_update(sender as object, e as datagridcommandeventargs)
> if updatedatastore(e) then
> filldatagrid(-1)
> end if
> end sub
> sub dgdata_cancel(sender as object, e as datagridcommandeventargs)
> filldatagrid(-1)
> end sub
> sub dgdata_pageindexchanged(sender as object, e as
> datagridpagechangedeventargs)
> dgdata.databind()
> end sub
> function updatedatastore(e as datagridcommandeventargs) as boolean
> dim i,j as integer
> dim params(7) 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
> else
> blngo = false
> lblmessage.text = lblmessage.text & "You forgot to enter a
> value <p>"
> end if
> next i
> if not blngo then
> return false
> exit function
> end if
> dim strsql as string = "UPDATE Users SET " & _
> "FirstName = '" & params(0) & "'," & _
> "LastName = '" & params(1) & "'," & _
> "Address = '" & params(2) & "'," & _
> "City = '" & params(3) & "'," & _
> "State = '" & params(4) & "'," & _
> "Zip = '" & params(5) & "'," & _
> "Phone = '" & params(6) & "' " & _
> "WHERE ID = " &
> 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 Users", 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 as string)
> 'open connection
> 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><body>
> <asp:Label id="lblMessage" runat="server"/>
> <form runat="server">
> <asp:Datagrid id="dgData" runat="server"
> BorderColor="Black" gridlines="vertical"
> cellpadding="4" cellspacing="0" width="100%"
> autogeneratecolumns="false"
> ondeletecommand="dgData_Delete"
> onEditcommand="dgData_Edit"
> onCancelcommand="dgData_Cancel"
> onUpdatecommand="dgData_Update"
> onpageindexchanged="dgData_PageIndexChanged">
> <columns>
> <asp:TemplateColumn headertext="ID">
> <itemtemplate>
> <asp:Label id="Name" text='<%#
> container.dataitem("ID") %>' runat="server"/>
> </itemtemplate>
> </asp:templatecolumn>
> <asp:boundcolumn headertext="First Name"
> datafield="FirstName"/>
> <asp:boundcolumn headertext="Last Name"
> datafield="LastName"/>
> <asp:boundcolumn headertext="Address" datafield="Address"/>
> <asp:boundcolumn headertext="City" datafield="City"/>
> <asp:boundcolumn headertext="State" datafield="State"/>
> <asp:boundcolumn headertext="Zip" datafield="Zip"/>
> <asp:boundcolumn headertext="Phone" datafield="Phone"/>
> <asp:editcommandcolumn edittext="Edit" canceltext="Cancel"
> updatetext="Update" headertext="Edit"/>
> <asp:buttoncolumn headertext="" text="Delete"
> commandname="Delete"/>
> </columns>
> </asp:datagrid><p>
> <asp:panel id="AddPanel" runat="server">
> <table>
> <tr>
> <td width="100" valign="top">First and last name: </td>
> <td width="300" valign="top">
> <asp:textbox id="tbFName" runat="server"/>
> <asp:textbox id="tbLName" runat="server"/>
> </td>
> </tr>
> <tr>
> <td width="100" valign="top">Address: </td>
> <td width="300" valign="top">
> <asp:textbox id="tbAddress" runat="server"/>
> </td>
> </tr>
> <tr>
> <td width="100" valign="top">City, State, Zip: </td>
> <td width="300" valign="top">
> <asp:textbox id="tbCity" runat="server"/>,
> <asp:textbox id="tbState" size=2
> runat="server"/>
> <asp:textbox id="tbZip" size=5 runat="server"/>
> </td>
> </tr>
> <tr>
> <td width="100" valign="top">Phone: </td>
> <td width="300" valign="top">
> <asp:textbox id="tbPhone" size=11 runat="server"/>
> </td>
> </tr>
> <tr>
> <td colspan=2 valign="top">
> <asp:Button id="btSubmit" text="Add" onclick="Submit"
> runat="server"/>
> </td>
> </tr>
> </table>
> </asp:panel>
> </form>
> </body></html>
>
Or download VWD Express Edition for free (For a year anyway). I've been
using it a few ws from a standing start and whilst I code for fun have
built a great KPI dashboard for work which updates continuously throughout
the day...really productive
http://msdn.microsoft.com/vstudio/express/vwd/
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
www.nickhodge.co.uk
nick_hodgeTAKETHISOUT@.zen.co.uk.ANDTHIS
"OHM ( One Handed Man )" <me@.mine.com> wrote in message
news:uKLso9qYGHA.4620@.TK2MSFTNGP04.phx.gbl...
> Two things. !
> 1.) Go and buy visual studio, you really wont go far using notepad (
> Although you can get by without it, it will be a nightmare )
> 2.) You dont say what the error is.?
> --
> ( OHM ) - One Handed Man
> AKA Terry Burns - http://TrainingOn.net
>
> <MPotok@.gmail.com> wrote in message
> news:1145338918.804159.312510@.v46g2000cwv.googlegroups.com...
>
Thanks for your response...I will go out and buy visual studio, I was
waiting until I finished reading this book, but I guess I should get it
sooner than that. I'm sorry for not being more clear...the error
occurs when the Submit button is clicked and it says "Error updating
the database", which is the error message in the Try-Catch block in the
ExecuteStatement function.
I am using an Access database with one table called Users and it has
the following fields:
FirstName
LastName
Address
City
State
Zip
Phone
Take out your try/catch and see what the exception is when it gets
thrown.
Also, don't buy Visual Studio. Either get hold of one of the express
versions, or write your code by hand. Drop notepad though, you can get
free trials of various programmer's editors (I use Ultra Edit studio
for everything), or even just use a freeware editor like notepad2 - you
just want to make sure you've got syntax highlighting, brace matching
etc. Writing code by hand takes longer, but it's the best way to learn
by a long long way.
Thanks for the help...I wound up getting it to work. Taking out the
try/catch block showed me the error "Must use Updatequery table", or
something of the like. I looked around and found that it was a
permissions issue on the folder containing the db!
it now works and i'm on my way with ASP.NET...i'll probably be back to
ask more questions when i get stuck!
Thanks again all!!!