Not inserting into SQL

[tehon3299]

This is my code:

		Sub UpdateDB()
			Dim MyCommand As New SqlCommand
			Dim MyConnection As New SqlConnection
			Dim MySQLDataAdapter as New SqlDataAdapter
			Dim SessionCmd As String
			Dim File as String
			File = Session("sFileName")
			
			MyConnection = New SqlConnection("server=(local);database=SongShare;Trusted_Connection=yes")

			SessionCmd = "INSERT INTO tmSongs FileName VALUES (" & File & ")"
			
			MyCommand = New SqlCommand(SessionCmd, MyConnection)
		End Sub

I am calling this function by saying UpdateDB() and for some reason its not putting anything in my SQL table. I am putting the filename is a session variable in a global.asax. Whats wrong with this code??

 

[quwiltw]

youre never actually executing the command in this code. youd need to call MyCommand.ExecuteNonQuery() or add the MyCommand as your InsertCommand property of the dataadapter and call its Update() command. Shouldnt FileName be in parens after tmSongs too?

 

[tehon3299]

Thanks quwiltw I just noticed that. Now I get a

"Timeout expired. The timeout period elapsed prior to obtaining a connection from the pool. This may have occurred because all pooled connections were in use and max pool size was reached."

Whats this all about? I have about 800 enties going into this database.

 

[quwiltw]

Can you eithe repose the update of your code or edit the old one? Cause I cant see where youre opening the connection either.

 

[tehon3299]

Here ya go:

		Sub UpdateDB()
			Dim MyCommand As New SqlCommand
			Dim MyConnection As New SqlConnection
			Dim MySQLDataAdapter as New SqlDataAdapter
			Dim SessionCmd As String
			Dim File as String
			File = Session("sFileName")
			
			MyConnection = New SqlConnection("server=(local);database=SongShare;Trusted_Connection=yes")

			SessionCmd = "INSERT INTO tmSongs (FileName) VALUES (" & File & ")"
			
			MyCommand = New SqlCommand(SessionCmd, MyConnection)
			
			MyCommand.Connection.Open()
					
			Try
				MyCommand.ExecuteNonQuery()
			Catch Exp As SQLException
				If Exp.Number = 2627
			        
				Else
			        
				End If
			End Try

MyCommand.Connection.Close()
		End Sub

Thanks

 

[quwiltw]

Youre not closing the connection.

The connection.Open() should also be in a try catch block for good measure.

 

[tehon3299]

Thanks. I edited my other code. It just doesnt insert anything into the table. Theres just nothing in it.

 

[quwiltw]

try printing out the exception and see what it says. its probably throwing an exception but youre essentially catching and suppressing it.

 

[tehon3299]

How would I print out the exception?

 

[tehon3299]

I have figured out that its getting to the ELSE inside of the CATCH. Its error number 128. What is that?

 

[quwiltw]

Umm.. I dont know why dont you look at the Message property of the exception?

Set a break point at the exception and look at it.

 

[tehon3299]

Heres the message...

Line 1: Incorrect syntax near Song_1.mp3.

I think there has to be a problem with my insert command. Do you see anything?

 

[tehon3299]

OK...I think there is something wrong with the insert because if it comes to a number to insert it will but once it gets for letters it bombs.

 

[quwiltw]

nope, have you tried pasting into QueryAnalyzer and see if it runs?

 

[tehon3299]

it runs but i cant test it with the variable so how should i test it?

 

[tehon3299]

Its not taking the whole string into the INSERT statement because I have the word break as something that Im inserting into the table and it thinks im trying to break from a loop.