Solved

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

Posted on 2007-11-25
7
188 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
  • 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
Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 
LVL 62

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 62

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

NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
This tutorial gives a high-level tour of the interface of Marketo (a marketing automation tool to help businesses track and engage prospective customers and drive them to purchase). You will see the main areas including Marketing Activities, Design …
In this video I am going to show you how to back up and restore Office 365 mailboxes using CodeTwo Backup for Office 365. Learn more about the tool used in this video here: http://www.codetwo.com/backup-for-office-365/ (http://www.codetwo.com/ba…

861 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

25 Experts available now in Live!

Get 1:1 Help Now