Insert Error

[Leeus]

Hi all, here is my code for a start

 

Try

 

kermitconnection = New SqlConnection("Integrated Security = True;Data Source = Kermit;Initial Catalog = FastTrack;")

kermitconnection.Open()

Dim findemail As SqlDataReader

Dim cmdfindemail As SqlCommand

cmdfindemail = New SqlCommand("select cont_key from contacts where cont_email = '" & fromaddr & "'", kermitconnection)

findemail = cmdfindemail.ExecuteReader

Dim contkey As Integer

contkey = 0

Do While findemail.Read

If Not findemail.Item("cont_key") Is DBNull.Value Then

contkey = findemail.Item("cont_key")

End If

Loop

 

If contkey = 0 Then

contkey = 3720

End If

 

Dim cmdinsertsupportitem As SqlCommand

txtdetails.AppendText("Insert into support (ContKey, DateRaised, ProductKey, ShortDesc, FullDesc, StatusKey, UrgencyKey, OwnerKey, TakenByKey) Values ('" & contkey & "', '" & DateTime.Now.ToString("dd\-MMM\-yy") & "', '2', '" & subject & "', '" & body & "', '3', '3', 'SUPPORT', 'SUPPORT')")

cmdinsertsupportitem = New SqlCommand("Insert into support (ContKey, DateRaised, ProductKey, ShortDesc, FullDesc, StatusKey, UrgencyKey, OwnerKey, TakenByKey) Values ('" & contkey & "', '" & DateTime.Now.ToString("dd\-MMM\-yy") & "', '2', '" & subject & "', '" & body & "', '3', '3', 'SUPPORT', 'SUPPORT')", kermitconnection)

cmdinsertsupportitem.ExecuteNonQuery()

MsgBox(contkey)

 

Dim getident As SqlDataReader

Dim cmdgetident As SqlCommand

cmdgetident = New SqlCommand("SELECT @@IDENTITY AS Ident", kermitconnection)

 

getident = cmdgetident.ExecuteReader

Dim out As String

Do While getident.Read

out = getident.Item("Ident")

Loop

MsgBox(out)

 

Catch

MsgBox("Error connecting")

End Try

kermitconnection.Close()

 

I have narrowed the problem down to the insert section, problem is if i paste in what the string is into query analyser it works fine, is there a better way to find out what the error is and why is it not working in vb but working fine if i manually paste it in???

[Nerseus]

What's the error message?

 

Also, you can't execute "SELECT @@IDENTITY" AFTER you run the INSERT, you need to keep them together (I'm almost positive but not 100%). But I'd get the INSERT working first :)

 

-Nerseus

[Leeus]

The error is the one Ihave in my catch, just a generic one, can I return the actual SQL server error, and if so, how?

 

I read that as long as I keep the same connection open I can run an @@identity later, anyway I KNOW the code is stumbling on the insert.

[Nerseus]

For now you can just catch a generic Exception object. Use MsgBox to show e.Message (assuming you call your Exception variable e).

 

You can also trap for SqlException, but the generic one will probably be fine for now.

 

You might be able to use the @@IDENTITY later, I can't recall offhand. I know that you generally don't WANT to, since it's another round trip for something that you could easily get during your first call.

 

-Nerseus

[Leeus]

Thanks for that SQLException, it worked and guess what?? I hadn't closed the previous data reader, it all works great now!

 

Thanks!