HI there,
I have written a simple form that inserts a new record when none exists and updates an existing record when one does. It uses ASP.net and C#. The insert statement works fine, but the update statement does not work at all, though no error is returned. Any help would be appreciated. The code in questions is in the Submit_Click function.
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;
using System.Data.SqlClient;
namespace PathfinderCharGen
{
public partial class HeroDetails : System.Web.UI.Page
{
#region SQLConnection
SqlConnection conn;
SqlCommand cmd;
SqlCommand cmd1;
SqlDataReader rdr;
String uid;
private void closeConn()
{
if (conn != null)
{
if (conn.State == ConnectionState.Open)
{
conn.Close();
}
conn.Dispose();
}
}
private SqlConnection createConn(string database)
{
// Here you define your server. Values can not be NULL. //Database Server Name.
string myDSN = "SQLEXPRESS";
//Local Server Name.
string myLSN = "ST-PC";
//Define the type of security, 'TRUE' or 'FALASE'.
string mySecType = "TRUE";
//Here you have your connection string you can edit it here.
string connString = ("server=" + myLSN + "\\" + myDSN + ";database=" + database + ";Integrated Security=" + mySecType);
//If you wish to use SQL security, well just make your own connection string...
// I make sure I have declare what conn stand for.
if (conn == null) { conn = new SqlConnection(); };
// Since i will be reusing the connection I will try this it the connection dose not exist.
if (conn.ConnectionString == string.Empty || conn.ConnectionString == null)
{
// I use a try catch stament cuz I use 2 set of arguments to connect to the database
try
{
//First I try with a pool of 5-40 and a connection time out of 4 seconds. then I open the connection.
conn.ConnectionString = "Min Pool Size=5;Max Pool Size=40;Connect Timeout=4;" + connString + ";";
conn.Open();
}
catch (Exception)
{
//If it did not work i try not using the pool and I give it a 45 seconds timeout.
if (conn.State != ConnectionState.Closed)
{
conn.Close();
}
conn.ConnectionString = "Pooling=false;Connect Timeout=45;" + connString + ";";
conn.Open();
}
return conn;
}
//Here if the connection exsist and is open i try this.
if (conn.State != ConnectionState.Open)
{
try
{
conn.ConnectionString = "Min Pool Size=5;Max Pool Size=40;Connect Timeout=4;" + connString + ";";
conn.Open();
}
catch (Exception)
{
if (conn.State != ConnectionState.Closed)
{
conn.Close();
}
conn.ConnectionString = "Pooling=false;Connect Timeout=45;" + connString + ";";
conn.Open();
}
}
return conn;
}
#endregion
protected void Page_Load(object sender, EventArgs e)
{
uid = Request.QueryString["uid"];
if (uid == null)
return;
createConn("PathfinderCharGen");
cmd = new SqlCommand("SELECT * FROM characters WHERE uid='" + uid + "'", conn);
rdr = cmd.ExecuteReader();
if (!rdr.HasRows)
return;
while (rdr.Read())
{
characterName.Text = Convert.ToString(rdr["name"]);
playerName.Text = Convert.ToString(rdr["player"]);
height.Text = Convert.ToString(rdr["height"]);
weight.Text = Convert.ToString(rdr["weight"]);
eyes.Text = Convert.ToString(rdr["eyes"]);
hair.Text = Convert.ToString(rdr["hair"]);
gender.Text = Convert.ToString(rdr["gender"]);
age.Text = Convert.ToString(rdr["age"]);
homeland.Text = Convert.ToString(rdr["homeland"]);
campaign.Text = Convert.ToString(rdr["campaign"]);
}
cmd.Dispose();
rdr.Dispose();
closeConn();
}
protected void submit_Click(object sender, EventArgs e)
{
createConn("PathfinderCharGen");
uid = Request.QueryString["uid"];
if (uid != null)
cmd1 = new SqlCommand("UPDATE characters SET name = @name, player = @player," +
" height = @height, weight = @weight, eyes = @eyes, hair = @hair," +
" gender = @gender, age = @age, homeland = @homeland, campaign = @campaign" +
" WHERE uid = @uid", conn);
else
cmd1 = new SqlCommand("INSERT into characters (name,player,height,weight,eyes," +
"hair,gender,age,homeland,campaign) VALUES(@name,@player,@height,@weight," +
"@eyes,@hair,@gender,@age,@homeland,@campaign); Select Scope_Identity()", conn);
cmd1.Parameters.AddWithValue("@name", (characterName.Text == null) ? " " : name.Text);
cmd1.Parameters.AddWithValue("@player", (playerName.Text == null) ? " " : player.Text);
cmd1.Parameters.AddWithValue("@height", (height.Text == null) ? " " : height.Text);
cmd1.Parameters.AddWithValue("@weight", (weight.Text == null) ? " " : weight.Text);
cmd1.Parameters.AddWithValue("@eyes", (eyes.Text == null) ? " " : eyes.Text);
cmd1.Parameters.AddWithValue("@hair", (hair.Text == null) ? " " : hair.Text);
cmd1.Parameters.AddWithValue("@gender", (gender.Text == null) ? " " : gender.Text);
cmd1.Parameters.AddWithValue("@age", (age.Text == null) ? " " : age.Text);
cmd1.Parameters.AddWithValue("@homeland", (homeland.Text == null) ? " " : homeland.Text);
cmd1.Parameters.AddWithValue("@campaign", (campaign.Text == null) ? " " : campaign.Text);
cmd1.Parameters.AddWithValue("@uid", (uid == null) ? 0 : Convert.ToInt32(uid));
if (uid == null)
{
uid = Convert.ToString(cmd1.ExecuteScalar());
}
else
{
SqlDataReader rdr1 = cmd1.ExecuteReader();
rdr1.Dispose();
}
cmd1.Dispose();
closeConn();
Response.AppendHeader("Refresh", "0; URL=HeroDetails.aspx?uid=" + uid);
return;
}
}
}
