Expiring Today—Celebrate National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

vb.net ado.net storing string to table

Posted on 2009-05-20
7
Medium Priority
?
290 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
DFW AZURE MEETUP TONIGHT FRI 6PM

We will be discussing what Azure Stack is, how does it fit into the suit of offerings that Azure has currently, and where can it fit into your organizations technology stack. We will also be discussing limitations of the platform while covering various applicable scenarios.

 

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

Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

Question has a verified solution.

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

It’s quite interesting for me as I worked with Excel using vb.net for some time. Here are some topics which I know want to share with others whom this might help. First of all if you are working with Excel then you need to Download the Following …
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 this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…

719 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