Added a column to my SQL database and now text field will not write to it. Why

I have a web form that is an interface for a database. The code was working fine until a field needed to be added. So I added the new field, updated the data adapter and data set, and when i enter the data into the field, all of the old fields are getting updated, but the new ones aren't. I am at wits end as to why. Can someone please help!!!

I am using VS Studio 2000 ASP.NET and C#. The code is below. The bold items are the new fields.

using System;

using System.Collections;

using System.ComponentModel;

using System.Data;

using System.Drawing;

using System.Web;

using System.Web.SessionState;

using System.Web.UI;

using System.Web.UI.WebControls;

using System.Web.UI.HtmlControls;

namespace TrafficDept

{

///<summary>

/// Summary description for AddOwner.

///</summary>

publicclass AddOwner : System.Web.UI.Page

{

protected System.Web.UI.WebControls.Button Button1;

protected System.Web.UI.WebControls.RequiredFieldValidator RequiredFieldValidator3;

protected System.Data.SqlClient.SqlConnection sqlConnection1;

protected System.Web.UI.WebControls.TextBox TextBox1;

protected System.Web.UI.WebControls.TextBox TextBox2;

protected System.Data.SqlClient.SqlDataAdapter daAddOwner;

protected System.Web.UI.WebControls.TextBox tbOtherOwnerLName;

protected System.Web.UI.WebControls.Panel namePanel;

protected System.Web.UI.WebControls.TextBox tbOtherOwnerMI;

protected System.Web.UI.WebControls.TextBox tbOtherOwnerFName;

protected System.Web.UI.WebControls.TextBox tbSecondOwnerLName;

protected System.Web.UI.WebControls.TextBox tbSecondOwnerMI;

protected System.Web.UI.WebControls.TextBox tbSecondOwnerFName;

protected System.Web.UI.WebControls.Button Button4;

protected SiteCubed.EditWorksProfessional tbNote;

protected System.Web.UI.WebControls.Panel notePanel;

protected System.Web.UI.WebControls.DropDownList ddlStatus;

protected PeterBlum.PetersDatePackage.DateTextBox tbRegDate;

protected System.Web.UI.WebControls.RequiredFieldValidator RequiredFieldValidator2;

protected System.Web.UI.WebControls.TextBox tbOwner;

protected System.Web.UI.WebControls.RequiredFieldValidator RequiredFieldValidator1;

protected System.Web.UI.WebControls.TextBox tbOwnerMI;

protected System.Web.UI.WebControls.TextBox tbOwnerLName;

protected System.Web.UI.WebControls.RequiredFieldValidator RequiredFieldValidator4;

protected System.Web.UI.WebControls.TextBox tbBusinessName;

protected System.Web.UI.WebControls.TextBox tbAddress;

protected System.Web.UI.WebControls.TextBox tbRegNo;

protected System.Web.UI.WebControls.TextBox tbPreviousOwner;

protected System.Web.UI.WebControls.TextBox tbPOwnerAddress;

protected System.Web.UI.WebControls.TextBox tbPORegNo;

protected PeterBlum.PetersDatePackage.DateTextBox tbTransferDate;

protected System.Web.UI.WebControls.DropDownList ddlMeans;

protected System.Web.UI.WebControls.Button Button3;

protected System.Web.UI.WebControls.Button Button2;

protected System.Data.SqlClient.SqlCommand sqlSelectCommand1;

protected System.Data.SqlClient.SqlCommand sqlInsertCommand1;

protected System.Data.SqlClient.SqlCommand sqlUpdateCommand1;

protected System.Data.SqlClient.SqlCommand sqlDeleteCommand1;

protected TrafficDept.dsAddOwner dsAddOwner1;

privatevoid Page_Load(object sender, System.EventArgs e)

{

// Put user code to initialize the page here

}

#region Web Form Designer generated code

privatevoid Button2_Click(object sender, System.EventArgs e)

{

Response.Redirect("AddOthers.aspx");

}

privatevoid Button1_Click(object sender, System.EventArgs e)

{

daAddOwner.Fill(dsAddOwner1);

dsAddOwner.OwnershipRow drOwner = dsAddOwner1.Ownership.NewOwnershipRow();

drOwner.Plates_ID =int.Parse(Session["PlatesID"].ToString());

drOwner.Status = ddlStatus.SelectedItem.Text;

drOwner.Current_Legal_Owner = tbOwner.Text;

drOwner.Owner_CurrentLegalOwnerMI = tbOwnerMI.Text.ToString();

drOwner.Owner_CurrentLegalOwnerLName = tbOwnerLName.Text;

drOwner.Owner_CurrentBusinessName = tbBusinessName.Text;

if (!tbRegDate.xIsEmpty)

drOwner.Date_of_Registration = DateTime.Parse(tbRegDate.Text.ToString());

drOwner.Address_of_Current_Owner = tbAddress.Text;

drOwner.Reg_No_for_Current_Owner = tbRegNo.Text;

drOwner.Previous_Owner = tbPreviousOwner.Text;

drOwner.Previous_Owner_Address = tbPOwnerAddress.Text;

drOwner.Reg_No_for_Previous_Owner = tbPORegNo.Text;

if (!tbTransferDate.xIsEmpty)

drOwner.Date_of_Transfer = DateTime.Parse(tbTransferDate.Text.ToString());

drOwner.By_Means_of = ddlMeans.SelectedItem.Text;

drOwner.Owner_SecondaryLegalOwnerFName = tbSecondOwnerFName.Text;

drOwner.Owner_SecondaryLegalOwnerMI = tbSecondOwnerMI.Text;

drOwner.Owner_SecondaryLegalOwnerLName = tbSecondOwnerLName.Text;

drOwner.Owner_OtherLegalOwnerFName = tbOtherOwnerFName.Text;

drOwner.Owner_OtherLegalOwnerMI = tbOtherOwnerMI.Text;

drOwner.Owner_OtherLegalOwnerLName = tbOtherOwnerLName.Text;

drOwner.Owner_Notes = tbNote.Text;

dsAddOwner1.Ownership.Rows.Add(drOwner);

daAddOwner.Update(dsAddOwner1);

Response.Redirect("AddOthersTP.aspx");

}

privatevoid Button3_Click(object sender, System.EventArgs e)

{

//daAddOwner.Fill(dsAddOwner1);

//dsAddOwner.OwnershipRow drAddOwner = dsAddOwner1.Ownership.NewOwnershipRow();

namePanel.Visible =true;

}

privatevoid Button4_Click(object sender, System.EventArgs e)

{

notePanel.Visible =true;

}

}

}

