ASP.NET

25-Apr-07 1:51

I read a boolean value from a table with this:

Public SQLconn As New SqlConnection(ConfigurationManager.ConnectionStrings("TestConnectionString").ToString())

Sub Page_Load(ByVal Sender As Object, ByVal e As System.EventArgs) Handles MyBase.Load
SQLconn.Open()

Dim SelectCmd1 As New SqlCommand("SELECT value1 from table1", SQLconn)
SelectCmd1.CommandType = CommandType.StoredProcedure
Dim result1 As Boolean
Dim myDataReader As SqlDataReader
myDataReader = SelectCmd.ExecuteReader()
myDataReader.Read()
result = myDataReader1.GetBoolean(0)

SQLconn.Close()

ChangeButton1(result1)
End Sub

Is it possible to read values from two tables or more with the same method and if not what can I do?

I tried this:

Dim SelectCmd1 As New SqlCommand("SELECT value1.table1, value2.table2 FROM table1, table2", SQLconn)
SelectCmd1.CommandType = CommandType.StoredProcedure
Dim result1 As Boolean
[b]Dim result2 As Boolean[/b]
Dim myDataReader As SqlDataReader
myDataReader = SelectCmd1.ExecuteReader()
myDataReader.Read()
result1 = myDataReader.GetBoolean(0)
[b]result2 = myDataReader.GetBoolean(1)[/b]

SQLconn.Close()

ChangeButton(result1, result2)
End Sub

Could this work????

Any help will be much appreciated!!!

kubben25-Apr-07 2:01

kubben

25-Apr-07 2:01

If you are using .net 2.0 then ado supports MARS (multiple result sets). Here is some sample code from MS help:

' Assumes that connection is a valid SqlConnection object.
Dim command As SqlCommand = New SqlCommand( _
  "SELECT CategoryID, CategoryName FROM dbo.Categories;" & _
  "SELECT EmployeeID, LastName FROM dbo.Employees", connection)
connection.Open()

Dim reader As SqlDataReader = command.ExecuteReader()

Dim nextResult As Boolean = True
Do Until Not nextResult
  Console.WriteLine(vbTab & reader.GetName(0) & vbTab & reader.GetName(1))

  Do While reader.Read()
    Console.WriteLine(vbTab & reader.GetInt32(0) & vbTab & reader.GetString(1))
  Loop

  nextResult = reader.NextResult()
Loop

reader.Close()
connection.Close()

Hope that helps.
Ben

Chetan Ranpariya25-Apr-07 2:14

25-Apr-07 2:14

hi,

This will not work.
The qurey provided by you will return table1.rowcount * table2.rowcount number of recored.

Can you please specify why you want data from both table and what is the actual requirment?

Thanks and Regards,
Chetan Ranpariya

kallileo25-Apr-07 2:45

25-Apr-07 2:45

@kubben I will check it

@Chetan

I have a hardware device that is connected to a sql database. I also have 7 switches connected to this device. So every time I toggle the boolean value(press button event in asp.net page) I turn on or off the corresponding switch. I use Page_load to initialize the button controls. Every switch is connected to a table with on one record that I update from the page.

The whole code for "one" button looks like this:

Public SQLconn As New SqlConnection(ConfigurationManager.ConnectionStrings("TestConnectionString").ToString())
Sub Page_Load(ByVal Sender As Object, ByVal e As System.EventArgs) Handles MyBase.Load
SQLconn.Open()
Dim SelectCmd1 As New SqlCommand("SELECT rawvalue FROM switch1", SQLconn)
SelectCmd1.CommandType = CommandType.StoredProcedure
Dim result1 As Boolean
Dim myDataReader1 As SqlDataReader
myDataReader1 = SelectCmd1.ExecuteReader()
myDataReader1.Read()
result1 = myDataReader1.GetBoolean(0)

SQLconn.Close()
ChangeButton1(result1)
End Sub

Sub ChangeButton1(ByVal Status As Boolean)

If Status Then
Label1.Text = "ON"
OnOff1.Style.Value = "background-image: url(../ImgContr/1.png);"
Else
Label1.Text = "OFF"
OnOff1.Style.Value = "background-image: url(../ImgContr/2.png);"
End If
End Sub

Sub OnOff_Click1(ByVal Sender As Object, ByVal e As System.EventArgs)

Dim updateCMD As SqlCommand
Dim query As String

If Label1.Text = "ON" Then
query = "UPDATE [switch1] SET [rawvalue] = 'False' WHERE [device] = 'channel1'"
ChangeButton1(0)
Else
query = "UPDATE [switch1] SET [rawvalue] = 'True' WHERE [device] = 'channel1'"
ChangeButton1(1)
End If

updateCMD = New SqlCommand(query, SQLconn)
updateCMD.CommandType = CommandType.Text
SQLconn.Open()
updateCMD.ExecuteNonQuery()
SQLconn.Close()

