SQL Server Insert in console Application

[bigjoe11a]

I'm Using VC# 2008 express in a console application.
What I'm trying to do in select * from users WHERE username= user.

If not fount. Then it inserts a new record. Every time the insert runs. I keep getting the column ID is Not Null and well the program errors out.

Can some one tell me what I'm doing wrong. I set up another table and I can insert records into that table. Why can't I insert into this table.
//////////////////////////////////////////////
public static void getuser()
{
/// <summary>
/// Gets doors config file.
/// </summary>


Person newuser = new Person();

newuser.username = RMDoor.DropInfo.RealName;
newuser.slevel = RMDoor.DropInfo.Access;
newuser.credits = 0;

SqlConnection MyConnection = new SqlConnection("Server=" + servername + ";Trusted_Connection=true;database=bbslist;User ID=" + sqluser + ";Password=" + sqlpass + "");
string queryString = "SELECT * FROM dbo.users WHERE username="+ newuser.username;

try
{
//SqlCommand sqlcommand = MyConnection.CreateCommand();
//sqlcommand.ExecuteNonQuery();

//SqlDataReader mySqlDataReader = sqlcommand.ExecuteReader(CommandBehavior.SingleResult);

SqlCommand command = new SqlCommand(queryString, MyConnection);
MyConnection.Open();

SqlDataReader reader = command.ExecuteReader();

// Call Read before accessing data.
reader.Read();

string name = reader.GetValue(0).ToString();
string credits = reader.GetValue(1).ToString();
string slevel = reader.GetValue(2).ToString();

RMDoor.ClrScr();
RMDoor.WriteLn("Name : " + name);
RMDoor.WriteLn("Credits : " + credits);
RMDoor.WriteLn("Security : " + slevel);

// Call Close when done reading.
reader.Close();

}
catch(SqlException e)
{
//If no record fount, Insert new record
RMDoor.WriteLn(e.Message + " | " + e.LineNumber + " ");
RMDoor.WriteLn("Adding New User");
//String MyQuery = @"INSERT INTO dbo.users VALUES ('" + newuser.username + "', '1000', '" + newuser.slevel + "')";
String MyQuery = @"INSERT INTO users VALUES ('username', '1000', '99')";
SqlCommand MyCmdInsert = new SqlCommand(MyQuery, MyConnection);

MyCmdInsert.ExecuteNonQuery();
}

Thread.Sleep(5000);

MyConnection.Close();
RMDoor.WriteLn();
RMDoor.WriteLn("Press Enter to Continue..");
RMDoor.ReadKey();
}

public static void search()
{
RMDoor.ClrScr();
RMDoor.TextAttr(15);
RMDoor.WriteLn("search for BBS list!");
RMDoor.Write("Enter BBS Name :");
string searchname = RMDoor.ReadLn();

SqlConnection MyConnection = new SqlConnection("Server=" + servername + ";Trusted_Connection=true;database=bbslist;User ID=" + sqluser + ";Password=" + sqlpass + "");

MyConnection.Open();

SqlCommand mySqlCommand = MyConnection.CreateCommand();

mySqlCommand.CommandText = "SELECT * FROM listing WHERE BBSName='" + searchname + "'";

SqlDataReader mySqlDataReader = mySqlCommand.ExecuteReader(CommandBehavior.SingleResult);
RMDoor.TextAttr(15);

while (mySqlDataReader.Read())
{

RMDoor.WriteLn(" " + mySqlDataReader["ID"] + " " + mySqlDataReader["BBSName"] + " " + mySqlDataReader["telnetAddress"]);

}

mySqlDataReader.Close();
MyConnection.Close();
RMDoor.WriteLn("Press Enter to Continue..");
RMDoor.ReadKey();


}
\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\

[olivthill2]

Where is you ID column?

When you writeString MyQuery = @"INSERT INTO users VALUES ('username', '1000', '99')";You assume that your users table has columns defined in the same order as your values, but maybe that's not the case. It is safer to write String MyQuery = @"INSERT INTO users(name_column1, name_column2, name_column3) VALUES ('username', '1000', '99')";If the ID column is defined to be not null, and if it is not among the three columns that you give, then there is a problem.

[bigjoe11a]

Where is you ID column?

When you writeString MyQuery = @"INSERT INTO users VALUES ('username', '1000', '99')";You assume that your users table has columns defined in the same order as your values, but maybe that's not the case. It is safer to write String MyQuery = @"INSERT INTO users(name_column1, name_column2, name_column3) VALUES ('username', '1000', '99')";If the ID column is defined to be not null, and if it is not among the three columns that you give, then there is a problem.

Thanks. That really helped. I don't why. But that's working


catch(SqlException e)
{
//If no record fount, Insert new record
RMDoor.WriteLn(e.Message + " | " + e.LineNumber + " ");
RMDoor.WriteLn("Adding New User");
//String MyQuery = @"INSERT INTO dbo.users VALUES ('" + newuser.username + "', '1000', '" + newuser.slevel + "')";
String MyQuery = @"INSERT INTO users (UserName, Credits, Slevel) VALUES ('" + newuser.username + "', '1000', '" + newuser.slevel + "')";
SqlCommand MyCmdInsert = new SqlCommand(MyQuery, MyConnection);

MyCmdInsert.ExecuteNonQuery();
}


on the 1st part. I can preform a search. Here's my code below.


SqlCommand mySqlCommand = MyConnection.CreateCommand();
mySqlCommand.CommandText = "SELECT * FROM users WHERE UserName='" + newuser.username + "'";

MyConnection.Open();

SqlDataReader mySqlDataReader = mySqlCommand.ExecuteReader(CommandBehavior.SingleResult);


while (mySqlDataReader.Read())
{
RMDoor.TextAttr(15);
RMDoor.WriteLn(" " + mySqlDataReader["ID"] + " " + mySqlDataReader["UserName"] + " " + mySqlDataReader["Credits"] + " " + mySqlDataReader["Slevel"] + "");

}


What I'm trying to do. is when a match is fount is to load the values from the row that matched into some global vars that I have made.
Can you please tell me how to do that.

thanks
Joe