I found my problem. The .dll was never being rebuilt.

Add User/Set Roles in Code and Read Roles

Can you write a stored procedure to add a user to your DB and set the roles the user belongs to?

I want to write a stored proc. to add users and set roles so it can be used in code instead of doing it manually.

After the user has been added and their roles set, can you write another stored proc. to give you what roles they belong to?Look for following sprocs in BOL and sp_helptext sprocs in QA

Might be a big help

sp_helprole
sp_helprotect
sp_helprolemember

sp_addrole
sp_addrolemember

However something you might need to check out

The Holy Book says:
sp_addrole cannot be used inside a user-defined transaction.|||insert into sysusers values
('new_user_id', 0, 'new_role_name', NULL, 0x00, getdate(), getdate(), 'dbo', NULL)|||Originally posted by Enigma
Look for following sprocs in BOL and sp_helptext sprocs in QA

Might be a big help

sp_helprole
sp_helprotect
sp_helprolemember

sp_addrole
sp_addrolemember

However something you might need to check out

The Holy Book says:
sp_addrole cannot be used inside a user-defined transaction.
sp_grantlogin, sp_grantdbaccess, sp_addrolemember, and sp_helpuser worked great. thanks.

Add a where condition only if a parameter exists

I'm pretty new to TSQL. I'm trying to write a generic proc that returns
either ALL records from a table or (if a parameter is set to 1) or only a
sub-set of that table (if parameter is set to 0).
Here's an example of what I'm aiming for:
Create table contact (
contactID int,
FullName varchar (40),
IsActive bit)
Create procedure sp_select_Contacts
@.ContactID int,
@.ShowAll bit = null
AS
-- if @.ShowAll is null, return all records
--otherwise limit records to records where isActive = True
How can I conditionally add a WHERE clause based on the value of @.ShowAll?
Thanks!
- JohnnyI presume that ContactId is the prinary key of Contacts. If this is the case
,
skip the @.ShowAll parameter.
If @.ContactId is null then have it return all, otherwise have it return only
that contact:
SELECT *
FROM Contacts
WHERE ContactId = COALESCE(@.ContactId,ContactId)
Alex Papadimoulis
http://weblogs.asp.net/Alex_Papadimoulis
"JohnnyMagz" wrote:

