SQL and MySql

  • (2 Pages)
  • +
  • 1
  • 2

20 Replies - 654 Views - Last Post: 25 September 2017 - 03:13 PM Rate Topic: -----

#16 JeremyBenson11  Icon User is offline

  • D.I.C Head

Reputation: 0
  • View blog
  • Posts: 234
  • Joined: 10-January 14

Re: SQL and MySql

Posted 25 September 2017 - 10:06 AM

System.Windows.Markup.XamlParseException: 'The invocation of the constructor on type 'Dark_Awakening.Window1' that matches the specified binding constraints threw an exception.' Line number '3' and line position '2'. ---> System.Data.SqlServerCe.SqlCeException: 

   at System.Data.SqlServerCe.SqlCeCommand.ProcessResults(Int32 hr)
   at System.Data.SqlServerCe.SqlCeCommand.CompileQueryPlan()
   at System.Data.SqlServerCe.SqlCeCommand.ExecuteCommand(Commandbehavior behavior, String method, ResultSetOptions options)
   at System.Data.SqlServerCe.SqlCeCommand.ExecuteReader(Commandbehavior behavior)
   at System.Data.SqlServerCe.SqlCeCommand.ExecuteReader()
   at Dark_Awakening.DB.insertRoom(room Room) in c:\Users\JeremyBenson11\Documents\SharpDevelop Projects\Dark Awakening\Dark Awakening\DB.cs:line 557



line 557

rdr = cmdTokenTest.ExecuteReader();


Was This Post Helpful? 0
  • +
  • -

#17 JeremyBenson11  Icon User is offline

  • D.I.C Head

Reputation: 0
  • View blog
  • Posts: 234
  • Joined: 10-January 14

Re: SQL and MySql

Posted 25 September 2017 - 10:27 AM

Sorry, I got passed that line. I had a miss-spelling in the table name. My favorite error.

I'm having a new problem. My code is stuck in an infinite loop. "room token fetched." message box display over and over. I'm not sure how. I'm quite confused about try catch setup.

The function has been restructured a little bit.

public void insertRoom(room Room)
		{
			
			// select token to see if it has been used before
			
			SqlCeConnection cn = new SqlCeConnection(connectionString);
			
			 if (cn.State == ConnectionState.Closed)
           	 {
                cn.Open();
                
                
                Room.token = Convert.ToBase64String(Guid.NewGuid().ToByteArray());
                bool tokenInserted = false;
              
                while(!tokenInserted)
                {
                	
                	// test token
					SqlCeDataReader rdr = null;
				
 	           	 	SqlCeCommand cmdTokenTest = new SqlCeCommand("SELECT token FROM rooms where token = " + '\'' + Room.token + '\'', cn);
 	           	 	
 	           	 	try{
 	           	 		
 	           	 		rdr = cmdTokenTest.ExecuteReader();
 	           	 		MessageBox.Show("Room token fetched");
 	           	 		
 	           	 		while(rdr.Read())
		 	           	 	{
		 	           	 		 
 	           	 				MessageBox.Show("fetched token: " + rdr.GetString(0));
		 	           	 		if(rdr.GetString(0) == Room.token)
		 	           	 		{
		 	           	 			
		 	           	 			Room.token = Convert.ToBase64String(Guid.NewGuid().ToByteArray());
		 	           	 			MessageBox.Show("re-value token because of match in db");
		 	           	 		}else{
		 	           	 			
		 	           	 			// insert room here, token not used
		 	           				
									SqlCeCommand cmdRoom;
					                
					                string query = "insert into rooms (name, outside, firstDescription, description, area, token) values (" +
					                	
					                	'\'' +  Room.name + '\'' + ',' +
					                	'\'' +  Room.outside + '\''  + ',' +
					                	'\'' +  Room.firstDescription + '\''  + ',' +
					                	'\'' +  Room.description + '\''  + ',' +
					                	'\'' +  Room.area + '\''  + ',' +
					                	'\'' +  Room.token + '\'';
					                	
					                query += ");";
					                
					                MessageBox.Show(query);
					                
					                cmdRoom = new SqlCeCommand(query, cn);
					
					                 cn.Close(); 	           	 			
		 	           	 			
		 	           	 			tokenInserted = true;
					                
								      try
								
								      {
								
								        cmdRoom.ExecuteNonQuery();
								
								        MessageBox.Show("room insert complete: "+Room.name+"", "Message", MessageBoxButtons.OK);
								
								      }
								
								      catch (SqlCeException sqlexception)
								
								      {
								
								        MessageBox.Show(sqlexception.Message, "Sqlexception.",
								
								          MessageBoxButtons.OK, MessageBoxIcon.Error);
								
								      }
								
								      catch (Exception ex)
								
								      {
								
								        MessageBox.Show(ex.Message, "ex.Message.", MessageBoxButtons.OK,
								
								          MessageBoxIcon.Error);
								
								      }
								
								
								       
		 	           	 			
		 	           	 			// end room token not found
		 	           	 		}
		 	           	 		
		 	           	 		// end while
		 	           	 	}
 	           	 		
 	           	 		// end try fetch token
 	           	 	}
					 catch (SqlCeException sqlexception)
						
						      {
						
						        MessageBox.Show(sqlexception.Message, "Sqlexception.",
						
						          MessageBoxButtons.OK, MessageBoxIcon.Error);
						
						      }
						
						      catch (Exception ex)
						
						      {
						
						        MessageBox.Show(ex.Message, "ex.Message.", MessageBoxButtons.OK,
						
						          MessageBoxIcon.Error);
						
						      }
						
 	           
			        // end insert room with token loop
				}
			
                // end if connection closed
			 }
			
			// end insert room
		}

