Insert Error

L

Leeus

Well-known member
Joined
Dec 23, 2002
Messages
50
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()
Click to expand...

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???
 
Whats the error message?

Also, you cant execute "SELECT @@IDENTITY" AFTER you run the INSERT, you need to keep them together (Im almost positive but not 100%). But Id get the INSERT working first :)

-Nerseus
 
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.
 
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 cant recall offhand. I know that you generally dont WANT to, since its another round trip for something that you could easily get during your first call.

-Nerseus
 
Thanks for that SQLException, it worked and guess what?? I hadnt closed the previous data reader, it all works great now!

Thanks!
 
Back
Top