ASP.net SQL Insert Problem

[Brad Baker]

I'm trying to write a simple asp.net page which updates some data in a SQL
database. At the top of the page I have the following code:

<%@ Page Language="C#" Debug="true" %>
<%@ import namespace="System.Data" %>
<%@ import namespace="System.Data.SqlClient" %>

<script language="c#" runat="server">
public void Page_Load(object sender, EventArgs e) {

if (Page.IsPostBack) {

string customerid = Request.QueryString["customerid"];

// Connect to the DB Server
SqlConnection objConn = new SqlConnection("Server=server;
Database=Database; UId=Username; Pwd=Password");
objConn.Open();

// Create a SQL query
string sqlquery = "UPDATE Configuration SET staging_url=@StagingURL WHERE
customer_id=@customerid ";

//Adapter command
SqlDataAdapter configuration_info_query = new SqlDataAdapter(sqlquery,
objConn);

//assign a parameter to our adapter command
configuration_info_query.SelectCommand.Parameters.Add(new
SqlParameter("@StagingURL", SqlDbType.NVarChar, 255));
configuration_info_query.SelectCommand.Parameters.Add(new
SqlParameter("@customerid", customerid));
}
}
</script>


In case your wondering - I purposefully left out the select statement in the
code above which populates the form below to reduce the size of the post.
In the body I have the following code:


<form runat="server">
<ASP:Repeater id="configuration_info_repeater" runat="server">
<HeaderTemplate>
<table>
</HeaderTemplate>
<ItemTemplate>
<tr>
<td><b>CustomerID</b></td>
<td><asp:Label ID="CustomerID" TextMode="SingleLine" runat="server"
Text='<%# DataBinder.Eval(Container.DataItem, "customer_id") %>' /> </td>
</tr>
<tr>
<td><b>Staging URL</b></td>
<td><asp:TextBox ID="StagingURL" runat="server" Text='<%#
DataBinder.Eval(Container.DataItem, "staging_url") %>' TextMode="SingleLine"
Width="500" /> </td>
</tr>
</ItemTemplate>
<FooterTemplate>
</table>
</FooterTemplate>
</ASP:Repeater>
<asp:Button ID="submit" runat="server" Text="submit" />
</form>

My problem is that when I press submit the database doesn't update. I'm
surely doing something stupid and obvious wrong but I'm completely baffled
what that is. If anyone has any insights I would truely appreciate them.

Thanks in Advance,
Brad

 

[writebrent]

Hi Brad...

Are you, by chance, missing some execution code?

Like


SqlCommand myCommand = new SqlCommand (sqlquery, objConn);
myCommand.ExecuteNonQuery();
objConn.Close()

--B

 

[Steven Cheng[MSFT]]

Hello Brad,

I'm a bit confused about the code you provided, based on your code
fragment, I can get the following things:

1. You want to update database records in ASP.NET web page(though your
thread title is "insert problem").

2. In your ASP.NET page's server-code block, instead of using the
"UpdateCommand" property, you're using the "SelectCommand" property to
store some UPDATE T-SQL statement. Also, DataAdapter is used for updating
data through DataSet/DataTable.

So if you just want to execute a single select/update/delete SQL
command(without using dataset/datatable), you can just call
SqlCommand.ExecuteXXX method instead of using DataAdapter.

Here are some articles introducing data access in ASP.NET 2.0:

#Performing Data Access
http://www.asp.net/QuickStart/aspnet/doc/data/default.aspx

Here is a msdn reference describe supported means to update database:

#How to: Update Records in a Database
http://msdn2.microsoft.com/en-us/library/ms233819.aspx



In addition, I notice that you use repeater control to display data in
page, will your update statement pickup udate paramters from the repeater
control?

Please feel free to provide some further background or code logic of your
web page so that we can help have a look and provide some futher ideas.



Sincerely,

Steven Cheng

Microsoft MSDN Online Support Lead



==================================================

Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications.



Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscriptions/support/default.aspx.

==================================================



This posting is provided "AS IS" with no warranties, and confers no rights.

 

[Brad Baker]

Steven -

Thank you so much for the information. Here are some followup comments:

1. You want to update database records in ASP.NET web page(though your
thread title is "insert problem").

That was a typographical error on my part. I'm trying to UPDATE.

2. In your ASP.NET page's server-code block, instead of using the
"UpdateCommand" property, you're using the "SelectCommand" property to
store some UPDATE T-SQL statement. Also, DataAdapter is used for updating
data through DataSet/DataTable.

I caught that after I had posted. The code now reads:
//assign a parameter to our adapter command
configuration_info_query2.UpdateCommand.Parameters.Add(new
SqlParameter("@StagingURL", SqlDbType.NVarChar, 255));
configuration_info_query2.UpdateCommand.Parameters.Add(new
SqlParameter("@customerid", customerid));

In addition, I notice that you use repeater control to display data in
page, will your update statement pickup udate paramters from the repeater
control?

It want it to - but I'm not sure it will given the way its currently
written. Maybe a repeater control isn't what I would want since the data
shouldn't be repeated in this case.

As you can probably tellI'm completely new to ASP.net so I may be doing this
all wrong. Essentially this should be the logic for my page:

1) The page will be passed a parameter via the URL (customer id) from
another non ASP.net page.
2) Use that to find a record in a Microsoft SQL database - if a record can't
be found create a new record using the customer id and empty values
3) Display the data in that record in various form fields on the page
4) Allow the user to update form feild values and save the values back into
the database

