Error While Using Database Procedure In A Gridview Control
Dec 19, 2006
Dear Forum,
I have a gridview control which is associated to a storedprocedure with a parameter(Customer Number) to be supplied. In the Define Custom Statement or stored procedure section I selected stored procedure and selected the stored procedure. The Define Parameter window I defaulted the Parameter Source as 'none' and default value as '%'. In the next screen, I do a test query which retuns the following error
There was an error executing the query. Please check the syntax of the command and if present, the type and values of the parameters and ensure that they are correct.
[Error 42000] [Microsoft][ODBC SQL Server Drive][SQL Server] Procedure 'SP_TransactionDetails' expects parameter '@cnum' which was not supplied.
I am using SQL server studio 2005 version2.0.
But the same procedure, if I use as SQL Statement, it works.
Hi, I am seeking a hopefully easy solution to spit back an error message when a user receives no results from a SQL server db with no results. My code looks like this What is in bold is the relevant subroutine for this problem I'm having. Partial Class collegedb_Default Inherits System.Web.UI.Page Protected Sub submit_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles submit.Click SqlDataSource1.SelectCommand = "SELECT * FROM [college_db] WHERE [name] like '%" & textbox1.Text & "%'" SqlDataSource1.DataBind() If (SqlDataSource1 = System.DBNull) Then no_match.Text = "Your search returned no results, try looking manually." End If End Sub Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load SqlDataSource1.SelectCommand = "SELECT * FROM [college_db] ORDER BY [name]" SqlDataSource1.DataBind() End Sub Protected Sub reset_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles reset.Click SqlDataSource1.SelectCommand = "SELECT * FROM [college_db] ORDER BY [name]" SqlDataSource1.DataBind() End SubEnd Class I'm probably doing this completely wrong, I'm a .net newb. Any help would be appreciated. Basically I have GridView spitting out info from a db upon Page Load, but i also have a search bar above that. The search function works, but when it returns nothing, I want an error message to be displayed. I have a label setup called "no_match" but I'm getting compiler errors. Also, next to the submit button, I also have another button (Protected sub reset) that I was hoping to be able to return all results back on the page, similar to if a user is just loading the page fresh. I'd think that my logic would be OK, by just repeating the source code from page_load, but that doens't work.The button just does nothing. One final question, unrelated. After I set this default.aspx page up, sorting by number on the bottom of gridview, ie. 1,2,3,4, etc, worked fine. But now that paging feature, as long with the sorting headers, don't work! I do notice on the status bar in the browser, that I receive an error that says, "error on page...and it referers to javascript:_doPostBack('GridView1, etc etc)...I have no clue why this happened. Any help would be appreciated, thanks!
Can someone help me with this issue? I am trying to update a record using a sp. The db table has an identity column. I seem to have set up everything correctly for Gridview and SqlDataSource but have no clue where my additional, phanton arguments are being generated. If I specify a custom statement rather than the stored procedure in the Data Source configuration wizard I have no problem. But if I use a stored procedure I keep getting the error "Procedure or function <sp name> has too many arguments specified." But thing is, I didn't specify too many parameters, I specified exactly the number of parameters there are. I read through some posts and saw that the gridview datakey fields are automatically passed as parameters, but when I eliminate the ID parameter from the sp, from the SqlDataSource parameters list, or from both (ID is the datakey field for the gridview) and pray that .net somehow knows which record to update -- I still get the error. I'd like a simple solution, please, as I'm really new to this. What is wrong with this picture? Thank you very much for any light you can shed on this.
Here is the Stored procedure ALTER procedure [dbo].[ActAuditInfo](@IndustryName nvarchar(50) output,@CompanyName nvarchar(50) output,@PlantName nvarchar(50) output,@GroupName nvarchar(50) output,@UserName nvarchar(50) output )asbegindeclare @AuidtID as varchar(30)Select @IndustryName=Industry_Name from Industry whereIndustry.Ind_Id_PK =(Select Audit_Industry from Audits whereAd_ID_PK=@AuidtID)Select @CompanyName=Company_Name from Company whereCompany.Cmp_ID_PK =(Select Audit_Company from Audits whereAd_ID_PK=@AuidtID)Select @PlantName=Plant_Name from Plant where Plant.Pl_ID_PK=(Select Audit_Plant from Audits where Ad_ID_PK=@AuidtID)Select @GroupName=Groups_Name from Groups whereGroups.G_ID_PK =(Select Audit_Group from Audits whereAd_ID_PK=@AuidtID)Select @UserName=Login_Uname from RegistrationDetails whereRegistrationDetails.UID_PK =(Select Audit_Created_By fromAudits where Ad_ID_PK=@AuidtID)SELECT Ad_ID_PK, Audit_Name, @IndustryName, @CompanyName, @PlantName,@GroupName, Audit_Started_On, Audit_Scheduledto, @UserName FROMAudits where Audit_Status='Active'end U can see here different parameters,my requirement is that iam havingID's of Industry,company,plant,group,username stored in a table calledPcra_Audits and i must display their related names in the front end.so this is the query iam using for that. Data in the database:Commercial83312 2 2 2 1 1 InactiveHere u can see 2,2,2,1,1 these are the IDs ofindustry,company,plant,group and username and Commercial83312 is tehaudit ID.now i want to display this data in teh front end as i cannot displaythe IDs i am retrieving the names of the particular IDs from therelated tables.Like iam getting name of the IndustryID from Industry Table,in thesame way others too.when iam running this procedure iam getting the gridview blank.iam passing the output parameters:@IndustryName nvarchar(50) output,@CompanyName nvarchar(50) output,@PlantName nvarchar(50) output,@GroupName nvarchar(50) output,@UserName nvarchar(50) outputinto the function in the frontend and iam calling that into the pageload method.please help me with this.
I have a simple gridview control set up that contains a single ControlParameter (a DropDownList) whose value is used in my SqlDataSource's SelectCommand: SelectCommand="SELECT * FROM [Wood_table] WHERE [wood_type] = ISNULL(@wood_type, [wood_type])" The ISNULL check is so that I can select "ALL" from my dropdown and get all the rows with null values. Everything works fine. Now what I'd like to do is exclude a specific wood_type value from the query if a checkbox control is selected. So what I'd like my select query to be when it's checked is something like SelectCommand="SELECT * FROM [Wood_table] WHERE [wood_type] = ISNULL(@wood_type, [wood_type]) AND @wood_type <> 'pine' " So I'd like the option of excluding that certain type of wood from the default query (all rows). I thought it might be better to just have a value in the dropdown list that was "ALL except pine" but that doesn't seem like it would work, what would I bind that to? I fooled around with having the CheckBox oncheckchanged event set a global based on the checked status, easy enough, but then how can I modify my SelectCommand? Should I just access the SqlDataSource object programmatically in my CheckBox handler and fiddle with the SelectCommand property? I tried this and it works, but this seems messy, now if I modify my GridView in Design mode I need to remember to update my SelectCommand strings in the CheckBox handler too. Is this the best way to do this?
Hi, My formview or gridview control stops updating or deleting a record once the record has a null value. I have table tblTest with the following pkID int NOT NULL **IDENTITY COLUMN** string1 varchar(30) string2 varchar(30) I then create a SqlDataSource with the statement: Select * From [tblTest] I have the insert, update and delete statements generated, and choose optimistic concurrency. I add a couple records of dummy data. I then drag a Formview control onto the page, and bind it to the SqlDataSource I just created. I then fire it up in my browser, and I can then update, insert and delete records. However, as soon as I update a record with a null value, I can no longer update or delete that record. So, if I had a record in my FormView like: string1: foo string2: bar I can update and delete normally. And when I update to: string1: foo string2: the database correctly inserts a null value into string2. However, once that null is in the record, I can't change anything about the record. If I try to delete the record, the FormView will then display the previous record, but I can still page to the record that should have been deleted, and it still exists in the db. If I try to update the record, the edits I make will not keep and the process will fail silently. What am I doing wrong? Should i be binding to a different object? Regards, Chris
Hi, I really need some help trying to figure out why my gridview is not working when I create a custom sql statement. It "executes" the query, but gives me an error message when I "test the query". Here is the error message: "There was an error executing the query. Please check the syntax of the command and if present, the types and values of the parameters and ensure they are correct. Syntax error: Expecting '.', identifier or quoted identifier." Here is my sql statement: SELECT TBLPROJECTS.NAME, TBLPROJECTTYPES.NAME AS PROJECTTYPE, TBLPROJECTS.DESCRIPTION, TBLUSERS_1.LOGIN AS OWNERNAME, TBLUSERS.LOGIN AS MANAGERNAME, TBLPROJECTS.START_DATE, TBLPROJECTS.END_DATE, TBLAOI.NAME AS AREAOFINTEREST, TBLPROJECTS.MANPOWER, TBLUNITS.NAME AS MANPOWERUNITFROM TBLPROJECTS INNER JOIN TBLAOI ON TBLPROJECTS.AOI_ID = TBLAOI.ID INNER JOIN TBLPROJECTTYPES ON TBLPROJECTS.PROJECTTYPE_ID = TBLPROJECTTYPES.ID INNER JOIN TBLUNITS ON TBLPROJECTS.MANPOWERUNITS_ID = TBLUNITS.ID INNER JOIN TBLUSERS ON TBLPROJECTS.MANAGER_ID = TBLUSERS.ID INNER JOIN TBLUSERS TBLUSERS_1 ON TBLPROJECTS.OWNER_ID = TBLUSERS_1.ID I have tested it on a new project and still it does not work, I cannot find any problem, please help!!!!!!!!!!!!!!!!!!!!!!!!
Hi all, I have a gridview bound with a SQLDataSource. I am using the Update feature of the SQLDataSource to update a SQL Server database with values entered into the gridview. However I am not getting it to work. I believe this is due to the controls that contain the user entries are not the gridview itself, but rather child controls within the gridview. I have been using the names of the actual controls but nothing happens. Upon submit, the screen returns blank, and the database is not updated. Here is some code:
As seen above, controls such as txtPrimarySettlement are referenced but the update is not successful. The text boxes are within the GridEditSettlement gridview. In the .aspx code I cannot use FindControl (at least I don't think it will work). So the questions are: Is it possible to reference the child controls, if so - how? Is there another way to do this, such as in the vb code behind - in the either the gridview's RowUpdating event or the SQLDataSource's Updating event. What is the best approach? Anyone come up against this issue before? Thanks, KB
Info: (vs 2005, sql server 2005, asp 2.0, C# project)I need to pass a variable from my web form to a stored procedure which should return a dataset to a gridview.My stored proc works fine but it’s not returning properly (or at all). Here is my stored proc:set ANSI_NULLS ON set QUOTED_IDENTIFIER ON go
ALTER PROCEDURE [dbo].[HrsUsed] -- Add the parameters for the stored procedure here @idUser INT AS BEGIN -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON;
-- Insert statements for procedure here
SELECT DATENAME(mm, leaveDate) + ' ' + DATENAME(yyyy, leaveDate) AS 'Leave Date', totalV, totalS FROM tblLeave WHERE idUser = @idUser ORDER BY leaveDate DESC
RETURN
END Here is my code: try { SqlConnection cxnHrsUsed = new SqlConnection(ConfigurationManager.ConnectionStrings["cxnLeaveRecords"].ConnectionString);
SqlCommand cmdHrsUsed = new SqlCommand("HrsUsed", cxnHrsUsed); cmdHrsUsed.CommandType = CommandType.StoredProcedure;
I am trying to do something that I would think is simple. I have a stored procedure used for deleting a record, and I want to call it from the "Delete" command of a Delete button on a GridView. This incredible simple SP accepts one value, the unique record ID to delete the record like this:CREATE PROCEDURE usp_DeleteBox /* ******************************************* Delete a record using the Passed ID. ********************************************** */ ( @pID as int = Null ) AS
DELETE FROM [Boxes] WHERE ID = @pID When I configured the data source for the GridView, I selected the "Delete" tab and selected my Stored Procedure from the list. As mentioned on another post I saw here, I set the "DataKeyNames" property of the GridView to my id field (called "ID", naturally). When I click the Delete button on a row, I get this error message: "Procedure or function usp_DeleteBox has too many arguments specified." If I leave the "DataKeyNames" property empty, it does nothing when I click delete. Can someone tell me the correct way to configure this? I am sure I am missing something obvious, and I would appreciate any suggestions. Thank you!
Help!I am trying to fill my datagrid using the SQLDataSource, using a stored procedure.The stored procedure expects a parameter which I can collect via the querystring, or a string. How can I pass the parameter through the SQLDatasSource?My SQLDataSource is SQLData1. I already have:SQLData1.SelectCommandType = SqlDataSourceCommandType.StoredProcedureSQLData1.SelectCommand = "dbo.get_players"Thanks in advance,Karls
I have a stored procedure in my SQL 2005 Server database named Foo that accepts two parameters, @paramA and @paramB.In my ASP.NET page, I have these:<asp:GridView id="gv" runat="server" AutoGenerateColumns="true" DataSourceID="DS" AllowSorting="true" DataKeyNames="ID"/><asp:SqlDataSource ID="DS" runat="server" ConnectionString="<%$ ConnectionStrings:CS1 %>" SelectCommand="Foo" SelectCommandType="StoredProcedure" OnSelecting="DS_Selecting"> <asp:Parameter Name="paramA" Type="String" /> <asp:Parameter Name="paramB" Type="String" /></asp:SqlDataSource>In my setup, paramA and paramB are set in DS_Selecting(), where I can access the Command.Parameters[] of DS.Now, here's the problem. As you can see, the GridView allows for sorting. When you click on a header title to sort, however, the GridView becomes empty. My question is, how can I get the GV sorted and in the correct direction (i.e. asc/desc)? My first step in my attempt was to add another parameter to the SqlDataSource and sotred procedure Foo (e.g. @SortByColumn), then changed Foo appropriately: ALTER PROCEDURE Foo @paramA nvarchar(64), @paramB nvarchar(64), @SortColumn nvarchar(16) = 'SearchCount' AS SELECT * FROM Searches ORDER BY CASE WHEN @SortColumn='SearchCount' THEN SearchCount WHEN @SortColumn='PartnerName' THEN PartnerName ELSE ID ENDThat works find and dandy. But wait--I want to get the correct ORDER BY direction too! So I add another parameter to the SqlDataSource and Foo (@SortDirection), then alter Foo: ... SELECT * From Searchces ORDER BY CASE /* Keep in mind that CASE short-circuits */ WHEN @SortColumn='SearchCount' AND @SortDirection='desc' SearchCount DESC WHEN @SortColumn='SearchCount' SearchCount WHEN @SortColumn='PartnerName' AND @SortDirection='desc' PartnerName DESC WHEN @SortColumn='PartnerName' PartnerName WHEN @SortColumn='ID' AND @SortDirection='desc' ID DESC ELSE ID END ...But including DESC or ASC after the column name to sort by causes SQL to error. What the heck can I do, besides convert all my stored procedures into in-line statements inside the ASP page, where I could then dynamically construct the appropriate SQL statement? I'm really at a loss on this one! Any help would be much appreciated!Am I missing a much simpler solution? I am making this too complicated?
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?
I am attempting to setup a Master-Details with GridView/DetailsView but I can't seem to find any information on using a stored procedure that requires parameters with the SqlDataSource control. SelectCommandType specifies that you are using a stored proc. SelectCommand specifies the name of the proc, but I haven't found any information on how to pass a parameter to the stored procedure.Is it even possible or do I have to forget about using the DetailsView control altogether?
i have a search page having four text boxes like name,accountnumber,ssn..etc we have to search the database by these values. but even if we give value in one text box keeping the others null the stored procedure have to serach and get the values. and we display it using gridview control.
here is the stored procedure i wrote.but its not working.its not giving any erros...but its not showing any values.
AS BEGIN if(@name!=null AND @ssn!=null AND @accountnumber!=null) select * from Userinfo where name=@name AND ssn=@ssn AND accountnumber=@accountnumber else if(@name=null AND @ssn!=null AND @accountnumber!=null) select * from Userinfo where ssn=@ssn AND accountnumber=@accountnumber else if(@name=null AND @ssn=null AND @accountnumber!=null) select * from Userinfo where accountnumber=@accountnumber else if(@name!=null AND @ssn=null AND @accountnumber!=null) select * from Userinfo where accountnumber=@accountnumber AND name=@name else if(@name!=null AND @ssn=null AND @accountnumber=null) select * from Userinfo where name=@name else if(@name!=null AND @ssn!=null AND @accountnumber=null) select * from Userinfo where name=@name AND ssn=@ssn else if(@name=null AND @ssn!=null AND @accountnumber=null) select * from Userinfo where ssn=@ssn
end
table name is userinfo
please help me with this. its very urgent. thanx for your help in advance.
Hi All I am trying to bind a gridview to my sql express database through code but my data is not being displayed, i dont get any errors though? If i bind it using an sql datasource it works fine so i know my connection string works and there is data in my database my code is as follows thanks gibbo Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load ReadRecords(GridView1, "Select * from Actions") End Sub
Public Shared Function GetConnString() As String Return System.Configuration.ConfigurationManager.ConnectionStrings("actionsConnectionString2").ConnectionString End Function
Private Sub ReadRecords(ByVal datagrid As GridView, ByVal SQL As String) Dim ConnString As String = GetConnString() Dim SqlString As String = SQL Using conn As New SqlClient.SqlConnection(ConnString) Using cmd As New SqlClient.SqlCommand(SqlString, conn) cmd.CommandType = CommandType.Text conn.Open() Using reader As SqlClient.SqlDataReader = cmd.ExecuteReader() datagrid.DataSource = reader datagrid.DataBind() End Using End Using End Using End Sub
HiI have page where gridview is loaded from the Excel sheet and when the button is clicked it must insert those values in to the table and ........the code for loading the gridview from the excel if working fine but when i click on the Insert button i am getting the errors the code behind for button is as follows: Protected Sub Button1_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button1.Click ' Dim constr As String = "...." 'your connection string ' Dim connection As SqlConnection = New SqlConnection(constr) Dim i As Integer Dim ia As Integer = ds.Tables("table").Rows.Count - 1 For i = 0 To ia Dim one As String = ds.Tables("table").Rows(i)("Column1") Dim two As String = ds.Tables("table").Rows(i)("Column2") Dim three As String = ds.Tables("table").Rows(i)("Column3") Dim four As String = ds.Tables("table").Rows(i)("Column4") Dim five As String = ds.Tables("table").Rows(i)("Column5") Dim six As String = ds.Tables("table").Rows(i)("Column6") Dim seven As String = ds.Tables("table").Rows(i)("Column7") Dim eight As String = ds.Tables("table").Rows(i)("Column8") Dim nine As String = ds.Tables("table").Rows(i)("Column9") Dim ten As String = ds.Tables("table").Rows(i)("Column10")
'get every cell content of this row like the above line ' Dim sqlsel As String = "insert into table('ID') values('" + one + "')" Dim cmdSavegrd As OdbcCommand = New OdbcCommand("INSERT INTO gd_master VALUES( " & one & ",'" & two & "', '" & three & "'," & four & ",'" & five & "'," & six & "," & seven & ",'" & eight & "'," & nine & ",'" & ten & "')", con) 'Dim sdc As SqlCommand = New SqlCommand(sqlsel, con) ' sdc.CommandType = CommandType.Text con.Open() 'sdc.ExecuteNonQuery() cmdSavegrd.ExecuteReader() con.Close()
Next End Sub The Error i am getting is as follows ........."Object reference not set to an instance of an object. " Please can any help me in this?>?
I'm trying to do a search but it seems like it couldnt get the text in my text book and run the command. Can anyone help me here?Function searchBook(ByVal booking_id As Integer) As System.Data.DataSet Dim search As Integer = txtSearch.Text Dim connectionString As String = "server='(local)'; trusted_connection=true; database='Speedo'"Dim dbConnection As System.Data.IDbConnection = New System.Data.SqlClient.SqlConnection(connectionString) Dim queryString As String = "SELECT [booking].* FROM [booking] WHERE [booking].[booking_id] LIKE '%1%'"Dim dbCommand As System.Data.IDbCommand = New System.Data.SqlClient.SqlCommand dbCommand.CommandText = queryString dbCommand.Connection = dbConnectionDim dbParam_booking_id As System.Data.IDataParameter = New System.Data.SqlClient.SqlParameter dbParam_booking_id.ParameterName = "@booking_id" dbParam_booking_id.Value = booking_id dbParam_booking_id.DbType = System.Data.DbType.Int32 dbCommand.Parameters.Add(dbParam_booking_id)Dim dataAdapter As System.Data.IDbDataAdapter = New System.Data.SqlClient.SqlDataAdapter dataAdapter.SelectCommand = dbCommandDim dataSet As System.Data.DataSet = New System.Data.DataSet dataAdapter.Fill(dataSet)Return dataSet End Function Protected Sub btnSearch_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnSearch.Click ' If Not Page.IsPostBack Then 'Dim search As String = txtSearch.Text 'MsgBox(search) If ddSearchCat.SelectedValue = "booking" ThenMsgBox("you have selected booking") GVSearch.DataSource = searchBook(txtSearch.Text) GVSearch.DataBind() ElseIf ddSearchCat.SelectedValue = "Log" ThenMsgBox("you have selected Log") GVSearch.DataSource = searchLog(txtSearch.Text) GVSearch.DataBind() ElseIf ddSearchCat.SelectedValue = "UserName" ThenMsgBox("you have selected UserName") GVSearch.DataSource = searchUserName(txtSearch.Text)MsgBox("HELLO IN GRID") GVSearch.DataBind() GVSearch.Visible = TrueMsgBox("out of grid") End If 'End If End Sub
I have a webform that I am wanting to display data from a database. Right now I am using the gridview but for future purposes when it comes to maintenance what would be the easiest way to do updates? For example, if I add a new record into the database I would like the webform to automatically update itself to show all the records including the new record I added. Do I need to use a "table" and somehow connect it to a database? Do any stored procedures need to be created? Suggestions/ideas/codes help help help would be verrrry much appreciated!!! Also I am using MS Server 2003 and C# as the programming language. Thank you!!!!
Hello, i currently have a gridview that is populated with data from a SQLServer datasource. I have put an output mask in the select statement, so the date and time attributes are displayed in the format i prefer them to be in. SELECT PatientNo, ConsultantName, HospitalName, CONVERT (varchar, Date, 101), CONVERT (varchar, Time, 8) FROM [Appointment]; However when i click the 'edit' link for a record in the gridview, i am unable to edit the date/time attributes and when i click update to confirm any changes to the other attributes, the values in the date/time attributes are emptied. How can i solve this update problem. I'm guessing i need to configure my SQL UPDATE statement, but bit stuck how i do this. Please help! Thanks, James
Dear people, When i test my page for uploading image too my sql database everthing goes ok (i think) en when i look into my database table i see 3 colums filled 1 column with: Image_title text 1 column with:Image_stream <binary data> 1 column with image_type image/pjpeg How can i show this image in a gridview column..... i have search for this problem but non of them i find i can use because its a too heavy script, or something i dont want. Is there a helping hand Below is the script for uploading the image.....and more
1 2 Imports System.Data 3 Imports System.Data.SqlClient 4 Imports System.IO 5 6 Partial Class Images_toevoegen 7 Inherits System.Web.UI.Page 8 9 10 Protected Sub btnUpload_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnUpload.Click 11 12 Dim imageSize As Int64 13 Dim imageType As String 14 Dim imageStream As Stream 15 16 ' kijkt wat de groote van de image is 17 imageSize = fileImgUpload.PostedFile.ContentLength 18 19 ' kijk welke type image het is 20 imageType = fileImgUpload.PostedFile.ContentType 21 22 ' Reads the Image stream 23 imageStream = fileImgUpload.PostedFile.InputStream 24 25 Dim imageContent(imageSize) As Byte 26 Dim intStatus As Integer 27 intStatus = imageStream.Read(imageContent, 0, imageSize) 28 29 ' connectie maken met de database 30 Dim myConnection As New SqlConnection(ConfigurationManager.ConnectionStrings("Personal").ConnectionString) 31 Dim myCommand As New SqlCommand("insert into tblMateriaal(Image_title,Image_stream,Image_type,ArtikelGroep,ArtikelMaat,Aantal,Vestiging,ArtikelNaam,ContactPersoon,DatumOnline) values(@Image_title,@Image_stream,@Image_type,@ArtikelGroep,@ArtikelMaat,@Aantal,@Vestiging,@ArtikelNaam,@ContactPersoon,@DatumOnline)", myConnection) 32 33 ' Mark the Command as a Text 34 myCommand.CommandType = CommandType.Text 35 36 ' geef alle parameters mee aan het command 37 Dim Image_title As New SqlParameter("@Image_title", SqlDbType.VarChar) 38 Image_title.Value = txtImgTitle.Text 39 myCommand.Parameters.Add(Image_title) 40 41 Dim Image_stream As New SqlParameter("@Image_stream", SqlDbType.Image) 42 Image_stream.Value = imageContent 43 myCommand.Parameters.Add(Image_stream) 44 45 Dim Image_type As New SqlParameter("@Image_type", SqlDbType.VarChar) 46 Image_type.Value = imageType 47 myCommand.Parameters.Add(Image_type) 48 49 Dim ArtikelGroep As New SqlParameter("@ArtikelGroep", System.Data.SqlDbType.NVarChar) 50 ArtikelGroep.Value = ddl1.SelectedValue 51 myCommand.Parameters.Add(ArtikelGroep) 52 53 Dim ArtikelMaat As New SqlParameter("@ArtikelMaat", System.Data.SqlDbType.NVarChar) 54 ArtikelMaat.Value = ddl2.SelectedValue 55 myCommand.Parameters.Add(ArtikelMaat) 56 57 58 Dim Aantal As New SqlParameter("@Aantal", System.Data.SqlDbType.NVarChar) 59 Aantal.Value = ddl3.SelectedValue 60 myCommand.Parameters.Add(Aantal) 61 62 Dim Vestiging As New SqlParameter("@Vestiging", System.Data.SqlDbType.NVarChar) 63 Vestiging.Value = ddl4.SelectedValue 64 myCommand.Parameters.Add(Vestiging) 65 66 Dim ArtikelNaam As New SqlParameter("@ArtikelNaam", System.Data.SqlDbType.NVarChar) 67 ArtikelNaam.Value = tb6.Text 68 myCommand.Parameters.Add(ArtikelNaam) 69 70 Dim ContactPersoon As New SqlParameter("@ContactPersoon", System.Data.SqlDbType.NVarChar) 71 ContactPersoon.Value = tb1.Text 72 myCommand.Parameters.Add(ContactPersoon) 73 74 Dim DatumOnline As New SqlParameter("@DatumOnline", System.Data.SqlDbType.NVarChar) 75 DatumOnline.Value = tb2.Text 76 myCommand.Parameters.Add(DatumOnline) 77 78 Try 79 myConnection.Open() 80 myCommand.ExecuteNonQuery() 81 myConnection.Close() 82 83 Response.Redirect("toevoegen.aspx") 84 Catch SQLexc As SqlException 85 Response.Write("Insert Failure. Error Details : " & SQLexc.ToString()) 86 End Try 87 88 89 End Sub 90 End class
Hi,I am trying to put SQL Server Stored Procedures into Sourcesafe as perthe Microsoft Knowledge Base article 818368, but have run into aproblem.The web server is SQL Server 2000 running on Windows 2003 Server. Theclient dev environment is Visual Studio 2003 Enterprise DeveloperEdition.I have carried out the following steps successfully:-1. Installed Sourcesafe client tools on the Server (sourcesafe is onanother server)2. Run the MSSQLServer service under a domain account that has Readand Write access to the Sourcesafe database.3. Added the above user to Sourcesafe using the Administrator tool.4. Installed the VS6 Stored Procedure Version Control components onthe Server5. Enabled Version Control for Stored Procedures on the clientHowever when I right-click on the Stored Proc node in Server explorerI do not get any of the Sourcesafe menu options. They are not greyedout, they are simply not there!Any help would be appreciated.Alternatively if anyone has recommendations for other strategies orother tools to use for this purpose than Sourcesafe then this wouldalso be welcomeKarl
I have a very simple stored procedure that returns a list of employees. The procedure works fine. However, sometimes there is no records returned. In that case, I'd like to like the procedure to return a single record with predetermined values. I've added the YELLOW portion of the code to my existing proc to reflects the logic of what I am trying to accomplish.
CREATE PROC proc_Emp
@Sel_Country AS BEGIN
SELECT DISTINCT employee, hrid FROM emp_table WHERE country = @Sel_Country
@SEND_RESULTS = CASE WHEN row_count = 0 THEN
SELECT 'No Country', 4444444444 ELSE
(send original results) END
END
I need to:
Query for the employee list
If the row count is zero/null then return fixed values
Else return the orginal data from Step 1. I'm guessing I need to use the OUTPUT command and configure some variables. But I'm not having much luck decoding BOL and I've not found any similar submissions to the forum.
Hi all, I have a several stored procedures that they are designed to do Update and Insert tasks and all of them after finishing the task will return the Inserted or Updated row. The problem is sometime I would need to call the stored procedure inside other ones and somehow i need to stop the inner stored procedures from producing resultsets. I don't find any SET options in SQL documentation which could control this behavior and block resultsets from being sent to the client.
Is there any way i could do this?
Exmaple: In following example as a result of dbo.AddressInsert being called within dbo.CustomerInsert, two resultsets are returned to the client!
Procedure dbo.AddressInsert(....) as BEGIN
Insert INTO Adress ....
SELECT * FROM AdressView WHERE AddressID = @@identity END
Procedure dbo.CustomerInsert(...) as begin -- transcation begin...
EXEC dbo.AddressInsert
INSERT INTO Customer....
-- end transcation
SELECT * FROM Customer WHERE CustomerID = @@identity
I create StringBuilder type forconcating string to create a lot of stored procedure at once However When I use this command BEGIN TRANSACTIONBEGIN TRY--////////////////////// SQL COMMAND ///////////////////////// -------------------- This any command --/////////////////////////////////////////////////////////// --COMMIT TRANEND TRYBEGIN CATCH IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION;END CATCHIF @@TRANCOUNT > 0 COMMIT TRANSACTION; on any command If I use Create a lot of Tablessuch as BEGIN TRANSACTION BEGIN TRY --////////////////////// SQL COMMAND /////////////////////////CREATE TABLE [dbo].[Table1]( Column1 Int , Column2 varchar(50) NULL ) ON [PRIMARY]CREATE TABLE [dbo].[Table2]( Column1 Int , Column2 varchar(50) NULL ) ON [PRIMARY] CREATE TABLE [dbo].[Table3](
Column1 Int ,
Column2 varchar(50) NULL
) ON [PRIMARY] --/////////////////////////////////////////////////////////// --COMMIT TRAN END TRY
BEGIN CATCH IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION; END CATCH
IF @@TRANCOUNT > 0 COMMIT TRANSACTION; It correctly works. But if I need create a lot of Stored procedure as the following code : BEGIN TRANSACTION BEGIN TRY --////////////////////// SQL COMMAND ///////////////////////// CREATE PROCEDURE [dbo].[DeleteItem1] @ProcId Int, @RowVersion Int AS BEGIN DELETE FROM [dbo].[ItemProcurement] WHERE [ProcId] = @ProcId AND [RowVersion] = @RowVersion END CREATE PROCEDURE [dbo].[DeleteItem2]
@ProcId Int
AS
BEGIN
DELETE FROM [dbo].[ItemProcurement]
WHERE
[ProcId] = @ProcId
END CREATE PROCEDURE [dbo].[DeleteItem3]
@ProcId Int
AS
BEGIN
DELETE FROM [dbo].[ItemProcurement]
WHERE
[ProcId] = @ProcId
END
--/////////////////////////////////////////////////////////// --COMMIT TRAN END TRY
BEGIN CATCH IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION; END CATCH
IF @@TRANCOUNT > 0 COMMIT TRANSACTION; It occurs Error ????? Please help me How should I solve them ?
Hi, I have an app where I am calling a stored procedure that runs and returns an integer. I would like to assign this integer to a control on the page. Here is a sample of code and below I will give the errors: Protected Sub NewButton_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles NewButton.Click Dim tempconnection As New Data.SqlClient.SqlConnection(ConfigurationManager.ConnectionStrings("TestConnectionString").ConnectionString) Dim tempproc As New Data.SqlClient.SqlCommand("SP_FetchProviders", tempconnection) Dim tempparam As New Data.SqlClient.SqlParameter tempproc.CommandType = Data.CommandType.StoredProcedure tempproc.Parameters.Add(New SqlParameter("@Agency_ID", Data.SqlDbType.Int)) tempproc.Parameters("@Agency_ID").Value = "8" 'Using a number just to test, will be populated by a session variable later tempparam = tempproc.CreateParameter() tempparam.ParameterName = "@OutValue" tempparam.Direction = Data.ParameterDirection.ReturnValue tempparam.SqlDbType = Data.SqlDbType.Int tempconnection.Open() tempproc.ExecuteNonQuery() Dim labelfill As String labelfill = tempproc.Parameters("@OutValue").Value tempconnection.Close() CType(Me.DetailsView1.FindControl("OmbudINSDDL"), DropDownList).Items.Add(labelfill) 'This is inside of a detailsview templatefield End Sub Here is the stored procedure set ANSI_NULLS ONset QUOTED_IDENTIFIER ONgoALTER procedure [dbo].[SP_FetchProviders] @Agency_ID intas Select Name, ProviderSubUnit_ID From ProviderSubunit a join Ombudsman b on a.contractproviders_id=b.contractproviders_id Where b.agency_id = @Agency_ID Order by Name This takes the @Agency_ID as input variable, and has returns an integer (according to SQL management studio). When I try to run my code I get the error that an sqlparameter with parametername @OutValue is not contained by this sqlparametercollection. This may be a simple question, but does the outvalue have to be declared in the stored procedure? If so, can you provide the right syntax? If not, can you offer a suggestion to populate the dropdownlist with the returned value? Thanks in advance.
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!!
Wanted to know which among these options is better and why? Or if theircould be scenarios where we could opt for one of these.a) flags passed from code to control the execution of queries within astored procedure i.e. - where queries within a single stored procedureare controlled by flags passed to them.ORb) Break individual queries into separate stored procedure
I have defined a stored procedure with one parameter. With this parameter I'm able to controll which year of the sales amount data should be selected. This works fine.
Now I want to implement this stored procedure as the source of the partitions. But if I do this I get an error. The syntax-check says, that everything is fine. But if I want to calculate the partition with this command: "exec dst.fact_umsatz_year 0" get the following error (in German):
OLE DB-Fehler: OLE DB- oder ODBC-Fehler : Falsche Syntax in der Nähe von ')'.; 42000; Falsche Syntax in der Nähe des exec-Schlüsselworts.; 42000. Fehler im OLAP-Speichermodul: Fehler beim Verarbeiten der FACT Umsatz Pivot View-Partition der Anzahl Kunden-Measuregruppe für den Vertrieb-Cube aus der OLAP AS-Datenbank.
hi.. im having a hard time restoring my .bak file.... i have a stored procedure like this.. and every time i call this in my program an error displays... (database in use. the system administrator must have exclusive use ot the database to run the restore procedure)
CREATE PROCEDURE sp_db_restore AS RESTORE DATABASE Inventory FROM DISK = 'C:MSSQL7BACKUPInventory.bak' WITH MOVE 'Inventory_data' TO 'C:Inventory_data.mdf', MOVE 'Inventory_log' TO 'C:Inventory_log.ldf', REPLACE