Was This Post Helpful? 0
  • +
  • -

#18 andrewsw  Icon User is online

  • the case is sol-ved
  • member icon

Reputation: 6379
  • View blog
  • Posts: 25,774
  • Joined: 12-December 12

Re: SQL and MySql

Posted 25 September 2017 - 11:56 AM

There is a (C#) debugging tutorial linked in my signature below. Take this and you will be able to set breakpoints and step through your code and discover why the loop never ends.
Was This Post Helpful? 0
  • +
  • -

#19 JeremyBenson11  Icon User is offline

  • D.I.C Head

Reputation: 0
  • View blog
  • Posts: 234
  • Joined: 10-January 14

Re: SQL and MySql

Posted 25 September 2017 - 12:41 PM

I've got some break points in Sharpdevelop. I'll set some more important points, and come back with some data. The problem is I'm not sure what data is supposed to be in the SQLCE objects, lol.

Alright, I set up some breakpoints, but I haven't a clue what I'm supposed to see. Maybe you could teach me a bit.

Here's an image of the rdr:
https://imgsafe.org/image/95aa6d5b29

rdr hasrows opened up:
https://imgsafe.org/image/95aaa86da1

also on with nonpublic members:
https://imgsafe.org/image/95aa5c0880

I also see an error in there which say sqlce does not support hasrows if underlying cursor is not scrollable. Not sure what it means.

The code goes to this line while(rdr.Read()) and jumps to line 638:

which is near the end of the loop here:

		// end try fetch token
 	           	 	} catch (SqlCeException sqlexception)
						
						      {
						
						        MessageBox.Show(sqlexception.Message, "Sqlexception.",
						
						          MessageBoxButtons.OK, MessageBoxIcon.Error);
						
						      }
						
						      catch (Exception ex)
						
						      {
						
						        MessageBox.Show(ex.Message, "ex.Message.", MessageBoxButtons.OK,
						
						          MessageBoxIcon.Error);
						
						      }
						
 	           
			        // end insert room with token loop
				}



that first top bracket is where it stops and goes back into the loop. Oddly I've got breakpoints in the catches, but they're never fired.
Was This Post Helpful? 0
  • +
  • -

#20 andrewsw  Icon User is online

  • the case is sol-ved
  • member icon

Reputation: 6379
  • View blog
  • Posts: 25,774
  • Joined: 12-December 12

Re: SQL and MySql

Posted 25 September 2017 - 01:08 PM

Sorry, I'm finding your code very hard to follow and overly intricate. (You also have everything inside a condition if the connection is closed: what if the connection is open?)

Personally, I'd remove all the code - all the messages and exception catching. Start with a few simple statements that actually carry out what you are trying to achieve. This will be easier to debug and to see what is actually happening.

I'm very much in favour of defensive programming: throwing in a few if statements to check that things are flowing as expected. However, sometimes it is a good idea to start by just checking the happy path. That is, assume that everything will work and check that the basic operation(s) you desire will succeed.

You should also be using parameterized queries, not concatenating values to SQL strings.



Personally, I prefer to defer introducing exception handling code (unless it is part of an accepted coding pattern) until I've run and tested the code a number of times; then I have a better understanding of what might go wrong and what sort of exceptions I might anticipate.
Was This Post Helpful? 0
  • +
  • -

#21 JeremyBenson11  Icon User is offline

  • D.I.C Head

Reputation: 0
  • View blog
  • Posts: 234
  • Joined: 10-January 14

Re: SQL and MySql

Posted 25 September 2017 - 03:13 PM

Sorry for any confusion. I had a few things to learn, and fixed code. For those reading make a new cmd, query, and connection for every statement. Best to break everything up into functions. One query type per function, that way it's less confusing.

Also ConpactView can be found on sourceforge. A free SDF viewer, so you can see visually if your data is being entered or not.

Here's the start of my fix:

