Count Records in sqlcommand or sqladapter?

Hi everyone? I have a small problem here, I want to count the records found under the following method:

Public Function ValidateAssembly(ByVal assynoAs String)As DataSetDim SQLConnAs SqlConnection =New SqlConnection(Connstr)Dim adapterAs SqlDataAdapter =New SqlDataAdapterDim dsVendorInfoAs DataSet =New DataSet("AssemblyHeader")Dim cmdAs System.Data.SqlClient.SqlCommand =New System.Data.SqlClient.SqlCommandDim BMItemnoAs New SqlParameter("@.v_assyno", SqlDbType.VarChar, 10) BMItemno.Value = GetBomAssyNo(assyno) SQLConn.Open()Try With cmd .CommandText ="SELECT * FROM dbo.cfn_bom_get_assy(@.v_assyno)" .Parameters.Add(BMItemno) .Connection = SQLConn .CommandType = CommandType.TextEnd With adapter.SelectCommand = cmd adapter.Fill(dsVendorInfo)Catch xAs ExceptionEnd Try SQLConn.Close()Return dsVendorInfoEnd Function

I want to see if this can be done and passed to the actual dataset? Is this possible? Or do I need to pass the found results from my query to a sqlreader? Also, Im I forced to put my colums together in a DataTable before actually binding to the DataSet?

 Thanks everyone! 

If I understand what you are looking for, you want the count of the number of records returned in your Dataset. All you have to do for that is:

dsVendorInfo.Tables(0).Rows.Count

|||Right, and how can I attach that to my dataset? Create a new column within the dataset that contains that value?|||

Why would you want to attach that to your dataset?? Anywhere you use the dataset you can retrieve the count.

|||

Scratch that. My intent is to simply pass the record count to a label. I am using an ajax control to extract data from this function and then the dataset populates all these labels in my form. I have a record count label which I have to also populate. So I was just thinking to add an extra column with a record count value but seems overhead to me. I created a new method that will just be used to populate that one label. My problem is that the record count does not show on your suggested "dsVendorInfo.Tables(0).Rows.Count" formula. I placed a break point on this line and its not being populated andI know its passing values (I am running a trace on the query). Here is my code:

<AjaxPro.AjaxMethod()> _Public Function ValidateAssembly(ByVal assynoAs String)As DataSetDim SQLConnAs SqlConnection =New SqlConnection(Connstr)Dim adapterAs SqlDataAdapter =New SqlDataAdapterDim dsVendorInfoAs DataSet =New DataSet("AssemblyHeader")Dim cmdAs System.Data.SqlClient.SqlCommand =New System.Data.SqlClient.SqlCommandDim BMItemnoAs New SqlParameter("@.v_assyno", SqlDbType.VarChar, 10) BMItemno.Value = GetBomAssyNo(assyno) SQLConn.Open()Try With cmd .CommandText ="SELECT * FROM dbo.cfn_bom_get_assy(@.v_assyno)" .Parameters.Add(BMItemno) .Connection = SQLConn .CommandType = CommandType.TextEnd With adapter.SelectCommand = cmd adapter.Fill(dsVendorInfo)Dim intCountAs Integer intCount = dsVendorInfo.Tables(0).Rows.Count 'showing empty when called upon RecordCount(intCount)Catch xAs ExceptionEnd Try SQLConn.Close()Return dsVendorInfoEnd Function Private Sub RecordCount(ByVal intNumAs Integer) lblRecordCount.Text = intNumEnd Sub

|||URGGHGH Scratch that too. I was passing my variable as an integer. It has to be passed as a string. Working beautifully now. Thanks for the help! :)

Could Not Find Installable ISAM

Pls Can someone tell me why this code is giving me "Could not find installable ISAM"

ProtectedSub Page_Load(ByVal senderAsObject,ByVal eAs System.EventArgs)HandlesMe.Load
Dim strConnAsNew OleDbConnection("Provider=Microsoft.JET.OLEDB.4.0;Data Source=" & Server.MapPath("~/App_Data/WK 48-2007.xls") &";" &"Extended Properties=""EXEL 8.0;HDR=YES""")
strConn.Open()
Dim StrSelectAsString ="SELECT * FROM [Sheet1$]"
Dim CommAsNew OleDbCommand(StrSelect, strConn)
Dim readerAs IDataReader = Comm.ExecuteReader
GRD.DataSource = reader
GRD.DataBind()
reader.Close()
strConn.Close()
EndSub

Hi,

From your description, it seems that you are using OLEDB Jet driver to connect to your EXCEL table, right?

When you try to set custom, provider-specific properties for the Microsoft OLE DB Provider for Jet in the ADO OLE DB connection string, you receive the following error message:

Could not find installable ISAM

CAUSE
The Microsoft OLE DB Provider for Jet includes many custom properties that you can set only after you establish a connection to the database. These provider-specific properties are custom OLE DB session properties. You cannot set these properties in the ADO OLE DB connection string; you must set these properties after the connection is opened.

For these properties in details and get more information on this issue, see
http://support.microsoft.com/kb/318161/en-us

Thanks.

|||

Hi Nai-Dong,

Tnx. Socan i use SqlClient to connect to EXEL table ? How ?

Thanks again

|||

Hi

Hope this is what you are looking for

Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\MyExcel.xls;Extended Properties="Excel 8.0;HDR=Yes;IMEX=1";

string SQLQuery = "SELECT * FROM [sheet1$]".

// excel worksheet name followed by a "$" and wrapped in "[" "]" brackets.

You can just replace all your OleDB with SQL... In other words, OleDBconnection with SQLConnection and so on.

This query can also be executed using a DataAdapter using the FILL method. That can give you back a dataset which will contain all the rows.

Hope this helps

VJ

|||

Hi,

Tnx. Socan i use SqlClient to connect to EXEL table ? How ?

Since you are using OLEDB driver, you should use OLEDBCommand (using System.Data.OleDb;) to execute your query.

Thanks.