Comparing

Seeing as I am not the best with SQL and I was having a big time trouble with this I thought I would come here for some help. I have two tables with names in both two columns and want to update one table with the names of any new names in the other table. I have tried the following code and many versions of such.

Please help. and yes this is not the best SQL code and thats why I came here.

Insert Into 2003(Name)SELECT User_Name FROM Qry WHERE (Name FROM 2003)<>(User_Name From Qry)

Also used the same code cept for UPDATE where Insert Into, and also changed Where statement to a couple other things.

Thank you for your time.

Billinsert into [2003] (name)

select q.user_name
from qry q, [2003] t
where t.name <> q.user_name

------------

update t
set t.name = q.username
from [2003] t, qry q
where t.name <> q.user_name

CommandText issue - SQL Reporting Services

I'm having a ton of trouble with a dataset. It builds at design time,
but fails at runtime, saying:

--------
Processing Errors
--------
An error has occurred during report processing.
Cannot set the command text for data set 'ds_Legal_Entity'.
Error during processing of the CommandText expression of dataset
'ds_Legal_Entity'.
--------
OK
--------

Below is the CommandText for ds_Legal_Entity that gives me the error:

="SELECT DISTINCT dbo.t_d_legal_entity.legal_entity_desc FROM
dbo.t_d_legal_entity INNER JOIN dbo.t_f_month_summary ON
dbo.t_d_legal_entity.legal_entity_key =
dbo.t_f_month_summary.legal_entity_key WHERE
(dbo.t_f_month_summary.acctg_mth_key = " &
Parameters!acctg_mth_key.Value & ")" &
IIF(Parameters!BusUnitKey.Value = 0,""," AND
(dbo.t_f_month_summary.bus_unit_key = " & Parameters!BusUnitKey.Value &
")") & " ORDER BY dbo.t_d_business_unit.legal_entity_desc"

If I delete everything after " & Parameters!acctg_mth_key.Value & ")",
I won't get the error, so I assume that's where the problem lies. I
just need another pair of eyes to see it.

Thanks!
Mike(Michael.EJ.Reynolds@.gmail.com) writes:
> Below is the CommandText for ds_Legal_Entity that gives me the error:
>="SELECT DISTINCT dbo.t_d_legal_entity.legal_entity_desc FROM
> dbo.t_d_legal_entity INNER JOIN dbo.t_f_month_summary ON
> dbo.t_d_legal_entity.legal_entity_key =
> dbo.t_f_month_summary.legal_entity_key WHERE
> (dbo.t_f_month_summary.acctg_mth_key = " &
> Parameters!acctg_mth_key.Value & ")" &
> IIF(Parameters!BusUnitKey.Value = 0,""," AND
> (dbo.t_f_month_summary.bus_unit_key = " & Parameters!BusUnitKey.Value &
> ")") & " ORDER BY dbo.t_d_business_unit.legal_entity_desc"
>
> If I delete everything after " & Parameters!acctg_mth_key.Value & ")",
> I won't get the error, so I assume that's where the problem lies. I
> just need another pair of eyes to see it.

The best way to sort this out, is to grab the generated SQL and then
copy and paste into Query Analyzer to run it there for hopefully a
better error message.

The one error I can see is that the ORDER BY clause includes a column
which is not in the SELECT list, which is required when you have DISTINCT.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Command delivery status in MSrepl_commands

I need to build some custom replication trouble shooter and I can't find
answers on couple of questions.
- How to determine status of command in MSrepl_commands? I mean status is
delivered this command to subscriber or not.
- How long delivered transactions reside in MSrepl_commands?
Have a look at the view msdistribution_status, which should be what you are
looking for. The code of the view is not encrypted and you could use it to
not do the group by and create your own version. If you want the actual
commands, then have a look at sp_browsereplcmds.
How long do commands reside in this table? If they have been read by all the
distribution agents involved and you don't have anonymous subscribers, the
commands will be removed by the cleanup agent. If a distribution agent isn't
synchronized or you have anonymous subscribers, they'll stay there until the
retention period is reached (72 hourd by default), and are then removed by
the cleanup agent.
Rgds,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)

come on SQLdatareader....read....read....read...ok help.

OK, I'm using VS2003 and I'm having trouble. The page works perfectly when I created it with WebMatrix but I want to learn more about creating code behind pages and this page doesn't work. I think it has some things to do with Query builder but I can't seem to get change outside "please register". I have the table populated and it is not coming back with "login successful" or "password wrong" when I've entered correct information. Enclosed is what I've done in VS2003. Can you see where my error is? Any help would be greatly appreciated.
Thanks again.

Imports System.data.sqlclient
Imports System.Data
Public Class login2
Inherits System.Web.UI.Page

#Region " Web Form Designer Generated Code "

'This call is required by the Web Form Designer.
<System.Diagnostics.DebuggerStepThrough()> Private Sub InitializeComponent()
Me.SqlConnection1 = New System.Data.SqlClient.SqlConnection
Me.SqlCommand1 = New System.Data.SqlClient.SqlCommand
'
'SqlConnection1
'
Me.SqlConnection1.ConnectionString = "server=LAWORKSTATION;user id=sa;database=test;password=t3st"
'
'SqlCommand1
'
Me.SqlCommand1.CommandText = "SELECT pass FROM Customer WHERE (email = 'txtusername.text')"
Me.SqlCommand1.Connection = Me.SqlConnection1

End Sub
Protected WithEvents lblUsername As System.Web.UI.WebControls.Label
Protected WithEvents lblPassword As System.Web.UI.WebControls.Label
Protected WithEvents txtUsername As System.Web.UI.WebControls.TextBox
Protected WithEvents txtPassword As System.Web.UI.WebControls.TextBox
Protected WithEvents btnSubmit As System.Web.UI.WebControls.Button
Protected WithEvents lblMessage As System.Web.UI.WebControls.Label
Protected WithEvents SqlConnection1 As System.Data.SqlClient.SqlConnection
Protected WithEvents SqlCommand1 As System.Data.SqlClient.SqlCommand

'NOTE: The following placeholder declaration is required by the Web Form Designer.
'Do not delete or move it.
Private designerPlaceholderDeclaration As System.Object

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

Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
'Put user code to initialize the page here
End Sub

Private Sub btnSubmit_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSubmit.Click
SqlConnection1.Open()
Dim dr As SqlDataReader = SqlCommand1.ExecuteReader
If dr.Read() Then
If dr("password").ToString = txtPassword.Text Then
lblMessage.Text = "login successful"
Else
lblMessage.Text = "Wrong password"
End If
Else
lblMessage.Text = "Please register"
End If
dr.Close()
SqlConnection1.Close()
End Sub
End ClassTake a look at this line here:

Me.SqlCommand1.CommandText = "SELECT pass FROM Customer WHERE (email = 'txtusername.text')"

What you are asking here is to retrieve all [pass] fields from the rows in [Customer] table where the value of field [email] is "txtusername.text".

What you probably meant to ask is retrieve all [pass] fields from the rows in [Customer] table where the value of field [email] is the same as the value of txtUsername.Text.

Which should translate into command text like so:

Me.SqlCommand1.CommandText = "SELECT pass FROM Customer WHERE (email = '" & txtUsername.Text & "')"

That should work. However, now you need to think about the possibility of a SQL injection attacks. Suppose somebody enters this is string as an email (single qoutes are significant):

'); DELETE Customer; SELECT ('

As you might've guessed, that would neatly zap your Customer table.

There are many ways to protect yourself from such an attack. Here's one possibility:

Create a function like this (pardon my VB.NET, it's rusty):

Function Tick(s As String)
Tick = s.Replace("'", "''")
End Function

And modify the command text assignment line like so:

Me.SqlCommand1.CommandText = "SELECT pass FROM Customer WHERE (email = '" & Tick(txtUsername.Text) & "')"

That would make sure that any attempts to close the tick marks in the statement would fail.

Anyways, happy coding!|||Better than trying to outsmart folks trying SQL Injection attacks,use parameters.|||OK, for testing purposes I did change it with querybuilder and this is what VS2003 enters in for the command:

Me.SqlCommand1.CommandText = "SELECT pass FROM Customer WHERE (email = ' "" & txtUsername.text & "" ')"

It enters double "" by default...and it doesn't work.

I then manually entered you line precisely as typed above:

Me.SqlCommand1.CommandText = "SELECT pass FROM Customer WHERE (email = '" & txtUsername.Text & "')"

I've also tried:
Me.SqlCommand1.CommandText = "SELECT pass FROM Customer WHERE email = '" & txtUsername.Text & "'"

It still doesn't work. The response is always "Please register".

I've tested the connection and it works. The table data is correct when I enter in the right login and password but the prompt is nothing but "Please register". This page without the code behind works flawlessly. I'm stumped.|||Erm unless I'm reading this incorrectly isn't the result of txtUserName.text ALWAYS empty? You're populating the command string before anyone's typed in their name.

Try moving the ...CommandText = ... line just before the ExecuteReader|||Good catch, pkr!

He is right. Although we've fixed the one problem, there's still the other one present: InitializeComponent is called (and the CommandText property initialized) at the time the page is loading, which prevents the actual submitted value of txtUsername from ever being submitted.

As "douglas.reily" noted, time to call in parameters for help.

First, let's modify our query to this:

SELECT pass FROM Customer WHERE (email = @.email)

Then, just before you call ExecuteReader, add parameter to the command:

Me.SqlCommand1.Parameters.Add(New SqlParameters("@.email", textUsername.Text)

That should do it.|||I think you are reading it incorrectly. SQLdatareader runs SQLcommand1 which, in turn, runs the query for getting the login and password. SQLdatareader is run only when the user has clicked on the "submit" button and txtUsername.text is populated then. At least, I think this is how it happens.
I'm going to move to stored procedures and parameters instead but this is just bugging me that this page works perfectly fine without a code behind page but it has problems when I redid it with VS2003.|||OK, I've made some changes and added a parameter. In the "click" sub I've populated the parameter like this:

SqlCommand1.Parameters.Item("@.email").Value = txtUsername.Text

This seems to work. Thanks for all your help.|||Glad it's working. But out of interest does it still work in you put the ....value = txtUserName.Text... line back to it's original position. i.e. not in the same function as the button event?