Solved

vb.net ado.net storing string to table

Posted on 2009-05-20
7
284 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
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 

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

Is Your Team Achieving Their Full Potential?

74% of employees feel they are not achieving their full potential. With Linux Academy, not only will you strengthen your team's core competencies but also their knowledge of of the newest IT topics.

With new material every week, we'll make sure that you stay ahead of the game.

Question has a verified solution.

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

Many of us here at EE write code. Many of us write exceptional code; just as many of us write exception-prone code. As we all should know, exceptions are a mechanism for handling errors which are typically out of our control. From database errors, t…
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
There are cases when e.g. an IT administrator wants to have full access and view into selected mailboxes on Exchange server, directly from his own email account in Outlook or Outlook Web Access. This proves useful when for example administrator want…
In this video, viewers are given an introduction to using the Windows 10 Snipping Tool, how to quickly locate it when it's needed and also how make it always available with a single click of a mouse button, by pinning it to the Desktop Task Bar. Int…

624 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