Putting SqlDataSource code in code-behind

Hi,

I need some help here. I have a SELECT sql statement that will query the table. How do I get the return value from the sql statement to be assigned to a label. Any article talk about this?

Thanks geniuses.

This link:

http://aspnet.4guysfromrolla.com/articles/022206-1.aspx

|||

Hi ahTan,

You may need to use a SqlDataAdapter to fill the result set into a DataSet. Here is an example.

SqlConnection cnn = new SqlConnection("your connection string here");
SqlDataAdapter sda = new SqlDataAdapter("SELECT * FROM Table1", cnn);
DataSet ds = new DataSet();
sda.Fill(ds);

sql

Putting calculated value into excel

I'm using VB to write some data to an Excel spreadsheet.
I use the following vb/sql statement to get the data I need:
ADO_rs.Source = "select least(2+(0.044117*CT_OFF_GEN),8)/count(*)as mydata2
calculate avg from """ + "6b_NRatio" + """.1 where ct_text like 'on/off
it produces the following correct output:
ct_off_time mydata2
-- --
05-MAY-03 18:57:57.7 1.97044
24-JAN-03 11:46:07.8 8
16-AUG-03 09:58:27.9 5.30701
......
16-AUG-03 21:22:57.7 6.09582
10-DEC-02 16:46:37.6 8
--
AVG 5.25773
If I could change the above code where I could store the "calculate avg as
mydata3" I could then put the data into excel.
But I can't do this
How can I store the AVG 5.25773 into a variable so I can use it in Excel
like so:
ADO_rs.Open
ActiveCell.Offset(x, 5).Value = ADO_rs.Fields!AVG GOES HERE
Maybe there is an easier way to do this' Any suggestions'
DavidHi
This seems to be very Excel specific SQL and therefore not really covered by
SQL server!
At a guess you will need to move the Average function into the select
columns
John
"Aster99" <Aster99@.home.net> wrote in message
news:%23igwDlOKEHA.204@.TK2MSFTNGP10.phx.gbl...
> I'm using VB to write some data to an Excel spreadsheet.
> I use the following vb/sql statement to get the data I need:
> ADO_rs.Source = "select least(2+(0.044117*CT_OFF_GEN),8)/count(*)as
mydata2
> calculate avg from """ + "6b_NRatio" + """.1 where ct_text like 'on/off
> it produces the following correct output:
> ct_off_time mydata2
> -- --
> 05-MAY-03 18:57:57.7 1.97044
> 24-JAN-03 11:46:07.8 8
> 16-AUG-03 09:58:27.9 5.30701
> ......
> 16-AUG-03 21:22:57.7 6.09582
> 10-DEC-02 16:46:37.6 8
> --
> AVG 5.25773
> If I could change the above code where I could store the "calculate avg as
> mydata3" I could then put the data into excel.
> But I can't do this
> How can I store the AVG 5.25773 into a variable so I can use it in Excel
> like so:
> ADO_rs.Open
> ActiveCell.Offset(x, 5).Value = ADO_rs.Fields!AVG GOES HERE
> Maybe there is an easier way to do this' Any suggestions'
> David
>sql

Putting calculated value into excel

I'm using VB to write some data to an Excel spreadsheet.
I use the following vb/sql statement to get the data I need:
ADO_rs.Source = "select least(2+(0.044117*CT_OFF_GEN),8)/count(*)as mydata2
calculate avg from """ + "6b_NRatio" + """.1 where ct_text like 'on/off
it produces the following correct output:
ct_off_time mydata2
-- --
05-MAY-03 18:57:57.7 1.97044
24-JAN-03 11:46:07.8 8
16-AUG-03 09:58:27.9 5.30701
......
16-AUG-03 21:22:57.7 6.09582
10-DEC-02 16:46:37.6 8
--
AVG 5.25773
If I could change the above code where I could store the "calculate avg as
mydata3" I could then put the data into excel.
But I can't do this :(
How can I store the AVG 5.25773 into a variable so I can use it in Excel
like so:
ADO_rs.Open
ActiveCell.Offset(x, 5).Value = ADO_rs.Fields!AVG GOES HERE
Maybe there is an easier way to do this' Any suggestions'
DavidHi
This seems to be very Excel specific SQL and therefore not really covered by
SQL server!
At a guess you will need to move the Average function into the select
columns
John
"Aster99" <Aster99@.home.net> wrote in message
news:%23igwDlOKEHA.204@.TK2MSFTNGP10.phx.gbl...
> I'm using VB to write some data to an Excel spreadsheet.
> I use the following vb/sql statement to get the data I need:
> ADO_rs.Source = "select least(2+(0.044117*CT_OFF_GEN),8)/count(*)as
mydata2
> calculate avg from """ + "6b_NRatio" + """.1 where ct_text like 'on/off
> it produces the following correct output:
> ct_off_time mydata2
> -- --
> 05-MAY-03 18:57:57.7 1.97044
> 24-JAN-03 11:46:07.8 8
> 16-AUG-03 09:58:27.9 5.30701
> ......
> 16-AUG-03 21:22:57.7 6.09582
> 10-DEC-02 16:46:37.6 8
> --
> AVG 5.25773
> If I could change the above code where I could store the "calculate avg as
> mydata3" I could then put the data into excel.
> But I can't do this :(
> How can I store the AVG 5.25773 into a variable so I can use it in Excel
> like so:
> ADO_rs.Open
> ActiveCell.Offset(x, 5).Value = ADO_rs.Fields!AVG GOES HERE
> Maybe there is an easier way to do this' Any suggestions'
> David
>

