Get totalRows and Row array with MySQL Database connection

[bdichiara]

I'm using an ODBC Adapter with a MySQL Data connection to retrieve information from my database, and the example I was given doesn't quite explain how to get the data into an array as well as the total records retrieved. All I have is:

Dim category As String = Request.QueryString("c")
Dim page As String = Request.QueryString("p")
Dim sSQL As String = "SELECT * FROM content LEFT JOIN categories ON content.category = categories.categoryid WHERE categories.cat_name = '" & category & "' AND content.name = '" & page & "' AND content.deleted <> '1' LIMIT 0,1"
Dim oDataAdapter As ODBCDataAdapter = New ODBCDataAdapter(sSQL, oConnection)
Dim oDataSet As DataSet = New DataSet()
oDataAdapter.Fill(oDataSet)
oDataGrid.DataSource = oDataSet

I don't quite understand what a DataSet is or what I can do with a datagrid, but all this does is put the data in a table, but I want to be able to separate the content out. How do I do that?

_______________
_brian.

 

[jbenson001]

a dataset is a collection of tables... look in the VS help files or on MSDN. You can loop through a table if you need to parse the data into an array.

 

[bdichiara]

How do I loop through the table to put it into an array?

_______________
_brian.

 

[jbenson001]

Why do you need data in an array?
And Please check the help files or MSDN.. you will find the answers to the questions you have asked so far.

 

[ca8msm]

I can't see any need to create an Array either so there's a couple of examples here that will help you loop through the data.


____________________________________________________________
Mark,
[URL unfurl="true"]http://aspnetlibrary.com[/url]

Need help finding an answer? Try the Search Facility or read FAQ222-2244.

 

[bdichiara]

I'm sorry, I'm new to ASP.NET and VB and I don't even know where to start. I don't know what to look for.

Here's what I'm trying to accomplish: I'm trying to get one or two field from my datasource and put it into a literal on the page. I'm storing my website in the database as HTML and need it to show up as HTML.

_______________
_brian.

 

[ca8msm]

Read the examples from the link I gave above then.


____________________________________________________________
Mark,
[URL unfurl="true"]http://aspnetlibrary.com[/url]

Need help finding an answer? Try the Search Facility or read FAQ222-2244.

 

[bdichiara]

OK, I looked at those examples and still am a bit lost. I actually found a different example that seems to work ok, so I will probably just use it.

DS1.ConnectionString = sConString
Dim category As String = Request.QueryString("c")
Dim page As String = Request.QueryString("p")
Dim sSQL As String = "SELECT * FROM content LEFT JOIN categories ON content.category = categories.categoryid WHERE categories.cat_name = '" & category & "' AND content.name = '" & page & "' AND content.deleted <> '1' LIMIT 0,1"
DS1.SelectCommand = sSQL

<form id="form1" runat="server">
<div>
	<asp:SqlDataSource ID="DS1" runat="server" ProviderName="System.Data.Odbc" />
	
	<asp:GridView ID="gvContent" runat="server" AutoGenerateColumns="False" BorderWidth="0px" CellPadding="0" CellSpacing="0"
    		  ShowHeader="false" DataKeyNames="contentid" DataSourceID="DS1" GridLines="None" AllowSorting="false">
		<Columns>
			<asp:BoundField DataField="contentid" HeaderText="contentID" Visible="false" ReadOnly="True" />
			<asp:BoundField DataField="content" HtmlEncode="false" ReadOnly="True" />
		</Columns>
	</asp:GridView>
</div>

<asp:Literal ID="litContent" runat="server" />
</form>

I only wish I could get the data in my Literal field and do away with all the gridview stuff.

Is there no simple way to reference the data as mydataset("content") or mydataset("contentid") or something like that? Creating these dataviews and datagrids seems a little too much for pages that only have 1 record that are use to display an article or page content.

_______________
_brian.

 

[ca8msm]

In the examples I linked to, you should have come across this article. By simply changing the Response.Write line to set your Literal's Text property, you should have exactly what you want.


____________________________________________________________
Mark,
[URL unfurl="true"]http://aspnetlibrary.com[/url]

Need help finding an answer? Try the Search Facility or read FAQ222-2244.

 

[bdichiara]

I went through and tried to use the example, but I ended up getting a Null value for my dataTable. I think it's because

This: Dim MyDataAdapter = New SqlClient.SqlDataAdapter(strSQL, MyConnection)

Doesn't produce the same result as

this: Dim oDataAdapter As ODBCDataAdapter = New ODBCDataAdapter(sSQL, oConnection)

And since I'm using MySQL, i can't use the sqlclient.sqldataadapter. in the examples for MySQL connections they always make a DataSet, and not a DataTable. I even went reading here:

http://msmvps.com/blogs/simpleman/archive/2005/06/24/54832.aspx

on how to loop through the dataset, but couldn't get that to work.

_______________
_brian.

 

[ca8msm]

This: Dim MyDataAdapter = New SqlClient.SqlDataAdapter(strSQL, MyConnection)

Doesn't produce the same result as

this: Dim oDataAdapter As ODBCDataAdapter = New ODBCDataAdapter(sSQL, oConnection)

I'm not sure what you mean. The first line you've posted above doesn't exist in the example I pointed to (there is no Dim keyword as it has already been declared). Secondly, for connection to MySQL databases, I always thought you used a OleDb.OleDbDataAdapter rather than the Odbc.OdbcDataAdapter.

in the examples for MySQL connections they always make a DataSet, and not a DataTable

This doesn't matter. If you read the help files, you'll see that a DataSet is just a collection of DataTables so if you fill a DataSet, you can just access it's DataTable's vis the Tables property e.g. myDataSet.Tables(0)



____________________________________________________________
Mark,
[URL unfurl="true"]http://aspnetlibrary.com[/url]

Need help finding an answer? Try the Search Facility or read FAQ222-2244.

 

[bdichiara]

Ok, so if I have a connection string and an SQL statement, how would you recommend I connect to my database, retrieve the records, and put it in format that allows me to use that section:

MyDataRow.Item("ProductName")

As shown in the example. This is the right track, I just keep having problems adapting my code to this example.

_______________
_brian.

 

[ca8msm]

I don't understand why you can't just copy the example I showed you and change the classes to use the OleDb class rather than the SQLClient class.


____________________________________________________________
Mark,
[URL unfurl="true"]http://aspnetlibrary.com[/url]

Need help finding an answer? Try the Search Facility or read FAQ222-2244.

 

[josie2007]

It has been mentioned by so many members in this forum in order to avoid sql injection not to use paramters instead. I would do some research in this forum how to avoid sql injection.

 

[bdichiara]

Thanks, ca8msm, that's what I ended up doing. I wasn't sure what I needed to keep and didn't need to keep to get this to work. I think it's working now.

josie2007, thanks for the tip. I do plan on taking precautions for SQL injections, I'm just new to vb.net and it's next on my list.

_______________
_brian.