rasing a seperate sub routine from a seperate button

[jimpose]

Raising a seperate sub routine from a seperate button

Hi All,

I wonder if anyone can help me with the following problem,

I have created a private sub routine (see below)


PrivateSub GetData(ByVal direction AsString)


this is used to query an sql database using ADO.net and create a data set, etc. Problem is I need to raise this GetData sub routine to run via a different asp button (see below)


PrivateSub btQuery_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btQuery.Click


Please could anyone advise on the piece of code I should use?

Many thanks in advance

James

 

[jmcilhinney]

I don't know the structure of you entire code but wouldn't it just be a case of calling the GetData method from inside the btQuery_Click method? Do you know how to call methods?

 

[jimpose]

Hi Jmcilhinney

Yes that is exactly what I want to do - call the method / code from 'GetData' when I click the 'btQuery' button - but, No, I am not entirely sure how to do this.

I have been advised that if I use the code below within my 'btQuery' sub it should work but advisor is not sure;


Dim direct AsString = "a process to get the direction"
GetData(direct)



My current 'GetData' sub routine code is as follows;


PublicSub GetData(ByVal direction AsString)
' Create SQL statement to return a page of records.
queryCommandVrf.Parameters.Clear()
SelectCase direction
Case "Next"
queryCommandVrf.CommandText = "Select eMailAddress, VSPVendorName, VendorProvidedName, Password, ReminderText, CompanyNumber, VATNumber, WebSiteURL from tblaccount where (eMailAddress like @queryfreetext) or (VSPVendorName like @queryfreetext) or (VendorProvidedName like @queryfreetext) or (Password like @queryfreetext) or (ReminderText like @queryfreetext) or (CompanyNumber like @queryfreetext) or (VATNumber like @queryfreetext) or (WebSiteURL like @queryfreetext)"
queryCommandVrf.Parameters.Add("@queryfreetext", SqlDbType.VarChar).Value = tbfreequery.Text
Case "Previous"
queryCommandVrf.CommandText = "Select eMailAddress, VSPVendorName, VendorProvidedName, Password, ReminderText, CompanyNumber, VATNumber, WebSiteURL from tblaccount where (eMailAddress like @queryfreetext) or (VSPVendorName like @queryfreetext) or (VendorProvidedName like @queryfreetext) or (Password like @queryfreetext) or (ReminderText like @queryfreetext) or (CompanyNumber like @queryfreetext) or (VATNumber like @queryfreetext) or (WebSiteURL like @queryfreetext)"
queryCommandVrf.Parameters.Add("@queryfreetext", SqlDbType.VarChar).Value = tbfreequery.Text
CaseElse
queryCommandVrf.CommandText = "Select eMailAddress, VSPVendorName, VendorProvidedName, Password, ReminderText, CompanyNumber, VATNumber, WebSiteURL from tblaccount where (eMailAddress like @queryfreetext) or (VSPVendorName like @queryfreetext) or (VendorProvidedName like @queryfreetext) or (Password like @queryfreetext) or (ReminderText like @queryfreetext) or (CompanyNumber like @queryfreetext) or (VATNumber like @queryfreetext) or (WebSiteURL like @queryfreetext)"
queryCommandVrf.Parameters.Add("@queryfreetext", SqlDbType.VarChar).Value = tbfreequery.Text

'Determine total pages.
Dim querytotalpages As SqlCommand = New SqlCommand("SELECT Count(*) FROM tblAccount", connectionVrf)
connectionVrf.Open()
Dim totalRecords AsInteger = CInt(querytotalpages.ExecuteScalar())
connectionVrf.Close()
totalPages = CInt(Math.Ceiling(CDbl(totalRecords) / pageSize))
EndSelect
' Fill a temporary table with query results.
Dim dataTableVrf As DataTable = New DataTable("AccountDetails")
Dim recordsAffected AsInteger = adapterVrf.Fill(dataTableVrf)
' If table does not exist, create it.
If custTable IsNothingThen custTable = dataTableVrf.Clone()
' Refresh table if at least one record returned.
If recordsAffected > 0 Then
SelectCase direction
Case "Next"
currentPage += 1
Case "Previous"
currentPage += -1
CaseElse
currentPage = 1
EndSelect
btNext.Visible = True
lblnext.Visible = True
btBack.Visible = True
lblback.Visible = True
lblnavtext.Visible = True
btUpdateRec.Visible = True
btDeleteRec.Visible = True
Else
tbStatusResults.Text = "Page " & currentPage & " of " & totalPages
EndIf
If recordsAffected = 0 Then
btNext.Visible = False
lblnext.Visible = False
btBack.Visible = False
lblback.Visible = False
lblnavtext.Visible = False
btUpdateRec.Visible = False
btDeleteRec.Visible = False
tbStatusResults.Text = "Sorry, No Records Retrieved"
' Clear rows and add New results.
custTable.Rows.Clear()
Dim dataRowVrf As DataRow
ForEach dataRowVrf In dataTableVrf.Rows
custTable.ImportRow(dataRowVrf)
Next
EndIf
AddHandler btNext.Click, New EventHandler(AddressOf btNext_Click)
AddHandler btBack.Click, New EventHandler(AddressOf btBack_Click)
' Populate DataSet with first page of records and bind to grid.
GetData("Default")
Dim dataViewVrf As DataView = New DataView(custTable, "", "AccountDetails", DataViewRowState.CurrentRows)
Dim startrecord AsString = CType(dataViewVrf(0)("AccountDetails"), String)
dataViewVrf.AllowEdit = True
Dim dataRowViewVrf As DataRowView
Dim em AsString = ""
Dim pa AsString = ""
Dim pr AsString = ""
Dim cna AsString = ""
Dim cnu AsString = ""
Dim va AsString = ""
Dim vs AsString = ""
Dim we AsString = ""
ForEach dataRowViewVrf In dataViewVrf
em &= dataRowViewVrf("eMailAddress").ToString & " "
pa &= dataRowViewVrf("Password").ToString & " "
pr &= dataRowViewVrf("ReminderText").ToString & " "
cna &= dataRowViewVrf("VendorProvidedName").ToString & " "
cnu &= dataRowViewVrf("CompanyNumber").ToString & " "
va &= dataRowViewVrf("VATNumber").ToString & " "
vs &= dataRowViewVrf("VSPVendorName").ToString & " "
we &= dataRowViewVrf("WebsiteURL").ToString & " "
Next
tbEmail.Text = em
tbPassword.Text = pa
tbReminder.Text = pr
tbConame.Text = cna
tbConum.Text = cnu
tbVatnum.Text = va
tbVendor.Text = vs
tbWebsite.Text = we
'close VRF database connection
connectionVrf.Close()
EndSub




