Print

Please login or register.
1 Hour 1 Day 1 Week 1 Month Forever
Login with username, password and session length

[MrHaugen]

Hi

I'm developing a plugin in VB.Net and have used this method to bring out results from my database until now:

Code: [Select]

      SQLQuery = "SELECT A.ArtistNumber, A.ArtistName, T.TrackName, T.Party, T.Club, T.Workout, T.Summer FROM Artist A, Track T WHERE A.ArtistName= '" & varLocalArtistName & "' AND T.TrackName= '" & varLocalTrackName & "'"

      ' Making a dataAdapter that connects the program an the database
        Dim dataAdapter As New System.Data.SqlClient.SqlDataAdapter(SQLQuery, tilkobling)
      ' Making a dataSet
        Dim myDataSet As New DataSet()
      ' Filling info from dataAdapter to myDataSet
        dataAdapter.Fill(myDataSet)
      ' Making a dataTable
        Dim myDataTable As DataTable
        myDataTable = myDataSet.Tables(0)

      ' Going through each of the rows and putting the apropriate mood in the variable. Or nothing if the rows not containing anything
        For Each tempRow In myDataTable.Rows
            varDBArtistName = temprad("ArtistName")
            varDBTrackName = temprad("TrackName")
            varParty = temprad("Party")
            varClub = temprad("Club")
            varWorkout = temprad("Workout")
            varSummer = temprad("Summer")
        Next tempRow
In the past I needed to return loads of different results. Now I have moved over to more advanced SQL Queries wich can return values depending on IF statements. I only need to return ONE value now, and think the code shown above will be kinda overkill. Unfortunately I don't really know what the code does in detail (5 years since I last used it), so I have not much chance of simplifying it.

I have a 4 line SQL Query I need to use, but to simplify the the example let's say I want to get the number of TrackElements into a variable. The SQL Query can then look like this:

Code: [Select]

SELECT COUNT(TRACKELEMENT.ElementNumber) FROM TRACKELEMENT WHERE TRACKELEMENT.TrackNumber = 1
EDIT: Can anyone help me to alter the code shown at the to so it's able to get the COUNT results over to a variable without the use of "For each tempRow" etc?

EDIT2: Can anyone help me to alter the code shown at the top so it's able to get the COUNT results over to a variable without the use of "For each tempRow, dataSet, dataTable" etc?

[Mr ChriZ]

OK If I'm getting what you wanna do correctly...
You wanna get the result of a Select Count statement, without interigating rows/coloums etc.

What I think you can do is use
ExecuteScalar ()

I'm quite new to T-SQL with .NET myself, so you'll have to play with this one.
In c# I use the following
SqlCommand sqlComm = new SqlCommand(@"SELECT COUNT(*)FROM table WHERE name = 'fred'", sqlConn);

if ((int)sqlComm.ExecuteScalar() > 0)
{
    //We have at least one fred.
}

you'll have to play to get it to work in VB though.

[MrHaugen]

That is not quite what I was looking for ChriZ. I have edited a bit in my original post.
I do not need help with the SQL query it self, but the VB code in the example at the top.

What I belive, is that the code could be done simpler without dataTables, rows etc.
I don't actually need a table when I'm only to return 1 value. Neither rows.

I have never ever returned a single value from a SQL Query. I just copied the example above form a 5 year old project.
Having a bit of a problem when trying to return the count value from the query example (the code at the bottom) with it.

[Mr ChriZ]

My answer was directed at the code side of things.
I think it is still the answer you're looking for,
but possibly you're connecting to SQLServer in a different way  than me.
I'll see if I can come up with a more detailed answer later.

[MrHaugen]

Alright, great. But I think my biggest problem will be how to set up the connection to the database without tables and stuff.
Don't see how that is done in your example. Edited the initial post agian btw...
I'll give your example a go when I get home from work. Maby I can bake it in with my old connection code somehow

[MrHaugen]

No, that got messy. Never tried ExecuteScalar before, and I have a problem understanding the "SqlCommand sqlComm = new SqlCommand(@"SELECT COUNT(*)FROM table WHERE name = 'fred'", sqlConn);" code. It's a different approach than I'm used to.

I'm obviously missing something. This is what I thought could work, based on my previous example:

Code: [Select]

    Public SQLQuery As String
    Public varTemp As String
    Public SQLconn As String = "Data Source=10.0.0.5;Initial Catalog=Test;User Id=DBUser;Password=temp;"

    Private Sub bt_sqltest_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles bt_sqltest.Click

        SQLQuery = "SELECT COUNT(TRACKELEMENT.ElementNumber) FROM TRACKELEMENT WHERE TRACKELEMENT.TrackNumber = 1"

        ' Making a dataAdapter that connects the program an the database
        Dim dataAdapter As New System.Data.SqlClient.SqlDataAdapter(SQLQuery, SQLconn)
        ' Making a dataSet
        Dim myDataSet As New DataSet()
        ' Filling info from dataAdapter to myDataSet
        dataAdapter.Fill(myDataSet)

        varTemp = myDataSet.ToString

        MessageBox.Show(varTemp)
