Problem inserting record into Database 1

[Wilnicm]

Hi,
I'm having problems with code to save inputs from a forms textbox to a data base. I have a Window forms with 6 textbox fields and a button click event that will save the input from these 6 textbox fields to my table called User profile that has 7 columns name Fname, Lname, UserName, Email, Pword, RePword, UserNumber. I have not been able to successfully do this the other tutorials i have found on this does not have this problem. I have not added any validation to this becuase i just wanted to get it working before i started adding validation and other things.. to it.. Here is my currect code..

SqlConnection CnNew = new SqlConnection();
CnNew.ConnectionString = @"Data Source=.\SQLEXPRESS;AttachDbFilename=C:\Program Files\Microsoft Visual Studio 9.0\VC#\CSharpProjects\MyDiaryVersionOne\MyDiaryVersionOne\MyDiary.mdf;Integrated Security=True;User Instance=True";
SqlCommand CmdSql = new SqlCommand();

string query = "INSERT INTO UserProfile VALUES(Fname,Lname,UserName,Email,Pword,RePword,UserNumber)";

CmdSql.CommandText = query;
CmdSql.CommandType = CommandType.Text;

CmdSql.Parameters.AddWithValue("Fname", TxtbxFname.Text);
CmdSql.Parameters.AddWithValue("Lname", TxtbxLname.Text);
CmdSql.Parameters.AddWithValue("UserName", TxtbxUname.Text);
CmdSql.Parameters.AddWithValue("Email", TxtbxEmail.Text);
CmdSql.Parameters.AddWithValue("Pword", TxtbxPword.Text);
CmdSql.Parameters.AddWithValue("RePword", TxtbxRepword.Text);
CmdSql.Parameters.AddWithValue("UserNumber", TxtbxUname.Text);
CmdSql.Connection = CnNew;
CnNew.Open();
CmdSql.ExecuteNonQuery();

<Will The Great>

 

[AlexCuse]

You need to prefix your parameters with an @ sign I believe:

 string query = "INSERT INTO UserProfile 
VALUES(@Fname,@Lname,@UserName,@Email,@Pword,@RePword,@UserNumber)";

Hope this helps,

Alex

[small]----signature below----[/small]
The author of the monograph, a native of Schenectady, New York, was said by some to have had the highest I.Q. of all the war criminals who were made to face a death by hanging. So it goes.

My Crummy Web Page

 

[Wilnicm]

Alex..
I'm getting a SQL Exception stating "An explicit value for the identity column in table 'UserProfile' can only be specified when a column list is used and IDENTITY_INSERT is ON" here is my current code with the suggested changes..


SqlConnection CnNew = new SqlConnection();
CnNew.ConnectionString = "Data Source=.\\SQLEXPRESS;AttachDbFilename=|DataDirectory|\\MyDiary.mdf;Integrated Security=True;User Instance=True";
SqlCommand CmdSql = new SqlCommand();

string query = "INSERT INTO UserProfile VALUES(@Fname,@Lname,@UserName,@Email,@Pword,@RePword,@UserNumber)";

CmdSql.CommandText = query;
CmdSql.CommandType = CommandType.Text;

CmdSql.Parameters.AddWithValue("@Fname", TxtbxFname.Text);
CmdSql.Parameters.AddWithValue("@Lname", TxtbxLname.Text);
CmdSql.Parameters.AddWithValue("@UserName", TxtbxUname.Text);
CmdSql.Parameters.AddWithValue("@Email", TxtbxEmail.Text);
CmdSql.Parameters.AddWithValue("@Pword", TxtbxPword.Text);
CmdSql.Parameters.AddWithValue("@RePword", TxtbxRepword.Text);
CmdSql.Parameters.AddWithValue("@UserNumber", TxtbxUname.Text);
CmdSql.Connection = CnNew;
CnNew.Open();
CmdSql.ExecuteNonQuery();

<Will The Great>

 

[jmeckley]

you need to explicitly state which columns you are updating

var sql = "insert into [table] ([column 1],[column 2]) values (@p1, @p2)"

Jason Meckley
Programmer
Specialty Bakers, Inc.

 

[Wilnicm]

Its still not working i'm not able to insert anything in the database..
here is my current code..
Thanks.. for the help in advanced..

string first = TxtbxFname.Text;
string last = TxtbxLname.Text;
string User = TxtbxUname.Text;
string Email = TxtbxEmail.Text;
string Pword = TxtbxPword.Text;
string RePword = TxtbxRepword.Text;


