Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Find max Value

Posted on 2007-07-23
23
Medium Priority
?
410 Views
Last Modified: 2013-11-26
Hi Experts

I have an active valid connection to a table and want to return the content of a field. In access I would use a dmax(myfield, mytable). I want to find the value and use it elsewhere. I have tried to achieve this by using:

con.ConnectionString = "Provider=SQLNCLI;" _
            & "Server=(local);" _
            & "Database=customerlink;" _
             & "Integrated Security=SSPI;" _
             & "DataTypeCompatibility=80;" _
             & "MARS Connection=True;"

            Dim mySQL As String, Isql As String
Isql = "SELECT     MAX(dwJobItemId) AS Expr1 FROM(dbo_jobitem)"
            rst = New ADODB.Recordset
            With rst
                .ActiveConnection = con
                .CursorLocation = ADODB.CursorLocationEnum.adUseClient
                .CursorType = ADODB.CursorTypeEnum.adOpenStatic
                .LockType = ADODB.LockTypeEnum.adLockBatchOptimistic
                .Open(Isql)
                MsgBox(rst.Fields("Expr1").Value)

This will not work. What quick solution can I use in vb.net?

Regards,
Joe
0
Comment
Question by:JoeBo747
  • 12
  • 10
23 Comments
 
LVL 1

Author Comment

by:JoeBo747
ID: 19546244
Hi Experts

This won't work either; I need a valid connection type?

