Passing Multiple Values From A Listbox Into A Stored Procedure
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 Complete Forum Thread with Replies
Sponsored Links:
Related Messages:
How To Save Listbox Multiple Select Values
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 Replies !
View Related
Sending Multiple Values From A ListBox To ControlParameter
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 Replies !
View Related
Passing Values To A Stored Procedure
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 Replies !
View Related
Why Wont This Multiple Selection Listbox Insert Values Into Db
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 <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" /> </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 218 </HeaderTemplate> 219 </asp:TemplateField> 220 <asp:TemplateField ShowHeader="False"> 221 <EditItemTemplate> 222 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 232 </ItemTemplate> 233 </asp:TemplateField> 234 </Fields> 235 </asp:DetailsView> 236 <br /> 237 <asp:Label ID="ErrorMessage" runat="server" CssClass="errortext"></asp:Label> <br /> 238 239 </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 Replies !
View Related
Passing Values From A Gridview To A Stored Procedure
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 Replies !
View Related
Trouble With Passing Values To Stored Procedure
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 Replies !
View Related
Passing Parms With No Values To A Stored Procedure
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 Replies !
View Related
Passing Compound Primary Key Values To A Stored Procedure Using ADO.NET
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 Replies !
View Related
Passing Parameter Values To Full Text Search Stored Procedure
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 Replies !
View Related
Passing Multiple Parameters To Stored Procedure Using SqlDataSource
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 Replies !
View Related
Handling Multiple Values From A Stored Procedure In ASP.NET.
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 Replies !
View Related
ListBox Web Server Control And Stored Procedure
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 Replies !
View Related
Listbox Contents To Sql Stored Procedure Command Parameter?
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 Replies !
View Related
Passing Multiple Values To A Paramter
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 Replies !
View Related
Passing Multiple Values Into A Subreport
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 Replies !
View Related
SSRS 2005: Passing Multiple Values Of A Parameter
Hello all, I have a SSRS report file for which I would like generate the report almost a thousand times based on different values of a parameter (@P). The values of this @P are stored in a sql server table. Do you know of an automated way I can generate the reports and save them on a folder? Thanks in advance, Saurav
View Replies !
View Related
Passing Multiple Values From Parent To Child Package
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 Replies !
View Related
Passing Multiple String Values Separted By A Comma As One Parameter
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 Replies !
View Related
Passing Multiple Values To Single Report Parameter Using Cube Surce
Hello Freinds, I'm facing a small problem while passing Multiple Values to a Single Report Parameter, this report is using a Cube as its source. I'm able to assign and send a single value to the report parameter but i'm not finding a way to send more than one value.. The following is the code which i am using to populate my Parameter Dim Param(0) As Microsoft.Reporting.WebForms.ReportParameter Param(0) = New Microsoft.Reporting.WebForms.ReportParameter("BUDASADepartment", "[BUDASA].[Department].&[Accommodation]") In the same 'BUDASADepartment' i want to add one more value as "[BUDASA].[Department].&[Others]" but i'm not able to figure out a way of do'ing it. Any Suggestions , Thanz !
View Replies !
View Related
How To Pass Multiple Values To An &"IN&" Clause Through Stored Procedure
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 Replies !
View Related
Passing Null Values To Stored Procedures
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 Replies !
View Related
Listbox Selected Values
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 Replies !
View Related
Selected Listbox Values In SQL Statement
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 Replies !
View Related
Use Listbox Values To Do Select Statement
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 Replies !
View Related
How To Insert All The Values From A Listbox Into The Database?
I want that the user can chose several options from one ‘listbox’, and to do this, I have created two ‘listbox’, in the first one there are the options to select, and the second one is empty. Then, in order to select the options I want the user have select one or more options from the first ‘listbox’ and then click in a link to pass the options selected to the second ‘listbox’. Thus, the valid options selected will be the text and values in the second ‘listbox’. I have seen this system in some websites, and I think it is very clear. Well, my question is, Is it possible to insert into the database all the values from a ‘listbox’ control? In my case from the second ‘listbox’ with all the values passed from the first one? If yes, in my database table I have to create one column (field) for every possible selected option? Thank you, Cesar
View Replies !
View Related
Passing Multiple Selections To A Stored Proc Parameter
Hi, I am currently in the process of building a stored procedure that needs the ability to be passed one, multiple or all fields selected from a list box to each of the parameters of the stored procedure. I am currently using code similar to this below to accomplish this for each parameter: CREATE FUNCTION dbo.SplitOrderIDs ( @OrderList varchar(500) ) RETURNS @ParsedList table ( OrderID int ) AS BEGIN DECLARE @OrderID varchar(10), @Pos int SET @OrderList = LTRIM(RTRIM(@OrderList))+ ',' SET @Pos = CHARINDEX(',', @OrderList, 1) IF REPLACE(@OrderList, ',', '') <> '' BEGIN WHILE @Pos > 0 BEGIN SET @OrderID = LTRIM(RTRIM(LEFT(@OrderList, @Pos - 1))) IF @OrderID <> '' BEGIN INSERT INTO @ParsedList (OrderID) VALUES (CAST(@OrderID AS int)) --Use Appropriate conversion END SET @OrderList = RIGHT(@OrderList, LEN(@OrderList) - @Pos) SET @Pos = CHARINDEX(',', @OrderList, 1) END END RETURN END GO I have it working fine for the single or multiple selection, the trouble is that an 'All' selection needs to be in the list box as well, but I can't seem to get it working for this. Any suggestions? Thanks My plan is to have the same ability as under the 'Optional' section of this page: http://search1.workopolis.com/jobshome/db/work.search_cri
View Replies !
View Related
How Do I Store Multiple Selected Listbox Items
Hi There This is probably realy simple but since im still a newbie some help would be appreciated. I have a listbox bound to an sqldatasource which has names of people from my sql database employeeDetails table. I simply want to allow someone to select to select multiple names and insert them into another field in my database. I have this bit workng, ie they can select multiple people on the list, it does insert, however only the first selected name, not the others.Can you please help me out Kind Regads Dan
View Replies !
View Related
Multiple Selection Listbox As Data Control?
Hi, i have a listbox with multiple selection enabled, the end user uses this listbox to select what data they want to view eg. they select "green" to view all the green cars, "red" to select all the red cars etc. i have the listbox as the control that is connected to the datasource (the sql used for it is select * from cars_table where color =@colorthis works fine when one item in the listbox is selected, but when multiples are selected it does not work what format does the =@color have to be when multiples are selected? i've tried "green, red" "green + red" etc. but cannot seem to get it workingdoes anybody have any working examples that i can take a look at? it seems to be a common action, yet i cannot seem to find any documentation on how to get it to workthanks in advance!
View Replies !
View Related
Using Asp.net Listbox Control (multiple Selection) And Sproc
I have a form where a user can select multiple items from a listbox control.How can I pass each item selected to a sproc? Do i need to created a paramter for each item in my listbox in my sproc?Has anyone done this, I dont want to create dynamic sql to handle this and i dont really want to create 100 parameters to handle my listbox items.thanks
View Replies !
View Related
Passing Radiobuttonlist Value To Stored Procedure
Hi i have some radio buttons, what i want to achieve is that if one of the radio buttons is clicked then the value is sent to the stored procedure, as as now the radio buttonlist is like this; <asp:RadioButtonList ID="SortButtons" runat="Server" RepeatDirection="Horizontal" RepeatLayout="Flow" Width="69px" OnSelectedIndexChanged="SortButtons_SelectedIndexChanged"><asp:ListItem Text="Price" Value="productPrice"></asp:ListItem><asp:ListItem Text="Quantity " Value="Qty"></asp:ListItem></asp:RadioButtonList> My question is how will my stored procedure take in the radio button that has been clicked and order it by that value, e.g if price is clicked how will the stored procedure take taht in, this is how my normal unordered stored procedure currently looks; CREATE PROCEDURE stream_CategoryResults@subCategoryID INT ASSELECT DISTINCT Companies.companyName, Companies.companyLogo,SubCategories.subCategoryName, Products.productPrice, Products.productInfoURL FROM Companies INNER JOIN Products ON Companies.companyID = Products.companyID INNER JOIN SubCategories ON Products.subcategoryID = SubCategories.subCategoryID WHERE SubCategories.subCategoryID=@subCategoryID
View Replies !
View Related
Passing Column Name To Stored Procedure
HII m trying to use the following Store Procedure to search Suppliers... user provides a Column Name (Search By) and an Expression... but i m not getting it work properly... plz review it and tell me wots wrong with this stored procedure is... CREATE PROCEDURE dbo.SearchSupplier ( @Column nvarchar(50), @Expression nvarchar(50) ) AS SELECT SupplierID,Name,Address,Country,City,Phone,Fax FROM Supplier WHERE @Column LIKE '%'+@Expression +'%' OR @Column = @Expression RETURN Thanks
View Replies !
View Related
Passing A Value To Another Stored Procedure...need Urgent Help
Hi i have been working on these stored procedures for what seems like ages now and i just cant understand why they dont work. I have followed many tutorials and think i have the correct synat but i jus keep getting errors. Basically, i have SPOne and SPTwo - SPOne is writing to a table called Publication that has PublicationID as its PK (auto generated). SPtwo needs to get this PublicationID from SPOne and use it to insert rows into a second table PublicationAuthors - the PublicationID is hence a FK in the table PublicationAuthors. The error i get is: Procedure or function 'SPOne' expects parameter '@publicationID', which was not supplied. Cannot insert the value NULL into column 'publicationID', table .dbo.PublicationAuthors'; column does not allow nulls. INSERT fails. SPOne is as follows: ALTER PROCEDURE dbo.StoredProcedureOne @typeID smallint=null, @title nvarchar(MAX)=null, @publicationID smallint OUTPUTASBEGIN SET NOCOUNT ON INSERT INTO Publication (typeID, title) VALUES (@typeID, @title) SELECT @publicationID = scope_identity() END and SPtwo is as follows: ASDECLARE @NewpublicationID IntEXEC StoredProcedureOne @NewpublicationID = OUTPUTSET @publicationID = @NewpublicationIDINSERT INTO PublicationAuthors (publicationID, authorID) VALUES (@publicationID, @authorID) SELECT @NewpublicationID Thanks Gemma
View Replies !
View Related
Passing Parameters Into Stored Procedure
I have two variables in my code behind .cs, string Zip;int MenuCode; I am trying to pass them to the stored procedure below and am getting compile errors. How do I properly construct the parameter object? error: Cannot create an object of type 'System.TypeCode' from its string representation '"String";' for the 'Type' property. (points to Parameter Name ="Zip" Type="string" <asp:SqlDataSource ID="LocalPremiumLinks" runat="server" ConnectionString="<%$ ConnectionStrings:ConnectionString2 %>" SelectCommand="CPLink" SelectCommandType="StoredProcedure"> <SelectParameters> <asp:Parameter Name="Zip" Type="string" /> <asp:Parameter Name="MenuCode" Type="double" /> </SelectParameters>
View Replies !
View Related
Passing @UserName Into Stored Procedure
I have successfully Create a Site, Inserting Updating and Deleting information in my DB all with Stored Procedures, But I need the ability to pass their username into my Stored Procedures. How and where do I code this in my ASPX file? My SP would be something like this Create procedure test @UserName varchar(50) As Select * From table Where username = @username All of the data is tied to the user in one way or another but I do not know what code to put in my page?
View Replies !
View Related
Passing A Result Set To A Stored Procedure
Hi All,I have sometimes used the following sort of query to pull data from one table to another:INSERT INTO Table1 SELECT fname, lname FROM Table2Now, let's suppose that I had created a stored procedure to do the insert (and any other logic i was concerned about) and I did something like this:EXECUTE Table1 _Insert SELECT fname, lname FROM Table2It won't work, giving an error that looks something like this:Server: Msg 201, Level 16, State 3, Procedure Table1_Insert, Line 0Procedure 'Table1_Insert' expects parameter '@fname', which was not supplied.I assume I'm not doing things right... how would I pass a result set to a stored procedure, with each row corresponding to an input parameter of the stored procedure?
View Replies !
View Related
Passing Parameters To A SQL Stored Procedure
I need help with a really basic task. I have a formview that works OK for selecting/updating records in the table. When the user edits the data and clicks submit, the updates post to the table along with two parameter values I add using this in the code-behind: Protected Sub DS_EditMasterOrg_Updating1(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.SqlDataSourceCommandEventArgs) Handles DS_EditMasterOrg.Updating e.Command.Parameters("@Last_Update_By").Value = User.Identity.Name e.Command.Parameters("@Last_Update_Dt").Value = System.DateTime.Now End Sub The next step, where I am stuck, is if the data is OK and does not need to be updated I have a button the user can click to indicate that fact. I am trying to use the button's onclick event handler to trigger a stored procedure to update those two fields in the record. The stored procedure expects these two parameters and executes correctly when they are supplied. I can't figure out the correct syntax of the statements to plug into the onclick event handler that will send the two parameters to the SP. What I have so far is simply this (which obviously doesn't work): Protected Sub Button1_Click(ByVal sender As Object, ByVal e As System.EventArgs) Dim sqlConnection1 As New Data.SqlClient.SqlConnection("Data Source=GV88Z91;Initial Catalog=contests;Integrated Security=True") Dim cmd As New Data.SqlClient.SqlCommand Dim rowsAffected As Integer Dim MOID As String = Profile.MasterOrgId Dim user As String = Context.User.Identity.Name With cmd .CommandText = "Director_VerifyMasterOrg" .CommandType = System.Data.CommandType.StoredProcedure .Connection = sqlConnection1 .Parameters.Add("MOID") .Parameters.Add("user") .Parameters("MOID").Value = Profile.MasterOrgId .Parameters("User").Value = Context.User.Identity.Name End With sqlConnection1.Open() rowsAffected = cmd.ExecuteNonQuery() sqlConnection1.Close() Thanks in advance for the assistance.
View Replies !
View Related
Passing XML String To Stored Procedure
hi,i have a stored procedure that is used to insert the employee data into a EMPLOYEE table.now i am passing the employee data from sqlCommand.i have the XML string like this'<Employee><Name>Gopal</Name><ID>10157839</ID><sal>12000</sal><Address>Khammam</Address></Employee>' when i pass this string as sql parameter it is giving an error. System.Data.SqlClient.SqlException: XML parsing error: A semi colon character was expectedbut when i execute the stored procedure in query analyzer by passing the same parameter. it is working.please reply me on gk_mpl@yahoo.co.in
View Replies !
View Related
Stored Procedure- Passing Arguments
I have the following stored procedure ALTER PROCEDURE dbo.TextEntered @searchString varchar(30) /* ( @parameter1 datatype = default value, @parameter2 datatype OUTPUT ) */ AS Select GAVPrimaryKey, NAME from towns where NAME like @searchString order by NAME RETURN doesn't work!!!! I would like to produce the following where s is @searchString: Select GAVPrimaryKey, NAME from towns where NAME like 's%' order by NAME Any ideas how I might acomplish this I have tried almost everything !
View Replies !
View Related
Passing Array With Ids To Stored Procedure
I want to pass and array of ids to a procedure for inserting i a relation table. I found some examples in other posts, but had problems getting them to work. I just want to pass a parameter with value like '1,45,89' to the procedure, then loop through it to insert the relations. (I´m using sql server 2000), had some problem with examples with strpos then. Any hints ? peace.
View Replies !
View Related
Parameter Passing To A Stored Procedure
Hey huys, I got this stored procedure. First of all: could this work? --start :) CREATE PROCEDURE USP_MactchUser @domainUserID NVARCHAR(50) , @EmployeeID NVARCHAR(50) , @loginType bit = '0' AS INSERT INTO T_Login (employeeID, loginType, domainUserID) Values (@EmployeeID, @loginType, @domainUserID) GO --end :) then I got this VB.Net code in my ASP.net page.... ---begin :) Private Sub matchUser() Dim insertMatchedUser As SqlClient.SqlCommand Dim daMatchedUser As SqlClient.SqlDataAdapter SqlConnection1.Open() 'conn openned daMatchedUser = New SqlClient.SqlDataAdapter("USP_MatchUser", SqlConnection1) daMatchedUser.SelectCommand.CommandType = CommandType.StoredProcedure daMatchedUser.SelectCommand.Parameters.Add(New SqlClient.SqlParameter("@EmployeeID", SqlDbType.NVarChar, 50)) daMatchedUser.SelectCommand.Parameters.Add(New SqlClient.SqlParameter("@domainUserID", SqlDbType.NVarChar, 50)) daMatchedUser.SelectCommand.Parameters("@EmployeeID").Value = Trim(lblEmployeeID.Text) daMatchedUser.SelectCommand.Parameters("@domainUserID").Value = Trim(lblDomainuserID.Text) daMatchedUser.SelectCommand.Parameters("@EmployeeID").Direction = ParameterDirection.Output daMatchedUser.SelectCommand.Parameters("@domainUserID").Direction = ParameterDirection.Output SqlConnection1.Close() 'conn closed End Sub --- If I try this it doesn't work (maybe that's normal :) ) Am I doing it wrong. The thing is, in both label.text properties a values is stored that i want to as a parameter to my stored procedure. What am I doing wrong?
View Replies !
View Related
.Net Passing Bad Formatted Value To Stored Procedure
Hi, that's the problem: I have a GridView, bound to a SQLDataSource, with an stored procedure as a Select query. The Select Parameters are bound to controls in the web form, acting like some filter fields. When I submit the page, everythings works fine, except when I try to set some value in the DateTime fields. .Net is enclosing the date with extra single quotes, as I could see in the Profiler: exec sel_despesa_procura @codigo=NULL,@fornecedor=NULL,@descricao=NULL,@vencto_ini=''2005-10-10 00:00:00:000'',@vencto_fim=''2005-10-20 00:00:00:000'',@pagto_ini=NULL,@pagto_fim=NULL,@valor=NULL,@valor_pago=NULL,@centro_custo=NULL,@pago=N'0,1' The fields are defined as follows: <SelectParameters>...<asp:ControlParameter ControlID="txtFiltroVencIni" Name="vencto_ini" PropertyName="Text" Type="DateTime" /><asp:ControlParameter ControlID="txtFiltroVencFim" Name="vencto_fim" PropertyName="Text" Type="DateTime" />...</SelectParameters> The stored procedure doesn't even execute, due to the bad formatted arguments. It returns the error: Msg 102, Level 15, State 1, Line 1Incorrect syntax near '2005'. I'm going to change the parameter type to varchar, as a workaround, but I'd like to solve this problem. Thanks in advance, Anderson
View Replies !
View Related
Passing Parameters To A Stored Procedure
Hi, I'm fairly new to TSQL so I'll try to explain as best I can. I'm using a stored procedure to return a recordset and I'm passing a variable/parameter into it from MS Access XP, the syntax for the SQL statement is something like the following (Extremely trimmed down) : CREATE PROCEDURE ExplodeDilutions @MyLSN AS VarChar(8) AS declare tcrl cursor FAST_FORWARD for SELECT TSAMPLE.* FROM TSample WHERE (TSample.ISmpShortCode = @MyLsn) The value I'm passing (@MyLSN) is 24/12359 (one LS Number) The above statement works fine (ie returns a recordset). But what I want to be able to do is pass a series of LS numbers into the Stored Procedure. So I've opted for using the SQL 'IN' function. So the SQL statement now looks like: CREATE PROCEDURE ExplodeDilutions @MyLSN AS VarChar(50) AS declare tcrl cursor FAST_FORWARD for SELECT TSAMPLE.* FROM TSample WHERE (TSample.ISmpShortCode IN (@MyLsn)) The value I'm passing (@MyLSN) is '24/12359','24/12360' This is where the problem begins because the stored procedure now doesn't return a recordset. If I run this SQL statement thru a VIEW (manually entering the LS numbers) then the correct recordset is returned. Also if I run the Stored Procedure using the Query Analyzer (where it will prompt me for the value of @MyLsn) and enter just one LS Number then it works fine but if I try to supply 2 LS Numbers then nothing is returned. I've tried all manner of syntax when supplying the LS numbers but alas to no avail. So if someone could point me in the right direction then that would be much appreiciated. Thanks in advance.
View Replies !
View Related
Passing A Boolean To A Stored Procedure
I am using VB6 and SQL Server 6.5. I want to pass a boolean to a SQL Server stored procedure. The code below is the main part that tries to pass 'True' to the stored procedure, but an error occurs when the line rst.Open.... is reached ? The error is as follows: The application is using arguments that are of the wrong type, are out of acceptable range, or are in conflict with one another. I have tried putting 'True' directly in the rst.Open... line instead of assigning it to a boolean variable but the same error occurs. The stored procedure works fine when run in SQL Server. Can anyone help ? Please. complete = True Set rst = New ADODB.Recordset rst.CursorType = adOpenForwardOnly rst.LockType = adLockReadOnly rst.Open "usp_CompleteUID_sel (" & complete & ")", UIDCon, , , adCmdStoredProc Do While Not rst.EOF lst_pastUIDs.AddItem (rst.Fields(1)) rst.MoveNext Loop rst.Close
View Replies !
View Related
Passing Parameters To Stored Procedure Using ADO
I have created a form that contains a textbox (txtState), button (cmdRun), and listbox (lstResults). I want to use the txtState textbox to supply a parameter to a stored procedure named (test), and then execute the stored procedure by clicking the (cmdRun) button which will display the results in the lstResults listbox. I have looked around everywhere for a nice simple answer, but only seem to find complex answers. Could someone please provide a nice simple solution. Here is the code for my stored procedure. It's the ADO code that I am having a great deal of trouble with. create procedure "test" @s nvarchar(2) as select states from customers where states = @s
View Replies !
View Related
|
TRACKER
