Showing posts with label converting. Show all posts
Showing posts with label converting. Show all posts

Thursday, March 29, 2012

Help with converting code: VB code in SQL Server 2000->Visual Studio BI 2005

Hi all--I'm trying to convert a function which I inherited from a SQL Server 2000 DTS package to something usable in an SSIS package in SQL Server 2005. Given the original code here:

Function Main()

on error resume next

dim cn, i, rs, sSQL

Set cn = CreateObject("ADODB.Connection")

cn.Open "Provider=sqloledb;Server=<server_name>;Database=<db_name>;User ID=<sysadmin_user>;Password=<password>"

set rs = CreateObject("ADODB.Recordset")

set rs = DTSGlobalVariables("SQLstring").value

for i = 1 to rs.RecordCount

sSQL = rs.Fields(0).value

cn.Execute sSQL, , 128'adExecuteNoRecords option for faster execution

rs.MoveNext

Next

Main = DTSTaskExecResult_Success

End Function

This code was originally programmed in the SQL Server ActiveX Task type in a DTS package designed to take an open-ended number of SQL statements generated by another task as input, then execute each SQL statement sequentially. Upon this code's success, move on to the next step. (Of course, there was no additional documentation with this code. :-)

Based on other postings, I attempted to push this code into a Visual Studio BI 2005 Script Task with the following change:

public Sub Main()

...

Dts.TaskResult = Dts.Results.Success

End Class

I get the following error when I attempt to compile this:

Error 30209: Option Strict On requires all variable declarations to have an 'As' clause.

I am new to Visual Basic, so I'm on a learning curve here. From what I know of this script:
- The variables here violate the new Option Strict On requirement in VS 2005 to declare what type of object your variable is supposed to use.

- I need to explicitly declare each object, unless I turn off the Option Strict On (which didn't seem recommended, based on what I read).

Given this statement:

dim cn, i, rs, sSQL

I'm looking at "i" as type Integer; rs and sSQL are open-ended arrays, but can't quite figure out how to read the code here:

Set cn = CreateObject("ADODB.Connection")

cn.Open "Provider=sqloledb;Server=<server_name>;Database=<db_name>;User ID=<sysadmin_user>;Password=<password>"

set rs = CreateObject("ADODB.Recordset")

This code seems to create an instance of a COM component, then pass provider information and create the recordset being passed in by the previous task, but am not sure whether this syntax is correct for VS 2005 or what data type declaration to make here. Any ideas/help on how to rewrite this code would be greatly appreciated!

Option Strict controls what we call "late binding." If option strict is ON, then what happens is that the compiler will generate code that takes full advantage of the type system in the CLR. This means that each variable has a compile-time type (hence you need to specify the "As" clause), and because each variable has a compile-time type, the IL that the compiler generates is fast (for example, method calls are translated to a "call" or a "callvirt" IL instruction).

If you turn option strict OFF, this is what is known as the "late binding" mode. There is no equivalent in C#. What the late binding mode allows you to do is to leave variables specified as "object" (and if you omit the "As" clause, the type is assumed to be "object"). The compiler, because it does not know what the type is, needs to generate code that calls the Visual Basic Runtime helpers to execute method calls.

Because of this, the compiler cannot provide checks for you during compile time. For example, you can do something like this:

dim o = CreateObject("ADODB.Connection")
o.foo()

The compiler will not check whether the "foo" method exists, because it doesn't know the type of o. And if "foo" indeed does not exist, you will get a runtime exception. Runtime exceptions are much harder to debug and diagnose; this is probably why people advise against turning option strict off.

However, in certain scenarios, such as yours, I believe that turning option strict off will make life easier. The compiler may not be as helpful, but it will make your code easier to understand.

The CreateObject calls are indeed creating COM objects. Your analysis of what the code is doing is correct. The .NET equivalent of the ADODB.Connection and ADODB.Recordset classes live in the "System.Data" namespace. Take a look here for some information: http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpref/html/frlrfSystemData.asp

I hope this helps - feel free to post any more questions or issues that you run into here :)|||

