[hoffmn]

I have a stored procedure that clears student accounts to showed that they have paid their bills. I am trying to create a form in vb.net that allows them to enter a parameter and then execute the stored procedure. I need to be point in the right direction though, it is a little above my skill set.

[krum110487]

some of these may help

http://www.developer...res-with-ADONET
http://bytes.com/top...tored-procedure

the best way to search is type:

"Execute stored procedures VB.net"

you will get all kinds of hits, try stuff out, and come back if you need more specific help!

This post has been edited by krum110487: 04 November 2009 - 06:27 PM

[hoffmn]

For extra help i am including some code. Hope this helps in trying to help me. I have not created any code for the form, but I have a DataAccess.vb class that contains all my functions. I want to call these functions and have a simple form allowing the end user to input the term to reset and then an execute button with some type of reply to say that it completed successfully.

Here is the DataAccess class:

Imports System.Data.SqlClient
Imports System.Configuration
Imports System.Configuration.ConfigurationSettings

Public Class DataAccess

	'   Function to retrieve the connection from the app.config
	Public Shared Function GetConnectionString(ByVal conName As String) As String

		Dim strReturn As New String("")

		If Not String.IsNullOrEmpty(conName) Then
			strReturn = ConfigurationManager.ConnectionStrings(conName).ConnectionString
		Else
			strReturn = ConfigurationManager.ConnectionStrings("iCAMSConnectionString").ConnectionString
		End If

		Return strReturn

	End Function

	'   Returns a BindingSource, which is used with, for example, a DataGridView control
	Public Shared Function GetBindingSource(ByVal cmd As SqlCommand) As BindingSource
		'declare our binding source
		Dim oBindingSource As New BindingSource()
		' Create a new data adapter based on the specified query.
		Dim daGet As New SqlDataAdapter(cmd)
		' Populate a new data table and bind it to the BindingSource.
		Dim dtGet As New DataTable()
		'set the timeout of the SqlCommandObject
		cmd.CommandTimeout = 240
		dtGet.Locale = System.Globalization.CultureInfo.InvariantCulture
		Try
			'fill the DataTable with the SqlDataAdapter
			daGet.Fill(dtGet)
		Catch ex As Exception
			'check for errors
			MsgBox(ex.Message, MsgBoxStyle.OkOnly, "Error receiving data")
			Return Nothing
		End Try
		'set the DataSource for the BindingSource to the DataTable
		oBindingSource.DataSource = dtGet
		'return the BindingSource to the calling method or control
		Return oBindingSource
	End Function

	'   Method for handling the ConnectionState of the connection object passed to it
	Public Shared Sub HandleConnection(ByVal conn As SqlConnection)
		With conn
			'do a switch on the state of the connection
			Select Case .State
				Case ConnectionState.Open
					'the connection is open
					'close then re-open
					.Close()
					.Open()
					Exit Select
				Case ConnectionState.Closed
					'connection is open
					'open the connection
					.Open()
					Exit Select
				Case Else
					.Close()
					.Open()
					Exit Select
			End Select
		End With
	End Sub

	Public Shared Function InsertNewRecord(ByVal term As String) As Boolean
		Dim cnInsert As New SqlConnection(GetConnectionString("iCAMSConnectionString"))
		Dim cmdInsert As New SqlCommand
		Dim sSQL As New String("")
		Dim iSqlStatus As Integer

		'Set the stored procedure we're going to execute
		sSQL = "pr_BillingAllClearReset"

		'Clear any parameters
		cmdInsert.Parameters.Clear()
		Try
			'Set the SqlCommand Object Properties
			With cmdInsert
				'Tell it what to execute
				.CommandText = sSQL
				.CommandType = CommandType.StoredProcedure
				'Now add the parameters to our procedure
				.Parameters.AddWithValue("@Term", term)
				'Set the connection of the object
				.Connection = cnInsert
			End With

			'Now take care of the connection
			HandleConnection(cnInsert)

			'Set the iSqlStatus to the ExecuteNonQuery 
			'status of the insert (0 = success, 1 = failed)
			iSqlStatus = cmdInsert.ExecuteNonQuery

			'Now check the status
			If Not iSqlStatus = 0 Then
				MsgBox(MsgBoxStyle.OkOnly, "Billing all clear was successful")
				Return False
			Else
				MsgBox(MsgBoxStyle.OkOnly, "Billing all clear was unsuccessful")
				Return True
			End If
		Catch ex As Exception
			MsgBox(ex.Message, MsgBoxStyle.OkOnly, "Error")
		Finally
			'Now close the connection
			HandleConnection(cnInsert)
		End Try
	End Function

