C# code on asp.net page to set data

Set data to SQL Database from random information

Page 1 of 1

7 Replies - 2641 Views - Last Post: 06 July 2010 - 09:38 AM Rate Topic: -----

#1 CDWitte  Icon User is offline

  • New D.I.C Head

Reputation: 1
  • View blog
  • Posts: 4
  • Joined: 05-July 10

C# code on asp.net page to set data

Posted 05 July 2010 - 04:52 PM

Hello fellow users,

Ok, so I'm totally stuck, I really didn't want to have to ask for help but I'm at the breaking point. I am attempting to create a web game; I am trying to add a class to instantiate fish objects, and populate their information into a SQL database. I have verified the SQL connection string because it is being used successfully in another portion of the code. It seems to go to the catch block for an exception at the line "cmdIns.ExecuteNonQuery();". If you can help me to understand what I might be doing wrong that would be great. If you can provide a better way to achieve the same results, that would be welcome as well.

Thank you,
Chris Witte



namespace WebApplication1
{
    public class AddFish
    {
        public void Main(int iterations,Page pg)
        {
            //Sets Query string
            string sqlIns = "INSERT INTO Fish (Species_ID, Size, LocationX, LocationY) VALUES (@Species_ID, @Size, @LocationX, @LocationY)";
            //Create connection
            SqlConnection db = new SqlConnection();
            //Retrieve connectionstring from method, that calls into system.web 
            //verrified works 
            GetConnectionString1 connstr = new GetConnectionString1();
            db.ConnectionString= (connstr.GetConnectionString(null));
            
            //Makes a new instance of SqlCommand
            SqlCommand cmdIns = new SqlCommand(sqlIns,db);
        
            try
            {
                //Generate needed random numbers
            Random x = new Random(Environment.TickCount + 1);
            Random y = new Random(Environment.TickCount + 2);
            Random z = new Random(Environment.TickCount + 3);
            int swt = x.Next(0, 1);
            int size = 0;
            int locy = 0;
                
                //Switch using random number
                switch (swt.ToString())
                {
                        //For case 0 create a Bass and add it to DB 
                    case "0":
                        Bass myBass = new Bass();
                        // Generate random number for size
                        size = y.Next(4, 32);
                        // Assigns values
                        myBass.Size = Convert.ToInt32(size);
                        myBass.LocationX = 1;
                        locy = z.Next(1, 2);
                        myBass.LocationY = Convert.ToInt32(locy);
                    
                        // ADD Values to the sqlcommand
                        cmdIns.Parameters.AddWithValue("@Species_ID", 1);
                        cmdIns.Parameters.AddWithValue("@Size", myBass.Size);
                        cmdIns.Parameters.AddWithValue("@LocationX", myBass.LocationX);
                        cmdIns.Parameters.AddWithValue("@LocationY", myBass.LocationY);
                        // Open connection
                        db.Open();
                        // Executes command
                        cmdIns.ExecuteNonQuery();
                        // Clears the sqlcommand info
                        cmdIns.Parameters.Clear();
                     break;

                case "1":
                    
                    Catfish myCat = new Catfish();
                    size = y.Next(8, 48);
                    myCat.Size = Convert.ToInt32(size);
                    myCat.LocationX = 1;
                    locy = z.Next(1,2);
                    myCat.LocationY = Convert.ToInt32(locy);
                    
                    cmdIns.Parameters.AddWithValue("@Species_ID", 1);
                    cmdIns.Parameters.AddWithValue("@Size", myCat.Size);
                    cmdIns.Parameters.AddWithValue("@LocationX", myCat.LocationX);
                    cmdIns.Parameters.AddWithValue("@LocationY", myCat.LocationY);
                    db.Open();
                    cmdIns.ExecuteNonQuery();
                    cmdIns.Parameters.Clear();
                    break;
            }
            }
            catch (Exception)
            {
                MessageBox msg = new MessageBox("Error While Sending Data --- ADDFISH", pg);


            } 
            finally
            {
            db.Close();
            }
           
        }
    }
}




Is This A Good Question/Topic? 0
  • +

Replies To: C# code on asp.net page to set data

#2 mayfan77  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 9
  • Joined: 19-June 10

Re: C# code on asp.net page to set data

Posted 05 July 2010 - 09:51 PM

Can I know the data type of the attributes in the database?
Was This Post Helpful? 0
  • +
  • -

#3 CDWitte  Icon User is offline

  • New D.I.C Head

Reputation: 1
  • View blog
  • Posts: 4
  • Joined: 05-July 10

Re: C# code on asp.net page to set data

Posted 05 July 2010 - 09:58 PM

Dear mayfan77,

For the table being accessed here all data types are INT.

Thank you,
Chris Witte
Was This Post Helpful? 0
  • +
  • -

#4 keakTheGEEK  Icon User is offline

  • D.I.C Regular
  • member icon

Reputation: 107
  • View blog
  • Posts: 344
  • Joined: 23-February 10

Re: C# code on asp.net page to set data

Posted 05 July 2010 - 11:50 PM

