Falling through to Error - HELP

[tdwainet]

Ok. I have querried this in Server Management Studio and The Record is there. I have the Item Code put together correctly prior to executing my query in VB; however, it keeps falling through to my error exception. Can anyone tell me what I'm doing wrong?

Public Sub Does_Case_UPC_Exists()
Dim Item_Case As String = String.Concat(TextBox1.Text, "-")
Item_Case = String.Concat(Item_Case, TextBox2.Text)
MsgBox(Item_Case)

Using cn As New SqlConnection(ConnectionString)
Using cmd As SqlCommand = cn.CreateCommand
cmd.CommandText = "SELECT OITM.CodeBars As U_upc_Code FROM OITM WHERE ItemCode = " & String.Format(Item_Case)
Try
cn.Open()
Dim o As Object = cmd.ExecuteScalar
If o IsNot Nothing Then
Me.TxtCaseUPC.Text = o.ToString
End If
cn.Close()
Catch ex As Exception
MsgBox("Item Does NOT exists in OITM table !")
Application.Exit()
End Try
End Using
End Using
End Sub

[GaryMazzone]

Have you looked at the actuall SQL that is being generated and sent to SQL server? Could you post it here

[tdwainet]

Is this what you are asking for ? If not please let me know what I need to post in addition to what I have.

Private Const ConnectionString As String = "Server=CORKYS-SERVER;user id=sa;password=sa;database=Corkys_Prod"

Private ReadOnly Property Connection() As SqlConnection
Get
Dim ConnectionToFetch As New SqlConnection(ConnectionString)
ConnectionToFetch.Open()
Return ConnectionToFetch

End Get
End Property

[techgnome]

no.. your command string... the SQL ... your select statement... what is the sql that is generated?

the first thing that strikes me as being off is your use of the string.format function... which I don't know why you are using it... as Item_Case already is a string, and you're not applying and formatting or anything.

I suspect other thigns are wrong as well, but that's the first and obvious...

-tg

[tdwainet]

Ok. I figured out why it was falling through . The data for the one field I was trying to get is NULL. I now have to build that data then update it. Now that I have that data built out. It is throwing out a weird error referring to column name invalid referring to actual data instead of a column name. I'm sorry to have so many questions. I'm primarily a DBA with SQL and I have done a lot of VB in the past; however, I'm fairly new to VB .net and I appreciate any assistance I can get expanding my horizons.
The following code is where it is erroring out on.

Public Sub Insert_Case_UPC_OITM(ByVal UPC_Code As String)
Dim Item_Case As String = String.Concat(TextBox1.Text, "-")
Item_Case = String.Concat(Item_Case, TextBox2.Text)

' Update OITM Table with New UPC Code for the CodeBars field
Dim adapter As New SqlDataAdapter
Dim sql As String = "UPDATE OITM SET CodeBars = " & UPC_Code & " WHERE OITM.ItemCode = " & Item_Case
MsgBox(sql)
Try
If ConnectionState.Closed Then
Connection.Open()
End If
adapter.UpdateCommand = New SqlCommand(sql, Connection)
adapter.UpdateCommand.ExecuteNonQuery()
MessageBox.Show("Case UPC for Item has been updated !", "Saved", MessageBoxButtons.OK, MessageBoxIcon.Information)
If ConnectionState.Open Then
Connection.Close()
End If

Catch ex As Exception
MsgBox(ex.ToString)
End Try
End Sub

The sql appears to be displaying correctly so I suspect it has something to do with the adapter commands in which it is trying to execute the code.

[si_the_geek]

It is throwing out a weird error referring to column name invalid referring to actual data instead of a column name.

That points heavily towards an error in your SQL statement, which Gary and tg suspected.

There are a couple of articles from our Database Development FAQs/Tutorials (at the top of this forum) which are relevant (particularly the second):

• How do I use values (numbers, strings, dates) in SQL statements?
• Why should I use Parameters instead of putting values into my SQL string?