con.ConnectionString = "Provider=SQLNCLI;" _
            & "Server=(local);" _
            & "Database=customerlink;" _
             & "Integrated Security=SSPI;" _
             & "DataTypeCompatibility=80;" _
             & "MARS Connection=True;"

            Dim Isql As String
            con.Open()
            Isql = "SELECT MAX(dwJobItemId) AS Expr1 FROM(dbo_jobitem)"
            Dim executescalarcommand As New SqlCommand
            executescalarcommand.Connection = con 'how do I connect this to my open connection?
            executescalarcommand.CommandType = CommandType.Text
            executescalarcommand.CommandText = Isql
            executescalarcommand.Connection.Open()
            Dim ImaxRec As Integer = CInt(executescalarcommand.ExecuteScalar)
            MessageBox.Show(ImaxRec
Regards
Joe
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 19546365
change this line a bit:
            Isql = "SELECT MAX(dwJobItemId) AS Expr1 FROM dbo_jobitem "

remove that line, the connection is already open:
>executescalarcommand.Connection.Open()
           
0
 
LVL 1

Author Comment

by:JoeBo747
ID: 19546397
Hi AngeIII

Thanks for your response. Made the alterations as suggested, I now get:

System.InvalidCastException was caught
  Message="Unable to cast COM object of type 'ADODB.ConnectionClass' to class type 'System.Data.SqlClient.SqlConnection'. Instances of types that represent COM components cannot be cast to types that do not represent COM components; however they can be cast to interfaces as long as the underlying COM component supports QueryInterface calls for the IID of the interface."
  Source="Customer Access"
  StackTrace:
       at Customer_Access.JobRequest.AddJob_Click(Object sender, EventArgs e) in C:\Documents and Settings\Joe\My Documents\Visual Studio 2005\Projects\Customer Access\Customer Access\JobRequest.vb:line 205

This error is thrown at:

executescalarcommand.Connection = con

Regards
Joe
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 19546427
that means that you declare con to be a ADODB.Connection, which you CANNOT use for a SqlCommand. you need a SqlConnection for that. do you have a reason for using ADODB.Connection?
0
 
LVL 1

Author Comment

by:JoeBo747
ID: 19546449
Hi Angellll

None other than the connection "con" is open in this procedure. What would you suggest I use?

Regards
Joe
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 19546481

SqlConnection con = new SqlConnection("Provider=SQLNCLI;" _
            & "Server=(local);" _
            & "Database=customerlink;" _
             & "Integrated Security=SSPI;" _
             & "DataTypeCompatibility=80;" _
             & "MARS Connection=True;")

            con.Open()

            Dim Isql As String
            Isql = "SELECT MAX(dwJobItemId) AS Expr1 FROM(dbo_jobitem)"

            Dim executescalarcommand As New SqlCommand(con, Isql)
            executescalarcommand.CommandType = CommandType.Text

            Dim ImaxRec As Integer = CInt(executescalarcommand.ExecuteScalar)
            MessageBox.Show(ImaxRec)
Regards
0
 

Expert Comment

by:zkac050
ID: 19546575
Stored procedure might be better in this case.
0
 
LVL 1

Author Comment

by:JoeBo747
ID: 19546620
Hi Angellll

SqlConnection and SqlCommand are reporting missing references. What am I missing?

Regards
Joe
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 19546654
add this line:
using System.Data.SqlClient

0
 
LVL 1

Author Comment

by:JoeBo747
ID: 19546730
Hi Angellll

I now have this

Using System.Data.SqlClient
                SqlConnection(con = New SqlConnection("Provider=SQLNCLI;" _
                & "Server=(local);" _
                & "Database=customerlink;" _
                 & "Integrated Security=SSPI;" _
                 & "DataTypeCompatibility=80;" _
                 & "MARS Connection=True;"))

                con.Open()

                Dim Isql As String
                Isql = "SELECT MAX(dwJobItemId) AS Expr1 FROM(dbo_jobitem)"

                Dim executescalarcommand As New SqlCommand(con, Isql)
                executescalarcommand.CommandType = CommandType.Text

                Dim ImaxRec As Integer = CInt(executescalarcommand.ExecuteScalar)
                MessageBox.Show(ImaxRec)
            End Using

Using System.Data.SqlClient, complains: System.Data.SqlClient is a namespace and cannot be used as an expression?

I have the following references:
Imports System.Data
Imports System.Data.OleDb
Imports System.Math
Imports system.data.SqlTypes

Regards
Joe
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 19546880
sorry, vb...
you need to add the line as Imports...

Imports System.Data
Imports System.Data.SqlClient
Imports System.Data.OleDb
Imports System.Math
Imports system.data.SqlTypes

and remove the Using line suggested (is for C#)
0
 
LVL 1

Author Comment

by:JoeBo747
ID: 19546925
Hi Angellll

I now have SqlConnection complaining: SqlConnection is a type and cannot be used as an expression?

Regards
Joe
0
 
LVL 1

Author Comment

by:JoeBo747
ID: 19548249
Hi Experts

Can anyone give me an example of a stored proeduer that would achieve this.

Regards
Joe
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 19548270
>I now have SqlConnection complaining: SqlConnection is a type and cannot be used as an expression?

on which line?
0
 
LVL 1

Author Comment

by:JoeBo747
ID: 19548540
HI Angellll

The whole of this section is underlined.

SqlConnection(con = New SqlConnection("Provider=SQLNCLI;" _
            & "Server=(local);" _
            & "Database=customerlink;" _
             & "Integrated Security=SSPI;" _
             & "DataTypeCompatibility=80;" _
             & "MARS Connection=True;"))

The first area underlined is the SqlConnection  which complains:
SqlConnection is a type and cannot be used as an expression


The rest of the statement is underlined.

& "Server=(local);" _
            & "Database=customerlink;" _
             & "Integrated Security=SSPI;" _
             & "DataTypeCompatibility=80;" _
             & "MARS Connection=True;"))

and complains:

operator = is not defined for types ADODBconnectios or System.Data.SqlClient.SqlConnection

Regards
Joe