Looks like you have a lot going on here. Couple of questions. First, do you have a primary key specified for fish table? If so, is the primary key Species_ID? If Species_ID is the unique identifier for your fish table and is set up to auto increment upon each insertion, then you don't need to include it in your insert statement.

Also, in your catch block. I recommend using the exception object and passing the message property of the exception object to your MessageBox pop up. This will at least provide you with more specific details as to what the error is. Just modify your catch block to this:
catch (Exception ex)
{
 MessageBox msg = new MessageBox(ex.Message, pg);
} 



ex.Message will give you more information as to what Visual Studio is complaining about. There are also some more helpful methods and properties of the Exception class that you can use to your advantage while debugging your code. Hope this information is useful to you. GOOD LUCK!
Was This Post Helpful? 0
  • +
  • -

#5 CDWitte  Icon User is offline

  • New D.I.C Head

Reputation: 1
  • View blog
  • Posts: 4
  • Joined: 05-July 10

Re: C# code on asp.net page to set data

Posted 06 July 2010 - 12:34 AM

Hello keakTheGEEK,

Yeah it is quite a lot going on there, this is my first attempt dealing with asp.net and exception handling, I am having trouble getting a message to appear on the page from a separate class other than the .aspx page. So, I had to create a javascripting class, it does not seem to work with the "(exception ex) ex.Message" That is how I had it originally but it does not show a message box here is the code for that class. Species_ID is a Foreign Key and is it possible that that may be the issue? How can I check for auto increment? With the following code no message box is being shown, but with my original code it throws the message "Error While Sending Data --- ADDFISH". I also tried ex.Message without the .ToString().

Code from the AddFish Class.
catch (Exception ex)
            {
                MessageBox msg = new MessageBox(ex.Message.ToString(), pg);


            } 




Code for MessageBox
public class MessageBox
    {
        public MessageBox(String  msg, Page pg)
        {
        // define a javascript alertbox containing
        //the string passed in as argument
        // create a new label

        Label lbl = new Label();

        //add the javascript to fire an alertbox to the label and
        //add the string argument passed to the subroutine as the
        // message payload for the alertbox
        System.Text.StringBuilder sb = new System.Text.StringBuilder();
        sb.Append(@"<script language='javascript'>");
        sb.Append(@"window.alert('" + msg + "')</script>");
        lbl.Text = sb.ToString(); 
            
        
        // add the label to the page to display the alertbox


            pg.Controls.Add(lbl);
        }
    }




Ok that was worthless, I feel dumb now, I'm sure we have all been there at some point, I think it is because I am not assigning a Fish_ID which does not allow NULL values... How can I set that to auto increment; so that I can make this code run after deployment to add fish into the game?
Was This Post Helpful? 0
  • +
  • -

#6 keakTheGEEK  Icon User is offline

  • D.I.C Regular
  • member icon

Reputation: 107
  • View blog
  • Posts: 344
  • Joined: 23-February 10

Re: C# code on asp.net page to set data

Posted 06 July 2010 - 12:54 AM

My bad @CDWitte. I forgot that the traditional .NET MessageBox isn't available in ASP .NET (only for desktop/Winforms Applications). As far as Species_ID being a foreign key. That could potentially be the issue. A foreign key in one table references another key in another table. If the ID that the foreign key is referencing doesn't exist in the other table, then that will throw an exception. Hard to say though w/o knowing what the actual error is. I would create a label on your form temporarily and assign the Label.Text property to ex.Message (in your catch block) so you can see what error is being generated. Then you will at least have a better idea on what approach to take to solve it...
Was This Post Helpful? 0
  • +
  • -

#7 Frinavale  Icon User is offline

  • D.I.C Addict
  • member icon

Reputation: 203
  • View blog
  • Posts: 776
  • Joined: 03-June 10

Re: C# code on asp.net page to set data

Posted 06 July 2010 - 07:56 AM

I think I just answered a question regarding message boxes, Javascript etc.

(Edit: I answered a similar question here.)

Instead of using a message box (for debugging purposes), place a label on the page and set the label's text to the error message.

I would like to know what exception you are experiencing...the only way I can know that is if you post the error message or the exception type.

-Frinny

This post has been edited by Frinavale: 06 July 2010 - 07:59 AM

Was This Post Helpful? 0
  • +
  • -

#8 CDWitte  Icon User is offline

  • New D.I.C Head

Reputation: 1
  • View blog
  • Posts: 4
  • Joined: 05-July 10

Re: C# code on asp.net page to set data

Posted 06 July 2010 - 09:38 AM

OK, I fixed it.

Thank you all for your advice.
I had to add

cmdIns.Parameters.AddWithValue("@Fish_ID", (nextFish + 1));



Because I was net setting the Fish_ID which was the PK it was failing, I was able to see the exception by setting a breakpoint and hovering over (exception ex.

Another problem I am having with the above code is the random numbers, It seems to always give the value of 0 for the select statement, and 1 for locy, every time it is ran. I did fix the code,

case "1":
...
...
...
cmdIns.Parameters.AddWithValue("@Species_ID", 2);
...
...
break;



Maybe I set up my random number generator wrong I am looking into this right now. If I get the entire code to work properly I will post it in case someone else runs into this issue.

Thank you,
Chris Witte
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1