HOW to ADD,MODIFY, and DELETE RECORD on the table/database connected (via ODBC) on Expression page??

Tue, 30 Jun 2009 18:40:31 Z

Hi All,

On the expression web page, i created default.aspx page and insert sqldatasource, then used GridView to connect a .dbf database thru ODBC. Now I can have a web-based view of the database remotely.

But if i want to create page to be able to insert, modify/edit, and delete the record of the connected database, how to do it in Expression Web 2??

Any one did that or has some idea of that? I do appreciate it. Thanks much.

Regards,

HOW to ADD,MODIFY, and DELETE RECORD on the table/database connected (via ODBC) on Expression page??

Tue, 30 Jun 2009 18:56:44 Z

Take a look at the tutorials in my signature.

ClarkNK, QVP
HomePage Doctor CHO
HomePageDoctor.com -- Expression Web database tutorials
Ownertrades.com -- Created with FP, Access, Bots and Wizards
MyNumbersTracker.com -- Created with Expression, VWDExress, SQL Express, and ASP.NET.

HOW to ADD,MODIFY, and DELETE RECORD on the table/database connected (via ODBC) on Expression page??

Tue, 30 Jun 2009 19:02:15 Z

There are a ton of tutorials regarding Asp.Net development. You can click the Learn tab at the top of this page, then select Expression Web. There are tutorials for working with Asp.Net data controls.

You could also downloaad and install Visual Web Develope,r and use it for your database work; that's one of its forte's. There are some great tutorials for working with data at: http://www.asp.net/learn/

In EW, if you already have a GridView control, you can modify the DataSource to enable the Insert, Edit, and Delete methods. In design view, click the little arrow at the top-right corner of the control (GridView Tasks), Select your DataSource and modify it through the wizard. Basically, what the wizard does is create the SQL statments that enable the methods you want to use.

Before doing the above, I strongly encourage you to review the tutorials so that you really understand what it is you are attempting to accomplish. It will dividends down the road.

-Preston

Columbia, CA. USA http://www.gildedmoon.com

HOW to ADD,MODIFY, and DELETE RECORD on the table/database connected (via ODBC) on Expression page??

Wed, 01 Jul 2009 15:37:29 Z

Now i have some specific question that i cannot find it on tutorials... about the SQL statements

When i tried to configure my datasource, cause i am using ODBC connection to .DBF database not normal .mdb Access database, in the wizard, it only has the page that let me "Define Custom Statements or Stored Procedures" with no other options like for .mdb database i guess. I must write my own sql statement for "SELECT", "UPDATE", "INSERT", "DELETE" and the "Query Builder" doesn't work.

So i am very confused here cause i don't know what sql statements to put under those four tabs.
If for SELECT, it might be simple as "SELECT * FROM databasename", but for UPDATE? i want to let web admin update any data under any column he might change in one row at a time, so how can i put that sql??

and for "INSERT", now i put:

INSERT INTO DATABASENAME (column 1, 2, 3, 4, 5) VALUES (" "," ", " ", " ", " ") Then when i run it, it shows up it insert a blank row..cause there's no specific values..

and for Delete, i want to set like admin can delete any one row at a time, but i don't how to put the sql