Again, Many thanks

James

 

[jimpose]

Hi All,

Thank you for the replies to my previous question - I have now sorted my previous issue but in turn have now encountered a second issue.

The error i am getting is when the "GetData" sub routine is being run. The "btQuery" once clicked calls the "GetData" routine and queries the sql database and attempts to populate a DataTable called "dataTableVrf".

I am getting the following error at line 123:


ExecuteReader: CommandText property has not been initialized

Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.InvalidOperationException: ExecuteReader: CommandText property has not been initialized

Source Error:

Line 121:Line 122: dataTableVrf = New DataTable("AccountDetails")Line 123: adapterVrf.Fill(dataTableVrf)Line 124: recordsAffected = adapterVrf.Fill(dataTableVrf)Line 125:


My Entire Webform code is below:

[SIZE=2][COLOR=#0000ff]Option[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]Explicit[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]On[/COLOR][/SIZE][SIZE=2] 
[/SIZE][SIZE=2][COLOR=#0000ff]Imports[/COLOR][/SIZE][SIZE=2] System
[/SIZE][SIZE=2][COLOR=#0000ff]Imports[/COLOR][/SIZE][SIZE=2] System.Data
[/SIZE][SIZE=2][COLOR=#0000ff]Imports[/COLOR][/SIZE][SIZE=2] System.Data.SqlClient
[/SIZE][SIZE=2][COLOR=#0000ff]Imports[/COLOR][/SIZE][SIZE=2] System.Drawing
[/SIZE][SIZE=2][COLOR=#0000ff]Public[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]Class[/COLOR][/SIZE][SIZE=2] vrfwebform
[/SIZE][SIZE=2][COLOR=#0000ff]Inherits[/COLOR][/SIZE][SIZE=2] System.Web.UI.Page
 
[/SIZE][SIZE=2][COLOR=#008000]'Current page variable
[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]Shared[/COLOR][/SIZE][SIZE=2] currentPage [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]Integer[/COLOR][/SIZE][SIZE=2] = 0
[/SIZE][SIZE=2][COLOR=#008000]'Webform variables
[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]Shared[/COLOR][/SIZE][SIZE=2] connectionVrf [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] SqlConnection = [/SIZE][SIZE=2][COLOR=#0000ff]New[/COLOR][/SIZE][SIZE=2] SqlConnection("data source=ROBOT;user id=vps200;pwd=;Initial Catalog=OnlineVRF;persist security info=False")
[/SIZE][SIZE=2][COLOR=#0000ff]Shared[/COLOR][/SIZE][SIZE=2] adapterVrf [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] SqlDataAdapter = [/SIZE][SIZE=2][COLOR=#0000ff]New[/COLOR][/SIZE][SIZE=2] SqlDataAdapter("", connectionVrf)
[/SIZE][SIZE=2][COLOR=#0000ff]Shared[/COLOR][/SIZE][SIZE=2] queryCommandVrf [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] SqlCommand = adapterVrf.SelectCommand()
[/SIZE][SIZE=2][COLOR=#0000ff]Shared[/COLOR][/SIZE][SIZE=2] dataReaderVrf [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] SqlDataReader
[/SIZE][SIZE=2][COLOR=#0000ff]Shared[/COLOR][/SIZE][SIZE=2] custTable [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] DataTable
[/SIZE][SIZE=2][COLOR=#0000ff]Shared[/COLOR][/SIZE][SIZE=2] dataViewVrf [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] DataView
[/SIZE][SIZE=2][COLOR=#0000ff]Shared[/COLOR][/SIZE][SIZE=2] dataRowViewVrf [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] DataRowView
[/SIZE][SIZE=2][COLOR=#0000ff]Protected[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]WithEvents[/COLOR][/SIZE][SIZE=2] d [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] System.Web.UI.WebControls.TextBox
[/SIZE][SIZE=2][COLOR=#0000ff]Shared[/COLOR][/SIZE][SIZE=2] dataTableVrf [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] DataTable


#[/SIZE][SIZE=2][COLOR=#0000ff]Region[/COLOR][/SIZE][SIZE=2] " Web Form Designer Generated Code "
[/SIZE][SIZE=2][COLOR=#008000]'This call is required by the Web Form Designer.
[/COLOR][/SIZE][SIZE=2]<System.Diagnostics.DebuggerStepThrough()> [/SIZE][SIZE=2][COLOR=#0000ff]Private[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]Sub[/COLOR][/SIZE][SIZE=2] InitializeComponent()
[/SIZE][SIZE=2][COLOR=#0000ff]End[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]Sub
[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]Protected[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]WithEvents[/COLOR][/SIZE][SIZE=2] tbPassword [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] System.Web.UI.WebControls.TextBox
[/SIZE][SIZE=2][COLOR=#0000ff]Protected[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]WithEvents[/COLOR][/SIZE][SIZE=2] tbReminder [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] System.Web.UI.WebControls.TextBox
[/SIZE][SIZE=2][COLOR=#0000ff]Protected[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]WithEvents[/COLOR][/SIZE][SIZE=2] tbAccountid [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] System.Web.UI.WebControls.TextBox
[/SIZE][SIZE=2][COLOR=#0000ff]Protected[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]WithEvents[/COLOR][/SIZE][SIZE=2] tbWebsite [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] System.Web.UI.WebControls.TextBox
[/SIZE][SIZE=2][COLOR=#0000ff]Protected[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]WithEvents[/COLOR][/SIZE][SIZE=2] tbVendor [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] System.Web.UI.WebControls.TextBox
[/SIZE][SIZE=2][COLOR=#0000ff]Protected[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]WithEvents[/COLOR][/SIZE][SIZE=2] tbVatnum [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] System.Web.UI.WebControls.TextBox
[/SIZE][SIZE=2][COLOR=#0000ff]Protected[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]WithEvents[/COLOR][/SIZE][SIZE=2] tbConum [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] System.Web.UI.WebControls.TextBox
[/SIZE][SIZE=2][COLOR=#0000ff]Protected[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]WithEvents[/COLOR][/SIZE][SIZE=2] tbConame [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] System.Web.UI.WebControls.TextBox
[/SIZE][SIZE=2][COLOR=#0000ff]Protected[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]WithEvents[/COLOR][/SIZE][SIZE=2] Reminder [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] System.Web.UI.WebControls.Label
[/SIZE][SIZE=2][COLOR=#0000ff]Protected[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]WithEvents[/COLOR][/SIZE][SIZE=2] Pword [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] System.Web.UI.WebControls.Label
[/SIZE][SIZE=2][COLOR=#0000ff]Protected[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]WithEvents[/COLOR][/SIZE][SIZE=2] Email [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] System.Web.UI.WebControls.Label
[/SIZE][SIZE=2][COLOR=#0000ff]Protected[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]WithEvents[/COLOR][/SIZE][SIZE=2] Website [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] System.Web.UI.WebControls.Label
[/SIZE][SIZE=2][COLOR=#0000ff]Protected[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]WithEvents[/COLOR][/SIZE][SIZE=2] Vendor [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] System.Web.UI.WebControls.Label
[/SIZE][SIZE=2][COLOR=#0000ff]Protected[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]WithEvents[/COLOR][/SIZE][SIZE=2] VatNum [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] System.Web.UI.WebControls.Label
[/SIZE][SIZE=2][COLOR=#0000ff]Protected[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]WithEvents[/COLOR][/SIZE][SIZE=2] CoNum [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] System.Web.UI.WebControls.Label
[/SIZE][SIZE=2][COLOR=#0000ff]Protected[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]WithEvents[/COLOR][/SIZE][SIZE=2] CoName [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] System.Web.UI.WebControls.Label
[/SIZE][SIZE=2][COLOR=#0000ff]Protected[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]WithEvents[/COLOR][/SIZE][SIZE=2] btClear [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] System.Web.UI.WebControls.Button
[/SIZE][SIZE=2][COLOR=#0000ff]Protected[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]WithEvents[/COLOR][/SIZE][SIZE=2] btSave [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] System.Web.UI.WebControls.Button
[/SIZE][SIZE=2][COLOR=#0000ff]Protected[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]WithEvents[/COLOR][/SIZE][SIZE=2] btNext [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] System.Web.UI.WebControls.Button
[/SIZE][SIZE=2][COLOR=#0000ff]Protected[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]WithEvents[/COLOR][/SIZE][SIZE=2] btQuery [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] System.Web.UI.WebControls.Button
[/SIZE][SIZE=2][COLOR=#0000ff]Protected[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]WithEvents[/COLOR][/SIZE][SIZE=2] Label1 [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] System.Web.UI.WebControls.Label
[/SIZE][SIZE=2][COLOR=#0000ff]Protected[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]WithEvents[/COLOR][/SIZE][SIZE=2] tbEmail [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] System.Web.UI.WebControls.TextBox
[/SIZE][SIZE=2][COLOR=#0000ff]Protected[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]WithEvents[/COLOR][/SIZE][SIZE=2] btUpdateRec [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] System.Web.UI.WebControls.Button
[/SIZE][SIZE=2][COLOR=#0000ff]Protected[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]WithEvents[/COLOR][/SIZE][SIZE=2] btDeleteRec [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] System.Web.UI.WebControls.Button
[/SIZE][SIZE=2][COLOR=#0000ff]Protected[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]WithEvents[/COLOR][/SIZE][SIZE=2] btInsert [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] System.Web.UI.WebControls.Button
[/SIZE][SIZE=2][COLOR=#0000ff]Protected[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]WithEvents[/COLOR][/SIZE][SIZE=2] tbEmailChk [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] System.Web.UI.WebControls.TextBox
[/SIZE][SIZE=2][COLOR=#0000ff]Protected[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]WithEvents[/COLOR][/SIZE][SIZE=2] tbRecordsUpdated [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] System.Web.UI.WebControls.TextBox
[/SIZE][SIZE=2][COLOR=#0000ff]Protected[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]WithEvents[/COLOR][/SIZE][SIZE=2] tbStatusResults [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] System.Web.UI.WebControls.TextBox
[/SIZE][SIZE=2][COLOR=#0000ff]Protected[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]WithEvents[/COLOR][/SIZE][SIZE=2] Label2 [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] System.Web.UI.WebControls.Label
[/SIZE][SIZE=2][COLOR=#0000ff]Protected[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]WithEvents[/COLOR][/SIZE][SIZE=2] btBack [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] System.Web.UI.WebControls.Button
[/SIZE][SIZE=2][COLOR=#0000ff]Protected[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]WithEvents[/COLOR][/SIZE][SIZE=2] lblback [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] System.Web.UI.WebControls.Label
[/SIZE][SIZE=2][COLOR=#0000ff]Protected[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]WithEvents[/COLOR][/SIZE][SIZE=2] lblnavtext [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] System.Web.UI.WebControls.Label
[/SIZE][SIZE=2][COLOR=#0000ff]Protected[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]WithEvents[/COLOR][/SIZE][SIZE=2] lblnext [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] System.Web.UI.WebControls.Label
[/SIZE][SIZE=2][COLOR=#0000ff]Protected[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]WithEvents[/COLOR][/SIZE][SIZE=2] tbfreequery [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] System.Web.UI.WebControls.TextBox
[/SIZE][SIZE=2][COLOR=#0000ff]Protected[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]WithEvents[/COLOR][/SIZE][SIZE=2] Label3 [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] System.Web.UI.WebControls.Label
[/SIZE][SIZE=2][COLOR=#0000ff]Protected[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]WithEvents[/COLOR][/SIZE][SIZE=2] Image1 [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] System.Web.UI.WebControls.Image
 
[/SIZE][SIZE=2][COLOR=#008000]'NOTE: The following placeholder declaration is required by the Web Form Designer.
[/COLOR][/SIZE][SIZE=2][COLOR=#008000]'Do not delete or move it.
[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]Private[/COLOR][/SIZE][SIZE=2] designerPlaceholderDeclaration [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] System.Object
[/SIZE][SIZE=2][COLOR=#0000ff]Private[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]Sub[/COLOR][/SIZE][SIZE=2] Page_Init([/SIZE][SIZE=2][COLOR=#0000ff]ByVal[/COLOR][/SIZE][SIZE=2] sender [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] System.Object, [/SIZE][SIZE=2][COLOR=#0000ff]ByVal[/COLOR][/SIZE][SIZE=2] e [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] System.EventArgs) [/SIZE][SIZE=2][COLOR=#0000ff]Handles[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]MyBase[/COLOR][/SIZE][SIZE=2].Init
[/SIZE][SIZE=2][COLOR=#008000]'CODEGEN: This method call is required by the Web Form Designer
[/COLOR][/SIZE][SIZE=2][COLOR=#008000]'Do not modify it using the code editor.
[/COLOR][/SIZE][SIZE=2]InitializeComponent()
[/SIZE][SIZE=2][COLOR=#0000ff]End[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]Sub
[/COLOR][/SIZE][SIZE=2]#[/SIZE][SIZE=2][COLOR=#0000ff]End[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]Region
 
[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]Private[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]Sub[/COLOR][/SIZE][SIZE=2] Page_Load([/SIZE][SIZE=2][COLOR=#0000ff]ByVal[/COLOR][/SIZE][SIZE=2] sender [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] System.Object, [/SIZE][SIZE=2][COLOR=#0000ff]ByVal[/COLOR][/SIZE][SIZE=2] e [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] System.EventArgs) [/SIZE][SIZE=2][COLOR=#0000ff]Handles[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]MyBase[/COLOR][/SIZE][SIZE=2].Load
[/SIZE][SIZE=2][COLOR=#008000]'hide buttons that are not required on load
[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]If[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]Not[/COLOR][/SIZE][SIZE=2] Page.IsPostBack [/SIZE][SIZE=2][COLOR=#0000ff]Then
[/COLOR][/SIZE][SIZE=2]btUpdateRec.Visible = [/SIZE][SIZE=2][COLOR=#0000ff]False
[/COLOR][/SIZE][SIZE=2]btDeleteRec.Visible = [/SIZE][SIZE=2][COLOR=#0000ff]False
[/COLOR][/SIZE][SIZE=2]btNext.Visible = [/SIZE][SIZE=2][COLOR=#0000ff]False
[/COLOR][/SIZE][SIZE=2]lblnext.Visible = [/SIZE][SIZE=2][COLOR=#0000ff]False
[/COLOR][/SIZE][SIZE=2]btBack.Visible = [/SIZE][SIZE=2][COLOR=#0000ff]False
[/COLOR][/SIZE][SIZE=2]lblback.Visible = [/SIZE][SIZE=2][COLOR=#0000ff]False
[/COLOR][/SIZE][SIZE=2]lblnavtext.Visible = [/SIZE][SIZE=2][COLOR=#0000ff]False
[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]End[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]If
[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]End[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]Sub
[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]Public[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]Sub[/COLOR][/SIZE][SIZE=2] GetData([/SIZE][SIZE=2][COLOR=#0000ff]ByVal[/COLOR][/SIZE][SIZE=2] direction [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]String[/COLOR][/SIZE][SIZE=2])
[/SIZE][SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][SIZE=2] queryCommandVrf [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] SqlCommand = adapterVrf.SelectCommand()
[/SIZE][SIZE=2][COLOR=#008000]' Create SQL statement to return a page of records.
[/COLOR][/SIZE][SIZE=2]queryCommandVrf.Parameters.Clear()
[/SIZE][SIZE=2][COLOR=#0000ff]Select[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]Case[/COLOR][/SIZE][SIZE=2] direction
[/SIZE][SIZE=2][COLOR=#0000ff]Case[/COLOR][/SIZE][SIZE=2] "Next"
queryCommandVrf.CommandText = "Select eMailAddress, VSPVendorName, VendorProvidedName, Password, ReminderText, CompanyNumber, VATNumber, WebSiteURL from tblaccount where (eMailAddress like @queryfreetext) or (VSPVendorName like @queryfreetext) or (VendorProvidedName like @queryfreetext) or (Password like @queryfreetext) or (ReminderText like @queryfreetext) or (CompanyNumber like @queryfreetext) or (VATNumber like @queryfreetext) or (WebSiteURL like @queryfreetext)"
queryCommandVrf.Parameters.Add("@queryfreetext", SqlDbType.VarChar).Value = tbfreequery.Text
[/SIZE][SIZE=2][COLOR=#0000ff]Case[/COLOR][/SIZE][SIZE=2] "Previous"
queryCommandVrf.CommandText = "Select eMailAddress, VSPVendorName, VendorProvidedName, Password, ReminderText, CompanyNumber, VATNumber, WebSiteURL from tblaccount where (eMailAddress like @queryfreetext) or (VSPVendorName like @queryfreetext) or (VendorProvidedName like @queryfreetext) or (Password like @queryfreetext) or (ReminderText like @queryfreetext) or (CompanyNumber like @queryfreetext) or (VATNumber like @queryfreetext) or (WebSiteURL like @queryfreetext)"
queryCommandVrf.Parameters.Add("@queryfreetext", SqlDbType.VarChar).Value = tbfreequery.Text
[/SIZE][SIZE=2][COLOR=#0000ff]End[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]Select
[/COLOR][/SIZE][SIZE=2][COLOR=#008000]' Fill a temporary table with query results.
[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][SIZE=2] dataTableVrf [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] DataTable
[/SIZE][SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][SIZE=2] recordsAffected [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]Integer
[/COLOR][/SIZE][SIZE=2]dataTableVrf = [/SIZE][SIZE=2][COLOR=#0000ff]New[/COLOR][/SIZE][SIZE=2] DataTable("AccountDetails")
adapterVrf.Fill(dataTableVrf)
recordsAffected = adapterVrf.Fill(dataTableVrf)
[/SIZE][SIZE=2][COLOR=#008000]' If table does not exist, create it.
[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]If[/COLOR][/SIZE][SIZE=2] custTable [/SIZE][SIZE=2][COLOR=#0000ff]Is[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]Nothing[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]Then[/COLOR][/SIZE][SIZE=2] custTable = dataTableVrf.Clone()
adapterVrf.Fill(custTable)
[/SIZE][SIZE=2][COLOR=#008000]' Refresh table if at least one record returned.
[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]If[/COLOR][/SIZE][SIZE=2] recordsAffected > 0 [/SIZE][SIZE=2][COLOR=#0000ff]Then
[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]Select[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]Case[/COLOR][/SIZE][SIZE=2] direction
[/SIZE][SIZE=2][COLOR=#0000ff]Case[/COLOR][/SIZE][SIZE=2] "Next"
currentPage += 1
[/SIZE][SIZE=2][COLOR=#0000ff]Case[/COLOR][/SIZE][SIZE=2] "Previous"
currentPage += -1
[/SIZE][SIZE=2][COLOR=#0000ff]End[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]Select
[/COLOR][/SIZE][SIZE=2]btNext.Visible = [/SIZE][SIZE=2][COLOR=#0000ff]True
[/COLOR][/SIZE][SIZE=2]lblnext.Visible = [/SIZE][SIZE=2][COLOR=#0000ff]True
[/COLOR][/SIZE][SIZE=2]btBack.Visible = [/SIZE][SIZE=2][COLOR=#0000ff]True
[/COLOR][/SIZE][SIZE=2]lblback.Visible = [/SIZE][SIZE=2][COLOR=#0000ff]True
[/COLOR][/SIZE][SIZE=2]lblnavtext.Visible = [/SIZE][SIZE=2][COLOR=#0000ff]True
[/COLOR][/SIZE][SIZE=2]btUpdateRec.Visible = [/SIZE][SIZE=2][COLOR=#0000ff]True
[/COLOR][/SIZE][SIZE=2]btDeleteRec.Visible = [/SIZE][SIZE=2][COLOR=#0000ff]True
[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]Else
[/COLOR][/SIZE][SIZE=2]tbStatusResults.Text = "No records found, please enter different a different search"
[/SIZE][SIZE=2][COLOR=#0000ff]End[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]If
[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]If[/COLOR][/SIZE][SIZE=2] recordsAffected = 0 [/SIZE][SIZE=2][COLOR=#0000ff]Then
[/COLOR][/SIZE][SIZE=2]btNext.Visible = [/SIZE][SIZE=2][COLOR=#0000ff]False
[/COLOR][/SIZE][SIZE=2]lblnext.Visible = [/SIZE][SIZE=2][COLOR=#0000ff]False
[/COLOR][/SIZE][SIZE=2]btBack.Visible = [/SIZE][SIZE=2][COLOR=#0000ff]False
[/COLOR][/SIZE][SIZE=2]lblback.Visible = [/SIZE][SIZE=2][COLOR=#0000ff]False
[/COLOR][/SIZE][SIZE=2]lblnavtext.Visible = [/SIZE][SIZE=2][COLOR=#0000ff]False
[/COLOR][/SIZE][SIZE=2]btUpdateRec.Visible = [/SIZE][SIZE=2][COLOR=#0000ff]False
[/COLOR][/SIZE][SIZE=2]btDeleteRec.Visible = [/SIZE][SIZE=2][COLOR=#0000ff]False
[/COLOR][/SIZE][SIZE=2]tbStatusResults.Text = "Sorry, No Records Retrieved"
[/SIZE][SIZE=2][COLOR=#0000ff]End[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]If
[/COLOR][/SIZE][SIZE=2][COLOR=#008000]' Clear rows and add New results.
[/COLOR][/SIZE][SIZE=2]custTable.Rows.Clear()
[/SIZE][SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][SIZE=2] dataRowVrf [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] DataRow
[/SIZE][SIZE=2][COLOR=#0000ff]For[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]Each[/COLOR][/SIZE][SIZE=2] dataRowVrf [/SIZE][SIZE=2][COLOR=#0000ff]In[/COLOR][/SIZE][SIZE=2] dataTableVrf.Rows
custTable.ImportRow(dataRowVrf)
[/SIZE][SIZE=2][COLOR=#0000ff]Next
 
[/COLOR][/SIZE][SIZE=2][COLOR=#008000]' Populate DataSet with first page of records and bind to grid.
[/COLOR][/SIZE][SIZE=2]GetData("direct")
[/SIZE][SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][SIZE=2] dataViewVrf [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] DataView = [/SIZE][SIZE=2][COLOR=#0000ff]New[/COLOR][/SIZE][SIZE=2] DataView(custTable, "", "AccountDetails", DataViewRowState.CurrentRows)
[/SIZE][SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][SIZE=2] startrecord [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]String[/COLOR][/SIZE][SIZE=2] = [/SIZE][SIZE=2][COLOR=#0000ff]CType[/COLOR][/SIZE][SIZE=2](dataViewVrf(0)("AccountDetails"), [/SIZE][SIZE=2][COLOR=#0000ff]String[/COLOR][/SIZE][SIZE=2])
dataViewVrf.AllowEdit = [/SIZE][SIZE=2][COLOR=#0000ff]True
[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][SIZE=2] dataRowViewVrf [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] DataRowView
[/SIZE][SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][SIZE=2] ai [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]String[/COLOR][/SIZE][SIZE=2] = ""
[/SIZE][SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][SIZE=2] em [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]String[/COLOR][/SIZE][SIZE=2] = ""
[/SIZE][SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][SIZE=2] pa [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]String[/COLOR][/SIZE][SIZE=2] = ""
[/SIZE][SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][SIZE=2] pr [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]String[/COLOR][/SIZE][SIZE=2] = ""
[/SIZE][SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][SIZE=2] cna [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]String[/COLOR][/SIZE][SIZE=2] = ""
[/SIZE][SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][SIZE=2] cnu [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]String[/COLOR][/SIZE][SIZE=2] = ""
[/SIZE][SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][SIZE=2] va [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]String[/COLOR][/SIZE][SIZE=2] = ""
[/SIZE][SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][SIZE=2] vs [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]String[/COLOR][/SIZE][SIZE=2] = ""
[/SIZE][SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][SIZE=2] we [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]String[/COLOR][/SIZE][SIZE=2] = ""
[/SIZE][SIZE=2][COLOR=#0000ff]For[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]Each[/COLOR][/SIZE][SIZE=2] dataRowViewVrf [/SIZE][SIZE=2][COLOR=#0000ff]In[/COLOR][/SIZE][SIZE=2] dataViewVrf
ai &= dataRowViewVrf("AccountID").String & " "
em &= dataRowViewVrf("eMailAddress").ToString & " "
pa &= dataRowViewVrf("Password").ToString & " "
pr &= dataRowViewVrf("ReminderText").ToString & " "
cna &= dataRowViewVrf("VendorProvidedName").ToString & " "
cnu &= dataRowViewVrf("CompanyNumber").ToString & " "
va &= dataRowViewVrf("VATNumber").ToString & " "
vs &= dataRowViewVrf("VSPVendorName").ToString & " "
we &= dataRowViewVrf("WebsiteURL").ToString & " "
[/SIZE][SIZE=2][COLOR=#0000ff]Next
[/COLOR][/SIZE][SIZE=2]tbAccountid.Text = ai
tbEmail.Text = em
tbPassword.Text = pa
tbReminder.Text = pr
tbConame.Text = cna
tbConum.Text = cnu
tbVatnum.Text = va
tbVendor.Text = vs
tbWebsite.Text = we
[/SIZE][SIZE=2][COLOR=#008000]'close VRF database connection
[/COLOR][/SIZE][SIZE=2]connectionVrf.Close()
[/SIZE][SIZE=2][COLOR=#0000ff]End[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]Sub
[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]Private[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]Sub[/COLOR][/SIZE][SIZE=2] btQuery_Click([/SIZE][SIZE=2][COLOR=#0000ff]ByVal[/COLOR][/SIZE][SIZE=2] sender [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] System.Object, [/SIZE][SIZE=2][COLOR=#0000ff]ByVal[/COLOR][/SIZE][SIZE=2] e [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] System.EventArgs) [/SIZE][SIZE=2][COLOR=#0000ff]Handles[/COLOR][/SIZE][SIZE=2] btQuery.Click
[/SIZE][SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][SIZE=2] direct [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]String[/COLOR][/SIZE][SIZE=2] = "a process to get the direction"
GetData(direct)
[/SIZE][SIZE=2][COLOR=#0000ff]End[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]Sub
[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]Private[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]Sub[/COLOR][/SIZE][SIZE=2] btBack_Click([/SIZE][SIZE=2][COLOR=#0000ff]ByVal[/COLOR][/SIZE][SIZE=2] sender [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] System.Object, [/SIZE][SIZE=2][COLOR=#0000ff]ByVal[/COLOR][/SIZE][SIZE=2] e [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] System.EventArgs) [/SIZE][SIZE=2][COLOR=#0000ff]Handles[/COLOR][/SIZE][SIZE=2] btBack.Click
GetData("Previous")
[/SIZE][SIZE=2][COLOR=#0000ff]End[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]Sub
[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]Private[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]Sub[/COLOR][/SIZE][SIZE=2] btNext_Click([/SIZE][SIZE=2][COLOR=#0000ff]ByVal[/COLOR][/SIZE][SIZE=2] sender [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] System.Object, [/SIZE][SIZE=2][COLOR=#0000ff]ByVal[/COLOR][/SIZE][SIZE=2] e [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] System.EventArgs) [/SIZE][SIZE=2][COLOR=#0000ff]Handles[/COLOR][/SIZE][SIZE=2] btNext.Click
GetData("Next")
[/SIZE][SIZE=2][COLOR=#0000ff]End[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]Sub
[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]Private[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]Sub[/COLOR][/SIZE][SIZE=2] btInsert_Click([/SIZE][SIZE=2][COLOR=#0000ff]ByVal[/COLOR][/SIZE][SIZE=2] sender [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] System.Object, [/SIZE][SIZE=2][COLOR=#0000ff]ByVal[/COLOR][/SIZE][SIZE=2] e [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] System.EventArgs) [/SIZE][SIZE=2][COLOR=#0000ff]Handles[/COLOR][/SIZE][SIZE=2] btInsert.Click
[/SIZE][SIZE=2][COLOR=#008000]'Attempt to insert new account details into VRF datatable (error checking present)
[/COLOR][/SIZE][SIZE=2]dataRowViewVrf = dataViewVrf.AddNew()
dataRowViewVrf.BeginEdit()
dataRowViewVrf("eMailAddress") = tbEmail.Text
dataRowViewVrf("Password") = tbPassword.Text
dataRowViewVrf("ReminderText") = tbReminder.Text
dataRowViewVrf("VendorProvidedName") = tbConame.Text
dataRowViewVrf("CompanyNumber") = tbConum.Text
dataRowViewVrf("VATNumber") = tbVatnum.Text
dataRowViewVrf("VSPVendorName") = tbVendor.Text
dataRowViewVrf("WebsiteURL") = tbWebsite.Text
dataRowViewVrf.EndEdit()
[/SIZE][SIZE=2][COLOR=#008000]'Preliminary form error checking to asscertain whether mandatory form fields have been completed
[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]If[/COLOR][/SIZE][SIZE=2] tbEmail.Text = "" [/SIZE][SIZE=2][COLOR=#0000ff]Then
[/COLOR][/SIZE][SIZE=2]tbStatusResults.Text = "Please enter a valid email address, you cannot create a new account without this information"
[/SIZE][SIZE=2][COLOR=#0000ff]End[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]If
[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]If[/COLOR][/SIZE][SIZE=2] tbPassword.Text = "" [/SIZE][SIZE=2][COLOR=#0000ff]Then
[/COLOR][/SIZE][SIZE=2]tbStatusResults.Text = "Please enter a password for this account, you cannot create a new account without this information"
[/SIZE][SIZE=2][COLOR=#0000ff]End[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]If
[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]If[/COLOR][/SIZE][SIZE=2] tbVendor.Text = "" [/SIZE][SIZE=2][COLOR=#0000ff]Then
[/COLOR][/SIZE][SIZE=2]tbStatusResults.Text = "Please enter a Protx vendor name of your choice, you cannot create a new account without this information"
[/SIZE][SIZE=2][COLOR=#0000ff]End[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]If
[/COLOR][/SIZE][SIZE=2][COLOR=#008000]'Check for duplicate EmailAddress in OnlineVRF database
[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][SIZE=2] emailcheckqueryVrf [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] SqlCommand
[/SIZE][SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][SIZE=2] emailcheckstringVrf [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]String
[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][SIZE=2] accountidgetVrf [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] SqlCommand
[/SIZE][SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][SIZE=2] accountidstring [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]String
[/COLOR][/SIZE][SIZE=2]emailcheckstringVrf = "Select eMailAddress from tblAccount where eMailAddress = @emailaddress"
emailcheckqueryVrf = [/SIZE][SIZE=2][COLOR=#0000ff]New[/COLOR][/SIZE][SIZE=2] SqlCommand(emailcheckstringVrf, connectionVrf)
emailcheckqueryVrf.Parameters.Add("@emailaddress", SqlDbType.VarChar).Value = tbEmail.Text
dataReaderVrf = emailcheckqueryVrf.ExecuteReader()
tbEmailChk.Text = ""
dataReaderVrf.Read()
tbEmailChk.Text = dataReaderVrf(0).ToString
dataReaderVrf.Close()
[/SIZE][SIZE=2][COLOR=#0000ff]If[/COLOR][/SIZE][SIZE=2] tbEmailChk.Text = "@emailaddress" [/SIZE][SIZE=2][COLOR=#0000ff]Then
[/COLOR][/SIZE][SIZE=2]tbStatusResults.Text = "An account with this email address already exists in our database, please choose new a email address"
custTable.RejectChanges()
[/SIZE][SIZE=2][COLOR=#0000ff]End[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]If
[/COLOR][/SIZE][SIZE=2][COLOR=#008000]'Check if DataSet has been changed, if so commit changes to datatable, if not reject changes
[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]If[/COLOR][/SIZE][SIZE=2] DataRowState.Modified [/SIZE][SIZE=2][COLOR=#0000ff]Then
[/COLOR][/SIZE][SIZE=2]tbStatusResults.Text = "You have made changes to the database, please click SAVE to commit changes to account details"
custTable.AcceptChanges()
[/SIZE][SIZE=2][COLOR=#0000ff]End[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]If
[/COLOR][/SIZE][SIZE=2][COLOR=#008000]'Store newly raised AccountID as a variable
[/COLOR][/SIZE][SIZE=2]accountidgetVrf = [/SIZE][SIZE=2][COLOR=#0000ff]New[/COLOR][/SIZE][SIZE=2] SqlCommand("select AccountID from tblAccount where eMailAddress = @emailaddress", connectionVrf)
accountidgetVrf.Parameters.Add("@emailaddress", SqlDbType.VarChar).Value = tbEmail.Text
dataReaderVrf = accountidgetVrf.ExecuteReader()
tbAccountid.Text = ""
dataReaderVrf.Read()
tbAccountid.Text = dataReaderVrf(0).ToString
dataReaderVrf.Close()
[/SIZE][SIZE=2][COLOR=#008000]'close OnlineVRF databse connection
[/COLOR][/SIZE][SIZE=2]connectionVrf.Close()
[/SIZE][SIZE=2][COLOR=#0000ff]End[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]Sub
[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]Private[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]Sub[/COLOR][/SIZE][SIZE=2] btUpdateRec_Click([/SIZE][SIZE=2][COLOR=#0000ff]ByVal[/COLOR][/SIZE][SIZE=2] sender [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] System.Object, [/SIZE][SIZE=2][COLOR=#0000ff]ByVal[/COLOR][/SIZE][SIZE=2] e [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] System.EventArgs) [/SIZE][SIZE=2][COLOR=#0000ff]Handles[/COLOR][/SIZE][SIZE=2] btUpdateRec.Click
dataRowViewVrf.BeginEdit()
tbEmail.Text = dataRowViewVrf("eMailAddress")
tbPassword.Text = dataRowViewVrf("Password")
tbReminder.Text = dataRowViewVrf("ReminderText")
tbConame.Text = dataRowViewVrf("VendorProvidedName")
tbConum.Text = dataRowViewVrf("CompanyNumber")
tbVatnum.Text = dataRowViewVrf("VATNumber")
tbVendor.Text = dataRowViewVrf("VSPVendorName")
tbWebsite.Text = dataRowViewVrf("WebsiteURL")
dataRowViewVrf.EndEdit()
[/SIZE][SIZE=2][COLOR=#008000]'Error checking to asscertain whether mandatory form fields have been updated
[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]If[/COLOR][/SIZE][SIZE=2] tbEmail.Text = "" [/SIZE][SIZE=2][COLOR=#0000ff]Then
[/COLOR][/SIZE][SIZE=2]tbStatusResults.Text = "Please enter a valid email address, you cannot create a new account without this information"
custTable.RejectChanges()
[/SIZE][SIZE=2][COLOR=#0000ff]End[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]If
[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]If[/COLOR][/SIZE][SIZE=2] tbPassword.Text = "" [/SIZE][SIZE=2][COLOR=#0000ff]Then
[/COLOR][/SIZE][SIZE=2]tbStatusResults.Text = "Please enter a password for this account, you cannot create a new account without this information"
custTable.RejectChanges()
[/SIZE][SIZE=2][COLOR=#0000ff]End[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]If
[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]If[/COLOR][/SIZE][SIZE=2] tbVendor.Text = "" [/SIZE][SIZE=2][COLOR=#0000ff]Then
[/COLOR][/SIZE][SIZE=2]tbStatusResults.Text = "Please enter a Protx vendor name of your choice, you cannot create a new account without this information"
custTable.RejectChanges()
[/SIZE][SIZE=2][COLOR=#008000]'If completed check if Row State modified and update changes to Data Table
[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]ElseIf[/COLOR][/SIZE][SIZE=2] DataRowState.Modified [/SIZE][SIZE=2][COLOR=#0000ff]Then
[/COLOR][/SIZE][SIZE=2]tbStatusResults.Text = "You have made changes to the database, please click SAVE to commit changes to account details"
custTable.AcceptChanges()
[/SIZE][SIZE=2][COLOR=#0000ff]End[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]If
[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]End[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]Sub
[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]Private[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]Sub[/COLOR][/SIZE][SIZE=2] btDeleteRec_Click([/SIZE][SIZE=2][COLOR=#0000ff]ByVal[/COLOR][/SIZE][SIZE=2] sender [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] System.Object, [/SIZE][SIZE=2][COLOR=#0000ff]ByVal[/COLOR][/SIZE][SIZE=2] e [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] System.EventArgs) [/SIZE][SIZE=2][COLOR=#0000ff]Handles[/COLOR][/SIZE][SIZE=2] btDeleteRec.Click
[/SIZE][SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][SIZE=2] deleteAccountVrf [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] SqlCommand
[/SIZE][SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][SIZE=2] dataReaderVrf [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] SqlDataReader
[/SIZE][SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][SIZE=2] deleteAccountString [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]String
[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][SIZE=2] intcountDelete [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]Integer
[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][SIZE=2] transactionDelete [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] SqlTransaction
[/SIZE][SIZE=2][COLOR=#008000]'Open VRF database Connection
[/COLOR][/SIZE][SIZE=2]connectionVrf = [/SIZE][SIZE=2][COLOR=#0000ff]New[/COLOR][/SIZE][SIZE=2] SqlConnection("data source=ROBOT;user id=vps200;pwd=;Initial Catalog=OnlineVRF;persist security info=False")
connectionVrf.Open()
[/SIZE][SIZE=2][COLOR=#008000]'Delete record from tblaccount
[/COLOR][/SIZE][SIZE=2]deleteAccountString = "delete from tblAccount where AccountID = @AccountID"
deleteAccountVrf = [/SIZE][SIZE=2][COLOR=#0000ff]New[/COLOR][/SIZE][SIZE=2] SqlCommand(deleteAccountString, connectionVrf)
deleteAccountVrf.Parameters.Add("@AccountID", tbAccountid.Text)
transactionDelete = connectionVrf.BeginTransaction
deleteAccountVrf.Transaction = transactionDelete
[/SIZE][SIZE=2][COLOR=#008000]'Try Update - if successful delete & report / if failed rollback and report faliure 
[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]Try
[/COLOR][/SIZE][SIZE=2]transactionDelete.Commit()
dataReaderVrf = deleteAccountVrf.ExecuteReader()
dataReaderVrf.RecordsAffected.ToString(tbRecordsUpdated.Text)
dataReaderVrf.Close()
intcountDelete = tbRecordsUpdated.Text
tbStatusResults.Text = "Congratulations " + intcountDelete + " Record(s) Deleted Successfully"
[/SIZE][SIZE=2][COLOR=#0000ff]Catch[/COLOR][/SIZE][SIZE=2] ex [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] Exception
transactionDelete.Rollback()
dataReaderVrf = deleteAccountVrf.ExecuteReader()
dataReaderVrf.RecordsAffected.ToString(tbRecordsUpdated.Text)
dataReaderVrf.Close()
intcountDelete = tbRecordsUpdated.Text
tbStatusResults.Text = "Error " + intcountDelete + " Record(s) Deleted"
[/SIZE][SIZE=2][COLOR=#0000ff]End[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]Try
[/COLOR][/SIZE][SIZE=2][COLOR=#008000]'Close VRF database Connection
[/COLOR][/SIZE][SIZE=2]connectionVrf.Close()
[/SIZE][SIZE=2][COLOR=#0000ff]End[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]Sub
[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]Private[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]Sub[/COLOR][/SIZE][SIZE=2] btClear_Click([/SIZE][SIZE=2][COLOR=#0000ff]ByVal[/COLOR][/SIZE][SIZE=2] sender [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] System.Object, [/SIZE][SIZE=2][COLOR=#0000ff]ByVal[/COLOR][/SIZE][SIZE=2] e [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] System.EventArgs) [/SIZE][SIZE=2][COLOR=#0000ff]Handles[/COLOR][/SIZE][SIZE=2] btClear.Click
[/SIZE][SIZE=2][COLOR=#008000]'Clear entire vrfwebform
[/COLOR][/SIZE][SIZE=2]tbEmail.Text = ""
tbPassword.Text = ""
tbReminder.Text = ""
tbConame.Text = ""
tbConum.Text = ""
tbVatnum.Text = ""
tbVendor.Text = ""
tbWebsite.Text = ""
tbStatusResults.Text = ""
tbRecordsUpdated.Text = ""
 
[/SIZE][SIZE=2][COLOR=#0000ff]End[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]Sub
[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]Private[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]Sub[/COLOR][/SIZE][SIZE=2] btSave_Click([/SIZE][SIZE=2][COLOR=#0000ff]ByVal[/COLOR][/SIZE][SIZE=2] sender [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] System.Object, [/SIZE][SIZE=2][COLOR=#0000ff]ByVal[/COLOR][/SIZE][SIZE=2] e [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] System.EventArgs) [/SIZE][SIZE=2][COLOR=#0000ff]Handles[/COLOR][/SIZE][SIZE=2] btSave.Click
[/SIZE][SIZE=2][COLOR=#008000]' Save updates
[/COLOR][/SIZE][SIZE=2]adapterVrf.Update(dataTableVrf.Select([/SIZE][SIZE=2][COLOR=#0000ff]Nothing[/COLOR][/SIZE][SIZE=2], [/SIZE][SIZE=2][COLOR=#0000ff]Nothing[/COLOR][/SIZE][SIZE=2], DataViewRowState.ModifiedCurrent))
[/SIZE][SIZE=2][COLOR=#008000]' Save inserts
[/COLOR][/SIZE][SIZE=2]adapterVrf.Update(dataTableVrf.Select([/SIZE][SIZE=2][COLOR=#0000ff]Nothing[/COLOR][/SIZE][SIZE=2], [/SIZE][SIZE=2][COLOR=#0000ff]Nothing[/COLOR][/SIZE][SIZE=2], DataViewRowState.Added))
 
[/SIZE][SIZE=2][COLOR=#0000ff]End[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]Sub
End[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]Class[/COLOR][/SIZE]

Any help on this one would be greatly appreicated

Many thanks

James

 

[jmcilhinney]

You can't call ExecuteReader on a Command object unless you have assigned a String containing a valid SQL query to its CommandText property. This also means that you cannot call Fill on a DataAdapter without doing the same for its SelectCommand, as it will implicitly call ExecuteReader. I'm not reading all that code to find where you've made the mistake. You really should avoid posting such large chunks of code. You will actually discourage people from helping you. Also, please enclose posted code in

 tags to make it more readable.  If you need more information then please read the FAQ.

 

[vis781]

There is just too much code here for anyone to sort through. Please select only the parts of the code that are giving you the trouble and only post that. The part that i've highlighted in red could be giving you the trouble...

Dim queryCommandVrf As SqlCommand = adapterVrf.SelectCommand()

I think that this should be

adapterVrf.selectcommand = queryCommandVrf

then place this code after you have set the commandtext property.