0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 19548844
>SqlConnection(con
I don't know where you tool that ( from ???

SqlConnection con = ....
0
 
LVL 1

Author Comment

by:JoeBo747
ID: 19554318
Hi  Angellll

Another day same problem! I have attempted a different approach.

I have the following declarations:

Imports System.Data
Imports System.Data.SqlClient
Imports System.Data.OleDb
Imports System.Math
Imports system.data.SqlTypes

The full Code as follows:

Private Sub AddJob_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles AddJob.Click
        Dim ctl As Control, X As Integer
        Try
            For Each ctl In Me.Controls
                If TypeOf ctl Is TextBox Then   'ctl.Type?
                    If IsNothing(ctl.Text) And ctl.Tag.ToString = "True" Then
                        ctl.BackColor = Color.Yellow
                        X = X + 1
                    End If

                End If
            Next
            If X > 0 Then
                MessageBox.Show("All fields coloured yellow" & vbCrLf & "Must be filled in before the Job" & vbCrLf & "To the system", conAppName, MessageBoxButtons.OK)
                Exit Sub
            End If

            Dim mySQL As String


            ''@trial block *************************
            Dim con As New SqlConnection
            con = New SqlConnection("Provider=SQLNCLI;" _
            & "Server=(local);" _
            & "Database=customerlink;" _
             & "Integrated Security=SSPI;" _
             & "DataTypeCompatibility=80;" _
             & "MARS Connection=True;")
            con.Open()

            Dim Isql As String
            Isql = "SELECT MAX(dwJobItemId) AS Expr1 FROM(dbo_jobitem)"
            Dim executescalarcommand As New SqlCommand
            executescalarcommand = con.CreateCommand()
            executescalarcommand.CommandText = Isql
            executescalarcommand.CommandType = CommandType.Text
            Dim ImaxRec As Integer = CInt(executescalarcommand.ExecuteScalar())
            MessageBox.Show(ImaxRec)

        Catch ex As Exception
            MsgBox(ex.ToString)
        Finally
            ' If (con.State = ConnectionState.Open) Then con.Close()
        End Try

    End Sub

There are no errors generated but I have the following warnings:
Warning      1      Referenced assembly 'C:\WINDOWS\assembly\GAC_32\Microsoft.SqlServer.BatchParser\9.0.242.0__89845dcd8080cc91\Microsoft.SqlServer.BatchParser.dll' targets a different processor than the application.       Customer Access

Warning      3      Referenced assembly 'c:\Program Files\Microsoft SQL Server\90\DTS\Tasks\Microsoft.SqlServer.SQLTask.dll' targets a different processor than the application.      Gba Customer Access

The code will run but produces:
 SystemNullReferenceException: Object reference is not set to an instance of an object.

Does this mean the connection string is not finding the table or is my syntax out?

Regards
Joe
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 19554349
I don't have any clue about the 2 warnings.
however, the Exception is clear: you try to use an object which is not initialized.
on which line does it occur.

maybe, in your "finally" block, you should first check if the object "con" is really set or if it is still nothing
0
 
LVL 1

Author Comment

by:JoeBo747
ID: 19554526
Hi  Angellll

Sorry the null reference was not the problem that was created by a text field tag not being set to false at runtime!

The code now runs as far as the con string and then complains:

A first chance exception of type 'System.ArgumentException' occurred in System.Data.dll

Unrecognised keyword Provider

I dont know where this leaves me?


Regards
Joe

0
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 2000 total points
ID: 19554650
con = New SqlConnection("Provider=SQLNCLI;" _
            & "Server=(local);" _
            & "Database=customerlink;" _
             & "Integrated Security=SSPI;" _
             & "DataTypeCompatibility=80;" _
             & "MARS Connection=True;")

is actually "wrong". as you use SqlConnection, leave out the "provider":

con = New SqlConnection("Server=(local);" _
            & "Database=customerlink;" _
             & "Integrated Security=SSPI;" _
             & "DataTypeCompatibility=80;" _
             & "MARS Connection=True;")

0
 
LVL 1

Author Comment

by:JoeBo747
ID: 19554715
Hi  Angellll

Thanks for sticking with me on this, I have had to reduce the connection to:

con = New SqlConnection("Server=(local);" _
            & "Database=customerlink;" _
             & "Integrated Security=SSPI;")

This works and my test:
If (con.State = ConnectionState.Open) Then
                MsgBox("Open")
            Else
                MsgBox("Connection Closed")
            End If

 reports open the code runs as far as:

Dim ImaxRec As Integer = CInt(executescalarcommand.ExecuteScalar)
 I then get an error:

Incorrect Syntax near (

Is this a problem with the sql syntax:

Isql = "SELECT MAX(dwJobItemId) AS Expr1 FROM(dbo_jobitem)"

Or do I have an error in the executescalercommand line?

Regards
Joe
0
 
LVL 143

Assisted Solution

by:Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3] earned 2000 total points
ID: 19554757
>Is this a problem with the sql syntax:
yes. actually, a problem I showed already earlier:


Isql = "SELECT MAX(dwJobItemId) AS Expr1 FROM dbo_jobitem "
0
 
LVL 1

Author Comment

by:JoeBo747
ID: 19554787
Hi  Angellll

I am again in your debt! This was a bit of a marathon, but you got me the right result in the end thanks for your perseverance.

Regards
Joe
0

Featured Post

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.

Question has a verified solution.

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

It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…
Suggested Courses

564 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