vb.net ado.net storing string to table

I am appending a string values captured from a datatable to a sql table.  My append works and when I open the table the values are there.

If I run a query on that field on the table, nothing is returned although the string is there.   This is the case whether I query it from the code, sql management studio or access.

I can run a wildcard query of the first character and that returns a record.  When I query of the exact field value, I get nothing.

I am using the trim function on the variable before appending it, so I have ruled out extraneous spaces.

The datatype is defined as varchar.




Private Sub loadGrid2()
        Dim squery As String
        Dim cnDb As New OdbcConnection(sConn)
 
        squery = "SELECT tc.ITEM, tc.UPC_CODE, tc.tote_qty, tc.SHIPPED_QTY, SECURITY.NAME, tc.PACKED_TIME, tb.BOX_NO " & _
        "FROM tblboxmst tb " & _
        "JOIN tote_control tc ON tb.CO_NUMBER = tc.CO_NUMBER " & _
        "LEFT JOIN SECURITY ON tc.PACKER_ID = SECURITY.USERID " & _
        "WHERE tb.TRACKING='" & sTrackingNum & "' AND tc.BOX_ID='SHPD' AND tc.TOTE_NUM=Right([tb].[box_no],3) " & _
        "ORDER BY tc.ITEM, tc.UPC_CODE, tb.TRACKING"
 
        Dim cmd As New OdbcCommand(squery, cnDb)
        Dim dr As OdbcDataReader
        Try
            cnDb.Open()
            dr = cmd.ExecuteReader()
 
            Dim dt As New DataTable("tblboxmst")
            dt.Load(dr)
            dr.Close()
 
            grdBoxInfo.DataSource = dt
            For Each dRow As DataRow In dt.Rows
 
                Dim sUPc As String = Trim(CStr(dRow(1)))
                appendUPCToTemp(sUPc)
 
            Next
 
        Catch ex As Exception
            MessageBox.Show(ex.Message)
        Finally
            cnDb.Close()
        End Try
    End Sub
 
    Private Sub appendUPCToTemp(ByVal sUPC As String)
        Dim sqry As String
        oDb = New dbFac()
        sqry = "INSERT into qc_scan_temp (UPC_SYS) VALUES (" & sUPC & ")"
        oDb.RcModify(sqry, "INSERT")
    End Sub
------------------------------
Public Class dbFac
    Public Function RcModify(ByVal sQuery As String, ByVal sOper As String) As Integer
        Dim iRecsAffected As Integer = 0
        Try
            Dim cnDb As New OdbcConnection(sConn)
            cnDb.Open()
            Dim cmd As New OdbcCommand(sQuery, cnDb)
            iRecsAffected = cmd.ExecuteNonQuery()
            cnDb.Close()
 
        Catch ex As Exception
            sErrNo = Err.Number.ToString & " - " & "3000"
            sError = sQuery & sOper & vbCrLf & Err.Description
            MessageBox.Show(sError)
        End Try
 
        RcModify = iRecsAffected
    End Function
End Class

Open in new window

Delta7428Asked:
Who is Participating?
 
Steve KrileConnect With a Mentor Commented:
hmmm....what is the datatype of the UPC_SYS column?
0
 
Steve KrileCommented:
I don't see where you set the value for sTrackingNum - am I missing something?
0
 
Delta7428Author Commented:
You are not missing anything.   I didn't include it in my code snippet.  sTrackingNum is a form variable set on a button click event before calling loadGrid2:
     sTrackingNum = trim(txtTrackingnum.text)
     loadGrid2()
0
Cloud Class® Course: C++ 11 Fundamentals

This course will introduce you to C++ 11 and teach you about syntax fundamentals.

 
Steve KrileCommented:
Can you post a few rows from the data you are trying to retrieve?  
0
 
Delta7428Author Commented:
UPC_SYS
-----------
72573456426
72573456448
72573456425
72573456383
72573456453

What is so odd is that a wildcard query on the entire string such as like '72573456453%' will return the record.  If I query on = '72573456453' it does not return it.  

This query works (without quotes):
----------------------------------
SELECT Trim([UPC_SYS]) AS U
FROM QC_SCAN_TEMP
WHERE (((Trim([UPC_SYS]))=72573456426))