Hi Timothy--Thanks for the analysis, and the suggestion of turning this option off. How do I do that for just this package? I read somewhere (don't remember the URL at the moment) that I had to turn this option off through a configuration parameter in a file ending in .vbproj. However, the ultimate goal is store this package in the SQL Server database and so give it the ability to schedule it through the SQL Server Agent. That doesn't seem feasible if I have to store connection information externally, which is a security risk...and I can't find any files with that name anyway.

I tried to add the following into the function:

Option Strict Off

That caused another compilation error, unless I'm missing something else. Any ideas on how to turn the Option Strict off for just this task?

|||

Better to leave Option Strict On.

Dim i as Integer

Dim cn as ADODB.Connection

Dim rs as ADODB.Recordset

These declarations will require a reference in the Script task project to the ADODB primary interop assembly.

-Doug

|||What was the error you get with Option Strict Off?

The finest level of granularity is to turn option strict off at the file level - put this at the top of the file:
Option Strict Off

If you pursue any solution where you need to turn on/off option strict, it's best to at the very least put the code you want option strict off for in another file.

I agree too that Option Strict should typically be left On, but there are situations where turning it Off can make it easier to code, at the expenses that I mentioned above.|||

Thanks all--These prior posts helped me work around the problem. It's appreciated--problem solved!

- Jonathan

Help with converting code: VB code in SQL Server 2000->Visual Studio BI 2005

Hi all--I'm trying to convert a function which I inherited from a SQL Server 2000 DTS package to something usable in an SSIS package in SQL Server 2005. Given the original code here:

Function Main()

on error resume next

dim cn, i, rs, sSQL

Set cn = CreateObject("ADODB.Connection")

cn.Open "Provider=sqloledb;Server=<server_name>;Database=<db_name>;User ID=<sysadmin_user>;Password=<password>"

set rs = CreateObject("ADODB.Recordset")

set rs = DTSGlobalVariables("SQLstring").value

for i = 1 to rs.RecordCount

sSQL = rs.Fields(0).value

cn.Execute sSQL, , 128'adExecuteNoRecords option for faster execution

rs.MoveNext

Next

Main = DTSTaskExecResult_Success

End Function

This code was originally programmed in the SQL Server ActiveX Task type in a DTS package designed to take an open-ended number of SQL statements generated by another task as input, then execute each SQL statement sequentially. Upon this code's success, move on to the next step. (Of course, there was no additional documentation with this code. :-)

Based on other postings, I attempted to push this code into a Visual Studio BI 2005 Script Task with the following change:

public Sub Main()

...

Dts.TaskResult = Dts.Results.Success

End Class

I get the following error when I attempt to compile this:

Error 30209: Option Strict On requires all variable declarations to have an 'As' clause.

I am new to Visual Basic, so I'm on a learning curve here. From what I know of this script:
- The variables here violate the new Option Strict On requirement in VS 2005 to declare what type of object your variable is supposed to use.

- I need to explicitly declare each object, unless I turn off the Option Strict On (which didn't seem recommended, based on what I read).

Given this statement:

dim cn, i, rs, sSQL

I'm looking at "i" as type Integer; rs and sSQL are open-ended arrays, but can't quite figure out how to read the code here:

Set cn = CreateObject("ADODB.Connection")

cn.Open "Provider=sqloledb;Server=<server_name>;Database=<db_name>;User ID=<sysadmin_user>;Password=<password>"

set rs = CreateObject("ADODB.Recordset")

This code seems to create an instance of a COM component, then pass provider information and create the recordset being passed in by the previous task, but am not sure whether this syntax is correct for VS 2005 or what data type declaration to make here. Any ideas/help on how to rewrite this code would be greatly appreciated!

Option Strict controls what we call "late binding." If option strict is ON, then what happens is that the compiler will generate code that takes full advantage of the type system in the CLR. This means that each variable has a compile-time type (hence you need to specify the "As" clause), and because each variable has a compile-time type, the IL that the compiler generates is fast (for example, method calls are translated to a "call" or a "callvirt" IL instruction).

If you turn option strict OFF, this is what is known as the "late binding" mode. There is no equivalent in C#. What the late binding mode allows you to do is to leave variables specified as "object" (and if you omit the "As" clause, the type is assumed to be "object"). The compiler, because it does not know what the type is, needs to generate code that calls the Visual Basic Runtime helpers to execute method calls.

Because of this, the compiler cannot provide checks for you during compile time. For example, you can do something like this:

dim o = CreateObject("ADODB.Connection")
o.foo()

The compiler will not check whether the "foo" method exists, because it doesn't know the type of o. And if "foo" indeed does not exist, you will get a runtime exception. Runtime exceptions are much harder to debug and diagnose; this is probably why people advise against turning option strict off.

However, in certain scenarios, such as yours, I believe that turning option strict off will make life easier. The compiler may not be as helpful, but it will make your code easier to understand.

The CreateObject calls are indeed creating COM objects. Your analysis of what the code is doing is correct. The .NET equivalent of the ADODB.Connection and ADODB.Recordset classes live in the "System.Data" namespace. Take a look here for some information: http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpref/html/frlrfSystemData.asp

I hope this helps - feel free to post any more questions or issues that you run into here :)|||

Hi Timothy--Thanks for the analysis, and the suggestion of turning this option off. How do I do that for just this package? I read somewhere (don't remember the URL at the moment) that I had to turn this option off through a configuration parameter in a file ending in .vbproj. However, the ultimate goal is store this package in the SQL Server database and so give it the ability to schedule it through the SQL Server Agent. That doesn't seem feasible if I have to store connection information externally, which is a security risk...and I can't find any files with that name anyway.

I tried to add the following into the function:

Option Strict Off

That caused another compilation error, unless I'm missing something else. Any ideas on how to turn the Option Strict off for just this task?

|||

Better to leave Option Strict On.

Dim i as Integer

Dim cn as ADODB.Connection

Dim rs as ADODB.Recordset

These declarations will require a reference in the Script task project to the ADODB primary interop assembly.

-Doug

|||What was the error you get with Option Strict Off?

The finest level of granularity is to turn option strict off at the file level - put this at the top of the file:
Option Strict Off

If you pursue any solution where you need to turn on/off option strict, it's best to at the very least put the code you want option strict off for in another file.

I agree too that Option Strict should typically be left On, but there are situations where turning it Off can make it easier to code, at the expenses that I mentioned above.|||

Thanks all--These prior posts helped me work around the problem. It's appreciated--problem solved!

- Jonathan

Monday, March 12, 2012

Help with "Error converting data type varchar to float"

Error converting data type varchar to float.
I am getting this error. Any one there to guide meYou have come to the right place. We are all mind readers here. We scan your brains alpha waves and decode it into the source code you are looking at that generated this error.|||Unfortunately, I am not receiving any signal from my scan. *shrug* I am also fighting off a cold though, so it could be a blockage on my side.

My best guess given the information you have provided so far is that you are attempting in your code to convert a varchar-format variable or constant containing a date into a float datatype.

This is a no-no in most technical (and social, for that matter) circles.|||Actually u should first tell that where this problem is coming in ur source code or database script|||I am getting this error. Any one there to guide meI knew a girl like that once...

On a more serious note, can you give us something more to work with to help you solve your problem? Please post the SQL code that you were running when you got this error. Look here (http://www.dbforums.com/showthread.php?t=1196943) for more detailed help.

-PatP

Sunday, February 19, 2012

help regarding this search

//and i m getting this error

Syntax error converting the varchar value 'NPO04/136 ' to a column of data type int.

Description:An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details:System.Data.SqlClient.SqlException: Syntax error converting the varchar value 'NPO04/136 ' to a column of data type int.

====================================

can anyone help me in this i m doing a search in a form ( for ex. u search for clientID n it'll come up in search result n once u select that it'll fill in the form) if anyone knw how to do this plz help me. thank u

Protected

Sub cmdSelect2_Click(ByVal senderAsObject,ByVal eAs System.EventArgs)Handles cmdSelect2.ClickDim sIDAsString

sID = lstResults.SelectedValue

Dim iIDAsInteger' iID = Int32.Parse(sID)'' If iID > 0 ThenDim selectSQLAsString

selectSQL =

"SELECT * FROM contactinfo "

selectSQL &=

"WHERE ClientID=@.ClientID"Dim cmdAsNew SqlCommand(selectSQL, conSR)

cmd.Parameters.AddWithValue(

"@.ClientID", iID)Dim readerAs SqlDataReader

Try

conSR.Open()

reader = cmd.ExecuteReader()

reader.Read()

' Fill the controls.'txtCaseid.Text = reader("Caseid").ToString()

txtClientID.Text = reader(

"ClientID").ToString()

txtFirstname.Text = reader(

"Client_Fname").ToString()

txtLastname.Text = reader(

"Client_Lname").ToString()

txtRace.Text = reader(

"Race").ToString()

txtCounty.Text = reader(

"County_of_origin").ToString()

txtGender.Text = reader(

"Gender").ToString()

txtState.Text = reader(

"State").ToString()

txtReligion.Text = reader(

"Religion").ToString()

txtContactID.Text = reader(

"ContactID").ToString()'reader.Close()'' enable_fields()

lblResults.Text =

""Catch exAs Exception'lblResults.Text = "Error inserting record"

lblResults.Text =

"Error inserting record"

lblResults.Text = ex.Message

Finally

conSR.Close()

EndTry

lblID.Text = sID

phID.Visible =

True

txtID.Text = sID

txtAct.Text =

"edit"

cmdSubmit.Text =

"Update"

lblCurrentAct.Text =

"Update Existing Record"'Else'If iID = 0 Then' lblResults.Text = "Client not found in Contact Information."'Else' lblResults.Text = "Please select a contact from the list."''' End IfEndSub

Hello my friend,

The field you wish to search by has non-numeric data in it so use a string to pass as the parameter instead of an int. You pass in iID so it crashes. Use a string variable instead and you should be fine.

Kind regards

Scotty