cry for help :( Much Thanks

HOW to ADD,MODIFY, and DELETE RECORD on the table/database connected (via ODBC) on Expression page??

Wed, 01 Jul 2009 16:45:30 Z

I use a DetailsView Asp.Net control that uses a SQLDataSource connected to a *mdf file. In the wizard for that control, you can enable Insert, Edit, and Delete methods. Note that I am using a SQLDataSource; not ODBC. IIRC, the Jet dB Engine (Used by ODBC and Access mdb files) uses SQL syntax that is different from the SQL statements used by the SQL Server engine. Therefore, the query builders in the SQLDataSource and ODBC wizards in EW will have different options.

Mike Wachal (MSFT), over at the SQL Server Express forum, posted this helpful paragraph yesterday:

"If you're new to using SQL Server, you may want to check out the Absolute Beginners video series, the first video is at http://www.facultyresourcecenter.com/curriculum/pfv.aspx?ID=6686 and you'll find links to the remaining videos. These tutorials will walk you through the basics of how to work with SQL Express. Many of the concepts will be familiar to you if you've worked with Access/Jet, but this will give you examples of how things work differently in SQL Server."

I hope this helps get things sorted out for you.

-Preston

Columbia, CA. USA http://www.gildedmoon.com

HOW to ADD,MODIFY, and DELETE RECORD on the table/database connected (via ODBC) on Expression page??

Wed, 01 Jul 2009 19:04:21 Z

I updated "INSERT" code to become :

INSERT INTO MAIT2(NAME, CONTACT, ACCOUNT,MAILTO, MAILCC, MAILFROM, SERVER, PORT, USER, PASS, AUTH)
VALUES(?,?,?,?,?,?,?,?,?,?,?)

AND it WORKS!

But i tried to update "UPDATE" code like:

UPDATE MAIT2 SET NAME=?, CONTACT=?, ACCOUNT=?, MAILTO=?, MAILCC=?, MAILFROM=?, SERVER=?, PORT=?, USER=?, PASS=?, AUTH=?
WHERE NAME=? AND CONTACT=?

It says: ERROR [07002ࡀ] [Microsoft][ODBC dBASE Driver]COUNT field incorrect

UPDATE MAIT2 SET NAME=?, CONTACT=?, ACCOUNT=?, MAILTO=?, MAILCC=?, MAILFROM=?, SERVER=?, PORT=?, USER=?, PASS=?, AUTH=?
WHERE NAME=?, CONTACT=?, ACCOUNT=?, MAILTO=?, MAILCC=?, MAILFROM=?, SERVER=?, PORT=?, USER=?, PASS=?, AUTH=?

Still says: ERROR [07002ࡀ] [Microsoft][ODBC dBASE Driver]COUNT field incorrect

If I changed UPDATE code to be:

UPDATE MAIT2 SET NAME=?, CONTACT=?, ACCOUNT=?, MAILTO=?, MAILCC=?, MAILFROM=?, SERVER=?, PORT=?, USER=?, PASS=?, AUTH=?

It says: ERROR [HYS12ࡀ] [Microsoft][ODBC dBASE Driver] Index not found.

HOW to ADD,MODIFY, and DELETE RECORD on the table/database connected (via ODBC) on Expression page??

Thu, 02 Jul 2009 03:52:46 Z

If you look at the tutorial

http://www.homepagedoctor.com/expressiontutorials/MultiTableRelationalDBSetup.htm

You will find what the insert and update statements look like for sql database. Although that is not what you are using, compare your Insert statement to the tutorial Insert statement and you can see how the formats of the two statements are analagous.

Once you see that, you can figure out how the Update query should look in your case by using the same analogies as for the Insert statement.

It looks as if you are missing a WHERE statement that defines the ID of the record being updated.

HOW to ADD,MODIFY, and DELETE RECORD on the table/database connected (via ODBC) on Expression page??

Thu, 02 Jul 2009 13:51:50 Z

THANK YOU SO MUCH ClarkNK, the tutorial is really helpful!
but still got some errors

In 'Tag Properties' for my SqlDataSource, in UpdateQuery, i set Update Command as

UPDATE [MAIT2] SET [CONTACT] = @CONTACT, [ACCOUNT] = @ACCOUNT, [MAILTO] = @MAILTO,[MAILCC] = @MAILCC,[MAILFROM] = @MAILFROM, [SERVER] = @SERVER, [PORT] = @PORT, [USER] = @USER, [PASS] = @PASS, [AUTH] = @AUTH WHERE [NAME] = @NAME

or

UPDATE [MAIT2] SET [NAME] = @NAME, [CONTACT] = @CONTACT, [ACCOUNT] = @ACCOUNT, [MAILTO] = @MAILTO,[MAILCC] = @MAILCC,[MAILFROM] = @MAILFROM, [SERVER] = @SERVER, [PORT] = @PORT, [USER] = @USER, [PASS] = @PASS, [AUTH] = @AUTH WHERE [NAME] = @NAME

Then created Parameters from NAME, CONTACT, to AUTH, for every column, and set NAME value as GridView1.SelectedValue, but when i tried to edit and update the table after i run it, it keeps saying:

ERROR [07002] [Microsoft][ODBC dBASE Driver] Too few parameters. Expected 11. body {font-family:"Verdana";font-weight:normal;font-size: .7em;color:black;} p {font-family:"Verdana";font-weight:normal;color:black;margin-top: -5px} b {font-family:"Verdana";font-weight:bold;color:black;margin-top: -5px} H1 { font-family:"Verdana";font-weight:normal;font-size:18pt;color:red } H2 { font-family:"Verdana";font-weight:normal;font-size:14pt;color:maroon } pre {font-family:"Lucida Console";font-size: .9em} .marker {font-weight: bold; color: black;text-decoration: none;} .version {color: gray;} .error {margin-bottom: 10px;} .expandable { text-decoration:underline; font-weight:bold; color:navy; cursor:hand; }

ERROR [07002] [Microsoft][ODBC dBASE Driver] Too few parameters. Expected 11.

Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.Data.Odbc.OdbcException: ERROR [07002] [Microsoft][ODBC dBASE Driver] Too few parameters. Expected 11.

Source Error:

An unhandled exception was generated during the execution of the  current web request. Information regarding the origin and location of the  exception can be identified using the exception stack trace below.

Stack Trace:

[OdbcException (0x80131937): ERROR [07002] [Microsoft][ODBC dBASE Driver] Too few parameters. Expected 11.]
   System.Data.Odbc.OdbcConnection.HandleError(OdbcHandle hrHandle, RetCode retcode) +1155538
   System.Data.Odbc.OdbcCommand.ExecuteReaderObject(CommandBehavior behavior, String method, Boolean needReader, Object[] methodArguments, SQL_API odbcApiMethod) +1147
   System.Data.Odbc.OdbcCommand.ExecuteReaderObject(CommandBehavior behavior, String method, Boolean needReader) +61
   System.Data.Odbc.OdbcCommand.ExecuteNonQuery() +92
   System.Web.UI.WebControls.SqlDataSourceView.ExecuteDbCommand(DbCommand command, DataSourceOperation operation) +386
   System.Web.UI.WebControls.SqlDataSourceView.ExecuteUpdate(IDictionary keys, IDictionary values, IDictionary oldValues) +325
   System.Web.UI.DataSourceView.Update(IDictionary keys, IDictionary values, IDictionary oldValues, DataSourceViewOperationCallback callback) +92
   System.Web.UI.WebControls.GridView.HandleUpdate(GridViewRow row, Int32 rowIndex, Boolean causesValidation) +907
   System.Web.UI.WebControls.GridView.HandleEvent(EventArgs e, Boolean causesValidation, String validationGroup) +704
   System.Web.UI.WebControls.GridView.OnBubbleEvent(Object source, EventArgs e) +95
   System.Web.UI.Control.RaiseBubbleEvent(Object source, EventArgs args) +37
   System.Web.UI.WebControls.GridViewRow.OnBubbleEvent(Object source, EventArgs e) +123
   System.Web.UI.Control.RaiseBubbleEvent(Object source, EventArgs args) +37
   System.Web.UI.WebControls.LinkButton.OnCommand(CommandEventArgs e) +118
   System.Web.UI.WebControls.LinkButton.RaisePostBackEvent(String eventArgument) +135
   System.Web.UI.WebControls.LinkButton.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument) +10
   System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument) +13
   System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData) +175
   System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +1565

