ERROR: Procedure Expects Parameter '@statement' Of Type 'ntext/nchar/nvarchar'.
Mar 27, 2004
/* INFO USED HERE WAS TAKEN FROM http://support.microsoft.com/default.aspx?scid=kb;en-us;262499 */
DECLARE @X VARCHAR(10)
DECLARE @ParmDefinition NVARCHAR(500)
DECLARE @Num_Members SMALLINT
SELECT @X = 'x.dbo.v_NumberofMembers'
DECLARE @SQLString AS VARCHAR(500)
SET @SQLString = 'SELECT @Num_MembersOUT=Num_Members FROM @DB'
SET @ParmDefinition = '@Num_MembersOUT SMALLINT OUTPUT'
EXECUTE sp_executesql <-LINE 11
@SQLString,
@ParmDefinition,
@DB = @X,
@Num_MembersOUT = @Num_Members OUTPUT
Just Need Help On This Error
Server: Msg 214, Level 16, State 2, Procedure sp_executesql, Line 11
Procedure expects parameter '@statement' of type 'ntext/nchar/nvarchar'.
I dont know why im getting a errrror b/c I followed http://support.microsoft.com/default.aspx?scid=kb;en-us;262499 exactly
View 3 Replies
ADVERTISEMENT
Apr 10, 2008
When I tried to insert armenian by doing the following
insert into tablex (field1) values (N'testdata')
it does not display in query analyzer or in the database as armenian.
When I copy this to word it does not convert it.
What else am I supposed to do to get that information to redisplay the correct way and I would appreciate any tutorials or samples you can show or direct me to.
Howard
View 11 Replies
View Related
Jan 28, 2005
Hi,
We are in process of converting all of the data type of the fields from CHAR/VARCHAR/TEXT into NCHAR/NVARCHAR/NTEXT (DBCS). Having more than 900 store procedure its look like real pain to make modification in all of the SPs.
After failed to find any help from GOOGLE, I am posting this request. I am basically looking for any automated tool which are convert data type in SP based on the field of the table used in the SP. Or at least which can provide me some sort of list which can helpful for doing manual reactoring.
PLEASE HELP ME!!!
Thanks,
Firoz Ansari
View 2 Replies
View Related
Jan 10, 2008
HI, I am running the below method which returns this error: The parameterized query '(@contactdate nvarchar(4000),@dnbnumber nvarchar(4000),@prospect' expects the parameter '@futureopportunity', which was not supplied" Please help.Private Shared Sub InsertData(ByVal sourceTable As System.Data.DataTable, ByVal destConnection As SqlConnection)
' old method: Lots of INSERT statements Dim rowscopied As Integer = 0
' first, create the insert command that we will call over and over:
destConnection.Open()Using ins As New SqlCommand("INSERT INTO [tblAppointmentDisposition] ([contactdate], [dnbnumber], [prospectname], [businessofficer], [phonemeeting], [followupcalldate2], [phonemeetingappt], [followupcalldate3], [appointmentdate], [appointmentlocation], [appointmentkept], [applicationgenerated], [applicationgenerated2], [applicationgenerated3], [comments], [newaccount], [futureopportunity]) VALUES (@contactdate, @dnbnumber, @prospectname, @businessofficer, @phonemeeting, @followupcalldate2, @phonemeetingappt, @followupcalldate3, @appointmentdate, @appointmentlocation, @appointmentkept, @applicationgenerated, @applicationgenerated2, @applicationgenerated3, @comments, @newaccount, @futureopportunity)", destConnection)
ins.CommandType = CommandType.Textins.Parameters.Add("@contactdate", SqlDbType.NVarChar)
ins.Parameters.Add("@dnbnumber", SqlDbType.NVarChar)ins.Parameters.Add("@prospectname", SqlDbType.Text)
ins.Parameters.Add("@businessofficer", SqlDbType.NChar)ins.Parameters.Add("@phonemeeting", SqlDbType.NVarChar)
ins.Parameters.Add("@followupcalldate2", SqlDbType.NVarChar)ins.Parameters.Add("@phonemeetingappt", SqlDbType.NVarChar)
ins.Parameters.Add("@followupcalldate3", SqlDbType.NVarChar)ins.Parameters.Add("@appointmentdate", SqlDbType.NVarChar)
ins.Parameters.Add("@appointmentlocation", SqlDbType.NVarChar)ins.Parameters.Add("@appointmentkept", SqlDbType.NVarChar)
ins.Parameters.Add("@applicationgenerated", SqlDbType.NVarChar)ins.Parameters.Add("@applicationgenerated2", SqlDbType.NVarChar)
ins.Parameters.Add("@applicationgenerated3", SqlDbType.NVarChar)ins.Parameters.Add("@comments", SqlDbType.Text)
ins.Parameters.Add("@newaccount", SqlDbType.NVarChar)ins.Parameters.Add("@futureopportunity", SqlDbType.NVarChar)
' and now, do the work: For Each r As DataRow In sourceTable.RowsFor i As Integer = 0 To 15
ins.Parameters(i).Value = r(i)
Next
ins.ExecuteNonQuery()
'If System.Threading.Interlocked.Increment(rowscopied) Mod 10000 = 0 Then
'Console.WriteLine("-- copied {0} rows.", rowscopied)
'End If
Next
End Using
destConnection.Close()
End Sub
View 6 Replies
View Related
May 15, 2007
Hi,I'm trying to create a page where a user can search the database according to some criteria and get back the result in the form of a GridView. Also, the user has the option of saving the criteria to another table in the database by assigning it a name so that it can be retrieved easily in the future.I have the search and display part working, however, saving the criteria to the database is giving problems for some reason.Given below is my stored procedure to add the info to the db. SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[AddToReport]
(@ReportName varchar(100), @ProjID varchar(300), @DeptCode varchar(20), @ProjType varchar(20), @ProjectTitle varchar(300),
@ProjectManagerID int, @DateRequested datetime, @DueDate datetime, @ProjectStatusID int)
AS
SET NOCOUNT ON
DECLARE @Dept varchar(50)
DECLARE @err int
BEGIN TRANSACTION
IF @ReportName IS NULL
BEGIN
RETURN -1
END
ELSE
BEGIN
IF @DeptCode IS NOT NULL
BEGIN
SET @Dept = REPLACE(CONVERT(char,@DeptCode),'.','')
END
SET @err = @@ERROR
INSERT INTO dbo.tbl_Report (ReportName, ProjID, DeptCode, ProjType, ProjectTitle, ProjectManagerID, DateRequested, DueDate, ProjectStatusID)
VALUES (@ReportName, @ProjID, @Dept, @ProjType, @ProjectTitle, @ProjectManagerID, @DateRequested, @DueDate, @ProjectStatusID);
IF @err<>0
BEGIN
ROLLBACK TRANSACTION
RETURN @err
END
END
COMMIT TRANSACTION Given below is the relevant codebehind. This is how the values are initialized: Dim newManager As New ListItem
newManager.Text = "Choose a Manager"
newManager.Value = 0
projectManagerDDL.Items.Add(newManager)
Dim newDept As New ListItem
newDept.Text = "Choose a Department"
newDept.Value = ""
deptCodeDDL.Items.Add(newDept)
Dim newID As New ListItem
newID.Text = "Choose a Project"
newID.Value = ""
projIDDDL.Items.Add(newID)
Dim newStatus As New ListItem
newStatus.Text = "Choose a Status"
newStatus.Value = 0
projectStatusDDL.Items.Add(newStatus)
Dim newDateRequestedMonth As New ListItem
newDateRequestedMonth.Text = "Month"
newDateRequestedMonth.Value = 0
dateRequestedMonthDDL.Items.Add(newDateRequestedMonth)
Dim newDateRequestedDay As New ListItem
newDateRequestedDay.Text = "Day"
newDateRequestedDay.Value = 0
dateRequestedDayDDL.Items.Add(newDateRequestedDay)
Dim newDateRequestedYear As New ListItem
newDateRequestedYear.Text = "Year"
newDateRequestedYear.Value = 0
dateRequestedYearDDL.Items.Add(newDateRequestedYear)
Dim newDueDateMonth As New ListItem
newDueDateMonth.Text = "Month"
newDueDateMonth.Value = 0
dueDateMonthDDL.Items.Add(newDueDateMonth)
Dim newDueDateDay As New ListItem
newDueDateDay.Text = "Day"
newDueDateDay.Value = 0
dueDateDayDDL.Items.Add(newDueDateDay)
Dim newDueDateYear As New ListItem
newDueDateYear.Text = "Year"
newDueDateYear.Value = 0
dueDateYearDDL.Items.Add(newDueDateYear) This is the submit code: Protected Sub saveButton_Click(ByVal sender As Object, ByVal e As System.EventArgs)
Dim connString As String
Dim con As SqlConnection
Try
connString = System.Web.Configuration.WebConfigurationManager.ConnectionStrings("ConnectionString1").ConnectionString
con = New SqlConnection(connString)
Dim cmd As SqlCommand = New SqlCommand()
cmd.Connection = con
If (([String].IsNullOrEmpty(reportNameTextBox.Text) = False) Or reportNameTextBox.Text <> "Enter Report Name") Then
cmd.Parameters.Add("ReportName", SqlDbType.VarChar, 300).Value = reportNameTextBox.Text
End If
If ([String].IsNullOrEmpty(projIDDDL.SelectedItem.Value)) = False Then
cmd.Parameters.Add("ProjID", SqlDbType.VarChar, 30).Value = projIDDDL.SelectedItem.Value
End If
If ([String].IsNullOrEmpty(deptCodeDDL.SelectedItem.Value)) = False Then
cmd.Parameters.Add("DeptCode", SqlDbType.VarChar, 20).Value = deptCodeDDL.SelectedItem.Value
End If
If (typeRBL.SelectedItem.Value <> "All") Then
cmd.Parameters.Add("ProjType", SqlDbType.VarChar, 20).Value = typeRBL.SelectedItem.Value
End If
If ([String].IsNullOrEmpty(projectTitleTextBox.Text)) = False Then
cmd.Parameters.Add("ProjectTitle", SqlDbType.VarChar, 300).Value = projectTitleTextBox.Text
End If
If CInt(projectManagerDDL.SelectedItem.Value) <> 0 Then
cmd.Parameters.Add("ProjectManagerID", SqlDbType.Int).Value = CInt(projectManagerDDL.SelectedItem.Value)
End If
If (dateRequestedDayDDL.SelectedItem.Value = 0 Or dateRequestedMonthDDL.SelectedItem.Value = 0 Or dateRequestedYearDDL.SelectedItem.Value = 0) Then
Dim dateRequested As New DateTime
dateRequested = Nothing
Else
Dim dateRequested As New DateTime(dateRequestedYearDDL.SelectedValue, dateRequestedMonthDDL.SelectedValue, dateRequestedDayDDL.SelectedValue)
If (dateRequested) <> Nothing Then
cmd.Parameters.Add("DateRequested", SqlDbType.DateTime).Value = dateRequested
End If
End If
If (dueDateDayDDL.SelectedItem.Value = 0 Or dueDateMonthDDL.SelectedItem.Value = 0 Or dueDateYearDDL.SelectedItem.Value = 0) Then
Dim dueDate As New DateTime
dueDate = Nothing
Else
Dim dueDate As New DateTime(dueDateYearDDL.SelectedValue, dueDateMonthDDL.SelectedValue, dueDateDayDDL.SelectedValue)
If (dueDate) <> Nothing Then
cmd.Parameters.Add("DueDate", SqlDbType.DateTime).Value = dueDate
End If
End If
If (projectStatusDDL.SelectedItem.Value) <> 0 Then
cmd.Parameters.Add("ProjectStatusID", SqlDbType.Int).Value = CInt(projectStatusDDL.SelectedItem.Value)
End If
cmd.CommandText = "dbo.AddToReport"
cmd.CommandType = CommandType.StoredProcedure
Try
con.Open()
cmd.ExecuteNonQuery()
Response.Write("Report Saved")
Catch ex As Exception
Response.Write(ex)
Finally
con.Close()
con.Dispose()
End Try
Catch ex As ApplicationException
Response.Write("Could not load the database")
End Try
End Sub The only absolute requirement when saving to the table is the ReportName. All the other criteria can be NULL. If I don't select and values and try to save the values, I get an error:System.Data.SqlClient.SqlException: Procedure or function 'AddToReport' expects
parameter '@ProjID', which was not supplied. at
System.Data.SqlClient.SqlConnection.OnError... etc If I choose the ProjID (thus giving it a value), I get the following error:System.Data.SqlClient.SqlException: Procedure or function 'AddToReport' expects
parameter '@DeptCode', which was not supplied. at
System.Data.SqlClient.SqlConnection.OnError... etc and so forth. I'm guessing it's a problem of NULLs somewhere, but I'm not sure. Thanks.
View 6 Replies
View Related
Aug 24, 2004
Hi,
I created a stored procedure in the sql server. I try to insert a record from the aspx page. But I keep getting this error,
"procedure expects parameter <@firstname>, which was not supplied". This is what I am doing.
cmd.CommandText = "proc_insertuser";
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("@firstname", SqlDbType.VarChar, 50);
cmd.Parameters["@firstname"].Value = txtFirstName.Text
....other parameters
I checked the parameter names, it matches correctly. The parameter does get added to the collection. I checked it using cmd.Parameters.Contains("@firstname") and the value is also correct. Using query analyzer I executed the procedure, I am able to insert a record.
Can anyone tell me what could be the problem.
TAI
View 2 Replies
View Related
Dec 26, 2007
I have a stored procedure that contains a paramteter of type nvarchar(max). What is the syntax to pass this parameter to the sp from a VB.Net application. Specifically, I need to know what to put in the 3rd parameter below:
cmd.Parameters.Add("@Name", SqlDbType.NVarChar, , Name)
View 1 Replies
View Related
Jul 2, 2006
Can any body help me in solving this problem.
First I use to get Error which reads "Object Must Implement Iconvertible"
After using the overloaded Sp.paramerers.add() function It started giving this problem.
I am giving the sample code.
ConObject = new SqlConnection(ConString);
ConObject.Open();
string SpString ="dbo.SP_Insert_NewPipeLine";
SqlCommand CmdObject = new SqlCommand(SpString,ConObject);
CmdObject.CommandType = CommandType.StoredProcedure;
CmdObject.Parameters.Add("@RequestTypeId",SqlDbType.Int,4,"0");
//CmdObject.Parameters["@RequestTypeId"].Value= 0;
CmdObject.Parameters.Add("@OnBehalfOf",SqlDbType.Int,4,"21");//Onbehalf of Id
//CmdObject.Parameters["@OnBehalfOf"].Value = 21;
CmdObject.Parameters.Add("@SubmittedBy",SqlDbType.Int,4,ddlSalesRep.SelectedValue.ToString());//Submitted by Id
//CmdObject.Parameters["@SubmittedBy"].Value = ddlSalesRep.SelectedItem.Value;
CmdObject.Parameters.Add("@CompanyId",SqlDbType.Int,4,ddlCustomer.SelectedItem.Value.ToString());//Company_Id
//CmdObject.Parameters["@CompanyId"].Value = ddlCustomer.SelectedItem.Value;
CmdObject.ExecuteScalar();
View 2 Replies
View Related
Oct 5, 2007
Maybe I am missing something completely obvious but as far as I can tell I aren't!
I am trying to simply pass a parameter to a stored proc to return results. Easy? So I thought so too...
This is my stored procedure below (in it's ALTER PROCEDURE form):
ALTER PROCEDURE [dbo].[spd_Tester]
-- Add the parameters for the stored procedure here
@Check int
AS
BEGIN
SELECT * FROM tblPerson
WHERE PersonID = @Check
END
Now I know this is a SQL Server forum but I figure anyone who reads this probably does some application programming as well, so here is the code that calls this. It's in VBA using Access 2003. I figure the error is being returned from SQL Server Express so that's why I have come to this forum instead of an Access forum! I could be wrong though...
Here is the code that calls this:
Dim rs As ADODB.Recordset
Dim cmd As ADODB.Command
Set cmd = New ADODB.Command
'Run sql command
With cmd
.ActiveConnection = CurrentProject.Connection
.CommandText = "dbo.spd_Tester"
.Parameters.Append .CreateParameter("@Check", adInteger, adParamInput, , Me.txtFirstName)
'Execute, return recordset
Set rs = .Execute
End With
Now I figure this should work. However, I get the error "Procedure or function 'spd_Tester' expects parameter '@Check', which was not supplied." when I execute the procedure with the number 1 in the txtFirstName text box.
Can someone tell me what I am doing wrong?? The tblPerson table has data in it, if I run the select from a query window, specifying a value for @Check, it works just fine.
Thanks in advance!
View 4 Replies
View Related
Feb 12, 2008
Hello guy...i have a problem with my web application...i'm using SqlDataSource and GridView....i'm using store procedure that i've created in my mssql server...the problem is when i'm trying to delete current row...this error alert and said
Error
"Procedure or Function "Roles_delete" expects parameter "@Role_name", which not supplied"
Im new user in asp.net....how i can passing my selected value to this parameter??? This is my code...please help me guy...
Asp.net code
<asp:UpdatePanel ID="UpdatePanel1" runat="server">
<ContentTemplate>
<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:PVMCCon %>"
DeleteCommand="Roles_delete" DeleteCommandType="StoredProcedure" SelectCommand="Roles_select"
SelectCommandType="StoredProcedure">
<DeleteParameters>
<asp:Parameter Name="Role_name" Type="String" />
</DeleteParameters>
</asp:SqlDataSource>
<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" BackColor="White"
BorderColor="#DEDFDE" BorderStyle="None" BorderWidth="1px" CellPadding="4" DataKeyNames="Role_application_id"
DataSourceID="SqlDataSource1" ForeColor="Black" GridLines="Vertical">
<FooterStyle BackColor="#CCCC99" />
<Columns>
<asp:CommandField ShowDeleteButton="True" />
<asp:BoundField DataField="Role_application_id" HeaderText="Role_application_id"
ReadOnly="True" SortExpression="Role_application_id" />
<asp:BoundField DataField="Role_name" HeaderText="Role_name" SortExpression="Role_name" />
<asp:BoundField DataField="Role_description" HeaderText="Role_description" SortExpression="Role_description" />
</Columns>
<RowStyle BackColor="#F7F7DE" />
<SelectedRowStyle BackColor="#CE5D5A" Font-Bold="True" ForeColor="White" />
<PagerStyle BackColor="#F7F7DE" ForeColor="Black" HorizontalAlign="Right" />
<HeaderStyle BackColor="#6B696B" Font-Bold="True" ForeColor="White" />
<AlternatingRowStyle BackColor="White" />
</asp:GridView>
</ContentTemplate>
</asp:UpdatePanel>
Store Procedure
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
ALTER PROCEDURE [dbo].[Roles_delete]
@Role_name varchar(50)
AS
BEGIN
SET NOCOUNT ON;
DELETE FROM Role
WHERE Role_name=@Role_name
END
Error
Procedure or Function "Roles_delete" expects parameter "@Role_name", which not supplied"
Please help me guy to settle my problem...
View 3 Replies
View Related
Dec 14, 2004
I want to retrieve data from SQL containing non English character but fail, can anyone shed me some light?
What I use currently:
Dim strSQL As String
strSQL = "SELECT ArticleID, "
strSQL &= "ISNULL(Body, '') AS Body, "
strSQL &= "ISNULL(Subject, '') AS Subject "
strSQL &= "FROM Articles "
strSQL &= "WHERE (Subject LIKE N'%' + @Keyword + '%' OR [Body] LIKE N'%' + @Keyword + '%') "
Dim con As New SqlConnection(ConfigurationSettings.AppSettings("ConnectionString"))
Dim cmd As New SqlDataAdapter(strSQL, con)
cmd.SelectCommand.Parameters.Add("@Keyword", SqlDbType.NVarChar).Value = keyword
...
I'm not so sure where should I place the letter "N", I use :
SELECT ArticleID,
ISNULL(Body, '') AS Body,
ISNULL(Subject, '') AS Subject,
FROM Articles
WHERE (Subject LIKE N'%SomeNonEnglishString%' OR [Body] LIKE N'%SomeNonEnglishString%')
in Query Analzyer, it works! But it failed in my program... oh my god...
Thanks a lot!
View 4 Replies
View Related
Nov 10, 2007
Prepared statement '(@CORP_NAME varchar(150),@REP_NAME varchar(150),@REP_TC_NO varch' expects parameter @CORP_NAME, which was not supplied
I know this is a classical error and I searched through the forum but I could no solve it. I am sure that I defined @CORP_NAME, but it says you did not. My code is below,please help me...
private void Submit1_ServerClick(object sender, System.EventArgs e)
{
erkaner = Page.Session.Contents["CORP_ID"].ToString();
sqlUpdateCommand1.Parameters.Add(new System.Data.SqlClient.SqlParameter("@CORP_NAME", System.Data.SqlDbType.VarChar, 150, TextBox32.Text));
sqlUpdateCommand1.Parameters.Add(new System.Data.SqlClient.SqlParameter("@REP_NAME", System.Data.SqlDbType.VarChar, 150, TextBox27.Text));sqlUpdateCommand1.Parameters.Add(new System.Data.SqlClient.SqlParameter("@REP_TC_NO", System.Data.SqlDbType.VarChar, 50, TextBox28.Text));
sqlUpdateCommand1.Parameters.Add(new System.Data.SqlClient.SqlParameter("@CORP_ID", System.Data.SqlDbType.Int, 4, erkaner));sqlUpdateCommand1.Parameters.Add(new System.Data.SqlClient.SqlParameter("@REP_EMAIL", System.Data.SqlDbType.VarChar, 50, TextBox29.Text));
sqlUpdateCommand1.Parameters.Add(new System.Data.SqlClient.SqlParameter("@REP_TEL", System.Data.SqlDbType.VarChar, 50, TextBox30.Text));sqlUpdateCommand1.Parameters.Add(new System.Data.SqlClient.SqlParameter("@REP_DUTY", System.Data.SqlDbType.VarChar, 150, TextBox31.Text));
sqlUpdateCommand1.Parameters.Add(new System.Data.SqlClient.SqlParameter("@FOUND_YEAR", System.Data.SqlDbType.VarChar, 50, TextBox33.Text));sqlUpdateCommand1.Parameters.Add(new System.Data.SqlClient.SqlParameter("@COUNTRY", System.Data.SqlDbType.VarChar, 50, DropDownListUlke.SelectedValue.ToString()));
if (DropDownListUlke.SelectedIndex == 148)
sqlUpdateCommand1.Parameters.Add(new System.Data.SqlClient.SqlParameter("@CITY", System.Data.SqlDbType.VarChar, 50, DropdownlistSehir.SelectedValue.ToString()));else if(DropDownListUlke.SelectedIndex != 0 && DropDownListUlke.SelectedIndex != 148)
sqlUpdateCommand1.Parameters.Add(new System.Data.SqlClient.SqlParameter("@CITY", System.Data.SqlDbType.VarChar, 50, TextBox1.Text));sqlUpdateCommand1.Parameters.Add(new System.Data.SqlClient.SqlParameter("@SECTOR", System.Data.SqlDbType.VarChar, 150, RadioButtonList6.SelectedValue.ToString()));
sqlUpdateCommand1.Parameters.Add(new System.Data.SqlClient.SqlParameter("@EMP_NUM", System.Data.SqlDbType.VarChar, 50, RadioButtonList1.SelectedValue.ToString()));sqlUpdateCommand1.Parameters.Add(new System.Data.SqlClient.SqlParameter("@CORP_ACT", System.Data.SqlDbType.VarChar, 150, DropDownList3.SelectedValue.ToString()));
sqlUpdateCommand1.Parameters.Add(new System.Data.SqlClient.SqlParameter("@PC_NUM", System.Data.SqlDbType.VarChar, 50, TextBox35.Text));sqlUpdateCommand1.Parameters.Add(new System.Data.SqlClient.SqlParameter("@INT_USER_NUM", System.Data.SqlDbType.VarChar, 50, "INT_USER_NUM"));
sqlUpdateCommand1.Parameters.Add(new System.Data.SqlClient.SqlParameter("@HAVE_LAN", System.Data.SqlDbType.VarChar, 50, RadioButtonList3.SelectedValue.ToString()));sqlUpdateCommand1.Parameters.Add(new System.Data.SqlClient.SqlParameter("@HAVE_SERVER", System.Data.SqlDbType.VarChar, 50, RadioButtonList4.SelectedValue.ToString()));
sqlUpdateCommand1.Parameters.Add(new System.Data.SqlClient.SqlParameter("@ECOM_SER_NUM", System.Data.SqlDbType.VarChar, 50, TextBox37.Text));sqlUpdateCommand1.Parameters.Add(new System.Data.SqlClient.SqlParameter("@OT_SER_NUM", System.Data.SqlDbType.VarChar, 50, TextBox38.Text));sqlUpdateCommand1.Parameters.Add(new System.Data.SqlClient.SqlParameter("@INT_CON_TYPE", System.Data.SqlDbType.VarChar, 50, RadioButtonList5.SelectedValue.ToString()));
if (CheckBox1.Checked)
sqlUpdateCommand1.Parameters.Add(new System.Data.SqlClient.SqlParameter("@APPLICATION1", System.Data.SqlDbType.VarChar, 500, "Ticari Uygulamalar (B2B,B2C,e-iş,e-ihracat,…)"));else sqlUpdateCommand1.Parameters.Add(new System.Data.SqlClient.SqlParameter("@APPLICATION1", System.Data.SqlDbType.VarChar, 500, ""));
if (CheckBox2.Checked)sqlUpdateCommand1.Parameters.Add(new System.Data.SqlClient.SqlParameter("@APPLICATION2", System.Data.SqlDbType.VarChar, 500, "Yönetişim Uygulamaları (Muhasebe, stok, satış, kalite, raporlama,denetim...)"));
else sqlUpdateCommand1.Parameters.Add(new System.Data.SqlClient.SqlParameter("@APPLICATION2", System.Data.SqlDbType.VarChar, 500, ""));
if (CheckBox3.Checked)sqlUpdateCommand1.Parameters.Add(new System.Data.SqlClient.SqlParameter("@APPLICATION3", System.Data.SqlDbType.VarChar, 500, "ERP Uygulamalari(Unity, SAP, JDE, BAAN, QAD vb.)"));
else sqlUpdateCommand1.Parameters.Add(new System.Data.SqlClient.SqlParameter("@APPLICATION3", System.Data.SqlDbType.VarChar, 500, ""));
if (CheckBox4.Checked)sqlUpdateCommand1.Parameters.Add(new System.Data.SqlClient.SqlParameter("@APPLICATION4", System.Data.SqlDbType.VarChar, 500, "Müşteri İlişkileri Yönetimi (CRM) Uygulamaları"));
else sqlUpdateCommand1.Parameters.Add(new System.Data.SqlClient.SqlParameter("@APPLICATION4", System.Data.SqlDbType.VarChar, 500, ""));
if (CheckBox5.Checked)sqlUpdateCommand1.Parameters.Add(new System.Data.SqlClient.SqlParameter("@APPLICATION5", System.Data.SqlDbType.VarChar, 500, "Bilgisayar Destekli Tasarım / Bilgisayar Destekli Üretim (CAD/CAM) Uygulamaları"));
else sqlUpdateCommand1.Parameters.Add(new System.Data.SqlClient.SqlParameter("@APPLICATION5", System.Data.SqlDbType.VarChar, 500, ""));
if (CheckBox6.Checked)sqlUpdateCommand1.Parameters.Add(new System.Data.SqlClient.SqlParameter("@APPLICATION6", System.Data.SqlDbType.VarChar, 500, "Veri Ambarı ve Veri Madenciliği Uygulamaları "));
else sqlUpdateCommand1.Parameters.Add(new System.Data.SqlClient.SqlParameter("@APPLICATION6", System.Data.SqlDbType.VarChar, 500, ""));
if (CheckBox7.Checked)sqlUpdateCommand1.Parameters.Add(new System.Data.SqlClient.SqlParameter("@APPLICATION7", System.Data.SqlDbType.VarChar, 500, TextBox36.Text));
else sqlUpdateCommand1.Parameters.Add(new System.Data.SqlClient.SqlParameter("@APPLICATION7", System.Data.SqlDbType.VarChar, 500, ""));
sqlUpdateCommand1.CommandText = "UPDATE dbo.CORP_TBL SET REP_NAME = @REP_NAME, CORP_NAME = @CORP_NAME, REP_TC_NO =" +
" @REP_TC_NO, REP_EMAIL = @REP_EMAIL, REP_TEL = @REP_TEL, REP_DUTY = @REP_DUTY, F" +
"OUND_YEAR = @FOUND_YEAR, COUNTRY = @COUNTRY, CITY = @CITY, SECTOR = @SECTOR, EMP" +
"_NUM = @EMP_NUM, CORP_ACT = @CORP_ACT, PC_NUM = @PC_NUM, INT_USER_NUM = @INT_USE" +
"R_NUM, HAVE_LAN = @HAVE_LAN, HAVE_SERVER = @HAVE_SERVER, ECOM_SER_NUM = @ECOM_SE" +
"R_NUM, OT_SER_NUM = @OT_SER_NUM, INT_CON_TYPE = @INT_CON_TYPE, APPLICATION1 = @A" +
"PPLICATION1, APPLICATION2 = @APPLICATION2, APPLICATION3 = @APPLICATION3, APPLICA" +
"TION4 = @APPLICATION4, APPLICATION5 = @APPLICATION5, APPLICATION6 = @APPLICATION" +
"6, APPLICATION7 = @APPLICATION7 WHERE CORP_ID = @CORP_ID";
sqlUpdateCommand1.Connection = sqlConnection1;
sqlUpdateCommand1.Connection.Open();
sqlUpdateCommand1.ExecuteNonQuery(); //This line gives the errorstring mySqlQuery = "UPDATE CORP_TBL SET FLAG = 1 WHERE CORP_ID=" + Page.Session.Contents["CORP_ID"].ToString();SqlCommand myCommand = new SqlCommand(mySqlQuery, sqlConnection1);
myCommand.ExecuteNonQuery();
Page.Response.Redirect("main.aspx");
}
View 10 Replies
View Related
Mar 26, 2007
Has anyone encountered this before?
Procedure or Function 'stored procedure name' expects parameter '@parameter', which was not supplied.
It seems that my code is not passing the parameter to the stored procedure.
When I click this hyperlink:
<asp:HyperLink
ID="HyperLink1"
Runat="server"
NavigateUrl='<%# "../Division.aspx?CountryID=" + Eval("CountryID")%>'
Text='<%# Eval("Name") %>'
ToolTip='<%# Eval("Description") %>'
CssClass='<%# Eval("CountryID").ToString() == Request.QueryString["CountryID"] ? "CountrySelected" : "CountryUnselected" %>'>
</asp:HyperLink>
it is suppose to get the country name and description, based on the country id.
I am passing the country id like this.
protected void Page_Load(object sender, EventArgs e)
{
PopulateControls();
}
private void PopulateControls()
{
string countryId = Request.QueryString["CountryID"];
if (countryId != null)
{
CountryDetails cd = DivisionAccess.GetCountryDetails(countryId);
divisionNameLabel.Text = cd.Name;
divisionDescriptionLabel.Text = cd.Description;
}
}
To my app code like this:
public struct CountryDetails
{
public string Name;
public string Description;
}
public static class DivisionAccess
{
static DivisionAccess()
public static DataTable GetCountry()
{
DbCommand comm = GenericDataAccess.CreateCommand();
comm.CommandText = "GetCountry";
return GenericDataAccess.ExecuteSelectCommand(comm);
}
public static CountryDetails GetCountryDetails(string cId)
{
DbCommand comm = GenericDataAccess.CreateCommand();
comm.CommandText = "GetCountryDetails";
DbParameter param = comm.CreateParameter();
param.ParameterName = "@CountryID";
param.Value = 2;
param.DbType = DbType.Int32;
comm.Parameters.Add(param);
DataTable table = GenericDataAccess.ExecuteSelectCommand(comm);
CountryDetails details = new CountryDetails();
if (table.Rows.Count > 0)
{
details.Name = table.Rows[0]["Name"].ToString();
details.Description = table.Rows[0]["Description"].ToString();
}
return details;
}
As you can see I have two stored procedures I am calling, one does not have a parameter and the other does. The getcountry stored procedure returns the list of countries in a menu that I can click to see the details of that country. That is where my problem is when I click the country name I get
Procedure or Function 'GetCountryDetails' expects parameter '@CountryID', which was not supplied
Someone please help!
Thanks Nickdel68
View 5 Replies
View Related
Mar 23, 2007
Here is my code. Please help me evaluate? Why do i get thiis error. What am i doing wrong: System.Data.SqlClient.SqlException: Procedure 'SearchHRMS' expects parameter
'@SearchString', which was not supplied 1 If Not IsPostBack Then
2 '2 - declare variables
3 Dim conn As SqlConnection
4 Dim cmd As SqlCommand
5
6 '3 - create connection
7 conn = New SqlConnection
8 conn.ConnectionString = ConfigurationManager.ConnectionStrings("HRMSConnectionString").ConnectionString
9 conn.Open()
10
11 '4 - create command
12 cmd = New SqlCommand
13 cmd.CommandType = CommandType.StoredProcedure
14 cmd.CommandText = "SearchHRMS"
15 '4.2 -Declare a parameter
16 Dim param As SqlParameter
17 param = New SqlParameter("@SearchString", SqlDbType.VarChar, 20)
18 param.Direction = ParameterDirection.Input
19 param.Value = "john"
20
21 '5-Link command to connection
22 cmd.Connection = conn
23
24 'EmployeeListDataList.DataSource = cmd.ExecuteReader()
25 'EmployeeListDataList.DataBind()
26 'GridView1.DataSource = cmd.ExecuteReader()
27 'GridView1.DataBind()
28 conn.Close()
29 End If
View 3 Replies
View Related
Nov 25, 2007
Hi everyone, can you please take a look at this code and tell me where I'm wrong.I am getting the error Procedure 'usp_Insert_Attribution' expects parameter '@At_Code', which was not supplied <asp:SqlDataSource
id="ds_Attribution"
runat="server"
SelectCommand="usp_Select_Attribution"
SelectCommandType="StoredProcedure"
InsertCommand="usp_Insert_Attribution"
InsertCommandType="StoredProcedure"
DeleteCommand="usp_Delete_Attribution"
DeleteCommandType="StoredProcedure"
UpdateCommand="usp_Update_Attribution"
UpdateCommandType="StoredProcedure">
<SelectParameters>
<asp:Parameter Name="At_ID" Type="Int32"/>
</SelectParameters>
<InsertParameters>
<asp:Parameter Name="Identity" Direction="Output" Type="Int32"/>
</InsertParameters>
<DeleteParameters>
<asp:Parameter Name="At_ID" Type="Int32"/>
</DeleteParameters>
<UpdateParameters>
<asp:Parameter Name="At_ID" Type="Int32"/>
</UpdateParameters>
</asp:SqlDataSource> <InsertItemTemplate>
<table>
<tr>
<td>Attribution Code</td>
<td>
<asp:Textbox ID="txtAt_Code" runat="server" Text='<%# Eval("At_Code") %>' CssClass="textbox" />
<asp:RequiredFieldValidator ID="rfvAt_Code" runat="server" ControlToValidate="txtAt_Code"
Display="Static" Text="*" ErrorMessage="Attribution Code must be entered." />
<asp:CustomValidator ID="cvAt_Code" runat="server" Text="*" ErrorMessage="Attribution Code already exists."
Display="Static" />
</td>
</tr>
<tr>
<td>Attribution Description</td>
<td>
<asp:Textbox ID="txtAt_Description" runat="server" CssClass="textBox_Multiline" maxlength="250" TextMode="MultiLine" Text='<%# Bind("At_Description") %>'></asp:Textbox>
</td>
</tr>
</table>
</table>
<asp:Panel ID="pnlAttributionOptions_I" runat="server" GroupingText="Options" CssClass="panel">
<table width="100%">
<tr>
<td><asp:LinkButton ID="lnkCancel" runat="server" Text="Cancel" CommandName="Cancel" CssClass="button" CausesValidation="false"></asp:LinkButton>
</td><td><asp:LinkButton ID="lnkInsert" runat="server" Text="Save" CommandName="Insert" CssClass="button"></asp:LinkButton>
</td></tr></table>
</asp:Panel>
</InsertItemTemplate> CREATE PROCEDURE dbo.usp_Insert_Attribution
(
@At_Code varchar(10),
@At_Description varchar(250),
@At_SystemDate datetime,
@Identity int OUT
)
AS
SET NOCOUNT OFF;
INSERT INTO Attribution (At_Code, At_Description, At_SystemDate)
VALUES (@At_Code,@At_Description, @At_SystemDate);
SELECT * FROM Attribution WHERE (At_ID = SCOPE_IDENTITY())
SET @Identity = SCOPE_IDENTITY()
GO
View 2 Replies
View Related
Feb 24, 2004
I'm trying to update a member's record via stored procedure. The procedure is thus:
*********************************
CREATE PROCEDURE upd_MemberProfile(
@MemberID VarChar(10),
@FirstName VarChar(20),
@LastName VarChar(20),
@Address VarChar(50),
@City VarChar(40),
@StateID Char(2),
@ZipCode Char(5),
@Email VarChar(30),
@PayPalSubscrID VarChar(22)
) AS
UPDATE Members
SET FirstName = @FirstName, LastName = @LastName, Address = @Address, City = @City, StateID = @StateID, ZipCode = @ZipCode, Email = @Email, PayPalSubscrID = @PayPalSubscrID
WHERE MemberID = @MemberID
GO
*****************************
The call from the Webpage is like so:
*****************************
...
Dim Subscr_id As String = "123ABC-HDTV"
Try
cmd.CommandText = dbo() & "upd_MemberProfile"
cmd.CommandType = CommandType.StoredProcedure
With cmd.Parameters
.Add("@MemberID", MemberID)
.Add("@FirstName", FirstName)
.Add("@LastName", LastName)
.Add("@Address", Address)
.Add("@City", City)
.Add("@StateID", StateID)
.Add("@ZipCode", ZipCode)
.Add("@Email", Payer_email)
.Add("@PayPalSubscrID", Subscr_id)
End With
If cnn.State.Open Then
cnn.Close()
End If
cnn.Open()
cmd.ExecuteNonQuery()
cnn.Close()
Catch ex As Exception
'Notify Admin of the Problem
MailUsTheOrder("ERROR on upd_MemberProfile: " & ex.Message)
'Close the SQL Connection
If cnn.State.Open Then
cnn.Close()
End If
End Try
******************************
This was working two weeks ago. I have changed NOTHING and now I get the error:
Procedure 'upd_MemberProfile' expects parameter '@PayPalSubscrID', which was not supplied.
Any Ideas?
View 2 Replies
View Related
Oct 31, 2004
Hi - Im working with a sign up page in which I'd like to include the date that the member signed up. I keep getting this error and I was wondering if anyone could help. The error is:
Procedure 'spAddCustomer' expects parameter '@MemberSince', which was not supplied.
I have the date in a label on the form and then I am passing to the database with:
cmdAddCustomer.Parameters.Add("@MemberSince", today_date.text).
Can anyone help with what I am doing wrong? Thanks!
View 9 Replies
View Related
Sep 8, 2007
why is this? I posted this to the Windows forms group but this may be a problem with my spro?
here is the;
1. sproc in SQL Server Express (on Vista)
2. the code ( vb.net VS2008 Beta 2)
3. the error
http://www.hazzsoftwaresolutions.net/sprocParam_ex.htm
Thank you for any help.
Greg
View 5 Replies
View Related
Jan 26, 2007
I have this SP definition and below that the code to execute it from .Net. But I'm getting this error: Procedure 'GetNewId' expects parameter '@Key', which was not supplied.
Saw an solution on some site which said to set the value of the output param to NULL before calling it, but that didn't do anything.
Anyone have any ideas why this may be happening?? Thanks in advance.
CREATE PROC GetNewId(@TableName varchar(32), @Key bigint OUTPUT) AS
Declare @NextKey bigint
BEGIN TRAN
SELECT @NextKey = NewID from ID where TableName = @TableName
IF (SELECT @NextKey)IS NULL
BEGIN
INSERT INTO ID(NewID, TableName) VALUES(0, @TableName)
END
UPDATE ID
SET NewID = NewID + 1 Where TableName = @TableName
SELECT @NextKey = NewID from ID where TableName = @TableName
SET @Key=@NextKey
COMMIT TRAN
Return @Key
GO
---------------------------------------------------------------------------------------------
_SQLCommand.CommandText = theCommStr;
_SQLCommand.CommandType = CommandType.StoredProcedure;
SqlParameter aInputTblPrm = new SqlParameter("@TableName", "Jack");
aInputTblPrm.Direction = ParameterDirection.Input;
SqlParameter aReturnKeyPrm = new SqlParameter("@Key", SqlDbType.BigInt);
aReturnKeyPrm.Direction = ParameterDirection.InputOutput;
_SQLCommand.Parameters.Add(aInputTblPrm);
_SQLCommand.Parameters.Add(aReturnKeyPrm);
_SQLCommand.ExecuteNonQuery();
View 9 Replies
View Related
Apr 23, 2008
Hi
In my code I do this
Dim ds As DataSet = SqlHelper.ExecuteDataset(System.Configuration.ConfigurationManager.ConnectionStrings("ConnectionString").ToString(), "usp_SearchCar", dealer, vehicleType, bodyType, make, model, engineType, year, minPrice, maxPrice, transmission)
But I get the error (in the subject)
I check and the value of dealer is an empty string but that's ok right?
ThanksP
View 3 Replies
View Related
Feb 10, 2008
<html xmlns="http://www.w3.org/1999/xhtml">
<head id="Head1" runat="server"><title>Untitled Page</title> </head>
<body>
<form id="form1" runat="server">
<asp:ScriptManager ID="ScriptManager1" runat="server">
</asp:ScriptManager>
<div>
<br />
<asp:Label ID="lblUserInfo" runat="server" Text="Label"></asp:Label><asp:SqlDataSource ID="SqlDataSource1" runat="server"
ConnectionString="<%$ ConnectionStrings:JaiConnectionString %>" DeleteCommand="JaiDeleteUser" DeleteCommandType="StoredProcedure" SelectCommand="SELECT users.user_name, users.user_pass, user_roles.role_name, users.VendorId FROM users INNER JOIN user_roles ON users.user_name = user_roles.user_name"
UpdateCommand="JaiUpdateUser" UpdateCommandType="StoredProcedure">
<DeleteParameters>
<asp:Parameter Name="user_name" Type="String" />
<asp:Parameter Name="user_pass" Type="String" />
<asp:Parameter Name="role_name" Type="String" />
<asp:Parameter Name="VendorId" Type="Int32" />
</DeleteParameters>
<UpdateParameters>
<asp:Parameter Name="user_name" Type="String" />
<asp:Parameter Name="user_pass" Type="String" />
<asp:Parameter Name="role_name" Type="String" />
<asp:Parameter Name="VendorId" Type="Int32" />
</UpdateParameters>
</asp:SqlDataSource><asp:GridView ID="GridView1" runat="server" AllowSorting="True"
AutoGenerateColumns="False" DataSourceID="SqlDataSource1">
<Columns>
<asp:CommandField ShowDeleteButton="True" ShowEditButton="True" /><asp:BoundField DataField="user_name" HeaderText="user_name"
SortExpression="user_name" /><asp:BoundField DataField="user_pass" HeaderText="user_pass"
SortExpression="user_pass" /><asp:BoundField DataField="role_name" HeaderText="role_name"
SortExpression="role_name" /><asp:BoundField DataField="VendorId" HeaderText="VendorId"
SortExpression="VendorId" />
</Columns>
</asp:GridView>
<br />
<br />
</div></form> </body>
</html>
<<<<<<<<<<<<<Stored Procedure>>>>>>>>>>>>>>>>
CREATE PROCEDURE [dbo].[JaiDeleteUser] @user_name varchar (25), @user_pass varchar (25), @role_name Varchar (15), @VendorId intAS
beginDelete from user_roles where user_name =@user_nameDELETE FROM [users] WHERE user_name =@user_nameendGO
I am getting the error
Procedure 'JaiDeleteUser' expects parameter '@user_name', which was not supplied
whenever I try to delete a record. While Updating works with no problem. Please help.
View 8 Replies
View Related
Nov 28, 2007
I am running this query to an sql server 2000 database from my aspcode:"select * from MyTable whereMySqlServerRemoveStressFunction(MyNtextColumn) = '" &MyAdoRemoveStressFunction(MyString) & "'"The problem is that the replace function doesn't work with the ntextdatatype (so as to replace the stresses with an empty string). I hadto implement the MySqlServerRemoveStressFunction, i.e. a function thattakes a column name as a parameter and returns the text contained inthis column having replaced some letters of the text (the letters withstress). Unfortunately, I could not do that because user-definedfunctions cannot return a value of ntext.So I have the following idea:"select * from MyTable whereCheckIfTheyAreEqualIngoringTheStesses(MyNtextColum n, '" & MyString &"')"How can I implement the CheckIfTheyAreEqualIngoringTheStessesfunction? (I don't know how to combine these functions to do what Iwant: TEXTPTR, UPDATETEXT, WRITETEXT, READTEXT)
View 2 Replies
View Related
Oct 25, 2006
We have a email table in the database (SQL Server 2005). When we try to execute the Insert Stored Proc in the database to add a record and leave the EmailCC and EmailBCC fields blank we are getting the following error: Msg 201, Level 16, State 4, Procedure syl_EmailQueueInsert, Line 0Procedure or Function 'syl_EmailQueueInsert' expects parameter '@EmailCC', which was not supplied. The EmailCC and EmailBCC are set to allow nulls. Shouldn’t the system automatically insert nulls into those colums if no value is supplied??? What am I doing wrong? Newbie
View 4 Replies
View Related
Nov 11, 2007
Hi i am getting this error whenever i try to execute a stored procedure, below is my stored procedure;
ALTER PROCEDURE [dbo].[retrieve_product]
-- Add the parameters for the stored procedure here
@productPrice decimal (10,2),
@productInfoURL varchar (255),
@categoryName varchar (255),@companyName varchar (255),
@subCategoryName varchar (255),
@companyWebsiteURL varchar (255)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Insert statements for procedure here
SELECT Categories.categoryName, SubCategories.subCategoryName,Companies.companyName, Products.productPrice,
Products.productInfoURL FROM Categories
INNER JOIN Products ON Categories.categoryID = Products.categoryID
INNER JOIN Companies ON Products.companyID = Companies.companyID
INNER JOIN SubCategories ON Categories.categoryID = SubCategories.categoryID AND Products.subcategoryID = SubCategories.subCategoryID
END
View 8 Replies
View Related
Feb 21, 2008
How do I supply the parameter @Host? Below is the part of my code that has the call to the stored procedure
public void DeleteHostName()
{ // start of the method
using (SqlConnection cn = new SqlConnection(ConfigurationManager.AppSettings["ConnectionString"])) {
cn.Open(); SqlCommand cmd = new SqlCommand("hnDeleteHost", cn); cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.Add(new SqlParameter("@pHostID", SqlDbType.VarChar, 50, "HostNameID")); cmd.Parameters.Add(new SqlParameter("@pRet", SqlDbType.Int, 0, ParameterDirection.Output, false, 0, 0, " ", DataRowVersion.Default, null)); cmd.UpdatedRowSource = UpdateRowSource.None; cmd.ExecuteNonQuery(); }
View 1 Replies
View Related
Mar 21, 2008
Hi i have been trying to insert some values selected in a checkbox list into the database, however it is giving me the following error "Procedure or Function 'SubscribeToNewsletters' expects parameter '@emailAddress', which was not supplied. "public void Page_Load(object sender, EventArgs e) -- on page load the checkbox list is populated through stored procedure (stream_NewsletterTypes)
{
try
{SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["streamConnectionString"].ConnectionString);
SqlCommand command = new SqlCommand("stream_NewsletterTypes", conn); command.CommandType = CommandType.StoredProcedure;
command.Connection.Open();SqlDataReader datareader = command.ExecuteReader();
AlertList.DataSource = datareader;AlertList.DataTextField = "newsletterName";
AlertList.DataBind();
command.Connection.Close();
command.Connection.Dispose();
}catch (Exception ex)
{throw new Exception("Exception. " + ex.Message);
}
}
protected void Btn_Subscribe_Click(object sender, EventArgs e) when the button is clicked, the values should be sent to the database
{SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["streamConnectionString"].ConnectionString);
SqlCommand command = new SqlCommand("stream_NewsletterTypes", conn);command.CommandType = CommandType.StoredProcedure;for (int i = 0; i < AlertList.Items.Count; i++)
{if (AlertList.Items[i].Selected)
{
command.Parameters.AddWithValue("firstName", txtFirstName.Text);command.Parameters.AddWithValue("surname", txtSurname.Text);
command.Parameters.AddWithValue("emailAddress", txtEmail.Text);command.Parameters.AddWithValue("newsletterID", AlertList.Items[i].Value);
}
}
try
{
conn.Open();
command.ExecuteNonQuery();
}catch (Exception ex)
{throw new Exception("Exception adding account. " + ex.Message);
}
finally
{
conn.Close();
}
}
And my stored procedure;ALTER PROCEDURE [dbo].[SubscribeToNewsletters]
@emailAddress VARCHAR(250),
@firstName VARCHAR(250),
@surname VARCHAR(250),
@newsletterID INT,@userID INT OUTPUT
AS
INSERT INTO ThinkUsers (emailAddress, firstName, surname)
VALUES
(@emailAddress, @firstName, @surname)
Select @userID = @@Identity -- this is the ID created in the TheUserTable
-- when you make that first insert, the database
-- will generate the next ID value in that table
INSERT INTO SubscribedNewsletters (userID, newsletterID)
VALUES
(@userID, @newsletterID)
View 26 Replies
View Related
Apr 25, 2008
I have a stored procedure to validate the data from the table
CREATE PROCEDURE [dbo].[proc_Validate_BLDG]
@BLDGData varchar(50),@output int OUTAS
BeginIf Exists (Select DISTINCT GBC FROM ServerName.Database.dbo.Table where GBC = @BLDGData) begin set @output = 1 end
Else begin set @output = 0 end
return @output
endGO
Also, I have a function in my asp.net pages to call the stored procedure
Public Function BLDGLookup() As Integer
'Create a connectionDim objConn As SqlConnection
objConn = GetConnection()
'Create a command object for the query Dim objCommand As New SqlCommand("proc_Validate_BLDG", objConn)
objCommand.CommandType = CommandType.StoredProcedure
Dim param As SqlParameter = Nothing
Dim output As IntegerobjCommand.Parameters.AddWithValue("@BLDGData", strBLDGTextBox)param = objCommand.Parameters.Add("@output", SqlDbType.Int)
param.Direction = ParameterDirection.Output
Try
objCommand.ExecuteNonQuery()output = Int32.Parse(objCommand.Parameters("@output").Value.ToString())
Catch ex As ExceptionDim ex2 As Exception = ex
Dim errorMessage As String = String.EmptyWhile Not (ex2 Is Nothing)
errorMessage += ex2.ToString()
ex2 = ex2.InnerException
End While
Response.Write(errorMessage)
Finally
End TryReturn output
objConn.Close()
End Function
However, when i put in code to call this function and return a value I am getting thsi error:
System.Data.SqlClient.SqlException: Procedure 'proc_Validate_BLDG' expects parameter '@BLDGData', which was not supplied.
View 1 Replies
View Related
May 21, 2008
hay all, i'm trying to pass parameters to a stored procedure ..and i keep getting this error "Procedure or function 'selectFromView' expects parameter '@Year', which was not supplied." this is the procedure implementation : ALTER PROCEDURE dbo.selectFromView
@Year as varchar(10),
@Country as varchar(10),
@Family as varchar(10),
@Manu as varchar(10),
@Status as varchar(10),
@Type as varchar(10),
@Operator as varchar(10)
AS
SELECT * FROM ViewofAll
WHERE
ProductionYear = @Year and
CountryName = @Country and
Family = @Family and
Manufacturer = @Manu and
Status = @Status and
Type = @Type and
OperatorName = @Operator
RETURN and below how i call the procedure : Dim connection As SqlConnection
connection = New SqlConnection()
connection.ConnectionString = "Data Source=BLACKIRIS3SQLEXPRESS;Initial Catalog=Aircrafts;Integrated Security=True"
connection.Open()
Dim command As SqlCommand
command = New SqlCommand("selectFromView", connection)
command.CommandType = CommandType.StoredProcedure
command.Parameters.Add(New SqlParameter("@Year", System.Data.SqlDbType.VarChar, 100)).Direction = ParameterDirection.Input
command.Parameters.Add(New SqlParameter("@Country", System.Data.SqlDbType.VarChar, 100)).Direction = ParameterDirection.Input
command.Parameters.Add(New SqlParameter("@Family", System.Data.SqlDbType.VarChar, 100)).Direction = ParameterDirection.Input
command.Parameters.Add(New SqlParameter("@Manu", System.Data.SqlDbType.VarChar, 100)).Direction = ParameterDirection.Input 'Stored prcedure parameters
command.Parameters.Add(New SqlParameter("@Status", System.Data.SqlDbType.VarChar, 100)).Direction = ParameterDirection.Input
command.Parameters.Add(New SqlParameter("@Type", System.Data.SqlDbType.VarChar, 100)).Direction = ParameterDirection.Input
command.Parameters.Add(New SqlParameter("@Operator", System.Data.SqlDbType.VarChar, 100)).Direction = ParameterDirection.Input
command.Parameters.Item("@Year").Value = myArray(0)
command.Parameters.Item("@Country").Value = myArray(1)
command.Parameters.Item("@Family").Value = myArray(2)
command.Parameters.Item("@Manu").Value = myArray(3)
command.Parameters.Item("@Status").Value = myArray(4)
command.Parameters.Item("@Type").Value = myArray(5)
command.Parameters.Item("@Operator").Value = myArray(5)
DSGrid.SelectCommand = command.CommandText With DSGrid
.DataBind()
End With i'm not really familiar with stored procedures ...any one can help me plz ? thanx in advance .
View 3 Replies
View Related
Aug 27, 2004
Procedure 'sp_displaylabels' expects parameter '@mod_id', which was not supplied.
I get the error at the SQLAdptr1.Fill(DS): Procedure 'sp_displaylabels' expects parameter '@mod_id', which was not supplied.
But i am passing the @mod_id value in the string Valmodid.
Private Sub BindData()
Dim DS As New DataSet
Dim SQLCmd1 As New SqlCommand
Dim moduleId As New SqlParameter
Dim Valmodid As String
Valmodid = Request.QueryString("modid")
moduleId = SQLCmd1.Parameters.Add("@mod_id", Valmodid)
SQLCmd1 = New SqlCommand("sp_displaylabels", MyConnection)
Dim SQLAdptr1 As SqlDataAdapter = New SqlDataAdapter(SQLCmd1)
SQLAdptr1.Fill(DS)
MyDataGrid.DataSource = DS
MyDataGrid.DataBind()
End Sub
'''My Stored Procedure code:
CREATE PROCEDURE [dbo].[sp_displaylabels]
(
@mod_id nvarchar(10)
)
AS
SELECT *
FROM tbl_labels
where module_id=@mod_id ORDER BY id ASC
GO
View 1 Replies
View Related
Jan 4, 2007
Running [dbo].[insertProjectDetails] ( @ProjectTitle = <DEFAULT>, @ProjectPeriod = <DEFAULT>, @Client = <DEFAULT>, @Position = <DEFAULT>, @ProjectDescription = <DEFAULT>, @Responsiblities = <DEFAULT>, @OtherInformation = <DEFAULT> ).
Procedure or Function 'insertProjectDetails' expects parameter '@ProjectTitle', which was not supplied.
No rows affected.
(0 row(s) returned)
@RETURN_VALUE =
Finished running [dbo].[insertProjectDetails].
Can any body send solution for this error?
View 5 Replies
View Related
Jun 4, 2015
When i open any reports getting the below error message.An error occurred within the report server database. This may be due to a connection failure, timeout or low disk condition within the database.
(rsReportServerDatabaseError)Procedure or function 'CreateSession' expects parameter '@SiteZone', which was not supplied.
View 7 Replies
View Related
Nov 23, 2015
I have a report that uses a stored procedure as a dataset.The stored procedure accepts four parameters, which I have defined in the report. works fine in query builder and SSMS.
When I run the report I get an error stating a parameter is missing. I tried deleting parameters and recreating not luck.
View 4 Replies
View Related
Feb 21, 2008
Why am I getting this error, what does it mean and how can I fix it. Here is my stored proceduce and method to call it.
ALTER PROCEDURE dbo.hnDeleteHost(@pHostID varchar(50), @pRet int out)AS
SELECT Host FROM HostName WHERE HostNameID = @pHostID
if (@@ROWCOUNT > 0) begin DELETE FROM HostName WHERE HostNameID = @pHostID SET @pRet = @@ROWCOUNTendelse begin SET @pRet = -1end
And the method
using (SqlConnection cn = new SqlConnection(ConfigurationManager.AppSettings["ConnectionString"])) {
cn.Open(); SqlCommand cmd = new SqlCommand("hnDeleteHost", cn); cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.Add(new SqlParameter("@pHostID", SqlDbType.VarChar, 50, "HostNameID")); cmd.UpdatedRowSource = UpdateRowSource.None; cmd.ExecuteNonQuery(); }
View 4 Replies
View Related
