Comments by Yogi ,Pune (Top 36 by date)

I am facing problem how to write statement which give book having multiple author

Where Book_Master.BPRICE>1000
Group By Author_Master.BID
Having COUNT(Author_Master.BID)>1

Yes

Thanks

Still I am not clear, what are you trying to say. Can you show me example in my code?

When I write below mentioned code

Select * From Student_Master
Inner Join Book_Master
On Student_Master.SID=Book_Master.SID
Inner Join Author_Master
On Book_Master.BID=Author_Master.BID
Where Book_Master.BPRICE>1000
Group By Author_Master.BID
Having COUNT(Author_Master.BID)>1

SQL throws en error :
Msg 8120, Level 16, State 1, Line 1
Column 'Student_Master.SID' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

Tables:
Student: SID,SNMAE
Book: BID,BNAME,BPRICE,SID
Author: AID,ANAME,BID

As I mentioned above I want to write query which give list of student who take books who's price is >1000 & Book having more than one Author.

Yes I used Primary key & Foreign key concept.

Imports System
Imports System.Data
Imports System.Data.OleDb
Imports System.Collections

Dim con As New OleDbConnection
Private Sub Form1_Load(sender As System.Object, e As System.EventArgs) Handles MyBase.Load
con.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=D:\Data\HR.accdb;"
con.Open()
End Sub
Private Sub Button1_Click(sender As System.Object, e As System.EventArgs) Handles Button1.Click
Dim cmd As New OleDbCommand
Dim rd As OleDbDataReader
Dim str As String
cmd.Connection = con
cmd.CommandText = "Select Max(SRN) from Employee"

rd = cmd.ExecuteReader(CommandBehavior.CloseConnection)
If (rd.HasRows = False) Then

txtSRN.Text = 1
Else
rd.Read()
Str = Convert.ToInt32(rd(0))
str = str + 1
txtSRN.Text = str
End If
End Sub

Is their is any setting to get db_owner permission?

now system gives error to "If (rd(0) <> DBNull.Value) Then" statement
Operator '<>' is not defined for types 'Object' and 'System.DBNull'

Still system throws an error Object cannot be cast from DBNull to other types.
for "str = Convert.ToInt32(rd(0))" statement

then what should be the code for the same? Please give me.

Thanks

but what about first issue
1. If (rd.HasRows) Then
for this statement every time system return "TRUE" whereas there is no value in Employee Table
As per the logic when there is no value in table system should execute code return in "Else" part.

Thanks for suggestion.

Now I want to show Item Type also with Item description.
As per selection of cmbPartNo combo box Item Type & Item Description should be display in respective text boxes.

No . Part_No is varchar

Yes I did it in Com box selectedindexchanged event

"Where ever your reference to 'dt' is! This table is expecting certain column types. You need to make sure the same is passed."
Please explain your statement in detail.I am unable to understand "reference to 'dt'". As per my code & tables i mentioned above can you give me which is reference table in my code.

Thank you

Yes I understand there is data type mismatch error, but in which table i have to check table design "ItemMaster or Invoice"
My doubt is still same "dr = dt.NewRow()" through this statement in which table system create new row.

If you look at my code. I am creating new row & inserting value into it not in my actual tables/database,that's why i am confused how to resolved this error.
Are you want to see my database structure.

I understand there is problem of string & int value storage, but exactly where?
I am not storing a data into database. I stored it into datatable.
I checked datatable sequence it is all right.

Thanks

Thanks

As per your suggestion I made changes,remove single quotes & drop To string from cmbItemName.Selected value.Now system showing following error.
No mapping exists from object type System.Data.DataRowView to a known managed provider native type.
at the time of debugging the code I noticed one thing, after command.Parameters.AddWithValue("@selectedItemNo", cmbItemName.SelectedValue) statement
cursor go to "Try" statement & then "rd = command.ExecuteReader" statement.
which is correct but when cursor go to "rd = command.ExecuteReader" statement system also highlighting catch block.i.e something is wrong in between
command.Parameters.AddWithValue("@selectedItemNo", cmbItemName.SelectedValue)
rd = command.ExecuteReader this two statement

I made changes in my code as per your suggestion & debug the code I found that after command.Parameters.AddWithValue("@selectedItemNo", cmbItemName.SelectedValue.ToString()) this statement in my code cursor go to rd = command.ExecuteReader &
If rd.Read = True Then statement after that cursor directly go to Catch statement & shows same error again even "If rd.Read = True Then" at this statement code return True value i.e True=True.

Is there is any thing missing in my code.

As per your suggestion when I removed .To string()system throws following error after "rd = command.ExecuteReader" statement.
No mapping exists from object type System.Data.DataRowView to a known managed provider native type.

Yes. Item_no is a integrer

Thanks for suggestion.

but now I am getting another error : "FindControl" is not a member of system.Windows.Forms.DataGridView.Row.

Why you use lblAmount 2nd time after breaket closed.Is it label or variable.

Yes issue is still there.

Please suggest me a solution

on rd = command.ExecuteReader statement system directly go to catch statement & show message "The connection was not closed. The connection's current state is open.".
above mentioned issue still exist.

Please suggest solution for my question

Please provide code for the same.

Thanks