none
What is wrong with this code? RRS feed

  • Question

  • I am hoping someone will be able to figure out what is wrong with this code. I have confirmed that the connection string works. I have also confirmed that the stored procedure works.

     

    I keep getting the error in the catch when run it. There are some notes that mention where the open()  and the executenonquery() methods were. I tried moving them to get it to work. That didn't help.

     

    Code Block

    try

    {

    // create a SqlConnection object to connect to the database, passing the connection string to the constructor

    SqlConnection mySqlConnection = new SqlConnection();

     

    //now you can set ConnectionString property for myConnection

    mySqlConnection.ConnectionString = configurationManager.AppSettings["myConnectionString"];

     

    // formulate a string containing the name of the stored procedure

    string procedureString = "spInsertNewPet";

     

    // create a SqlCommand object to hold the SQL statement

    SqlCommand mySqlCommand = new SqlCommand("spInsertNewPet", mySqlConnection);

     

    // set the CommandText property of the SqlCommand object to procedureString

    mySqlCommand.CommandText = procedureString;

     

    // set the CommandType property of the SqlCommand object to CommandType.StoredProcedure

    mySqlCommand.CommandType = CommandType.StoredProcedure;

     

    // open the database connection using the Open() method of the SqlConnection object

    // I had the open() method here but I moved it below to try it

    // run the stored procedure. I also moved this below to try it

    //mySqlCommand.ExecuteNonQuery();

     

    // create a SqlDataAdapter object

    SqlDataAdapter mySqlDataAdapter = new SqlDataAdapter();

     

    // set the SelectCommand property of the SqlAdapter object to the SqlCommand object

    mySqlDataAdapter.SelectCommand = mySqlCommand;

     

    // create a DataSet object to store the results of the stored procedure call

    DataSet myDataSet = new DataSet();

     

    // use the Fill() method of the SqlDataAdapter object to retrieve the rows from the stored procedure call,

    // storing the rows in a DataSet named Pets

    mySqlDataAdapter.Fill(myDataSet, "Pets"); //

    mySqlCommand.CommandType = CommandType.StoredProcedure;

     

    mySqlCommand.Parameters.AddWithValue("@CustID", SqlDbType.Int).Value = myPet.CustID;

    mySqlCommand.Parameters.AddWithValue("@PetName", SqlDbType.Int).Value = myPet.PetName;

    mySqlCommand.Parameters.AddWithValue("@BreedID", SqlDbType.Int).Value = myPet.BreedID;

    mySqlCommand.Parameters.AddWithValue("@Gender", SqlDbType.Char).Value = myPet.Gender;

    mySqlCommand.Parameters.AddWithValue("@Weight", SqlDbType.Decimal).Value = myPet.Weight;

    mySqlCommand.Parameters.AddWithValue("@DOB", SqlDbType.SmallDateTime).Value = myPet.Weight;

    mySqlCommand.Parameters.AddWithValue("@DateFixed", SqlDbType.SmallDateTime).Value = myPet.DOB;

     

    mySqlConnection.Open(); // this was above where the comment is for it

    mySqlCommand.ExecuteNonQuery();

    mySqlConnection.Close();

     

    Console.ReadLine();

    }

    catch

    {

    Console.WriteLine("Something went wrong");

    }

     

     

    Tuesday, January 15, 2008 12:37 AM

