i need dynamic query with different criteria - Visual Basic Discussion Boards

Skinning is just custom drawing, you write a UI where all controls display a bitmap they can discover via a config file. Nothing to it.

Christian Graus - Microsoft MVP - C++

I'm having a problem formating a column in my listview to show the column as a currency column. I'm sure there is a simple answer to this, but I have a column called "Length" which is an integer and a column "StartingPrice" which is the currency....with the following code they both show up as currency fields, what am I doing wrong?

While datareader.Read
            test = New System.Windows.Forms.ListViewItem
            Dim columns As Integer
            test = lstPending.Items.Add(datareader(0).ToString)
            For columns = 1 To datareader.FieldCount - 1
                If datareader.Item("StartingPrice") Then
                    test.SubItems.Add(Format(datareader.Item(columns).ToString, "Currency"))
                Else
                    test.SubItems.Add(datareader.Item(columns).ToString)
                End If
            Next
            rows += 1
End While

Your test will always take the first path in the if statement since the column "StartingPrice" always exists.

you want

If datareader.GetName(column).Equals("StartingPrice") Then
  test.SubItems.Add(Format(datareader.Item(columns).ToString, "Currency"))
Else
  test.SubItems.Add(datareader.Item(columns).ToString)
End If

Absolute faith corrupts as absolutely as absolute power
Eric Hoffer
All that is necessary for the triumph of evil is that good men do nothing.
Edmund Burke

Great, that worked like a charm, much appreciated!

Hi, I want to create windows service in order to control the printer queue. And now I have read the following article http://www.developerfusion.co.uk/show/3441/[^] in order to create a simple windows service application. But one more thing that i still need is the way that I could control the printer queue from vb.net service application. Could any one provide me any information or resource in order to control the printer queue from vb.net service application for me? I need it in order to build a system to record the information about the printer utilization in my organization.

A thousand mile of journey, begin with the first step.

APO-CEDC
Save Children Norway-Cambodia Office

Use the System.Management (WMI) classes to get the queue informantion a simplistic example is here[^].

Absolute faith corrupts as absolutely as absolute power
Eric Hoffer
All that is necessary for the triumph of evil is that good men do nothing.
Edmund Burke

Thank you very much for your comment. But your code example is showing in C# version. I prefer to use it VB.Net, but now I still find the way to conver C# language to VB.Net. If it is possible then it would be fine for me.

Thank you for your help!!! Smile | :)

A thousand mile of journey, begin with the first step.

APO-CEDC
Save Children Norway-Cambodia Office

Hi, i have a form which has a 1 datagrid and 12 label & textbox(like NAME, SURNAME, NATIONALITY, JOB etc..)and some buttons .I want to get data from database with one or more criteria (User if fill 2 criteria like NAME and SURNAME my query must be where NAME=txtname.text AND SURNAME=txtsurname.text) OR (user maybe fill only one criteria like NAME my query must be where NAME=txtname.text ) or (user maybe want to fill 8 or 6 or all criteria) and if user dont fill any criteria and click get button, query must be (Select * from ANABILGI) thats all..

I try to write a code below but unfortunately it was'nt worked.Would you somebody help me please.

Thanks a lot

Private Sub withcriteria()

Dim param As String = "ADI= txtad.Text & (param1) & SOYADI=txtsoyad.text & (param2)& BABAADI=txtbabaad.text & (param3)& ANAADI=txtanaad.text & (param4)& DYERI=txtdogumyer.text & (param5)& DTARIHI=txtdogumtar.text & (param6)& UYRUK=txtuyruk.text & (param7)& MESLEK=txtmeslek.text & (param8)& DIN=txtdin.text & (param9)& MEDENIHAL=txtmedenihal.text & (param10)& CINSIYET=txtcinsiyet.text & (param11)& TAHSIL=txttahsil.text"

Dim param1, param2, param3, param4, param5, param6, param7, param8, param9, param10, param11 As String

If txtad.Text.Length And txtsoyad.Text.Length And txtbabaad.Text.Length And txtanaad.Text.Length And txtdogumyer.Text.Length And txtdogumtar.Text.Length And txtuyruk.Text.Length And txtmeslek.Text.Length And txtdin.Text.Length And txtmedenihal.Text.Length And txtcinsiyet.Text.Length And txttahsil.Text.Length < 0 Then

Dim da As New OleDbDataAdapter("Select * from ANABILGI", baglan)

Dim ds As New DataSet

baglan.Open()

da.Fill(ds, "tablo")

DataGrid1.DataSource = ds.Tables("tablo")

baglan.Close()

DataGrid1.Select(DataGrid1.CurrentRowIndex)

Else

If txtad.Text.Length < 0 Then

param1 = "OR"

Else

param1 = "AND"

End If

If txtsoyad.Text.Length < 0 Then

param2 = "OR"

Else

param2 = "AND"

End If

If txtbabaad.Text.Length < 0 Then

param3 = "OR"

Else

param3 = "AND"

End If

If txtanaad.Text.Length < 0 Then

param4 = "OR"

Else

param4 = "AND"

End If

If txtdogumyer.Text.Length < 0 Then

param5 = "OR"

Else

param5 = "AND"

End If

If txtdogumtar.Text.Length < 0 Then

param6 = "OR"

Else

param6 = "AND"

End If

If txtuyruk.Text.Length < 0 Then

param7 = "OR"

Else

param7 = "AND"

End If

If txtmeslek.Text.Length < 0 Then

param8 = "OR"

Else

param8 = "AND"

End If

If txtdin.Text.Length < 0 Then

param9 = "OR"

Else

param9 = "AND"

End If

If txtmedenihal.Text.Length < 0 Then

param10 = "OR"

Else

param10 = "AND"

End If

If txtcinsiyet.Text.Length < 0 Then

param11 = "OR"

Else

param11 = "AND"

End If

End If

Dim da As New OleDbDataAdapter("Select * from ANABILGI where (param)", baglan)

Dim ds As New DataSet

baglan.Open()

da.Fill(ds, "tablo")

DataGrid1.DataSource = ds.Tables("tablo")

baglan.Close()

DataGrid1.Select(DataGrid1.CurrentRowIndex)

MAGICIANMERLIN wrote:
Dim param As String = "ADI= txtad.Text & (param1) & SOYADI=txtsoyad.text & (param2)& BABAADI=txtbabaad.text & (param3)& ANAADI=txtanaad.text & (param4)& DYERI=txtdogumyer.text & (param5)& DTARIHI=txtdogumtar.text & (param6)& UYRUK=txtuyruk.text & (param7)& MESLEK=txtmeslek.text & (param8)& DIN=txtdin.text & (param9)& MEDENIHAL=txtmedenihal.text & (param10)& CINSIYET=txtcinsiyet.text & (param11)& TAHSIL=txttahsil.text"

What are you expecting this to do ?

MAGICIANMERLIN wrote:
Dim da As New OleDbDataAdapter("Select * from ANABILGI where (param)", baglan)

What are you expecting to happen here ?

The easiest way to do this is to write a stored proc ( because the best way to do all of this is with stored procs ), that takes all of these possible search values, and searches using LIKE, so that you end up with blah LIKE % when no search paramater was provided. If you must do it creating SQL on the fly like this, then just build the query as you go. Either way, you should be parsing any input strings for SQL injection attacks.

Christian Graus - Microsoft MVP - C++

Thank you but i am not using sql database i am using Access database and i will try to do it in Access Queries if possible..

Thanks A lot for your kindly answer

Access may not be a real database, but last time I checked, it used SQL.

Christian Graus - Microsoft MVP - C++

[edit]Hello!
I want to center my text in the RichTextBox Control but i don't want use "\tab"
Is a solution exist ?
thank you for your answer .. [/edit]

I'm sorry but i've found my answer
i give the url : The RTF for RichTextBox

SeLoRBIS

Please help me this problem

This is nowhere near enough information to help you.

You're going to have to supply a better explanation of what your having a problem with. A code sample might also help.

RageInTheMachine9532
"...a pungent, ghastly, stinky piece of cheese!" -- The Roaming Gnome

I am trying to use a DropDownList for displaying and changing data within a database. As soon as I select a new value from the list and click the change link then look at the DropDownList selected index value in the Update_Command function it has changed back to the original value. Is there a way to capture the new value before the bind occurs so I can update the database?

---Code-----------------------------------------------------

<%@ Import Namespace="System.Data.OleDb"%>
<%@ Import Namespace="System.Data" %>

<%@ Page
Language="vb"
ValidateRequest="false"
AutoEventWireup="false"
enableViewState="True" %>
<%@ Import Namespace="nsGlobal" %>

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML>
<HEAD>
<title>Web Page</title>
<link rel="stylesheet" type="text/css" href="http://www.access.org/Aspx.css" media="screen">

<script language="VB" runat=server>

Dim sql as String
Dim idx as Integer
Dim StrucEvents as New clsStrucEvents

Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
If Not Request.IsSecureConnection Then
Dim sUrl as string
sUrl = Request.Url.ToString.Replace("http://www.access.org/",ConfigurationSettings.AppSettings("SecureUrl"))
Response.Redirect(sUrl)
End If

If Not IsPostBack Then
Dim PageStats as New clStats
PageStats.Load_PageStats()
End If

Fill_DonerDl

End Sub

Function ListTitleBlock() as String
Dim html as String
html = "<h2>"
html = html & "Doner List for "
html = html & StrucEvents.Id
html = html & "</h2>"
Return html

End Function

Sub Cancel_Command(ByVal sender As Object, ByVal e As DataListCommandEventArgs)
dlEventDonor.EditItemIndex = -1
dlEventDonor.DataBind()
End Sub

Sub Edit_Command(ByVal sender As Object, ByVal e As DataListCommandEventArgs)
dlEventDonor.EditItemIndex = e.Item.ItemIndex
dlEventDonor.DataBind()
End Sub

Sub Update_Command(ByVal sender As Object, ByVal e As DataListCommandEventArgs)
Dim strlstEdY as String, strlstEdD as String
strlstEdY = CType(e.Item.FindControl("lstEdY"), DropDownList).SelectedItem.Value
strlstEdD = CType(e.Item.FindControl("lstEdD"), DropDownList).SelectedItem.Value
Response.Write(strlstEdY & " " & strlstEdD & "<hr>")
End Sub

Sub Delete_Command(ByVal sender As Object, ByVal e As DataListCommandEventArgs)
End Sub

Function Fill_DataSource(strFld as String) as DataSet

Dim ds as DataSet = New DataSet()
Dim tbl As DataTable = New DataTable("tbl")
Dim col As DataColumn = New DataColumn(strFld)
tbl.Columns.Add(col)
ds.Tables.Add(tbl)
Dim Row As DataRow

Dim idx as integer

Select Case strFld
Case "EdY"
For idx = -4 to 5
Row = tbl.NewRow()
Row(strFld) = CStr(Year(DateAdd("yyyy",idx,Now)))
tbl.Rows.Add(Row)
Next

Case "EdM"
For idx = 1 to 12
Row = tbl.NewRow()
Row(strFld) = CStr(idx)
tbl.Rows.Add(Row)
Next
Case "EdD"
For idx = 1 to 31
Row = tbl.NewRow()
Row(strFld) = CStr(idx)
tbl.Rows.Add(Row)
Next

End Select

Session(strFld) = ds
Return ds

End Function

Function Get_SelectedIndex(strFld as String,strValue as string) as Integer
Dim iLoop as Integer

Dim dt as DataTable = Session(strFld).Tables("tbl")
For iLoop = 0 to dt.Rows.Count - 1
If strValue = dt.Rows(iLoop)(strFld) then
Return iLoop
End If
Next iLoop

End Function

''''''''''''''''''''''''''''''''
'''Database Access Functions '''
'\/''''''''''''''''''''''''''\/'
Private Sub Fill_DonerDl()

Dim objConn As OleDbConnection
Dim objComd As OleDbDataAdapter
Dim sql As String
Dim objDs as DataSet = New DataSet()
StrucEvents = Session("StrucEvents")

'On Error Resume Next

objConn = New OleDbConnection(ConfigurationSettings.AppSettings("Conn"))

sql = "SELECT "
sql = sql & "TblEventDonor.* "
sql = sql & "FROM TblEventDonor "
sql = sql & "WHERE "
sql = sql & "EdId = '" & StrucEvents.Id & "' "
sql = sql & ";"
'Response.Write(sql & "<hr>")
objComd = New OleDbDataAdapter(sql, objConn)
objComd.Fill(objDs,"TblEventDonor")

dlEventDonor.Datasource=objDs.Tables("TblEventDonor").DefaultView

DataBind()

objComd.Dispose()
objDs.Dispose()
objConn.Close()

End Sub

</script>

</HEAD>
<body MS_POSITIONING="GridLayout">

<form id="Form1" method="post" runat="server">

<div id='PageBox' style='width:100%;Left:0px;'>

<asp:DataList
runat="server"
Id="dlEventDonor"
BorderColor="#FFEF00"
BorderWidth="1"
GridLines="Both"
CellPadding="3"
CellSpacing="0"
OnEditCommand="Edit_Command"
OnCancelCommand="Cancel_Command"
OnUpdateCommand="Update_Command"
OnDeleteCommand="Delete_Command"
>

<HeaderTemplate>
<%# ListTitleBlock() %>
</HeaderTemplate>
<ItemTemplate>
<div class="Section1a">
<%# Container.DataItem("EdY")%>
<%# Container.DataItem("EdM")%>
<%# Container.DataItem("EdD")%>
<%# Container.DataItem("EdDonor")%>

<asp:LinkButton
Text="Edit"
CommandName="Edit"
Runat="server"
ID="edit" />
<asp:LinkButton
Text="Delete"
CommandName="Delete"
Runat="server"
ID="delete" />

<br>
</div>
</ItemTemplate>

<EditItemTemplate>
Year
..Month
<br>...Day:
<asp:Dropdownlist
DataTextField="EdY"
DataSource='<%# Fill_DataSource("EdY") %>'
SelectedIndex='<%# Get_SelectedIndex("EdY", Container.DataItem("EdY")) %>'
runat=server
ID="lstEdY"/>
</asp:Dropdownlist>