Putting calculated value into excel

I'm using VB to write some data to an Excel spreadsheet.
I use the following vb/sql statement to get the data I need:
ADO_rs.Source = "select least(2+(0.044117*CT_OFF_GEN),8)/count(*)as mydata2
calculate avg from """ + "6b_NRatio" + """.1 where ct_text like 'on/off
it produces the following correct output:
ct_off_time mydata2
-- --
05-MAY-03 18:57:57.7 1.97044
24-JAN-03 11:46:07.8 8
16-AUG-03 09:58:27.9 5.30701
.......
16-AUG-03 21:22:57.7 6.09582
10-DEC-02 16:46:37.6 8
AVG 5.25773
If I could change the above code where I could store the "calculate avg as
mydata3" I could then put the data into excel.
But I can't do this
How can I store the AVG 5.25773 into a variable so I can use it in Excel
like so:
ADO_rs.Open
ActiveCell.Offset(x, 5).Value = ADO_rs.Fields!AVG GOES HERE
Maybe there is an easier way to do this? Any suggestions?
David
Hi
This seems to be very Excel specific SQL and therefore not really covered by
SQL server!
At a guess you will need to move the Average function into the select
columns
John
"Aster99" <Aster99@.home.net> wrote in message
news:%23igwDlOKEHA.204@.TK2MSFTNGP10.phx.gbl...
> I'm using VB to write some data to an Excel spreadsheet.
> I use the following vb/sql statement to get the data I need:
> ADO_rs.Source = "select least(2+(0.044117*CT_OFF_GEN),8)/count(*)as
mydata2
> calculate avg from """ + "6b_NRatio" + """.1 where ct_text like 'on/off
> it produces the following correct output:
> ct_off_time mydata2
> -- --
> 05-MAY-03 18:57:57.7 1.97044
> 24-JAN-03 11:46:07.8 8
> 16-AUG-03 09:58:27.9 5.30701
> ......
> 16-AUG-03 21:22:57.7 6.09582
> 10-DEC-02 16:46:37.6 8
> --
> AVG 5.25773
> If I could change the above code where I could store the "calculate avg as
> mydata3" I could then put the data into excel.
> But I can't do this
> How can I store the AVG 5.25773 into a variable so I can use it in Excel
> like so:
> ADO_rs.Open
> ActiveCell.Offset(x, 5).Value = ADO_rs.Fields!AVG GOES HERE
> Maybe there is an easier way to do this? Any suggestions?
> David
>

put inserted value into a parameter in a trigger

How would i get the value of a field that i just inserted and put that into a parameter, so that i could update another table.

This is the code that i used in the trigger that did not work:
@.field1 = select srcfield1 from inserted

Anyway here is the full code:

CREATE TABLE Source (srcID int IDENTITY, srcField1 nvarchar(50))
CREATE TABLE Destination (destID int IDENTITY, destField1 nvarchar(50))
go

CREATE TRIGGER tr_SourceInsert ON [dbo].[Source]
FOR INSERT
@.Field nvarchar(50) output
AS
SELECT @.Field1 = SELECT Field1 FROM inserted
UPDATE Destination
SET Field1 = @.Field
where destID = '1'
go

INSERT Source(srcfield1) VALUES ('A')
goBased on your sample, there are no rows in the destination table. There couldn't be anything to update.

Just FYI, you appear to be taking a "one row" approach to your trigger, This will fail the first time you insert multiple rows into the source table using a single SQL statement.

If you explain a bit more about what you are trying to do, I'd bet that someone here can help you, but I don't understand well enough to be much help yet.

-PatP|||Okay, i forgot the line of sql that inserted a row into the DESTINATION table. Here it is data in it.
INSERT Source(srcfield1) VALUES ('1')

