I already sent this to the ACCESS newsgroup. But since I do not know really
which side is really causing the problem, I have decided to send this
inquiry
to this newsgroup also, if I may.
Below is the environment of the application:
a. MS Access 2003 application running on Windows XP
b. SQL Server 2000 - backend running MS Server 2003 OS
Below is the code that is giving me an error:
Dim com As ADODB.Command
Set com = New ADODB.Command
With com
.ActiveConnection = "DSN=YES2;DATABASE=YES100SQLC;"
.CommandText = "sp_Recalculate"
.CommandType = adCmdStoredProc
.Parameters.Refresh
.Parameters("@ItemNumber") = ItemNum
.Execute ' This is where it hangs up...
CREATE PROCEDURE DBO.sp_Recalculate
@ItemNumber nvarchar(50),
@TotalInStock int = 0,
@TotalCost money = 0
AS
BEGIN
SET @TotalInStock = (
SELECT Sum([Quantity in Stock])
FROM [Inventory Products]
WHERE [Item Number] = @ItemNumber)
SET @TotalCost = (
SELECT Sum([Cost] * [Quantity in Stock])
FROM [Inventory Products]
WHERE [Item Number] = @ItemNumber)
END
When the process goes to the ".Execute" line, it hangs up for a long time
then gives me an error message "Everflow". I have been trying to solve
this issue but do not have an idea for now of the cause.
Below is my finding:
a. When I run the stored procedure in the SQL analyzer, it works just fine.
I placed a SELECT statement to view the result of the stored procedure.
It gives the correct values.
I have the codes below in excel VBA that always returns a "-1" value of the rs.RecordCount. The sql connection is open, BUT IS NOT ABLE TO EXECUTE THE STORED PROCEDURE. Anyone please I need help ;(
Database: SQL Server 2005 Programming language: VBA (Excel 2003)
Dim cn as new ADODB.Connection Dim cmd as new ADODB.Command Dim rs as new ADODB.Recordset Dim connstr as string
I have posted various questions on the microsoft ng trying to identify the cause of this error.
ADODB.Command error '800a0cb3' Object or provider is not capable of performing requested operation.
I have MDAC 2.8 installed locally on my machine.
Via IIS I created a virtual directory via IIS that points to my ASP files on c:
Via the SQL Server IIS for XML configuration utility I created a virtual directory with a different names that points to the same directory as that created via IIS.
The SQL database is on a different machine and I connect via the OLEDB DSNless connection string.
I used a ADODB.Stream to transform the XML against the XSL but I couldnt get it to work. To simplify things and work towards a solution I inserted the code into my ASP from the MS KB article Q272266 (see below). I amended the ms code to change the connection code and call a stored procedure that exists on the database. The ms code gives the same error as my original code.
I tried changing the CursorLocation to server and client but the results were the same.
I put a SQL trace on the DB to determine if the stored procedure gets ran, it does not.
If I run the following in the URL it works:
If I run http://localhost/p2/?sql=SELECT+*+FROM+tblStatus+FOR+XML+AUTO&root=root&xsl=tblStatusDesc.xsl it works. If I run the xml template it works: http://localhost/p2/xml/test.xml
The two lines above run. My IIS server uses a virtual directory called dev, so when I run the ASP I type http://localhost/DEV/secure/aframes.asp the IIS virtual directory creted by sql server is called p2 but has the same source code directory.
Here is the MS code amended as described above that does not work.
sQuery = "<ROOT xmlns:sql='urn:schemas-microsoft-com:xml-sql'><sql:query>Select StatusDesc from tblStatus for XML Auto</sql:query></ROOT>" '*************************************************
Dim txtResults ' String for results dim CmdStream ' as ADODB.Stream
Set adoConn = CreateObject("ADODB.Connection") Set adoStreamQuery = CreateObject("ADODB.Stream")
adoConn.ConnectionString = sConn adoConn.Open
Set adoCmd = CreateObject("ADODB.Command") set adoCmd.ActiveConnection = adoConn
adoConn.CursorLocation = adUseClient
Set adoCmd.ActiveConnection = adoConn
adoStreamQuery.Open ' Open the command stream so it may be written to adoStreamQuery.WriteText sQuery, adWriteChar ' Set the input command stream's text with the query string adoStreamQuery.Position = 0 ' Reset the position in the stream, otherwise it will be at EOS
Set adoCmd.CommandStream = adoStreamQuery ' Set the command object's command to the input stream set above adoCmd.Dialect = "{5D531CB2-E6Ed-11D2-B252-00C04F681B71}" ' Set the dialect for the command stream to be a SQL query. Set outStrm = CreateObject("ADODB.Stream") ' Create the output stream outStrm.Open adoCmd.Properties("Output Stream") = response ' Set command's output stream to the output stream just opened adoCmd.Execute , , adExecuteStream ' Execute the command, thus filling up the output stream.
We use a number of similar databases and frequently create a newdatabase using a backup restore of another similar database. We try tokeep changes between databases in _Additional tables - like AccountAdditional, Sale_Additional so most tables stay the same. The latestrestored database (I'll call it DBaseA) is behaving differently in VB6code and I need help trying to make it work.I have been using use an ADODB.Command to execute a stored procedureusing adCmdStoredProc and Parameters.Refresh to get an output parametervalue which is an integer. Here is a sample of the VB:Public Function UnlockAccount(Account_ID As String) As BooleanDim LCmd As ADODB.CommandOn Error GoTo ERROR_UnlockAccountUnlockAccount = TrueIf Account_ID <> "" ThenIf DBConnect() ThenSet LCmd = New ADODB.CommandLCmd.ActiveConnection = CN(0)LCmd.CommandTimeout = 0LCmd.CommandType = adCmdStoredProcLCmd.CommandText = "Unlock_Account"LCmd.Parameters.RefreshLCmd.Parameters("@Account_ID").VALUE = Account_IDLCmd.Execute , , adExecuteNoRecordsIf LCmd.Parameters("@Status") <> 0 ThenUnlockAccount = FalseEnd IfElsemsgbox "UnlockAccount: DBConnect Failed", "UnlockAccount"End IfEnd IfExit FunctionERROR_UnlockAccount:UnlockAccount = Falsemsgbox "UnlockAccount: App Error: " & Err & " " & _Err.Description, "UnlockAccount"DBConnect TrueExit FunctionResume'for debuggingEnd Functionand this is one of the sps that fails - can't be more simple!Create PROCEDURE Unlock_Account( @Account_ID UNIQUEIDENTIFIER,@Status INTEGER =null OUTPUT) AS/* Strip functionality */SET @Status = 0goThis code is still working in at least 4 other databases. It fails indatabase DBaseA with the error:Invalid character for cast conversionWe are talking about the exact same table definition and the exact samestored procedure just in different databases.I fumbled around on the Internet and found a mention in a PowerBuilderweb site of additional parameters in the connect string which seems tofix the problem in SOME of the stored procs - but not all.The added parameters are:DelimitIdentifier='No';MsgTerse='Yes';CallEscape='No';FormatArgsAsExp='N'They are not documented in MS but are in Sybase?? No idea why, but someof the stored proc functions work when I add this to the connect string.The complete connect string is:Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=DBaseA;Data Source=PHASE2-S500,1433;Network Library=DBMSSOCN;DelimitIdentifier='No';MsgTerse='Yes';CallEscape='No';FormatArgsAsExp='N'The databases are on different servers - but they are running the sameversion of SQL2000, the same version of Windows. I see no differencesin options set in the different servers or between databases that workand this one. And dbcc checkdb runs clean.The compiled code that fails, fails across the board - not just on my(developer) PC. The same source code works on the same tables in otherdatabases, including the one we copied....The final kicker - if I build a SQL statement string and get therecordset instead, it works like a charm. But I have to fix about 20 VBfunctions and check that I do have a recordset and SET NOCOUNT ON in all20 sps.I feel this is either something so obvious I will kick myself or soserious Microsoft will be digging into it.Any ideas anyone??Sandie*** Sent via Developersdex http://www.developersdex.com ***Don't just participate in USENET...get rewarded for it!
We have an ASP 3.0 application that currently works "correctly" on one server, Server A, and we€™re testing it on another server, Server B, which is 64 bit.
For this example, the stored procedure sp_TestProcedure is:
CREATE PROCEDURE sp_TestProcedure @Name varchar(50) AS INSERT INTO tblTest ([Name], Date) VALUES (@Name, getDate()) SELECT COUNT(*) AS 'Count' FROM tblTest
The basic point is the stored procedure does an INSERT and then a SELECT.
Now... to the issue. On Server A, the variable rs above ends up with a single open Recordset which is the results of the SELECT statement. However, on Server B, rs is set to a closed recordset, and rs.NextRecordset() gets a second recordset of the results of the SELECT statement.
I understand what's going on. Server B is first returning the number of rows affected by the INSERT which translates to a closed recordset. But Server A does not do this.
I would like to know why the default behavior of the command's .Execute is different on the different servers. Does it relate to the Provider/Driver settings in the connection string? Does it have anything to do with 64 bit VS. 32 bit servers?
I know that one way to address this issue to add SET NOCOUNT ON to the start of the stored procedure. But we have many stored procedures, and if the solution is a change in the connection string, that would be preferred. Also, whatever the possible solution is, I also looking to discover *why* it's happening.
Hi fellows I am working in a formula about drilling machine. This machine sends the inofrmation to the server basically the name of a blasthole, revolution,pulldown and so on. I have another database with information of the same blasthole, but with different items such as drill diameter,site,area and so on, so i have two tables with information, but both of them have the same blasthole that means that i can relate with a join and identify each blasthole. but teh main point is that i have a specific column con a distance that the machine is drilling for example in a certain moment this machine is drilling 1 feet 3 feet 4 feet until to reach the bottom of a limit. his My problem is that i want to create a from to for those distances.
I was thinking to create a pk2 in order to know how many values have each blasthole and create a loop and maybe i can organize the column from something like this
hi have written SSIS script and i am using script component to Row count below my code what i have written. and i am getting error below i have mention...after code see the error using System;
} the error Script component has encountered an exception in user code Object is not an ADODB.RecordSet or an ADODB.Record. Parameter name: adodb at System.Data.OleDb.OleDbDataAdapter.FillFromADODB(Object data, Object adodb, String srcTable, Boolean multipleResults) at System.Data.OleDb.OleDbDataAdapter.Fill(DataTable dataTable, Object ADODBRecordSet) at ScriptMain.CreateNewOutputRows() at UserComponent.PrimeOutput(Int32 Outputs, Int32[] OutputIDs, PipelineBuffer[] Buffers) at Microsoft.SqlServer.Dts.Pipeline.ScriptComponentHost.PrimeOutput(Int32 outputs, Int32[] outputIDs, PipelineBuffer[] buffers)
I'm trying to re-write my database to de-couple the interface (MS Access) from the SQL Backend. Â As a result, I'm going to write a number of Stored Procedures to replace the MS Access code. Â My first attempt worked on a small sample, however, trying to move this on to a real table hasn't worked (I've amended the SP and code to try and get it to work on 2 fields, rather than the full 20 plus).It works in SQL Management console (supply a Client ID, it returns all the client details), but does not return anything (recordset closed) when trying to access via VBA code.The Stored procedure is:-
USE [VMSProd] GO /****** Object: StoredProcedure [Clients].[vms_Get_Specified_Client] Script Date: 22/09/2015 16:29:59 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON
How do you use a variable in the ORDER BY command in a sql statement. I currently have: **************************************** CREATE Procedure SS_POList ( @CompanyID nvarchar(10), @PO varchar (20) = '%' ) As
SELECT SS_Sendback.EndUserPO, SS_Sendback.PO, COUNT(SS_Sendback.EndUserPO) as pocount, SUM(SS_Sendback.Customerprice) as totcost
FROM SS_Sendback
WHERE SS_Sendback.EndusercustomerNumber = @CompanyID AND SS_Sendback.EnduserPO Like @PO
GROUP BY SS_Sendback.EndUserPO, SS_Sendback.PO
ORDER BY SS_Sendback.PO GO ************************************* I changed it to ************************************* CREATE Procedure SS_POList ( @CompanyID nvarchar(10), @PO varchar (20) = '%', @Order varchar(20) ) As
SELECT SS_Sendback.EndUserPO, SS_Sendback.PO, COUNT(SS_Sendback.EndUserPO) as pocount, SUM(SS_Sendback.Customerprice) as totcost
FROM SS_Sendback
WHERE SS_Sendback.EndusercustomerNumber = @CompanyID AND SS_Sendback.EnduserPO Like @PO
GROUP BY SS_Sendback.EndUserPO, SS_Sendback.PO
ORDER BY @Order GO
and I receive the following error.. error1008: the select item identified by the Order By number 1 contains a variable as part of the expression identifying a column position. Variables are only allowed when ordering by an expression referencing a column name. How does it know @order is a position and not a column name... It's a variable. I'm obviously doing something wrong. Can someone help. Thanks
The above procedure is called 4 times from another procedure by passing appropriate parameters. Most of the time, it executes successfully for 2 times before failing. When I try to debug, it always hangs at EXEC master..xp_cmdshell @CommandSQL. The task manager shows bcp.exe as running at this point. Interestingly, the required rows are exported using the bcp command, but the stored procedure does not move further until bcp.exe is manually stopped from the task manager.
hi,i had a small doubt , i have a table xxx with two columns (a int,b int) and i have inserted 5 rows my query is to add the two colums using astored procedure and the result has to be displayed in an separatecolumn --this has to be done only stored procedures ---i know how tosolve the problem using computed columns conceptlike thiscreate table yyy(a int ,b int ,total as a+b)insert into yyy values (12,13)select * from yyyi need the answer using stored procedures---is it possiblepls help mesatish
could someone please tell me : am I supposed to use the OLE DB Command in a dataflow to call a stored procedure to return a value? Or is it just supposed to be used to call a straightforward insert statement only?
What I am hoping to do:
I have a table with a few columns and one identity column. In a dataflow I would like to effect an insert of a record to this table and retrieve the identity value of the inserted record... and I'd like to store the returned identity in a user variable.
If I AM supposed to be able to do this... then how on earth do I do it?
I have spent hours fooling around with the OLE DB command trying to call a stored proc and get a return value.
In the Advanced Editor any time I try to add an output column (by clicking on Add Column) I just get an error dialog that says "the component does not allow adding columns to this input or output)
So, am getting pretty concussed .. banging my head of the wall like this...
So put me out of my misery someone please.... is the OLE DB Command intended for this or not?
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)
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?
Hi everybody, I would like to know if it's possible to execute a stored procedure, passing it parameters, using not CommandType.StoredProcedure value of sqlcommand, but CommandType.Text.
I tried to use this: sqlCmd.CommandType = CommandType.Text sqlCmd.Parameters.Add(sqlPar) sqlCmd.ExecuteNonQuery()
With this sql command: "exec sp ..."
I wasn't able to make it to work, and I don't know if it's possible.
Another question: if it's not possible, how can I pass a Null value to stored procedure? This code: sqlPar = new SqlParameter("@id", SqlDbType.Int) sqlPar.Direction = ParameterDirection.Output cmd.Parameters.Add(sqlPar)
sqlPar = new SqlParameter("@parent_id", DBNull) cmd.Parameters.Add(sqlPar)
doesn't work, 'cause I get this error: BC30684: 'DBNull' is a type and cannot be used as an expression.
How can I solve this? Bye and thanks in advance.
P.S. I would prefer first method to call a stored procedure ('cause I could call it with 'exec sp null' sql command, solving the other problem), but obviusly if it's possible...=)
All- Is there a way that I can embedd a call to a stored procedure into an existing INSERT section in a table adapter? Say my objective is to call a stored procedure called personfill automatically RIGHT AFTER the TableAdapter inserts a row into the person table. One catch is that the stored procedure must be sent the value of unique identifier field person_id, which was created for the new person record automatically by the db. (If this is not possible to do, I might try using a TRIGGER in the person table.) Below is the INSERT code of the TableAdapter. My guess is that if I could call a procedure, I would want to put the call between lines 12 and 13. Your comments would be most appreciated!!! -Kurt1 <InsertCommand> 2 <DbCommand CommandType="Text" ModifiedByUser="false"> 3 <CommandText>INSERT INTO [person] ([family_id], [circle_id], [person_type_id], [last], [first], [username], [password]) VALUES (@family_id, @circle_id, @person_type_id, @last, @first, @username, @password)</CommandText> 4 <Parameters> 5 <Parameter AllowDbNull="false" AutogeneratedName="" DataSourceName="" DbType="Int16" Direction="Input" ParameterName="@family_id" Precision="0" ProviderType="SmallInt" Scale="0" Size="0" SourceColumn="family_id" SourceColumnNullMapping="false" SourceVersion="Current" /> 6 <Parameter AllowDbNull="false" AutogeneratedName="" DataSourceName="" DbType="Int16" Direction="Input" ParameterName="@circle_id" Precision="0" ProviderType="SmallInt" Scale="0" Size="0" SourceColumn="circle_id" SourceColumnNullMapping="false" SourceVersion="Current" /> 7 <Parameter AllowDbNull="false" AutogeneratedName="" DataSourceName="" DbType="Int16" Direction="Input" ParameterName="@person_type_id" Precision="0" ProviderType="SmallInt" Scale="0" Size="0" SourceColumn="person_type_id" SourceColumnNullMapping="false" SourceVersion="Current" /> 8 <Parameter AllowDbNull="false" AutogeneratedName="" DataSourceName="" DbType="AnsiString" Direction="Input" ParameterName="@last" Precision="0" ProviderType="VarChar" Scale="0" Size="0" SourceColumn="last" SourceColumnNullMapping="false" SourceVersion="Current" /> 9 <Parameter AllowDbNull="false" AutogeneratedName="" DataSourceName="" DbType="AnsiString" Direction="Input" ParameterName="@first" Precision="0" ProviderType="VarChar" Scale="0" Size="0" SourceColumn="first" SourceColumnNullMapping="false" SourceVersion="Current" /> 10 <Parameter AllowDbNull="true" AutogeneratedName="" DataSourceName="" DbType="AnsiString" Direction="Input" ParameterName="@username" Precision="0" ProviderType="VarChar" Scale="0" Size="0" SourceColumn="username" SourceColumnNullMapping="false" SourceVersion="Current" /> 11 <Parameter AllowDbNull="true" AutogeneratedName="" DataSourceName="" DbType="AnsiString" Direction="Input" ParameterName="@password" Precision="0" ProviderType="VarChar" Scale="0" Size="0" SourceColumn="password" SourceColumnNullMapping="false" SourceVersion="Current" /> 12 </Parameters> 13 </DbCommand> 14 </InsertCommand> 15 <SelectCommand> 16 <DbCommand CommandType="Text" ModifiedByUser="true"> 17
I have a process that is running on the os. This process is picking up FTP files every 5 min, it renames them so not to confuse them with the new files, copies all renamed files into one file to be used by bulk insert, runs the bulk insert to populate a table, and then runs the stored procedure that scrubbing the data and insert it into another table. For every transaction that I do in my stored procedure, I do the error checking as follows:
IF @@error <> 0 BEGIN ROLLBACK TRANSACTION RETURN
If my stored procedure encounters an error, return statement will stop it from running. If this happens, I need to stop the process that is running on the os as well.
Questions:
How can that be accomplished?
How to restart the stored procedure ones the error has been corrected?
I'm using SQL RS 2005 and have a report where we want the report to run a different stored procedure depending on if a condition is true. I've set my 'command type' to stored proc and can type in the name of a stored procedure. If I type in just one stored procedure's name, it runs fine. But if I try to use a =IIF(check condition, if true run stored proc 1, if false run storedproc 2) then the exclamation (run) button is greyed out. Does anyone know how I can do this? Thanks.
I have no problem getting OLE DB Command transformations to support single returns by a procedure.
For example, exec name_of_procedure ?,?,? OUTPUT
However, I have a stored procedure which accepts 1 input and returns 5 outputs. This procedure works fine at the command line but when I try to incorporate it into a OLE DB Command I don't get the multiple values returned. There's no problem at all configuring the transform as it recognizes all input and output parameters. For some reason I just don't get values returned.
I have an application which runs successfully on a couple of my customer's machines but fails on a third. It seems to fail when opening the database:
Unable to cast COM object of type 'ADODB.CommandClass' to interface type 'ADODB._Command'. This operation failed because the QueryInterface call on the COM component for the interface with IID '{B08400BD-F9D1-4D02-B856-71D5DBA123E9}' failed due to the following error: No such interface supported (Exception from HRESULT: 0x80004002 (E_NOINTERFACE)). Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=false; Initial Catalog=lensdb;Data Source = SQL
Before I got this error I was getting another problem (sorry didn't make a copy of that error's text) that made me think that adodb.dll simply wasn't loaded/registered. I got rid of that error by copying my adodb.dll onto the third machine and running gacutil /i. There is now an entry in winntassemblies for adodb.
Just in case you think it could be an obvious registry problem: when I started getting the current error I thought that maybe the registry needed updating and I merged the following lines into onto the target machine (from my dev machine):
I have a Lookup Transformation that matches the natural key of a dimension member and returns the dimension key for that member (surrogate key pipeline stuff).
I am using an OLE DB Command as the Error flow of the Lookup Transformation to insert an "Inferred Member" (new row) into a dimension table if the Lookup fails.
The OLE DB Command calls a stored procedure (dbo.InsertNewDimensionMember) that inserts the new member and returns the key of the new member (using scope_identity) as an output.
What is the syntax in the SQL Command line of the OLE DB Command Transformation to set the output of the stored procedure as an Output Column?
I know that I can 1) add a second Lookup with "Enable memory restriction" on (no caching) in the Success data flow after the OLE DB Command, 2) find the newly inserted member, and 3) Union both Lookup results together, but this is a large dimension table (several million rows) and searching for the newly inserted dimension member seems excessive, especially since I have the ID I want returned as output from the stored procedure that inserted it.
Thanks in advance for any assistance you can provide.
Hi all - I'm trying to optimized my stored procedures to be a bit easier to maintain, and am sure this is possible, not am very unclear on the syntax to doing this correctly. For example, I have a simple stored procedure that takes a string as a parameter, and returns its resolved index that corresponds to a record in my database. ie exec dbo.DeriveStatusID 'Created' returns an int value as 1 (performed by "SELECT statusID FROM statusList WHERE statusName= 'Created') but I also have a second stored procedure that needs to make reference to this procedure first, in order to resolve an id - ie: exec dbo.AddProduct_Insert 'widget1' which currently performs:SET @statusID = (SELECT statusID FROM statusList WHERE statusName='Created')INSERT INTO Products (productname, statusID) VALUES (''widget1', @statusID) I want to simply the insert to perform (in one sproc): SET @statusID = EXEC deriveStatusID ('Created')INSERT INTO Products (productname, statusID) VALUES (''widget1', @statusID) This works fine if I call this stored procedure in code first, then pass it to the second stored procedure, but NOT if it is reference in the second stored procedure directly (I end up with an empty value for @statusID in this example). My actual "Insert" stored procedures are far more complicated, but I am working towards lightening the business logic in my application ( it shouldn't have to pre-vet the data prior to executing a valid insert). Hopefully this makes some sense - it doesn't seem right to me that this is impossible, and am fairly sure I'm just missing some simple syntax - can anyone assist?
I executed them and got the following results in SSMSE: TopSixAnalytes Unit AnalyteName 1 222.10 ug/Kg Acetone 2 220.30 ug/Kg Acetone 3 211.90 ug/Kg Acetone 4 140.30 ug/L Acetone 5 120.70 ug/L Acetone 6 90.70 ug/L Acetone ///////////////////////////////////////////////////////////////////////////////////////////// Now, I try to use this Stored Procedure in my ADO.NET-VB 2005 Express programming: //////////////////--spTopSixAnalytes.vb--///////////
Public Class Form1
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
Dim sqlConnection As SqlConnection = New SqlConnection("Data Source = .SQLEXPRESS; Integrated Security = SSPI; Initial Catalog = ssmsExpressDB;")
Dim sqlDataAdapter As SqlDataAdapter = New SqlDataAdaptor("[spTopSixAnalytes]", sqlConnection)
'Pass the name of the DataSet through the overloaded contructor
'of the DataSet class.
Dim dataSet As DataSet ("ssmsExpressDB")
sqlConnection.Open()
sqlDataAdapter.Fill(DataSet)
sqlConnection.Close()
End Sub
End Class ///////////////////////////////////////////////////////////////////////////////////////////
I executed the above code and I got the following 4 errors: Error #1: Type 'SqlConnection' is not defined (in Form1.vb) Error #2: Type 'SqlDataAdapter' is not defined (in Form1.vb) Error #3: Array bounds cannot appear in type specifiers (in Form1.vb) Error #4: 'DataSet' is not a type and cannot be used as an expression (in Form1)
Please help and advise.
Thanks in advance, Scott Chang
More Information for you to know: I have the "ssmsExpressDB" database in the Database Expolorer of VB 2005 Express. But I do not know how to get the SqlConnection and the SqlDataAdapter into the Form1. I do not know how to get the Fill Method implemented properly. I try to learn "Working with SELECT Statement in a Stored Procedure" for printing the 6 rows that are selected - they are not parameterized.
I have some code that I need to run every quarter. I have many that are similar to this one so I wanted to input two parameters rather than searching and replacing the values. I have another stored procedure that's executed from this one that I will also parameter-ize. The problem I'm having is in embedding a parameter in the name of the called procedure (exec statement at the end of the code). I tried it as I'm showing and it errored. I tried googling but I couldn't find anything related to this. Maybe I just don't have the right keywords. what is the syntax?
CREATE PROCEDURE [dbo].[runDMQ3_2014LDLComplete] @QQ_YYYY char(7), @YYYYQQ char(8) AS begin SET NOCOUNT ON; select [provider group],provider, NPI, [01-Total Patients with DM], [02-Total DM Patients with LDL],
I have a sub that passes values from my form to my stored procedure. The stored procedure passes back an @@IDENTITY but I'm not sure how to grab that in my asp page and then pass that to my next called procedure from my aspx page. Here's where I'm stuck: Public Sub InsertOrder() Conn.Open() cmd = New SqlCommand("Add_NewOrder", Conn) cmd.CommandType = CommandType.StoredProcedure ' pass customer info to stored proc cmd.Parameters.Add("@FirstName", txtFName.Text) cmd.Parameters.Add("@LastName", txtLName.Text) cmd.Parameters.Add("@AddressLine1", txtStreet.Text) cmd.Parameters.Add("@CityID", dropdown_city.SelectedValue) cmd.Parameters.Add("@Zip", intZip.Text) cmd.Parameters.Add("@EmailPrefix", txtEmailPre.Text) cmd.Parameters.Add("@EmailSuffix", txtEmailSuf.Text) cmd.Parameters.Add("@PhoneAreaCode", txtPhoneArea.Text) cmd.Parameters.Add("@PhonePrefix", txtPhonePre.Text) cmd.Parameters.Add("@PhoneSuffix", txtPhoneSuf.Text) ' pass order info to stored proc cmd.Parameters.Add("@NumberOfPeopleID", dropdown_people.SelectedValue) cmd.Parameters.Add("@BeanOptionID", dropdown_beans.SelectedValue) cmd.Parameters.Add("@TortillaOptionID", dropdown_tortilla.SelectedValue) 'Session.Add("FirstName", txtFName.Text) cmd.ExecuteNonQuery() cmd = New SqlCommand("Add_EntreeItems", Conn) cmd.CommandType = CommandType.StoredProcedure cmd.Parameters.Add("@CateringOrderID", get identity from previous stored proc) <------------------------- Dim li As ListItem Dim p As SqlParameter = cmd.Parameters.Add("@EntreeID", Data.SqlDbType.VarChar) For Each li In chbxl_entrees.Items If li.Selected Then p.Value = li.Value cmd.ExecuteNonQuery() End If Next Conn.Close()I want to somehow grab the @CateringOrderID that was created as an end product of my first called stored procedure (Add_NewOrder) and pass that to my second stored procedure (Add_EntreeItems)
I have a stored procedure and in that I will be calling a stored procedure. Now, based on the parameter value I will get stored procedure name to be executed. how to execute dynamic sp in a stored rocedure
at present it is like EXECUTE usp_print_list_full @ID, @TNumber, @ErrMsg OUTPUT
I want to do like EXECUTE @SpName @ID, @TNumber, @ErrMsg OUTPUT
