Extended Stored Procs In Visual Basic? COM??
Aug 2, 2000Is it possible to write extended stored procs in VB? I do not know C or C++ or Perl or whatever ....
How about calling a COM from a stroed procedure???
Thanks,
Judith
ADVERTISEMENT
Create Extended Stored Procedures In Visual Basic
Mar 9, 2001You know this sample ODS dll project:
$80oolsdevtoolssamplesodsxp_hello
I need to find a template like this in Visual Basic.
On this site I read through the xp_Encrypt project which was developed in VB. I did not see source code or a downloadable project file.
My searching on the internet hasn't yielded any practicle results, all examples are either how to use an extended stored procedure or are a MSVC++ project.
Anyone find VB related resources, anywhere? Books, TV, magazines????
Executing Extended Stored Procs???
Dec 3, 1999I've written a very simple extended stored proc--little more than what VC++
6.0's ext stored proc wizard generates--set up the xp in the master database,
and eventually want to execute it from a trigger defined on a table in another database...
I've tested the xp in Query Analyzer. If the DB is set to 'master', the xp
runs (exec xp_InputTrigger), no problem. If I change databases and try to run the xp, I get:
Server: Msg 2812, Level 16, State 62, Line 1
Could not find stored procedure 'xp_InputTrigger'.
Keying "exec master.xp_InputTrigger", also fails: Could not find stored procedure 'master.xp_InputTrigger'.
I've checked permissions--all seem to be set right. Anyone have any insight
for me here? Many thanks,
mjo
SQL Mail (Extended Stored Procs - Failure Logging)
Aug 16, 2001What happens if any of the extended stored procs below can't connect to the Exchange server? Is the failure logged? Does it back off and try again later?
xp_startmail
xp_sendmail
xp_findnextmsg
xp_readmail
xp_deletemail
sp_processmail
xp_stopmail
Stored Procedure Error When Run From Visual Basic In Access
Jan 23, 2002I am working in an access data project. I have a stored procedure that runs fine when I open and run it directly in sql. When I use the DoCmd.OpenStoredProcedure method in VB code, the stored procedure also runs fine (and successfully adds records as it should) but then I
get an error: #7874 "...can't find the
object...'[Name of sp'". This halts the vb code and is a
problem. Here's example code from a sp that causes
this problem:
Insert into Table (Field1, Field2, Field3, Field4)
Select Field1, 'Test', Field5, GetDate()
from View1
I understand there may be another syntax to run a stored procedure from access visual basic other than DoCmd. I would very much appreciate guidance as to how to do this.
Thank you.
Passing Parameters To A Stored Procedure In Visual Basic
Jan 5, 2007Hi peeps,
I need some help with passing parameters to a stored procedure from my visual basic code.
Unfortunately im a bit of a novice with Visual basic and therefore have very little experience with it.
I have written a stored procedure in VS 2005 which when executed from the server explorer appears to retrieve the results that I require. However I am at a loss for how to actually call this procedure from my visual basic code.
The stored procedure is fairly simple requiring 5 colums from 2 tables. The procedure requires a single parameter to be passed to it.
The code for the procedure is listed below:
/*
Name: usp_display_all_users
Description: Displays activeuser, personid, comment from table: pswds
Userid and sort from table: people
Where the username is like the parameter supplied.
Both tables joined on personid
Author: Iain Blackwood
Modification log: Change
Description Date Changed by
Created proc 02/01/07 Iain Blackwood
*/
ALTER PROCEDURE usp_display_all_users
(
@searchStr nvarchar(128) =''
)
AS
SELECT dbo.pswds.activeuser, dbo.pswds.personid, dbo.people.userid, dbo.people.sort, dbo.pswds.comment
FROM dbo.pswds INNER JOIN
dbo.people ON dbo.pswds.personid = dbo.people.personid
WHERE (dbo.people.sort LIKE @searchStr + '%')
ORDER BY dbo.people.sort
The Visual Basic application I am working on firstly requires login details from the user to build a connection string for the SqlConnection. Once these vaules have been succesfully retrieved the application should display a view with the data returned by the stored procedure (in this case the stored procedure should use the default input parameter value of an empty string to return every row of data from the tables). However I also require that the stored procedure be called if the user enters a search string into the relevant textbox.
I have managed to reproduce the view I require with the following code however this is using SQL commands passed directly to the an SqlDataAdapter and not by calling the Stored procedure that i have written.
Private Sub fillDataGrid()
' I NEED TO:
' 1: Fill the data set with all Accounts
' 2: Diplay the Data to the data grid
' delcare a new SQL connection
sqlCon = New SqlConnection(conStr)
' Delcare and build the SQL Command String: WILL BE REPLACED BY STORED PROCEDURE
Dim comStrPeople As String = "SELECT pswds.activeuser, pswds.personid, userid, sort, pswds.comment"
comStrPeople += " FROM pswds INNER JOIN"
comStrPeople += " people ON pswds.personid = people.personid"
comStrPeople += " ORDER BY sort"
' Display the command string: TEMPOARY
testlbl2.Text = comStrPeople
' Declare a new SQL data adapter
sqlDataAdapter1 = New SqlDataAdapter(comStrPeople, sqlCon)
Try
' Declare a new dataset
sqlDataSet = New DataSet
' fill the sql data adapter with data from dataset: called PeoplePswds
sqlDataAdapter1.Fill(sqlDataSet, "PeoplePswds")
' Fill the forms datagrid view with data from the Dataset table PeoplePswds
DataGrid1.DataSource = sqlDataSet.Tables("PeoplePswds").DefaultView
Catch ex As Exception
' Display suitable error message
MessageBox.Show("Unable to retrieve Account Data at sub fillDataGrid" + ex.Message)
End Try
End Sub
I Guess what im asking for is someone to show / help with how the stored procedure is called from the visual basic code and passed the parameter/s required.
Thanx Flakkie
Stored Procedure Not Returning OUTPUT Parameters To Visual Basic Program
Mar 24, 2008I have coded a stored procedure to return nearly all of the columns of a single record selected by using a unique key value. The record is in an SQL database, not within an in-memory DataSet. All of the parameters that I wish to have returned to my program are defined as OUTPUT; the two key values are defaulted to INPUT, as there is no need to return them to the calling program. I also have defined the direction of these parameters in the calling SQLDataAdapter function. However, when I run this, the values returned are either the current date for my DateTime parameters, Nothing for my Char parameters or 0's for my integer parameters.
When I try testing the sproc alone, by using the "Step Into Stored Procedure" action in Visual Studio, I get a message in the Debug Output window indicating that parameter @TktClassID was expected and not supplied. This is an OUTPUT parameter, which makes me question why I should be providing any sort of value for it within my VB code. Following are the function definition from my SQLDataAdapter class that calls my sproc, and the sproc itself. I appreciate any help that anyone can provide.
**FUNCTION DEFINITION FROM SQLDataAdapter Class
Public Function Fetch(ByVal ticket As Ticket) As Ticket
Dim connbuilder As New System.Data.SqlClient.SqlConnectionStringBuilder
connbuilder("Data Source") = "ITS-KCGV7VZSQLEXPRESS"
connbuilder("Integrated Security") = "True"
connbuilder("Initial Catalog") = "ITSHelpDesk"
Using conn As New System.Data.SqlClient.SqlConnection(connbuilder.ConnectionString)
Using comm As New System.Data.SqlClient.SqlCommand("dbo.TicketFetch", conn)
conn.Open()
comm.CommandType = CommandType.StoredProcedure
Dim parm As System.Data.SqlClient.SqlParameter
'Add Input parameters (i.e. Key values)
'Add @TicketYear parameter
parm = comm.Parameters.Add("@TicketYear", SqlDbType.SmallInt)
parm.Value = DBNull.Value
parm.Direction = ParameterDirection.Input
'Add @TicketID parameter
parm = comm.Parameters.Add("@TicketID", SqlDbType.Int)
parm.Value = DBNull.Value
parm.Direction = ParameterDirection.Input
'Add Output parameters
'Add @TktClassID parameter
parm = comm.Parameters.Add("@TktClassID", SqlDbType.SmallInt)
parm.Direction = ParameterDirection.Output
parm.SourceColumn = ticket.TktClassID
'Add @TktRequestTypeID parameter
parm = comm.Parameters.Add("@TktRequestTypeID", SqlDbType.SmallInt)
parm.Direction = ParameterDirection.Output
parm.SourceColumn = ticket.TktRequestTypeID
'Add @DateOpened parameter
parm = comm.Parameters.Add("@DateOpened", SqlDbType.DateTime)
parm.Direction = ParameterDirection.Output
parm.SourceColumn = ticket.DateOpened
'Add @DateClosed parameter
parm = comm.Parameters.Add("@DateClosed", SqlDbType.DateTime)
parm.Direction = ParameterDirection.Output
parm.SourceColumn = ticket.DateClosed
'Add @DateLastAssigned parameter
parm = comm.Parameters.Add("@DateLastAssigned", SqlDbType.DateTime)
parm.Direction = ParameterDirection.Output
parm.SourceColumn = ticket.DateLastAssigned
'Add @DateLastStatusChange parameter
parm = comm.Parameters.Add("@DateLastStatusChange", SqlDbType.DateTime)
parm.Direction = ParameterDirection.Output
parm.SourceColumn = ticket.DateLastStatusChange
'Add @TktStatus parameter
parm = comm.Parameters.Add("@TktStatusID", SqlDbType.SmallInt)
parm.Direction = ParameterDirection.Output
parm.SourceColumn = ticket.TktStatusID
'Add @DescrRequest parameter
parm = comm.Parameters.Add("@DescrRequest", SqlDbType.VarChar)
parm.Direction = ParameterDirection.Output
parm.SourceColumn = ticket.DescrRequest
'Add @DescrResolution parameter
parm = comm.Parameters.Add("@DescrResolution", SqlDbType.VarChar)
parm.Direction = ParameterDirection.Output
parm.SourceColumn = ticket.DescrResolution
parm.Value = " " 'Handle bug?
'Add @OpenStatus parameter
parm = comm.Parameters.Add("@OpenStatus", SqlDbType.Bit)
parm.Direction = ParameterDirection.Output
parm.SourceColumn = ticket.OpenStatus
'Add @UserLastUpdate parameter
parm = comm.Parameters.Add("@UserLastUpdate", SqlDbType.Char)
parm.Direction = ParameterDirection.Output
parm.SourceColumn = ticket.UserLastUpdate
'Add @DateLastUpdate parameter
parm = comm.Parameters.Add("@DateLastUpdate", SqlDbType.DateTime)
parm.Direction = ParameterDirection.Output
parm.SourceColumn = ticket.DateLastUpdate
comm.ExecuteNonQuery()
End Using
End Using
Return ticket
End Function
**STORED PROCEDURE DEFINITION
USE [ITSHelpDesk]
GO
/****** Object: StoredProcedure [dbo].[TicketFetch] Script Date: 03/24/2008 08:40:53 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: Tim Peters
-- Create date: 3/17/2008
-- Description: Fetch Ticket from Ticket table
-- =============================================
ALTER PROCEDURE [dbo].[TicketFetch]
-- Add the parameters for the stored procedure here
@TicketYear smallint = 0,
@TicketID int = 0,
@TktClassID smallint = NULL OUTPUT,
@TktRequestTypeID smallint = NULL OUTPUT,
@DateOpened datetime = NULL OUTPUT,
@DateClosed datetime = NULL OUTPUT,
@DateLastAssigned datetime = NULL OUTPUT,
@DateLastStatusChange datetime = NULL OUTPUT,
@TktStatusID smallint = NULL OUTPUT,
@DescrRequest varchar(500) = NULL OUTPUT,
@DescrResolution varchar(500) = NULL OUTPUT,
@OpenStatus bit = NULL OUTPUT,
@UserLastUpdate char(10) = NULL OUTPUT,
@DateLastUpdate datetime = NULL OUTPUT
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
@TktClassID = [TktClassID],
@TktRequestTypeID = [TktRequestTypeID],
@DateOpened = [DateOpened],
@DateClosed = [DateClosed],
@DateLastAssigned = [DateLastAssigned],
@DateLastStatusChange = [DateLastStatusChange],
@TktStatusID = [TktStatusID],
@DescrRequest = [DescrRequest],
@DescrResolution = [DescrResolution],
@OpenStatus = [OpenStatus],
@UserLastUpdate = [UserLastUpdate],
@DateLastUpdate = [DateLastUpdate]
FROM [dbo].[Ticket]
WHERE [TicketYear] = @TicketYear AND [TicketID] = @TicketID
END
RETURN
Visual Basic Slow Down Sql Server 2000 Running A Stored Procedure
Nov 30, 2007I hav the following problem. I have written an stored procedure in sql server 2000 as the following
CREATE PROCEDURE dbo.pa_rellena
@pFechaInicio datetime
AS
declare @pFechaFin datetime
declare @auxcod_cen char(10)
declare @importeEfectivo decimal(17,2)
declare @importeTarjetas1 decimal(17,2)
declare @importeTarjetas2 decimal(17,2)
declare @importeVales decimal(17,2)
declare @importeTalones decimal(17,2)
declare @importeGastos decimal(17,2)
select @pFechaFin=@pFechaInicio+1
--Borramos las tablas temporales si las hemos creado con anterioridad y no se han borrado
if object_id('tmpCentros') is not null
drop table tmpCentros
if object_id('tmpCentros2') is not null
drop table tmpCentros2
if object_id('tmpMaxCajas') is not null
drop table tmpMaxCajas
if object_id('tmpCajasCentro') is not null
drop table tmpCajasCentro
if object_id('tmpVales') is not null
drop table tmpVales
if object_id('tmpDiarioEfectivo') is not null
drop table tmpDiarioEfectivo
if object_id('tmpDiarioTalones') is not null
drop table tmpDiarioTalones
if object_id('tmpDiarioTarjetas') is not null
drop table tmpDiarioTarjetas
if object_id('tmpDiarioSegundaForma') is not null
drop table tmpDiarioSegundaForma
if object_id('tmpDiarioGastosTarjetas') is not null
drop table tmpDiarioGastosTarjetas
if object_id('temp1') is not null
drop table temp1
--Seleccionamos todos los centros de Salvador Bachiller
select * into tmpCentros2
from centros
where centros.tienda=1
order by cod_cen
--Seleccionamos el maximo de cajas por cada centro
select cod_cen, max(cod_caja) as cajas into tmpMaxCajas
from cierrecaja
where fecha>=@pFechaInicio and fecha<@pFechaFin
group by cod_cen
order by cod_cen
--Mezclamos los centros con el maximo de cajas
select c.cod_cen, c.Centro, c.Direccion, c.localidad, c.provincia, c.cpostal, c.telefono, m.cajas, operaciones, cajas_tot, tienda, franquicia into tmpCentros
from tmpCentros2 as c left outer join tmpMaxCajas as m on c.cod_cen=m.cod_cen
--Cajas por centro
select distinct cod_cen as cod_cen, cod_caja as cod_caja into tmpCajasCentro
from cierrecaja
where fecha>=@pFechaInicio and fecha<@pFechaFin
--Los vales de cada centro
select cod_cen,sum(importe) as imp1 into tmpVales
from vales where
fecha>=@pFechaInicio and fecha<@pFechaFin
group by cod_cen
--Efectivo de cada centro
select cod_cen,'01' as vendedor,'EFECTIVO' as descripcion, (sum(diario.TotEuro)-Sum(Diario.Imppa2)) as importe1,0 as exp1, (sum(Diario.TotEuro)-sum(Diario.imppa2)) as importe2 into tmpDiarioEfectivo
from diario
where fecha>=@pFechaInicio and fecha<@pFechaFin and cod_cen in (select cod_cen from tmpCentros) and cod_caja in (select cod_caja from tmpCajasCentro) and diario.cod_pago='01'
group by cod_cen
--Talones por centro
select centros.cod_cen,'02' as vendedor,'TALONES' as descripcion, sum(diario.TotEuro) as importe1,0 as exp1, sum(Diario.TotEuro) as importe2 into tmpDiarioTalones
from centros inner join diario on centros.cod_cen=diario.cod_cen
where fecha>=@pFechaInicio and fecha<@pFechaFin and diario.cod_cen in (select cod_cen from tmpCentros) and cod_caja in (select cod_caja from tmpCajasCentro) and diario.cod_pago='02'
group by centros.cod_cen
--Tarjetas por centro
select cod_cen,'03' as vendedor,'TARJETAS' as descripcion, sum(diario.TotEuro) as importe1,0 as exp1, sum(Diario.TotEuro*(FPago.Descuento/100)) as importe2, sum(Diario.TotEuro) - sum(Diario.TotEuro*(FPago.Descuento/100)) as importe3 into tmpDiarioTarjetas
from FPago left join Diario on fpago.Cod_pago=Diario.cod_pago
where fecha>=@pFechaInicio and fecha<@pFechaFin and cod_cen in (select cod_cen from tmpCentros) and cod_caja in (select cod_caja from tmpCajasCentro) and Fpago.Descuento<>0
group by cod_cen
--Segunda Froma de Pago
select cod_cen,'03' as vendedor,'TARJETAS' as descripcion,sum(diario.imppa2) as importe1 into tmpDiarioSegundaForma
from fpago left join Diario on Fpago.cod_pago=diario.cod_pa1
where fPago.cod_pago<>'99' and fecha>=@pfechaInicio and fecha<@pFechaFin and cod_cen in (select cod_cen from tmpCentros) and cod_caja in (select cod_caja from tmpCajasCentro) and Fpago.Descuento<>0
group by cod_cen
--Comisiones tarjetas de pago
select cod_cen,'10' as vendedor, 'GASTOS (-)' as descripcion, sum(Diario.imppa2*(fPago.Descuento/100)) as importe2 into tmpDiarioGastosTarjetas
from Fpago left join Diario on FPago.cod_pago= Diario.cod_pa1
where fPago.cod_pago<>'99' and fecha>=@pFechaInicio and fecha<@pFechaFin and cod_cen in (select cod_cen from tmpCentros) and cod_caja in (select cod_caja from tmpCajasCentro) and Fpago.Descuento<>0
group by cod_cen
/*
--Venta neta por centro
declare cursortemporal cursor for select cod_cen from TmpCentros2
open cursortemporal
delete detallecaja_aux
fetch next from cursortemporal into @auxcod_cen
while @@fetch_status=0
Begin
select @importeVales=imp1 from tmpVales where cod_cen=@auxcod_Cen
select @importeEfectivo=importe2 from tmpDiarioEfectivo where cod_cen=@auxcod_Cen
select @importeTalones=importe2 from tmpDiarioTalones where cod_cen=@auxcod_cen
select @importeTarjetas1=importe3 from tmpDiarioTarjetas where cod_cen=@auxcod_cen
select @importeTarjetas2=importe1 from tmpDiarioSegundaForma where cod_cen=@auxcod_cen
select @importeGastos=importe2 from tmpDiarioGastosTarjetas where cod_cen=@auxcod_cen
select @importeVales=isnull(@importeVales,0)
select @importeEfectivo=isnull(@importeEfectivo,0)
select @importeTalones=isnull(@importeTalones,0)
select @importeTarjetas1=isnull(@importeTarjetas1,0)
select @importeTarjetas2=isnull(@importeTarjetas2,0)
select @importeGastos=isnull(@importeGastos,0)
print @auxcod_cen
print @importeVales
print @importeEfectivo
print @importeTalones
print @importeTarjetas1
print @importeTarjetas2
print @importeGastos
insert into detallecaja_aux (cod_cen,importe1)
values(@auxcod_cen, @importeVales+@importeEfectivo+@ImporteTalones+@ImporteTarjetas1+@importeTarjetas2-@importeGastos)
fetch next from cursortemporal into @auxcod_cen
select @importeVales=0
select @importeEfectivo=0
select @importeTalones=0
select @importeTarjetas1=0
select @importeTarjetas2=0
select @importeGastos=0
end
close cursortemporal
*/
select * from detallecaja_aux
GO
When I try to run it from visual basic it slow down the sql server.
What can I do?
Extended Procs Using C#
Oct 13, 2006What is the procedure to create a extended stored procedure using C#.
Actually we have created a dll file using c# and it was added to the bin folder of mssql server.
Also dll file is added to the sql server using "xp_extendedproc"
But while executing the extended proc we got this error
"ODBC: Msg 0, Level 16, State 1
Cannot load the DLL xp_TestSql.dll, or one of the DLLs it references. Reason: 126(The specified module could not be found.)"
Only Functions And Extended Stored Procedures Can Be Executed From Within A Function. Sp_executesql Is A Extended Stored Prod
May 15, 2008i have created the folowing function but keep geting an error.
Only functions and extended stored procedures can be executed from within a function.
Why am i getting this error!
Create Function myDateAdd
(@buildd nvarchar(4), @avdate as nvarchar(25))
Returns nvarchar(25)
as
Begin
declare @ret nvarchar(25)
declare @sqlval as nvarchar(3000)
set @sqlval = 'select ''@ret'' = max(realday) from (
select top '+ @buildd +' realday from v_caltable where realday >= '''+ @avdate +''' and prod = 1 )a'
execute sp_executesql @sqlval
return @ret
end
Query That Select Extended Procs
Jul 23, 2005HiIf I register my own proc via execsp_addextendedproc 'xp_myproc', 'xp_mylib.dll'.How then I can look all my own procs and dll's?In which tables are this data saved?Thx.
View 1 Replies View RelatedT-SQL And Visual Basic 2005 Codes That Execute A User-Defined Stored Procedure In Management Studio:How To Declare EXEC && Sp?
Jan 17, 2008Hi all,
In my SQL Server Management Studio Express (SSMSE), I executed the following sql code suuccessfully:
--insertNewRocord.sql--
USE shcDB
GO
CREATE PROC sp_insertNewRecord @procPersonID int,
@procFirstName nvarchar(20),
@procLastName nvarchar(20),
@procAddress nvarchar(50),
@procCity nvarchar(20),
@procState nvarchar(20),
@procZipCode nvarchar(20),
@procEmail nvarchar(50)
AS INSERT INTO MyFriends
VALUES (@procPersonID, @procFirstName, @procLastName, @procAddress,
@procCity, @procState, @procZipCode, @procEmail)
GO
EXEC sp_insertNewRecord 7, 'Peter', 'Wang', '678 Old St', 'Detroit',
'Michigon', '67899', 'PeterWang@yahoo.com'
GO
=======================================================================
Now, I want to insert a new record into the dbo.Friends table of my shcDB by executing the following T-SQL and Visual Basic 2005 codes that are programmed in a VB2005 Express project "CallshcDBspWithAdoNet":
--Form1.vb--
Imports System.Data
Imports System.Data.SqlClient
Imports System.Data.SqlTypes
Public Class Form1
Public Sub InsertNewFriend()
Dim connectionString As String = "Integrated Security-SSPI;Persist Security Info=False;" + _
"Initial Catalog=shcDB;Data Source=.SQLEXPRESS"
Dim connection As SqlConnection = New SqlConnection(connectionString)
connection.Open()
Try
Dim command As SqlCommand = New SqlCommand("sp_InsertNewRecord", connection)
command.CommandType = CommandType.StoredProcedure
EXEC sp_insertNewRecord 6, 'Craig', 'Utley', '5577 Baltimore Ave',
'Ellicott City', 'MD', '21045', 'CraigUtley@yahoo.com'
Console.WriteLine("Row inserted: " + _
command.ExecuteNonQuery().ToString)
Catch ex As Exception
Console.WriteLine(ex.Message)
Throw
Finally
connection.Close()
End Try
End Sub
End Class
===========================================================
I ran the above project in VB 2005 Express and I got the following 5 errors:
1. Name 'EXEC' is not declared (in Line 16 of Form1.vb)
2. Method arguments must be enclosed in parentheses (in Line 16 of Form1.vb)
3. Name 'sd-insertNewRecord' is not declared. (in Line 16 of Form1.vb)
4.Comma, ')', or a valid expression continuation expected (in Line 16 of Form1.vb)
5. Expression expected (in Line 16 of Form1.vb)
============================================================
I know that "EXEC sp_insertNewRecord 6, 'Craig', 'Utley', '5577 Baltimore Ave',
'Ellicott City', 'MD', '21045', 'CraigUtley@yahoo.com' "in Line 16 of Form1.vb is grossly in error.
But I am new in doing the programming of T-SQL in VB 2005 Express and I am not able to change it.
Please help and advise me how to correct these problems.
Thanks in advance,
Scott Chang
T-SQL And Visual Basic 2005 Codes That Execute A User-Defined Stored Procedure In Management Studio Express (Part 2)
Jan 23, 2008Hi Jonathan Kehayias, Thanks for your valuable response.
I had a hard time to sumbit my reply in that original thread yesterday. So I created this new thread.
Here is my response to the last code/instruction you gave me:
I corrected a small mistake (on Integrated Security-SSPI and executed the last code you gave me.
I got the following debug error message:
1) A Box appeared and said: String or binary data would be truncated.
The statement has been terminated.
|OK|
2) After I clicked on the |OK| button, the following message appeared:
This "SqlException was unhandled
String or binary data would be truncated.
The statement has been terminated."
is pointing to the "Throw" code statement in the middle of
.......................................
Catch ex As Exception
MessageBox.Show(ex.Message)
Throw
Finally
..........
Please help and advise how to correct this problem in my project that is executed in my VB 2005 Express-SQL Server Management Studio Express PC.
Thanks,
Scott Chang
The code of my Form1.vb is listed below:
Imports System.Data
Imports System.Data.SqlClient
Imports System.Data.SqlTypes
Public Class Form1
Public Sub InsertNewFriend()
Dim connectionString As String = "Data Source=.SQLEXPRESS;Initial Catalog=shcDB;Integrated Security=SSPI;"
Dim connection As SqlConnection = New SqlConnection(connectionString)
Try
connection.Open()
Dim command As SqlCommand = New SqlCommand("sp_insertNewRecord", connection)
command.CommandType = CommandType.StoredProcedure
command.Parameters.Add("@procPersonID", SqlDbType.Int).Value = 7
command.Parameters.Add("@procFirstName", SqlDbType.NVarChar).Value = "Craig"
command.Parameters.Add("@procLastName", SqlDbType.NVarChar).Value = "Utley"
command.Parameters.Add("@procAddress", SqlDbType.NVarChar).Value = "5577 Baltimore Ave"
command.Parameters.Add("@procCity", SqlDbType.NVarChar).Value = "Ellicott City"
command.Parameters.Add("@procState", SqlDbType.NVarChar).Value = "MD"
command.Parameters.Add("@procZipCode", SqlDbType.NVarChar).Value = "21045"
command.Parameters.Add("@procEmail", SqlDbType.NVarChar).Value = "CraigUtley@yahoo.com"
Dim resulting As String = command.ExecuteNonQuery
MessageBox.Show("Row inserted: " + resulting)
Catch ex As Exception
MessageBox.Show(ex.Message)
Throw
Finally
connection.Close()
End Try
End Sub
Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
InsertNewFriend()
End Sub
End Class
Stored Procs: Is There A Way To Divide Up (or Namespace) Groups Of Stored Procs
Jan 15, 2008Is there a way to namespace groups of stored procs to reduce confusion when using them?
For C# you can have ProjectName.ProjectSection.Classname when naming a class. I am just wondering if there is a way to do the same with SQL stored procs. I know Oracle has packages and the name of the package provides a namespace for all of the stored procs inside it.
Extended Properties Created By Visual Studio On CLR UDF's
Aug 23, 2007
When one deploys a CLR DLL that contains [SqlFunction]'s, Visual Studio creates three extended properties on the SQL function that is created: these are,
AutoDeployed, SqlAssemblyFile, SqlAssemblyFileLine.
What I haven't found anywhere on Help or MSDN online is what these extended properties mean.
Could someone enlighten me?
1. What is a description of the above properties?
2. What is the benefit of using them (clearly the function works without using them). Does it help debugging?
3. Does the developer need to manually keep for instance the SqlAssemblyFileLine in sync with changes in the .NET code?
Thanks a lot.
DTS And Visual Basic
May 15, 2001I have already created package which loads a text file to database using the dts wizard in Enterprise Manager.How do I execute that package using visual basic?Please provide the Code!!!Thanks
View 2 Replies View RelatedVisual Basic
Jan 19, 1999I need help.
160821A network error was encountered while sending results to the front end. Check the SQL Server errorlog for more information.
I need help.
Our SQL Server is crashing. The Database is still recovering. I can not kill any Process when the Server crashed. I shoot down and restarted the server. Nothing to do. I can not access to the database. It is recovering. How long? I do not know. What can be the reason of the recovering? Nor the event log of Windows NT or the log files of the SQL SERVER can help me.
Here is a part of the log file
23216Arithmetic overflow error for type %s, value = %f.
10915There are more columns in the INSERT statement than values specified in the VALUES clause. The number of values in the VALUES clause must match the number of columns specified in the INSERT statement.
99/01/19 01:14:25.69 spid25 bufwait: timeout, BUF_IO, bp 0x1bba600, pg 0x11b50, stat 0x801000/0x6, obj 0x23494814, bpss 0x124a2a0
99/01/19 01:14:27.15 ods Error : 17824, Severity: 10, State: 0
99/01/19 01:14:27.15 ods Unable to write to ListenOn connection '.pipesqlquery', loginname 'sa', hostname 'myserver'.
99/01/19 01:14:27.15 ods OS Error : 232, The pipe is being closed.
Visual Basic .NET
Oct 22, 2004Anyone know any good Visual Basic .NET STep By Step books for an almost beginner.
View 2 Replies View RelatedVisual Basic
Feb 27, 2008What code can I write so that the output is in US dollars?
View 3 Replies View RelatedVisual Basic 6.0
Mar 22, 2007Greetings,
Firstly, apologies if this topic is out of the scope of this board, but I am stumped.
Basically, we have an old Visual Basic 6.0 application which currently accesses an Access database, but which we would like to upgrade to an SQL compact edition database. I have created the database, but have so far turned up next to no details reagarding how to connect to the damn thing in VB6.
Can anyone help? Or at least point me in the right direction?
Many thanks,
MadSkunk
Visual Basic, OLE DB Provider And DTS
Oct 25, 1999I want to use Visual Basic to create DTS connection. In the example below a connection is created using an ODBC string. However, I would like to make the Connection's DataSource be an OLE-DB provider instead of ODBC. However, I cannot find any information on the syntax for using an OLE-DB provider as the DataSource.
Your help is appreciated.
Les
strODBCdsn = "server1" 'Create a DSN called SQL7 or change name
strUserName = "sa" 'User name assume same source and dest
strPassword = "" 'with the same Password
Set oConnection = oPackage.Connections.New
oConnection.ID = 1
oConnection.DataSource = strODBCdsn
oConnection.UserID = strUserName
oConnection.Password = strPassword
oPackage.Connections.Add oConnection
Run DTS Package From Visual Basic 6.3
Jan 6, 2005Anyone have any code i can use to call a DTS package from Visual Basic. I would like to have an outlook macro that will run a dts package when i receive an email. All i need is the code that will call a dts package for a sql server that is not local to the box. I've tried this code but i get an error
run_tmie error '-2147217900 (80040e14)' automation error
Public Sub main()
Dim oPkg As DTS.Package2
Set oPkg = New DTS.Package2
oPkg.LoadFromSQLServer MyServerName, MyUserName, MyPassword, , MyDTSPackageName
oPkg.Execute
oPkg.UnInitialize
Set oPkg = Nothing
End Sub
Thanks in advance.
Visual Basic And Sql Server
Jan 3, 2004Hi experts I am new to SQL Server and have got to do a project using VB & SQL Server. right now i got to prepare case study document wherein i got to mention the pros & cons of using VB+sql server... kindly help me. Thanks in advance.
View 1 Replies View RelatedWhich Better For Visual Basic Developer
Jun 19, 2006hi
i found these books for learnning sql server
which better to read for Visual Basic Developer ??
http://www.sqlserver2005books.com/1-3805-0735622507-%20Microsoft_SQL_ServerTM_2005_Reporting_Services_Step_by_Step_Step_by_Step_Microsoft.html
------------------------
http://www.sqlserver2005books.com/1-3805-0735622078-%20Microsoft_SQL_ServerTM_2005_Database_Essentials_Step_by_Step_Step_By_Step_Microsoft.html
thank you
SQL CE Trouble In Visual Basic 6
Dec 14, 2006I use SQL Server 2005 Compact Edition RC1 with Visual Basic 6.0. connection provider that i use is Microsoft.SQLSERVER.MOBILE.OLEDB.3.0. for database management i use SQL Server Management Studio. some query can't execute in visual basic but in sql management studio, that query can run very well. for example:
1. select KodeSatker, KodeSatker + ' - ' + Nama as Nama from TMSatker where len(kodesatker)=3 order by KodeSatker
2. Select a.kdprog, a.nama, b.kodesatker + ' - ' + b.nama as SKPD, a.nonurusan from TMProgram a left join TMSatker b on substring(a.kdprog, 1, len(a.kdprog) - 2) = b.kodesatker
2 query above can run in sql management studio, but in visual basic 6.0, that query have an error.
error msg:run time error '2147217887 (80040e21)':
multiple-step operation generated errors. check each status value
how to solve that problem?
thanks before.
Problem With Using Stored Procs As I/p To Another Stored Procs
May 7, 2004HI,
CREATE PROCEDURE PROC1
AS
BEGIN
SELECT A.INTCUSTOMERID,A.CHREMAIL,B.INTPREFERENCEID,C.CHR PREFERENCEDESC
FROM CUSTOMER A
INNER JOIN CUSTOMERPREFERENCE B
ON A.INTCUSTOMERID = B.INTCUSTOMERID
INNER JOIN TMPREFERENCE C
ON B.INTPREFERENCEID = C.INTPREFERENCEID
WHERE B.INTPREFERENCEID IN (6,7,2,3,12,10)
ORDER BY B.INTCUSTOMERID
END
IF I AM USING THIS PROC AS I/P TO ANOTHER PROC THEN IT GIVES NO PROBLEM AS I CAN USE ?
CREATE PROCEDURE PROC2
AS
BEGIN
CREATE TABLE #SAATHI(INTCUSTOMERID INT,CHREMAIL NVARCHAR(60),INTPREFERENCEID INT,CHRPREFERENCEDESC NVARCHAR(50))
INSERT INTO #SAATHI
EXEC PROC1
ST......1,
ST......2,
END.
BUT IF , I USE ANOTHER PROC SIMILAR TO THE FIRST ONE WHICH GIVES SLIGHTLY DIFFERENT RESULTS AND GIVES TWO SETS OF RESULTS,THEN WE HAVE A PROBLEM,HO TO SOLVE THIS :-
CREATE PROCEDURE MY_PROC
AS
BEGIN
SELECT A.INTCUSTOMERID,A.CHREMAIL,B.INTPREFERENCEID,C.CHR PREFERENCEDESC
FROM CUSTOMER A
INNER JOIN CUSTOMERPREFERENCE B
ON A.INTCUSTOMERID = B.INTCUSTOMERID
INNER JOIN TMPREFERENCE C
ON B.INTPREFERENCEID = C.INTPREFERENCEID
WHERE B.INTPREFERENCEID IN (23,12,10)
ORDER BY B.INTCUSTOMERID
END
SELECT A.INTCUSTOMERID,MAX(case when A.intpreferenceid = 23 then '1'
else '0' end) +
MAX(case when A.intpreferenceid = 12 then '1'
else '0' end) +
MAX(case when A.intpreferenceid = 10 then '1'
else '0' end) AS PREFER
FROM CUSTOMER
GROUP BY A.INTCUSTOMERID
ORDER BY A.INTCUSTOMERID
END
WHICH NOW GIVES ME TWO SETS OF DATA , BOTH REQUIRED THEN HOW TO USE ONE SET OF RESULTS AS I/P TO ANOTHER PROC AND OTHER SET OF RESULTS AS I/P TO YET ANOTHER PROC .
CREATE PROCEDURE PROC2
AS
BEGIN
CREATE TABLE #SAATHI(INTCUSTOMERID INT,CHREMAIL NVARCHAR(60),INTPREFERENCEID INT,CHRPREFERENCEDESC NVARCHAR(50))
INSERT INTO #SAATHI
EXEC MY_PROC
ST......1,
ST......2,
END.
BUT, HERE I WANT TO USE FIRST DATASET ONLY , HOW TO USE IT ?
THANKS.
Basic Sql Querying In Visual Web Developer
Jun 1, 2008I'd like to be able to query a database to get the value of a
certain row. However, I'd like to save this value into a variable
and use them as I'd like, NOT nessessarily display them in a data
control such as a gridview etc. I've been able to find a lot of
information on binding datasourses to predefined data controls, but I
can't find anything on simply querying a database for values and using
those values to display a page as I want.My basic end goal is to create what's basically a shopping site like Amazon only for about 100 or so products.Thanks
in advance for any help. I'm just getting into Visual Web
Developer so please excuse my ignorance and lack of posts.
Visual Basic .NET, SQL2000 && Images
Feb 20, 2003I am creating a program in VB .NET to insert an image in one of the fields in MS SQL2000.
VB variant type DIM'd as "Image", SQL field type is "Image". I cannot get the SQL insert statement to work. I'm trying to get a single insert statement to fill all fields in the row, one of which is image.
error=Operator '&' is not defined for types 'String' and 'System.Drawing.Image'
The '&' symbol is used to concatenate the SQL statements items
Any easy way to store an image?
Visual Basic Connecting To MS SQL Server
Aug 3, 2001is there any way to use Visual Basic and MSSQL without using ODBC?
please let me know.
Visual Basic Executes DTS Package
Jan 12, 2005I have created a rule in outlook to run this Visual Basic Script when I receive a specific email. The codes runs a SQL Server DTS Package that imports data and runs a couple of stored procs. If the DTS package takes a while to execute my outlook will lock up until the entire package is complete. I want to run the DTS package to run but I don’t want the application to remain locked till its complete. Anyone have any ideas of how to get around this....
Public Sub RunTVDTSPackage(Item As Outlook.MailItem)
Dim oPackage As New DTS.Package
On Error GoTo eh
oPackage.LoadFromSQLServer "DummyServerName", "DummyUser", "DummyPSWd", _
DTSSQLStgFlag_Default, _
"", "", "", "Top_Customer - TV", 0
'Execute the Package
oPackage.Execute
'MsgBox oPackage.Description, vbInformation, _
"Re-import Excel sheet."
'Clean up.
MsgBox ("Ran DTS Package")
Set oPackage = Nothing
Exit Sub
eh:
MsgBox Err.Description, vbCritical, _
"Error Running Package"
End Sub
Collates - CP437 - ADO - Visual Basic
Feb 21, 2005Hello, I am workink with a Database that has the Collate SQL_Latin1_General_CP437_CI_AS (Instance and Database-SQL2000), this convert the Database in a OEM Server.
I Can save Characters From 0 to 256 ASCII code CP 437 very well on the database (I import this Data with an DTS) but when i retrive the information with Visual Basic 6.0 with ADO some character > 127 are changed.
Did you know how I can fix It ?
I know that the problem is that Windows is ANSI and my SQL Server OEM, but .. a dont´t know how yo fix it.
Thanks I sorry because my poor english. :(
Walter
Argentina
How To Create Sql Database In Visual Basic
Jan 11, 2007First i am newbie in vb and Sql server...
Is possible to create and attaching, via vb classic or VBA, a sql
database in this instance:\DVD377-14D9E48CSQLEXPRESS...
I have Sql Express.
Database name: mydatabase
Database Table: mytable
fileds:
fiedl1 text format
fiedl2 text format
fiedl3 text format
fiedl4 date format
fiedl5 number format
fiedl6 text format
Using DTS Object Model In Visual Basic
Apr 17, 2004Hi Everyone,
I would like to know any good reference books or online material about using DTS Object Model in Visual Basic.
Thanks in advance.
With Regards,
kalyan