I just get "System.Data.DataSet"  Not exactly what I wanted to get
Also tried to print the dataAdapter. Didn't get any better.

[MrHaugen]

You are totally right. The ExecuteScalar is the key to success I think.
Have tried alot of examples on the web, but can't make it work right.

Code: [Select]

Public connString As String = "Data Source=10.0.0.x;Initial Catalog=Test;User Id=DBUser;Password=temptemp;"

    Private Sub bt_sqltest_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles bt_sqltest.Click
        Dim mySelectQuery As String = "SELECT COUNT(TRACKELEMENT.ElementNumber) FROM TRACKELEMENT WHERE TRACKELEMENT.TrackNumber = 1"
        Dim myConnection As New SqlConnection(connString)
        Dim myCommand As New SqlCommand(mySelectQuery, myConnection)
        myConnection.Open()
        Dim myReader As SqlDataReader = myCommand.ExecuteReader()

        ' This one works badly. Complains that there is nothing to read
        Console.WriteLine(myReader.GetInt32(0).ToString & ", " & myReader.GetString(1))
        ' always call Close when done reading.
            myReader.Close()
            ' always call Close when done reading.
            myConnection.Close()
    End Sub
There is a problem with the console line. It finds nothing, even though I know the select query is correct. Not exactly sure what the Console.WriteLine does either. Msdn says it prints result to the normal output. But it will not even print a string put directly into the code (Console.WriteLine("Test"))

Any suggestions or complete examples?

[Mr ChriZ]

Here's a complete C# Example

        /// <summary>
        /// Runs a SQL statement, returning an integer from the first row and field.
        /// </summary>
        /// <param name="connectionString">The SQL server.</param>
        /// <param name="sqlStatement">The SQL statement.</param>
        /// <returns>integer</returns>
        private int runSQLStatement(string connectionString, string sqlStatement)
        {
            // create a new SqlConnection object with the appropriate connection string
            SqlConnection sqlConn = new SqlConnection(connectionString);

            // open the connection
            sqlConn.Open();

            //Create the SQL Command
            SqlCommand sqlComm = new SqlCommand(sqlStatement, sqlConn);

            //Get the return value
            int returnValue = sqlComm.ExecuteNonQuery();
           
            //Close the connection
            sqlConn.Close();           

            //Execute the query and return the number of rows effected.
            return returnValue;
        }

I'd convert this into VB for you but it's almost mid-night,
and I'm long shifts almost at the moment as my work collegue is on holiday!
Maybe tomorrow.

[MrHaugen]

Thanks ChriZ! Again, just take your time.
Okaaay, here we go! Almost there...

I managed to make a usefull code out of different examples.

Code: [Select]

        Dim Sql As String = String.Format("IF (SELECT COUNT(TRACKELEMENT.ElementNumber) FROM TRACKELEMENT WHERE TRACKELEMENT.TrackNumber = (SELECT TRACK.TrackNumber FROM TRACK WHERE TRACK.TrackName = 'feel good inc' AND TRACK.ArtistNumber = (SELECT ARTIST.ArtistNumber FROM ARTIST WHERE ARTIST.ArtistName = 'gorillaz'))) = 6 PRINT '1' ELSE PRINT '0'")

        'Open a connection to the database
        Dim connection As New SqlConnection("Data Source=10.0.0.X;Initial Catalog=Test;User Id=DBUser;Password=temptemp;")

        'Create a command to execute the sql statement
        Dim command As New SqlCommand(Sql, connection)

        'Open the connection and execute the command. Note, this uses the ExecuteScalar method as you are only going to get a single value back.
        connection.Open()
        Dim tempInt As Int32 = Convert.ToInt32(command.ExecuteScalar)
        connection.Close()

        ' Messagebox with the SQL query result
        MessageBox.Show(tempInt)
If I run this code without the IF (only the Select query) I get the number of elements. Perfect!
Once I try to use the IF and the condition I get "0" in the messagebox, no mather what the condition is (<, >, = , ==, 1, 999 etc). I just don't get it. Is it the PRINT code it don't like? Are there alternatives?

I would like to inklude the IF conditions in the query to simplify the VB code. Not actually sure if it makes things faster or not though.
The idea is to make the IF condition trigger a second or third SELECT, UPDATE or INSERT query  

[src666]

I'm having some trouble figuring out what your goal is for the query based upon the examples you have given, but I can tell that you are in desperate need of some JOIN logic.

The following should return the count based upon the table structure I have seen so far:

SELECT COUNT(*)
FROM TrackElement TE
INNER JOIN Track T ON (T.TrackNumber = TE.TrackNumber)
INNER JOIN Artist A ON (A.ArtistNumber = T.ArtistNumber)
WHERE T.TrackName = 'feel good inc'
AND A.ArtistName = 'gorillaz'

