Running SQL Queries using Code Files 1

[cschristian78]

We start off with a form that allows the user to select the database which resides on a SQL Server, Portfolio within the database, Peril, Country and State. These selections are used to create specific tables needed to run additional queries. After these selections are made,
I then allow the user to name the [SET] how ever they want and to create multiple [SETS]. At this point, they select the run button and I have set queries that I have created but I have them in a vb code file. When running the query, I just call the string the was created on the code file. For example, the following query is in a code file:

'CA DOI Zones'
Public strCADOIZones As String = "INSERT INTO [zz_Results Master] (Name3, Risks, TIV, [Set], Type, [Index], Peril) " & _
"SELECT Profdata.dbo.Cresta([Cresta])AS [CA DOI Zone], Count(LOC.LOCID) AS Risks, Sum(Val) AS TIV, [Set] AS [Set], 'CA DOI Zone' AS Type, [Index], Peril AS Peril " & _

"FROM (loc INNER JOIN qrySumLoccvg_Old ON loc.LOCID = qrySumLoccvg_Old.LOCID) LEFT JOIN [CA DOI Zones] ON loc.CRESTA = [CA DOI Zones].[CA DOI Zone], setdesc " & _

"WHERE [SET] = '" & dsSetDesc.Tables("SetDesc").Rows(RDCount).Item(1) & "'" & _

"GROUP BY [CRESTA], [Set], [Index], [Peril] " & _

"ORDER BY [SET], [NAME3], [Index]"

When trying to run this query, I get an error when it compiles the WHERE clause. This is because the dataset is only accessible within my form. Is there a way to reference a specific row in a dataset when using code files?

Thanks for the help,
Scott



I have about 15 or so queries that I need to run. In each query, the user selects the peril, country and statecode which within the interface. Based off of the selected criteria, My problem is when creating the loop which runs through the queries, I need to

 

[ThatRickGuy]

pass the dataset into the function where you have that code.

sub CADOIZones(SetDesc as data.dataset)
 Public strCADOIZones As String = "INSERT INTO [zz_Results Master] (Name3, Risks, TIV, [Set], Type, [Index], Peril) " & _
  "SELECT  Profdata.dbo.Cresta([Cresta])AS [CA DOI Zone], Count(LOC.LOCID) AS Risks, Sum(Val) AS TIV, [Set] AS [Set], 'CA DOI Zone' AS Type, [Index], Peril AS Peril " & _

  "FROM (loc INNER JOIN qrySumLoccvg_Old ON loc.LOCID = qrySumLoccvg_Old.LOCID) LEFT JOIN [CA DOI Zones] ON loc.CRESTA = [CA DOI Zones].[CA DOI Zone], setdesc " & _

  "WHERE [SET] = '" & SetDesc.Tables("SetDesc").Rows(RDCount).Item(1) & "'" & _

  "GROUP BY [CRESTA], [Set], [Index], [Peril] " & _

  "ORDER BY [SET], [NAME3], [Index]"

'...
end sub

----------------------

http://www.ringdev.com

 

[cschristian78]

So something like this:

Sub CADOIZones(ByVal SetDesc As Data.DataSet)

Dim strCADOIZones As String = "INSERT INTO [zz_Results Master] (Name3, Risks, TIV, [Set], Type, [Index], Peril) " & _

"SELECT Profdata.dbo.Cresta([Cresta])AS [CA DOI Zone], Count(LOC.LOCID) AS Risks, Sum(Val) AS TIV, [Set] AS [Set], 'CA DOI Zone' AS Type, [Index], Peril AS Peril " & _

"FROM (loc INNER JOIN qrySumLoccvg_Old ON loc.LOCID = qrySumLoccvg_Old.LOCID) LEFT JOIN [CA DOI Zones] ON loc.CRESTA = [CA DOI Zones].[CA DOI Zone], setdesc " & _

"WHERE [SET] = '" & dsSetDesc.Tables("SetDesc").Rows(RDCount).Item(1) & "'" & _

"GROUP BY [CRESTA], [Set], [Index], [Peril] " & _

"ORDER BY [SET], [NAME3], [Index]"

Dim cmdCADOIZones As New SqlClient.SqlCommand(strCADOIZones, ProfDataCon)
cmdCADOIZones.ExecuteNonQuery()
cmdCADOIZones.CommandTimeout = 600
End Sub

Then do I call this sub in behind the click event of the run button? When I try to execute this I get the following error.
C:\_Projects\Location Profiles Automation\DataProfiles\Data Profiles.NET\Forms\frmAnalysisOptions.vb(978): Argument not specified for parameter 'SetDesc' of 'Public Sub CADOIZones(SetDesc As System.Data.DataSet)'.