and i think for my .dbf database, there is no ID field

HOW to ADD,MODIFY, and DELETE RECORD on the table/database connected (via ODBC) on Expression page??

Thu, 02 Jul 2009 14:45:38 Z

Here is the code, i set CHAR as type for each parameter, maybe not that correct, but if i don't set type, it still has the same error msg: Too few parameters, expected 11. Sure it means the 11 fields in my database/table, and i am sure i didn't spell wrong or miss any field i think..cry :(

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<%@ Page Language="C#" %>
<html dir="ltr" xmlns="http://www.w3.org/1999/xhtml">

<head runat="server">
<meta content="text/html; charset=utf-8" http-equiv="Content-Type" />
<title>Untitled 1</title>
</head>

<body>

<form id="form1" runat="server">
   <asp:SqlDataSource id="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:ConnectionString3 %>" DeleteCommand="DELETE FROM MAIT2 WHERE NAME=@NAME " ProviderName="<%$ ConnectionStrings:ConnectionString3.ProviderName %>" SelectCommand="SELECT * FROM MAIT2" UpdateCommand="UPDATE [MAIT2] SET [CONTACT] =@CONTACT, [ACCOUNT] = @ACCOUNT, [MAILTO] = @MAILTO, [MAILCC] = @MAILCC, [MAILFROM] = @MAILFROM, [SERVER] = @SERVER, [PORT] = @PORT, [USER] = @USER, [PASS] = @PASS, [AUTH] = @AUTH WHERE [NAME] = @NAME ">
       <DeleteParameters>
           <asp:controlparameter ControlID="GridView1" Name="NAME" PropertyName="SelectedValue" Type="Char" />
       </DeleteParameters>
       <UpdateParameters>
           <asp:parameter Name="CONTACT" Type="Char" />
           <asp:parameter Name="ACCOUNT" Type="Char" />
           <asp:parameter Name="MAILTO" Type="Char" />
           <asp:parameter Name="MAILCC" Type="Char" />
           <asp:parameter Name="MAILFROM" Type="Char" />
           <asp:parameter Name="SERVER" Type="Char" />
           <asp:parameter Name="PORT" Type="Char" />
           <asp:parameter Name="USER" Type="Char" />
           <asp:parameter Name="PASS" Type="Char" />
           <asp:parameter Name="AUTH" Type="Char" />
           <asp:controlparameter ControlID="GridView1" Name="NAME" PropertyName="SelectedValue" Type="Char" />
       </UpdateParameters>
   </asp:SqlDataSource>
   <asp:GridView id="GridView1" runat="server" AutoGenerateColumns="False" DataKeyNames="NAME" DataSourceID="SqlDataSource1">
       <Columns>
           <asp:commandfield ShowEditButton="True">
           </asp:commandfield>
           <asp:commandfield ShowDeleteButton="True">
           </asp:commandfield>
           <asp:boundfield DataField="NAME" HeaderText="NAME" ReadOnly="True" SortExpression="NAME">
           </asp:boundfield>
           <asp:boundfield DataField="CONTACT" HeaderText="CONTACT" SortExpression="CONTACT">
           </asp:boundfield>
           <asp:boundfield DataField="ACCOUNT" HeaderText="ACCOUNT" SortExpression="ACCOUNT">
           </asp:boundfield>
           <asp:boundfield DataField="MAILTO" HeaderText="MAILTO" SortExpression="MAILTO">
           </asp:boundfield>
           <asp:boundfield DataField="MAILCC" HeaderText="MAILCC" SortExpression="MAILCC">
           </asp:boundfield>
           <asp:boundfield DataField="MAILFROM" HeaderText="MAILFROM" SortExpression="MAILFROM">
           </asp:boundfield>
           <asp:boundfield DataField="SERVER" HeaderText="SERVER" SortExpression="SERVER">
           </asp:boundfield>
           <asp:boundfield DataField="PORT" HeaderText="PORT" SortExpression="PORT">
           </asp:boundfield>
           <asp:boundfield DataField="USER" HeaderText="USER" SortExpression="USER">
           </asp:boundfield>
           <asp:boundfield DataField="PASS" HeaderText="PASS" SortExpression="PASS">
           </asp:boundfield>
           <asp:boundfield DataField="AUTH" HeaderText="AUTH" SortExpression="AUTH">
           </asp:boundfield>
       </Columns>
   </asp:GridView>
</form>

</body>

</html>

HOW to ADD,MODIFY, and DELETE RECORD on the table/database connected (via ODBC) on Expression page??

Sat, 04 Jul 2009 15:23:44 Z

and felt strange to just find out that the 'preview' of Insert page works well in Expression Web,

but if i 'browse' from IIS, the insert page still doesn't work ... omg

HOW to ADD,MODIFY, and DELETE RECORD on the table/database connected (via ODBC) on Expression page??

Sat, 04 Jul 2009 16:23:05 Z

What kind of insert? HMTL, or PHP?

HOW to ADD,MODIFY, and DELETE RECORD on the table/database connected (via ODBC) on Expression page??

Sat, 04 Jul 2009 16:26:44 Z

Her last post is kind of cryptic, but I think she's still talking about her Insert/Update Record pages for her ASP.NET app. Could be wrong...

cheers,
scott

HOW to ADD,MODIFY, and DELETE RECORD on the table/database connected (via ODBC) on Expression page??

Sat, 04 Jul 2009 16:26:56 Z

Thank you for reply.

I mean Insert page as i created an .aspx page for inserting new record to the connected database table. I put Insert statement as INSERT INTO MAIT2(NAME, CONTACT, ACCOUNT,MAILTO, MAILCC, MAILFROM, SERVER, PORT, USER, PASS, AUTH)
VALUES(?,?,?,?,?,?,?,?,?,?,?)

as i mentioned in the previous replies. It works when I preview it from EW2, but if i view it remotely or from IIS, it doesn't work

and also didn't figure out why Update and Del cannot work well either. I posted error msg and code in last replies

HOW to ADD,MODIFY, and DELETE RECORD on the table/database connected (via ODBC) on Expression page??

Sat, 04 Jul 2009 20:36:40 Z

If you can perform the Insert, Update and Delete operations using the EW dev server, but these same operations do not work in IIS, then there may be a roles/permissions configuration setting in IIS on both your local machine and the remote server that is not correct. You might want to check it out.

You might also post your application error message at the appropriate Asp.Net forum: http://forums.asp.net/ .

-Preston

Columbia, CA. USA http://www.gildedmoon.com

HOW to ADD,MODIFY, and DELETE RECORD on the table/database connected (via ODBC) on Expression page??

Mon, 06 Jul 2009 12:56:04 Z

Thanks much Preston!!

You said right, now i fixed the Insert page problem cause i didn't set permissions of the folder and database as Write, Browse, Executable for users.. now it really can run well over IIS.

The update and delete record still cannot work, i am thinking if it's the prob of not having ID column in the table? now i set name as primary key, and i am wondering why it cannot run well. So far i cannot think of anything else.. and i am not supposed to change the database, but i think i'll give it a try.

The error msg of Update and Del page now is: "INDEX NOT Found"..

HOW to ADD,MODIFY, and DELETE RECORD on the table/database connected (via ODBC) on Expression page??

Mon, 06 Jul 2009 16:11:28 Z

Don't make changes to your database design at this juncture; especially if you're not supposed to do so. You'll likely cause other problems to crop up.

I strongly suggest that you follow my earlier recommendation to post your question at the appropriate forum at http://forums.asp.net/ . You might also want to look at some of the SQL / Database design documentation at MSDN.

-Preston

Columbia, CA. USA http://www.gildedmoon.com