And i got the trigger to work by changing the code to:
SELECT @.field1 = srcField1 from inserted

How would i change this to take a 'multiple row' that you refer to.

thanks.|||To allow for multiple row inserts, you'd do something like:CREATE TABLE Source (srcID int IDENTITY, srcField1 nvarchar(50))
GO

CREATE TABLE Destination (destID int IDENTITY, destField1 nvarchar(50))
go

CREATE TRIGGER tr_SourceInsert ON [dbo].[Source]
FOR INSERT AS

UPDATE d
SET d.Field1 = i.Field1
FROM inserted AS i
JOIN destination AS d
ON d.destID = '1'

RETURN
GO

INSERT Source(srcfield1) VALUES ('1')
GO

INSERT Source(srcfield1) VALUES ('A')
GOThis code still seems suspect for a trigger, since I can't fathom why you would always want to update the destination table this way. There may be a reason for it, but I'm skeptical.

-PatP

Pulling 2 values from 2 textboxes displayed in a third

I have a problem trying to get a value from textbox 1 and textbox 2.
If those two values match the data in the database then it has to
return a third corresponding value to a third textbox (or label) after
a user clicks a submit button. Basically, I am trying to allow users
to enter a value of weight in pounds in textbox 1 and a zip code in
textbox 2 then pulling the shipping amount from a courier's database
and returning that dollar amount to a third textbox or label after
they click submit. How would I write this code in vb.NET?

Thanks,
TGHere's an example to get you started:

--schema
CREATE TABLE ShippingCharges
(
ShippingWeight int NOT NULL,
PostalCode varchar(5) NOT NULL,
ShippingCharge decimal(9, 2) NOT NULL
CONSTRAINT PK_ShippingCharges
PRIMARY KEY(ShippingWeight, PostalCode)
)

'VB.Net snippet
Try
Dim connection As New SqlConnection("Data
Source=MyServer;Initial Catalog=MyDatabase;Integrated Security=SSPI")
connection.Open()
Dim command As New SqlCommand("SELECT @.ShippingCharge =
ShippingCharge FROM ShippingCharges WHERE PostalCode = @.PostalCode AND
ShippingWeight = @.ShippingWeight", connection)
command.Parameters.Add(New SqlParameter("@.ShippingCharge",
SqlDbType.Decimal))
command.Parameters("@.ShippingCharge").Direction =
ParameterDirection.Output
command.Parameters("@.ShippingCharge").Precision = 9
command.Parameters("@.ShippingCharge").Scale = 2
command.Parameters.Add(New SqlParameter("@.ShippingWeight",
Convert.ToInt32(Me.TextBox1.Text)))
command.Parameters.Add(New SqlParameter("@.PostalCode",
Me.TextBox2.Text))
command.ExecuteScalar()
If command.Parameters("@.ShippingCharge").Value Is DBNull.Value
Then
MessageBox.Show("No shipping charge found")
Else
Me.TextBox3.Text =
command.Parameters("@.ShippingCharge").Value.ToString()
End If
connection.Close()
Catch ex As SqlException
MessageBox.Show(ex.ToString)
Catch ex As Exception
MessageBox.Show(ex.ToString)
End Try
End Sub

--
Hope this helps.

Dan Guzman
SQL Server MVP

"TG" <tjgraham4@.hotmail.com> wrote in message
news:4822c297.0409231326.4a2a892a@.posting.google.c om...
>I have a problem trying to get a value from textbox 1 and textbox 2.
> If those two values match the data in the database then it has to
> return a third corresponding value to a third textbox (or label) after
> a user clicks a submit button. Basically, I am trying to allow users
> to enter a value of weight in pounds in textbox 1 and a zip code in
> textbox 2 then pulling the shipping amount from a courier's database
> and returning that dollar amount to a third textbox or label after
> they click submit. How would I write this code in vb.NET?
> Thanks,
> TG

Publisher keeps trying to connect to subscriber

hi,

I manually changed a value in a table that makes part of the Replication and after that the sync failed on the procedure that updates the table on the subscriber.

So I manually re-start the sync. But for some reason it keeps trying to reconnect to the subscriber.
If I try to delete the subscriber to push it again, it locks.

I went to the Agent History, it showed the error "The process could not connect to Subscriber"
ODBC SQL SERVER DRIVER (number 08001)

I checked the SQL on the subscriber and it's running.
Does anybody has an idea of what I could do ?

Thanks !Before somebody try to answer, I got the replication running again.
I STOPPED the Sql Server at the subscriber and Re-started it.

things looks fine now.

Renata