CommandText

Dim MiSQL As String = "INSERT INTO tabla1(ID,Proveedor,Tipo) VALUES (@.IDCentro,@.Proveedor,@.Tipo)"
............
cm.Parameters.Add(New SqlParameter("@.IDCentro", SqlDbType.Int, 4)).Value = 15
cm.Parameters.Add(New SqlParameter("@.Proveedor", SqlDbType.NVarChar, 50)).Value = "IBM"
cm.Parameters.Add(New SqlParameter("@.Tipo", SqlDbType.TinyInt, 1)).Value = 35

Hi friens, its possible to get the string with the vaules of parameters changed?, i mean get this string in code:

INSERT INTO tabla1(ID,Proveedor,Tipo) VALUES (15,IBM,35);

I tried with CommandText but in this string are the variables and not the values...thx a lot.

No.

You can get something similiar if you you SQL Profiler, but it'll look something like:

sp_ExecuteSQL "INSERT INTO tabla1(ID,Proveedor,Tipo) VALUES (@.IDCentro,@.Proveedor,@.Tipo)",@.IDCentruo=N'15',@.Proveedor=N'IBM',@.Tipo=N'35'

Command.Parameters collection populated automatically

Hi,

We have a lot of VB6 code that uses ADO 2.7 and stored procs wih Sql 2005. I have noticed recently that if I use the follow code:

Dim con As New ADODB.Connection
con.ConnectionString = "driver={SQL Server};server=(local);database=test;uid=sa;pwd="
con.Open

Dim com As New ADODB.Command

com.ActiveConnection = con
com.CommandText = "usp_GetSetting"
com.CommandType = adCmdStoredProc

com.Parameters.Append com.CreateParameter(...)

It will fail. the reason being the after setting the CommantText and Type ADO then seems to automatically go away and populate the Parameters collection from the databases metadata according to the SP we are calling.

I have never seen this before, I thought the Refresh method had to be called before the parameters collection get populated.

Can anyone help me please?

Ok,

I think I know what is going on. Is just so happens that my latest debug code contained com.Parameters.Count before the first Parameters.Append statement. I think it is the act of counting the parameters that is causing it to go off and retrieve them all.

Sorry to be an idiot :(

Graham