> I'm pretty new to TSQL. I'm trying to write a generic proc that returns
> either ALL records from a table or (if a parameter is set to 1) or only a
> sub-set of that table (if parameter is set to 0).
> Here's an example of what I'm aiming for:
> Create table contact (
> contactID int,
> FullName varchar (40),
> IsActive bit)
> Create procedure sp_select_Contacts
> @.ContactID int,
> @.ShowAll bit = null
> AS
> -- if @.ShowAll is null, return all records
> --otherwise limit records to records where isActive = True
> How can I conditionally add a WHERE clause based on the value of @.ShowAll?
> Thanks!
> - Johnny|||You need dynamic sql in this case
something like (untested)
Create procedure sp_select_Contacts
@.ContactID int,
@.ShowAll bit = null
AS
declare @.sql varchar(8000)
set @.sql = 'select * from contact'
if @.showall is not null
set @.sql = @.sql + ' where isactive = 1'
exec (@.sql)
go
"JohnnyMagz" <JohnnyMagz@.discussions.microsoft.com> wrote in message
news:B48A82FB-F620-44C5-82B5-F51D23D19B41@.microsoft.com...
> I'm pretty new to TSQL. I'm trying to write a generic proc that returns
> either ALL records from a table or (if a parameter is set to 1) or only a
> sub-set of that table (if parameter is set to 0).
> Here's an example of what I'm aiming for:
> Create table contact (
> contactID int,
> FullName varchar (40),
> IsActive bit)
> Create procedure sp_select_Contacts
> @.ContactID int,
> @.ShowAll bit = null
> AS
> -- if @.ShowAll is null, return all records
> --otherwise limit records to records where isActive = True
> How can I conditionally add a WHERE clause based on the value of @.ShowAll?
> Thanks!
> - Johnny|||Assuming isActive is never NULL and @.ShowAll is either NULL or 1.
...where isActive = isnull(@.ShowAll, 0) or isActive = @.ShowAll
"JohnnyMagz" <JohnnyMagz@.discussions.microsoft.com> wrote in message
news:B48A82FB-F620-44C5-82B5-F51D23D19B41@.microsoft.com...
> I'm pretty new to TSQL. I'm trying to write a generic proc that returns
> either ALL records from a table or (if a parameter is set to 1) or only a
> sub-set of that table (if parameter is set to 0).
> Here's an example of what I'm aiming for:
> Create table contact (
> contactID int,
> FullName varchar (40),
> IsActive bit)
> Create procedure sp_select_Contacts
> @.ContactID int,
> @.ShowAll bit = null
> AS
> -- if @.ShowAll is null, return all records
> --otherwise limit records to records where isActive = True
> How can I conditionally add a WHERE clause based on the value of @.ShowAll?
> Thanks!
> - Johnny

Add a column to a table.

I know this should be stupidly easy to do but I'm pulling my hair out trying to figure out how to write a SQL query to add a column to an existing table with SQL Server 2005. Every single reference I've found says to use "ALTER TABLE", yet when I do SQL says...

"The ALTER TABLE SQL construct or statement is not supported."

So how the $%^$ am I supposed to add a column? I've wasted about 3 hours on this simple task and it's really starting to piss me off.

Hi,

from where are you firing your query ? iring the query from the query ane of the Managment studio just works fine for me with the following script.

USE ADVENTUREWORKS

GO

DROP TABLE SomeTable

GO

CREATE TABLE SomeTable

(

SomeColumn INT

)

ALTER TABLE SomeTable

ADD SomeCOlumn2 INT

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

|||

That's where it gives me the error. A simple statement like this does it.

ALTER TABLE History ADD Mar06 int

Even if I just do this query it says it too.

ALTER TABLE History

In Management Studio it pops up the error message and gives me a choice of clicking "Continue" or "Cancel". If I click "Contine" it does the query and it works, but if I do the exact same query from a PHP script it doesn't work, even through the rest of the queries in the script work just fine. I don't get it at all. The full query that I want to run is...
ALTER TABLE History ADD Mar06 int DEFAULT 0 NOT NULL
Edit: Okay, I finally figured it out and it seems the user I was logged in as didn't have ALTER permissions. That error message was making me think it was something wrong with my query so that's what I was focused on fixing. It looks to be working fine now, though that error message still pops up.