End Sub

It works for 7 switches too but I have to write every SUB 7 times.

And the initialization looks like this:

Sub Page_Load(ByVal Sender As Object, ByVal e As System.EventArgs) Handles MyBase.Load
SQLconn.Open()
Dim SelectCmd1 As New SqlCommand("prodiakoptis1", SQLconn)
SelectCmd1.CommandType = CommandType.StoredProcedure
Dim result1 As Boolean
Dim myDataReader1 As SqlDataReader
myDataReader1 = SelectCmd1.ExecuteReader()
myDataReader1.Read()
result1 = myDataReader1.GetBoolean(0)

SQLconn.Close()

SQLconn.Open()
Dim SelectCmd2 As New SqlCommand("prodiakoptis2", SQLconn)
SelectCmd2.CommandType = CommandType.StoredProcedure
Dim result2 As Boolean
Dim myDataReader2 As SqlDataReader
myDataReader2 = SelectCmd2.ExecuteReader()
myDataReader2.Read()
result2 = myDataReader2.GetBoolean(0)

SQLconn.Close()

Chetan Ranpariya25-Apr-07 23:12

25-Apr-07 23:12

Hi,

First of all you dont need to open and close the connection everytime you are calling new stored procedure.

You can create only one stored procedure which will return all the values u need in the form of output parameters. Remeber this is advisable only if your number of switches is not going to change in future.

You must be familier with how to create and execute stored procedure with output parameters.

Thanks and Regards,
Chetan Ranpariya

kallileo25-Apr-07 23:35

25-Apr-07 23:35

I actualy created a SP to retrieve the data from the tables.

ALTER PROCEDURE dbo.switch1
/*
(
@parameter1 int = 5,
@parameter2 datatype OUTPUT
)
*/
AS
SELECT [rawvalue] FROM [switch1]
/* SET NOCOUNT ON */
RETURN

I have one SP for every table.
Even if I create a SP what I don't know is how to read all this values with the datareader.

Chetan Ranpariya25-Apr-07 23:45

25-Apr-07 23:45

Hi,

Is your current procedure is returning more than one rows/values?

Thanks and Regards,
Chetan Ranpariya

kallileo26-Apr-07 0:03

26-Apr-07 0:03

No the table has only one row and the procedure returns only one boolean value.

Thanks
Leonidas Kallis

Chetan Ranpariya26-Apr-07 0:08

26-Apr-07 0:08

Hi,

Then it is not necessary to use reader.
You can create a procedure that will return the values u need and then you those values as per your requirement.
In the procedure you can write selecte statements for all the tables and the assing the values to the output parameters.

By this way you will not need to write the same code again and again and only one procedure will do all your work.

Thanks and Regards,
Chetan Ranpariya

Chetan Ranpariya26-Apr-07 0:34

26-Apr-07 0:34

Hi,

I hope the following solution will help you.

You create a common event handler for the command event of all your switchbuttons.

public void bttns_command(object sender, CommandEventargs e)
{
if(e.CommandName == "Switch1")
{
//do work related to switch1;
if(Label1.Text == "ON")
{
query = "Update ..........."
ChangeButton("Switch1",0);
}
else
{
query = "update............."
ChangeButton*("Switch1",1);
}
updateCMD = New SqlCommand(query, SQLconn)
updateCMD.CommandType = CommandType.Text
SQLconn.Open()
updateCMD.ExecuteNonQuery()
SQLconn.Close()
}
else if(e.CommandName == "Switch2")
{
if(Label2.Text == "ON")
{
query = "Update ..........."
ChangeButton("Switch2",0);
}
else
{
query = "update............."
ChangeButton*("Switch2",1);
}
updateCMD = New SqlCommand(query, SQLconn)
updateCMD.CommandType = CommandType.Text
SQLconn.Open()
updateCMD.ExecuteNonQuery()
SQLconn.Close()
}
else if(e.CommandName == "Switch3")
{
if(Label3.Text == "ON")
{
query = "Update ..........."
ChangeButton("Switch3",0);
}
else
{
query = "update............."
ChangeButton*("Switch3",1);
}
updateCMD = New SqlCommand(query, SQLconn)
updateCMD.CommandType = CommandType.Text
SQLconn.Open()
updateCMD.ExecuteNonQuery()
SQLconn.Close()
}
}

The html for your buttons should look like following.

<asp:Button id="bttnSwitch1" Runat="server" CommandName = "Switch1"
OnCommand="bttns_command"></asp:Button>
<asp:Button id="bttnSwitch2" Runat="server" CommandName = "Switch2"
OnCommand="bttns_command"></asp:Button>
<asp:Button id="bttnSwitch3" Runat="server" CommandName = "Switch3"
OnCommand="bttns_command"></asp:Button>

