VB.NET with stored procedure. - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

VB.NET with stored procedure.

Author

Topic

chriskhan2000
Aged Yak Warrior

544 Posts

Posted - 2004-11-16 : 12:14:41

Here's the situation. I have created a program from VB.Net that will extract the data from a stored procedure to a datagrid.
The stored procedure calls data from 3 different databases, such as ACCT, HR, and FIN. Which has same schema, tables, and fields. I'm using a Union All for all 3 select statements from the stored procedure and then use another select statement to call from those 3 select statements. At the moment I'm able to pull all information from all 3 databases. Also note that the client can be in 1 or more database. If they do exist in more than one database, it will sum up amount the client spend.

What I want to do from the VB.Net program is allowed the user to select which database from a picklist. Let say they can pick one or more database to have the data. After selecting the database, the user will click a button to access the data. It will then go through each select statement and append to the next one if that database is in the picklist.

Here's my stored procedure:

code:--------------------------------------------------------------------------------
CREATE PROCEDURE SP_CLIENT
@STARTDATE VARCHAR(10),@ENDDATE VARCHAR(10)
AS

SELECT MAX(USER_ID) AS USER,
MIN(NAME),MIN(CITY), SUM(AMOUNT), MIN(STATE), MIN(ZIPCODE), MIN(DBNAME) AS DBNAME
FROM