End Class

And in case it helps, my app.config:

<?xml version="1.0" encoding="utf-8" ?>
<configuration>
  <connectionStrings>
	<add name="iCAMSConnectionString"
		 connectionString="Persist Security Info=False;
   Data Source=ngu-sqlsrv-01.iCAMS.dbo;
   Initial Catalog=YourTable;
   Integrated Security=SSPI;
   Trusted_Connection=TRUE;
   Application Name=BillingAllClearResetApplication"
		 providerName="System.Data.SqlClient" />
  </connectionStrings>
	<system.diagnostics>
		<sources>
		  <!-- This section defines the logging configuration for My.Application.Log -->
			<source name="DefaultSource" switchName="DefaultSwitch">
				<listeners>
					<add name="FileLog"/>
					<add name="EventLog"/>
				</listeners>
			</source>
		</sources>
		<switches>
		  <add name="DefaultSwitch" value="Information" />
		</switches>
		<sharedListeners>
			<add name="FileLog"
				 type="Microsoft.VisualBasic.Logging.FileLogTraceListener, Microsoft.VisualBasic, Version=8.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a, processorArchitecture=MSIL" 
				 initializeData="FileLogWriter"/>
			<add name="EventLog" type="System.Diagnostics.EventLogTraceListener" initializeData="BillingAllClear"/>
		</sharedListeners>
	</system.diagnostics>
</configuration>

Any help would be greatly appreciated.

[hoffmn]

I am trying to make a new instance of this program from scratch and I keep getting an error when trying to add my data source. When I try connecting I get the following message.

An unexpected error has occured.

Error Message: Could not load type 'Microsoft.VisualStudio.DataDesign.SyncDesigner.SyncFacade.SyncManager' from assembly 'Microsoft.VisualStudio.DataDesign.SyncDesigner.DslPackage, Version=9.0.0.0, Culture=neutral, PublicToken=b03f5f7f11d50a3a'.

[hoffmn]

Did some quick searching on my own. This is a result of not having the most recent updates for my VS.Net.

[hoffmn]

I have a stored procedure that clears student accounts to showed that they have paid their bills. I am trying to create a form in vb.net that allows them to enter a parameter and then execute the stored procedure. I need to be point in the right direction though, it is a little above my skill set.

Here is my SQL code for the stored procedure