Leave the IF stuff out. If you need a query to do multiple things depending on different circumstances, you should try to produce individual queries for each circumstance and use your program's logic to figure out what to call.

If this doesn't help, provide a little more detail on exactly what your goal is (what data you are looking for, in what groupings, etc.). I'll be glad to help with any SQL issues you are having - I live most of my days developing with MS SQL.

[MrHaugen]

I'm having some trouble figuring out what your goal is for the query based upon the examples you have given, but I can tell that you are in desperate need of some JOIN logic.

The plan is to have a seperate program on my server to compare the different values in trackelements if there are a total of 5 elements. Let's say the middle value of all 5 elements will be placed in trackelement number 6.
In the plugin, what I need is to figure out how many track elements there are in a given track. Based on that I want to make a INSERT Element if there are less than 5. Make a SELECT trackelement 6 query if there are above 5 elements (to download the middle value). Or simply exit if the middle value have not been calculated in the standalone app yet.

The query you provided works like a charm, but so did my example  I guess this is a more correct way of doing what I tried to accomplish. Probably alot faster to execute too. I tried to study the Join function a few days ago, but I gave up after looking at a few examples. Didn't understand how it worked.

I tried to implement the IF stuff in the query to simplify the code. But as you say it's probably better to make some code to handle the IF's and make other queries depending on the outcome of the VB plugin. I just thought it might be easier the other way around.

I actually made the IF stuff in the query work. Just had a big problem with the PRINT function I guess.

Code: [Select]

IF (SELECT COUNT(TRACKELEMENT.ElementNumber)
FROM TRACKELEMENT WHERE TRACKELEMENT.TrackNumber =
(SELECT TRACK.TrackNumber FROM TRACK WHERE TRACK.TrackName = 'feel good inc.' AND TRACK.ArtistNumber =
    (SELECT ARTIST.ArtistNumber FROM ARTIST WHERE ARTIST.ArtistName = 'gorillaz'))) > 6  SELECT * FROM Artist ELSE SELECT * FROM TRACK
Just throw in ELSEIF's and a INSERT satement and I'm about finished. What is the downside of this, compared to implement the IF logic in the VB code?

[src666]

Just curious, but what data is stored in a TrackElement? Are you mimicking some structure from MC, or is this a structure you designed yourself? I just started looking at MC yesterday, really, so as far as it is concerned I'm a total newb.

I'm just taking a wild guess, but it looks like you might be using individual TrackElements when you should just be using a single row in a table. I could be totally wrong, though. Not sure how much info you want to share in public, but a look at your data structure may go a long way to streamlining things.

The problem with using IF in SQL is that SQL is a query language, and isn't supposed to be used for procedural code. They have added procedural code elements, because sometimes it's just more efficient to use logic at the database end to restrict the amount of data you have to send back to the client (i.e. remote connections, etc.), but overall it is preferred to keep as much of it out of your queries as possible.

As for JOINs, I think they will make your life MUCH easier in the long run, and I'll be glad to help you out with those.

For example:

SELECT A.Artistname, T.AlbumName, T.TrackName
FROM Artist A
INNER JOIN Track T ON (T.ArtistName = A.ArtistName)
ORDER BY A.ArtistName

This gives you a complete list of your artists, their albums and the tracks on the album (assuming that AlbumName is a field in the Track table - maybe there's an Album table that I haven't seen yet?). If you just SELECT * using the above syntax, you get 1 row for each Track, but each row includes both Artist and Track information. It "joins" the two tables together into a single result, with the ON clause (T.ArtistName = A.ArtistName) telling it what data goes together.

[MrHaugen]

Just curious, but what data is stored in a TrackElement? Are you mimicking some structure from MC, or is this a structure you designed yourself? I just started looking at MC yesterday, really, so as far as it is concerned I'm a total newb.

I'm just taking a wild guess, but it looks like you might be using individual TrackElements when you should just be using a single row in a table. I could be totally wrong, though. Not sure how much info you want to share in public, but a look at your data structure may go a long way to streamlining things.

It's not a secret at all. I'm planing of using a custom library field to tag different moods and styles that represent each song. Here you can get a better idea of my early plan for the plugin: http://yabb.jriver.com/interact/index.php?topic=36737.0

In the trackelements table I got bit values representing diffrent moods and styles. Like: Angry, Energetic, Background, Relaxing, Happy, Sad, Romantic etc. etc. This is stored as 1 or 0 for each one. All those values are stored up to 5 times for each track. 1 time for each user. When the results from 5 different users have been uploaded, the "middle" value are stored in element number 6 (done with a stand alone application).

Thant's a part of the plan.

By the way. I haven't created a Album table yet, and will probably never do. The reason is the search hits. There might be alot of tracks that are on more than one album for each artist. So there would be alot less hits.