Help needed returning variable values.

[Aaron]

Complete code follows.

I am new to .NET programming (and programming in general) and I am having a
difficult time understanding how to fill a variable in one sub, and then
access it from another. I have tried declaring them as shared, public,
friend, etc and I always get an error stating that something is not valid
on a local variable declaration.

For example, in the following code for Sub DataGrid_Select, I have
CurrentID and CurrentActID being populated with the values of a specific
datagrid cell. Later, in the Button2_Click event, I need to get the value
of those variables to create a SQL update command, but I cannot find the...
namespace, I guess is what I need, for that variable.

In a similar situation, you can see that I am rewriting my SQL connection
code for every routine that needs to access the DB. I created Function
DBConnect, and would like to call that in place of where I am writing the
full code, but I am not able to retuen the sqlcon object to the calling
sub. What am I doing wrong?

Can anyone explain the correct way of doing these things?



Public Class WebForm1
Inherits System.Web.UI.Page
Protected WithEvents DropDownList1 As
System.Web.UI.WebControls.DropDownList
Protected WithEvents Button1 As System.Web.UI.WebControls.Button
Protected WithEvents Label1 As System.Web.UI.WebControls.Label
Protected WithEvents Label2 As System.Web.UI.WebControls.Label
Protected WithEvents Label3 As System.Web.UI.WebControls.Label
Protected WithEvents Label4 As System.Web.UI.WebControls.Label
Protected WithEvents Label5 As System.Web.UI.WebControls.Label
Protected WithEvents Label6 As System.Web.UI.WebControls.Label
Protected WithEvents Label7 As System.Web.UI.WebControls.Label
Protected WithEvents Label8 As System.Web.UI.WebControls.Label
Protected WithEvents Label9 As System.Web.UI.WebControls.Label
Protected WithEvents Label10 As System.Web.UI.WebControls.Label
Protected WithEvents Button2 As System.Web.UI.WebControls.Button
Protected WithEvents Label11 As System.Web.UI.WebControls.Label
Protected WithEvents Label12 As System.Web.UI.WebControls.Label
Protected WithEvents CheckBoxList1 As
System.Web.UI.WebControls.CheckBoxList
Protected WithEvents txtFullname As System.Web.UI.WebControls.TextBox
Protected WithEvents txtCompany As System.Web.UI.WebControls.TextBox
Protected WithEvents txtTitle As System.Web.UI.WebControls.TextBox
Protected WithEvents txtConfNotes As System.Web.UI.WebControls.TextBox
Protected WithEvents txtActDate As System.Web.UI.WebControls.TextBox
Protected WithEvents txtFollowUpDate As
System.Web.UI.WebControls.TextBox
Protected WithEvents txtBPhone As System.Web.UI.WebControls.TextBox
Protected WithEvents txtWorkExt As System.Web.UI.WebControls.TextBox
Protected WithEvents Label13 As System.Web.UI.WebControls.Label
Protected WithEvents txtSearchDate As System.Web.UI.WebControls.TextBox
Protected WithEvents Label14 As System.Web.UI.WebControls.Label
Protected WithEvents DataGrid1 As System.Web.UI.WebControls.DataGrid
Protected WithEvents Label15 As System.Web.UI.WebControls.Label
Protected WithEvents DataGrid2 As System.Web.UI.WebControls.DataGrid
Protected WithEvents txtClientCell As System.Web.UI.WebControls.TextBox
Protected WithEvents Button3 As System.Web.UI.WebControls.Button
Protected WithEvents Button4 As System.Web.UI.WebControls.Button
Protected WithEvents Label16 As System.Web.UI.WebControls.Label
Protected WithEvents Label17 As System.Web.UI.WebControls.Label
Protected WithEvents Label18 As System.Web.UI.WebControls.Label
Protected WithEvents Label19 As System.Web.UI.WebControls.Label
Protected WithEvents txtNotes As System.Web.UI.WebControls.TextBox

Dim CurrentActivityID As String
Dim CurrentID As String



#Region " Web Form Designer Generated Code "