USE [iCAMS]
GO
/****** Object:  StoredProcedure [dbo].[pr_BillingAllClearReset]	Script Date: 11/09/2009 13:44:23 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


ALTER PROCEDURE [dbo].[pr_BillingAllClearReset]
	@Term AS VARCHAR(20) = ''
AS
BEGIN
-- Syntax message
IF @Term = ''
BEGIN
	PRINT ''
	PRINT 'pr_BillingAllClearReset is a stored procedure to reset the BillingAllClear flag'
	PRINT 'for all students with a status record for the specified term'
	PRINT ''
	PRINT 'example: EXEC pr_BillingAllClearReset @Term = ''SP-08'''
	PRINT ''
	RETURN --abort procedure

END

DECLARE @TermID int
SET @TermID = iCAMS.dbo.fn_TermTextToID (@Term)

UPDATE CAMS_Enterprise.dbo.Student
SET BillingAllClear = 'No'
WHERE StudentUID IN (SELECT StudentUID FROM fn_EnrolledStudents(@TermID))
		OR StudentUID IN (SELECT StudentUID FROM fn_RegisteredStudents(@TermID))


SELECT StudentUID
		, StudentID
		, LastName
		, FirstName
		, MiddleInitial
		, BillingAllClear
FROM CAMS_Enterprise.dbo.Student S
WHERE StudentUID IN (SELECT StudentUID FROM fn_EnrolledStudents(@TermID))
		OR StudentUID IN (SELECT StudentUID FROM fn_RegisteredStudents(@TermID))

END

Here is my VB.Net code

Imports System.Data.SqlClient
Imports System.Configuration
Imports System.Configuration.ConfigurationSettings

Public Class DataAccess

	'   Function to retrieve the connection from the app.config
	Public Shared Function GetConnectionString(ByVal conName As String) As String

		Dim strReturn As New String("")

		If Not String.IsNullOrEmpty(conName) Then
			strReturn = ConfigurationManager.ConnectionStrings(conName).ConnectionString
		Else
			strReturn = ConfigurationManager.ConnectionStrings("iCAMSConnectionString").ConnectionString
		End If

		Return strReturn

	End Function

	'   Returns a BindingSource, which is used with, for example, a DataGridView control
	Public Shared Function GetBindingSource(ByVal cmd As SqlCommand) As BindingSource
		'declare our binding source
		Dim oBindingSource As New BindingSource()
		' Create a new data adapter based on the specified query.
		Dim daGet As New SqlDataAdapter(cmd)
		' Populate a new data table and bind it to the BindingSource.
		Dim dtGet As New DataTable()
		'set the timeout of the SqlCommandObject
		cmd.CommandTimeout = 240
		dtGet.Locale = System.Globalization.CultureInfo.InvariantCulture
		Try
			'fill the DataTable with the SqlDataAdapter
			daGet.Fill(dtGet)
		Catch ex As Exception
			'check for errors
			MsgBox(ex.Message, MsgBoxStyle.OkOnly, "Error receiving data")
			Return Nothing
		End Try
		'set the DataSource for the BindingSource to the DataTable
		oBindingSource.DataSource = dtGet
		'return the BindingSource to the calling method or control
		Return oBindingSource
	End Function

	'   Method for handling the ConnectionState of the connection object passed to it
	Public Shared Sub HandleConnection(ByVal conn As SqlConnection)
		With conn
			'do a switch on the state of the connection
			Select Case .State
				Case ConnectionState.Open
					'the connection is open
					'close then re-open
					.Close()
					.Open()
					Exit Select
				Case ConnectionState.Closed
					'connection is open
					'open the connection
					.Open()
					Exit Select
				Case Else
					.Close()
					.Open()
					Exit Select
			End Select
		End With
	End Sub

	Public Shared Function InsertNewRecord(ByVal term As String) As Boolean
		Dim cnInsert As New SqlConnection(GetConnectionString("iCAMSConnectionString"))
		Dim cmdInsert As New SqlCommand
		Dim sSQL As New String("")
		Dim iSqlStatus As Integer

		'Set the stored procedure we're going to execute
		sSQL = "pr_BillingAllClearReset"

		'Clear any parameters
		cmdInsert.Parameters.Clear()
		Try
			'Set the SqlCommand Object Properties
			With cmdInsert
				'Tell it what to execute
				.CommandText = sSQL
				.CommandType = CommandType.StoredProcedure
				'Now add the parameters to our procedure
				.Parameters.AddWithValue("@Term", term)
				'Set the connection of the object
				.Connection = cnInsert
			End With

			'Now take care of the connection
			HandleConnection(cnInsert)

			'Set the iSqlStatus to the ExecuteNonQuery 
			'status of the insert (0 = success, 1 = failed)
			iSqlStatus = cmdInsert.ExecuteNonQuery

			'Now check the status
			If Not iSqlStatus = 0 Then
				MsgBox(MsgBoxStyle.OkOnly, "Billing all clear was successful")
				Return False
			Else
				MsgBox(MsgBoxStyle.OkOnly, "Billing all clear was unsuccessful")
				Return True
			End If
		Catch ex As Exception
			MsgBox(ex.Message, MsgBoxStyle.OkOnly, "Error")
		Finally
			'Now close the connection
			HandleConnection(cnInsert)
		End Try
	End Function

End Class

I appreciate any help that can be offered.

[PsychoCoder]

Topics merged, please do not create anymore duplicates

[hoffmn]

PsychoCoder, on 9 Nov, 2009 - 10:48 AM, said:

Topics merged, please do not create anymore duplicates

It was in another forum, it is not like I duplicated it in the VB.Net forum. I wanted to see if any of the SQL guys could help me out since I was not getting much help in the VB.Net forum.

[PsychoCoder]

It doesn't matter if it's in the same forum or a different forum, please do not create duplicate topics. You're not always going to get help on your time frame, we're all volunteers here and have jobs, school, families, etc and aren't here 100% of the time. Opening a duplicate topic in a different forum isn't going to speed this up at all

[hoffmn]

krum110487, on 04 November 2009 - 05:26 PM, said:

some of these may help

http://www.developer...res-with-ADONET
http://bytes.com/top...tored-procedure

the best way to search is type:

"Execute stored procedures VB.net"

you will get all kinds of hits, try stuff out, and come back if you need more specific help!

Thanks for your assistance. I was able to complete this using LINQ. Works beautifully.