SQLGetData

[awiora]

Hello,
I'm having a rough time with the code below, using SQLGetData. The piece of code: SQLGetData(glStmt, 1, 1, sData.ToString, MAX_DATA_BUFFER, lOutLen)
vval = sData.Length
MsgBox("outLen: " & lOutLen.ToString & vbNewLine & _
"iStatus: " & iStatus & vbNewLine & _
"value: " & CChar(sData.ToString))
I'm trying to get the value of sData, but I'm not getting anything.
Any suggestions will be great.



Dim hEnv As Integer
Dim hDBC As Integer
Dim glStmt As Integer

If SQLAllocEnv(hEnv) = SQL_SUCCESS Then
MsgBox("SQL AllocEnv Succeeded!")
Else
MsgBox("Unable to initialize ODBC API drivers!")
End If


Dim iStatus As Integer
If SQLAllocConnect(hEnv, hDBC) = SQL_SUCCESS Then
MsgBox("SQL AllocConnect Successful!")
Else
MsgBox("Could not allocate memory for connection Handle!")
' Free the Environment
iStatus = SQLFreeEnv(hEnv)
If iStatus = SQL_ERROR Then
MsgBox("Error Freeing Environment From ODBC Drivers")
End If
End If

Dim sResult As String
Dim iSize As Integer
Dim sConnect As String

Dim DSN As String
Dim UID As String
Dim PWD As String

DSN = Me.txtDSN.Text
UID = Me.txtName.Text
PWD = Me.txtPswd.Text

sConnect = "DSN=" & DSN & ";UID=" & UID & ";PWD=" & PWD

If SQLDriverConnect(hDBC, Me.Handle.ToInt32, sConnect, Len(sConnect), sResult, Len(sResult), iSize, 0) = SQL_SUCCESS Then
MsgBox("Connection Successful!")
Else
MsgBox("Connection UNSuccessful!")
End If


If SQLAllocStmt(hDBC, glStmt) = SQL_SUCCESS Then
MsgBox("SQL AllocStmt Successfully")
Else
MsgBox("Could not allocate memory for a statement handle!")
End If

sSQL = "SELECT " & _
" ""- Account Basic Detail"".""Account Name"" AS Account_Name " & _
"FROM DWH " & _
"WHERE ""- Geography"".""Country Cd"" = 'US'

If SQLExecDirect(glStmt, sSQL, Len(sSQL)) = SQL_SUCCESS Then
MsgBox("SQL ExecDirect Succesfully!", MsgBoxStyle.Information, "Execute Query")
Else
MsgBox("Error Executing Query")
End If

Dim bPerform As Integer
Dim lOutLen As Integer
Dim iColumn As Integer = 1
Dim sData As StringBuilder = New StringBuilder()
Dim vval As String

bPerform = SQLFetch(glStmt)

If bPerform = SQL_SUCCESS Then ' If rows of data available
iStatus = SQLGetData(glStmt, 1, 1, sData.ToString, MAX_DATA_BUFFER, lOutLen)
vval = sData.Length
MsgBox("outLen: " & lOutLen.ToString & vbNewLine & _
"iStatus: " & iStatus & vbNewLine & _
"value: " & CChar(sData.ToString))
Else
MsgBox("No more rows available") ' No more rows available
End If

'Release the ODBC Statement Handle
bPerform = SQLFreeStmt(glStmt, SQL_DROP)

' ODBC Variables and Constants
Dim glEnv As Long
Dim glDbc As Long
Dim sSQL As String

Const MAX_DATA_BUFFER As Integer = 8000
Const SQL_SUCCESS_WITH_INFO As Long = 1
Const SQL_NO_DATA_FOUND As Long = 100
Const SQL_DROP As Long = 1
Const SQL_NUMERIC As Long = 2
Const SQL_INTEGER As Long = 4
Const SQL_FLOAT As Long = 6
Const SQL_DOUBLE As Long = 8
Const SQL_DATA_SOURCE_NAME As Long = 6
Const SQL_SUCCESS As Long = 0
Const SQL_ERROR As Long = -1
Const SQL_CLOSE As Long = 0
Const SQL_CHAR As Long = 1
Const SQL_DECIMAL As Long = 3
Const SQL_SMALLINT As Long = 5
Const SQL_REAL As Long = 7
Const SQL_VARCHAR As Long = 12
Const SQL_USER_NAME As Long = 8

'ODBC Declarations
Public Declare Function SQLAllocEnv Lib "odbc32.dll" (ByRef env As Integer) As Short
Public Declare Function SQLAllocConnect Lib "odbc32.dll" (ByVal env As Integer, ByRef ldbc As Integer) As Short
Public Declare Function SQLFreeEnv Lib "odbc32.dll" (ByVal env As Integer) As Short
Public Declare Function SQLDriverConnect Lib "odbc32.dll" (ByVal ldbc As Integer, ByVal hWnd As Integer, ByVal szCSIn As String, ByVal cbCSIn As Short, ByVal szCSOut As String, ByVal cbCSMax As Short, ByRef cbCSOut As Short, ByVal f As Short) As Short
Public Declare Function SQLConnect Lib "odbc32.dll" (ByVal ldbc As Integer, ByVal Server As String, ByVal serverlen As Integer, ByVal uid As String, ByVal uidlen As Integer, ByVal pwd As String, ByVal pwdlen As Integer) As Short
Public Declare Function SQLExecDirect Lib "odbc32.dll" (ByVal lStmt As Integer, ByVal sqlString As String, ByVal sqlstrlen As Integer) As Short
Public Declare Function SQLAllocStmt Lib "odbc32.dll" (ByVal ldbc As Integer, ByRef lStmt As Integer) As Short
Public Declare Function SQLError Lib "odbc32.dll" (ByVal env As Integer, ByVal ldbc As Integer, ByVal lStmt As Integer, ByVal SQLState As String, ByRef NativeError As Integer, ByVal Buffer As String, ByVal Buflen As Integer, ByRef Outlen As Integer) As Integer

Public Declare Function SQLGetData Lib "odbc32.dll" (ByVal lStmt As Integer, ByVal col As Integer, ByVal wConvType As Integer, ByVal lpbBuf As String, ByVal dwbuflen As Integer, ByRef lpcbout As Integer) As Short

Public Declare Function SQLFetch Lib "odbc32.dll" (ByVal lStmt As Integer) As Short
Public Declare Function SQLFreeStmt Lib "odbc32.dll" (ByVal lStmt As Integer, ByVal EndOption As Integer) As Short

 

[alim]

you make the simple code to more complex.. please try with simple one..if I want to understand .. I have to read and check this code for 1 or 2 hours.. so please make it simple and check your code step by step using quick watch view, and then you will realize what is missing...

because... it is really very tough to understand your coding.. and why are you calling APIs for no means.. there is no need to call API, you can handle those exception by using normal exception handler.

And the other thing, if you have tested your connection so there is less chance of not to get connected.. if you use your tested connection string.. but why are you typing your conncetion string again? just call it, if you have saved your conncetion string in your project.

Dim ConncetionStr as string=my.settings.connectionStr 'if you have tested it in your project

it will help you to reduce your coding...



n Please put the CODE tag ...