ADO.NET & SQL Server Stored Procedure

[Guest]

Hi,

Me again. I want to create a SQL Server StoreProcedure and I want to access
it with ADO.NET. I am using VB.NET.

Creating StoreProcedure it was easy but my question is this.

I have table with 4 Column and 6 rows. In the Column 3 for all rows I have
to insert data. The data considt of the decimal value of Croiss Exchange and
6 my variable as:

mUSD = 1.33500
mAUD = 0.91841
mSTG = 2.39300
mJPY = 0.01068
mEuro = 1.63900
mCYP = 2.85700

My Stored Procudure I have to use the CurrncyId as
USD = 1
AUD = 2
STG = 3
JPY = 4
Euro = 5
CYP = 6

Now if I create a Stored Procedure as:

"INSERT INTO tbl_DAILYRATE(xCHANGE) VALUES (@mUSD) WHERE cCHANHEID = USD"

and try to insert data into SQL Server I am getting error that says my
Stored Procedure string has error.

Does anyone know how to Create a Stored procedure to Insert data using some
column as ID Key?

Thank you in advance.

Rgds.
GC

 

[Cor Ligthert [MVP]]

Niyazi,

If you use the SQL, Oracle or OleDBDataadapter in your toolbox, than a
wizard is started.

The most simple is to add as item a component. Drag one of above to your
component and the wizard starts and at the end it to create all your
command. Than you open the generated code in the component and all your code
is there.

Another way, not forever save with complex query string are the
commandbuilders

http://msdn.microsoft.com/library/d...mdatasqlclientsqlcommandbuilderclasstopic.asp

Did you know that there is in fact a better newsgroup for this kind of
questions.

microsoft.public.dotnet.framework.adonet

I hope this helps,

Cor

 

[Guest]

Hi Cor,

Sorry I didn't realize that I was in wrong group.
I find the answere as follows:

Here is my SQL Server Stored Procedure that updates 1 column using WHERE
clause

CREATE PROCEDURE UPDATE_DAILYRATE
@mNO int,
@mCUR nvarchar (7)
AS
Update tblRATE SET mCurrency = @mCUR WHERE mCurrencyType = @mNO
GO

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++


And here is my VB.NET code that calls the above Stored Procedure and insert
the value into specific column and row


++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++


Try

Dim connectionSQLSERVER As New SqlConnection(strConnSQLSERVER)
cn = connectionSQLSERVER


'INSERT USD CROSS EXCHANGE
Dim cmdUSD As New SqlCommand("UPDATE_DAILYRATE", cn)
cmdUSD.CommandType = CommandType.StoredProcedure

Dim ParamColl As SqlParameterCollection = cmdUSD.Parameters

Dim mUSDNO As Integer = 1
Dim mNO As Integer
mNO = mUSDNO
Dim paramDOVIZCINSI As SqlParameter = ParamColl.Add("@mNO", mNO)
paramDOVIZCINSI.Direction = ParameterDirection.Input

Dim mCUR As Decimal = mUSD
Dim paramDOVIZKURU As SqlParameter = ParamColl.Add("@mCUR", mCUR)
paramDOVIZKURU.Direction = ParameterDirection.Input

cn.Open()
cmdUSD.ExecuteNonQuery()

'Clear the variable
ParamColl.Clear()
mNO = 0
mCUR = 0
paramDOVIZCINSI = Nothing
paramDOVIZKURU = Nothing
cn.Close()

Catch ex As Exception
msgErrorUSD = ex.Message.ToString
End Try


I hope this helps others as well.

Again thank you very much your kind input.

Rgds,
GC

 

[Cor Ligthert [MVP]]

Niyazi,

You are *not* in the wrong group (you never can be in my opinon in one as
long as you don't know the other ones), however there are in my opinon
betters and therefore there is not any reason to say "Sorry" for that.

I am glad that you did succeed.

Cor