my INSERT is not working

[Tom Wright]

I'm not getting an exception when my code runs thru this method, but it's also not writing out this record. Any suggestion? private void WriteRecord(byte[] ByteBuffer, int TrackNumber) { //Get Query record and write response to it. SqlConnection myConnection = new SqlConnection(strConnect); SqlDataReader myReader = null; SqlTransaction transaction = myConnection.BeginTransaction(); try { myConnection.Open(); SqlCommand myCommand = new SqlCommand("SELECT * FROM DMV2 WHERE DMV2_TRACK_NO = " + TrackNumber + " AND DMV2_RCD_TYPE = 'Q'", myConnection); myCommand.Connection = myConnection; myReader = myCommand.ExecuteReader(); myReader.Read(); String DMV2_T_KEY = myReader["DMV2_T_KEY"].ToString(); String DMV2_UNIT_NO = myReader["DMV2_UNIT_NO"].ToString(); String DMV2_QUERY_TYPE = myReader["DMV2_QUERY_TYPE"].ToString(); String DMV2_TRACK_NO = myReader["DMV2_TRACK_NO"].ToString(); String DMV2_ORG_DTTM = myReader["DMV2_ORG_DTTM"].ToString(); String DMV2_ORG_WORKSTATION = myReader["DMV2_ORG_WORKSTATION"].ToString(); String DMV2_ORG_ID = myReader["DMV2_ORG_ID"].ToString(); String DMV2_RCV_WORKSTATION = myReader["DMV2_RCV_WORKSTATION"].ToString(); String myText = "INSERT INTO DMV2" + "(DMV2_RCD_TYPE, DMV2_QUERY_TYPE, DMV2_TRACK_NO, DMV2_T_KEY, DMV2_UNIT_NO, " + "DMV2_ORG_DTTM, DMV2_ORG_WORKSTATION, DMV2_ORG_ID, DMV2_RCV_DTTM, DMV2_RCV_WORKSTATION, " + "DMV2_RCV_STATUS, DMV2_DATA_TEXT)" + "VALUES ('R', '" + DMV2_QUERY_TYPE + "', '" + DMV2_TRACK_NO + "', '" + DMV2_T_KEY + "', '" + DMV2_UNIT_NO + "', '" + DMV2_ORG_DTTM + "', '" + DMV2_ORG_WORKSTATION + "', '" + DMV2_ORG_ID + "', CURRENT_TIMESTAMP, '" + DMV2_RCV_WORKSTATION + "', 'P','" + ByteBuffer.ToString() + "')"; myCommand = new SqlCommand(myText, myConnection, transaction).ExecuteNonQuery(); transaction.Commit(); } catch(SqlException sqlError) { MessageBox.Show(sqlError.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error); transaction.Rollback(); } myConnection.Close(); }

Tom Wright tawright915@yahoo.com

[Empires]

You should switch to stored procedures if possible. One thing I see is this 'ByteBuffer.ToString()', this is going to return System.Byte[] -- thats all I see from here, the problem is probably a datatype. Copy the myText to your clipboard during an execution and try to run that query exactly as it was copied into SQL Query Analyzer, or use SQL Profiler to see whats happening.

Empires

[Tom Wright]

Yeah I'm seeing System.Byte[]. Thanks for the heads up on that. However, if I put a messagebox(I can't step thru it on my pc so I have to use message boxes to see where I am at in the code) before the string is put together, but after the select statement is run, nothing pop's up. It's like it just dropping out of the method with out returning an exception. It's strange. Would an SP work faster since it's server side? Or does it just make it easier to insert rows? One other thing. Is looks like the primary key is a seeded key. Would this be something that could be causing the problem? Thanks

Tom Wright tawright915@yahoo.com

[Colin Angus Mackay]

Tom Wright wrote:

Would an SP work faster since it's server side? Or does it just make it easier to insert rows?

Or even make your application more secure - which is the primary reason I use stored procedures.

---

Scottish Developers events: * .NET debugging, tracing and instrumentation by Duncan Edwards Jones and Code Coverage in .NET by Craig Murphy * Developer Day Scotland: are you interested in speaking or attending? My: Website | Blog

[Colin Angus Mackay]

Tom Wright wrote:

private void WriteRecord(byte[] ByteBuffer, int TrackNumber)

Tom Wright wrote:

SqlCommand myCommand = new SqlCommand("SELECT * FROM DMV2 WHERE DMV2_TRACK_NO = " + TrackNumber + " AND DMV2_RCD_TYPE = 'Q'", myConnection);

I don't see how this even compiles as TrackNumber is an int. You should read about SQL Injection Attacks because this code is perfect for attacking. Here is an article that tells you What a SQL Injection Attack is, how it works, how to spot code that is susceptable and how to write better code[^]

---

Scottish Developers events: * .NET debugging, tracing and instrumentation by Duncan Edwards Jones and Code Coverage in .NET by Craig Murphy * Developer Day Scotland: are you interested in speaking or attending? My: Website | Blog

[Christian Graus]

Colin Angus Mackay wrote:

I don't see how this even compiles as TrackNumber is an int.

C# will work this out for you, if you concatenate a string and an int.

Christian Graus - Microsoft MVP - C++ Metal Musings - Rex and my new metal blog

[Colin Angus Mackay]

Christian Graus wrote:

C# will work this out for you, if you concatenate a string and an int.

:-O I just tried it. So it does.

---

Scottish Developers events: * .NET debugging, tracing and instrumentation by Duncan Edwards Jones and Code Coverage in .NET by Craig Murphy * Developer Day Scotland: are you interested in speaking or attending? My: Website | Blog

[Tom Wright]

Learn something new everyday. By the way I haven't read that link yet, but I did move all of the SQL statements to a SP. It works much better and seems like less of a headache. I should have done that a long time ago. I think I will still change how I'm passing my parameters to the SP as I'm still concatenating them together and do an ExecuteNonQuery. I think I'll look in to the parameters way of passing them, as well as read the link you sent me. Thanks

Tom Wright tawright915@gmail.com