Passing Multiple Values From A Listbox Into A Stored Procedure

Dec 9, 2007

hi i have a listbox with selectedmode = multiple, i am currently using this code in my code behind (c#) to call the storedprocedure within the datasource but its not working: Do i have to write specific code in c# to send the mulitple values through?protected void confButton_Click(object sender, EventArgs e)

{

try

{foreach (ListItem item in authorsListBox4.Items)

{if (item.Selected)

{

AddConfSqlDataSource.Insert();

}

}saveStatusLabel.Text = "Save Successfull: The above publication has been saved";

}catch (Exception ex)

{saveStatusLabel.Text = "Save Failed: The above publication failed to save" + ex.Message;

}

}

View 3 Replies


ADVERTISEMENT

Passing Values To A Stored Procedure

Mar 21, 2008

I have a stored procedure. Into this stored procedure i need to pass values to a 'IN' statement from asp.net. So when i am passing it , it should b in like a string variable with the ItemIds separated by commas. the procedure i have is :


create procedure SelectDetails
@Id string
as
Select * from DtTable where itemid in(@Id)


Here the itemid field in DtTable is of type int. Now when i execute the produre it is showing error as the Itemid is int and i am passing a string value to it.
How can i solve this problem?

View 4 Replies View Related

How To Save Listbox Multiple Select Values

Jan 11, 2005

Hi,

I have an ASP.NET form that stores it's data in MSDE but I just added a multi-select ListBox to the form and I'm having a hard time coming up with a way of writing that data to the database. Should I write the values into a column on the same table where I store the rest of the data from the form (values separated by a comma) or shouild I create another table (one to many) and store the data there. I like the second option, but I'm not sure how to loop through each value and write it to the database table.

I grab the values for the selection as follow:

foreach (ListItem lstItem in lbAttendees.Items)
{
if (lstItem.Selected == true)
{
grpList.Add(lstItem.Value.ToString());
}
}

but I'm not sure on what to do next and could use some help.

Thanks
Germano

View 3 Replies View Related

Sending Multiple Values From A ListBox To ControlParameter

Dec 15, 2005

Any ideas on how I can send multiple values from a listbox to a stored procedure?  right now I have a ListBox Control called lbCategory, and I want to pass multiple selected items to a stored procedure.  
<asp:SqlDataSource ID="dsFS" runat="server" ConnectionString="someConnectionString" SelectCommand="usp_FS" SelectCommandType="StoredProcedure">
<SelectParameters>
<asp:ControlParameter ControlID="lbCategory" DefaultValue="%" Name="category" PropertyName="SelectedValue" type="String" />
</SelectParameters>
</asp:SqlDataSource>

View 5 Replies View Related

Transact SQL :: Passing Multiple String Param Values To Stored Proc

Jul 21, 2015

CREATE TABLE Test
(
EDate Datetime,
Code varchar(255),
Cdate int,
Price int
);

[Code] ....

Now I have to pass multiple param values to it. I was trying this but didnt get any success

exec
[SP_test]'LOC','LOP'

View 10 Replies View Related

Trouble With Passing Values To Stored Procedure

Feb 13, 2008

Hi All, I'm a newbie learning windows applications in visual basic express edition, am using sqlexpress 2005 So i have a log in form with username and password text fields.the form passes these values to stored procedure 'CheckUser' Checkuser then returns a value for groupid. If its 1 they are normal user, if its 2 its admin user. Then opens another form called Organisations, and closes the log in form. However when i run the project, and enter a username and password and press ok ti tells me that there is incorrect syntax beside a line. I have no idea, and I'm sure that there is probably other things wrong in there. here is the code for the login button click event:  Public Class Login Dim connString As String = "server = .SQL2005;" & "integrated security = true;" & "database = EVOC"
'Dim connString As String = _ '"server = .sqlexpress;" _ '& "database = EVOC;" _ '& "integrated security = true;"




Private Sub OK_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Login_Log_In.Click ' Create connection
Dim conn As SqlConnection = New SqlConnection(connString) ' Create command

Dim cmd As SqlCommand = New SqlCommand() cmd.Connection = conn cmd.CommandText = "CheckUser"

Dim inparam1 As SqlParameter = cmd.Parameters.Add("@Username", SqlDbType.NVarChar) inparam1.Value = Login_Username.ToString Dim inparam2 As SqlParameter = cmd.Parameters.Add("@Password", SqlDbType.NVarChar) inparam2.Value = Login_Password.ToString inparam1.Direction = ParameterDirection.Input inparam2.Direction = ParameterDirection.Input 'Return value
Dim return_value As SqlParameter = cmd.Parameters.Add("@return_value", SqlDbType.Int) return_value.Direction = ParameterDirection.ReturnValue cmd.Connection.Open() Dim rdr As SqlDataReader = cmd.ExecuteReader Dim groupID As Integer

groupID = return_value.Value

If groupID < 0 Then
MessageBox.Show("Access is Denied") Else Dim Username = Me.Login_Username Dim org As New Organisations org.Show() End If

conn.Close()



End Sub The stored procedure code is ok as i know it works as it should, but if it helps the code is: ALTER PROCEDURE [dbo].[CheckUser] -- Add the parameters for the stored procedure here
@UserName nvarchar(50) = N'', @Password nvarchar(50) = N''
ASBEGIN
-- SET NOCOUNT ON added to prevent extra result sets from-- interfering with SELECT statements.
SET NOCOUNT ON; -- Insert statements for procedure here
IF (SELECT COUNT(*) FROM dbo.EVOC_Users WHERE Username=@Username AND Password=@Password)=1BEGINPRINT 'User ' + @Username + ' exists'
SELECT TOP 1 UserGroup FROM dbo.EVOC_Users WHERE Username=@Username AND Password=@PasswordRETURN (SELECT TOP 1 UserGroup FROM dbo.EVOC_Users WHERE Username=@Username AND Password=@Password)ENDELSE BEGINPRINT 'User ' + @Username + ' does not exist'
RETURN (-1)ENDEND
 All help greatly appreciated to get me past this first hurdle in my first application!! CheersTom  

View 16 Replies View Related

Passing Values From A Gridview To A Stored Procedure

May 7, 2008

Hello. im tryinng to build an application that has a girdview with values from 2 different tables. The select query i have used in a stored procedure works great but when i try to write something to update into 2 different tables I cant figure out how to do it.
This is the first time im writing stored procedures but from what i can tell i need to do 2 seperate updates to insert mu values.
im using the AdventureWorksLT database provided by microsoft to experiment with and my gridview consists of 2 tables populated by the following stored procedure:ALTER PROCEDURE dbo.GetSomething AS
SELECT Customer.CustomerID, Customer.LastName, Customer.FirstName, CustomerAddress.AddressID, CustomerAddress.AddressType FROM SalesLT.Customer, SalesLT.CustomerAddress
WHERE Customer.CustomerID = CustomerAddress.CustomerID

RETURN
What id like to do is to make the GridView editable and then send the all thee values back so the changes are saved in both tables. Could anyone help me write a stored procedure that gets the values from the fields in the gridview that is beeing changed and send them back to the tables?

View 4 Replies View Related

Passing Parms With No Values To A Stored Procedure

Feb 28, 2008

I'm want to pass empty parm value to a stored procedure. I'm new at this stuff and not sure how to do this. To make it work I can put values in parms and it works great. I don't want to populate some of the fields in the table with values. The fields are cnty,dist,beat,convertedkey. Anyone know how I can get it done? Code below. Thanks for your help!

int shift = Convert.ToInt16(dr.Cells[1].Value.ToString());

int prior = Convert.ToInt16(dr.Cells[2].Value.ToString());

string lname = Convert.ToString(dr.Cells[3].Value.ToString());

string fname = Convert.ToString(dr.Cells[4].Value.ToString());

string unit = Convert.ToString(dr.Cells[5].Value.ToString());

string handle = Convert.ToString(dr.Cells.Value.ToString());

int badge = Convert.ToInt16(dr.Cells[7].Value.ToString());

string cnty = Convert.ToString('1');

string dist = Convert.ToString('1');

int beat = Convert.ToInt16(1);

int convertedkey = Convert.ToInt16(1);

char action = 'I';

char assign = 'Y';

var db = new dpoffassignedDataContext();

var q = db.SP_DpAssignAllOfficers(action, convertedkey, shift, prior, lname, fname, unit, handle, cnty, dist, beat, badge, assign);





Stored procedure below;

ALTER PROCEDURE SP_DpAssignAllOfficers

(

@action nvarchar(1),

@int_id int,

@shift int,

@prior int,

@lname nvarchar(20),

@fname nvarchar(20),

@unit nvarchar(5),

@handle nvarchar(10),

@cnty nvarchar(10),

@dist nvarchar(10),

@beat int,

@badge int,

@assign char



)

AS

INSERT

INTO dpofficers(dpo_prior,dpo_lname,dpo_fname,dpo_unit,dpo_handle,dpo_cnty,dpo_dist,dpo_beat,dpo_badge,dpo_shift)

VALUES (@prior,@lname,@fname,@unit,@handle,@cnty,@dist,@beat,@badge,@shift)

View 6 Replies View Related

Passing A Column Of Values To The Stored Procedure

Apr 18, 2006

how can i send a column of values to the stored procedure for filtering that stored procedure values...?

View 1 Replies View Related

Why Wont This Multiple Selection Listbox Insert Values Into Db

Jan 19, 2008

Hi, im a newbie. using VB.net / visual studio 2005 and SQL express
I have pasted my page code below
Basically i have a lisbox which is bound to an sqldatasource.  It allows the user to select multiple names.  I then have another datasource which when the page is submitted it posts various values from different fields into my db.  The insert works fine.My problem is that the multiple selection wont allow me to insert into my db, it just captures the first value.I used this sub below to prove  that when i click a button on my page it takes all my selected choices and puts them inside a text box, this works i can see them all.  I then bound the textbox text value to the one im inserting into the db, however once again its only inserting the first value from the text box, how is this possible, the text box shows all the values and its set to string, it should insert everything in the text box Protected Sub selectedContact_Click(ByVal sender As Object, ByVal e As System.EventArgs)
Dim alternatives As ListBox = CType(UploadPictureUI.FindControl("alternatives"), ListBox)
Dim TextBox1 As TextBox = CType(UploadPictureUI.FindControl("TextBox1"), TextBox)
For intLoopIndex As Integer = 0 To alternatives.Items.Count - 1
If alternatives.Items(intLoopIndex).Selected Then
TextBox1.Text &= alternatives.Items(intLoopIndex).Text & _
ControlChars.CrLf
End If
Next
End Sub
 Here is my full page code
 1 <%@ Page Language="VB" MasterPageFile="~/MasterPage.master" Title="Create Profile" Debug="true" %>
2 <script runat="server">
3 Protected Sub UploadPictureUI_ItemInserting(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.DetailsViewInsertEventArgs) Handles UploadPictureUI.ItemInserting
4 'Reference the FileUpload control
5 Dim UploadedFile As FileUpload = CType(UploadPictureUI.FindControl("UploadedFile"), FileUpload)
6
7 'Make sure a file has been successfully uploaded
8 If UploadedFile.PostedFile Is Nothing OrElse String.IsNullOrEmpty(UploadedFile.PostedFile.FileName) OrElse UploadedFile.PostedFile.InputStream Is Nothing Then
9 ShowErrorMessage("No file was uploaded. Please make sure that you've selected a file to upload.")
10 e.Cancel = True
11 Exit Sub
12 End If
13
14 'Make sure we are dealing with a JPG or GIF file
15 Dim extension As String = Path.GetExtension(UploadedFile.PostedFile.FileName).ToLower()
16 Dim MIMEType As String = Nothing
17
18 Select Case extension
19 Case ".gif"
20 MIMEType = "image/gif"
21 Case ".jpg", ".jpeg", ".jpe"
22 MIMEType = "image/jpeg"
23 Case ".png"
24 MIMEType = "image/png"
25
26 Case Else
27 'Invalid file type uploaded
28 ShowErrorMessage("Only GIF, JPG, and PNG files can be uploaded.")
29 e.Cancel = True
30 Exit Sub
31 End Select
32
33 'Specify the values for the MIMEType and ImageData parameters
34 e.Values("MIMEType") = MIMEType
35
36 'Load FileUpload's InputStream into Byte array
37 Dim imageBytes(UploadedFile.PostedFile.InputStream.Length) As Byte
38 UploadedFile.PostedFile.InputStream.Read(imageBytes, 0, imageBytes.Length)
39 e.Values("imageData") = imageBytes
40 End Sub
41
42 Private Sub ShowErrorMessage(ByVal msg As String)
43 ErrorMessage.Text = msg
44 ErrorMessage.Visible = True
45 End Sub
46
47 Protected Sub UploadPictureUI_ItemInserted(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.DetailsViewInsertedEventArgs) Handles UploadPictureUI.ItemInserted
48 'If item successfully inserted, send user back to default
49 If e.Exception Is Nothing Then
50 Response.Redirect("~/confirmation.aspx")
51 End If
52 End Sub
53
54 Protected Sub selectedContact_Click(ByVal sender As Object, ByVal e As System.EventArgs)
55 Dim alternatives As ListBox = CType(UploadPictureUI.FindControl("alternatives"), ListBox)
56 Dim TextBox1 As TextBox = CType(UploadPictureUI.FindControl("TextBox1"), TextBox)
57 For intLoopIndex As Integer = 0 To alternatives.Items.Count - 1
58 If alternatives.Items(intLoopIndex).Selected Then
59 TextBox1.Text &= alternatives.Items(intLoopIndex).Text & _
60 ControlChars.CrLf
61 End If
62 Next
63 End Sub
64
65
66 </script>
67 <asp:Content ID="Content1" ContentPlaceHolderID="ContentPlaceHolder1" Runat="Server">
68 <table border="0" cellpadding="0" cellspacing="0" style="vertical-align: middle;
69 width: 795px; text-align: center">
70 <tr>
71 <td align="center" style="height: 650px" valign="middle">
72 <asp:DetailsView ID="UploadPictureUI" runat="server" AutoGenerateRows="False" CssClass="createProfile"
73 DataKeyNames="id" DataSourceID="accessProfiles" GridLines="None" DefaultMode="Insert">
74 <Fields>
75 <asp:TemplateField HeaderText="Create Profile">
76 <InsertItemTemplate>
77 <table style="width: 350px">
78 <tr>
79 <td align="center" style="width: 150px; height: 30px">
80 Name:</td>
81 <td align="center" style="width: 200px; height: 30px">
82 <asp:TextBox ID="Nametxtbx" runat="server" CssClass="textboxstyle" Text='<%# Bind("Name") %>' MaxLength="20"></asp:TextBox>
83 <asp:RequiredFieldValidator ID="RequiredFieldValidator1" runat="server" ControlToValidate="Nametxtbx"
84 CssClass="errorMessage" ErrorMessage="First Name is Required" ForeColor="">*</asp:RequiredFieldValidator></td>
85 </tr>
86 <tr>
87 <td align="center" style="width: 150px; height: 30px">
88 Email:</td>
89 <td align="center" style="width: 200px; height: 30px">
90 <asp:TextBox ID="emailtxtbx" runat="server" CssClass="textboxstyle" Text='<%# Bind("email") %>'></asp:TextBox>
91 <asp:RequiredFieldValidator ID="RequiredFieldValidator3" runat="server" ControlToValidate="emailtxtbx"
92 CssClass="errorMessage" ErrorMessage="Email Address is Required" ForeColor="">*</asp:RequiredFieldValidator>
93 <asp:RegularExpressionValidator ID="RegularExpressionValidator1" runat="server" ControlToValidate="emailtxtbx"
94 ErrorMessage="RegularExpressionValidator" ForeColor="" ValidationExpression="w+([-+.']w+)*@w+([-.]w+)*.w+([-.]w+)*">*</asp:RegularExpressionValidator></td>
95 </tr>
96 <tr>
97 <td align="center" style="width: 150px; height: 30px">
98 Mobile:</td>
99 <td align="center" style="width: 200px; height: 30px">
100 &nbsp;<asp:TextBox ID="mobiletxtbx" runat="server" CssClass="textboxstyle" Text='<%# Bind("mobile") %>' MaxLength="11"></asp:TextBox>
101 <asp:RequiredFieldValidator ID="RequiredFieldValidator4" runat="server" ControlToValidate="mobiletxtbx"
102 CssClass="errorMessage" ErrorMessage="Mobile Number is Required" ForeColor="">*</asp:RequiredFieldValidator>
103 </td>
104 </tr>
105 <tr>
106 <td align="center" style="width: 150px; height: 30px">
107 Extension:</td>
108 <td align="center" style="width: 200px; height: 30px">
109 <asp:TextBox ID="extensiontxtbox" runat="server" CssClass="textboxstyle" Text='<%# Bind("extension") %>' MaxLength="11"></asp:TextBox>
110 <asp:RequiredFieldValidator ID="RequiredFieldValidator5" runat="server" ControlToValidate="extensiontxtbox"
111 CssClass="errorMessage" ErrorMessage="Phone Extension is Required" ForeColor="">*</asp:RequiredFieldValidator></td>
112 </tr>
113 <tr>
114 <td align="center" style="width: 150px; height: 30px">
115 Alternative Contact:</td>
116 <td align="center" style="width: 200px; height: 30px">
117 <asp:ListBox ID="alternatives" runat="server" CssClass="listboxstyle"
118 DataSourceID="getAlternatives" DataTextField="Name" DataValueField="Name" SelectedValue='<%# Bind("alternativeContact") %>'
119 SelectionMode="Multiple"></asp:ListBox>
120 <asp:RequiredFieldValidator ID="RequiredFieldValidator6" runat="server" ControlToValidate="alternatives"
121 CssClass="errorMessage" ErrorMessage="Alternative Contact is Required" ForeColor="">*</asp:RequiredFieldValidator></td>
122 </tr>
123 <tr>
124 <td align="center" style="width: 150px; height: 30px">
125 Job Role:</td>
126 <td align="center" style="width: 200px; height: 30px">
127 <asp:DropDownList ID="DropDownList2" runat="server" CssClass="dropdownliststyle" DataSourceID="accessrole"
128 DataTextField="role" DataValueField="roleID" AppendDataBoundItems="True" SelectedValue='<%# Bind("roleID") %>'>
129 <asp:ListItem Value="" Selected="False">- Please Select -</asp:ListItem>
130 </asp:DropDownList>
131 <asp:RequiredFieldValidator ID="RequiredFieldValidator7" runat="server" ControlToValidate="DropDownList1"
132 CssClass="errorMessage" ErrorMessage="Job Role is Required" ForeColor="">*</asp:RequiredFieldValidator></td>
133 </tr>
134 <tr>
135 <td align="center" style="width: 150px; height: 30px">
136 Responsibilities:</td>
137 <td align="center" style="width: 200px; height: 30px">
138 <asp:TextBox ID="resposibilities" runat="server" CssClass="textareastyle" Text='<%# Bind("responsibilities") %>'
139 TextMode="MultiLine" MaxLength="200"></asp:TextBox>
140 <asp:RequiredFieldValidator ID="RequiredFieldValidator8" runat="server" ControlToValidate="resposibilities"
141 CssClass="errorMessage" ErrorMessage="Role Responsibilities are Required" ForeColor="">*</asp:RequiredFieldValidator></td>
142 </tr>
143 <tr>
144 <td align="center" style="width: 150px; height: 29px">
145 Hobbies:</td>
146 <td align="center" style="width: 200px; height: 29px">
147 <asp:TextBox ID="hobbiestxtbx" runat="server" CssClass="textareastyle" Text='<%# Bind("hobbies") %>'
148 TextMode="MultiLine" MaxLength="200"></asp:TextBox></td>
149 </tr>
150 <tr>
151 <td align="center" style="width: 150px; height: 30px">
152 Team Name:</td>
153 <td align="center" style="width: 200px; height: 30px">
154 <asp:DropDownList ID="DropDownList1" runat="server" CssClass="dropdownliststyle" DataSourceID="accessTeam"
155 DataTextField="TeamName" DataValueField="TeamID" AppendDataBoundItems="True" SelectedValue='<%# Bind("TeamID") %>'>
156 <asp:ListItem Value="" Selected="False">- Please Select -</asp:ListItem>
157 </asp:DropDownList>
158 <asp:RequiredFieldValidator ID="RequiredFieldValidator9" runat="server" ControlToValidate="DropDownList2"
159 CssClass="errorMessage" ErrorMessage="Team Name is Required" ForeColor="">*</asp:RequiredFieldValidator></td>
160 </tr>
161 <tr>
162 <td align="center" style="width: 150px; height: 30px">
163 I am a Manager:</td>
164 <td align="center" style="width: 200px; height: 30px">
165 <asp:CheckBox ID="isManager" runat="server" Checked='<%# Bind("isManager") %>' Text="Yes" /></td>
166 </tr>
167 <tr>
168 <td align="center" style="width: 150px; height: 30px">
169 I am a Team Manager:</td>
170 <td align="center" style="width: 200px; height: 30px"><asp:CheckBox ID="isTeamManager" runat="server" Checked='<%# Bind("isTeamManager") %>' Text="Yes" /></td>
171 </tr>
172 <tr>
173 <td align="center" style="width: 150px; height: 30px">
174 MyManager is:</td>
175 <td align="center" style="width: 200px; height: 30px">
176 <asp:DropDownList ID="myManagerIs" runat="server" CssClass="dropdownliststyle" DataSourceID="MyManager"
177 DataTextField="Name" DataValueField="id" AppendDataBoundItems="true" SelectedValue='<%# Bind("teamManagerID") %>'>
178 <asp:ListItem Value="" Selected="False">- Please Select -</asp:ListItem>
179 </asp:DropDownList>
180 </td>
181 </tr>
182 <tr>
183 <td align="center" style="width: 150px; height: 30px">
184 Upload Image:</td>
185 <td align="center" style="width: 200px; height: 30px">
186 <asp:FileUpload ID="UploadedFile" runat="server"
187 Width="150px" />&nbsp;</td>
188 </tr>
189 <tr>
190 <td align="center" style="width: 150px; height: 30px">
191 Image Title:</td>
192 <td align="center" style="width: 200px; height: 30px">
193 <asp:TextBox ID="imagetitle" runat="server" CssClass="textboxstyle" Text='<%# Bind("imageTitle") %>' MaxLength="30"></asp:TextBox>
194 <asp:RequiredFieldValidator ID="RequiredFieldValidator10" runat="server" ControlToValidate="imagetitle"
195 CssClass="errorMessage" ErrorMessage="Image Title is Required" ForeColor="">*</asp:RequiredFieldValidator></td>
196 </tr>
197 <tr>
198 <td align="center" style="width: 150px; height: 30px">
199 Hide This Profile:</td>
200 <td align="center" style="width: 200px; height: 30px">
201 <asp:CheckBox ID="profileHide" runat="server" Checked='<%# Bind("Display") %>' /></td>
202 </tr>
203 <tr>
204 <td align="center" colspan="2" style="height: 30px">
205 <asp:TextBox ID="TextBox1" runat="server" Text='<%# Eval("alternativeContact") %>'></asp:TextBox><br />
206 <asp:LinkButton ID="selectedContact" runat="server" OnClick="selectedContact_Click" OnClientClick="selectedContact_Click">Add Selected Contacts</asp:LinkButton></td>
207 </tr>
208 <tr>
209 <td align="center" colspan="2" style="height: 30px">
210 <asp:ValidationSummary ID="ValidationSummary1" runat="server" CssClass="errorMsgstyle"
211 ForeColor="" />
212 </td>
213 </tr>
214 </table>
215 </InsertItemTemplate>
216 <HeaderTemplate>
217 &nbsp;
218 </HeaderTemplate>
219 </asp:TemplateField>
220 <asp:TemplateField ShowHeader="False">
221 <EditItemTemplate>
222 &nbsp;
223 </EditItemTemplate>
224 <InsertItemTemplate>
225 <asp:LinkButton ID="btnInsert" runat="server" CausesValidation="True" CommandName="Insert"
226 Text="Insert"></asp:LinkButton>
227 <asp:LinkButton ID="btnCancel" runat="server" CausesValidation="False" CommandName="Cancel"
228 Text="Cancel"></asp:LinkButton>
229 </InsertItemTemplate>
230 <ItemTemplate>
231 &nbsp;
232 </ItemTemplate>
233 </asp:TemplateField>
234 </Fields>
235 </asp:DetailsView>
236 <br />
237 <asp:Label ID="ErrorMessage" runat="server" CssClass="errortext"></asp:Label>&nbsp;<br />
238 &nbsp;
239 &nbsp; &nbsp;&nbsp;</td>
240 </tr>
241 <tr>
242 <td style="width: 100px">
243 <asp:SqlDataSource ID="accessProfiles" runat="server" ConnectionString="<%$ ConnectionStrings:ConnectionString %>"
244 InsertCommand="INSERT INTO [employeeDetails] ([isManager], [isTeamManager], [Name], [RoleID], , [extension], [mobile], [alternativeContact], [imageTitle], [imageMimeType], [imageData], [TeamID], [Display], [teamManagerID], [responsibilities], [hobbies]) VALUES (@isManager, @isTeamManager, @Name, @RoleID, @email, @extension, @mobile, @alternativeContact, @imageTitle, @MimeType, @imageData, @TeamID, @Display, @teamManagerID, @responsibilities, @hobbies)" >
245 <InsertParameters>
246 <asp:Parameter Name="isManager" Type="byte" DefaultValue="0" Size="1" />
247 <asp:Parameter Name="isTeamManager" DefaultValue="0" Size="1" Type="Byte" />
248 <asp:Parameter Name="Name" Type="String" />
249 <asp:Parameter Name="RoleID" Type="Int32" />
250 <asp:Parameter Name="email" Type="String" />
251 <asp:Parameter Name="extension" Type="String" />
252 <asp:Parameter Name="mobile" Type="String" />
253 <asp:Parameter Name="alternativeContact" Type="String" />
254 <asp:Parameter Name="imageTitle" Type="String" />
255 <asp:Parameter Name="MimeType" Type="String" />
256 <asp:Parameter Name="imageData" />
257 <asp:Parameter Name="TeamID" Type="Int32" />
258 <asp:Parameter Name="Display" type="Byte" DefaultValue="1" Size="0" />
259 <asp:Parameter Name="teamManagerID" Type="Int32" />
260 <asp:Parameter Name="responsibilities" Type="String" />
261 <asp:Parameter Name="hobbies" Type="String" />
262 </InsertParameters>
263 </asp:SqlDataSource>
264 <asp:SqlDataSource ID="accessrole" runat="server" ConnectionString="<%$ ConnectionStrings:ConnectionString %>"
265 SelectCommand="SELECT [role], [RoleID] FROM [role] ORDER BY [role] DESC"></asp:SqlDataSource>
266 <asp:SqlDataSource ID="MyManager" runat="server" ConnectionString="<%$ ConnectionStrings:ConnectionString %>"
267 SelectCommand="SELECT id, Name, isManager, isTeamManager FROM employeeDetails WHERE (isManager > 0) OR (isTeamManager > 0) ORDER BY Name DESC">
268 </asp:SqlDataSource>
269 <asp:SqlDataSource ID="accessTeam" runat="server" ConnectionString="<%$ ConnectionStrings:ConnectionString %>"
270 SelectCommand="SELECT [TeamID], [TeamName] FROM [Team] ORDER BY [TeamName] ASC"></asp:SqlDataSource>
271 <asp:SqlDataSource ID="getAlternatives" runat="server" ConnectionString="<%$ ConnectionStrings:ConnectionString %>"
272 SelectCommand="SELECT [Name] FROM [employeeDetails] ORDER BY [Name]"></asp:SqlDataSource>
273 </td>
274 </tr>
275 </table>
276 </asp:Content>
277
278

 
Thanks for your help

View 1 Replies View Related

Passing Compound Primary Key Values To A Stored Procedure Using ADO.NET

Apr 10, 2008

Hi all,
I've had this problem for a while now and I'm looking for a better solution.


I'm pulling through a dataset from a SQL Server 2005 database and populating it into a DataGridView. The end user updates information on the grid and I then want to updates the results in the SQL Server table. My table has a compound primary key made up of two fields.


I'm currently looping through the data grid and for each value identified to update I'm executing a stored procedure that takes the two fields as parameters. However I know that this method isn't very efficient, especially if the user wants to update a few hundred records as it will execute the stored procedure a few hundred times.


Is there a way I can pass the two parameters in an ArrayList or something like that? I need SQL Server to be able to take the parameter. Is XML the way to go or is there any additional support in the .NET framework for such problems?


Thanks for your help.

View 7 Replies View Related

Passing Multiple Parameters To Stored Procedure Using SqlDataSource

Oct 9, 2007

Hi,I have a stored procedure that takes 3 parameters. I am using a sqldatasource to pass the values to the stored procedure. To better illustrated what I just mention, the following is the code behind:SqlDataSource1.SelectCommand = "_Search"SqlDataSource1.SelectParameters.Add("Field1", TextBox1.Text)SqlDataSource1.SelectParameters.Add("Field2", TextBox2.Text)SqlDataSource1.SelectParameters.Add("Field3", TextBox3.Text)SqlDataSource1.SelectCommandType = SqlDataSourceCommandType.StoredProcedureGridView1.DataSourceID = "SqlDataSource1"GridView1.DataBind()MsgBox(GridView1.Rows.Count) It doesn't return any value. I am wondering is that the correct way to pass parameters to stored procedure?Stan 

View 2 Replies View Related

Passing Parameter Values To Full Text Search Stored Procedure

Oct 24, 2006

I thought this would be quite simple but can't find the correct syntax:ALTER Procedure usp_Product_Search_Artist_ProductTitle

(@ProductTitle varchar(255))

AS

SELECT ProductTitle

FROM tbl_Product


WHERE CONTAINS(dbo.tbl_Product.ProductTitle, @ProductTitle)
   My problem is that if I pass "LCD Flat Screen" as teh @ProductTitle parameter the query falls over as it contains spaces. Guess i'm looking for something like: WHERE CONTAINS(dbo.tbl_Product.ProductTitle, '"' + @ProductTitle + "'")Thanks in advance.R  

View 4 Replies View Related

T-SQL (SS2K8) :: Passing Multiple Parameters With Table Valued Parameter To Stored Procedure?

May 21, 2014

Can we Pass table valued parameters and normal params like integer,varchar etc..to a single stored procedure?

View 1 Replies View Related

ListBox Web Server Control And Stored Procedure

May 15, 2008

Hi, I've a  ListBox Web Server Control where I select a list of citiesnow I would like to create a stored procedure with the selected values... please note that I use metods like these for execute my Stored procedure public DataSet Getgfdfgh(SqlConnection connection, String IDAttivitaTipo, DateTime DataInizio, DateTime DataFine)        {            ConnectionState currState = connection.State;            if (((connection.State & ConnectionState.Open) != ConnectionState.Open))                connection.Open();            try            {                SqlParameter[] parameters = new SqlParameter[3];                parameters[0] = new SqlParameter("@IDAttivitaTipo", IDAttivitaTipo);                parameters[1] = new SqlParameter("@DataInizio", DataInizio);                parameters[2] = new SqlParameter("@DataFine", DataFine);                SqlCommand cmd = CreateStoreProcedureCommand("Getfhdfh", connection, parameters);                SqlDataAdapter adapter = new SqlDataAdapter(cmd);                DataSet ds = new DataSet();                adapter.Fill(ds);                return ds;            }            finally            {                if ((currState == ConnectionState.Closed))                    connection.Close();            }        }   How Can I manage a list of values ??Thanks for help me!! 

View 1 Replies View Related

Handling Multiple Values From A Stored Procedure In ASP.NET.

Jan 21, 2008

Hi all, 
I’m returning two values from a stored procedure, one is a basic string confirming that an email has been sent and the other is the normal value returned from running an INSERT statement. So in my code I’m using the ExecuteNonQuery() method. I’m not sure how to handle both returned values in my code in my data layer. This is what I have:
ExecuteNonQuery(cmd);
return Convert.ToString(cmd.Parameters["@ReturnedValue"].Value).ToLower();
Obviously I’d need to return the value returned by the ExecuteNonQuery method as well, normally I’d simply convert the value to an int and precede this with the return keyword like so:
return (int)ExecuteNonQuery(cmd);
Obviously I can’t do this as I need to return two values, the normal value returned by the ExecuteNonQuery() method and my own output parameter value. Any ideas how I can do both? My current method containing the code further above returns a string but clearly this doesn’t help. I’m guessing that maybe I should return an object array so I can return both values? I haven’t encountered this problem before so I’m just guessing. Please help.
Thanks

View 4 Replies View Related

Returning Multiple Values From A Stored Procedure.

Feb 7, 2007

my stored procedure performs actions of deletion and insertion. Both the inserted and deleted items are output in temp tables with single column.
Is there a way to return the content of these two tables?
Is there a way to return a table from the stored procedure?

Thanks in advance
waamax

View 1 Replies View Related

Listbox Contents To Sql Stored Procedure Command Parameter?

Feb 11, 2004

Hello,

Stuck in a spot and hoping someone will nudge me in the right direction....

I'm trying to write to a sql db via a storedprocedure using a parameter. i'm pretty certain the below statement is causing the problem. but i'm not sure how to properly refer to it....


Dim connString As String
connString = "integrated security=false;user id=sa;server=HCENT1;database=LicenseRenewal;persist security info=False"
Dim myConnection As New SqlConnection(connString)
Dim myCommand As New SqlCommand("InsertPage1", myConnection)
myCommand.CommandType = CommandType.StoredProcedure
' .......other (working) parameter statements.........
Dim parameterDates As New SqlParameter("@Dates", SqlDbType.VarChar, 4000)
parameterDates.Value = Session("lstDates")
myCommand.Parameters.Add(parameterDates)

myConnection.Open()
myCommand.ExecuteNonQuery()
myConnection.Close()


the session("lstDates") is the contents of lstDates.Items (listbox) from a previous page.
i'm guessing its not valid to refer to it as a varchar, can someone point me to the proper way to handle this?

tia
andy

View 4 Replies View Related

How To Pass Multiple Values To An IN Clause Through Stored Procedure

Jun 11, 2004

I created a stored procedure like the following in the hope that I can pass mulitple company_id to the select statement:


CREATE PROC sp_test @in_company_code nvarchar(1024)
AS

select company_code, name, description
from member_company
where company_code in (@in_company_code)


However, I tried the following :

exec sp_test 'abc', 'rrd', 'bbc'

Procedure or function sp_test has too many arguments specified.

and SQLServer doesn't like it.

Did I specify this stored procedure correct?
If so, how can I can pass multiple values to the stored procedure then to the sql statement?
If not, is it possible to specify a stored procedure like this?

Thanks!

View 2 Replies View Related

Compare With Multiple Date Values Passed Into Stored Procedure?

Dec 14, 2012

I have a scenario where I need to compare a single DateTime field in a stored procedure against multiple values passed into the proc.So I was thinking I could pass in the DateTime values into the stored procedure with a User Defined Table type ... and then in the stored procedure I would need to run through that table of values and compare against the CreatedWhenUTC value.I could have the following queries for example:

WHERE CreatedWhenUTC <= dateValue1 OR CreatedWhenUTC <= dateValue2 OR CreatedWhenUTC <= dateValue 3

The <= is determined by another operator param passed in. So the query could also be:

WHERE CreatedWhenUTC > dateValue1 OR CreatedWhenUTC > dateValue2 OR CreateWhenUTC > dateValue3 OR CreateWhenUTC > dateValue4

View 3 Replies View Related

JDBC: Calling A Stored Procedure With Multiple Return Values.

Jul 23, 2005

Using JDBC, is there a way to call a stored procedure with multiplereturn values? Thanks.

View 4 Replies View Related

Can The OLE DB Command Transformation Support Multiple Values Returned By A Stored Procedure Or Is The Limit One Value Only?

Apr 10, 2007

I have no problem getting OLE DB Command transformations to support single returns by a procedure.

For example, exec name_of_procedure ?,?,? OUTPUT



However, I have a stored procedure which accepts 1 input and returns 5 outputs. This procedure works fine at the command line but when I try to incorporate it into a OLE DB Command I don't get the multiple values returned. There's no problem at all configuring the transform as it recognizes all input and output parameters. For some reason I just don't get values returned.



thanks

John

View 14 Replies View Related

Integration Services :: Pass Multiple Parameter Values To SSIS Package In 2012 Through Stored Procedure?

Jul 9, 2015

we can  assign one parameter value for each excecution of  [SSISDB].[catalog].[set_object_parameter_value] by calling this catalog procedure..

Example: If I have 5 parameters in SSIS package ,to assign a value to those 5 parameters at run time should I call this [SSISDB].[catalog].[set_object_parameter_value] procedure 5 times ? or is there a way we can pass all the 5 parameters at 1 time .

1. Wondering if there is a way to pass multiple parameters in a single execution (for instance to pass XML string values ??)
2.What are the options to pass multiple parameter values to ssis package through stored procedure.?

View 4 Replies View Related

Passing Multiple Values Into A Subreport

May 7, 2008

I am doing report development against OLAP (Cube). I have a parameter which is a multi-value parameter, and I need to pass this as a parameter into a sub-report (pass all the values selected in this multi-value from the main report to sub report parameter).

Is this even possible? If yes how do I achieve this.

Currenty I sent like Fields!Region.Value(0), but I want to send actually Fields!Region.Value() (meaning all selected).

Any help ASAP is appreciated.

View 3 Replies View Related

Passing Multiple Values To A Paramter

Apr 20, 2007

Hi,



I'm trying to pass multiple values to a single parameter from a report to a second report. For instance I want to pass the values a user selected in the original report, such as the countries a user select under a Country filter, and once the second report is called, I want that report to filter on those same countries, right now I can only pass one of the values selected to the second report. If someone can let me know if this is possible it'd be much appreciated, thanks in advance.

View 1 Replies View Related

Passing Multiple Values To A Single Parameters

Dec 28, 2007

I have a SQL query that goes like this
"select * from Product where ProductID in (1,2,3)"
How can i create a stored procedure where a single input parameter can take multiple values?
Can anyone help me with this?

View 4 Replies View Related

Passing Multiple Values From Parent To Child Package

Dec 20, 2006

Starting with "How to: Use Values of Parent Variables in Child Packages" in the SQL Server 2005 Books Online (http://msdn2.microsoft.com/en-us/library/ms345179.aspx), it seems I need to create a separate package configuration in the child package (of type parent package variable) for each variable I want to pass from the parent to the child. Is that really so? The XML configuration file type allows me to specify any number of variables; how do I do that with the parent package variable?

For that matther, why doesn't the Execute Package Task simply allow me to specify the values of child variables (or other properties) directly? It seems SSIS has made something as trivial as a series of function calls completely opaque:

MyChildPackage(var1=1, var2="foo");

MyChildPackage(var1=2, var2="bar");

MyChildPackage(var1=3, var2="baz");

View 2 Replies View Related

Passing Multiple String Values Separted By A Comma As One Parameter

Oct 16, 2007



Hello,



I have a stored procedure that accepts one parameter called @SemesterParam. I can pass one string value such as €˜Fall2007€™ but what if I have multiple values separated by a comma such as 'Fall2007','Fall2006','Fall2005'. I still would like to include those multiple values in the @SemesterParam parameter. I would be curious to hear from some more experienced developers how to deal with this since I am sure someone had to that before.



Thanks a lot for any feedback!

View 6 Replies View Related

SQL Server 2014 :: Passing Multiple Columns Values From Query To One Variable?

Aug 10, 2014

Is it possible to assign multiple columns from a SQL query to one variable. In the below query I have different variable (email, fname, month_last_taken) from same query being assigned to different columns, can i pass all columns to one variable only and then extract that column out of that variable later? This way I just need to write the query once in the complete block.

DECLARE @email varchar(500)
,@intFlag INT
,@INTFLAGMAX int
,@TABLE_NAME VARCHAR(100)

[code].....

View 1 Replies View Related

Passing Null Values To Stored Procedures

Aug 29, 2006

Version: ASP.Net 2.0Language: C#Hello Everyone!I'm saving data from a function to an SQL Server 2000 db via a stored procedure. There are a few fields which are conditional and thus need not be passed to the procedure everytime. Under those circumstances I need to store null values in the fields.The problem is if I the value of the parameter as null, VS throws the error that a required parameter is not passed. I tried setting 'IsNullable' property of the parameter to true but it works for only those fields who are declared NULL in the stored procedure (For e.g. @DOB DATETIME = NULL).Strangely, this works well in classic ASP.Does anyone know how to accomplish this? Note, altering stored procedures is not an option for me.Thanks a lot!

View 4 Replies View Related

Listbox Selected Values

Dec 1, 2005

Hi. With VWD i've produced the following code.<asp:SqlDataSource ID="SqlDataSource2" runat="server" ConnectionString="<%$ ConnectionStrings:50469ConnectionString %>"SelectCommand="SELECT * FROM [ibs] WHERE ([liedID] = @liedID)"><SelectParameters><asp:ControlParameter ControlID="ListBox1" Name="liedID" PropertyName="SelectedValue" Type="Int16" />But the query is only returning one row of the table. Even when multiple values were selected in the ListBox1. Could someone tell me how to do?Thanks, Kin Wei.

View 2 Replies View Related

Use Listbox Values To Do Select Statement

May 20, 2007

Hi,
I have SQL database 2000 which has one table Sheet1, I retrieved the columns in the ListBox, then chosed some of them and moved it to ListBox2.
The past scenario worked great, and I checked the moved values, it was succesfully moved, but when I tried to copy the values in ArrayList to do a select statement it didn't worked at all.
public string str;protected void Button3_Click(object sender, EventArgs e)
{ArrayList itemsSelected = new ArrayList(); string sep = ",";
//string str;for (int i = 0; i < ListBox2.Items.Count; i++)
{if (ListBox2.Items[i].Selected)
{
itemsSelected.Add(ListBox2.Items[i].Value);
}
int itemsSelCount = itemsSelected.Count; // integer variable which holds the count of the selected items.
str = ListBox2.Items[i].Value + sep;
Response.Write(str);
}
 SqlConnection SqlCon = new SqlConnection("Data Source=AJ-166DCCD87;Initial Catalog=stat_rpt;Integrated Security=True;Pooling=False");
String SQL1 = "SELECT " + str + " from Sheet1"; SqlDataAdapter Adptr = new SqlDataAdapter(SQL1, SqlCon);
SqlCommandBuilder CB = new SqlCommandBuilder(Adptr);DataTable Dt = new DataTable();
Adptr.Fill(Dt);
//return Dt;
GridView1.DataBind();
SqlCon.Close();
}
I did some changes and the new error message is
Incorrect syntax near the keyword 'from'.
Thank you

View 6 Replies View Related

Selected Listbox Values In SQL Statement

Jan 9, 2008

I have seen some information on this but have not understood fully the answers.  I am fairly new to this and have spent the afternoon trying to work on this and although I have leant some really cool things I am no nearer.  How do I get selected values from a LIstbox into a SQL statement using the IN command  
First I loop through a List box control to get the multi selected values: Dim li As ListItemFor Each li In lbPClass.Items  If li.Selected = True Then    strPC += li.Value & ", "  End IfNextLabel5.Text = strPC The result in the lable looks something like: 100, 101, 102 , I get rid of the trailing blank and commar with:Label6.Text = Left(Label5.Text, Trim(Len(Label5.Text) - 2)) I have created a parameter called @PROPCLASS that will use the values from the selected values in the listbox in a SQL query using an IN statement: Cmd.Parameters.Add(New OleDbParameter("@PROPCLASS", Label6.Text)) strSQL = "SELECT * FROM web_transfer WHERE ( PROP_CLASS IN (@PROPCLASS)) AND (ACRES >= @lowSize)AND (ACRES < @HighSize)  AND (YEAR = @lowYear)AND (SALE_PRICE > @lowPrice) AND (SALE_PRICE < @highPrice) " 
It is really the first bit of the WHERE clause I am interested in.  The sql statement works if I either type in the actual values IN( 100, 101,102) or if I select only one value in the list box.  If I select two then the sql statement does not work. 
My question is: How do I get the values selected in the List box into my SQL statement   Thank you in advance, s

View 1 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved