A bit lost, can anyone point the way?

[roleki]

I'm trying to write a little application in VB 2005 that prints address labels based on user input, but having verified everything works using textbox input, I want to step it up a bit and use a SQL Query to drive the various address fields. That's where I'm running into trouble...

I went to MSDN to see if I couldn't figure out the specific what, how & why based on their documentation, but the most I could get out of that was that I should be using a DataSet. After that it's just a series of tutorials on how to use various wizards, which don't really tell me anything.

The labels will be printed on a DYMO label printer; the fields are set as following (using textbox input)

DYMOLabel.SetField("Street1", tbxStreet1.Text)
DYMOLabel.SetField("State", tbxState.Text)
...
and so on.

My SQL Query will return one row every time it's run, with your basic Address information in columns (CompanyName, StreetAddress1, StreetAddress2, State, ZIP, Country)

All I want to do is instead of using

DYMOLabel.SetField("Street1", tbxStreet1.Text)

I want to use (the equivalent of)

DYMOLabel.SetField("Street1", WhateverDataBoundConstructINeedToUseToGetSQLData.StreetAddress1)

Any ideas on what where or how I should be doing this?

 

[HarleyQuinn]

thread796-1525604 might be of interest to you.

Also, how many labels will you be wanting to print? If it's a few then getting them all in one trip to the database might be more efficient than going once per set of information you need to print.

Hope this helps

HarleyQuinn
---------------------------------
The most overlooked advantage to owning a computer is that if they foul up there's no law against wacking them around a little. - Joe Martin

Get the most out of Tek-Tips, read FAQ222-2244 before posting.

 

[roleki]

Thanks for the link; for this application, it's ok to just get the one address per trip - the labels will be attached to skids of stock to identify them as 'part of somesuch shipment' so that the skids won't inadvertantly make their way into the wrong staging area.

Unfortunately, I can't do anything with SPs because the data is coming out of our ERP system, which we're disallowed by policy from modifying.

So, in the meantime, I've come up with this:

(In the DYMOPrint module...)

Dim dsLabelFields As New DataSet
dsLabelFields = ShipLabelMaker.SQLMethods.getDataSet("tblLabelFields", strQueryString)

(Then in ShipLabelMaker.SQLMethods...)

Public Class SQLMethods

Public Shared Function getDataSet(ByVal tableName As String, ByVal sqlString As String) As DataSet
Dim conn As SqlConnection
Dim da As SqlDataAdapter
Dim ds As New DataSet
Dim cs As String = "Server=TheServer;DataBase=TheDB;Uid=TheUser;Pwd=ThePWD"

conn = New SqlConnection(cs)

Try
conn.Open()
da = New SqlDataAdapter(sqlString, conn)
da.Fill(ds, tableName)
conn.Close()
Catch ex As Exception
MessageBox.Show("Error! " + ex.Message, "Error!", MessageBoxButtons.OK, MessageBoxIcon.None)
End Try

Return ds
End Function
End Class

(Then back in the DYMOPrint module...)

DYMOLabel.SetField("ShipToName", dsLabelFields.Tables.Item(0).Rows.Item(0).Item("ShipToName").ToString())

That works, but is there a more intuitive (i.e. cleaner/better) way to reference the data in the DataSet than this?

 

[ArtieChoke]

You're doing it fine. Just change the field retrieval to:

dsLabelFields.Rows(0).Item("ShipToName")

"I think we're all Bozos on this bus!" - Firesign Theatre

 

[roleki]

Thanks; I tried your suggestion but .Rows is not a member of System.Data.DataSet; I tried

dsLabelFields.Tables(0).Rows(0).Item(1).ToString()

and that worked, except I have to use the column index rather than the name of the column when grabbing data; otherwise I get an error that "[name of column] does not belong to [temp table name for dataset].

I must be going about this wrong somehow, even though at the end of the day, it works.

 

[ArtieChoke]

Try using a datatable instead of dataset. It has the rows method.

"I think we're all Bozos on this bus!" - Firesign Theatre