The same query does not work if I add the quotes:
----------------------------------
This query works (without quotes):
SELECT Trim([UPC_SYS]) AS U
FROM QC_SCAN_TEMP
WHERE (((Trim([UPC_SYS]))=72573456426))



 Private Sub cmdSubmit_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdSubmit.Click
        sTrackingNum = Trim(txtTrackingNum.Text)
        emptyScanTable()
        loadGrid2()
    End Sub
 
    Private Sub emptyScanTable()
        Dim sqry As String = ""
        oDb = New dbFac()
        sqry = "DELETE FROM QC_SCAN_TEMP"
        Dim iRecordCount As Integer = oDb.RcModify(sqry, "DELETE")
    End Sub
 
Private Sub loadGrid2()
        Dim squery As String
        Dim cnDb As New OdbcConnection(sConn)
 
        squery = "SELECT tc.ITEM, tc.UPC_CODE, tc.tote_qty, tc.SHIPPED_QTY, SECURITY.NAME, tc.PACKED_TIME, tb.BOX_NO " & _
        "FROM tblboxmst tb " & _
        "JOIN tote_control tc ON tb.CO_NUMBER = tc.CO_NUMBER " & _
        "LEFT JOIN SECURITY ON tc.PACKER_ID = SECURITY.USERID " & _
        "WHERE tb.TRACKING='" & sTrackingNum & "' AND tc.BOX_ID='SHPD' AND tc.TOTE_NUM=Right([tb].[box_no],3) " & _
        "ORDER BY tc.ITEM, tc.UPC_CODE, tb.TRACKING"
 
        Dim cmd As New OdbcCommand(squery, cnDb)
        Dim dr As OdbcDataReader
        Try
            cnDb.Open()
            dr = cmd.ExecuteReader()
 
            Dim dt As New DataTable("tblboxmst")
            dt.Load(dr)
            dr.Close()
 
            grdBoxInfo.DataSource = dt
            For Each dRow As DataRow In dt.Rows
 
                Dim sUPc As String = Trim(CStr(dRow(1)))
                appendUPCToTemp(sUPc)
 
            Next
 
        Catch ex As Exception
            MessageBox.Show(ex.Message)
        Finally
            cnDb.Close()
        End Try
    End Sub
 
    Private Sub appendUPCToTemp(ByVal sUPC As String)
        Dim sqry As String
        oDb = New dbFac()
        sqry = "INSERT into qc_scan_temp (UPC_SYS) VALUES (" & sUPC & ")"
        oDb.RcModify(sqry, "INSERT")
    End Sub
------------------------------
Public Class dbFac
    Public Function RcModify(ByVal sQuery As String, ByVal sOper As String) As Integer
        Dim iRecsAffected As Integer = 0
        Try
            Dim cnDb As New OdbcConnection(sConn)
            cnDb.Open()
            Dim cmd As New OdbcCommand(sQuery, cnDb)
            iRecsAffected = cmd.ExecuteNonQuery()
            cnDb.Close()
 
        Catch ex As Exception
            sErrNo = Err.Number.ToString & " - " & "3000"
            sError = sQuery & sOper & vbCrLf & Err.Description
            MessageBox.Show(sError)
        End Try
 
        RcModify = iRecsAffected
    End Function
End Class

Open in new window

0
 
Delta7428Author Commented:
It was nvarchar(50), I had changed it to varchar(50) yestereday ... no difference.

The UPC_Code field in the totecontrol table that is loaded into the dataset is nvarchar(13).  

I changed UPC_SYS to nvarchar(13) and it started working.   I changed the datatypes back the way I thought they were to prove to myself the difference in the datatypes was the issue and THE QUERIES STILL WORKED...

I spent 2 hours trying to figure this out yesterday, 1 of them working with another programmer more experienced in .NET.  

So this is just one of mysterious glitches.  It started working after I started finagling the datatypes, so I'll award you points skrile.  Thanks.
0
 
Steve KrileCommented:
Thanks for the grade...not sure I earned it  ;)

But, yeah, data types could make a difference.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.