if checkbox1.checked = true then
'ur sql query which will show the result in datagridview
elseif checkbox2.checked = true then
'ur sql query
elseif checkbox1.checked = true and checkbox2.checked = true then
'ur sql query
else 'means if both are unchecked then display error or if u want to display all details of the table
'whatever u need
End if
M.Waqas Aslam commented: very nice one , Pooja , simple and clear.+4
prompt means display the form name which is showing the error.
Yes I have changed the string to boolean....
ok i will tell u in easy words....
I have one form say frmPatientRegister in which I need the connection string
module DBConnection
when the connection will fail it will contain false which will be passed to the module fail connection i.e. catch block...so I also want it to return the form name...
I have a connection module in which I have written the open and close database connection....
if database connection is an error it will return false else if success it will return true
the function of the modules will be called from the form code....
what I need is if the connection is an error the module should also get the form name which returned the error...is it possible???
check my below code....
DBConnection.module
Imports System.Data.SqlClient
Module DBConnection
Public Connection As SqlConnection = New SqlConnection()
Public Function Open_DB_Connection() As String
Try
Connection.ConnectionString = "Data Source = LocalHost\;Initial Catalog = Database;Integrated Security = True"
Connection.Open()
Return "Success"
Catch ex As Exception
MsgBox("Connection Error: " + ex.Message)
Return "Fail"
Exit Function
End Try
End Function
Public Function Close_DB_Connection() As String
Try
Connection.Close()
Return "Success"
Catch ex As Exception
MsgBox("Connection Error: " + ex.Message)
Return "Fail"
Exit Function
End Try
End Function
End Module
Call from the function is this way....
Dim Open_DB_Con As String
Dim Close_DB_Con As String
Open_DB_Con = Open_DB_Connection()
'other stuff required like queries....
Close_DB_Con = Close_DB_Connection()
now what I need is if the functions Open_DB_Connection() or Close_DB_Connection() returns error it should prompt the form name....
For deleting data with the help of datagridview and updating in access u can use
'Open connection
Dim i As Integer
Dim ID As String
i = DatagridView1.CurrentRow.Index
ID = DatagridView1.Item(0, i).Value 'whichever will be primarykey or unique data
Try
Dim MessageText As String
Dim myCommand As OleDbCommand
myCommand = New OleDbCommand("Delete from tablename where colname='" + ID + "'", Connection)
Dim DBReader As OleDbDataReader = myCommand.ExecuteReader
DBReader.Close()
Catch ex As Exception
MsgBox("Error in delete query: " + ex.Message)
End Try
'then show the updated datagridview again
Private Sub DataGridView1_CellClick(sender As Object, e As System.Windows.Forms.DataGridViewCellEventArgs) Handles DataGridView1.CellClick
Dim i As Integer
i = DataGridView1.CurrentRow.Index
Form1.TextBox1.Text = DataGridView1.Item(1, i).Value.ToString
Form1.TextBox2.Text = DataGridView1.Item(2, i).Value.ToString
End Sub
Whatever Jim has given it works...add few more lines of code
Private Sub Button7_Click(sender As System.Object, e As System.EventArgs) Handles Button7.Click 'checked on button click
Dim found As Boolean = InListView(ListView1, ComboBox1.Text)
If found = True Then
MsgBox("Exists")
Else
'MsgBox("No")
ListView1.Items.Add(ComboBox1.Text)
End If
End Sub
Private Function InListView(lvw, text) As Boolean
For Each item As ListViewItem In lvw.items
If item.Text = text Then Return True
Next
Return False
End Function
it shud be Listview1.Items.Add and then the reader....
Try
Dim myCommand As SqlCommand
myCommand = New SqlCommand("SELECT * FROM DoctorRegister ", Connection)
Dim reader As SqlDataReader = myCommand.ExecuteReader
While reader.Read
ListView1.Items.Add(reader.Item("FirstName")).Selected = True
End While
Catch ex As Exception
MsgBox(ex.Message)
End Try
selectedItem is not the property for listview.....
you can create a module and then call the module whenever u need a database connection....
this will help you to update the changes in your database only once if u need to change it anytime in future,,,,else u need to change in each and every line if u dont create a module....
Module
Imports System.Data.SqlClient
Module Module1
Public Connection As SqlConnection = New SqlConnection()
Public Function Open_DB_Connection() As String
Try
Connection.ConnectionString = "Data Source = LocalHost\;Initial Catalog = Databasename;Integrated Security = True"
Connection.Open()
Return "Success"
Catch ex As Exception
Return "Fail"
Exit Function
End Try
End Function
Public Function Close_DB_Connection() As String
Try
Connection.Close()
Return "Success"
Catch ex As Exception
Return "Fail"
Exit Function
End Try
End Function
End Module
when u need to call the database in your code...then call as
'Open connection
Dim Open_DB_Con As String
Dim Close_DB_Con As String
Open_DB_Con = Open_DB_Connection()
If Open_DB_Con = "Success" Then
Debug.Print("Database connection sucessfull")
ElseIf Open_DB_Con = "Fail" Then
MsgBox("Could not establish DataBase connection! ")
End If
Try
Dim myCommand As SqlCommand
myCommand = New SqlCommand("select * from Test", Connection)
Dim DBReader As SqlDataReader = myCommand.ExecuteReader
While DBReader.Read
Label1.Text = DBReader("colname")
End While
DBReader.Close()
Catch ex As Exception
MsgBox(ex.Message)
End Try
'closing connection
Close_DB_Con = Close_DB_Connection()
If Close_DB_Con = "Success" Then
Debug.Print("Database connection closed")
ElseIf Close_DB_Con = "Fail" Then
MsgBox("Could not close DataBase connection! ")
Debug.Print("Could not close DataBase connection! ")
End If
Mitja - when I copied ur code I get an error that 'Class 'System.Windows.Forms.DataGridViewRow' cannot be indexed because it has no default property.'
See what I have done in my code.....
Try
Dim myCommand As SqlCommand
myCommand = New SqlCommand("SELECT * FROM PurchaseOrder ", Connection)
Dim dt As New DataTable
dt.Load(myCommand.ExecuteReader)
If dt.Columns.Count <= 0 Then
MsgBox("No records found")
Else
With DataGridView1
.AutoGenerateColumns = True
.DataSource = dt
End With
End If
For Each row As DataGridViewRow In DataGridView1.Rows
If row("ConditionColumnName").Value.ToString() = "your condition" Then
row.[ReadOnly] = True
'you can even color the row differently
'like:
row.DefaultCellStyle.BackColor = Color.Red
Else
row.[ReadOnly] = False
'normal color
row.DefaultCellStyle.BackColor = Color.White
End If
Next
Catch ex As Exception
'Debug.Print("Exception: ")
Throw ex
End Try
Dim conn As New SqlConnection(ConnectionString)
Dim strSQL As String = "your sql query"
Dim da As New SqlDataAdapter(strSQL, conn)
Dim ds As New DataSet
da.Fill(ds, "Tablename")
With ListBox1
.DataSource = ds.Tables("Tablename")
.DisplayMember = "ColumnName" 'column name to be displayed in combo box
.SelectedIndex = 0
End With
the below thing I had done in my change password code...check if this helps u.... this code I had written in a button click event....
'Open connection
If txtUName.Text = "" Or txtOldPwd.Text = "" Or txtNewPwd.Text = "" Or txtCNPwd.Text = "" Then
MsgBox("All fields are mandatory")
Else
If txtNewPwd.Text <> txtCNPwd.Text Then
MsgBox("Password and Confirm Password should be same")
txtCNPwd.Text = ""
txtNewPwd.Text = ""
txtNewPwd.Focus()
Else
Try
Dim myCommand As SqlCommand
myCommand = New SqlCommand("Select Username, password from userdetails where Username='" + txtUName.Text + "' and password='" + txtOldPwd.Text + "'", Connection)
Dim reader As SqlDataReader = myCommand.ExecuteReader
Dim value As Boolean
value = reader.Read
reader.Close()
If value = True Then
Try
Dim myCommand2 As SqlCommand
myCommand2 = New SqlCommand("update userdetails set password='" + txtNewPwd.Text + "' where username='" + txtUName.Text + "'", Connection)
myCommand2.ExecuteNonQuery()
MsgBox("Password Changed Successfully")
Me.Close()
Catch ex As Exception
MsgBox("Error in select query: " + ex.Message)
End Try
Else
MsgBox("Old password is incorrect")
txtCNPwd.Text = ""
txtNewPwd.Text = ""
txtOldPwd.Text = ""
txtOldPwd.Focus()
End If
Catch ex As Exception
MsgBox("Error in select query: " + ex.Message)
End Try
End If
End If
'close connection
let me tell u all in brief....actually I did not go for one table since if anyone opens the database then it is difficult to get the outpatient and inpatient.....
So I made two different table for inpatient and outpatient....so that it is easy to find the data.....it may be stupidity but I had to do it .... :)
Create a crystalreport for ur project...... eg. Crystalreport.rpt
After the report is created create parameters as I told u....fromdate and todate and then avail the condition in the Record Selection Editor Formula as ur database_columnname in {fromdate} to todate
Go to ur Main Report Preview Window and see if u are getting ur output....the report will have two textboxes as from date and to date then enter the value and see if u get ur output....
this deals with ur crystal report...
Now ur win form.....
Add two datetimepickers for fromdate and todate.....one button to show the report and one crystal report viewer....
in ur button click event write the code which I had given u earlier and give the correct database and the correct crystal report path....
Run ur program and then see what output u get.....
to need to connect ur crystal report to crystal report viewer as u said in point 2....
Check and let me know if still u face any error....
write below code in ur button click event textbox2 is the field where u will enter number
MsgBox(ConvertNum(TextBox2.Text))
Then write the below function
Public Shared Function ConvertNum(ByVal Input As Long) As String 'Call this function passing the number you desire to be changed
Dim output As String = Nothing
If Input < 1000 Then
output = FindNumber(Input) 'if its less than 1000 then just look it up
Else
Dim nparts() As String 'used to break the number up into 3 digit parts
Dim n As String = Input 'string version of the number
Dim i As Long = Input.ToString.Length 'length of the string to help break it up
Do Until i - 3 <= 0
n = n.Insert(i - 3, ",") 'insert commas to use as splitters
i = i - 3 'this insures that we get the correct number of parts
Loop
nparts = n.Split(",") 'split the string into the array
i = Input.ToString.Length 'return i to initial value for reuse
Dim p As Integer = 0 'p for parts, used for finding correct suffix
For Each s As String In nparts
Dim x As Long = CLng(s) 'x is used to compare the part value to other values
p = p + 1
If p = nparts.Length Then 'if p = number of elements in the array then we need to do something different
If x <> 0 Then
If CLng(s) < 100 Then
output = output & " And " & …
'Open connection
Try
Dim myCommand As SqlCommand
myCommand = New SqlCommand("SELECT * FROM tablename where Day(BirthDate)='" + Date.Now.Day.ToString + "' and Month(Birthdate) = '" + Date.Now.Month.ToString + "'", Connection)
Dim reader As SqlDataReader = myCommand.ExecuteReader
Dim bool As Boolean
bool = reader.Read
reader.Close()
If bool = False Then
MsgBox("Invalid details")
Else
Dim dt As New DataTable
dt.Load(myCommand.ExecuteReader)
With DataGridView1
.AutoGenerateColumns = True
.DataSource = dt
End With
End If
reader.Close()
Catch ex As Exception
MsgBox("Error Connecting to Database: " & ex.Message)
End Try
close connection
