Solved

vb.net ado.net storing string to table

Posted on 2009-05-20
7
253 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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 

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

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

Wouldn’t it be nice if you could test whether an element is contained in an array by using a Contains method just like the one available on List objects? Wouldn’t it be good if you could write code like this? (CODE) In .NET 3.5, this is possible…
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…
Here's a very brief overview of the methods PRTG Network Monitor (https://www.paessler.com/prtg) offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…
This demo shows you how to set up the containerized NetScaler CPX with NetScaler Management and Analytics System in a non-routable Mesos/Marathon environment for use with Micro-Services applications.

760 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now