Any thoughts?

Thanks

 

[ThatRickGuy]

make sure you provide the dataset when you call the sub.

you would call it like:

public sub btnRun_Click....
  CADOIZones(dsSetDesc)
end sub

and make sure the code in the sub points to "SetDesc" the variable that is inside the parameters list, not dsSetDesc.

-Rick

----------------------

http://www.ringdev.com

 

[cschristian78]

Thanks for the help. However, when running the query, I am getting this error:

System.NullReferenceExeption: Object reference not set to an instance of an object.

I assume this means that there is nothing being brought into the query - no value for [Set]. The dataset is reading a table I have created, would it be easier to have the "Where" clause point to the table directly instead of the dataset?

Thanks,

Scott

 

[ThatRickGuy]

A null reference exception means an object has not been instantiated. What line is it occuring on?

-Rick

----------------------

http://www.ringdev.com

 

[cschristian78]

Here is the rest of the error:

at Project1.sqlcode.cadoizones(DataSet SetDesc) in Cath...SQLCode.vb:line308
at Project1.frmAnalysisOptions.cmdRun_Click(Object eventSender, EventArgs eventArgs) in Cath...frmAnalysisOptions.vb:line1144

 

[ThatRickGuy]

what is the line of code that is causing that error?

-Rick

----------------------

http://www.ringdev.com

 

[cschristian78]

Here is the sub, which is on a code file

Line 308

Public Sub CADOIZones(ByVal SetDesc As Data.DataSet)

Dim strCADOIZones As String = "INSERT INTO [zz_Results Master] (Name3, Risks, TIV, [Set], Type, [Index], Peril) " & _

"SELECT Profdata.dbo.Cresta([Cresta])AS [CA DOI Zone], Count(LOC.LOCID) AS Risks, Sum(Val) AS TIV, [Set] AS [Set], 'CA DOI Zone' AS Type, [Index], Peril AS Peril " & _

"FROM (loc INNER JOIN qrySumLoccvg_Old ON loc.LOCID = qrySumLoccvg_Old.LOCID) LEFT JOIN [CA DOI Zones] ON loc.CRESTA = [CA DOI Zones].[CA DOI Zone], setdesc " & _

"WHERE [SET] = '" & dsSetDesc.Tables("SetDesc").Rows(RDCount).Item(1) & "'" & _

"GROUP BY [CRESTA], [Set], [Index], [Peril] " & _

"ORDER BY [SET], [NAME3], [Index]"

Dim cmdCADOIZones As New SqlClient.SqlCommand(strCADOIZones, ProfDataCon)
cmdCADOIZones.ExecuteNonQuery()
cmdCADOIZones.CommandTimeout = 600

End Sub

There are no spaces between lines. I just did this for us to view.

Line 1144 is when I call the sub:

Try
CADOIZones(dsSetDesc) --> This is line 1144
Catch ex As Exception
MessageBox.Show(ex.ToString)
End Try

 

[ThatRickGuy]

First, make sure that dsSetDesc is instantiated. put a break point on the CADOIZones(dsSetDesc) line, put the mouse over dsSetDesc (or type "?dsSetDesc" in the immediate window) and make sure it is not "nothing".

next, hit F11 to step through the code. the cursor should jump to the CADOIZones sub. We need to find the line that is throwing the error, so you can either just step through the code and see what is the last line befor the cursor jumps to the error handler, or you can take out the exception handling for the moment. That will leave the error unhandled so you will be able to tell exactly where it is occuring.

-Rick


----------------------

http://www.ringdev.com

 

[cschristian78]

When I put my cursor over the (dsSetDesc) part of CADOIZones(dsSetDesc) it says it is equal to {system.data.dataset}.

I get the error when trying to run the sub. Below is where it quits.

Public Sub CADOIZones(ByVal SetDesc As Data.DataSet).

It runs for a second and then throws the error.

Scott

 

[ThatRickGuy]

yes, we need to know which line of code causes the error.

remove the exception handling.

run the procedure.

at some point in time a message box will appear saying "Unhandled exception... break or continue?"

hit break.

the cursor should be highlighting the line that threw the exception. we need to know what that line is.

-Rick

----------------------

http://www.ringdev.com

 

[cschristian78]

It doesn't give me that option. It goes right into the Error. I have gone into the exceptions dialog box and made sure all exceptions were using the parent but I am unable to see the option to break or continue.
Thanks,
Scott

 

[ThatRickGuy]

