Hi!
Is it possible to set a query result (scalar) to scalar variable. I would like to set a qery result (SELECT COUNT(*) FROM MyTable) to a scalar variable:
DECLARE @temp int
SET @temp = query result...
Is it possible? I couldn't find the way to do that...
Hi,
below is the sql statements for my web service using C#.
Code Block
string sql = "SELECT TOP 1 Pos FROM" + "TABLE1" +"ORDER BY Pos ASC"
SqlCommand comm = new SqlCommand(sql, conn);
Now if i want to set the Pos to a variable where i can call at another part of my program, how do i do that?
Hello,
I have a report which displays a customers invoice, in both the companys local currency, and the customers local currency.
The report language is "English (United Kingdom)"
The fields showing customers currency language setting is set to something else, i.e. "France (French)" to display the Euro currency.
The application handles 34 currencies, the query returns the language string, ("France (French)"), to allow the report to bind its language setting to the querys output.
However, it doesn't work, a normal textbox will display the correct country name string, but Reporting Services cannot bind the language setting to a query result. So I also tried setting it as a report parameter, but no joy either (all currencys revert to USD).
I'm using =First(Fields!curFormat.Value, "myDataSet") to bind the 'language' setting, the result of this expression returns "France (French)", which is a valid option for this language setting, as it's in the drop down list.
Rather than create 34 seperate reports for each currency, are there any suggestions on how to bind a fields language setting to a query result?
Hello Again, did you miss me?
ok now i have this problem. I need to make a query that return one single data. I need to store this data in a variable. Something like that:
DECLARE @A VARCHAR(50)
@A=SELECT mycolumn FROM mytable WHERE mycolumn = 'Something'
There is a form to do it?
Thanks Friends
Hello,
I am interested in what the simplest was to get a query result that will only ever have one result (ie One column, one row) into a variable. An ugly way is to use a cursor that simply fetches the first row but that seems to be a horrible way to do it and it has sometimes major drawbacks sometimes (mainly if I have to dynamically choose the table). Surely there is a better way?
What do you think? A simple example would be nice.
Cheers
J
Is this possible as given below
declare @Qry as varchar(8000)
declare @Cnt as int
begin
set @Qry = 'select @Cnt=count(*) from ' + @TableName
exec @Qry
select @Cnt
end
But its not working....
can any one help me out in this.....
Thnx
Parag
The following query is failing when trying to apply the
MAX(field_x_order)
to the variable @max
Note the x is represented by the string variable @stri
declare @i int
declare @stri varchar(10)
declare @max int
set @i = 18
set @max = 0
while @i < 49
begin
set @i = @i + 1
set @stri = cast(@i as varchar(10))
select @max = MAX(field_ + @stri + _ORDER) FROM table_name WHERE field_ + @stri + IS NOT NULL -- error: Incorrect syntax near the keyword 'IS'.
exec ('UPDATE display_1a SET field_' + @stri + '_order = field_' + @stri + '_order ' + @max + 'WHERE field_' + @stri + ' IS NULL')
end
I have also tried:
select MAX(field_ + @stri + _ORDER) INTO @max = FROM display_1a WHERE field_ + @stri + IS NOT NULL -- error: Incorrect syntax near '@max'.
and:
select @max = ('SELECT MAX(field_' + @stri + '_ORDER) FROM display_1a WHERE field_' + @stri + ' IS NOT NULL') -- error: Conversion failed when converting the varchar value 'SELECT MAX(field_19_ORDER) FROM display_1a WHERE field_19 IS NOT NULL' to data type int.
Thanks,
Lou
Lou
I have a dynamic query which returns me a result and I want to capture that value to make further use of it in the same code. Is that possible??
exec ('select col_nm from table_name'). i want the result of this query to be captured.
DP
Hi:
Is there a way to assign a dynamic query result to a local variable?
declre @sqlString nvarchar(4000),
@minEventDate datetime,
@databaseName varchar(25)
selct @databaseName = 'customer_12345'
(actually, a cursor loop will assign the database name dynamically, here just to simplify the situation)
select @sqlString =
'select ' + @minEventDate + '= (select min(eventDate) from ' + @databaseName + '.dbo.tblABC)'
exec sql_executesql @sqlString
print '@minEventDate: ' + cast(@minEventDate as varchar(19))
Though the select min(eventDate) from customer_12345.dbo.tblABC
returns a date, ex. '02/01/2004 12:35 pm', however, the printed @minEventDate is always with Null value. It mean, the value was never correctly assigned to the local variable.
As an alternate way, I am using temp table to insert it with the query result and then assign to the local variable. Since I have many local variables to try to get the min, max, count for around 10 fields, perfer a way to direct assign to the local variable instead of 10 temp tables.
thanks
-D
Hi.
I am trying to store the column value to a variable from a distributed query.
The query is formed on the fly.
i need to accomplish something like this
declare @id int
declare @columnval varchar(50)
declare @query varchar(1024)
@Query = "select @columnval = Name from server.database.dbo.table where id ="+convert(varchar,@ID)
exec (@query)
print @Columnname
-MAK
Hello all:
Here is a sample query:
DECLARE @KEYID NVARCHAR (50) ; SET @KEYID = '1074958'
DECLARE @ENTITY NVARCHAR (100); SET @ENTITY = 'HouseDimension'
DECLARE @KeyCol NVARCHAR(50);
SET @KeyCol = (SELECT LEFT(@ENTITY, (SELECT CHARINDEX( 'DIM', @ENTITY) -1)) )+ 'Key'
DECLARE @KeyValue NVARCHAR (1000)
SET @KeyValue = 'SELECT '+ @KeyCol + ' FROM HouseManagementFact WHERE HouseKey = ' + @KEYID +
' GROUP BY ' + @KeyCol + ' HAVING SUM(TotalClaimCount) > 0 OR SUM(HouseCount) > 0 '
The value resulting from Executing @KeyValue is an integer.
I want to store this value in a new variable say @VAR2
When I do this
DECLARE @VAR2 INT
SET @VAR2 = execute sp_executesql @KeyValue
its giving me an error.
can somebody let me know the correct form of storing the value resulting from @KeyValue in some variable ?
Hello!
I have a aspx page in which I have a Gidview populated by a sqlDataSouce.
This is my code:
<%@ Page Language="VB" AutoEventWireup="false" CodeFile="CostEmployee1.aspx.vb" Inherits="RecursosHumanos_CostEmployee1" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"><html xmlns="http://www.w3.org/1999/xhtml" ><head runat="server"> <title>Untitled Page</title></head><body> <form id="form1" runat="server"> <div> <asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" CellPadding="4"
DataSourceID="SqlDataSource1" ForeColor="#333333" GridLines="None" Style="z-index: 100; left: 0px; position: absolute; top: 0px"> <FooterStyle BackColor="#507CD1" Font-Bold="True" ForeColor="White" /> <Columns> <asp:TemplateField> <ItemTemplate> <asp:LinkButton ID="Editar" Text="Editar" runat="server" CommandName="Edit"></asp:LinkButton> </ItemTemplate> <EditItemTemplate> <asp:LinkButton ID="LinkButton1" runat="server" CausesValidation="True" CommandName="Update"
Text="Actualizar" style="color: white"></asp:LinkButton> <asp:LinkButton ID="LinkButton2" runat="server" CausesValidation="False" CommandName="Cancel"
Text="Cancelar" style="color: white"></asp:LinkButton> </EditItemTemplate> </asp:TemplateField> <asp:TemplateField> <ItemTemplate> <asp:LinkButton ID="Apagar" Text="Apagar" runat="server" CommandName="Delete" OnClientClick='return confirm("Tem a certeza que deseja apagar este registo?");' CausesValidation="false"></asp:LinkButton> </ItemTemplate> </asp:TemplateField> <asp:TemplateField HeaderText="Id_CostEmployee" InsertVisible="False" SortExpression="Id_CostEmployee"> <EditItemTemplate> <asp:Label ID="Label1" runat="server" Text='<%# Eval("Id_CostEmployee") %>'></asp:Label> </EditItemTemplate> <ItemTemplate> <asp:Label ID="Label1" runat="server" Text='<%# Bind("Id_CostEmployee") %>'></asp:Label> </ItemTemplate> </asp:TemplateField> <asp:TemplateField HeaderText="Id_Employee" SortExpression="Id_Employee"> <EditItemTemplate> <asp:TextBox ID="TextBox1" runat="server" Text='<%# Bind("Id_Employee") %>'></asp:TextBox> </EditItemTemplate> <ItemTemplate> <asp:Label ID="Label2" runat="server" Text='<%# Bind("Id_Employee") %>'></asp:Label> </ItemTemplate> </asp:TemplateField> <asp:TemplateField HeaderText="FullName" SortExpression="FullName"> <EditItemTemplate> <asp:TextBox ID="textbox5" runat="server" Text='<%# Bind("FullName")%>'></asp:TextBox> </EditItemTemplate> <ItemTemplate> <asp:Label ID="Label6" runat="server" Text='<%# Bind("FullName") %>'></asp:Label> </ItemTemplate> </asp:TemplateField> <asp:TemplateField HeaderText="NumEmployee" SortExpression="NumEmployee"> <EditItemTemplate> <asp:TextBox ID="TextBox6" runat="server" Text='<%# Bind("NumEmployee") %>'></asp:TextBox> </EditItemTemplate> <ItemTemplate> <asp:Label ID="Label7" runat="server" Text='<%# Bind("NumEmployee") %>'></asp:Label> </ItemTemplate> </asp:TemplateField> <asp:TemplateField HeaderText="Period" SortExpression="Period"> <EditItemTemplate> <asp:TextBox ID="TextBox2" runat="server" Text='<%# Bind("Period") %>'></asp:TextBox> </EditItemTemplate> <ItemTemplate> <asp:Label ID="Label3" runat="server" Text='<%# Bind("Period") %>'></asp:Label> </ItemTemplate> </asp:TemplateField> <asp:TemplateField HeaderText="CostHour" SortExpression="CostHour"> <EditItemTemplate> <asp:TextBox ID="TextBox3" runat="server" Text='<%# Bind("CostHour") %>'></asp:TextBox> </EditItemTemplate> <ItemTemplate> <asp:Label ID="Label4" runat="server" Text='<%# Bind("CostHour") %>'></asp:Label> </ItemTemplate> </asp:TemplateField> <asp:TemplateField HeaderText="Date" SortExpression="Date"> <EditItemTemplate> <asp:TextBox ID="TextBox4" runat="server" Text='<%# Bind("Date") %>'></asp:TextBox> </EditItemTemplate> <ItemTemplate> <asp:Label ID="Label5" runat="server" Text='<%# Bind("Date") %>'></asp:Label> </ItemTemplate> </asp:TemplateField> </Columns> <RowStyle BackColor="#EFF3FB" /> <EditRowStyle BackColor="#2461BF" /> <SelectedRowStyle BackColor="#D1DDF1" Font-Bold="True" ForeColor="#333333" /> <PagerStyle BackColor="#2461BF" ForeColor="White" HorizontalAlign="Center" /> <HeaderStyle BackColor="#507CD1" Font-Bold="True" ForeColor="White" /> <AlternatingRowStyle BackColor="White" /> </asp:GridView> <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:EuroscutConnectionString %>" SelectCommand="SELECT [HR.CostEmployee].Id_CostEmployee, [HR.CostEmployee].Id_Employee, [HR.CostEmployee].Period, [HR.CostEmployee].CostHour, [HR.CostEmployee].Date, [HR.Employee].FullName, [HR.Employee].NumEmployee FROM [HR.CostEmployee] INNER JOIN [HR.Employee] ON [HR.CostEmployee].Id_Employee = [HR.Employee].Id_Employee"
UpdateCommand="UPDATE [HR.CostEmployee] set Period = @Period, CostHour = @CostHour where Id_CostEmployee = @Id_CostEmployee"
DeleteCommand="DELETE from [HR.CostEmployee] where (Id_CostEmployee = @Id_CostEmployee)"> <UpdateParameters> <asp:Parameter Name="Period" /> <asp:Parameter Name="CostHour" /> <asp:Parameter Name="Id_CostEmployee" /> </UpdateParameters> <DeleteParameters> <asp:Parameter Name="Id_CostEmployee" Type="int32" /> </DeleteParameters> </asp:SqlDataSource> </div> </form></body></html> When I run the page I'm able to edit the row but when I try to delete it gives me the error:
Must declare the scalar variable "@Id_CostEmployee".
I'm tired of "googling" this error, and I've tried all the advices, nothing...
I don't know what is happening here, I have 5 other forms, all simillar and they all work!
Any suggestions, pleeeeaaaase?
Thank's!
Paula
I'm making an ecommerce web app from following the Apress "Beginning ASP.Net 2 E-commerce with C#" book, and when I implement a stored procedure (I made a mdf DB in the app_Data folder), I get the following message: Must declare the scalar variable @CategoryIDThe code used to obtain this error is below: CREATE PROCEDURE DeleteCategory(@CategoryINT int)ASDELETE FROM CategoryWHERE CategoryID = @CategoryID I get this error with every Stored Procedure I try to implement. What should I do to fix this? In SQL Server 2k5 Management Studio, this problem does not present itself.
View 1 Replies View RelatedHi with the code below I am getting the error
Error inserting record. Must declare the scalar variable "@contractWHERE"
I removed @contract and it then gave me the error
Error inserting record. Must declare the scalar variable "@zipWHERE"
I was wondering if some can point me in the right direction for fixxing this
protected void cmdUpDate_Click(Object sender, EventArgs e)
{
//Define ADO.NET Objects.
string updateSQL;
updateSQL = "UPDATE Authors SET ";
updateSQL += "au_id=@au_id, au_fname=@au_fname, au_lname=@au_lname, ";
updateSQL += "phone=@phone, address=@address, city=@city,state=@state, ";
updateSQL += "zip=@zip, contract=@contract";
updateSQL += "WHERE au_id@au_id_original";
SqlConnection con = new SqlConnection(connectionString);
SqlCommand cmd = new SqlCommand(updateSQL, con);
//Add the parameters.
cmd.Parameters.AddWithValue("@au_id", txtID.Text);
cmd.Parameters.AddWithValue("@au_fname", txtFirstName.Text);
cmd.Parameters.AddWithValue("@au_lname", txtLastName.Text);
cmd.Parameters.AddWithValue("@phone", txtPhone.Text);
cmd.Parameters.AddWithValue("@address", txtAddress.Text);
cmd.Parameters.AddWithValue("@city", txtCity.Text);
cmd.Parameters.AddWithValue("@state", txtState.Text);
cmd.Parameters.AddWithValue("@zip", txtZip.Text);
cmd.Parameters.AddWithValue("@contract", Convert.ToInt16(chkContract.Checked));
cmd.Parameters.AddWithValue("au_id_original", lstAuthor.SelectedItem.Value);
//Try to open the database and execute the update
try
{
con.Open();
int updated = cmd.ExecuteNonQuery();
lblStatus.Text = updated.ToString() + " records inserted.";
}
catch (Exception err)
{
lblStatus.Text = "Error inserting record. ";
lblStatus.Text += err.Message;
}
finally
{
con.Close();
}
}
}
Many Thanks in advance
CREATE PROCEDURE [dbo].[Testing]
@FilteredID VARCHAR (MAX),
@SchoolCode VARCHAR (MAX),
[Code]....
I tried to execute above sproc in SQL Server Management Studio , I received the error: Must declare the scalar variable "@Score1".
OK i have my stored procedure all set up and working.. But when i try and add a second variable called @iuser and then after i execute the stored procedure, i get an error saying:-
ERROR
"Must declare scalar variable @iuser"
Here is the code i am using in my stored proc, also my stored proc worked fine before i used a second variable??!
//BEGIN
ALTER PROCEDURE [dbo].[putpending]
(@cuuser nvarchar(1000), @iuser nvarchar(1000))
AS
Declare @sql nvarchar(1000)
SELECT @sql =
'INSERT INTO ' +
@cuuser +
' (Pending) VALUES (@iuser)'
EXECUTE (@sql)
RETURN
//END
And i know my VB.NET code is working but i will put it in anyway:-
//BEGIN
'variables
Dim user As String
user = Profile.UserName
Dim intenduser As String
intenduser = DetailsView1.Rows(0).Cells(1).Text.ToString()
'connection settings
Dim cs As String
cs = "Data Source=.SQLEXPRESS;AttachDbFilename=|DataDirectory|friends.mdf;Integrated Security=True;User Instance=True"
Dim scn As New SqlConnection(cs)
'parameters
Dim cmd As New SqlCommand("putpending", scn)
cmd.CommandType = CommandType.StoredProcedure
cmd.Parameters.Add("@cuuser", SqlDbType.NVarChar, 1000)
cmd.Parameters("@cuuser").Value = user
cmd.Parameters.Add("@iuser", SqlDbType.NVarChar, 1000)
cmd.Parameters("@iuser").Value = intenduser
'execute
scn.Open()
cmd.ExecuteNonQuery()
scn.Close()
//END
Any ideas why i am getting this error message?
the following is my code
can anybody rectify my problem that i get when running my application
"Must declare scalar variable @ID"
<asp:GridView ID="GridView1" DataKeyNames="ID" runat="server" AutoGenerateColumns="False" BackImageUrl="~/App_Themes/SkinFile/back1.jpg"
BorderColor="Teal" BorderStyle="Solid" DataSourceID="SqlDataSource1">
<Columns>
<asp:CommandField ButtonType="Button" ShowSelectButton="True" />
<asp:BoundField DataField="ID" HeaderText="ID" SortExpression="ID" />
<asp:BoundField DataField="Answers" HeaderText="Answers" SortExpression="Answers" />
</Columns>
</asp:GridView>
<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:YahooConnectionString7 %>"
DeleteCommand="DELETE FROM Answers WHERE (ID = @ID)" InsertCommand="INSERT INTO Answers(ID, Answers) VALUES (@ID, @Answers)"
SelectCommand="SELECT Answers.* FROM Answers" UpdateCommand="UPDATE Answers SET ID = @ID, Answers = @Answers WHERE (ID = @ID)">
<DeleteParameters>
<asp:Parameter Name="ID" />
</DeleteParameters>
<UpdateParameters>
<asp:Parameter Name="ID" />
<asp:Parameter Name="Answers" />
</UpdateParameters>
<InsertParameters>
<asp:Parameter Name="ID" />
<asp:Parameter Name="Answers" />
</InsertParameters>
</asp:SqlDataSource>
Following stored proc uses dynamic sql but it gives the error
Msg 137, Level 15, State 2, Line 3
Must declare the scalar variable "@ProjectBenefitID".
though its declared .please. tell the workaround
ALTER PROCEDURE [dbo].[spPMPT_GetBenefit]
@ProjectBenefitID INT,
@OrderBYVARCHAR(40),
-- Parmeters for Paging [Start]
@TotalPages INT OUT ,
@CurrentPageNumber INT OUT ,
@NumberOfRecordsINT = 5, /*PageSize*/
@CurrentPage INT = 0/*PageNumber*/
-- Parmeters for Paging [End]
AS
SET NOCOUNT ON
DECLARE @TMPFLOAT
DECLARE @ErrorMsgID INT
DECLARE@ErrorMsg VARCHAR(200)
----- Paging declarations start
DECLARE @SQLFinal NVARCHAR(4000)
DECLARE @Count INT
DECLARE @SC VARCHAR(4000)
----- Paging declarations end
DECLARE@SelectASVARCHAR(4000)
DECLARE@FromASVARCHAR(4000)
DECLARE@WhereASVARCHAR(4000)
DECLARE@LsOrderBy ASVARCHAR(4000)
-- Initialize vars
SET @SC= ''
SET @From= ''
SET @Where= ''
SET @Select= ''
SET @SQLFinal= ''
SET @Count= 0
IF (@CurrentPage = 0 OR @CurrentPage IS NULL)
BEGIN
--Generate error message
SELECT @ErrorMsg = 'Error occured in Stored Procedure ' + (SELECT name from sysobjects WHERE ID = @@PROCID) + '. The Page Number cannot be zero.'
--Raise error to the user
RAISERROR (@ErrorMsg,16/*severity*/,1/*state*/)
--Return error indicator
RETURN (-1)
END
IF (@NumberOfRecords = 0 OR @NumberOfRecords IS NULL )
BEGIN
--Generate error message
SELECT @ErrorMsg = 'Error occured in Stored Procedure ' + (SELECT name from sysobjects WHERE ID = @@PROCID) + '. Number of records per page cannot be zero.'
--Raise error to the user
RAISERROR (@ErrorMsg,16/*severity*/,1/*state*/)
--Return error indicator
RETURN (-1)
END
IF (@Orderby IS NULL OR @Orderby = '')
BEGIN
--Generate error message
SELECT @ErrorMsg = 'Error occured in Stored Procedure ' + (SELECT name from sysobjects WHERE ID = @@PROCID) + '. The Order by cannot be null.'
--Raise error to the user
RAISERROR (@ErrorMsg,16/*severity*/,1/*state*/)
--Return error indicator
RETURN (-1)
END
CREATE TABLE #TEMP_BENEFIT1
(
AssessBenefitID INT,
ProjectBenefitID INT,
ExpectedQuantity INT,
ExpectedQuality VARCHAR(2000),
Comments VARCHAR(2000)
)
INSERT INTO #TEMP_BENEFIT1 SELECT AssessBenefitID,ProjectBenefitID,
Quantity,Quality,
Comments
FROM PMPT_AssessBenefit
WHERE ProjectBenefitID=@ProjectBenefitID AND AssessFlag='E' --and AssessBenefitID=@IterationID
CREATE TABLE #TEMP_BENEFIT2
(
AssessBenefitID INT,
ProjectBenefitID INT,
ActualQuantity INT,
QtyFileID INT,
QtyFileName VARCHAR(100),
QtyFilepath VARCHAR(100),
ActualQuality VARCHAR(2000),
QuaFileID INT,
QualFileName VARCHAR(100),
QualFilepath VARCHAR(100),
Comments VARCHAR(2000),
refAssessBenefitID INT,
DateasON DATETIME
)
INSERT INTO #TEMP_BENEFIT2 SELECT PAB.AssessBenefitID,PAB.ProjectBenefitID,
PAB.Quantity,pab.qtyFileID,
(SELECT FileName FROM PMPT_Files WHERE FileID = pab.qtyFileID) as QtyFileName,
(SELECT UploadedFilePath FROM PMPT_Files WHERE FileID = pab.qtyFileID) as QtyFilepath,
PAB.Quality,pab.quaFileID,
(SELECT FileName FROM PMPT_Files WHERE FileID = pab.quaFileID) AS QualFileName,
(SELECT UploadedFilePath FROM PMPT_Files WHERE FileID = pab.quaFileID) as QuaFilepath,
PAB.Comments,PAB.refEXPAssessBenefitID,PAB.DateasON
FROM PMPT_AssessBenefit PAB
WHERE ProjectBenefitID=@ProjectBenefitID AND AssessFlag='A'
DECLARE @UNIT VARCHAR(100)
SELECT @UNIT=NAME FROM PMPT_Picklists WHERE PicklistID = (SELECT unitID FROM PMPT_ProjectBenefits WHERE ProjectBenefitID=@ProjectBenefitID)
IF @UNIT IS NULL
SET @UNIT = ''
SET @Select='
DECLARE @UNIT VARCHAR(100)
SELECT @UNIT=NAME FROM PMPT_Picklists WHERE PicklistID = (SELECT unitID FROM PMPT_ProjectBenefits WHERE ProjectBenefitID='+CONVERT(VARCHAR(10),@ProjectBenefitID))+'
SELECTT1.AssessBenefitID, CAST(T1.ExpectedQuantity AS VARCHAR)+'' ''+ @UNIT as ExpectedQuantity,
CAST( T2.ActualQuantity AS VARCHAR)+'' ''+ @UNIT as ActualQuantity, T2.QtyFileID, T2.QtyFileName AS QtyFileName ,T2.QtyFilepath, T1.ExpectedQuality AS ExpectedQuality , T2.ActualQuality AS ActualQuality ,
T2.QuaFileID,T2.QualFileName AS QualFileName ,T2.QualFilepath, T2.COMMENTS,CONVERT(VARCHAR(10),T2.DateasON,103) AS DateasON
FROM#TEMP_BENEFIT1 T1,#TEMP_BENEFIT2 T2
WHERET1.AssessBenefitID = T2.refAssessBenefitID'
create PROCEDURE [Update_Purged]
AS
Declare @Stg_Purged_union table
(PurgedAccountUnionID int IDENTITY,
Coid char(50),
FacilityId int,
AccountID int,
PatientName char(50),
PatientNo char(50),
PT char(50),
ST char(50),
PurgeDt datetime);
BEGIN
INSERT INTO @Stg_Purged_Union
([Coid]
,null
,null
,[PatientName]
,[PatientNo]
,[PT]
,
,[PurgeDt])
select coid, patientname, patientno, pt, st, purgedt from
[Stg_PurgedAccount]
--updating the facilityid in @stg_purged_account_file_union
update @stg_purged_union set facilityid=(select
facilityid from appfacility as b
where b.unitnum=(select
unitnum from appfacility as c
where c.unitnum=b.unitnum) and @Stg_Purged_Union.coid=b.unitnum)
I am getting the following error.
Must declare the scalar variable "@Stg_Purged_Union"
I need to send the result of a procedure to an update statement.Basically updating the column of one table with the result of aquery in a stored procedure. It only returns one value, if it didnt Icould see why it would not work, but it only returns a count.Lets say I have a sproc like so:create proc sp_countclients@datecreated datetimeasset nocount onselect count(clientid) as countfrom clientstablewhere datecreated > @datecreatedThen, I want to update another table with that value:Declare @dc datetimeset @dc = '2003-09-30'update anothertableset ClientCount = (exec sp_countclients @dc) -- this line errorswhere id_ = @@identityOR, I could try this, but still gives me error:declare @c intset @c = exec sp_countclients @dcWhat should I do?Thanks in advance!Greg
View 4 Replies View Related
I have an Execute SQL Task that executes "select count(*) as Row_Count from xyztable" from an Oracle Server. I'm trying to assign the result to a variable. However when I try to execute I get an error:
[Execute SQL Task] Error: An error occurred while assigning a value to variable "RowCount": "Unsupported data type on result set binding Row_Count.".
Which data type should I use for the variable, RowCount? I've tried Int16, Int32, Int64.
Thanks!
ive encounter this problem when i install the application at client side..enfect, i try it on all the PC at my office, i wouldnt encounter this problem..can anyone tell me what is possibly wrong with this?here is the code possible code effectedASPDim myDataset As Data.DataSet = New Data.DataSet Dim conn As Connection = New Connection("AbnormalControlDataConnString", "P_CreateAbnormalFormSect0") conn.AddParameter("Pending", Me.Session.Item("UserId"), Data.SqlDbType.Char, 80) conn.AddParameter("CreateBy", Me.Session.Item("UserId"), Data.SqlDbType.Char, 80) conn.executeSproc(myDataset) Me.Session.Add("RefNo", myDataset.Tables(0).Rows(0).Item(0)) conn = Nothing myDataset = Nothing gridview1.dataBind() <~~~~~~ i have this gridview bind another sproc named[P_GetAbnormalFormSect0)conn.vb Public Sub New(ByVal con As String, ByVal sprocName As String) configCon = con connectionString = System.Configuration.ConfigurationManager.ConnectionStrings(configCon).ConnectionString ' myConnection = New System.Data.SqlClient.SqlConnection(connectionString) myCommand = New System.Data.SqlClient.SqlCommand() myCommand.Connection = myConnection myCommand.CommandType = CommandType.StoredProcedure myCommand.CommandText = sprocName End Sub Public Sub AddParameter(ByVal name As String, ByVal value As String, ByVal type As SqlDbType, ByVal size As Int16) myCommand.Parameters.Add(name, type, size) myCommand.Parameters.Item(myCommand.Parameters.Count - 1).SqlValue = value End Sub Public Sub executeSproc(ByRef dataset As Data.DataSet) myConnection.Open() myDataSet = New System.Data.DataSet() myDataSet.CaseSensitive = True dataAdapter = New System.Data.SqlClient.SqlDataAdapter() dataAdapter.SelectCommand = myCommand If Not dataset Is Nothing Then dataAdapter.TableMappings.Add("asd", "UserInfo") dataAdapter.Fill(myDataSet) dataset = myDataSet myConnection.Close() Else myCommand.ExecuteNonQuery() End If End SubSprocset ANSI_NULLS ONset QUOTED_IDENTIFIER ONGOALTER PROCEDURE [dbo].[P_GetAbnormalFormSect0] -- Add the parameters for the stored procedure here @REFNO CHAR(11) = NULLASBEGIN -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON; DECLARE @ErrorCode int SET @ErrorCode = 0 SELECT * FROM dbo.AFStatusInfo WHERE dbo.AFStatusInfo.RefNo = @REFNO IF(@@ROWCOUNT = 0) BEGIN SET @ErrorCode = -1 END IF( @@ERROR <> 0 ) BEGIN SET @ErrorCode = @@ERROR END RETURN @ErrorCodeEND-----------------------------------------------------------------------------------------------set ANSI_NULLS ONset QUOTED_IDENTIFIER ONGOALTER PROCEDURE [dbo].[P_CreateAbnormalFormSect0] -- Parameters for the stored procedure @Pending CHAR(80), @CreateBy CHAR(80) ASBEGIN -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON; -- @ErrorCode : 0 No Error -- 1 No Data (Ignore when no ResultSet required) -- 2 Parameter not complete -- 3 Conflict with existing data -- Other error code please refer to @@ERROR code DECLARE @ErrorCode int SET @ErrorCode = 0 DECLARE @Id VARCHAR(11) DECLARE @Temp VARCHAR(4) SET @Temp = CAST((SELECT COUNT(*) FROM [AFStatusInfo] WHERE RefNo like ('BS200607%'))AS CHAR(3)) SET @Temp = @Temp + 1000 SET @Id = 'BS' SET @Id = @Id + CONVERT(CHAR(4),GETDATE(),20) SET @Id = @Id + CONVERT(CHAR(2),GETDATE(),10) SET @Id = @Id + SUBSTRING(@Temp,2,3) INSERT INTO [AFStatusInfo] (RefNo ,FlowStatus ,[Pending] ,[CreateBy] ,[DateCreated]) VALUES (@Id ,'New' ,@Pending ,@CreateBy ,CONVERT(CHAR(8), GETDATE(), 3)) SELECT @Id AS "RefNo" IF( @@ERROR <> 0 ) BEGIN SET @ErrorCode = @@ERROR END RETURN @ErrorCode OnError: RETURN @ErrorCodeEND----------------------------------------------------------------------------------------------------------------
View 1 Replies View RelatedHi,Can somone please tell me why I get the error 'Must declare the scalar variable "@recCount".' when I execute this Store Procedure? ALTER PROCEDURE dbo.CountTEst AS SET NOCOUNT ON DECLARE @sql as nvarchar(100); DECLARE @recCount as int SET @SQL = 'SELECT @recCount = COUNT(*) FROM Pool' exec sp_executesql @SQL RETURN @recCount In this case I expect RETURN value = 4.I've tried various approaches, including adding an OUTPUT parameter to the exec statement, but just can't get the syntax correct. Thanks
View 14 Replies View Related I am not seeing the problem with this. I have a button at the bottom of this form that does this...
protected void Button1_Click(object sender, EventArgs e)
{
SqlDataSource1.Insert();
}
And the SqlDataSource code...<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:calldbConnectionString2 %>"
ProviderName="<%$ ConnectionStrings:calldbConnectionString2.ProviderName %>"
InsertCommand="INSERT INTO IDcall(IDcompany, enteredBy, likelihood6mo, liklihood12mo, stamp, objective, dateOfCall, applications, specifics, developmentStage, results, nextStep, salesStrategy, potentialFirstYear, other, valueOfSale, alternateContacts, IDcontact1) VALUES (@company, @entered, @mo6, @mo12, @stamp, @objective, @dateOfCall, @applications, @specifics, @development, @results, @next, @strategy, @potential, @other, @value, @alternate, @IDcontact1)">
<InsertParameters><asp:ControlParameter ControlID="DropDownList_Company" Name="@company"
PropertyName="SelectedValue" />
<asp:ControlParameter ControlID="TextBox_signature" Name="@entered"
PropertyName="Text" />
<asp:ControlParameter ControlID="DropDownList_6mo" Name="@mo6"
PropertyName="SelectedValue" />
<asp:ControlParameter ControlID="DropDownList_12mo" Name="@mo12"
PropertyName="SelectedValue" />
<asp:ControlParameter ControlID="TextBox_submittedOn" Name="@stamp"
PropertyName="Text" />
<asp:ControlParameter ControlID="TextBox_Purpose" Name="@objective"
PropertyName="Text" />
<asp:ControlParameter ControlID="TextBox_dateOfCall" Name="@dateOfCall"
PropertyName="Text" />
<asp:ControlParameter ControlID="TextBox_applications" Name="@applications"
PropertyName="Text" />
<asp:ControlParameter ControlID="TextBox_specifics" Name="@specifics"
PropertyName="Text" />
<asp:ControlParameter ControlID="TextBox_development" Name="@development"
PropertyName="Text" />
<asp:ControlParameter ControlID="TextBox_results" Name="@results"
PropertyName="Text" />
<asp:ControlParameter ControlID="TextBox_nextStep" Name="@next"
PropertyName="Text" />
<asp:ControlParameter ControlID="TextBox_strategy" Name="@strategy"
PropertyName="Text" />
<asp:ControlParameter ControlID="TextBox_potentialFirstYear" Name="@potential"
PropertyName="Text" />
<asp:ControlParameter ControlID="TextBox_other" Name="@other"
PropertyName="Text" />
<asp:ControlParameter ControlID="TextBox_Value" Name="@value"
PropertyName="Text" />
<asp:ControlParameter ControlID="TextBox_alternateContacts" Name="@alternate"
PropertyName="Text" />
<asp:ControlParameter ControlID="DropDownList_Contact" Name="IDcontact1"
PropertyName="SelectedValue" />
</InsertParameters>
</asp:SqlDataSource>
When I click the button I get Must declare the scalar variable "@company". How is @company not declared?
And here's my stackStack Trace:
[SqlException (0x80131904): Must declare the scalar variable "@company".]
System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) +925466
System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) +800118
System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) +186
System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) +1932
System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString) +149
System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async) +1005
System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result) +132
System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe) +149
System.Data.SqlClient.SqlCommand.ExecuteNonQuery() +135
System.Web.UI.WebControls.SqlDataSourceView.ExecuteDbCommand(DbCommand command, DataSourceOperation operation) +404
System.Web.UI.WebControls.SqlDataSourceView.ExecuteInsert(IDictionary values) +447
System.Web.UI.WebControls.SqlDataSource.Insert() +13
Calls_CallNew.Button1_Click(Object sender, EventArgs e) in c:InetpubwwwrootCallsCallNew.aspx.cs:88
System.Web.UI.WebControls.Button.OnClick(EventArgs e) +105
System.Web.UI.WebControls.Button.RaisePostBackEvent(String eventArgument) +107
System.Web.UI.WebControls.Button.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument) +7
System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument) +11
System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData) +33
System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +1746
Version Information: Microsoft .NET Framework Version:2.0.50727.1433; ASP.NET Version:2.0.50727.1433
I’m getting the error messages: Must declare the scalar variable "@CustomerName".Must declare the scalar variable "@CustomerEmail". When using the code:sqlcommand = "UPDATE Transactions SET CustomerName=@CustomerName WHERE transid='" + thetransid.ToString().Trim() + "' ";
sqlcommand += "UPDATE Transactions SET CustomerEmail=@CustomerEmail WHERE transid='" + thetransid.ToString().Trim() + "' ";
cmd.Parameters.Add("@CustomerName", CustomerNameTextBox.Text);
cmd.Parameters.Add("@CustomerEmail", CustomerEmailTextBox.Text);
cmd = new SqlCommand(sqlcommand, conn);
cmd.ExecuteNonQuery();
conn.Close();
Hi All,
I'm totaly new to administrating databases.
All I want to do is run the sql server script located at http://www.data-miners.com/sql_companion.htm#downloads.
This creates some tables and uploads a series of text files into them.
When I run the script through SQL Server Express 2005 I get the error Must declare the scalar variable "@DATADIR". I suspect it's something with me putting in the wrong path.
The text files that the script needs to load into the table are located on the K drive, and I have changed the path in
declare @DATADIR varchar(128)
set @DATADIR='C:gordonookdatafinal extfiles'
to
declare @DATADIR varchar(128)
set @DATADIR='k: extfiles'
I suspect this is the wrong syntax that's why it's not working but I might be totally wrong.
The text file and the server are both saved on the k drive.
Regards,
Seaweed
I have the following code:
begin
SET @CONTADOR = @CONTADOR + 1;
set @strQuery='';
set @intYDS = 0;
SET @strQUERY = 'SELECT @intYDS = ISNULL(COALESCE(YDS,0),0)'
[Code] ....
But when I run returned me the following error: Must declared the scalar variable.
Hi all,
In my SQL Server Management Studio Express, I have a Database "testDb" that has a dbo.Inventory with a column "quantity". I executed the following sql script:
USE testDb
GO
CREATE FUNCTION AverageQuantity(@funcType varchar(20))
RETURNS numeric AS
BEGIN
DECLARE @average numeric
SELECT @average = AVG(quantity) FROM Inventory WHERE type=@funcType
RETURN @averge
END
GO
I got the following error message:
Msg 137, Level 15, State 2, Procedure AverageQuantity, Line 6
Must declare the scalar variable "@averge".
I think it was declared already in the BEGIN - END block. Please tell me why I got this error and advise me how to fix this problem.
Thanks in advance,
Scott Chang
Code Block
CREATE FUNCTION myFUNCTION (@YearMounth varchar(5) =[84/08] )
RETURNS table AS
return (
SELECT dbo.T1.PersonelNo, dbo.T1.OfficeCode, dbo.T1.jobCode
FROM dbo.T1
WHERE (LEFT(dbo.T1.StartDate, 5) <= @yearMounth) AND
(LEFT(dbo.T1.EndDate, 5) >=@yearMounth)
)
this FUNCTION run in SQL Server 2000 But Not Run In SQL Server 2005 And This Error
Code Block
Msg 137, Level 15, State 2, Procedure myFUNCTION, Line 6
Must declare the scalar variable "@yearMounth".
I am trying to run a query in the data window and get the following error. How do I resolve?
query:
select distinct [Client ID] as ClientID
FROM ITSTAFF.Incident
WHERE [Group Name] = 'ITSTAFF' and [Client ID] is not null and [Company ID] = @[Company ID]
error:
TITLE: Microsoft Report Designer
------------------------------
An error occurred while executing the query.
Must declare the scalar variable "@".
------------------------------
Hi I’m getting an error that says “Must declare the scalar variable "@StartDate".� for the following line of code :
dt = ((DataView)(EventDataSource1.Select(dssa))).ToTable()
Can anyone help me out? Here is my entire code.
Dim EventDataSource1 As New SqlDataSource()EventDataSource1.ConnectionString =
ConfigurationManager.ConnectionStrings("ASPNETDBConnectionString").ToStringDim dssa As New DataSourceSelectArguments()Dim EventID As String = ""Dim DataView = ""Dim dt As New Data.DataTableDim conn As New Data.SqlClient.SqlConnection(ConfigurationManager.ConnectionStrings("ASPNETDBConnectionString").ToString())Dim cmd As New Data.SqlClient.SqlCommand("SELECT EventID FROM Event WHERE ([StartDate] = @StartDate)", conn)EventDataSource1.SelectCommand = ("SELECT EventID FROM Event WHERE ([StartDate] = @StartDate)")conn.Open()dt = ((DataView)(EventDataSource1.Select(dssa))).ToTable()EventID = dt.Rows(0)(0).ToString()EventDataSource1.SelectParameters.Add("@StartDate",StartDate)EventID = cmd.ExecuteScalar()tbEventIDTest.Text = EventID
I'm attempting to create my first login form using the CreateUserWizard. I've spent this week reading up on how to create and customizing it. I want it to 1) the required user name is an email address (which seems to be working fine) and 2) having extra information inserted into a separate customized table. I now have the form working to the point where it accepts an email address for the username and it then writes that information along with the password to the aspnetdb.mdf...but i can't get the rest of the information to write to my custom table.I am getting the error "Must declare the scalara variable "@UserName" here's my .cs code:public partial class _Default : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
}
protected void CreateUserWizard1_CreatedUser(object sender, EventArgs e)
{
TextBox UserNameTextBox =
(TextBox)CreateUserWizardStep1.ContentTemplateContainer.FindControl("UserName");
SqlDataSource DataSource =
(SqlDataSource)CreateUserWizardStep1.ContentTemplateContainer.FindControl("InsertCustomer");
MembershipUser User = Membership.GetUser(UserNameTextBox.Text);
object UserGUID = User.ProviderUserKey;
DataSource.InsertParameters.Add("UserId", UserGUID.ToString());
DataSource.Insert();
}
protected void CreateUserWizard1_CreatingUser(object sender, LoginCancelEventArgs e)
{
CreateUserWizard cuw = (CreateUserWizard)sender;
cuw.Email = cuw.UserName;
}
} protected void CreateUserWizard1_CreatingUser(object sender, LoginCancelEventArgs e) { CreateUserWizard cuw = (CreateUserWizard)sender; cuw.Email = cuw.UserName; }} and the asp<asp:SqlDataSource ID="InsertCustomer" runat="server" ConnectionString="<%$ ConnectionStrings:kalistaConnectionString %>" InsertCommand="INSERT INTO [Customer] ([CustID], [CustEmail], [CustFN], [CustLN], [CustAddress], [CustCity], [AreaTaxID], [CustPostal_Zip], [CustCountry], [CustPhone], [CustAltPhone]) VALUES (@UserId, @UserName, @FirstName, @LastName, @Address, @City, @ProvinceState, @PostalZip, @Country, @Phone, @AltPhone)" ProviderName="<%$ ConnectionStrings:kalistaConnectionString.ProviderName %>"> <InsertParameters> <asp:ControlParameter Name="CustEmail" Type="String" ControlID="UserName" PropertyName="Text" /> <asp:ControlParameter Name="CustFN" Type="String" ControlID="FirstName" PropertyName="Text" /> <asp:ControlParameter Name="CustLN" Type="String" ControlID="LastName" PropertyName="Text" /> <asp:ControlParameter Name="CustAddress" Type="String" ControlID="Address" PropertyName="Text" /> <asp:ControlParameter Name="CustCity" Type="String" ControlID="City" PropertyName="Text" /> <asp:ControlParameter Name="AreaID" Type="String" ControlID="AreaID" PropertyName="SelectedValue" /> <asp:ControlParameter Name="CustPostal_Zip" Type="String" ControlID="PostalZip" PropertyName="Text" /> <asp:ControlParameter Name="CustCountry" Type="String" ControlID="Country" PropertyName="SelectedValue" /> <asp:ControlParameter Name="CustPhone" Type="String" ControlID="Phone" PropertyName="Text" /> <asp:ControlParameter Name="CustAltPhone" Type="String" ControlID="AltPhone" PropertyName="Text" /> </InsertParameters> </asp:SqlDataSource> thanks for the help
I started to receive this error as soon I added the line
"DataSource.SelectParameters.Clear()" Otherwise I was getting"variable
userid already decalred". I'm pasting the code below, please let me
know what I'm doing wrong. I think it is a minor problem. 1 <%@ Page Language="VB" AutoEventWireup="false" CodeFile="Registration.aspx.vb" Inherits="Members_Registration" Trace="true" %>
2
3 <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
4
5 <html xmlns="http://www.w3.org/1999/xhtml" >
6 <head runat="server">
7 <title>Untitled Page</title>
8 </head>
9 <body>
10 <form id="form1" runat="server">
11 <div>
12 <asp:CreateUserWizard ID="CreateUserWizard1" runat="server" BackColor="#FFFBD6" BorderColor="#FFDFAD" BorderStyle="Solid" BorderWidth="1px" Font-Names="Verdana" Font-Size="0.8em" OnCreatedUser="CreateUserWizard1_CreatedUser">
13 <WizardSteps>
14 <asp:WizardStep ID="CreateUserWizardStep0" runat="server">
15 <table>
16 <tr>
17 <th>Billing Information</th>
18 </tr>
19 <tr>
20 <td>Billing Address:</td>
21 <td>
22 <asp:TextBox runat="server" ID="BillingAddress" MaxLength="50" />
23 <asp:RequiredFieldValidator runat="server" ID="RequiredFieldValidator1" ControlToValidate="BillingAddress"
24 ErrorMessage="Billing Address is required." />
25 </td>
26 </tr>
27 <tr>
28 <td>Billing City:</td>
29 <td>
30 <asp:TextBox runat="server" ID="BillingCity" MaxLength="50" Columns="15" />
31 <asp:RequiredFieldValidator runat="server" ID="RequiredFieldValidator2" ControlToValidate="BillingCity"
32 ErrorMessage="Billing City is required." />
33 </td>
34 </tr>
35 <tr>
36 <td>Billing State:</td>
37 <td>
38 <asp:TextBox runat="server" ID="BillingState" MaxLength="25" Columns="10" />
39 <asp:RequiredFieldValidator runat="server" ID="RequiredFieldValidator3" ControlToValidate="BillingState"
40 ErrorMessage="Billing State is required." />
41 </td>
42 </tr>
43 <tr>
44 <td>Billing Zip:</td>
45 <td>
46 <asp:TextBox runat="server" ID="BillingZip" MaxLength="10" Columns="10" />
47 <asp:RequiredFieldValidator runat="server" ID="RequiredFieldValidator4" ControlToValidate="BillingZip"
48 ErrorMessage="Billing Zip is required." />
49 </td>
50 </tr>
51 </table>
52 </asp:WizardStep>
53 <asp:WizardStep ID="CreateUserWizardStep1" runat="server">
54 <table>
55 <tr>
56 <th>Shipping Information</th>
57 </tr>
58 <tr>
59 <td>Shipping Address:</td>
60 <td>
61 <asp:TextBox runat="server" ID="ShippingAddress" MaxLength="50" />
62 <asp:RequiredFieldValidator runat="server" ID="RequiredFieldValidator5" ControlToValidate="ShippingAddress"
63 ErrorMessage="Shipping Address is required." />
64 </td>
65 </tr>
66 <tr>
67 <td>Shipping City:</td>
68 <td>
69 <asp:TextBox runat="server" ID="ShippingCity" MaxLength="50" Columns="15" />
70 <asp:RequiredFieldValidator runat="server" ID="RequiredFieldValidator6" ControlToValidate="ShippingCity"
71 ErrorMessage="Shipping City is required." />
72 </td>
73 </tr>
74 <tr>
75 <td>Shipping State:</td>
76 <td>
77 <asp:TextBox runat="server" ID="ShippingState" MaxLength="25" Columns="10" />
78 <asp:RequiredFieldValidator runat="server" ID="RequiredFieldValidator7" ControlToValidate="ShippingState"
79 ErrorMessage="Shipping State is required." />
80 </td>
81 </tr>
82 <tr>
83 <td>Shipping Zip:</td>
84 <td>
85 <asp:TextBox runat="server" ID="ShippingZip" MaxLength="10" Columns="10" />
86 <asp:RequiredFieldValidator runat="server" ID="RequiredFieldValidator8" ControlToValidate="ShippingZip"
87 ErrorMessage="Shipping Zip is required." />
88 </td>
89 </tr>
90 </table>
91 </asp:WizardStep>
92 <asp:CreateUserWizardStep ID="CreateUserWizardStep2" runat="server">
93 <ContentTemplate>
94 <table>
95 <tr>
96 <th>User Information</th>
97 </tr>
98 <tr>
99 <td>Username:</td>
100 <td>
101 <asp:TextBox runat="server" ID="UserName" />
102 <asp:RequiredFieldValidator runat="server" ID="RequiredFieldValidator9" ControlToValidate="UserName"
103 ErrorMessage="Username is required." />
104 </td>
105 </tr>
106 <tr>
107 <td>Password:</td>
108 <td>
109 <asp:TextBox runat="server" ID="Password" TextMode="Password" />
110 <asp:RequiredFieldValidator runat="server" ID="RequiredFieldValidator10" ControlToValidate="Password"
111 ErrorMessage="Password is required." />
112 </td>
113 </tr>
114 <tr>
115 <td>Confirm Password:</td>
116 <td>
117 <asp:TextBox runat="server" ID="ConfirmPassword" TextMode="Password" />
118 <asp:RequiredFieldValidator runat="server" ID="RequiredFieldValidator13" ControlToValidate="ConfirmPassword"
119 ErrorMessage="Confirm Password is required." />
120 </td>
121 </tr>
122 <tr>
123 <td>Email:</td>
124 <td>
125 <asp:TextBox runat="server" ID="Email" />
126 <asp:RequiredFieldValidator runat="server" ID="RequiredFieldValidator11" ControlToValidate="Email"
127 ErrorMessage="Email is required." />
128 </td>
129 </tr>
130 <tr>
131 <td>Question:</td>
132 <td>
133 <asp:TextBox runat="server" ID="Question" />
134 <asp:RequiredFieldValidator runat="server" ID="RequiredFieldValidator12" ControlToValidate="Question"
135 ErrorMessage="Question is required." />
136 </td>
137 </tr>
138 <tr>
139 <td>Answer:</td>
140 <td>
141 <asp:TextBox runat="server" ID="Answer" />
142 <asp:RequiredFieldValidator runat="server" ID="RequiredFieldValidator14" ControlToValidate="Answer"
143 ErrorMessage="Answer is required." />
144 </td>
145 </tr>
146 <tr>
147 <td colspan="2">
148 <asp:CompareValidator ID="PasswordCompare" runat="server" ControlToCompare="Password"
149 ControlToValidate="ConfirmPassword" Display="Dynamic" ErrorMessage="The Password and Confirmation Password must match."></asp:CompareValidator>
150 </td>
151 </tr>
152 <tr>
153 <td colspan="2">
154 <asp:Literal ID="ErrorMessage" runat="server" EnableViewState="False"></asp:Literal>
155 </td>
156 </tr>
157 </table>
158
159
160 <asp:SqlDataSource ID="InsertExtraInfo" runat="server"
161 ConnectionString="<%$ ConnectionStrings:Member %>"
162 InsertCommand="INSERT INTO UserAddress (Userid, BillingAddress, BillingCity,BillingState,BillingZip, ShippingAddress,ShippingCity,ShippingState,ShippingZip) VALUES (@userid, @BillingAddress, @BillingCity, @BillingState, @BillingZip, @ShippingAddress, @ShippingCity, @ShippingState, @ShippingZip)"
163 SelectCommand="SELECT UserAddress.* FROM UserAddress">
164 <InsertParameters>
165 <asp:ControlParameter Name="BillingAddress" Type="String" ControlID="BillingAddress" PropertyName="Text" />
166 <asp:ControlParameter Name="BillingCity" Type="String" ControlID="BillingCity" PropertyName="Text" />
167 <asp:ControlParameter Name="BillingState" Type="String" ControlID="BillingState" PropertyName="Text" />
168 <asp:ControlParameter Name="BillingZip" Type="String" ControlID="BillingZip" PropertyName="Text" />
169 <asp:ControlParameter Name="ShippingAddress" Type="String" ControlID="ShippingAddress" PropertyName="Text" />
170 <asp:ControlParameter Name="ShippingCity" Type="String" ControlID="ShippingCity" PropertyName="Text" />
171 <asp:ControlParameter Name="ShippingState" Type="String" ControlID="ShippingState" PropertyName="Text" />
172 <asp:ControlParameter Name="ShippingZip" Type="String" ControlID="ShippingZip" PropertyName="Text" />
173 </InsertParameters>
174 </asp:SqlDataSource>
175
176 </ContentTemplate>
177 </asp:CreateUserWizardStep>
178 <asp:CompleteWizardStep ID="CompleteWizardStep1" runat="server">
179 </asp:CompleteWizardStep>
180 </WizardSteps>
181 <NavigationButtonStyle BackColor="White" BorderColor="#CC9966" BorderStyle="Solid"
182 BorderWidth="1px" Font-Names="Verdana" ForeColor="#990000" />
183 <HeaderStyle BackColor="#FFCC66" BorderColor="#FFFBD6" BorderStyle="Solid" BorderWidth="2px"
184 Font-Bold="True" Font-Size="0.9em" ForeColor="#333333" HorizontalAlign="Center" />
185 <CreateUserButtonStyle BackColor="White" BorderColor="#CC9966" BorderStyle="Solid"
186 BorderWidth="1px" Font-Names="Verdana" ForeColor="#990000" />
187 <ContinueButtonStyle BackColor="White" BorderColor="#CC9966" BorderStyle="Solid"
188 BorderWidth="1px" Font-Names="Verdana" ForeColor="#990000" />
189 <SideBarStyle BackColor="#990000" Font-Size="0.9em" VerticalAlign="Top" />
190 <TitleTextStyle BackColor="#990000" Font-Bold="True" ForeColor="White" />
191 <SideBarButtonStyle ForeColor="White" />
192 </asp:CreateUserWizard>
193
194 </div>
195
196 </form>
197 </body>
198 </html>
199
200 Partial Class Members_Registration
Inherits System.Web.UI.Page
Protected Sub CreateUserWizard1_CreatedUser(ByVal sender As Object, ByVal e As System.EventArgs) Handles CreateUserWizard1.CreatedUser
Dim UserNameTextBox As TextBox = DirectCast(CreateUserWizardStep2.ContentTemplateContainer.FindControl("UserName"), TextBox)
Dim DataSource As SqlDataSource = DirectCast(CreateUserWizardStep2.ContentTemplateContainer.FindControl("InsertExtraInfo"), SqlDataSource)
Dim User As MembershipUser
User = Membership.GetUser(UserNameTextBox.Text)
Dim UserGUID
UserGUID = User.ProviderUserKey
Trace.Write("userid", UserGUID.ToString)
DataSource.SelectParameters.Clear()
' DataSource.InsertParameters.Clear()
DataSource.InsertParameters.Add("UserId", UserGUID.ToString)
DataSource.Insert()
End Sub
End Class