|
Glad that it works now. Thanks for sharing that piece of information 
Give a man a fish, he'll eat for a day. Teach a man how to fish, he'll eat for lifetime.
Pradeep Joe
|
|
|
|
|
Parameters are passed to Stored Procedure from VB.NET Application like
Dim param As SqlParameter
param = New SqlParameter("@name",DbType.String)
param.Direction = ParameterDirection.Input
param.Value = "ABC"
comand.Parameters.Add(param)
But is there any way to specify parameters without specifying the
parameter name and type like
Dim param As SqlParameter
param = New SqlParameter
param.Direction = ParameterDirection.Input
param.Value = "ABC"
comand.Parameters.Add(param)
But when i try like this i am getting an error saying that
"Parameter1 is not a parameter in the procedure"...
Can anybody tell me how to solve this .
Thanx in advance
|
|
|
|
|
hi dnsl
Number one, The name of your Parameter you are declaring in SQL, should be the same name as your Parameter you Add in your Vb code.
|dnsl Wrote:
But is there any way to specify parameters without specifying the
parameter name and type like..
No you cannot , that code you give, will give you an Exception
Hope it helps
Vuyiswa Maseko,
Sorrow is Better than Laughter, it may Sadden your Face, but It sharpens your Understanding
VB.NET/SQL7/2000/2005
http://vuyiswamb.007ihost.com
http://Ecadre.007ihost.com
vuyiswam@tshwane.gov.za
modified on Friday, February 15, 2008 3:39 AM
|
|
|
|
|
What you are attempting to do is pass values by position. This is generally considered bad practice as a change to the stored proc (ie adding a new parameter but not to the end of the list) could result in values being passed to the wrong column.
You can always do:
command.Parameters.Add(new SQLParameter("@name",value))
This uses the overload for new SQLParameter(name as String, Value as Object)
Better still, write a set of overloaded functions such as:
Public Overloads Sub AddParameter(ByVal parameterName As String, _<br />
ByVal parameterValue As Boolean) <br />
AddParameter(parameterName, parameterValue, System.Data.DbType.Boolean, System.Data.ParameterDirection.Input)<br />
End Sub<br />
Public Overloads Sub AddParameter(ByVal parameterName As String, _<br />
ByVal parameterValue As Int32) <br />
AddParameter(parameterName, parameterValue, System.Data.DbType.Int32, System.Data.ParameterDirection.Input)<br />
End Sub<br />
<br />
Public Overloads Sub AddParameter(ByVal parameterName As String, _<br />
ByVal parameterValue As Object, _<br />
ByVal dbType As System.Data.DbType, _<br />
ByVal direction As System.Data.ParameterDirection)<br />
Try<br />
' Add new parameter to command object - if already exists then simply update the value<br />
If m_cmd.Parameters.Contains(parameterName) Then<br />
m_cmd.Parameters(parameterName).Value = parameterValue<br />
Else<br />
Dim prm As SqlParameter = New SqlParameter<br />
<br />
With prm<br />
.ParameterName = parameterName<br />
.Direction = direction<br />
.DbType = dbType<br />
.Value = parameterValue<br />
End With<br />
m_cmd.Parameters.Add(prm)<br />
End If<br />
<br />
Catch ex As Exception<br />
Throw New ApplicationException(String.Format("Unable to add parameter {0} to command", parameterName), ex)<br />
End Try<br />
End Sub
Bob
Ashfield Consultants Ltd
|
|
|
|
|
When i run this query in my local machine i get an n error --Msg 170, Level 15, State 1, Line 20
Line 20: Incorrect syntax near '('.
USE CustList_PGE_0131
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE CustList_PGE013108(
[ClientCustID] [char](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[CustomerName] [varchar](40) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Address] [varchar](60) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[City] [varchar](30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[State] [char](2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Zip] [char](5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[MailAddress] [varchar](60) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[MailCity] [varchar](30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[MailState] [char](2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[MailZip] [char](5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Type] [char](1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ClientCoCustListID] [int] IDENTITY(1,1) NOT NULL,
[Ucode] [char](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Flag] [char](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Program_ID] [tinyint] NULL CONSTRAINT [DF_CustList_PGE051007_Program_ID] DEFAULT ((1)),
CONSTRAINT PK_CustList_PGE013108 PRIMARY KEY CLUSTERED
(
[ClientCoCustListID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
)
ON [PRIMARY]
GO
SET ANSI_PADDING OFF
But if irun the same query in my server it createsd the table without any problem.
So is there some thing that i am overseeing in my local.
Thanks
|
|
|
|
|
I believe it could be because of compatability issues. Your local box might be running Sql Server 2000 whereas your server might be running Sql Server 2005
Take a look at the Breaking Changes to database engine in Sql Server 2005[^]
On your local box, have the script modified in this fashion
USE CustList_PGE_0131
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE CustList_PGE013108(
[ClientCustID] [char](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[CustomerName] [varchar](40) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Address] [varchar](60) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[City] [varchar](30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[State] [char](2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Zip] [char](5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[MailAddress] [varchar](60) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[MailCity] [varchar](30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[MailState] [char](2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[MailZip] [char](5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Type] [char](1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ClientCoCustListID] [int] IDENTITY(1,1) NOT NULL,
[Ucode] [char](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Flag] [char](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Program_ID] [tinyint] NULL CONSTRAINT [DF_CustList_PGE051007_Program_ID] DEFAULT ((1)),
CONSTRAINT PK_CustList_PGE013108 PRIMARY KEY CLUSTERED
(
[ClientCoCustListID] ASC
)
)
ON [PRIMARY]
GO
Create Unique Index [IndexName] on [dbo].[CustList_PGE013108][(ClientCustID)]
With IGNORE_DUP_KEY on [Primary]
Go
SET ANSI_PADDING OFF
Ref: http://www.codeprof.com/dev-archive/75/19-85-759122.shtm[^]
Give a man a fish, he'll eat for a day. Teach a man how to fish, he'll eat for lifetime.
Pradeep Joe
|
|
|
|
|
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
-- =============================================
-- Author: <author,,jiju>
-- Create date: <create date,14.2.2008,="">
-- Description: <description,stored procedure="" to="" create="" tables,="">
-- =============================================
alter PROCEDURE [dbo].[CreateTable]
-- Add the parameters for the stored procedure here
@databaseName nvarchar(100),
@databaseTable nvarchar(100),
@primaryKey nvarchar(100)
AS
declare @qry nvarchar(1500)
BEGIN
SELECT @qry =
'CREATE TABLE '+@databaseName +'.dbo. '+ @databaseTable +'(
[ClientCustID] [char](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[CustomerName] [varchar](40) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Address] [varchar](60) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[City] [varchar](30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[State] [char](2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Zip] [char](5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[MailAddress] [varchar](60) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[MailCity] [varchar](30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[MailState] [char](2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[MailZip] [char](5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Type] [char](1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ClientCoCustListID] [int] IDENTITY(1,1) NOT NULL,
[Ucode] [char](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Flag] [char](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Program_ID] [tinyint] NULL CONSTRAINT [DF_CustList_PGE051007_Program_ID] DEFAULT ((1)),
CONSTRAINT '+ @primaryKey +' PRIMARY KEY CLUSTERED
(
[ClientCoCustListID] ASC
)
) ON [PRIMARY]
'
print @qry
--To execute the query string
exec SP_Executesql @qry
END
GO
--To execute the stored procedure and create the tables
exec createTable 'CustList_PGE_0131','CustList_PGE013108','PK_CustList_PGE013108'
|
|
|
|
|
Hello,
I am trying to retrieve an image from SQL but the line didPreviouslyConnect = true; is giving an error.HELP!!ME!!
I have the code writen
private void RetrieveImage()
{
try
{
SqlConnection PicCon = new SqlConnection();
SqlDataAdapter da = new SqlDataAdapter("SELECT photo FROM Upload where Account_No = '" + Txt_balance.Text + "," + Txt_Bankcharge.Text + "," + Txt_Refundamount.Text + "," + Txt_TellerID.Text + "," + Txt_Tellername.Text + "'", PicCon);
SqlCommandBuilder cbd = new SqlCommandBuilder(da);
dsPictures = new DataSet();
da.Fill(dsPictures);
didPreviouslyConnect = true;
byte[] arrPicture = (byte[])dsPictures.Tables[0].Rows[0]["photo"];
MemoryStream ms = new MemoryStream(arrPicture);
pictureBox2_Accountholder.Image = Image.FromStream(ms);
pictureBox2_Accountholder.SizeMode = PictureBoxSizeMode.StretchImage;
pictureBox2_Accountholder.BorderStyle = BorderStyle.Fixed3D;
ms.Close();
}
catch (Exception e1)
{
MessageBox.Show(e1.Message.ToString());
}
KIDO KIDI MINI REZA
HERE FIX DI MATURITY
SAMBA RAMBA DIBLO
STYLE ANTIDIZASTA
BLISH MANTIRIOLOJ
|
|
|
|
|
What is the error you are getting?
|
|
|
|
|
Hi,
Can anyone give me a idea to convert or replace the existing date content '1/1/1900' to NULL while insert and update a table.
Thanks,
John
|
|
|
|
|
UPDATE MyTable<br />
SET TheDateColumn = NULL<br />
WHERE TheDateColumn = '1900-01-01'
|
|
|
|
|
Hi all,
I am developing a calendar application in C# that will require to store certain data (like date events, calendar style and some other preferences)in a database.
My preference will be MS SQL 2005 (though could be MySQL, or similar), however I wish to know if I was to use MS SQL 2005, could I include some dlls or other resource in the install of the application, purely for the application to run, without having to install another copy of MS SQL 2005 in the client PC? Like a cut down version of MS SQL 2005 as client site install? Otherwise I may have to go for MySQL or MS Access 2003, to avoid extra cost of the application.
Any ideas an suggestions are welcome.
Thanks in advance.
Alain
|
|
|
|
|
i need to make a copy of a datatable's row (access database).
since the table has quite a lot of columns i don't want to do it column by column:
INSERT INTO myTable(Col1, Col2, Col3) SELECT Col1, Col2, Col3 FROM myTable WHERE ID = 2
but by inserting the whole row like this:
INSERT INTO myTable SELECT * FROM myTable WHERE ID = 2
i think i found an example like that somewhere on the net. but it doesn't work for me. any solution?
"I love deadlines. I like the whooshing sound they make as they fly by." (DNA)
|
|
|
|
|
Smithers-Jones wrote: think i found an example like that somewhere on the net. but it doesn't work for me
Why doesn't it work? What error do you get?
Paul Marfleet
"No, his mind is not for rent
To any God or government"
Tom Sawyer - Rush
|
|
|
|
|
it says
"an unattended exception of the type 'system.data.oledb.oledbexception' occured in system.data.dll"
(hope i translated it correct to english).
i created a module to do all my database-connections, in this module i have among others a sub to which i pass sql-commands as string and that then executes the sql-command.
the error occurs when executing the oledbcommand (myOleDbCommand.ExecuteNonQuerry).
i experienced this error usually only, when there is some error in the sql-command.
that's why i think the sql-command is incorrect or not working with access.
"I love deadlines. I like the whooshing sound they make as they fly by." (DNA)
|
|
|
|
|
Just out of curiosity, how many records can a query return to a calling .net app before weird stuff starts happening? I'm writing code that could potentially return millions of records in a single query.
"Why don't you tie a kerosene-soaked rag around your ankles so the ants won't climb up and eat your candy ass..." - Dale Earnhardt, 1997
-----
"...the staggering layers of obscenity in your statement make it a work of art on so many levels." - Jason Jystad, 10/26/2001
|
|
|
|
|
John Simmons / outlaw programmer wrote: I'm writing code that could potentially return millions of records in a single query.
What are you going to do with these records? If you are presenting them to the user, you should consider using some sort of paging mechanism to limit the number of rows returned from the database.
Paul Marfleet
"No, his mind is not for rent
To any God or government"
Tom Sawyer - Rush
|
|
|
|
|
I'm trying to order a table by a datetime column (ASC) , then delete the top 3 rows. Help out the n00b. Here's what doesn't work..
DELETE TOP(3)
FROM Table
ORDER BY DateTime ASC
DELETE TOP(3)
FROM Table
WHERE (SELECT * FROM Table ORDER BY DateTime ASC)
i've tried hundreds of other combinations.. Thanks guys/gals.
modified on Wednesday, February 13, 2008 1:58 PM
|
|
|
|
|
Nevermind, figured it out... Thanks all!
DELETE FROM TableName
WHERE (DateTimeCol IN
(SELECT TOP (3) DateTimeCol
FROM TableName AS TableName_1
ORDER BY DateTimeCol))
|
|
|
|
|
I am very much a newbie in terms of database design and am in need of some guidance.
I have to store come contact information in one of my database tables and was wondering what would be the best approach to take. Should I split up the address with separate fields for city, state, zip etc, or store it all in one varchar block ?
What would be the better approach to take with respect to maintainability and performance?
Thank you for your time
|
|
|
|
|
phesx wrote: Should I split up the address with separate fields for city, state, zip etc
Yes, that would be a better option. When you need the address as a whole it is easy to get them concatenated. Having the entire address under one varchar column is complicated when you need them individually.
Give a man a fish, he'll eat for a day. Teach a man how to fish, he'll eat for lifetime.
Pradeep Joe
|
|
|
|
|
Hey everybody!
I'm a bit new in unmanaged c++, let alone ADO, so its a bit of a newbie question...
I'm trying to get the signatures of the stored procedures in my database.
Thanks!
|
|
|
|
|
Hi,
This is my stored prosecure.But when i execute
exec CreateDataBases 'testdb','testdb','c:\\testdb.mdf','c:\\testdb.ldf'
It says the db already exists??
Am confused plaease help me...
-- ================================================
-- Template generated from Template Explorer using:
-- Create Procedure (New Menu).SQL
--
-- Use the Specify Values for Template Parameters
-- command (Ctrl-Shift-M) to fill in the parameter
-- values below.
--
-- This block of comments will not be included in
-- the definition of the procedure.
-- ================================================
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <author,,name>
-- Create date: <create date,,="">
-- Description: <description,,>
-- =============================================
Create PROCEDURE CreateDataBases
-- Add the parameters for the stored procedure here
(
@database nvarchar(100),
@databaseName nvarchar(100),
@mdfName nvarchar(100),
@ldfName nvarchar(100)
)
AS
declare @qry nvarchar(800)
BEGIN
select @qry = 'CREATE DATABASE ' + @database +
' ON PRIMARY (NAME= ' + @databaseName +',
FILENAME ='''+ @mdfName + ''',
SIZE = 2MB, MAXSIZE = 10MB, FILEGROWTH = 10%)
LOG ON (NAME ='+@databaseName+',FILENAME='''+ @ldfName+''',SIZE = 1MB,
MAXSIZE = 5MB, FILEGROWTH = 10%)'
--print @qry
exec SP_Executesql @qry
END
GO
exec CreateDataBases 'testdb','testdb','c:\\testdb.mdf','c:\\testdb.ldf'
|
|
|
|
|
It probably means that there is already a "testdb" database on your server.
|
|
|
|
|
That's a pretty wild guess based on such an unclear error message... 
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
