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

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
rolltide0Asked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
Fernando SotoConnect With a Mentor RetiredCommented:
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
 
Jorge PaulinoIT Pro/DeveloperCommented:
><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
 
rolltide0Author Commented:
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
Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
rolltide0Author Commented:
the command im trying to use is SELECT MAX(ORDER_NUM) FROM WORK
0
 
rolltide0Author Commented:
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
 
Fernando SotoRetiredCommented:
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
 
rolltide0Author Commented:
This was exactly what I was looking for, thanks
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.