These functions in my DB class helped a lot.
public void createTable(string name, string[] fields)
		{
			
			SqlCeConnection cn = new SqlCeConnection(connectionString);
			
			 if (cn.State == ConnectionState.Closed)
           	 {
                cn.Open();
                
                SqlCeCommand cmd;
                
                string query = "create table "+name+" (";
                	
                foreach(string elem in fields)
                {
                	
                	query += elem;
                	
                }
                		
                query +=");";
                
                //MessageBox.Show(query);
                
                cmd = new SqlCeCommand(query, cn);

			      try
			
			      {
			
			        cmd.ExecuteNonQuery();
			
			       // MessageBox.Show("Table created: "+name+"", "Message", MessageBoxButtons.OK);
			
			      }
			
			      catch (SqlCeException sqlexception)
			
			      {
			
			       	 //  MessageBox.Show(sqlexception.Message, "Sqlexception.",
					//   MessageBoxButtons.OK, MessageBoxIcon.Error);
			
			      }
			
			      catch (Exception ex)
			
			      {
			
			        //MessageBox.Show(ex.Message, "ex.Message.", MessageBoxButtons.OK,
					// MessageBoxIcon.Error);
			
			      }
			
			
			        cn.Close();
			
				}
			// close create table function
		}
		
		public void insertRoom(room Room)
		{
			
			// first create a token
			Room.token = Convert.ToBase64String(Guid.NewGuid().ToByteArray());
			
			// test the token for use
			
			while(testToken("rooms", Room.token))
			{
				
				Room.token = Convert.ToBase64String(Guid.NewGuid().ToByteArray());
				
			}
			
			SqlCeConnection conn = new SqlCeConnection(connectionString);
			
			if(conn.State == ConnectionState.Closed)
			{
				
				conn.Open();
				
			}
			
			string query = "INSERT INTO rooms (name, outside, firstDescription, description, area, token) " +
							"VALUES (@name, @outside, @firstDescription, @description, @area, @token)";
			
			try{
				
				SqlCeCommand cmd = new SqlCeCommand(query, conn);
				
				cmd.Parameters.AddWithValue("@name", Room.name);
				cmd.Parameters.AddWithValue("@outside", Room.outside);
				cmd.Parameters.AddWithValue("@firstDescription", Room.firstDescription);
				cmd.Parameters.AddWithValue("@description", Room.description);
				cmd.Parameters.AddWithValue("@area", Room.area);
				cmd.Parameters.AddWithValue("@token", Room.token);
				
				cmd.ExecuteNonQuery();

			}catch(SqlCeException ex)
			{
				
				MessageBox.Show(ex.Message);
				
			}finally{
				
				conn.Close();
				
			}
			// end insert room
		}
		
		public bool testToken (string table, string token)
		{
			
			SqlCeConnection conn = new SqlCeConnection(connectionString);
			
			if(conn.State == ConnectionState.Closed)
			{
				
				conn.Open();
				
			}
			
			string query = "SELECT token FROM rooms WHERE token = @token";
			
			try{
				
				SqlCeCommand cmd = new SqlCeCommand(query, conn);
				
				cmd.Parameters.AddWithValue("@token", token);
				
				cmd.CommandType = CommandType.Text;
				
				string fetchedToken = (string)cmd.ExecuteScalar();
				
				MessageBox.Show("fetched: " + fetchedToken);
				
				if(fetchedToken != token)
				{
					
					return false;
					
				}else{
					
					return true;
					
				}
				
			}  catch (SqlCeException sqlexception)
			
			 {
			
			       	 MessageBox.Show(sqlexception.Message, "Sqlexception.",
				     MessageBoxButtons.OK, MessageBoxIcon.Error);
			
			 }
			catch (Exception ex)
			{
			
			        MessageBox.Show(ex.Message, "ex.Message.", MessageBoxButtons.OK,
					MessageBoxIcon.Error);
			}
			
			return true;
			
			// end function test token
		}
		
		
		public string returnToken(string table, string field, string value)
		{
			
			string returnToken = "";
			
			switch(field)
			{
					
					
				case "name":
					// fetch a token and return
					SqlCeConnection conn = new SqlCeConnection(connectionString);
					
					if(conn.State == ConnectionState.Closed)
					{
						
						conn.Open();
						
					}
					
					string query = "SELECT token FROM " + table + " WHERE " + field + " = @value";
					
					try{
						
							SqlCeCommand cmd = new SqlCeCommand(query, conn);
					
							cmd.CommandType = CommandType.Text;
							
							cmd.Parameters.AddWithValue("@value", value);
							
							returnToken = (string)cmd.ExecuteScalar();
						
					}catch(SqlCeException er)
					{
						
						MessageBox.Show(er.Message);
						
					}finally
					{
						
						conn.Close();
						
					}
				
					// end fetch token by name field in table					
				break;
					
				// end type of feild table
			}
			
			
			return returnToken;
			
			// end return token from table by feild and value
		}


This post has been edited by JeremyBenson11: 25 September 2017 - 03:17 PM

Was This Post Helpful? 0
  • +
  • -

  • (2 Pages)
  • +
  • 1
  • 2