AboutSyed Rizwan Muhammad Rizvi Expertise I can answers questions regarding web based and desktop based programming in VB.Net. Which can include SOAP, XML, Custom Controls, COM Interoperability etc.
Experience Have been working in this specific area for last 2 years previously I was a VB 6 Developer with experties in other languages as well. Total 10 years of programming experience.
Question QUESTION: hello, I am new to VB.Net.
I have written code to pass a SQL query, but I need to store the results in an array. I don't know the VB.Net substitute for the VB code rs.getrows
here is the gist of the code.
Import System.Data.SqlClient
Private Sub getdata()
Dim sqlcon as new Sqlconnection(dbstring)
sqlcon.Open()
Dim sqlcom as new SqlCommand
sqlcom.Connection=sqlcon
sqlcom.CommandType=CommandType.Text
sqlcom.CommandText="Select ID from Table"
Dim Dr as SqlDataReader
Dr=sqlcom.ExecuteReader
Dr.Read()
''''Code needed here to store results into array'''
sqlcon.close()
sqlcom.connection=Nothing
End Sub
In VB is used ADODB , the code was
Private Sub getdata()
Dim cn as New ADODB.Connection
Dim rs as New ADODB.RecordSet
Dim a() as Integer
cn.open dbstring ''''already defined and initialized
rs.open "Select ID from Table" , cn, adOpenStatic, adLockReadOnly
If Not rs.EOF then
a= rs.GetRows
end if
cn.close
rs.close
set cn=nothing
set rs=nothing
End Sub
I can read all the data in dr.Read() by using msgbox(dr(0))
but I don't know how to copy the values to an integer array.
thank you for your help
ANSWER: Hi the exact method is not supported, you can work on with various work arounds though, e.g.
While Dr.Read
MsgBox Dr(0)
End While
Let me know if you need further help.
---------- FOLLOW-UP ----------
QUESTION: Thank you. What I want to do is get all the ID values from the table using this query, and then store it in an integer array, then generate a random ID (from those values) using the Rnd(). But I still can't copy the Dr.Read() to an integer array.
Or how to convert dr.Read() values to a compatible format.
How can I store these values? If you have any other suggestions to get a random ID number from the retrieved data, then please let me know.
Thank you again
ANSWER: Try:
Dim intCol As New Collection
While dr.Read
intCol.Add(dr(0), dr(0))
End While
Here you are using a collection instead of an array I think you should be able to achieve what you want using Collection as well.
Let me know if you have further questions.
---------- FOLLOW-UP ----------
QUESTION: Thank you.
I have no idea about how to go about using collections, so instead i used this code:
Import System.Data.SqlClient
Private Sub getdata()
Dim a as Array
Dim b as New ArrayList
Dim rndNum,rndID,count as Integer
Dim sqlcon as new Sqlconnection(dbstring)
sqlcon.Open()
Dim sqlcom as new SqlCommand
sqlcom.Connection=sqlcon
sqlcom.CommandType=CommandType.Text
sqlcom.CommandText="Select ID from Table"
Dim Dr as SqlDataReader
Dr=sqlcom.ExecuteReader(CommandBehaviour.SingleResult)
While Dr.Read()
b.Add(Dr)
End While
a=b.ToArray
count=UBound(a,1)
rndNum=CInt(Rnd() * count)
rndID= a(IIf(rndNum=0,1,rndNum)""" HERE I GET AN ERROR"
sqlcon.close()
sqlcom.connection=Nothing
End Sub
I get the error : Cast from type "SqlDataReader" to type "Integer" is not valid.
Could you please suggest how to rectify this?
Thank you for your help.
ANSWER: change b.Add(Dr) to b.Add(Dr(0))
---------- FOLLOW-UP ----------
QUESTION: Thanks this worked perfectly!!
I had another question, part of the same project, about loading images into a picturebox using datareader.
This is the code I used:
Public Sub FillPic(ByVal PassedID As Integer)
dim SQLConn as New SqlConnection(dbString)
SQLConn.Open()
Dim SQLCom as New SqlCommand
Dim dr as SqlDataReader
SQLCom.Connection = SQLConn
SQLCom.CommandType = CommandType.Text
SQLCom.CommandText = "Select picture from PictTable " _
& "where PictID = " & PassedID
dr = SQLCom.ExecuteReader()
dr.Read()
Dim bytBLOBData(dr.GetBytes(0, 0, Nothing, 0, Integer.MaxValue) - 1) As Byte
dr.GetBytes(0, 0, bytBLOBData, 0, bytBLOBData.Length)
dr.Close()
Dim stmBLOBdata As New MemoryStream(bytBLOBData)
PictureBox1.Image=Image.FromFile(stmBLOBdata)''''I GET AN ERROR HERE''
SQLConn.Close()
SQLCom.Connection=Nothing
End Sub
The complier shows "Value of type System.IO.MemoryStream cannot be converted to type String"
How do I load the picture into the picturebox from the SQL database table?
Thank you for your help!
ANSWER: use Image.FromStream instead of Image.FromFile
---------- FOLLOW-UP ----------
QUESTION: Thank you again. I have another question:
How can I access a SQL table and add data to it without using the "INSERT INTO" query statement?
When I tried to use SQLCom.CommandType=CommandType.TableDirect
SQLCom.CommandText="MyTable"
I got the error "CommandType.TableDirect is not supported by the .Net SqlClient Data Provider."
is there something in SQLClient that works like the vb code
Please let me know how to fix this using SQLCommand options. Thank you :-)
ANSWER: You don't wanna try what you are trying with ADO.net it is recommendation and the only way "I" know of entering data in a table using SQLClient you might want to give OLEDBCommand a try, though it is not recommended to use direct table access.
---------- FOLLOW-UP ----------
QUESTION: Thank you.
Another question: what's the VB.Net substitute for SQLQueryString?
I want to pass a SQL query to a crystal report, and some parameter values too. It's to get some values from the database with dates ranging between a selected From Date (formattedFromDate) and To Date (formattedToDate)
In vb6 I used
Dim oApp As CRAXDRT.Application
Dim oReport As CRAXDRT.Report
Set oApp = New CRAXDRT.Application
Set oReport = oApp.OpenReport("e:practical
eports
eport1.rpt", 1)
