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]....
I am trying to execute a stored procedure and place the information in a datagrid(without using the .net wizard) i want to do it manually. Using ado.net i believe. I am using vb.net and asp.net
Public cmd As New SqlCommand()
Public saoda As New SqlDataAdapter(cmd)
Public conn As New SqlConnection(" ")
Dim saods As New DataSet
[code]....
I am trying to pass some declared variables to a stored procedure. Here is my
Private Sub Button1_Click_1(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles startpayrollButton.Click
Dim ssql As String = "select MAX(payrolldate) AS [payrolldate], " & _
[Code].....
In my project I am using a LINQ to SQL class that contains a stored procedure which includes a string value as an output parameter.So to capture that, I created a string variable as follows in the procedure:Dim spOutput As String? However, when I do this I get the following message "Type 'String' must be a value type or a type argument constrained to 'Structure' in order to be used with 'Nullable' or nullable modifier '?'"
View 2 RepliesI was just handed a VB.NET prototyping effort, and I'm not very experienced with VB.NET.I had to create a stored procedure which returns a self-referential table in order. Once, I completed that I wanted to get a DataGridView to show the results.
Previously, my team has been doing a lot of this via the GUI designer of VB.NET so I don't have a ton of code to describe, unfortunately.I was trying to follow their pattern which I will briefly describe here:
I have a DataGridView whose DataSource property points to a BindingSource. The BindingSource points to a DataSet. The editor for that DataSet calls a TableAdapter which gets the data from the stored procedure.
I previewed the data for the TableAdapter. The data is there. On frmMain's load the TableAdapter's Fill command is run. Not seeing anything. And my hunch is that this is way too complex a process to be correct. Or maybe not. I did a lot of Google searches before I decided to bother you fine folks.
Using SQL Server 2008 I created a Stored Procedure called MyStoreProc and it runs fine from the Management Tools.
In VB.Net 2008 I created a new dataset and a new TableAdaptor. In this table adapter I created a new Query called FillByGrid and selected the Stored Procedure. Previewed data and it previewed correctly.
On a form I created DataGridView and selected the Table Adapter from the dataset.
I ran the app and no data is shown. Visual Studio autocreated the code below and I changed it to select the the Query I just created:
Me.MyTableAdapter.FillByGrid(Me.MyDataset.MyTableAdaptor)
No data is shown on the grid so I tried the manual approach:
' Create the dataset
Dim da As New SqlDataAdapter, ds As New DataSet
Dim conn As New SqlConnection
[Code]....
Still no data shown. However stepping through the I can see that the connection is open, and "da.Fill(ds, "tbl1")" takes a little bit of time as it is running the Stored Procedure and ds table has the correct number of rows and columns. Its just not being shown on the datagrid.
Creating another table adapter in the dataset and returning data from a database table using a standard select * from table command display in the datagridview fine.
I have a problem in populating datagridview using storedprocedure i tried but i cant get it.[code]...
View 2 RepliesThe 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 have a form that I'm running a stored procedure in and I'd like to pass the results to a datagridview and then offer the user the ability to export those results to a flat text file.
View 1 RepliesI've noticed that I can use LINQ to call a SQL Stored Procedure and use those results in a datagridview and have sorting by column automatically included. However, if the LINQ call is done on a web service, it seems like I have to return the results as an array which, when it gets back to the client, doesn't have the interface needed by the datagridview control to do it's sorting. I haven't been able to find an easy way to get the sorting ability back.
I've tried to serialize the result on the web service but the returned type can't be serialized. I tried putting the data context in a common DLL so I could use the Attach functionality but that doesn't exist for stored procedures. I suppose I could make a table in the data context just for this purpose but that will be a pain to maintain. Would I just be better off skipping LINQ and using an old fashioned typed dataset for this?
I have an app written in vb2005 that allows users to search a sql database for a store name. Once a store is selected, it then presents the user with 12 different tabs, each with a DataGridView showing various bits of info about the store. It's a large and convoluted database, so I'm using stored proceedures to pull the data for each tab. Here's a sample of one of the subs that populates the tabs.[code...]
Now, this works just fine to populate the grids on the tabs, and the same code is copied into each of the subs that fill the grids (changing the stored proc, parameter and grid names, of course). The problem I'm having is how to move changes made to the data in the grid back into the database.
I have stored procs that do this (and the procs do work), I just can't seem to figure out how to trigger the stored proc when the user makes a change.
If it matters, there is only a grid on each tab, I did not use a binding source as I am trying to keep an abstraction layer between the data and the app. I did create a dataset, which is how I gain access to the stored procs, but I left the grids unbound and (as seen in my code) bind them at run time.
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.
I have a number of tabpages that each contain a datagridview control only. Based on the results of a SQL statement I then loop through each tabpage and set its name. The reason for this is that at runtime (and depending on the results of the SQL statement), the contents of each tab page will change - henceI loop through the tabpages and set the name/ text (to reflect what will later be shown in the DataGridView)
The datagridview control on each tab has the default name e.g. DataGridView1, DataGridView2, DataGridView3 so it's easy for me to loop through them e.g.:
for i as integer = 1 to 3
[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 RepliesIf 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 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
I 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 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 am entering data in a dataGridView, I have a column named with 6 characters.The first 3 characters are always "HHH"When I enter in this cell, Is there any way to put automatically these 3 characters and start to write in the fourth one?
View 2 RepliesI 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]....