Newbie help!!!

[MPotok]

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">
<aspatagrid 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>

<aspanel 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"/>&nbsp;
<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" align="right">
<asp:Button id="btSubmit" text="Add" onclick="Submit"
runat="server"/>
</td>
</tr>
</table>
</aspanel>
</form>
</body></html>

 

[OHM \( One Handed Man \)]

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.?

 

[Nick Hodge]

Or download VWD Express Edition for free (For a year anyway). I've been
using it a few weeks 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
(e-mail address removed)

 

[MPotok]

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

 

[Flinky Wisty Pomm]

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.

 

[MPotok]

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!!!