Okay, we'll I'll try a few stabs in the dark.

put a break point on this line:

Dim cmdCADOIZones As New SqlClient.SqlCommand(strCADOIZones, ProfDataCon)

check and see what ProfDataCon is. If it is nothing then you need to make sure it is either declared publicly, or pass it into the sub like the dataset.

-Rick

----------------------

http://www.ringdev.com

 

[cschristian78]

Rick,
Unfortunately, we are not having much luck. I am not even getting to the break. Let me describe what I have where and maybe we'll come across something. In the frmAnalysisOptions form, I declare the dataset as follows:

'//OPEN SETDESC TABLE
Dim dsSetDesc As New DataSet()
Dim dtSetDesc As New DataTable("SetDesc")
Dim drSetDesc As DataRow()

'Select Statement to Populate SetDesc Table into Dataset
Dim cmdSetDesc As New SqlClient.SqlCommand( _
"SELECT * FROM [ProfData].dbo.[SetDesc]", ProfDataCon)
Dim daSetDesc As New SqlClient.SqlDataAdapter(cmdSetDesc)

'fill dataset
daSetDesc.FillSchema(dsSetDesc, SchemaType.Source, "SetDesc")
daSetDesc.Fill(dsSetDesc, "SetDesc")

After I create the dataset, I then create my loop. The loop will run all of the queries for each of the sets in the SetDesc table. The dataset contains all fields in the table. When passing the dataset into the CADOIZones sub, how does it pull the [SET] Field for the query? All I need in the query is to have the "Where" clause = the [set] that is within the setdesc table. Specifically,

dsSetDesc.Tables("SetDesc").Rows(ndx).Item(1) = the set location.

Then I will just call the public sub CADOIZones within the frmAnalysisOptions form as you suggested:
CADOIZones(dsSetDesc).

I get the error after jumping to the sub. It highlights the sub declaration but does not allow me to break or continue.

What other tests can I try?

Thanks again,
Scott

 

[ThatRickGuy]

Scott,

This line:

CADOIZones(dsSetDesc)

sends a reference of the dsSetDesc variable to the CADOIZones sub.

The CADOIZones sub is defined like this:

sub CADOIZones(SetDesc as data.dataset)

that means that inside the CADOIZones sub, any time you use the variable SetDesc, it will actually be using the variable dsSetDesc that you passed it.

and again this IS WRONG

dsSetDesc.Tables("SetDesc").Rows(ndx).Item(1) = the set location.

the variable dsSetDesc does not exist in this sub. the variable SetDesc does. you should use:

SetDesc.Tables("SetDesc").Rows(ndx).Item(1) = the set location.

Also, make sure that the dtSetDesc object is not nothing on the CADOIZones(dsSetDesc) line. If it's not then put a break on every line of that sub. see which one is the last to fire befor it tosses you to the handler. That will show you which line is throwing the error.

-Rick

----------------------

http://www.ringdev.com

 

[cschristian78]

GOT IT!

It was the for "ds" at the beginning of the code below.

dsSetDesc.Tables("SetDesc").Rows(ndx).Item(1) = the set location.

Makes sense to remove the "ds" when passing the SetDesc as the argument. The code that works is as follows:

Sub:

Public Sub CADOIZones(ByVal SETDESC As Data.DataSet)

Dim strCADOIZones As String = "INSERT INTO [zz_Results Master] (Name3, Risks, TIV, [Set], Type, [Index], Peril) " & _

"SELECT Profdata.dbo.Cresta([Cresta])AS [CA DOI Zone], Count(LOC.LOCID) AS Risks, Sum(Val) AS TIV, [Set] AS [Set], 'CA DOI Zone' AS Type, [Index], Peril AS Peril " & _

"FROM (loc INNER JOIN qrySumLoccvg_Old ON loc.LOCID = qrySumLoccvg_Old.LOCID) LEFT JOIN [CA DOI Zones] ON loc.CRESTA = [CA DOI Zones].[CA DOI Zone], SetDesc " & _

"WHERE [SET] = '" & SETDESC.Tables("SETDESC").Rows(RDCount).Item(1) & "'" & _

"GROUP BY [CRESTA], [SET], [Index], [Peril] " & _

"ORDER BY [SET], [Index]"

Dim cmdCADOIZones As New SqlClient.SqlCommand(strCADOIZones, ProfDataCon)
cmdCADOIZones.ExecuteNonQuery()
cmdCADOIZones.CommandTimeout = 600

On frmAnalysisOptions form:

CADOIZones(dsSetDesc)

Thanks again for the time.

Scott