(SELECT A.USER_ID,B.NAME, A.AMOUNT, B.CITY,B.STATE,B.ZIPCODE,DBNAME='ACCT'
FROM ACCT.DBO.CLIENT A, ACCT.DBO.LOCATION B
WHERE A.USER_ID=B.USER_ID AND CHECK_DATE BETWEEN @STARTDATE AND @ENDDATE

UNION ALL

(SELECT C.USER_ID,C.AMOUNT, D.NAME,D.CITY,D.STATE,D.ZIPCODE,DBNAME='HR'
FROM HR.DBO.CLIENT C, HR.DBO.LOCATION D
WHERE C.USER_ID=D.USER_ID AND CHECK_DATE BETWEEN @STARTDATE AND @ENDDATE

UNION ALL

(SELECT E.USER_ID,E.AMOUNT, F.NAME,F.CITY,F.STATE,F.ZIPCODE,DBNAME='FIN'
FROM FIN.DBO.CLIENT E, FIN.DBO.LOCATION F
WHERE E.USER_ID=F.USER_ID AND CHECK_DATE BETWEEN @STARTDATE AND @ENDDATE
) AS TABLE1

GROUP BY USER_ID
ORDER BY NAME
--------------------------------------------------------------------------------

Here's what I did in VB.NET:

code:--------------------------------------------------------------------------------
Private Sub btnExecute_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnExecute.Click

'Dim ds As New DataSet("DataSetName")
Dim con As New SqlConnection("data source=SP2003;persist security info=False;initial catalog=ACCT;UID=sa;PWD=Fall2003")

Dim ds As New DataTable("")
Dim cmd As SqlCommand = New SqlCommand("SP_CLIENT", con)
Dim adapter As New SqlClient.SqlDataAdapter
cmd.CommandType = CommandType.StoredProcedure

'Enter Parameter for Start Date
Dim myParam1 As SqlParameter = cmd.Parameters.Add("@StartDate", SqlDbType.VarChar, 10)
myParam1.Value = txtStartDate.Text

'Enter Parameter for End Date
Dim myParam2 As SqlParameter = cmd.Parameters.Add("@EndDate", SqlDbType.VarChar, 10)
myParam2.Value = txtEndDate.Text

'Open the connection
con.Open()

'Assign the select command
adapter.SelectCommand = cmd
adapter.SelectCommand.Connection = con

'Fill the datatable, use a Try catch to see what error i was getting.
Try
adapter.Fill(ds)
Catch ex As System.Exception
MessageBox.Show(ex.GetBaseException.Message)
End Try

'Fill the grid
DataGrid1.DataSource = ds

'close the connection
con.Close()

End Sub
--------------------------------------------------------------------------------

With this I was able to fill the datagrid with all data from all 3 databases, but I want to be able to pick which database to extract the data from a picklist.

I'm not sure where to go from here. I have got the picklist with static menu, such as ACCT, HR, and FIN. Do I need to do a Do Case or what from the VB application. Please advise.

nr
SQLTeam MVY

12543 Posts

Posted - 2004-11-16 : 13:22:06

Pass the values to the SP concatenated together and

CREATE PROCEDURE SP_CLIENT
@STARTDATE VARCHAR(10),@ENDDATE VARCHAR(10) ,
@type varchar(20) -- e.g. ''FINACCT'
AS

SELECT MAX(USER_ID) AS USER,
MIN(NAME),MIN(CITY), SUM(AMOUNT), MIN(STATE), MIN(ZIPCODE), MIN(DBNAME) AS DBNAME
FROM

(SELECT A.USER_ID,B.NAME, A.AMOUNT, B.CITY,B.STATE,B.ZIPCODE,DBNAME='ACCT'
FROM ACCT.DBO.CLIENT A, ACCT.DBO.LOCATION B
WHERE A.USER_ID=B.USER_ID AND CHECK_DATE BETWEEN @STARTDATE AND @ENDDATE
and @type like '%ACCT%'

UNION ALL

(SELECT C.USER_ID,C.AMOUNT, D.NAME,D.CITY,D.STATE,D.ZIPCODE,DBNAME='HR'
FROM HR.DBO.CLIENT C, HR.DBO.LOCATION D
WHERE C.USER_ID=D.USER_ID AND CHECK_DATE BETWEEN @STARTDATE AND @ENDDATE
and @type like '%HR%'

UNION ALL

(SELECT E.USER_ID,E.AMOUNT, F.NAME,F.CITY,F.STATE,F.ZIPCODE,DBNAME='FIN'
FROM FIN.DBO.CLIENT E, FIN.DBO.LOCATION F
WHERE E.USER_ID=F.USER_ID AND CHECK_DATE BETWEEN @STARTDATE AND @ENDDATE
and @type like '%FIN%'

) AS TABLE1

GROUP BY USER_ID
ORDER BY NAME

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

chriskhan2000
Aged Yak Warrior

544 Posts

Posted - 2004-11-18 : 12:11:37

Thank you NR. I think your query for the Stored Procedure is correct. However now I think that I'm having problem with my VB.Net program. After adding this parameter in my VB.Net program I get this error.

VB.Net Code added:

Dim myParam3 As SqlParameter = cmd.Parameters.Add("@Type", SqlDbType.VarChar, 20)
myParam2.Value = PickList.Items

Error when filling data table:

"Object must implement IConvertiable."

Not sure why I'm getting this error. Do I need to add IConvertiable or do some settings? Please help.

DustinMichaels
Constraint Violating Yak Guru

464 Posts

Posted - 2004-11-18 : 12:30:45

quote:
Originally posted by chriskhan2000

Thank you NR. I think your query for the Stored Procedure is correct. However now I think that I'm having problem with my VB.Net program. After adding this parameter in my VB.Net program I get this error.

VB.Net Code added:

Dim myParam3 As SqlParameter = cmd.Parameters.Add("@Type", SqlDbType.VarChar, 20)
myParam2.Value = PickList.Items

Error when filling data table:

"Object must implement IConvertiable."

Not sure why I'm getting this error. Do I need to add IConvertiable or do some settings? Please help.

The line myParam2.Value = PickList.Items is the problem.

The Items Property of the PickList refers to the collection of all the items in the pick list. You should probably use something like myParam2.Value = PickList.SelectedItem.Value

Dustin Michaels

chriskhan2000
Aged Yak Warrior

544 Posts

Posted - 2004-11-18 : 13:03:01

Dustin,

Thanks for replying. When I use what you suggested, I didn't get that iconvertiable error any more, but I got something else.

I'm getting a unhandled execption error.

'System.NullReferenceException'

I did debug it through, and my Picklist shows nothing as the value.
I did test it with a text box and it seems to be working fine when I use the text box as the parameter. So I know it's something wrong with the picklist that doesn't seem right. Here's the code for the picklist.

Dim myParam3 As SqlParameter = cmd.Parameters.Add("@Type", SqlDbType.VarChar, 20)
myParam3.Value = PickList.SelectedItem.Value

Seems like it's not seeing what's in that listbox. What I'm doing is have another listbox that contains all the parameter and the user can select 1 or several items from that listbox to the picklist box. Not sure what else I can do.

DustinMichaels
Constraint Violating Yak Guru

464 Posts

Posted - 2004-11-18 : 14:16:43

My guess is that is that nothing is selected from the pick list when this code is called. Maybe you can do this.

Dim myParam3 As SqlParameter = cmd.Parameters.Add("@Type", SqlDbType.VarChar, 20)

If PickList.SelectedItem Is Nothing Then
myParam3.Value = Convert.DbNull
Else
myParam3.Value = PickList.SelectedItem.Value

If that doesn't work you may want to see if the PickList object isn't null. Perhaps in your aspx file you don't have an element with an id of PickList. Also you may of forgotten to declare your PickList variable with protected scope. Try declaring your variable like this if all of the above doesn't work.

Protected WithEvents PIckList As DropDownList

Dustin Michaels

chriskhan2000
Aged Yak Warrior

544 Posts

Posted - 2004-11-18 : 14:53:43

I try that and it's still not working. I think I have another solution though. But if you can help me out it will be great.

I try it with a text box and it seems to be working great. Now what I can do is use the Picklist as a display for the user only, but instead have the text box hidden. So when the user adds a parameter from the menu listbox into the picklist box, it will also add that item to the text box. I was able to do this, but what I'm having problem with is when the user clicks the item from the picklist to have it remove. I can't seem to remove that item from the textbox.

Do you know the syntax to remove an item from a text box? So let say if the user clicks on item1 to be removed form picklist, it will also remove that from text box.

Here's the code for adding to the picklist and the textbox:

Dim check As Integer
'checks to see duplicates
check = lstPick.FindStringExact(lstDatabase.SelectedItem)
If check = -1 Then
txtList.Text = txtList.Text & lstMenu.SelectedItem
PickList.Items.Add(lstMenu.SelectedItem)
PickList.Update()
Else
MessageBox.Show(lstMenu.SelectedItem & " " & "is already selected.")
End If

This other code will remove it from the Picklist but I don't know how to also get the same item removed from the text box.

lstPick.Items.Remove(lstPick.SelectedItem)

Once again thanks for your help.

chriskhan2000
Aged Yak Warrior

544 Posts

Posted - 2004-11-18 : 16:34:28

Another thing is that when I set the listbox to text, i was able to use whatever item that I have selected in the Picklist. The problem is that I can only select 1 item at a time, and I don't want to have to select it from the picklist. I want to use all those items in the picklist as the parameter.

Dim myParam3 As SqlParameter = cmd.Parameters.Add("@Type", SqlDbType.VarChar, 20)
myParam3.Value = PickList.Text

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources