• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 415
  • Last Modified:

Find max Value

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
JoeBo747
Asked:
JoeBo747
  • 12
  • 10
2 Solutions
 
JoeBo747Author Commented:
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
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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
 
JoeBo747Author Commented:
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
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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
 
JoeBo747Author Commented:
Hi Angellll

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

Regards
Joe
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:

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
 
zkac050Commented:
Stored procedure might be better in this case.
0
 
JoeBo747Author Commented:
Hi Angellll

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

Regards
Joe
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
add this line:
using System.Data.SqlClient

0
 
JoeBo747Author Commented:
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
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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
 
JoeBo747Author Commented:
Hi Angellll

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

Regards
Joe
0
 
JoeBo747Author Commented:
Hi Experts

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

Regards
Joe
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
>I now have SqlConnection complaining: SqlConnection is a type and cannot be used as an expression?

on which line?
0
 
JoeBo747Author Commented:
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
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
>SqlConnection(con
I don't know where you tool that ( from ???

SqlConnection con = ....
0
 
JoeBo747Author Commented:
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
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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
 
JoeBo747Author Commented:
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
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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
 
JoeBo747Author Commented:
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
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
>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
 
JoeBo747Author Commented:
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
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.

Join & Write a Comment

Featured Post

Cloud Class® Course: Amazon Web Services - Basic

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

  • 12
  • 10
Tackle projects and never again get stuck behind a technical roadblock.
Join Now