If I run a SQL statement or execute a stored procedure from SQL Mgmt Studio, I will get two tabs: Results and Messages.I am lookig to return EXACTLY whats in the messages (the tab I'd see in SQL Mgmt Studio)window to a string variable. Is there a property in ADO.NET (maybe in the command object?)that contains the messages returned from a stored procedure? I am using VB 2008.
View 1 RepliesI know there are the 'Wizards' to help me, but I need the code to access the Table Manager, the data set and so on, create them in my project and access the tables or stored procedures
View 2 RepliesHow can i select certain columns from a stored procedure, lets say i just want 2 of the 10 columns item and total sales.[code]....
View 2 RepliesI have an existing stored procedure that need to be used now as inline SQL statement in my VB console application. How do I change it?
Stored Procedure:
:Setvar CUSTOMDBNAME "My_DB"
USE [$(CUSTOMDBNAME)]
GO
[Code]..
My stored procedure:
[Code]...
So my idea is to have (in my VB.net project) a string that could dynamically change the stored procedure "Future"
My IDE is Visual studio 2008.I just want to ask how to create Stored Procedures in Microsoft Access Database (.mdb) and pass the values to a VB.NET Windows Application.
[Code]...
The store procedure which give me the information I need is:
USE [UCSMIS]
GO
SET ANSI_NULLS ON
[Code].....
When I try to add it to my report it gives an error, is it because of the temp table and how should I go about it?
I wish to create a stored procedure with multiple select statements. In addition, I then want to access each of the statements on the page load event. The purpose of the statements is to create multiple 'counts', and a number of labels on the page will each contain an individual returned count. At the moment I have created a basic stored procedure with two select statements:
CREATE PROCEDURE [dbo].[Finance_CustomerCount]
AS SELECT COUNT(Quote_ID) AS Count1 FROM dbo.generatedquotes WHERE (status = 2)
AND (Contract_End_Date >= GETDATE()) OR (status = 4) OR (status = 7) OR (status = 1)
SELECT COUNT(invoice_id) AS Count2 FROM dbo.invoices GROUP BY invoice_open HAVING (invoice_open = 'Y')
RETURN
I can then access the result of the first select statement by using this code:
Dim newconn As String = Session("ConnectionString")
Dim connection As New Data.SqlClient.SqlConnection(newconn)
Dim MyCommand As Data.SqlClient.SqlCommand = New Data.SqlClient.SqlCommand("Finance_CustomerCount", Connection)
[Code] ....
So what I basically need to know is, how do I access the second select statement result set for another label.
I have the following Stored Procedures create or replace PROCEDURE WEB_AC
[Code]...
First one: At some point in the application I have the valor parameter (of the visual Basic Function) as a string with spaces that is something like "some string with spaces". When this happens, the stored procedure don't update the table. If I execute the SP directly in the DB (with SQL Developer) all works fine. I know it has something to do with the string missing some quotes(') but I haven't make it work yet. Some ideas on this?
Second problem: Sometimes, when debuging the application, if I interrupt the execution, I start getting the ORA-24338 'statement handle not executed' error for hours every time I try to execute it again. I believe it has something to do with an open transaction. But honestly, as I'm new in working with Oracle, I really have no idea what the problem could be.
[Code]...
My IDE is MS Visual Studio 2008.I just want to ask how can I get the result of a store procedure from MS Access using VB.Net and use the result in a Crystal Report.
I have a Windows Application Project that requires this function.
Access 2007 and Stored Procedure. I keep getting this error: Parameter [@MyNumber] has no default value.
[Code]...
I have a stored procedure to update a table, but needs a couple of values from another table.
the first two selects get the value from the table and then are used in the update statement.
The select statments:
Select @iStatusDropDownValueID = iDropDownValueID
From DropDownValue
Inner Join DropDownValueType On DropDownValue.iDropDownValueTypeID =
[Code].....
First, the values that are retrieved by the first two select statements are always the same. So they could be passed in by the code itself. I don't know that this will speed things up at all, just make the entire stored procedure better and easier to read.
Second, if the "Value Name" should change this store procedure will break (which is possible, but not often).
I am looking for any insight into the Best Practices for this situation.
Need to design a program that can encrypt and decrypt messages stored in simple text files using a private key stored in a separate file. Software should also be able to allow the users to enter simple messages that either displays the encrypted or decrypted message. The encryption method should use a simple substitution method. It should be set out in the following format:
Example (the "@" is the separator):
@
The character The Code End of line
A @ 4gh EOL
B @ 84!9 EOL
Has to use the 256 ASCII character codes. The separator will be used to separate the character and its corresponding code.Here are the pseudocodes I have come up with:
� Read any private key file
separator = readline(file);
while not EOF(file) do
tempStr = readline(file);
[code].....
Im executing a stored procedure which saves some data to the database. once this is done im then trying to refresh a datagrid which has all of the records in.
The problem im having is the datagrid doesn't show the newly added record but if I wait a second or so and then refresh it does.
[Code]...
The "RefreshLog bit works but only If I manually do it after a second or so
I have a vb.net project. I have used the GUI and wizard to create a DataSource pointing to a SQL Server Stored Procedure. It works great! However, a new column has been added to the stored procedure.In the GUI if I right click the data source name and refresh, nothing happens.If I right click the stored procedure name and refresh, nothing happens.If I preview data I can see the new column returned.If I choose edit with wizard there is a red exclamation point next to the name of the stored procedure and red X be each column name.
When I hover over the field name it says "Column ABC in the DataSet references a column missing from the Database".When I hover over the stored procedure it says "STORED_PROCEDURE is mapped to STORED_PROCEDURE in the DataSet. Element STORED_PROCEDURE in the DataSet contains columns that do not exist in the Database".I can't figure out how to get the new columnd in the dataset.
I have a small question about stored procedures and the DateTime2 datatype in SQL Server 2008.
I got several stored procedures using the DateTime2 datatype and I want to insert the date 0001.01.01 00:00:00 but this won't work with VB.net and I cannot find the reason why.
I use this code fragment:
Dim sqlStatement As New SqlClient.SqlCommand
Dim sqlTransaction As SqlClient.SqlTransaction
sqlStatement.CommandType = CommandType.StoredProcedure
[Code]....
to call the stored procedures from my program (the stored procedure is a simple INSERT statement nothing else and works fine with actual dates). But when I enter the date 0001.01.01 00:00:00 it always comes up with the error that I cannot insert dates before '01.01.1753' when I want to execute the stored procedure.
Now I already know that the DateTime2 datatype in SQL Server should support this.
So my question is it possible that this is a driver problem and updating the SQLClient would solve this, or is this a general problem and I can finally stop searching and just use 1753.01.01.
I've been looking through some of my predecessors code and in all of this data level classes, he's defined all his stored procedure names as constants at the top of the classif there's really any benefit of doing this, other than to have all the procedure names at the top of the class?
View 6 RepliesI have been creating some asp.net sites that connect to an AS400 for data and I use a SQLDataSource for this with the following connection string (changed for security reasons). Now I need to create a winform application in VB.NET and am wondering how I would do this? I have only used MSSQL in winforms and use the sqldatareader for Select statements and execute non-query for others. I need to call some stored procedures on the AS400 passing parameters. I was hoping to do this by code like I do with MSSQL rather than drop in controls to bind to.
Provider=IBMDASQL.DataSource.1;Data Source=192.168.100.2;Password=xxxxx;User ID=xxxxx;Initial Catalog=S12345;Default Collection=AABBCC
Using VB2008 How can we fill the datagrid with information from a stored procedure and a sp variable.We have a dataset named: DBnamesDataSet, a source: ShownameBindingSource and a adapter: Show_nameTableAdapter.
Stored procedure:
alter PROCEDURE [dbo].[show_name]
@name varchar(50)
AS
[code]....
How I can execute a stored procedure and the put the results into a webbrowser to display. I know html so I will format the stuff to look how I want it to.
View 6 RepliesCan any one provide me the link or give me an idea how to read all the tables returned by SQL Stored Procedure?
My Stored procedure returns two tables:
ID Name
-----------
1 Jeff
2 Joe
ID Pages
----------
1 2
i want to read data from both of these tables. I tried using SQLDataReader and then populating into datatables but then i can only read the values of 1st table. I want to read both the table values. How do i do it? please let me know if you guys have any ideas how to deal with this.
In a nutshell, I am selecting values from a checked box column in a grid view. After checking the boxes I have a function that concatenates each customer number into a string called @CusList to use a parameter for a stored procedure Public Function SelectCusList()
Dim CusShow As String
Dim CusStr As String
Dim CusList As String
CusStr = ""
[Code]...
I want use a stored procedure in my project but I have no idea about it. Please could you tell me a little bit about how to use I can use one in my vb.net application ?
View 2 RepliesI have noticed the following but unable to understand why.Whenever a procedure is called in .net it takes more time than when it is called subsequently.Even if a procedure is called after some interval it takes more time than it takes if it is called quite frequently. i am not asking about sql query or sql command. any user definded function or user defined method takes much more time to finish when it is called occasionaly. but when the same procedure is called quite often it finishes a lot faster. why?
View 1 RepliesI am currently building an ASPX webpage for internal use. Basically, there are two buttons on this page and I need each one to kick off a different stored procedure on our local SQLServer based on the needed task. these stored rocedures do quite a bit of work and take 3-5 minutes to finish running. In neither case do I need any kind of gridview or other output from these stored procedures, as they are simply crunching/cleaning data that will populate some tables that the user will then use to complete their task. There are also no inputs or parameters needed.
So i'm OK at ASPX and pretty good at SQL, but have no idea on the VB code behind page. This is what I have ben able to put together based on 3 days of googling. i think i am close but can't quite get across the line.
CODE:
I'm not sure if you need to see any of my ASPX or my SQL. both work fine on their own, but i am not able to get these buttons to work.
I have a stored procedure that when I run it within my application it times out, but when I use Management Studio and pass in the same exact parameters, the stored procedure executes under a second. I had this exact same issue with the same stored procedure before, and all I did was recompile it, and it fixed the problem, but I do not want to keep having to recompile this stored procedure every few days or so. Has anyone else ran into this issue before?
Another note I would like to mention, is that I'm currently working on a test database, so I'm not inserting, deleting or updating any of the records that the Stored Procedure is using. So the database itself is not changing, but for some reason the Stored Procedure is showing degradation, and at the most we have 2 users in the database at a time.
Below is the code I use to call the Stored Procedure from my app.
cmd.CommandText = "sp__RECAP_SELECTION_GET_GRID_RECORDS_RECAP_TYPES"
cmd.Parameters.Add("@FK_KitchenID", SqlDbType.SmallInt).Value = cmbKitchenCustomer.KitchenID_SelectedPrimaryKey
[Code]....
As I mentioned, if I run the Stored Procedure within Mangement Studio, it runs with no problems. Also if I recompile the stored procedure, it runs fine within the app, but after a few days, the stored procedure starts to timeout again within the app and I need to recompile it again.
I am pretty new to VB and I am not sure why this is not working, basically I am trying to run a stored procedure from my web code. The sp runs just fine in SQL, I've tried it several times so I am sure that is not the problem. I don't want to return any results, I just want to see an "ok" statement if it runs and an error message if it doesn't. The code I am using for the lables (warnings and confirmation) is reused from earlier on the same page, the same goes for the validations (valUpload).
[Code]...
I am using SSMS 2008 and VB. I'm a novice VB developer. I am trying to display results of a simple stored proc on my ASPX page. But I get the error below. Here is my code behind for the ASPX page:
>MsgBox(GlobalFunctions.GlobalF.GetDevSQLServerStoredProcedure())
And my code from GlobalF namespace:
Public Shared Function GetDevSQLServerStoredProcedure()
Dim conn As SQLConnection
Dim DSPageData As New System.Data.DataSet[code]....
Where can I find information on creating or how can I create a button to execute a store procedure
View 6 RepliesI am trying to get some data relevant to a stored procedure (or funtion) back from a database using .Net. The first thing I need to be able to do, is get the stored proc from the database and turn it into string format.The information I need is: The return set of columns, tables used within the SP, Stored Procedures called from the SP. The only way of doing this at the moment that i can think of, is though parsing the text and looking for keyword matches.
View 3 Replies