Solved

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

Posted on 2007-11-25
7
199 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
Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

 
LVL 63

Accepted Solution

by:
Fernando Soto earned 500 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

Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

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…
This video Micro Tutorial shows how to password-protect PDF files with free software. Many software products can do this, such as Adobe Acrobat (but not Adobe Reader), Nuance PaperPort, and Nuance Power PDF, but they are not free products. This vide…
Michael from AdRem Software outlines event notifications and Automatic Corrective Actions in network monitoring. Automatic Corrective Actions are scripts, which can automatically run upon discovery of a certain undesirable condition in your network.…

691 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