Declare Variable For All In SP
In a previous life, for each variable that we passed into a query, we would set -1 to the default for all so that when we converted it to an SP, we could query a specific dataset or or all. The following is a sample bit of code, I can not for the life of me remember how to pull back all using -1.
The following is the code that I currently have, it's a simplified version of the total SP that I am trying to use, but enough to give you the idea of what I am trying to do.
The MemberId field is a varchar(20) in the table.
Create procedure sp_GetClaims_BY_MemberID
@Memberid varchar (50)
as
Select top 100 * from [QICC-TEST].dbo.tblClaims_eligible
where Membid = @memberid
EXEC sp_GetClaims_BY_MemberID '99999999999'
The above SP works fine, I just need to be able to modify it so that I can pull back all records for all member id's, any suggestions?
I am currently working in SQL 2000.
View Complete Forum Thread with Replies
Related Forum Messages:
Declare Variable
Declare @DBName varchar(25) select @DBName = 'Production' Select @DBName = @DBName + '.dbo.'+'sysfiles' select @DBName select * from @DBName When I executes above lines of code in query analyser it give me an error like : Server: Msg 137, Level 15, State 2, Line 5 Must declare the variable '@DBName'. give me solution as soon as possible Thanks Aric
View Replies !
Must Declare The Scalar Variable
Hi 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
View Replies !
Must Declare The Scalar Variable
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 Replies !
Must Declare The Scalar 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
View Replies !
Must Declare The Scalar Variable
Hi, I found a nice tutorial on the net on how storing images to a SQL DB (http://blogs.msdn.com/jdixon/articles/495408.aspx) but have some troubles in retreiving the image back from the database. Once I click the View details link, I receive an error message "Must declare the scalar variable @..." Can someone tell me how I can define this scalar variable and/or how I can display an image from my database ? Thanks to all !!! Bart
View Replies !
Declare Variable Dynamically
I'm attempting to modify some Crosstab generating code, and I need someadvice/examples.Currently, the code uses a single string variable to store thedynamically generated query (www.johnmacintyre.ca). The problem is thatI am trying to pivot biological taxonomy information, and may end upwith a table containing over 200 columns. This takes the dynamic stringwell over the 8000char limit for variables.[color=blue]>From my understanding, the EXEC() command does not have the 8000char[/color]limit if the execution string is broken into chunks, and concatenatede.g. EXEC(sql1 + sql2 + sql3 + ...). So the solution I think I need isto:1) start a counter at the beginining of the dynamic generation2) append the counter value to the end of a string variable name3) DECLARE the new variable and attach that loop cycle of text to it,or attach each chunk of characters < 80004) build the EXEC() string by concatenating each dynamic varibleCan this be done? Should it be done? Is there a better way to addressthis type of problem?Thanks for any ideas or insightsTim Pascoe
View Replies !
Must Declare The Scalar Variable
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"
View Replies !
Declare Variable Error
Can anyone tell me why I keep getting this error? I am declaring the variable, but it's not recognizing it? What am I missing? ------error--------------- Server: Msg 137, Level 15, State 2, Procedure sp_CopyData, Line 85 Must declare the variable '@DatabaseFrom'. -----sp---- SET QUOTED_IDENTIFIER ON GO SET ANSI_NULLS ON GO create Procedure dbo.sp_CopyData (@ClientAbbrev nchar(4) ) AS DECLARE @DatabaseFrom varchar(100) Set @DatabaseFrom = @ClientAbbrev + '.dbo.tsn_ClaimStatus' -------------------------------------------------------------- delete from sherrisplayground.dbo.tsn_ClaimStatus where csclientcode = @ClientAbbrev ---Insert Data from Original table into copied table--------- Insert into [AO3AO3].sherrisplayground.dbo.tsn_ClaimStatus ( CsClientCode, ClaimStatusID, Pat, Claim, [ID], Code, [Date], ActionID, Comment2, Comment3, Comment4, [Followup Date], Checkamt, UserName) select @ClientAbbrev, ClaimStatusID, Pat, Claim, [ID], Code, [Date], ActionID, Comment2, Comment3, Comment4, [Followup Date], Checkamt, UserName from @DatabaseFrom return GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS ON GO
View Replies !
Using A Variable In SQL Script (Declare @x)
I am not sure why the following does not work... I am declaring a variable to hold a string to be used in my script. The contents of the variable looks perfect and works independently, the variable it does not work. (BTW, do you call this a variable in T-SQL too?). The idea is to pass the value to the script to generate different results sets. //Parameter DECLARE @Codes varchar(8000); SET @Codes = '''07-1110_CHA,1'',''07-1110_DCV,2'''; //Examine contents - results: '07-1110_CHA,1','07-1110_DCV,2' Select @Codes; //Works fine Select screening_cd,check_amount From accounting Where screening_cd in ('07-1110_CHA,1','07-1110_DCV,2'); //This does not work Select screening_cd,check_amount From accounting Where screening_cd in (@Codes); TIA
View Replies !
Error 137: Must Declare Variable ...
Hello, I have the following SP, which gives out a "Error 137: Must declare variable @tmp_return_tbl" error. This is the important part of the code: . . . -- DECLARE TABLE VARIABLE DECLARE @tmp_return_tbl TABLE (tID int, Text_Title nvarchar(30), Text_Body nvarchar(100)) -- fill out table variable USING A SELECT FROM ANOTHER TABLE VARIABLE -- NO PROBLEM HERE INSERT INTO @tmp_return_tbl SELECT TOP 1 * FROM @tmp_tbl ORDER BY NEWID() -- TRYING TO UPDATE A TABLE UPDATE xTable SET xTable.fieldY = xTable.fieldY + 1 WHERE xTable.tID = @tmp_return_tbl.tID --THIS PRODUCES THE ERROR . . . I know I cannot use a table variable in a JOIN without using an Alias, or use it directly in dynamic SQL (different scope) - but is this the problem here? What am I doing wrong? Your help is much appreciated.
View Replies !
Must Declare Scalar Variable @ID
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>
View Replies !
Must Declare Scalar Variable???
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?
View Replies !
Must Declare The Scalar Variable
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'
View Replies !
Where Do I Declare SQL Connection Variable In My Application
In my asp.net application , I have to open/close SQL databse connection many time, and I dont want to declare Following variable everytime I open SQL database connection Dim cntec As New SqlClient.SqlConnection(cnStrtec) Dim datec As New SqlClient.SqlDataAdapter Dim dstec As New DataSet Dim cmdtec As New SqlClient.SqlCommand so do I decalre above variable in module.vb as Public variable or is there any other way to do this. Please some one give me idea how to do this Please give me some examples thank you maxmax
View Replies !
Where Do I Declare SQL Connection Variable In My Asp.net Application
In my asp.net application , I have to open/close SQL databse connection many time, and I dont want to declare Following variable everytime I open SQL database connection Dim cntec As New SqlClient.SqlConnection(cnStrtec) Dim datec As New SqlClient.SqlDataAdapter Dim dstec As New DataSet Dim cmdtec As New SqlClient.SqlCommand so do I decalre above variable in module.vb as Public variable or is there any other way to do this. Please some one give me idea how to do this thank you maxmax
View Replies !
Error Message: Must Declare Variable @ID
I’m having trouble with a datalist. The default view is the Item Template which has an Edit button. When I click the Edit button, I run the following code (for the EditCommand of the Datalist): DataList1.EditItemIndex = e.Item.ItemIndex DataBind() It errors out with the message “Must declare variable @ID�. I’ve used this process on other pages without problem. The primary key for the recordsource that populates this datalist is a field named “AutoID�. There is another field named ID that ties these records to a master table. The list of rows returned in the datalist is based off the ID field matching a value in a dropdown list on the page (outside of the datalist). So my SQLdatasource has a parameter to match the ID field to @ID. For some reason, it's not finding it and I cannot determine why. I haven't had this issue on other pages. Here’s my markup of the SQLDataSource and the Datalist/Edit Template: <asp:SqlDataSource ID="SqlDataSource4" runat="server" ConnectionString="<%$ ConnectionStrings:SMARTConnectionString %>" DeleteCommand="DELETE FROM [tblSalesSupport] WHERE [NBID] = @NBID" InsertCommand="INSERT INTO [tblSalesSupport] ([ID], [NBNC], [NBEC], [Description], [Estimate], [CompanyID], [CompanyName], [ProjectNumber]) VALUES (@ID, @NBNC, @NBEC, @Description, @Estimate, @CompanyID, @CompanyName, @ProjectNumber)" SelectCommand="SELECT * FROM [tblSalesSupport] WHERE ([ID] = @ID)" UpdateCommand="UPDATE [tblSalesSupport] SET [ID] = @ID, [NBNC] = @NBNC, [NBEC] = @NBEC, [Description] = @Description, [Estimate] = @Estimate, [CompanyID] = @CompanyID, [CompanyName] = @CompanyName, [ProjectNumber] = @ProjectNumber WHERE [NBID] = @NBID"> <DeleteParameters> <asp:Parameter Name="NBID" Type="Int32" /> </DeleteParameters> <UpdateParameters> <asp:Parameter Name="ID" Type="Int32" /> <asp:Parameter Name="NBNC" Type="Boolean" /> <asp:Parameter Name="NBEC" Type="Boolean" /> <asp:Parameter Name="Description" Type="String" /> <asp:Parameter Name="Estimate" Type="Decimal" /> <asp:Parameter Name="CompanyID" Type="Int32" /> <asp:Parameter Name="CompanyName" Type="String" /> <asp:Parameter Name="ProjectNumber" Type="String" /> <asp:Parameter Name="NBID" Type="Int32" /> </UpdateParameters> <SelectParameters> <asp:ControlParameter ControlID="ddlFind" Name="ID" PropertyName="SelectedValue" Type="Int32" /> </SelectParameters> <InsertParameters> <asp:Parameter Name="ID" Type="Int32" /> <asp:Parameter Name="NBNC" Type="Boolean" /> <asp:Parameter Name="NBEC" Type="Boolean" /> <asp:Parameter Name="Description" Type="String" /> <asp:Parameter Name="Estimate" Type="Decimal" /> <asp:Parameter Name="CompanyID" Type="Int32" /> <asp:Parameter Name="CompanyName" Type="String" /> <asp:Parameter Name="ProjectNumber" Type="String" /> </InsertParameters> </asp:SqlDataSource> <asp:DataList CssClass="MainFormDisplay" ID="DataList1" runat="server" DataKeyField="NBID" DataSourceID="SqlDataSource1" width="100%"> <HeaderTemplate>….</HeaderTemplate> <ItemTemplate>….</ItemTemplate> <EditItemTemplate> <table border="0" style="width: 100%"> <tr class="MainFormDisplay" valign="top"> <td colspan="8"> <asp:TextBox ID="txtNBID" runat="server" Text='<%# Eval("NBID") %>' Visible="true"></asp:TextBox> <asp:TextBox ID="txtID" runat="server" Text='<%# Bind("ID") %>' Visible="True"></asp:TextBox></td> </tr> <tr class="MainFormDisplay"> <td valign="top" style="width: 100px"><asp:Checkbox ID="chkNBNC" runat="server" Checked='<%# Bind("NBNC") %>' /></td> <td style="width: 100"><asp:CheckBox ID="chkNBEC" runat="server" Checked='<%# Bind("NBEC") %>' Width="100px" /></td> <td style="width: 100px"><asp:TextBox ID="txtCompanyName" runat="server" Text='<%# Bind("CompanyName")%>' Width="100px"></asp:TextBox></td> <td style="width: 100px"><asp:TextBox ID="txtProjectNumber" runat="server" Text='<%# Bind("ProjectNumber") %>' Width="100px"></asp:TextBox></td> <td style="width: 100px"><asp:TextBox ID="txtDescription" runat="server" Text='<%# Bind("Description") %>' Width="100px"></asp:TextBox></td> <td style="width: 100px"><asp:TextBox ID="txtEstimate" runat="server" Text='<%# Bind("Estimate","{0:N2}") %>' Width="100px"></asp:TextBox></td> <td style="width: 55px"><asp:CheckBox ID="ckDeleteFlag" runat="server" /></td> <td style="width: 100px"><asp:Button ID="ItemSaveButton" runat="server" CommandName="Update" Text="Save" /> <asp:Button ID="ItemCancelButton" runat="server" CommandName="Cancel" Text="Cancel" /></td> </tr> </table> </EditItemTemplate> </asp:DataList><br />
View Replies !
Must Declare The Scalar Variable @RefNo
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 Replies !
Must Declare The Scalar Variable Error
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
View Replies !
Must Declare The Scalar Variable In Sql 2005
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".
View Replies !
AddWithValue : Must Declare The Scalar Variable &"@…
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();
View Replies !
Must Declare Scalar Variable Error... Need Help URGENT!
doing insert using this method Dim insertSQL As String insertSQL = "Insert into " & myDB & " (student_name, student_passport, student_rcnumber, " & _ "test_level, test_venue1, test_venue2, test_row, test_column, " & _ "student_sex, student_age, student_dob,student_country, student_state, " & _ "guardian_name, guardian_passport, guardian_relation, " & _ "guardian_address1, guardian_address2, guardian_postcode, " & _ "guardian_homephone, guardian_mobilephone, guardian_otherphone, " & _ "payment, remarks, student_att) " & _ "" & _ "Values(@student_name, @student_passport, @student_rcnumber, " & _ "@test_level, @test_venue1, @test_venue2, @test_row, @test_column, " & _ "@student_sex, @student_age, @student_dob,@student_country, @student_state, " & _ "@guardian_name, @guardian_passport, @guardian_relation, " & _ "@guardian_address1, @guardian_address2, @guardian_postcode, " & _ "@guardian_homephone, @guardian_mobilephone, @guardian_otherphone, " & _ "@payment, @remarks, @student_att)" Dim conn As New OleDbConnection(myNorthWind) Dim cmd As New OleDbCommand(insertSQL, conn) cmd.Parameters.AddWithValue("@student_name", txtName.Text.Trim) cmd.Parameters.AddWithValue("@student_passport", txtPassport.Text.Trim) cmd.Parameters.AddWithValue("@student_rcnumber", txtReceipt.Text.Trim) cmd.Parameters.AddWithValue("@test_level", txtTestLevel.Text) cmd.Parameters.AddWithValue("@test_venue1", txtVenue1.Text.Trim) cmd.Parameters.AddWithValue("@test_venue2", txtVenue2.Text.Trim) cmd.Parameters.AddWithValue("@test_row", dropAlpha.SelectedItem) cmd.Parameters.AddWithValue("@test_column", dropNumeric.SelectedItem) cmd.Parameters.AddWithValue("@student_sex", dropSex.SelectedItem) cmd.Parameters.AddWithValue("@student_age", dropAge.SelectedItem) '------------Assembly Date Format Dim dob As New Date dob = dropDay.SelectedItem & "/" & dropMonth.SelectedItem & "/" & dropYear.SelectedItem dob = String.Format("{0:MM/dd/yyyy}", dob) cmd.Parameters.AddWithValue("@student_dob", dob) '------------End Assembly cmd.Parameters.AddWithValue("@student_country", txtCountry.Text) cmd.Parameters.AddWithValue("@student_state", txtState.Text) cmd.Parameters.AddWithValue("@guardian_name", txtGdName.Text.Trim) cmd.Parameters.AddWithValue("@guardian_passport", txtGdPassport.Text.Trim) cmd.Parameters.AddWithValue("@guardian_relation", txtGdRelation.Text.Trim) cmd.Parameters.AddWithValue("@guardian_address1", txtAddress1.Text.Trim) cmd.Parameters.AddWithValue("@guardian_address2", txtAddress2.Text.Trim) cmd.Parameters.AddWithValue("@guardian_postcode", txtPostal.Text) cmd.Parameters.AddWithValue("@guardian_homephone", txtHome.Text) cmd.Parameters.AddWithValue("@guardian_mobilephone", txtMobile.Text) cmd.Parameters.AddWithValue("@guardian_otherphone", txtOther.Text) cmd.Parameters.AddWithValue("@payment", txtPayment.Text.Trim) cmd.Parameters.AddWithValue("@remarks", txtRemarks.Text.Trim) If rdbAbsent.Checked = True Then cmd.Parameters.AddWithValue("@student_att", 0) ElseIf rdbPresent.Checked = True Then cmd.Parameters.AddWithValue("@student_att", 1) End If conn.Open() cmd.ExecuteNonQuery() conn.Close()Then i got this error must declar scalar variable @student_name need some enlighten plzz T_T
View Replies !
Asp.net 2.0 Registration: Must Declare The Scalar Variable '@BillingAddress'
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
View Replies !
Using A Date Function To Declare A Variable Used In A SQL Query
Hi all can you help me, I know that I am doing some thing wrong. What I need to do is set a variable to the current date so I can use it in a SQL query to an access database. This is what I have so far <script runat="server""> Sub Page_Load dim --all the variables for my sql connections-- dim ff1 As Date then my sql connection and queries sql="SELECT FullRate, " & ff1 &" FROM table1 WHERE hotelnumber = " & hotel This works is I set ff1 as a string and specify the string (my column headings are set as dates in my table) dim ff1 As string ff1="30/01/2006" but I need ff1 to be the current date and is I use ff1 As date it returns time and date Is there any way to set ff1 to the current date in this format "dd/mm/yyyy" Any help is greatly appreciated
View Replies !
SQLServer Declare New Variable Based On Another Variables Value
Hi, I'm a complete newbie to SQLServer and t-sql generally. What I want to do is create a new variable in a stored procedure based upon the value of another variable. eg in the loop below I want to create 10 new variables, called @var0,@var1,@var2 ...@var9 declare @varname nvarchar(10) declare @i integer select @i=0 while @i<10 begin set @varname = cast(('@var'+cast(@i as char)) as nvarchar(10)) set @i=@i+1 end Does anyone know of a way to do this?
View Replies !
Using A Declare Variable To Represent A Data Type Length
I'm trying to use a variable set by DECLARE to represent a data type length with CAST. Don't ask why. Here is what I'm got: declare @n int set @n = (select max(len(col_1)) from tblOne) select cast(Col_2 as varchar(@n)) from tblOne This should make Col_2 the length of the longest entry in Col_1. It does not work. I must use a set number for the length after the data type like this: declare @n int set @n = (select max(len(col_1)) from tblOne) select cast(Col_2 as varchar(28)) -- (28 is longest current entry in Col_1) from tblOne Is there a way around this? I'm new to SQL.
View Replies !
Crystal Report Error Message (Must Declare The Variable)
hi there, I have a crystal report that calls a stored procedure from SQL Server 2000. The only parameter I have is @A the SP is: -------------- CREATE PROCEDURE Final @A INT AS Declare @SQL VARCHAR(2000) SET @SQL = 'SELECT * FROM Schools where Areano = @A' EXEC(@SQL) GO ------------- From the standard report creation wizard, i choose the data (SP). and select the SP, then i got window to enter a parameter values for @A, and i check ( Set to Null value), then press OK, the following error occured Must Declare the variable @A Please help me! Best Regards
View Replies !
SQL Reporting Services 2005: Must Declare Scalar Variable
I have a report that calls a stored procedure that builds a temp table and then runs a select that joins on that temp table. The only parameter I have is @Name, an optional parameter that can be filled or NULL. I keep getting the error "An error has occurred during local report processing. An error has occurred during report processing. Query execution failed for data set. Must declare the scalar variable "@Name". I have checked to make sure this parameter is connected to the dataset by looking at the parameter tab. I've searched on posts but haven't seen an answer. Does anyone have any idea what this could be about and how to fix it?
View Replies !
[SQLServer 2000 Driver For JDBC] Must Declare The Variable '@P2GROUP'
Hello,I am working with the driver JDBC and I have a strange error that I can't find in Google for example.Caused by: com.seeburger.smarti.util.SmartiEJBException: nested exception is: com.seeburger.smarti.dao.DAOSysException: java.sql.SQLException: [Microsoft][SQLServer 2000 Driver for JDBC][SQLServer]Must declare the variable '@P2GROUP'.I don't know what is this variable '@P2GROUP'.Does anybody know it ?Thanks
View Replies !
Creating CSV Files Using BCP && Stored Procedures:BCP Executed From T-SQL Using Xp_cmdshell-How To Declare The Scalar Variable @@
Hi all, I tried to create a CSV file using Bulk Copy Program (BCP) and Stored Procedures: BCP executed from T-SQL using xp_cmdshell. I have the following sql code executed in my SQL Server Management Studio Express and error message: --scBCPcLabResults.sql-- declare @sql varchar(8000) select @sql = 'bcp ChDbLabResults out c:cpChDbLabResults.txt -c -t, -T -S' + @@.SQLEXPRESS exec master..xp_cmdshell @sql Msg 137, Level 15, State 2, Line 3 Must declare the scalar variable "@@". ========================================================================================= --scBCPcLabResults.sql-- declare @sql varchar(8000) select @sql = 'bcp ChDbLabResults out c:cpChDbLabResults.txt -c -t, -T -S' + @@SQLEXPRESS exec master..xp_cmdshell @sql Msg 137, Level 15, State 2, Line 3 Must declare the scalar variable "@@SQLEXPRESS". =================================================================== I copied this set of code from a tutorial article that says "@@servername". My Sql Server is SQLEXPRESS, so I put @@.SQLEXPRESS or @@SQLEXPRESS in the code of scBCPcLabResults.sql. I do not know why I got an error {Must declare the scalar variable "@@"} or {Must declare the scalar variable "@@SQLEXPRESS"}!!!??? Please help and advise me how to solve this problem. Thanks in advance, Scott Chang
View Replies !
Incorrect Syntax Near 'nvarchar'. Must Declare The Scalar Variable &"@CODE&".
I have this issue and I can not figure out the problem. I have 4 other forms from the same database using practly the same code, slight variations based on datavalidation requirements. IIS6 SQL Express 2005. I have tried to defint eh colum for CODE as a bound filed and as a templated field. I get the same error.ASPX Page <%@ Page Language="C#" MasterPageFile="~/Site.master" AutoEventWireup="true" CodeFile="MaintainBSP.aspx.cs" Inherits="MaintainBSP" Title="Maintain BSP Codes" %> <%@ MasterType VirtualPath="~/Site.master" %> <asp:Content ID="Content1" ContentPlaceHolderID="ContentPlaceHolder1" Runat="Server"><br /> <table class="mainTable" cellspacing="0" cellpadding="3" align="center"> <tr><td class="mainTableTitle">BSP Codes</td></tr> <tr><td> <table align="center"> <tr> <td><asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False"BorderColor="Silver" BorderStyle="Solid" BorderWidth="1px" HorizontalAlign="Center" CellPadding="3"DataKeyNames="CODE" DataMember="DefaultView" DataSourceID="SqlDataSource1" OnRowEditing="GridView1_OnRowEditing" OnRowCancelingEdit="GridView1_EndEdit" OnRowUpdated="GridView1_EndEdit"> <Columns> <asp:CommandField ShowEditButton="True" EditText="Edit" CancelText="Cancel" UpdateText="Update" HeaderStyle-CssClass="rptTblTitle" > <HeaderStyle CssClass="rptTblTitle"></HeaderStyle> </asp:CommandField> <asp:BoundField DataField="CODE" HeaderText="Code" ReadOnly="true" HeaderStyle-CssClass="rptTblTitle" /> <asp:TemplateField HeaderText="Bottle Size" SortExpression="Btl Sz"> <EditItemTemplate> <asp:TextBox ID="TextBox1" MaxLength="10" Columns="10" runat="server" Text='<%# Bind("[BOTTLE$SIZE]") %>'></asp:TextBox> <asp:RequiredFieldValidator ID="RequiredFieldValidator1" runat="server" ErrorMessage="Bottle Size is a required field." Text="*" ControlToValidate="TextBox1"></asp:RequiredFieldValidator><asp:RegularExpressionValidator ID="RegularExpressionValidator1" runat="server" ErrorMessage="Bottle size must be a number followed by 'ML' or 'L'" Text="*" ControlToValidate="TextBox1" ValidationExpression="[0-9.]+(ML|L)"></asp:RegularExpressionValidator> </EditItemTemplate> <ItemTemplate> <asp:Label ID="Label1" runat="server" Text='<%# Bind("[BOTTLE$SIZE]") %>'></asp:Label> </ItemTemplate> <HeaderStyle CssClass="rptTblTitle" /> </asp:TemplateField> <asp:TemplateField HeaderText="Labeled" SortExpression="Labeled"> <EditItemTemplate> <asp:TextBox ID="TextBox2" MaxLength="1" Columns="2" runat="server" Text='<%# Bind("LABELED") %>'></asp:TextBox> <asp:RequiredFieldValidator ID="RequiredFieldValidator2" runat="server" ErrorMessage="Labeled is a required field" Text="*" ControlToValidate="TextBox2"></asp:RequiredFieldValidator> </EditItemTemplate> <ItemTemplate> <asp:Label ID="Label2" runat="server" Text='<%# Bind("LABELED") %>'></asp:Label> </ItemTemplate> <HeaderStyle CssClass="rptTblTitle" /> </asp:TemplateField> <asp:TemplateField HeaderText="Bottles Per Case" SortExpression="Btls Per Case"> <EditItemTemplate> <asp:TextBox ID="TextBox3" Columns="4" runat="server" Text='<%# Bind("[BOTTLES$PER$CASE]") %>'></asp:TextBox> <asp:RequiredFieldValidator ID="RequiredFieldValidator3" runat="server" ErrorMessage="Bottles per case must be a whole number." Text="*" ControlToValidate="TextBox3"></asp:RequiredFieldValidator> </EditItemTemplate> <ItemTemplate> <asp:Label ID="Label3" runat="server" Text='<%# Bind("[BOTTLES$PER$CASE]") %>'></asp:Label> </ItemTemplate> <HeaderStyle CssClass="rptTblTitle" /> </asp:TemplateField> <asp:TemplateField HeaderText="Liters Per Case" SortExpression="Ltrs Per Case"> <EditItemTemplate> <asp:TextBox ID="TextBox4" MaxLength="8" Columns="8" runat="server" Text='<%# Bind("[LITERS$PER$CASE]") %>'></asp:TextBox> <asp:RequiredFieldValidator ID="RequiredFieldValidator4" runat="server" ErrorMessage="Liters per case must be a number." ControlToValidate="TextBox4" Text="*"></asp:RequiredFieldValidator> </EditItemTemplate> <ItemTemplate> <asp:Label ID="Label4" runat="server" Text='<%# Bind("[LITERS$PER$CASE]") %>'></asp:Label> </ItemTemplate> <HeaderStyle CssClass="rptTblTitle" /> </asp:TemplateField> <asp:CommandField ShowDeleteButton="True" DeleteText="Delete" HeaderStyle-CssClass="rptTblTitle" > <HeaderStyle CssClass="rptTblTitle"></HeaderStyle> </asp:CommandField> </Columns> </asp:GridView> <table id="tblAddBSP" runat="server" width="100%"> <tr><td colspan="2" align="center"><asp:Label ID="lblAddMessage" runat="server" Text="" style="color:Red;font-weight:bold;"/></td></tr> <tr> <td style="font-weight:bold;">Code</td> <td><asp:TextBox ID="txtAddCode" runat="server" MaxLength="1" Columns="2"></asp:TextBox><asp:RequiredFieldValidator ID="rfv_txtAddCode" runat="server" ErrorMessage="Please Supply a BSP Code." Text="*" ControlToValidate="txtAddCode" SetFocusOnError="True"></asp:RequiredFieldValidator> </td> </tr> <tr> <td style="font-weight:bold;">Bottle Size</td> <td><asp:TextBox ID="txtAddSize" runat="server" MaxLength="10" Columns="10"></asp:TextBox> <asp:RequiredFieldValidator ID="RequiredFieldValidator1" runat="server" ErrorMessage="Bottle Size is a required field." Text="*" ControlToValidate="txtAddSize"></asp:RequiredFieldValidator><asp:RegularExpressionValidator ID="RegularExpressionValidator1" runat="server" ErrorMessage="Bottle size must be a number followed by 'ML' or 'L'" Text="*" ControlToValidate="txtAddSize" ValidationExpression="[0-9.]+(ML|L)"></asp:RegularExpressionValidator> </td> </tr> <tr> <td style="font-weight:bold;">Labeled</td> <td><asp:TextBox ID="txtAddLabeled" runat="server" MaxLength="1" Columns="2"></asp:TextBox></td> </tr> <tr> <td style="font-weight:bold;">Bottles Per Case</td> <td><asp:TextBox ID="txtAddBottlesPerCase" runat="server" MaxLength="4" Columns="4"></asp:TextBox></td> </tr> <tr> <td style="font-weight:bold;">Liters Per Case</td> <td><asp:TextBox ID="txtAddLitersPerCase" runat="server" MaxLength="8" Columns="8"></asp:TextBox></td> </tr><tr><td colspan="2" align="right"><asp:Button ID="btnAddNew" runat="server" Text="Add BSP" onclick="btnAddNew_Click" /></td></tr> </table> </td> </tr> </table> </td></tr> </table><asp:ValidationSummary ID="ValidationSummary1" runat="server" ShowMessageBox="True" ShowSummary="False" /><asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:SqlConnectionString %>" ProviderName="<%$ ConnectionStrings:SqlConnectionString.ProviderName %>"DeleteCommand="DELETE FROM BSP WHERE CODE = @CODE" InsertCommand="INSERT INTO BSP (CODE, BOTTLE$SIZE, LABELED, BOTTLES$PER$CASE, LITERS$PER$CASE) VALUES (@CODE, @BOTTLE$SIZE, @LABELED, @BOTTLES$PER$CASE, @LITERS$PER$CASE)" SelectCommand="SELECT CODE, BOTTLE$SIZE, LABELED, BOTTLES$PER$CASE, LITERS$PER$CASE FROM BSP order by CODE" UpdateCommand="UPDATE BSP SET BOTTLE$SIZE = @BOTTLE$SIZE, LABELED = @LABELED, BOTTLES$PER$CASE = @BOTTLES$PER$CASE, LITERS$PER$CASE = @LITERS$PER$CASE WHERE [CODE] = @CODE"> <UpdateParameters> <asp:Parameter Name="BOTTLE$SIZE" type="String" /> <asp:Parameter Name="LABELED" type="Char" /> <asp:Parameter Name="BOTTLES$PER$CASE" type="Int32" /> <asp:Parameter Name="LITERS$PER$CASE" type="Decimal" /> <asp:Parameter Name="CODE" type="Char" /> </UpdateParameters> <InsertParameters> <asp:ControlParameter ControlID="txtAddSize" Name="BOTTLE$SIZE" type="String" /> <asp:ControlParameter ControlID="txtAddLabeled" Name="LABELED" type="Char" /> <asp:ControlParameter ControlID="txtAddBottlesPerCase" Name="BOTTLES$PER$CASE" type="Int32" /> <asp:ControlParameter ControlID="txtAddLitersPerCase" Name="LITERS$PER$CASE" type="Decimal" /> <asp:ControlParameter ControlID="txtAddCode" Name="CODE" type="Char" /> </InsertParameters> </asp:SqlDataSource> </asp:Content> CODE BEHIND using System; using System.Collections; using System.Configuration; using System.Data; using System.Web; using System.Web.Security; using System.Web.UI; using System.Web.UI.HtmlControls; using System.Web.UI.WebControls; using System.Web.UI.WebControls.WebParts; using reports; using System.Data.SqlClient; public partial class MaintainBSP : System.Web.UI.Page {protected void Page_Load(object sender, EventArgs e) {Master.ActiveTab = Helpers.Tabs.Admin;Security.CheckPageAccess(Security.AccessTypes.Administrator); }protected void GridView1_OnRowEditing(Object sender, GridViewEditEventArgs e) {tblAddBSP.Visible = false; }protected void GridView1_EndEdit(Object sender, EventArgs e) {tblAddBSP.Visible = true; }protected void btnAddNew_Click(object sender, EventArgs e) { try { SqlDataSource1.Insert();lblAddMessage.Text = "Add BSP '" + txtAddCode.Text + "' successful."; txtAddCode.Text = String.Empty;txtAddSize.Text = String.Empty; txtAddLabeled.Text = String.Empty;txtAddBottlesPerCase.Text = String.Empty;txtAddLitersPerCase.Text = String.Empty; }catch (SqlException ex) {if (ex.Number == 2627) { lblAddMessage.Text = "The code '" + txtAddCode.Text + "' is already in the database.<br />Select another code for this BSP.";txtAddCode.Text = String.Empty; } elselblAddMessage.Text = ex.Number + " - " + ex.ErrorCode.ToString() + " - " + ex.Message; } catch {lblAddMessage.Text = "There was an issue inserting the BSP Code '" + txtAddCode.Text + "'. Please check the values and try again."; } } }
View Replies !
Must Declare The Scalar Variable &"@CategoryID&". Error In Stored Procdure.
Hi People, i'm having some trouble with a stored proceddure used for an updat from a Formview. Error message i'm getting is Must declare the scalar variable "@CategoryID". I can't seem to work out why this is occuring and not allowing my proc to run properly. If anyone could help me that would be great :-) Here is the whole store procedure. ALTER PROCEDURE dbo.DeluxeGuideAdmin_Update @ID int,@CategoryID varchar(10), @RegionID int,@CompanyName nvarchar(25), @Email nvarchar(25),@PDFFront nvarchar(50), @PDFBack nvarchar(50),@ThumbFront nvarchar(50), @ThumbBack nvarchar(50),@Notes nvarchar(max) AS DECLARE @SQL varchar(1000) SET @SQL = 'UPDATE DeluxeGuide SET CategoryID = @CategoryID, RegionID = @RegionID, CompanyName = @CompanyName, Email = @Email, Notes = @Notes'IF (@PDFFront IS NOT NULL) OR (@PDFFront != '') SET @SQL = @SQL + ', PDFFront = @PDFFront'IF (@PDFBack IS NOT NULL) OR (@PDFBack != '') SET @SQL = @SQL + ', PDFBack = @PDFBack'IF (@ThumbFront IS NOT NULL) OR (@ThumbFront != '') SET @SQL = @SQL + ', ThumbFront = @ThumbFront'IF (@ThumbBack IS NOT NULL) OR (@ThumbBack != '') SET @SQL = @SQL + ', ThumbBack = @ThumbBack' SET @SQL = @SQL + ' WHERE (ID = @ID)' Print '@SQL = ' + @SQLEXEC(@SQL) RETURN
View Replies !
Receive &&"Must Declare The Variable&&" When Upgrading To Reporting Services 2005
We are in the process of migrating our databases to SQL Server 2005 and our Reporting Services Reports to 2005. We have been doing this in a phased approach with excellent success. However, I have a set of Reporting Services 2000 reports that are reading from a SQL Server 7.0 database. If possible, I would like to migrate the reports before we migrate the database (we're not ready to migrate the database yet). When I converted the reports to Reporting Services 2005, I first received an error message regarding my data source. Basically the message says anything developed in Visual Studio 2005 using the Microsoft SQL Server connection type cannot connect to a database prior to Microsoft SQL Server 2000. So I switched the connection string to be a OLE DB type. Well ... the reports contain parameters (i.e. @plant, @employee, etc). So when I attempt to run the query, I get a message saying "Must declare the variable '@plant'". I have searched for a work around until we migrate the database but I am coming up empty. Is there a way for me to run a report with parameters from Reporting Services 2005 to a SQL Server database that is prior to SQL Server 2000? Thanks in advance.
View Replies !
Need Help With SQL UPDATE...and The Error: Must Declare The Scalar Variable &"@UserName&".
I am trying to update a field in a pre-existing record in my database. This update is supposed to happen when the protected sub entitled "PictureUpload" is called by button click. I listed the code immediately below and then I listed the error that I am getting further below that. Does anybody know what I am doing wrong and why I am getting this error? Thanks in advance and forgive my newbie ignorance! Here is a portion of the Protected Sub that I am using in an attempt to update a field in a pre-existing record... Protected Sub PictureUpload(ByVal sender As Object, ByVal e As System.EventArgs) Dim ImageUploaded As Integer = 1 srcprofiles_BasicProperties.UpdateParameters("ImageUploaded").DefaultValue = ImageUploaded srcprofiles_BasicProperties.Update() End Sub Here is the SqlDataSource control I included on the page with (what I hope is) appropriate formatting... <asp:SqlDataSource ID="srcprofiles_BasicProperties" runat="server" ConnectionString="Data Source=.SQLEXPRESS;AttachDbFilename=|DataDirectory|UserProfilesDB.mdf;Integrated Security=True;User Instance=True" ProviderName="System.Data.SqlClient" SelectCommand="SELECT * FROM [profiles_BasicProperties] WHERE ([UserName] = @UserName)" UpdateCommand="UPDATE [profiles_BasicProperties] SET [ImageUploaded] = @ImageUploaded WHERE ([UserName] = @UserName)"> <SelectParameters> <asp:Parameter DefaultValue="imageuploaded01" Name="UserName" Type="String" /> </SelectParameters> <UpdateParameters> <asp:Parameter Name="ImageUploaded" Type="Int32" /> </UpdateParameters></asp:SqlDataSource> ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^...and now the error...^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ Server Error in '/PC_Dev' Application. Must declare the scalar variable "@UserName". Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. Exception Details: System.Data.SqlClient.SqlException: Must declare the scalar variable "@UserName".Source Error: Line 164: Dim ImageUploaded As Integer = 1 Line 165: srcprofiles_BasicProperties.UpdateParameters("ImageUploaded").DefaultValue = ImageUploaded Line 166: srcprofiles_BasicProperties.Update() Line 167: Line 168: Source File: C:UsersMDocumentsPC_DevProfiles_BuildProfile.aspx Line: 166 Stack Trace: [SqlException (0x80131904): Must declare the scalar variable "@UserName".] System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) +859322 System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) +736198 System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) +188 System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) +1959 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) +903 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) +415 System.Data.SqlClient.SqlCommand.ExecuteNonQuery() +135 System.Web.UI.WebControls.SqlDataSourceView.ExecuteDbCommand(DbCommand command, DataSourceOperation operation) +401 System.Web.UI.WebControls.SqlDataSourceView.ExecuteUpdate(IDictionary keys, IDictionary values, IDictionary oldValues) +721 System.Web.UI.WebControls.SqlDataSource.Update() +17 ASP.profiles_buildprofile_aspx.PictureUpload(Object sender, EventArgs e) in C:UsersMDocumentsPC_DevProfiles_BuildProfile.aspx:166 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) +5102
View Replies !
Stored Procedure - Must Declare The Scalar Variable &"@var1&".
Hi all, when i run this query i get the following error "Must declare the scalar variable "@var1". What am i doing wrong? Code: set ANSI_NULLS ON set QUOTED_IDENTIFIER ON GO ALTER PROCEDURE [dbo].[admin3c] -- Add the parameters for the stored procedure here @tableName varchar(100), @authorityId varchar(50) AS BEGIN SET NOCOUNT ON; Declare @SQL VarChar(2000), @var1 varchar(50), @var2 int, @total int SELECT @SQL = ' select@var1 = name, @var2 = sum(age), @total = count(*) from dbo.'+@tableName+' where authority='+@authorityId+' group by name' Exec ( @SQL) select @total - @var2 END
View Replies !
&&"Must Declare The Scalar Variable&&" In Table-valued Function
Hi, I'm having trouble with this multi-statement table-valued function: ALTER FUNCTION MakeArDetail ( -- Add the parameters for the function here @dateStart DATETIME, @dateEnd DATETIME ) RETURNS @arDetail TABLE ( Insurer VARCHAR(50), NABP INT DEFAULT 0, Claim MONEY DEFAULT 0, Payment MONEY DEFAULT 0, NumRx CHAR(7), PatientName VARCHAR(50), Paid030 MONEY DEFAULT 0, Paid3160 MONEY DEFAULT 0, Paid6190 MONEY DEFAULT 0, Paid91120 MONEY DEFAULT 0, Paid121 MONEY DEFAULT 0 ) AS BEGIN DECLARE @arTemp TABLE ( Insurer VARCHAR(50), NABP INT DEFAULT 0, Claim MONEY DEFAULT 0, Payment MONEY DEFAULT 0, NumRx CHAR(7), PatientName VARCHAR(50), Paid030 MONEY DEFAULT 0, Paid3160 MONEY DEFAULT 0, Paid6190 MONEY DEFAULT 0, Paid91120 MONEY DEFAULT 0, Paid121 MONEY DEFAULT 0 ) INSERT INTO @arTemp SELECT DISTINCT Insurer,NABP,0,0,NumRx,Patient,0,0,0,0,0 FROM Pims; UPDATE @arTemp SET Claim = (SELECT SUM(Pims.AmtReq) FROM Pims WHERE Pims.Insurer = @arTemp.Insurer AND Pims.NABP = @arTemp.NABP AND Pims.NumRx = @arTemp.NumRx ); INSERT INTO @arDetail SELECT * FROM @arTemp RETURN END GO I get Msg 137, Level 15, State 2, Procedure MakeArDetail, Line 43 Must declare the scalar variable "@arTemp". I don't understand why SQL thinks @arTemp is a scalar variable which has to be declared. If I don't include the UPDATE command the thing works.
View Replies !
Updating Data In Database VB Code Problem, &" Must Declare Scalar Variable&"
I get a Must Declare Scalar Variable for CompanyName error. Please help. Thank you. datasource.ConnectionString = ConfigurationManager.ConnectionStrings("ConnString").ToString() datasource.UpdateCommand = ("UPDATE Company SET [CompanyName] = @CompanyName), = @Email, [PhoneNumber] = @PhoneNumber, [WebsiteName] = @WebsiteName WHERE ([CompanyID] = " & Request.QueryString("CID"))datasource.UpdateParameters.Add("@CompanyName", txtName.Text) datasource.UpdateParameters.Add("@Email", txtEmail.Text)datasource.UpdateParameters.Add("@PhoneNumber", txtPhoneNumber.Text) datasource.UpdateParameters.Add("@WebsiteName", txtWebsite.Text) datasource.Update()
View Replies !
System.Data.SqlClient.SqlException: Must Declare The Scalar Variable &"@username&".
Im trying to insert some values into a table but i get the following error: System.Data.SqlClient.SqlException: Must declare the scalar variable "@username". the strange thing is that the variable username is declared at the beginning and acording to my debugger the variable username has a value (that it gets from a textbox when a button is pressed). Here is my code so please feel free to point out what im doing wrong. Im a beginner to using asp.net.1 protected void ButtonSubmit_Click(object sender, EventArgs e) 2 { 3 string @username = TextBoxUsername.Text; 4 string @company = TextBoxCompany.Text; 5 string @password = TextBoxPassword.Text; 6 string @mail = TextBoxMail.Text; 7 string @adr = TextBoxAdr.Text; 8 string @phone = TextBoxPhone.Text; 9 string @contact = TextBoxContact.Text; 10 string myConnectionString; 11 12 myConnectionString = @"Data Source=.SQLEXPRESS;AttachDbFilename=C:UsersalhoDocumentsIntrapointWebApp_DataIntrapoint.mdf;Integrated Security=True;Connect Timeout=30;User Instance=True"; 13 14 SqlConnection myConnection = new SqlConnection(myConnectionString); 15 string myInsertQuery = "INSERT INTO company (username, password, company, mail, adr, phone, contact) Values(@username, @password, @company, @mail, @adr, @phone, @contact)"; 16 SqlCommand myCommand = new SqlCommand(myInsertQuery); 17 myCommand.Connection = myConnection; 18 myConnection.Open(); 19 myCommand.ExecuteNonQuery(); 20 myCommand.Connection.Close(); 21 22 }
View Replies !
Help With Inserting A New Record Into Database - Error Must Declare The Scalar Variable &"@BookMarkArrayA&".
Hi, Can anybody help me with this, I've got a simple program to add a new record to a table (2 items ID - Integer and Program - String) that matches all examples I can find, but when I run it I get the error : Must declare the scalar variable "@BookMarkArrayA". when it reaches the .insert command, I've tried using a local variable temp in place of the array element and .ToString , but still get the same error This is the code : Public Sub NewCustomer() Dim temp As String = " " Dim ID As Integer = 1 'Restore the array from the view state BookMarkArrayA = Me.ViewState("BookMarkArrayA") temp = BookMarkArrayA(6) Dim Customer As SqlDataSource = New SqlDataSource() Customer.ConnectionString = ConfigurationManager.ConnectionStrings("CustomerConnectionString").ToString() Customer.InsertCommand = "INSERT INTO [Table1] ([ID],[Program]) VALUES (@ID, @BookMarkArrayA(6))" Customer.InsertParameters.Add ("ID", ID) Customer.InsertParameters.Add ("Program", @BookMarkArrayA(6)) Customer.Insert() End Sub Cheers Ken
View Replies !
Must Declare The Scalar Variable &"@UserName&" ERROR
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
View Replies !
Error: 'Must Declare The Scalar Variable &"@StartDate&".'
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
View Replies !
Must Declare The Scalar Variable &"@company&".
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
View Replies !
Must Declare The Scalar Variable &"@recCount&".
Hi,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 Replies !
Declare The Scalar Variable &&"@average&&"
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
View Replies !
Must Declare The Scalar Variable &&"@&&" Error
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 "@". ------------------------------
View Replies !
Must Declare The Scalar Variable &"@row_number&".
Hi Everyone I have the following stored procedure below, it gives the following error when I am trying to run it: Must declare the scalar variable "@row_number" . Other stored procedures I have run fine. STORED PROCEDURE set ANSI_NULLS ON set QUOTED_IDENTIFIER OFF GO ALTER PROCEDURE [sp_find_patients] @AccessCode varchar(500), @patientHospNo varchar(255), @patientFirstName varchar(255), @patientLastName varchar(255), @irow_number int As declare @sql varchar(8000) declare @row_number int SET @row_number = @irow_number set @AccessCode = "'" + replace(@AccessCode,'-',"','") + "'" SELECT @sql = 'SELECT TOP 1 * FROM (SELECT ROW_NUMBER() OVER (ORDER BY category.category,sub_category.sub_category, patientFirstName,patientLastName) AS RowNumber, * FROM patient INNER JOIN sub_category ON patient.sub_categoryID = sub_category.sub_categoryID INNER JOIN category ON sub_category.categoryID = category.categoryID) _myResults WHERE patient.sub_categoryID in (' + @AccessCode + ') ' IF @patientHospNo <> '' SELECT @sql = @sql + 'AND patientHospNo = '''+@patientHospNo+''' ' IF @patientFirstName <> '' SELECT @sql = @sql + 'AND patientFirstName = '''+@patientFirstName+''' ' IF @patientLastName <> '' SELECT @sql = @sql + 'AND patientLastName = '''+@patientLastName+''' ' SELECT @sql = @sql + 'AND RowNumber > @row_number' exec (@sql) I am calling this SP by: Set rsCmd = Server.CreateObject("ADODB.Command") rsCmd.ActiveConnection = rsConnection rsCmd.CommandText = "sp_find_patients" rsCmd.CommandType = 4 set sp_AccessCode = rsCmd.CreateParameter("@AccessCode",200,1,500, AccessCode) rsCmd.Parameters.Append sp_AccessCode set sp_patientHospNo = rsCmd.CreateParameter("@patientHospNo",200,1,255, patientHospNo) rsCmd.Parameters.Append sp_patientHospNo set sp_patientFirstName = rsCmd.CreateParameter("@patientFirstName",200,1,255, patientFirstName) rsCmd.Parameters.Append sp_patientFirstName set sp_patientLastName = rsCmd.CreateParameter("@patientLastName",200,1,255, patientLastName) rsCmd.Parameters.Append sp_patientLastName set sp_row_number = rsCmd.CreateParameter("@row_number",3,1,, row_number) rsCmd.Parameters.Append sp_row_number Set rsObj = rsCmd.Execute Thank you very much.
View Replies !
&"Must Declare&" A Variable That Has Already Been Decalred. PLS Help.
Hi. I keep getting an odd "must declare variable @created_date" error... seems like an easy one but i can't seem to get rid of it. For my money, the variable "IS" declared but it keeps appearing. the error is as follows: Quote: Must declare the variable '@created_date'. Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. Exception Details: System.Data.SqlClient.SqlException: Must declare the variable '@created_date'. Source Error: An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below. Stack Trace: [SqlException (0x80131904): Must declare the variable '@created_date'.] System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) +95 System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) +83 System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) +345 System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) +3152 System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString) +144 System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async) +1084 System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result) +340... etc AND, here is my code where the variable is declared: Code: Sub DetailsView1_ItemInserting(ByVal Src As Object, ByVal Args As DetailsViewInsertEventArgs) 'validate the controls If Args.Values("ISBN") = "" Then Args.Cancel = True MSG.Text = "-- Missing ISBN number" End If If Args.Values("author_first") = "" Then Args.Cancel = True MSG.Text = "-- Missing first name" End If If Args.Values("author_last") = "" Then Args.Cancel = True MSG.Text = "-- Missing last name" End If If Args.Values("publisher") = "" Then Args.Cancel = True MSG.Text = "-- Missing Publisher" End If If Args.Values("text_description") = "" Then Args.Cancel = True MSG.Text = "-- Missing item description" End If If Not IsNumeric(Args.Values("regular_price")) Then Args.Cancel = True MSG.Text = "-- regular price is not numeric" Else If Args.Values("regular_price") < 0 Then Args.Cancel = True MSG.Text = "-- regular price is out of range" End If End If If Not IsNumeric(Args.Values("discount_price")) Then Args.Cancel = True MSG.Text = "-- Discount price is not numeric" Else If Args.Values("discount_price") < 0 Then Args.Cancel = True MSG.Text = "-- Discount price is out of range" End If End If Dim MyConn As System.Data.SqlClient.SqlConnection = New System.Data.SqlClient.SqlConnection("server=ARIA;database=dushkinmedia;Integrated Security=SSPI") ' ||||| Pass in the StoreProcedure or Command String, as well as the Connection object Dim MyCmd As New System.Data.SqlClient.SqlCommand("sp_RegisterNewAudioBook", MyConn) ' ||||| Set the Command Type (Stored Procedure, Text, etc) MyCmd.CommandType = System.Data.CommandType.StoredProcedure ' ||||| Create Parameter Objects for values passed in Dim objParam1, objParam2, objParam3, objParam4, objParam5, objParam6, _ objParam7, objParam8, objParam9, objParam10, objParam11, objParam12, _ objParam13, objParam14, objParam15 As System.Data.SqlClient.SqlParameter ' ||||| Add your parameters to the parameters Collection objParam1 = MyCmd.Parameters.Add("@audiobookID", Data.SqlDbType.Int) objParam2 = MyCmd.Parameters.Add("@title", Data.SqlDbType.VarChar) objParam3 = MyCmd.Parameters.Add("@author_first", Data.SqlDbType.VarChar) objParam4 = MyCmd.Parameters.Add("@author_last", Data.SqlDbType.VarChar) objParam5 = MyCmd.Parameters.Add("@publisher", Data.SqlDbType.VarChar) objParam6 = MyCmd.Parameters.Add("@ISBN", Data.SqlDbType.VarChar) objParam7 = MyCmd.Parameters.Add("@regular_price", Data.SqlDbType.VarChar) objParam8 = MyCmd.Parameters.Add("@discount_price", Data.SqlDbType.VarChar) objParam9 = MyCmd.Parameters.Add("@text_description", Data.SqlDbType.Text) objParam10 = MyCmd.Parameters.Add("@created_date", Data.SqlDbType.DateTime) 'objParam10 = MyCmd.Parameters.AddWithValue("@img_name", imgName) objParam11 = MyCmd.Parameters.AddWithValue("@img_data", System.Data.SqlDbType.Image) 'objParam12 = MyCmd.Parameters.AddWithValue("@img_contenttype", imgcontenttype) 'objParam13 = MyCmd.Parameters.AddWithValue("@aud_name", audName) objParam14 = MyCmd.Parameters.AddWithValue("@aud_data", System.Data.SqlDbType.VarBinary) 'objParam15 = MyCmd.Parameters.AddWithValue("@aud_contenttype", audcontenttype) ' ||||| Set the Parameter values to the passed in values objParam1.Value = audiobookID objParam2.Value = title objParam3.Value = author_first objParam4.Value = author_last objParam5.Value = publisher objParam6.Value = ISBN objParam7.Value = regular_price objParam8.Value = discount_price objParam9.Value = text_description objParam10.Value = created_date 'objParam11.Value = imgBinaryData 'objParam14.Value = audBinaryData Try ' ||||| Check if Connection to DB is already open, if not, then open a connection ' ||||| DB not already Open...so open it MyConn.Open() MyCmd.ExecuteNonQuery() ' ||||| Close the Connection Closes with it MyConn.Close() Catch e As Exception Beep() MSG.Text = e.ToString End Try Response.Redirect("~/audiobook_services/audiobook_view.aspx") End Sub I even checked my stored procedure, and it is declared there: Code: ALTER PROCEDURE dbo.sp_RegisterNewAudioBook ( /*AudioBook Table*/ @created_date DATETIME = NULL, @title VARCHAR(50) = NULL, @author_first VARCHAR(50) = NULL, @author_last VARCHAR(50) = NULL, @publisher VARCHAR(50) = NULL, @ISBN VARCHAR(50) = NULL, @regular_price VARCHAR(50) = NULL, @discount_price VARCHAR(50) = NULL, @text_description TEXT = NULL, /*BookImage Table*/ @img_name VARCHAR(50) = NULL, @img_data IMAGE = NULL, @img_contenttype VARCHAR(50) = NULL, /*BookAudioDesc Table*/ @aud_name VARCHAR(50) = NULL, @aud_data VARBINARY(50) = NULL, @aud_contenttype VARCHAR(50) = NULL, /*Foreign Key Declaration*/ @pub_id INT = 0 ) AS IF @title IS NULL OR @author_first IS NULL OR @author_last IS NULL OR @publisher IS NULL OR @ISBN IS NULL OR @regular_price IS NULL OR @discount_price IS NULL OR @text_description IS NULL RAISERROR('Please fill in all fields', 16, 1) ELSE BEGIN INSERT INTO dbo.AudioBook (created_date, title, author_first, author_last, publisher, ISBN, regular_price, discount_price, text_description) VALUES (GetDate(), @title, @author_first, @author_last, @publisher, @ISBN, @regular_price, @discount_price, @text_description) IF @@error <> 0 RAISERROR('User was not added', 16, 1) ELSE BEGIN DECLARE @audiobookID INT SELECT @audiobookID = @@IDENTITY SELECT Count(*) FROM AudioBook WHERE audiobookID = @audiobookID END BEGIN select convert(varchar(50),GetDate(),1) from AudioBook END END So, why, after all the verified fact checking, does the compiler still complained that i haven't declared the variable @created_date? I am thinking that the only possible reason is that i recently did a "aspnet_regiis -i" to help fix a different problem. Could this possibly be a reason why my compiler is complaining about missing variables that are obviously there??? Help much apprecaited.
View Replies !
Unable To Update Or Delete GridView Entries... &"Must Declare The Scalar Variable &"@ID1&".&"
Hello, I am having issues and can't see any errors in my code! When attempting to delete a table entry from my SQL database, I get the error "Must declare the scalar variable "@ID1"." even though I declare it as a parameter in my code! Can anyone see an issue with my code below?1 <asp:GridView ID="GridView1" runat="server" AllowSorting="True" 2 DataSourceID="SqlDataSource1" AutoGenerateColumns="False" OnSorted="GridView1_Sorted"> 3 <Columns> 4 <asp:CommandField ShowDeleteButton="True" ShowEditButton="True" /> 5 <asp:BoundField DataField="column1" HeaderText="CO" SortExpression="column1" /> 6 <asp:BoundField DataField="column2" HeaderText="Network" SortExpression="column2" /> 7 <asp:BoundField DataField="column3" HeaderText="C/S/T" SortExpression="column3" /> 8 <asp:BoundField DataField="column4" HeaderText="Date Received" SortExpression="column4" DataFormatString="{0:d}" HtmlEncode="False" /> 9 <asp:BoundField DataField="DESCRIPTION" HeaderText="Description" SortExpression="DESCRIPTION" /> 10 <asp:BoundField DataField="column5" HeaderText="In Service Date" SortExpression="column5" DataFormatString="{0:d}" HtmlEncode="False" /> 11 <asp:BoundField DataField="REMARKS" HeaderText="Remarks" SortExpression="REMARKS" /> 12 <asp:BoundField DataField="ID" HeaderText="ID" InsertVisible="False" ReadOnly="True" 13 SortExpression="ID" /> 14 </Columns> 15 </asp:GridView> 16 <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:ConduitConnectionString %>" 17 SelectCommand="SELECT [ID], [CO/Area] AS column1, [NW #] AS column2, [C/S/T] AS column3, [DATE REC'D] AS column4, [DESCRIPTION], [I/S DATE] AS column5, [REMARKS] FROM [Inspector Workload] WHERE ([Inspector Name] = @Inspector_Name) ORDER BY [CO/Area], [DATE REC'D]" DeleteCommand="DELETE FROM [Inspector Workload] WHERE (ID = @ID1)" UpdateCommand="UPDATE [Inspector Workload] SET [CO/Area] = @column1, [NW #] = @column2, [C/S/T] = @column3, [DATE REC'D] = @column4, [DESCRIPTION] = @DESCRIPTION, [I/S DATE] = @column5, [REMARKS] = @REMARKS WHERE ([ID] = @ID1)"> 18 <SelectParameters> 19 <asp:QueryStringParameter Name="Inspector_Name" QueryStringField="Name" Type="String" /> 20 </SelectParameters> 21 <DeleteParameters> 22 <asp:Parameter Name="ID" Type="Int32" /> 23 </DeleteParameters> 24 <UpdateParameters> 25 <asp:Parameter Name="column1" Type="String" /> 26 <asp:Parameter Name="column2" Type="Double" /> 27 <asp:Parameter Name="column3" Type="String" /> 28 <asp:Parameter Name="column4" Type="DateTime" /> 29 <asp:Parameter Name="DESCRIPTION" Type="String" /> 30 <asp:Parameter Name="column5" Type="DateTime" /> 31 <asp:Parameter Name="REMARKS" Type="String" /> 32 <asp:Parameter Name="ID" Type="Int32" /> 33 </UpdateParameters> 34 </asp:SqlDataSource> Thanks!Rob.
View Replies !
Declare @var????
I am trying to get a grasp on the Sql Stored procedures it seems i dont really understnad what DECLARE @Date DateTime means??? I mean i think it means that i am just declaring a varible name Date that will hold a DateTime Value??? is that correct or is it more to it???? CREATE PROCEDURE dbo.Tracking_GetStatus AS DECLARE @Date DateTime DECLARE @Begining DateTime DECLARE @Ending DateTime SET @Date = GETDATE() SET @Begining = DATEADD(ss,(DATEPART(ss,@Date)*-1), DATEADD(mi,(DATEPART(mi,@Date)*-1), DATEADD(hh,(DATEPART(hh,@Date)*-1),@Date))) SET @Ending = DATEADD(ss,-1, DATEADD(dd,1,DATEADD(ss,(DATEPART(ss,@Date)*-1), DATEADD(mi,(DATEPART(mi,@Date)*-1), DATEADD(hh,(DATEPART(hh,@Date)*-1),@Date))))) SELECT Vehicl, UpdateTi XCoord, YCoord, Status FROM Track WHERE UpdateTime >= @Begining AND UpdateTime <= @Ending RETURN GO
View Replies !
DECLARE In SQL CE
Can I use DECLARE in SQL 2005 Compact, and if not, how do I do INSERTs into tables which have columns with Primary Key constraints? Matt
View Replies !
My DropDownList Won't Declare?
Need a little help! I am trying to insert ListItems values from a DropDownList into a database table. However in the code behind I am continuosly met with the error Name 'ddltest' is not declared. As you can see from the code below ddltest is an object with the ID ddltest. What am I doing wrong? Protected Sub ddltest_SelectedIndexChanged(ByVal sender As Object, ByVal e As System.EventArgs)Dim MyVar As String = ddltest.SelectedItem.Value If MyVar = "" Then ErrorMessage.Text = "Please select a test" Else 'Insert selection into databaseDim oConnection As New SqlConnection Dim oCommand As SqlCommand Dim sConnString As String Dim sSQL As String sConnString = "Data Source=.SQLEXPRESS;AttachDbFilename=|DataDirectory|xxxxx.mdf;Integrated Security=True;User Instance=false"oConnection = New SqlConnection(sConnString) sSQL = "INSERT INTO testDB(Myxxxx) Values (@Myxxxx)" oConnection.Open()oCommand = New SqlCommand(sSQL, oConnection)oCommand.Parameters.Add(New SqlParameter("@Myxxxx", MyVar)) oCommand.ExecuteNonQuery() oConnection.Close() ErrorMessage.Text = "You selected " & MyVar & " and it has been added to the database." End If End Sub <asp:TemplateField HeaderText="Test"> <EditItemTemplate> <asp:DropDownList ID="ddltest" runat="server" AutoPostBack="True" OnSelectedIndexChanged="ddltest_SelectedIndexChanged" > <asp:ListItem Selected="True" Value="" ><-- Please Select a test --></asp:ListItem><asp:ListItem Value="1">1</asp:ListItem> <asp:ListItem Value="2">2</asp:ListItem> <asp:ListItem Value="3">3</asp:ListItem> <asp:ListItem Value="4">4</asp:ListItem> <asp:ListItem Value="5" >5</asp:ListItem> </asp:DropDownList> </EditItemTemplate> </asp:TemplateField>
View Replies !
|
TRACKER