if (first == "" || last == "" || User == "" || Pword == "" || RePword == "")
{
MessageBox.Show("Please ensure all fields are entered", "Error", MessageBoxButtons.OK, MessageBoxIcon.Asterisk);
}
else
{
System.Data.SqlClient.SqlConnection dataConnection = new SqlConnection();
dataConnection.ConnectionString =
@"Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\MyDiary.mdf;Integrated Security=True;User Instance=True";

System.Data.SqlClient.SqlCommand dataCommand = new SqlCommand();
dataCommand.Connection = dataConnection;

//tell the compiler and database that we're using parameters (thus the @first, @last, @nick)
dataCommand.CommandText = ("INSERT [UserProfile] ([Fname],[Lname],[UserName],,[Pword],[RePword]) VALUES (@first,@last,@User,@Email,@Pword,@RePword)");

//add our parameters to our command object
dataCommand.Parameters.AddWithValue("@first", first);
dataCommand.Parameters.AddWithValue("@last", last);
dataCommand.Parameters.AddWithValue("@User", User);
dataCommand.Parameters.AddWithValue("@Email", Email);
dataCommand.Parameters.AddWithValue("@Pword", Pword);
dataCommand.Parameters.AddWithValue("@RePword", RePword);


dataConnection.Open();
dataCommand.ExecuteNonQuery();
dataConnection.Close();

<Will The Great>

 

[jmeckley]

try removing the @ sign when you AddWithValue.
dataCommand.Parameters.AddWithValue("first", first);

you are also subject to some nasty issues if you don't dispose of the connection/command. research disposing database connections for more information.

Jason Meckley
Programmer
Specialty Bakers, Inc.

 

[Wilnicm]

Nothing.. got updated in the database.. I changed the code as you stated.. i'm wondering if its because i'm not using an adapter or i read somewhere my permissions my be preventing me from inserting it.. i've been at this problem for about a Week to no Avail..

<Will The Great>

 

[jmeckley]

if permissions was an issue an exception would be thrown.

Jason Meckley
Programmer
Specialty Bakers, Inc.

 

[Wilnicm]

Well no exception is being thrown and the database.. is not being updated.. Its probably something simple i'm not noticing.. or something..


<Will The Great>

 

[ZmrAbdulla]

may be the missing

//tell the compiler and database that we're using parameters (thus the @first, @last, @nick)  
                dataCommand.CommandText = ("INSERT [COLOR=red]INTO [/COLOR][UserProfile] ([Fname],[Lname],[UserName],[Email],[Pword],[RePword]) VALUES (@first,@last,@User,@Email,@Pword,@RePword)");

Zameer Abdulla

 

[AlexCuse]

How are you checking the database? Does it exist on a local instance of SQL Express? Or are you attaching the same .mdf that you used in your app? It may just be that you are using the wrong connection string, and the database being updated is not the one you inserted to.

I've never tried connecting to SQL Server by attaching the .mdf file but I would bet that is where you are running into issues. If its' able to open the connection to the database and run the command without any exceptions, I find it very doubtful that the rows are not being inserted. Just in case, can you post the creation script for the table in question? (right click the table ->script table as -> CREATE TO -> Clipboard or New Query Window)

Zameer, you can get away with just the INSERT. But it looks funny doesn't it

[small]----signature below----[/small]
The author of the monograph, a native of Schenectady, New York, was said by some to have had the highest I.Q. of all the war criminals who were made to face a death by hanging. So it goes.

My Crummy Web Page

 

[ZmrAbdulla]

Zameer, you can get away with just the INSERT. But it looks funny doesn't it

Because I am thinking in MS Access way.

Zameer Abdulla

 

[Chopstik]

This may perhaps also be a good candidate for SQL Profiler to see what is actually being sent to the DB. (Having recently been converted to Profiler, I've found it's a very handy tool when trying to troubleshoot these types of issues.)

------------------------------------------------------------------------------------------------------------------------
Reason and free inquiry are the only effectual agents against error; they are the natural enemies of error and of error only.

Thomas Jefferson

http://lessthandot.com

 

[jmeckley]

chopstik do you have any tutorial links for getting started with profiler. I've tried a couple times to work with it, but I don't know how to use it, or what the results mean.

Jason Meckley
Programmer
Specialty Bakers, Inc.

 

[Chopstik]

Don't feel bad... I'd heard about it for a while but couldn't ever really figure it out... I still have some questions about filtering but I can get it to do what I want so it's a start...

This helped me get started for real so hopefully it'll give you a push in the right direction.

http://www.sqlservercentral.com/articles/administration/66217/printable

------------------------------------------------------------------------------------------------------------------------
Reason and free inquiry are the only effectual agents against error; they are the natural enemies of error and of error only.

Thomas Jefferson

http://lessthandot.com

 

[Chopstik]

Also, if you have some more questions, there are some good people over in forum183 or at the link in my signature that may offer additional insight in its uses and benefits. Good luck!

------------------------------------------------------------------------------------------------------------------------
Reason and free inquiry are the only effectual agents against error; they are the natural enemies of error and of error only.

Thomas Jefferson

http://lessthandot.com

 

[Wilnicm]

My current code i have inserts the row into the table but the row does not get inserted until Under the Query Designer Menu i click the "Execute SQL" button Am i doing something wrong or i'm not understing.. I thought my code was to automatically insert the row in the table i didn't think i needed after i stop and exit the application that i would need to do this for it to take affect on the table it doesn't make sense or do i need to change some property or change my code.. to make this correct.. Thanks..

Here is my Code..

SqlConnection dataConnection = new SqlConnection();
dataConnection.ConnectionString =
@"Server=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\MyDiary.mdf;Database=MyDiary;Trusted_Connection=Yes";

System.Data.SqlClient.SqlCommand dataCommand = new SqlCommand();
dataCommand.Connection = dataConnection;

dataConnection.Open();

//tell the compiler and database that we're using parameters (thus the @first, @last, @nick)
dataCommand.CommandText = ("INSERT INTO UserProfile (Fname,Lname,UserName,Email,Pword,RePword) Values (@first,@last,@User,@Myemail,@MyPword,@MyRePword)");

//add our parameters to our command object
dataCommand.Parameters.AddWithValue("@first", first);
dataCommand.Parameters.AddWithValue("@last", last);
dataCommand.Parameters.AddWithValue("@User", User);
dataCommand.Parameters.AddWithValue("@MyPword", MyPword);
dataCommand.Parameters.AddWithValue("@Myemail", Myemail);
dataCommand.Parameters.AddWithValue("@MyRePword", MyRePword);


dataCommand.ExecuteNonQuery();
dataConnection.Close();
dataConnection.Dispose();

<Will The Great>

 

[jmeckley]

nothing looks out of place with this code. It will cause problems if an exception is thrown, but other than that it looks ok.

for more information google try finally IDisposable.

Jason Meckley
Programmer
Specialty Bakers, Inc.