All replies

  • ok your code has the right concept for the most part ... but the logic falls short once you reach the DataAdapter section.


    SqlConnection mySqlConnection = new SqlConnection();

     

    mySqlConnection.ConnectionString = configurationManager.AppSettings["myConnectionString"];

     

    string procedureString = "spInsertNewPet";


    SqlCommand mySqlCommand = new SqlCommand("spInsertNewPet", mySqlConnection);


    mySqlCommand.CommandText = procedureString;


    mySqlCommand.CommandType = CommandType.StoredProcedure;



    Now you need to actually open the connection.

    mySqlConnection.Open();

    Now RETRIEVE a DataAdapter provided by your Command class

    SqlDataAdapter adapter = mySqlCommand.ExecuteReader();

    Now you have a data adapter that is linked with the actual query results from your Stored Procedure.

    From there use the adapter variable to fill in a data set ... I think you can figure the rest out Wink

    Hope that helped.
    Tuesday, January 15, 2008 5:48 AM
  • Uhhh... I double checked the MSDN Documentation just to be sure but, SqlCommand.ExecuteReader() doesnt return a SqlDataAdapter, it returns a SqlDataReader. A SqlDataReader is a forward only collection of rows, so Im not entirely sure thats what the op wants.

     

    Tuesday, January 15, 2008 3:38 PM
  •  Sabrecat wrote:

    Uhhh... I double checked the MSDN Documentation just to be sure but, SqlCommand.ExecuteReader() doesnt return a SqlDataAdapter, it returns a SqlDataReader. A SqlDataReader is a forward only collection of rows, so Im not entirely sure thats what the op wants.

     

     

    Hi,

     

    I think what mcox05 meant was you don't have to call a method of SqlCommand object to retrieve the results. You just pass the command object to the DataAdapter object and it will call the needed methods for you and fill the DataTable object. So, the order of statements should be like this :

     

    //1 : Connection creation and initialization

    //2 : Command creation and initialization including parameters

    //3 : DataAdapter creation and assignment of Command to DataAdapter's SelectCommand

    //4 : Open connection

    //5 : Create and Fill DataTable

    //6 : Close Connection.

     

    Try it.

     

    [Note : I think you missed the most important thing to describe here, that is the error message you recieved while executing your code. You should provide the error message to help others understand you problem.]

    Tuesday, January 15, 2008 4:03 PM
  • Im not the OP, but I agree with you that the steps you outlined will get you your data from the database

    Tuesday, January 15, 2008 4:39 PM
  • Thanks to the three of you for your time. MCOX05 is right. I am embarrassed to admit I never actually opened the connection. I don't know how I over looked that. I had originally experimented with having this set up a different way. Some how I forgot to put that back in when I redid this.

     

    I like the list that Decyclone made. I was thinking about making a check list of the basic components like this. I think this would be a good tool for me to use until all of this becomes second nature.

     

    Thanks for the reminder sabrecat to include the error. I was burnt when I posted this last night.

     

                                             Not seeing the trees for the forest,

                                              

                                                             -thanks

     

    Tuesday, January 15, 2008 9:03 PM
  •  

    Hi new to sql server,

     

    If your code worked, mark this post answered and put in the working code so that it can help someone having the same problem as you and everyone stops providing more solutions. You should always mark the best reply as answer to the problem, and close (logically) the post.

    Tuesday, January 15, 2008 9:43 PM
  • haha sorry I made the typo. I was fixiated on your code which used a DataAdapter that I actually typed it in! Wink

    Anyways, glad to see with some assistance that the solution works for you.
    Tuesday, January 15, 2008 10:10 PM
  •  

    Someone has recommended some code to use in the catch that might shed some light on the problem.

     

    Below is what I got back with the catch scenario:

     

    Sql Error:

    Message: Procedure or function 'spInsertNewPet' expects parameter @CustID , which was not supplied.

    Number: 201

    Class: 16                

    Line Number: 0

    Procedure: spInsertNewPet

    State4

    server localhost/sqlexpress

     

    I don't understand why the parameter @CustID would not have been supplied. I think my c# and stored procedure are right. Obviously, something is wrong. I don't understand why the parameter isn't being supplied. I think I am using the right variable for the value in the parameter that is in the c# code.

     

    I also retested my stored proc in Sql Server and confirmed that it did work. I checked this by looking at the new row it added. This was done correctly.

     

    I have included the stored the script I used to create the stored proc. I am hoping this might help.

     

    This is the latest version of my code. This still doesn't work. See the error above and the paragraph below it for what I found out from it. The code that generated this is below in the catch.

     

     

    Code Block

    try

    {

    SqlConnection mySqlConnection = new SqlConnection();

    mySqlConnection.ConnectionString = ConfigurationManager.AppSettings["myConnectionString"];

    string procedureString = "spInsertNewPet";

    SqlCommand mySqlCommand = new SqlCommand("spInsertNewPet", mySqlConnection);

    mySqlCommand.CommandText = procedureString;

    mySqlCommand.CommandType = CommandType.StoredProcedure;

    mySqlConnection.Open();

    SqlDataAdapter mySqlDataAdapter = new SqlDataAdapter();

    mySqlDataAdapter.SelectCommand = mySqlCommand;

    DataSet myDataSet = new DataSet();

    mySqlDataAdapter.Fill(myDataSet, "Pets");

     

    mySqlCommand.Parameters.AddWithValue("@CustID", SqlDbType.Int).Value = myPet.CustID;

    mySqlCommand.Parameters.AddWithValue("@PetName", SqlDbType.VarChar).Value = MyPet.PetName;

    mySqlCommand.Parameters.AddWithValue("@BreedID", SqlDbType.Int).Value = myPet.BreedID;

    mySqlCommand.Parameters.AddWithValue("@Gender", SqlDbType.Char).Value = myPet.Gender;

    mySqlCommand.Parameters.AddWithValue("@Weight", SqlDbType.Decimal).Value = myPet.Weight;

    mySqlCommand.Parameters.AddWithValue("@DOB", SqlDbType.SmallDateTime).Value = myPet.DOB;

    mySqlCommand.Parameters.AddWithValue("@DateFixed", SqlDbType.SmallDateTime).Value = myPet.DateFixed;

     

    mySqlCommand.ExecuteNonQuery();

    mySqlConnection.Close();

    mySqlConnection.Dispose();

    mySqlCommand.Dispose();

    GC.Collect();

    }

    catch (SqlException sqlex)

    {

    foreach (SqlError _sqlError in sqlex.Errors)

    {

    Console.WriteLine("SqlError:{0}\tMessage:{1}{0}\tNumber:{2}{0}\tClass:{3}{0}\n\tLine number:{4}{0}\tProcedure:{5}{0}\tState{6}{0}\tServer{7}",

    Environment.NewLine, _sqlError.Message, _sqlError.Number, _sqlError.Class, _sqlError.LineNumber, _sqlError.Procedure, _sqlError.State, _sqlError.Server);

    }

    }

     

     

    Here is the script that I used to create the stored proc

     

     

    Code Block

    USE VetClinic

    GO

    CREATE PROC spInsertNewPet

    (

    @CustID int,

    @PetName varchar(20),

    @BreedID int,

    @Gender char(1),

    @Weight decimal,

    @DOB smalldatetime,

    @DateFixed smalldatetime

    )

    AS

    INSERT INTO Pets

    (

    CustID,

    PetName,

    BreedID,

    Gender,

    Weight,

    DOB,

    DateFixed

    )

    VALUES

    (

    @CustID,

    @PetName,

    @BreedID,

    @Gender,

    @Weight,

    @DOB,

    @DateFixed

    )

    GO

     

     

    Wednesday, January 16, 2008 12:32 AM
  •  

    What is the Type of myPet.CustID?

     

    - and -

     if you set a break point at the statement

    mySqlCommand.Parameters.AddWithValue("@CustID", SqlDbType.Int).Value = myPet.CustID;

      what is the value of myPet.CustID?

     

     Just a guess but the value might be null or not of type that you can cast to int

    Wednesday, January 16, 2008 5:30 AM
  • Hi,

     

    I checked your sample codes and found there was something wrong with the following:

    Code Block

    string procedureString = "spInsertNewPet";

    SqlCommand mySqlCommand = new SqlCommand("spInsertNewPet", mySqlConnection);

    mySqlCommand.CommandText = procedureString;

    mySqlCommand.CommandType = CommandType.StoredProcedure;

    mySqlConnection.Open();

    SqlDataAdapter mySqlDataAdapter = new SqlDataAdapter();

    mySqlDataAdapter.SelectCommand = mySqlCommand;

    DataSet myDataSet = new DataSet();

    mySqlDataAdapter.Fill(myDataSet, "Pets");

     

    Note that mySqlCommand is used to insert some values into Pets table, I don't think you can give mySqlCommand to mySqlDataAdapter.SelectCommand. You can try to change it as follows:

    mySqlDataAdapter.SelectCommand = new SqlCommand("select * from Pets", mySqlConnection);

     

    Hope this helps,

    Regards,

    Wednesday, January 16, 2008 5:35 AM