Now you ChangButton function shuld look like following:
private void ChangeButton(string switchname, bool status)
{
if(switchname == "Switch1")
{
if(status)
{
Label1.Text = "ON";
OnOff1.Style.Value = "background-image: url(../ImgContr/1.png);"
}
else
{
Label1.Text = "OFF"
OnOff1.Style.Value = "background-image: url(../ImgContr/2.png);"
}
}
else if(switchname == "Switch2")
{
if(status)
{
Label2.Text = "ON";
OnOff2.Style.Value = "background-image: url(../ImgContr/1.png);"
}
else
{
Label2.Text = "OFF"
OnOff2.Style.Value = "background-image: url(../ImgContr/2.png);"
}
}
else if(switchname == "Switch3")
{
if(status)
{
Label3.Text = "ON";
OnOff3.Style.Value = "background-image: url(../ImgContr/1.png);"
}
else
{
Label3.Text = "OFF"
OnOff3.Style.Value = "background-image: url(../ImgContr/2.png);"
}
}
}

I hope this will help you.

Thanks and Regards,
Chetan Ranpariya1

kallileo26-Apr-07 0:39

Re: Web Parts Very Cool ???

26-Apr-07 0:39

Thank you very much...
I will study the above code and see what I can do...

javascript Function

siddisagar25-Apr-07 1:14

siddisagar

25-Apr-07 1:14

Hi,

My form contains only Textbox and Button.I have a string variable in cs file and HTML TextField in aspx page. Str I have a value i am placing Request.Querystring().So I need javascript validation like this

user Should not Enter the value that String value contains.Pls tell me syntax in javascript.

Thanks

Re: javascript Function

Christian Graus25-Apr-07 2:07

Christian Graus

25-Apr-07 2:07

You would do better to render the value as a variable in javascript. Then your textbox should contain an onblur that checks if it's text equals this variable.

Christian Graus - Microsoft MVP - C++
Metal Musings - Rex and my new metal blog
"I am working on a project that will convert a FORTRAN code to corresponding C++ code.I am not aware of FORTRAN syntax" ( spotted in the C++/CLI forum )

Re: javascript Function

gus_br25-Apr-07 2:55

gus_br

25-Apr-07 2:55

Sure,

and just to complement Graus sugestion, you can get that variable in your URL using a Javascript QueryString method (search for it) in the onload event of your page`s body.

Web Parts Very Cool ???

Rahul_asp.net25-Apr-07 1:01

Rahul_asp.net

25-Apr-07 1:01

How can we use web parts as in like >> have a look at this site
http://www.pageflakes.com (asp.net 2.0 )

szukuro25-Apr-07 1:13

szukuro

25-Apr-07 1:13

An article from the CTO of the site you mentioned:
http://www.codeproject.com/Ajax/MakingGoogleIG.asp[^]

DataGrid Sorting Problem

sonia_basangar25-Apr-07 0:54

sonia_basangar

25-Apr-07 0:54

Hi
ALL of you.
i use view state for sorting code is given but problem
is that it sorts perfectly on name but not on date ..
What to do????

actually i chage the date into varchar in order to display in drid
For Ex it shows 01 Feb 2007 in Grid but when i click on header it
dont sort in correct way..

<br />
<br />
Bind();<br />
oColumnCollection=dgHistory.Columns;<br />
if(ViewState["SortOrder"].ToString() == "" || ViewState["SortOrder"].ToString() == "DESC")<br />
{<br />
dv.Sort = e.SortExpression;<br />
					//	oCol.HeaderText = oCol.HeaderText + " (ASC)";<br />
					<br />
ViewState["SortExpression"]= e.SortExpression + "ASC";<br />
ViewState["SortOrder"] = "ASC";<br />
				}<br />
else<br />
{        <br />
dv.Sort = e.SortExpression + " DESC";<br />
//	oCol.HeaderText = oCol.HeaderText + " (DESC)";<br />
					<br />
ViewState["SortExpression"]= e.SortExpression + " DESC";<br />
ViewState["SortOrder"] = "DESC";<br />
	}<br />
		<br />
dgHistory.DataBind();<br />

Thanks

Re: DataGrid Sorting Problem

RSArockiam25-Apr-07 1:35

RSArockiam

25-Apr-07 1:35

For date sorting, you have to use some different logic, especially date sorting is the best one. But, here you are using just string sorting, so you won't get correct result.

So, use date field in the dataview instead of changing into varchar (that is best way), and use format to display the date into the specified format. You can use the DataFormatString property in grid.

For example: for bound column,
....asp:BoundColumn DataField="FieldName" DataFormatString="{0:dd MMM yyyy}" .....

I hope, you can get the answer from this.

Regards
R.Arockiapathinathan

Re: DataGrid Sorting Problem

Chetan Ranpariya25-Apr-07 2:07