Re: vb codes migration

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

here is the code that is working under VS2005.
conn.ConnectionString = connstr
conn.Open()
cmd.Connection = conn
With cmd
.CommandText = "{ call update_portal_docs(?, ?, ?, ?, ?, ?, ?) }"
.CommandType = CommandType.Text
.Parameters.Add("@id", OleDb.OleDbType.Integer)
.Parameters.Add("@name", OleDb.OleDbType.VarChar, 250)
.Parameters.Add("@location", OleDb.OleDbType.VarChar, 1024)
.Parameters.Add("@folder", OleDb.OleDbType.VarChar, 1024)
.Parameters.Add("@mod_date", OleDb.OleDbType.DBDate)
.Parameters.Add("@found_date", OleDb.OleDbType.DBDate)
.Parameters.Add("@valid_doc", OleDb.OleDbType.VarChar, 5)


.Parameters(0).Value = 0
.Parameters(1).Value = System.DBNull.Value
.Parameters(2).Value = System.DBNull.Value
.Parameters(3).Value = System.DBNull.Value
.Parameters(4).Value = Now
.Parameters(5).Value = Now
.Parameters(6).Value = "True"
.ExecuteNonQuery()
End With

However, if I took the parameters.add out, the code is not working. The
codes in VB6 and VS2003 use .COM odbc object to call the above stored
procedure. The VS2005 code uses the .NET ODBC object to call the above stored
proc.

Is there a way to take the parameters.add codes out and make it to work (the
same way VB6 and VS2003 code does)?

Thanks.

"Erland Sommarskog" wrote:

d052057 (d052057@xxxxxxxxxxxxxxxxxxxxxxxxx) writes:
I am have a piece of code that were written in VB6 and then it has been
migrated to VS2003. Now, I am going to rewrite it with VS2005. I am
running to the problem regarding old codes (VB6 & vs2003) vs the codes
in VS2005.

While I think I am able to answer your question, I would like to point
out that this newsgroup is about the native OLE DB API. For questions on
ADO .Net, the best venue is microsoft.public.dotnet.framework.adonet.

and here is the new codes written in VS2005.
...
.CommandText = "{ call erpappuser.update_portal_docs(?, ?, ?, ?,
?, ?, ?)}"
.CommandType = CommandType.Text
.Parameters(0).Value = "0"
.Parameters(1).Value = System.DBNull.Value
.Parameters(2).Value = System.DBNull.Value
.Parameters(3).Value = System.DBNull.Value
.Parameters(4).Value = Now
.Parameters(5).Value = Now
.Parameters(6).Value = "True"
.ExecuteScalar()
End With

==================================================
There are several ways to deals with this but I choose this type of
calling stored procedure since it is very simple and short and I don't
need to create parameters in the code.

Any reason why this piece of is not working?

I appreciate your sense of humour, but next you have something that does
not work, please specify what "is not working" means. That is, do you
get an error message? Do you get an unexpected results? Does heaven fall
down on you? Something else?

To call a stored procedure from SqlClient you should use
CommandType.StoredProcedure. The ODBC syntax you use above, is not
understood by SqlClient. You should also create the parameters explicitly
with their data types.

Also, it looks funny that you call ExecuteScalar without bothering about
the return value.


--
Erland Sommarskog, SQL Server MVP, esquel@xxxxxxxxxxxxx

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

.

Relevant Pages

  • Re: CommandType = TEXT vs Stored Procedure.
    ... CommandType = Stored Procedure ... would this not get rid of the performance gains ... The SQL text will be passed to SQL Server, which will execute it on the ...
    (microsoft.public.dotnet.languages.vb)
  • Re: fmtonly problem (bug ??)
    ... not-so-short stored proc I would not want to run the full query just to ... a stored procedure could be written to generate different result sets ... Erland Sommarskog, SQL Server MVP, esquel@xxxxxxxxxxxxx ... Books Online for SQL Server 2005 at ...
    (comp.databases.ms-sqlserver)
  • Re: bcp utility
    ... ideal would be to loop through records in a stored procedure to create ... I know how to run it from the command prompt. ... Erland Sommarskog, SQL Server MVP, esquel@xxxxxxxxxxxxx ... Books Online for SQL Server 2005 at ...
    (comp.databases.ms-sqlserver)
  • Re: How to FAST and EASY ENCRYPTION ALL Stored Procedure in my MS-SQL Database ?
    ... How to FAST and EASY ENCRYPTION ALL Stored Procedure in my MS-SQL ... compare merge/tool that permits you easily undo false positives. ... Erland Sommarskog, SQL Server MVP, esquel@xxxxxxxxxxxxx ... Books Online for SQL Server 2005 at ...
    (comp.databases.ms-sqlserver)
  • Re: fmtonly problem (bug ??)
    ... The stored procedure is simple enough but it uses that function to JOIN ... Using the schema, ... Erland Sommarskog, SQL Server MVP, esquel@xxxxxxxxxxxxx ... Books Online for SQL Server 2005 at ...
    (comp.databases.ms-sqlserver)