Problem with filling table in DB

[Reinout]

I have a problem filling up a table into my SQL Database. I can write in almost every table, accept from one of them.

I'll explain the situation. I have a table, I'll call it X, and another one, let's say Y. I can fill up both without any problem, so I guess my connection to the DB can't be very wrong. Now, there is a many-to-many connection between those tables, therefor I made an extra table, called XY. There is the problem, seems like I can't write into that one. I already tried to rename it, without result, I even deleted it and started all over, but still I can't write into that table. As a matter of fact, I can, when I'm in the query analyzer of SQL Server itself. But I can't when I'm trying to connect to it through ASP.NET.

Here's the code I'm using :

'set up connection
Dim myConnection As New SQLConnection()
myConnection.ConnectionString = "Data Source=[IP];" & _
"Initial Catalog=[Database];" & _
"User ID=***;" & _
"Password=***"
MyConnection.Open()

...

If btBier.Text = "Nieuw"
BierId = ddlBier.SelectedItem.Value
Else
Dim myCommandBier As New SQLCommand _
("insert into Bier VALUES('"& tbNaamBier.Text & "','" & BrouwerId & "') SELECT @@Identity", myConnection)
BierId = myCommandBier.ExecuteScalar()
End if

Dim myCommandKaart As New SQLCommand _
("insert into Kaart VALUES('"& tbTekst.Text & "','" & tbVorm.Text & "') SELECT @@Identity", myConnection)
KaartId = myCommandKaart.ExecuteScalar()
		
Dim myCommandKaartBier As New SQLCommand _
("insert into kaartbier VALUES('" & KaartId & "','" & BierId & "')", myConnection)

Can somebody help me please? I can't seem to find the mistake :s

 

[crazyboybert]

What error do you get? Sounds like the account you are using to access SQL doesnt have permissions on the table in question.

Rob

Go placidly amidst the noise and haste, and remember what peace there may be in silence - Erhmann 1927

 

[Reinout]

I get no error Rob. It's just : when I look in the database, nothing has changed into that table.
I checked the permissions before I asked this question, but I have the same permisions on that table as on the others.

 

[crazyboybert]

OK. I can't see anything wrong with code other than there is no call to execute the last Command. I'm assuming that is in your code but not included in the example?

As a side point, you should really consider the use of Stored Procedures instead of inline SQL or at the very least replace single quotes with two single quotes when you build your sql string to avoid SQL Injection like this...

tbNaamBier.Text.Replace("'", "''"

With your current code, depending on the permissions of the account accessing your database a "hacker" could really cause some trouble. Heres an msdn article on data access security which covers this topic.

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnnetsec/html/SecNetch12.asp

Rob

Go placidly amidst the noise and haste, and remember what peace there may be in silence - Erhmann 1927

 

[Reinout]

Oh god... I forgot the

myCommandKaartBier.ExecuteNonQuery()

I knew it was going to be a stupid mistake, but this one is unbelievable.

Well, thanks for your help, it's been a long time (in the spring of 2002) since I last worked with ASP.NET.

I'll read the MSDN article, my previous page was for an intranet without internet acces, so the protection is rather new to me. I hope I'll understand