Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
See more: Visual-Studio
Trying to convert PL/SQL developer into VB Studio SQL.
 
In PL/SQL I can execute:
 
AND TO_CHAR(SUBSTR(CM.DATA,to_char(instr(cm.data,'^^^')+3),4)) = &chute
 

When SQL is executed a popup will allow me to enter chute number and data is populated.
 
Just looking for VB equivilent.
Posted 24-Apr-13 2:50am
Edited 24-Apr-13 6:17am
Maciej Los158.3K
v2
Comments
ryanb31 at 24-Apr-13 8:01am
   
Where will this be used? In a web app? In a Windows Form? In SMS? Just use parameters but they won't be popups unless you execute the SP in SMS.
Chris Simmonds at 24-Apr-13 8:53am
   
Eventually will be used as a single windows form. Just working out Proof of concept before creating forms.

1 solution

Rate this: bad
good
Please Sign up or sign in to vote.

Solution 1

There is no "popup" like PL/SQL in SQL statements in VB .NET code.
 
Your user enters the value to be found in a TextBox on the form. You use that TextBox value as a parameter to the SQL statement. In my example, I trim spaces from the beginning and end of the TextBox value because sometimes users accidentally enter them.
 
@CHUTE in the SQL statement below is the placeholder for the parameter.
The parameter value is put into a SqlParameter object. The SqlParameter object is added to the SqlCommand object.
 
Here is an example:
 
        
    Dim cn As New SqlConnection
    Dim obCommand As SqlCommand
    Dim rs As SqlDataReader
 
    '.... open the connection ...
    cn.ConnectionString = "Your Connection String"
    cn.Open()
 
    ...
 

    Dim obCommand As SqlCommand
    Const SQL_SELECT As String = "SELECT col1,col2,col3 FROM table1 WHERE col1=@CHUTE;"
    obCommand = New SqlCommand(SQL_SELECT, cn) ' Instantiate SqlCommand object
    Dim obParm As New SqlParameter
    Dim strChute As String=TextBox1.Text.Trim ' Remove front and back spaces
    Try
       obParm.ParameterName = "CHUTE"     ' Name of @ parameter in the SQL statement
       obParm.SqlDbType = SqlDbType.NVarChar ' Data Type
       obParm.Size = strChute.Length      ' Size of parameter
       obParm.Value = strChute            ' Parameter value
       obCommand.Parameters.Add(obParm)   ' Add to SqlCommand object
       obParm = Nothing
       rs = obCommand.ExecuteReader       ' Execute SQL statement
       If rs.HasRows Then
           While rs.Read                  ' Retrieve a row
               ...
               ...
               ...
           End While
       End If
     Catch myException As SqlException
        Call ShowSQLException(myException)
     Catch myException As Exception
        MsgBox(myException.Message, MsgBoxStyle.Critical, "Caption")
     Finally
        Try
           rs.Close()
        Catch
        End Try
        Try
           rs.Dispose()
        Catch
        End Try
        Try
           obCommand.Dispose()
        Catch
        End Try
     End Try
  Permalink  
v9

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
0 OriginalGriff 277
1 Maciej Los 230
2 BillWoodruff 205
3 Sergey Alexandrovich Kryukov 195
4 DamithSL 165
0 OriginalGriff 5,130
1 DamithSL 4,157
2 Maciej Los 3,650
3 Kornfeld Eliyahu Peter 3,460
4 Sergey Alexandrovich Kryukov 2,811


Advertise | Privacy | Mobile
Web03 | 2.8.141216.1 | Last Updated 24 Apr 2013
Copyright © CodeProject, 1999-2014
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100