?
Solved

need help working with querys configured with the query builder in visual basic 2005

Posted on 2007-11-25
7
Medium Priority
?
200 Views
Last Modified: 2010-04-21
I have a ton of books on database management with visual basic 2005, but I am having problems finding hte correct solution to what I am trying to do. I have a query that I used the query builder to make. I 1 column of all the rows in my work table in decending order. What I am trying to do is get the first table's data so I can auto generate the next work order number by it when creating a new order. For reference my table adapter is WORKTableAdapter and I named the query getLastOrderNumber.

 I know as much to use WORKTableAdapter.getLastOrderNumber() but from there I do not know how to get the information I need. Any information would really be helpful.

Thanks
Rolltide0
0
Comment
Question by:rolltide0
[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
  • 2
7 Comments
 
LVL 48

Expert Comment

by:jpaulino
ID: 20346575
><What I am trying to do is get the first table's data so I can auto generate the next work order number

Don't understando to well what you want to do but if you want to get the next autonumber you can do select statement like this:

"SELEC *, max(id) AS MaxID FROM getLastOrderNumber"

And MaxID +1 will be the next autonumber

jpaulino
0
 

Author Comment

by:rolltide0
ID: 20346794
I can't get that statement to work. the getLastOrderNumber is a custom query that returns the order number from all rows in decending order, meaning the largest is on the top.

 I tried to customize that query using the max method but I cannot get it to work. I have done programming in visual basic but never database programming. If you can provide some coding examples that would really help.
0
 

Author Comment

by:rolltide0
ID: 20346880
the command im trying to use is SELECT MAX(ORDER_NUM) FROM WORK
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 63

Accepted Solution

by:
Fernando Soto earned 2000 total points
ID: 20347099
Hi rolltide0;

Try it this way see sample code.

Using a table adapter to get the next value to use is expensive. If you use the ExecuteScalar method of the SqlCommand object it will be much quicker and efficient. The next best thing to do is to make the ad hoc query a stored procedure.

You will need to change the connection string in the sample.

Fernando
Imports System.Data.SqlClient
 
 
        Dim connStr As String = "Data Source=ServerName\SQLEXPRESS;Initial Catalog=YourDBNameHere; Integrated Security=SSPI"
        Dim conn As New SqlConnection(connStr)
        Dim cmd As SqlCommand = conn.CreateCommand()
        cmd.CommandText = "SELECT MAX(ORDER_NUM) FROM WORK"
        conn.Open()
        Dim nextOrderNumber As Integer = CInt(cmd.ExecuteScalar()) + 1
        conn.Close()
 
        MessageBox.Show("Next unused ID = " & nextOrderNumber.ToString())

Open in new window

0
 

Author Comment

by:rolltide0
ID: 20347155
Fernando that is almost exact;y what I am looking for. I need a little bit if clarification. On your connect string, your source is serverName\sqlexpress.   What if I'm not using sql express? The database im connecting to lets say the name of it is dbaseServer and it is using sql 2005 standard. How would I format the connection string?
0
 
LVL 63

Expert Comment

by:Fernando Soto
ID: 20347274
Hi Hi rolltide0;

You can use the machine name as in this example for a standard MS SQL Server.

Dim connStr As String = "Data Source=VIPER; Initial Catalog=YourDBNameHere; Integrated Security=SSPI"

You can go to this web site to get the different ways to construct a connection string for all different types of databases.

http://www.connectionstrings.com/

Fernando
0
 

Author Closing Comment

by:rolltide0
ID: 31410876
This was exactly what I was looking for, thanks
0

Featured Post

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

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 …
The ECB site provides FX rates for major currencies since its inception in 1999 in the form of an XML feed. The files have the following format (reducted for brevity) (CODE) There are three files available HERE (http://www.ecb.europa.eu/stats/exch…
In this video you will find out how to export Office 365 mailboxes using the built in eDiscovery tool. Bear in mind that although this method might be useful in some cases, using PST files as Office 365 backup is troublesome in a long run (more on t…
Sometimes it takes a new vantage point, apart from our everyday security practices, to truly see our Active Directory (AD) vulnerabilities. We get used to implementing the same techniques and checking the same areas for a breach. This pattern can re…

762 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