Thats it - pretty simple in concept. It seems frustratingly complex in
ASP.net as opposed to other languages I've used though

I'm going to read through some of the links you sent and hopefully I can get
a better feeling for what I should be doing. If you have any other
recommendations though I would love to hear them.

Thanks Again,
Brad

 

[Brad Baker]

I think I am getting closer. I've decided to scrap my previous approach it
was far too complex. I've opted to download the Visual Web Developer 2005
Express edition IDE from Microsoft and I've re-written the page using it.
(See code below)

I can now update, insert and select by manipulating the formview1 mode
manually. What I need to figure out is how I can incorporate some
conditional logic so that the correct mode is enabled automatically. What I
would like to be able to do pseudo code is: Check if a DB record exists
using the customer id passed via the URL, if it doesn't exist, use insert
mode, otherwise use edit mode

I can't figure out how to do that. I was thinking perhaps I could do it on
page load. I'm just not sure how.

Brad


<%@ Page Language="C#" %>
<form id="form1" runat="server">
<div>
<asp:SqlDataSource ID="CRM1Datasource" runat="server"
ConnectionString="Data Source=SERVER;Initial Catalog=CustomerData;Persist
Security Info=True;User ID=username;Password=password"
ProviderName="System.Data.SqlClient" SelectCommand="SELECT *
FROM [SiteConfiguration] WHERE ([customer_id] = @customer_id)"
InsertCommand="INSERT INTO SiteConfiguration(customer_id) VALUES
(@customerid)" UpdateCommand="UPDATE SiteConfiguration SET staging_url =
@staging_url, db_server = @db_server WHERE (customer_id = @customer_id)">
<SelectParameters>
<asp:QueryStringParameter Name="customer_id"
QueryStringField="customerid" Type="String" />
</SelectParameters>
<UpdateParameters>
<asp:FormParameter FormField="customer_id"
Name="customer_id" />
<asp:FormParameter FormField="staging_url"
Name="staging_url" />
<asp:FormParameter FormField="db_server" Name="db_server" />
</UpdateParameters>
<InsertParameters>
<asp:QueryStringParameter Name="customer_id"
QueryStringField="customer_id" />
</InsertParameters>
</asp:SqlDataSource>

