VB.NET - need code for calling a procedure in windows application using ado.net

Asked By venkat kancheti on 10-Dec-10 06:59 AM
i need code for  calling a procedure in windows application using ado.net
Nowshad M replied to venkat kancheti on 10-Dec-10 07:44 AM
Hi,
Refer to the following sample code

'Declare outside of class
Imports System.Data.SqlClient


'Declare inside of class >
Dim SQLStr As String
Private ConnString As String

'Connstring = Server Name, Database Name, Windows Authentication 
connstring = "Data Source=myserver;Initial Catalog=databasename;Integrated Security=True"
      
'SQL Staments
      
'SQL query = myQuery = "SQL Statment"
      
SQLStr = "SELECT * FROM tblQuestion"
      
SQLStr = "INSERT into tblQuestion(Name, Question) VALUES('Fred', 'How to use SQL?')"
      
SQLStr = "UPDATE tblQuestion SET Answer = 'Like this' Where Question = 'How to use SQL?'"
      
SQLStr = "DELETE FROM tblQuestion WHERE Question='How to use SQL?'"
        
'Write to SQL
      
Dim SQLConn As New SqlConnection() 'The SQL Connection
Dim SQLCmd As New SqlCommand() 'The SQL Command
      
SQLConn.ConnectionString = ConnString 'Set the Connection String
SQLConn.Open 'Open the connection
      
SQLCmd.Connection = SQLConn 'Sets the Connection to use with the SQL Command
SQLCmd.CommandText = SQLStr 'Sets the SQL String
SQLCmd.ExecuteNonQuery() 'Executes SQL Commands Non-Querys only
      
SQLConn.Close() 'Close the connection  
        
      







'Read from SQL
      
Dim SQLConn As New SqlConnection() 'The SQL Connection
Dim SQLCmd As New SqlCommand() 'The SQL Command
Dim SQLdr As SqlDataReader        'The Local Data Store
      
SQLConn.ConnectionString = ConnString 'Set the Connection String
SQLConn.Open 'Open the connection
      
SQLCmd.Connection = SQLConn 'Sets the Connection to use with the SQL Command
SQLCmd.CommandText = SQLStr 'Sets the SQL String
SQLdr = SQLCmd.ExecuteReader 'Gets Data
      
While dr.Read() 'While Data is Present        
      MsgBox(dr("Column Name")) 'Show data in a Message Box
End While

Loop While SQLdr.NextResult() 'Move to the Next Record
SQLdr.Close 'Close the SQLDataReader        

SQLConn.Close() 'Close the connection
Reena Jain replied to venkat kancheti on 10-Dec-10 08:10 AM
hi,

here is the code for you

<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Data.OleDb" %>
 
<script language="VB" runat="server">
 
  Sub Page_Load(Sender As Object, E As EventArgs)
 
  Dim objConnection As OleDbConnection
  Dim objCmd      As OleDbCommand
  Dim strConnection As String
   
  strConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
          "Data Source=C:\Northwind.mdb"
 
   
  ' Create and open the connection object
  objConnection = New OleDbConnection(strConnection)
  objConnection.Open()
   
  ' Create the Command and set its properties
  objCmd = New OleDbCommand()
  objCmd.Connection = objConnection
  objCmd.CommandText = "procedurename"
  objCmd.CommandType = CommandType.StoredProcedure
 
  dgSales.DataSource = objCmd.ExecuteReader(CommandBehavior.CloseConnection)
  dgSales.DataBind()
 
  End Sub
 
</script>
 
<html>
  <body>
  <h2>Using a stored procedure</h2>
  <asp:datagrid id="dgSales" runat="server" />
  </body>
</html>

or

Dim con As New SqlConnection
Dim cmd As New SqlCommand
Try
   With con
     .ConnectionString = sqlConnectionString
     .Open()
   End With
   With cmd
     .Connection = con
     .CommandText = "UpdateProcedure"
     .CommandType = CommandType.StoredProcedure
     .Parameters.AddWithValue("CName", txtCName.Text)
     .Parameters(0).SqlDbType = SqlDbType.NVarChar
    .Parameters.AddWithValue("CAddress",txtCAddress1.Text)
     .Parameters(1).SqlDbType = SqlDbType.NVarChar
     .Parameters.AddWithValue("CCity", txtCCity)
     .Parameters(2).SqlDbType = SqlDbType.NVarChar
     .ExecuteNonSqlQuery()
  End With
Catch ex As Exception
 
Finally
  cmd.Dispose()
  con.Close()
End Try

hope this will help you