Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

vb.net ado.net storing string to table

Posted on 2009-05-20
7
Medium Priority
?
294 Views
Last Modified: 2012-05-07
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

0
Comment
Question by:Delta7428
  • 4
  • 3
7 Comments
 
LVL 16

Expert Comment

by:Steve Krile
ID: 24435427
I don't see where you set the value for sTrackingNum - am I missing something?
0
 

Author Comment

by:Delta7428
ID: 24437643
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
 
LVL 16

Expert Comment

by:Steve Krile
ID: 24439893
Can you post a few rows from the data you are trying to retrieve?  
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:Delta7428
ID: 24441075
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
 
LVL 16

Accepted Solution

by:
Steve Krile earned 2000 total points
ID: 24441347
hmmm....what is the datatype of the UPC_SYS column?
0
 

Author Comment

by:Delta7428
ID: 24441851
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
 
LVL 16

Expert Comment

by:Steve Krile
ID: 24441876
Thanks for the grade...not sure I earned it  ;)

But, yeah, data types could make a difference.
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

More often than not, we developers are confronted with a need: a need to make some kind of magic happen via code. Whether it is for a client, for the boss, or for our own personal projects, the need must be satisfied. Most of the time, the Framework…
Calculating holidays and working days is a function that is often needed yet it is not one found within the Framework. This article presents one approach to building a working-day calculator for use in .NET.
In response to a need for security and privacy, and to continue fostering an environment members can turn to for support, solutions, and education, Experts Exchange has created anonymous question capabilities. This new feature is available to our Pr…
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…

886 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question