</div>
<asp:FormView ID="FormView1" runat="server"
DataSourceID="CRM1Datasource" DefaultMode="Insert"
OnPageIndexChanging="FormView1_PageIndexChanging">
<EditItemTemplate>
Customer ID:
<asp:TextBox ID="customer_id" runat="server" Text='<%#
Bind("customer_id") %>'>
</asp:TextBox><br />
Staging URL:
<asp:TextBox ID="staging_url" runat="server" Text='<%#
Bind("staging_url") %>'>
</asp:TextBox><br />
DB Server:<br />
<asp:ListBox ID="dbserver" runat="server" SelectedValue='<%#
Bind("db_server") %>'>
<asp:ListItem></asp:ListItem>
<asp:ListItem>DB6</asp:ListItem>
<asp:ListItem>DB7</asp:ListItem>
</asp:ListBox><br />
<br />
<asp:Button ID="UpdateButton" runat="server"
CausesValidation="True" CommandName="Update"
Text="Update">
</asp:Button>&nbsp;
</EditItemTemplate>
<InsertItemTemplate>
It seems these customer hasn't been added to the system yet.
Please enter their customer ID to the system to begin.<br />
Customer ID:
<asp:TextBox ID="customer_id" runat="server" Text='<%#
Bind("customer_id") %>'>
</asp:TextBox>
<asp:Button ID="AddButton" runat="server"
CausesValidation="True" CommandName="Insert"
Text="Add">
</asp:Button>
</InsertItemTemplate>
<ItemTemplate>
Customer ID:
<asp:TextBox ID="customer_id" runat="server" Text='<%#
Bind("customer_id") %>'></asp:TextBox><br />
Staging URL:
<br />
<asp:TextBox ID="staging_url" runat="server" Text='<%#
Bind("staging_url") %>'></asp:TextBox>
<asp:Button ID="Button1" runat="server" Text="Button" />
</ItemTemplate>
</asp:FormView>
</form>

 

[Steven Cheng[MSFT]]

Thanks for your reply Brad,

From your last message, I can see you have been able to use
FormView+SqlDataSource to display the data from database and also
edit/update them.

For your new questions:

==================
What I
would like to be able to do pseudo code is: Check if a DB record exists
using the customer id passed via the URL, if it doesn't exist, use insert
mode, otherwise use edit mode
==================

I think you can do it through the following means:

1. Create a helper function which will accept an ID value as paramter and
return whether the record exists in database. Of course, you need to put
data accesssing code in this function to query the backend database.

2. In your page which contains the FormView, set its "DefaultMode" to
"Insert" so that by default it will display Insert mode template instead of
readonly template. e.g.

<asp:FormView ID="FormView1" runat="server" DataKeyNames="CategoryID"
DataSourceID="SqlDataSource1"
DefaultMode="Insert">

Also, in code behind , we need to add some code in Page_Load to check the
database( to see whether a record exists in database or not) and then
adjust the formView's Mode to the proper one. e.g.

#Here " IsRecordExisting" is the helper function, and I simply return
(cid%2 == 0), you need to replace it with your own code whch query database
to see whether the record of the given id exists. Then, we use the
FormView.ChangeMode method to dynamically determine which template(insert
or update to display).

=========================
public partial class dataaccess_FormViewPage : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
string cid = Request.QueryString["cid"];

if(string.IsNullOrEmpty(cid))
{
cid = "1";
}
if(IsRecordExisting(int.Parse(cid)))
{
FormView1.ChangeMode(FormViewMode.Edit);
}
}

protected bool IsRecordExisting(int cid)
{
return cid % 2 == 0;
}

..........................
}

=======================

and the FormView is linked to a Sqldatasource which is configured to use a
querystring "cid" as Selectparamter. e.g:

============================
<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$
ConnectionStrings:NorthwindConnectionString %>"
DeleteCommand="....................."
InsertCommand="......................."
SelectCommand="SELECT [CategoryID], [CategoryName],
[Description] FROM [Categories] where [CategoryID] = @CategoryID"
UpdateCommand=".......................">

<SelectParameters >
<asp:QueryStringParameter Name="CategoryID"
QueryStringField="cid" DefaultValue="1" />
</SelectParameters>
...................
=============================


If you have anything unclear on the above stuff, please feel free to let me
know.

Sincerely,

Steven Cheng

Microsoft MSDN Online Support Lead


This posting is provided "AS IS" with no warranties, and confers no rights.