Solved

vb.net ado.net storing string to table

Posted on 2009-05-20
7
264 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
3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

 

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 500 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

Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
HTTPS jquery doesn't work 9 61
What namespace do I need to import? 2 29
How to join on ID, with prefix? 15 58
help with regular expression to search string 6 25
A long time ago (May 2011), I have written an article showing you how to create a DLL using Visual Studio 2005 to be hosted in SQL Server 2005. That was valid at that time and it is still valid if you are still using these versions. You can still re…
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
This Micro Tutorial will give you a basic overview how to record your screen with Microsoft Expression Encoder. This program is still free and open for the public to download. This will be demonstrated using Microsoft Expression Encoder 4.
This video shows how to quickly and easily add an email signature for all users on Exchange 2016. The resulting signature is applied on a server level by Exchange Online. The email signature template has been downloaded from: www.mail-signatures…

773 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