<System.Diagnostics.DebuggerStepThrough()> Private Sub
InitializeComponent()

End Sub

Private Sub Page_Init(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles MyBase.Init
'CODEGEN: This method call is required by the Web Form Designer
'Do not modify it using the code editor.
InitializeComponent()
End Sub

#End Region



#Region " Developer Generated Code "

Function DBConnect()
Dim sqlcon As New SqlClient.SqlConnection() ' Dim new SQL
connection object
sqlcon.ConnectionString = "Server=Boca2;User
ID=ID;password=PASS;Database=Pursuittest" ' Connection string
sqlcon.Open() ' Open a connection to the DB
Return sqlcon
End Function

Sub Page_Load(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles MyBase.Load
Utilities.CreateConfirmBox(Me.Button2, _
"Are you sure you want to update the record?")
End Sub

Sub UpdateLabels()
'Shows page count for DG paging events
Label14.Text = "Displaying page " & (DataGrid1.CurrentPageIndex +
1).ToString() & " of " & DataGrid1.PageCount
End Sub

Sub DrawData()
Dim ColumnNames ' Used to define selected columns
Dim OrderClause ' Used to specify ordering
Dim WhereClause ' Used to specify Database for query and concat.
first part of SQL statement
Dim Criteria ' Used to define query criteria
Dim UserName ' Used for determining user. Not Implemented.
Dim sqlcmd0 As New SqlClient.SqlCommand() ' Dim new SQL command
object
'Dim sqlcon As New SqlClient.SqlConnection() ' Dim new SQL
connection object

'DBConnect()
Dim sqlcon As New SqlClient.SqlConnection() ' Dim new SQL
connection object
sqlcon.ConnectionString = "Server=Boca2;User
ID=ID;password=PASS;Database=Pursuittest" ' Connection string
sqlcon.Open() ' Open a connection to the DB

Dim ds As New DataSet() ' Dim new Dataset
Dim da As New SqlClient.SqlDataAdapter() ' Dim new SQL data adapter
object
'sqlcon.ConnectionString = "Server=Boca2;User
ID=sa;password=fdstemers;Database=Pursuittest" ' Connection string
sqlcmd0.Connection = sqlcon ' Telling the SQL Command object which
connection to use
'sqlcon.Open() ' Open a connection to the DB
ColumnNames = "Client_Name_Last as Last, Client_Name_First as
First, Client_Name_Full as FullName, Client_Title_Long as Title,
Client_Title_CD as TitleCode, Client_Company as Company, Activity_Agent as
Agent, Activity_Date as ActivityDate, Activity_FollowUp as FollowUpDate,
Activity_Notes as Notes, Activity_Notes_Confidential as Confidential,
Client_BEmail as BEmail, Client_BPhone, Client_BPhone_Ext, Client_Cell,
Client_Person_ID, Activity_ID" 'Use these column names for select query
WhereClause = "SELECT " & ColumnNames & " FROM Searches_Activity
WHERE " ' Building the first part of the SQL query
Criteria = "(Activity_Activity_CD = 2) and (Activity_Date >='" &
txtSearchDate.Text() & "') and (Activity_agent='" &
DropDownList1.SelectedItem.Text & "')" ' SQL Select criteria
Criteria = Mid(Criteria, 1, Len(Criteria) - 1) & ")" ' Not sure
why, but this trims the last character from CRITERIA and adds a right
parenthesis
OrderClause = " ORDER BY Activity_FollowUp DESC" ' Ordering the
recordset
sqlcmd0.CommandText = WhereClause & Criteria & OrderClause '
Telling the SQL Command Object what to use for the command text by
concatonating WHERECLAUSE, CRITERIA, and, ORDERCLAUSE
da.SelectCommand = sqlcmd0 ' Telling the SQL data adaper object
which command to use
da.Fill(ds, "SA") ' Fill the DS dataset and call that table SA
sqlcon.Close() ' Close the SQL Connection
DataGrid1.DataSource = ds 'Define which dataset to be used by
datagrid1
DataGrid1.DataKeyField = "Client_Person_ID" ' Define a primary key
for the Dataset
DataGrid1.DataBind() 'Bind the datagrid to it's datasource
Label13.Text = ds.Tables("SA").Rows.Count.ToString & " Records
Found" ' Fill the label with the number of records in the data set DS
End Sub

Sub FillTextBoxes(ByVal e As DataGridCommandEventArgs)

If e.Item.Cells(1).Text.Equals("&nbsp;") Then
txtFullname.Text = ""
Else
txtFullname.Text = e.Item.Cells(1).Text
End If
If e.Item.Cells(2).Text.Equals("&nbsp;") Then
txtTitle.Text = ""
Else
txtTitle.Text = e.Item.Cells(2).Text
End If
If e.Item.Cells(3).Text.Equals("&nbsp;") Then
txtCompany.Text = ""
Else
txtCompany.Text = e.Item.Cells(3).Text
End If
If e.Item.Cells(4).Text.Equals("&nbsp") Then
txtActDate.Text = ""
Else
txtActDate.Text = e.Item.Cells(4).Text
End If
If e.Item.Cells(5).Text.Equals("&nbsp;") Then
txtFollowUpDate.Text = ""
Else
txtFollowUpDate.Text = e.Item.Cells(5).Text
End If
If e.Item.Cells(6).Text.Equals("&nbsp;") Then
txtBPhone.Text = ""
Else
txtBPhone.Text = e.Item.Cells(6).Text
End If
If e.Item.Cells(7).Text.Equals("&nbsp;") Then
txtWorkExt.Text = ""
Else
txtWorkExt.Text = e.Item.Cells(7).Text
End If
If e.Item.Cells(8).Text.Equals("&nbsp;") Then
txtClientCell.Text = ""
Else
txtClientCell.Text = e.Item.Cells(8).Text
End If
If e.Item.Cells(9).Text.Equals("&nbsp;") Then
txtNotes.Text = ""
Else
txtNotes.Text = e.Item.Cells(9).Text
End If
If e.Item.Cells(10).Text.Equals("&nbsp;") Then
txtConfNotes.Text = ""
Else
txtConfNotes.Text = e.Item.Cells(10).Text
End If
End Sub

Sub ClearTextBoxes()
'Clears text from textboxes
txtFullname.Text() = ""
txtTitle.Text() = ""
txtCompany.Text() = ""
txtNotes.Text() = ""
txtConfNotes.Text() = ""
txtActDate.Text() = ""
txtFollowUpDate.Text() = ""
txtBPhone.Text() = ""
txtWorkExt.Text() = ""
txtClientCell.Text() = ""
End Sub

Sub DataGrid1_Paging(ByVal sender As Object, ByVal e As
DataGridPageChangedEventArgs)
DataGrid1.CurrentPageIndex = e.NewPageIndex ' tell the datagrid
that we are on a new page
DrawData() ' Call Drawdata to fill the dataset and bind the
datagrid
UpdateLabels() ' call to update labels to show record and page
counts
ClearTextBoxes()
DataGrid1.SelectedIndex = -1 ' Tell the datagrid that nothing is
selected
End Sub

Sub DataGrid_Select(ByVal sender As Object, ByVal e As
DataGridCommandEventArgs)
Dim CurrentID = e.Item.Cells(11).Text.ToString
Dim CurrentActivityID = e.Item.Cells(12).Text.ToString
Dim sqlcmd0 As New SqlClient.SqlCommand()
Dim sqlcon As New SqlClient.SqlConnection()
Dim ds2 As New DataSet()
Dim da2 As New SqlClient.SqlDataAdapter()
sqlcon.ConnectionString = "Server=Boca2;User
ID=ID;password=PASS;Database=Pursuittest"
sqlcmd0.Connection = sqlcon
sqlcon.Open()
sqlcmd0.CommandText = "select BDCallsMade as 'Calls Made',
BDEmailsSent as 'Emails Sent', activity_ID, company.company as 'Company
Name', date as 'Activity Date', followup as 'Follow Up Date', notes as
Notes, notes_confidential as Confidential from activity Activity INNER JOIN
Company ON Activity.Client_Company_ID = Company.Company_ID where
(activity_cd = 2 and client_id =" & CurrentID & ") order by date Desc"
da2.SelectCommand = sqlcmd0
da2.Fill(ds2, "Person")

sqlcon.Close()
DataGrid2.DataSource = ds2
'DataGrid2.DataBind()
FillTextBoxes(e)
'DataGrid2.Visible = True
Label18.Visible = True
Label18.Text = ds2.Tables("Person").Rows(0).Item(0).ToString
Label19.Visible = True
Label19.Text = ds2.Tables("Person").Rows(0).Item(1).ToString
Button3.Visible = True
Button4.Visible = True
Label16.Visible = True
Label17.Visible = True
Button3.Enabled = True
Button4.Enabled = True
'DataGrid1.Visible = False
End Sub

Sub Button1_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles Button1.Click

ClearTextBoxes() 'Clear Data from textboxes

DrawData() 'Query for data

DataGrid1.CurrentPageIndex = 0 ' Returns DG to page 0

UpdateLabels() ' call to update labels to show record and page
counts

'Scorecard buttons and labels are hidden when a query is issued
'-->
Button3.Enabled = True
Button4.Enabled = True
Label16.Visible = False
Label17.Visible = False
Label18.Visible = False
Label19.Visible = False
Button3.Visible = False
Button4.Visible = False
'<--

'DataGrid1.Visible = True '>> Used to show second DG with activity
history
'DataGrid2.Visible = False '>> Used to show second DG with activity
history

End Sub

Sub Button2_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles Button2.Click
Button3.Enabled = True
Button4.Enabled = True
Label16.Visible = False
Label17.Visible = False
Label18.Visible = False
Label19.Visible = False
Button3.Visible = False
Button4.Visible = False
ClearTextBoxes()
'DataGrid2.Visible = False
'DataGrid1.Visible = True


End Sub

Sub Button3_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles Button3.Click
Label18.Text = Label18.Text + 1
Button3.Enabled = False
End Sub

Sub Button4_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles Button4.Click
Label19.Text = Label19.Text + 1
Button4.Enabled = False

End Sub





#End Region


End Class


Public Class Utilities


#Region " Utility "
Public Shared Sub CreateConfirmBox(ByRef btn As WebControls.Button, _
ByVal strMessage As String)
btn.Attributes.Add("onclick", "return confirm('" & strMessage &
"');")
End Sub


#End Region


End Class

 

[rdi]

There are a couple different ways to access variables globally.

First: I'll assume for a minute that you're creating a windows form type
program--when you create your form in VS.Net, the whole thing is a class
with a collection of subs, functions and varialbles.

Public Class Form1
public myVar1 as integer
private sub mySub1()
myVar1 = 1
end sub
private sub mySub2()
dim mv as integer
mv = myVar1
msgbox(mv.tostring)
end sub
end Class

Second: Functions return variables while subs do NOT.
public class form1
private function myFunc() as integer
dim myVar1 as integer
myVar1 = 10
return myVar1
end function
private sub()
dim mv as integer
mv = myFunc()
msgbox(mv.tostring)
end sub
end class

Lastly, functions and subs can either accept arguments or not accept
arguments, that's the programmer's choice. I could have defined ANY of
these functions, subs and/or variables as public, private or whatever else I
needed based on what I needed them to do.

HTH.

 

[Aaron]

Thank you for the insight. I'm actually developing an asp.net web form
application. Do variables act any differently in this environment?


Aaron

 

[Cor Ligthert]

Thank you for the insight. I'm actually developing an asp.net web form

application. Do variables act any differently in this environment?

As far as I know is the only real difference that they have normally, even
when they are made public on the form, have no longer livetime than the page
and that therefore when the page is sended every thing is gone.

I hope this helps?

Cr

 

[Don I]

Aaron,

Sorry about that--I didn't have time to actually read through your
code. Otherwise I would've seen that you weren't making a windows
form. As it was, I only read that you wanted to know how to use
variables throughout your program. And responded to that request.

I don't know anything about ASP.net but I would have to believe that
it's the same.

 

[Cor Ligthert]

Hi Don,

I did not look to what you wrote to Aaron when I answered his last question,
however I did it now and could not find anything that is different in your
samples on a windowform or a webform.

I hope this helps.

Cor

 

[Aaron]

OK, I tried the code, but I am still getting "Nothing" as the value of my
variable.

Here is what I am doing:

Outside of any event handler I have the following lines:

Public CurrentID As String
Public CurrentActivityID As String

Then I have a Datagrid select event. You can see that I am loading these
variables with the contents of a DG cell.

Sub DataGrid_Select(ByVal sender As Object, ByVal e As
DataGridCommandEventArgs)
CurrentID = e.Item.Cells(11).Text.ToString
CurrentActivityID = e.Item.Cells(12).Text.ToString
Dim sqlcmd0 As New SqlClient.SqlCommand()
Dim sqlcon = DBConnect() ' Dim new SQL connection object
Dim ds2 As New DataSet()
Dim da2 As New SqlClient.SqlDataAdapter()
sqlcmd0.Connection = sqlcon
sqlcmd0.CommandText = "select BDCallsMade as 'Calls Made',
BDEmailsSent as 'Emails Sent', activity_ID as ActID, company.company as
'Company Name', date as 'Activity Date', followup as 'Follow Up Date',
notes as Notes, notes_confidential as Confidential from activity Activity
INNER JOIN Company ON Activity.Client_Company_ID = Company.Company_ID where
(activity_cd = 2 and client_id =" & CurrentID & ") order by date Desc"
da2.SelectCommand = sqlcmd0
da2.Fill(ds2, "Person")
sqlcon.Close()
DataGrid2.DataSource = ds2
DataGrid2.DataBind()
FillTextBoxes(e)
DataGrid2.Visible = True
Label18.Visible = True
Label18.Text = ds2.Tables("Person").Rows(0).Item(0).ToString
Label19.Visible = True
Label19.Text = ds2.Tables("Person").Rows(0).Item(1).ToString
Button3.Visible = True
Button4.Visible = True
Label16.Visible = True
Label17.Visible = True
Button3.Enabled = True
Button4.Enabled = True
DataGrid1.Visible = False
End Sub

And finally, I have a button click event which will update the database.
This is where I need the values of those variables.

Sub UpdateRecord()
Dim cid As String
cid = CurrentID
Dim sqlcmd0 As New SqlClient.SqlCommand()
Dim sqlcon = DBConnect()
Dim ds2 As New DataSet()
Dim da2 As New SqlClient.SqlDataAdapter()
sqlcmd0.Connection = sqlcon
sqlcmd0.CommandText = "Update person set name_full = @NameFull
where person_ID =" & cid
End Sub



I am trying to replicate what you showed me as:

Public Class Form1
public myVar1 as integer ' This is outside of an event handler
private sub mySub1() ' My sub is called Datagrid_Select
myVar1 = 1 'CurrentID = e.Item.Cells(11).Text.ToString
end sub

private sub mySub2() ' My sub is called UpdateRecord
dim mv as integer ' I dimmed cid
mv = myVar1 ' I set cid = CurrentID
msgbox(mv.tostring) ' My code calls for commandtext
end sub
end Class

This is where the variable is "Nothing"

Any ideas where I am going wrong?

Thanks for your help,
Aaron

 

[Cor Ligthert]

Hi Aaron,

In this resource kit are a lot of samples, I think that it is good to have a
look for that
,
The problems in your program are in my opinon not on one place and you did
make it direct a little bit complex. Therefore it is in my idea almost
impossible to help you from a newsgroup.

Have a look at those samples for ASP. There is also an asp.net resource kit,
however I think the VB.net one is better. I do the link from the ASP.net
one also beneath these.

http://msdn.microsoft.com/vbasic/vbrkit/default.aspx

And if you have problems installing it

http://msdn.microsoft.com/vbasic/vbrkit/faq/#installvdir

http://msdn.microsoft.com/asp.net/asprk/

I hope this helps a little bit?

Cor