<asp:Dropdownlist
DataValueField="EdM"
DataTextField="EdM"
DataSource='<%# Fill_DataSource("EdM") %>'
SelectedIndex='<%# Get_SelectedIndex("EdM", Container.DataItem("EdM")) %>'
runat=server
ID="lstEdM"/>
</asp:Dropdownlist>

<asp:Dropdownlist
DataValueField="EdD"
DataTextField="EdD"
DataSource='<%# Fill_DataSource("EdD") %>'
SelectedIndex='<%# Get_SelectedIndex("EdD", Container.DataItem("EdD")) %>'
runat=server
ID="lstEdD"/>
</asp:Dropdownlist>

<asp:TextBox
Text='<%# DataBinder.Eval(Container.DataItem, "EdDonor") %>'
runat=server
ID="txtEdDonor"/>

<br>
<asp:LinkButton
id="button2"
runat="server"
Text="Update"
CommandName="update" />
<asp:LinkButton
id="button3"
runat="server"
Text="Cancel"
CommandName="cancel" />
</EditItemTemplate>

</ASP:DataList>
</div>
</form>
</body>
</HTML>

Thanks for any help!!

Steve

If there's a FAQ, this should be on it. The answer is no. You should wrap your databind in a !IsPostback block, so it does not bind on postback, it will get the data source along with the selected index from viewstate.

Christian Graus - Microsoft MVP - C++

I have created a custom principal class based on IPrincipal and have exteded it for additional properties and methogs such as .UserName, .UserID to return data and IsInAnyRole to accept a list of possible roles for a user. Is there a way that I can use Declarative security with this for something like...

Public Shared Sub _<br />
    [PrincipalPermissionAttribute(SecurityAction.Demand, IsInAnyRole:="Role1,Role2,etc...")] _<br />
    PrivateInfo()<br />
    <br />
        'Print secret data.<br />
        Console.WriteLine(ControlChars.CrLf + "You have access to the private data!")<br />
    End Sub

The isInAnyRole is not part of the PrincipalPermissionAttribute class though. Is there an easy way to write or extend it so that I can use it with extended methods from my custom principal class?

Thanks in advanvce for any help...

Dear friends
I am new in vb.net and i try to make a some sample for practice. I am using that code below

Dim cmddelete As New OleDbCommand("Delete from ANABILGI where ADI=@param1, AND SOYADI= @param2, AND BABAADI=@param3", baglan)
cmddelete.Parameters.Add("@param1", txtad.Text)
cmddelete.Parameters.Add("@param2", txtsoyad.Text)
cmddelete.Parameters.Add("@param3", txtbabaad.Text)
baglan.Open()
cmddelete.ExecuteNonQuery()
baglan.Close()

but if i try to run it it was an error (Name 'ExecuteNonQuery' is not declared)
-------------------------------------------------------------------------------------------------
but if i run that code with update statement it is working that code is also below

Dim cmdupdate As New OleDbCommand("Update ANABILGI SET ADI=@adi, SOYADI=@soyadi, BABAADI=@babaadi, ANAADI=@anaadi WHERE ID=@ind", baglan)
cmdupdate.Parameters.Add("@adi", txtad.Text)
cmdupdate.Parameters.Add("@soyadi", txtsoyad.Text)
cmdupdate.Parameters.Add("@soyadi", txtbabaad.Text)
cmdupdate.Parameters.Add("@soyadi", txtanaad.Text)
cmdupdate.Parameters.Add("@ind", DataGrid1.CurrentRowIndex + 1)
baglan.Open()
cmdupdate.ExecuteNonQuery()
baglan.Close()
Getalldataindatagrid()
DataGrid1.Refresh()
End Sub
--------------------------------------------------------------------------------------------------
Thank you for your valuable helps

The DELETE query should not have any commas.

Thank you for your help;

I was corrected it by the same way which an other friend help to me about this.But also thank you for your kindly interest..

can anyone of u tell me how can i get the remote Computer system informtaion
like ( memeory info, machine info , ports info )

you have to install some component over there . to whom you will query and it will reply you back. like web service. or any component deployed on Transaction server or through remoting.

Hi,
How to display the images in VB.NETDatagrid

Thanks

Hello All,

I am not sure if I am repeating the same question asked in this forum before.

Can we dynamically generate the reports, say, like an user selecting the required fields across DB tables (through a front-end), specifying the criteria -us building the SQL query- and fetching the result from the DB.

Any help on this would be appreciated.

Thanks

Yes, of course we can. You need to provide the user with a list of possible search fields, build your SQL from them, check for injection attacks, and run the query. Which bit are you stuck on ?

Christian Graus - Microsoft MVP - C++