Solved

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

Posted on 2007-11-25
7
193 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
Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

 
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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

1.0 - Introduction Converting Visual Basic 6.0 (VB6) to Visual Basic 2008+ (VB.NET). If ever there was a subject full of murkiness and bad decisions, it is this one!   The first problem seems to be that people considering this task of converting…
Introduction: When running hybrid database environments, you often need to query some data from a remote db of any type, while being connected to your MS SQL Server database. Problems start when you try to combine that with some "user input" pass…
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…

839 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