Having a bit of an issue getting my insert to work properly. When I run the insert all within the same method, it works flawlessly... however when I try to send the Insert statement to my new Connection class (which I will have handle all database requests), I am getting the following error.
Note: I am using C# and Microsoft SQL Server.
System.Data.SqlClient.SqlException (0x80131904): Must declare the scalar variable "@CollectionGroupID".
I believe I am not sending the parameters over, however I am not sure of the best way to do this.
Here's my AddGame method:
public static void AddGame(int gameId)
{
string statement = "INSERT INTO Collection (CollectionGroupID, SharedID, UserID, GameID, Owned, Favorited, WishList, DeletedIndicator, AddUser, AddDate, ModUser, ModDate) VALUES (@CollectionGroupID, @SharedID, @UserID, @GameID, @Owned, @Favorited, @WishList, @DeletedIndicator, @AddUser, @AddDate, @ModUser, @ModDate)";
using (SqlCommand cmd = new SqlCommand())
{
cmd.Parameters.AddWithValue("@CollectionGroupID", "0");
cmd.Parameters.AddWithValue("@SharedID", "0");
cmd.Parameters.AddWithValue("@UserID", "0");
cmd.Parameters.AddWithValue("@GameID", gameId);
cmd.Parameters.AddWithValue("@Owned", "Y");
cmd.Parameters.AddWithValue("@Favorited", "N");
cmd.Parameters.AddWithValue("@WishList", "N");
cmd.Parameters.AddWithValue("@DeletedIndicator", "N");
cmd.Parameters.AddWithValue("@AddUser", "test/admin");
cmd.Parameters.AddWithValue("@AddDate", DateTime.Now);
cmd.Parameters.AddWithValue("@ModUser", "test/admin");
cmd.Parameters.AddWithValue("@ModDate", DateTime.Now);
Connection.Open();
Connection.Statement(statement);
Connection.Close();
}
}
And here is my Statement method in my Connection class
public static void Statement(string sql)
{
Console.WriteLine("Attempting to submit data to the database...");
try
{
SqlCommand cmd = new SqlCommand(sql, conn);
cmd.ExecuteNonQuery();
}
catch (SqlException e)
{
Console.WriteLine(e);
}
}
I feel like perhaps I am overlooking a simple solution. Any help appreciated!
-Travis W.
Command parameter is defined in SqlCommand
in your AddGame
method
you are passing the raw Sql over to the Statement
method and inside the method you are creating another SqlCommand
without parameter defined. This is why the parameters are not being passed in.
you should just do
using (SqlConnection connection = new SqlConnection(connectionString))
{
//OR using (SqlConnection connection = Connection.Open())
//If you want to keep your Connection class to avoid having to pass in connection string.
using (SqlCommand cmd = new SqlCommand(statement, connection))
{
...
cmd.ExecuteNonQuery ()
}
}
inside your AddGame
method
User contributions licensed under CC BY-SA 3.0