Can anyone help me here INNER JOIN error..

[edgarbenilde]

what's lacking with my code..?

please help me...


Dim com As New OleDbCommand("SELECT 'Sheet1'.'RsoNo', 'Sheet1'.'Customer', 'Sheet3'.'ItemCode', 'Sheet3'.'ItemDescription', " & _
"'Sheet3'.'Source','Sheet3'.'MOQ','Sheet3'.'QuantityRequired','Sheet3'.'Remarks','Sheet1'.'ZeroStock ', " & _
"'Sheet1'.'NewProduct','Sheet1'.'ProjectForecast','Sheet1'.'WithMotherPO','Sheet1'.'Others', " & _
"'Sheet1'.'RequestedBy', 'Sheet1'.'NotedBy', 'Sheet1'.'RecievedBy' " & _
"FROM ('Sheet1' 'Sheet1' " & _
"INNER JOIN 'Sheet3' 'Sheet3' ON " & _
"'Sheet1'.'RsoNo'='Sheet3'.'RsoNo' where 'rsono'='" & useprintrsono & "'")
com.CommandType = CommandType.Text '' command type
com.Connection = cn '' give connection to command
Dim adp As New OleDbDataAdapter '' declare adapter
adp.SelectCommand = com '' select command for adpapter to work on
Dim ds As New DataSet '' delcare dataset
adp.Fill(ds, "sheet3")


and what table should i put here since it is combination of two tables... " adp.Fill(ds, "sheet3") "

if long statement what it is look like..?

please help me...

[Max Peck]

Why are you enclosing all your column names in single-quotes?

SELECT 'Sheet' FROM table is going to return 'Sheet' - not the data column. You're working with SQL Server here, right?

-Max

[jmcilhinney]

Firstly, if you're going to use a join then I suggest you alias the table names to make your query shorter and easier to read. For instance, instead of this:

SELECT Table1.Column1, Table2.Column2 FROM Table1 INNER JOIN Table2 ON Table1.ID = Table2.ID

do this:

SELECT t1.Column1, t2.Column2 FROM Table1 t1 INNER JOIN Table2 t2 ON Table1.ID = Table2.ID

That will make quite a difference in a long query.

Secondly, as Max peck suggests, you don't enclose identifiers in single quotes. They can be enclosed in what I call the "reverse single quote", which is on the same key as the tilde (~) symbol. Maybe that's what you are using and it just doesn't display well on this forum. That said, the standard is to use square brackets, but even that is pointless if the identifier is not a reserved word. If you're hard-coding the SQL code then you may choose to use them for consistency, but it accomplishes nothing unless the identifier being wrapped is a reserved word.

Thirdly, I told you several threads ago to go to www.connectionstrings.com and read what changes you have to make to your SQL code in order to get data from Excel worksheets. You haven't made the required changes so you presumably have chosen to ignore that advice.

[edgarbenilde]

No Get The Data Sir..

But The Filling It To The Dataset It Did Not..

How Could It Do It..?

Please Me...

[jmcilhinney]

What have you changed? Have you followed the link I provided?

[edgarbenilde]

i mean i got the data. it is successfully load the data but the problem is that it is not filter based on my selected RSONO. because i wanted only data will display belong to RSONO selected...

i go to connectionstring i get the connection there on how to connect from my database...

my problem is only filling it to the dataset since it is a combination of two tables...

how could i fill the it to the dataset..?

[jmcilhinney]

What type of data does your RSONO column contain? Is it text or numbers?

[edgarbenilde]

hi, why it's giving me error "datatype mismacth in creteria expression"

I use here access 2003. and but originally the data is came from excel but i got the same that error. that's why i tried to transfer in access. but still the error.

here is my codes in access....

this is combination two tables i put it in query.

Private Sub GetRecords(ByVal myRSONO As String)

Dim cmd As New OleDbCommand
cmd.Connection = cn
cmd.CommandType = CommandType.Text
cmd.CommandText = "Select * from FinalCustItemRSO where RSONO = '" & myRSONO & "'"
da = New OleDbDataAdapter
da.SelectCommand = cmd
da.Fill(ds, "FinalCustItemRSO")
End Sub

[jmcilhinney]

This is the exact same issue that we were addressing in another thread. If you're trying to make it difficult for yourself and those who are trying to help you then you are succeeding.

As I asked you in that other thread over four hours before you started this thread, and you are yet to answer:

What type of data does your RSONO column contain? Is it text or numbers?

If the column contains text then you enclose a literal value in single quotes, as you've done. If it's numbers the you don't use single quotes. If you were to use parameters to insert the value instead of string concatenation this issue would never arise.

If you ask people for help and then they ask you for more information in order to provide that help, I suggest supplying the information in future. If not, you may find that those people lose interest in trying to help, and that can only be bad for you.

[edgarbenilde]

Hi, jmcilhinney..

RSONO is a NUMBER datatypes...

that is the primary key from table1 and foreign key to table2 the same datatype ("number")

[edgarbenilde]

i also use parameters structures but still the same i tried this one...

Private Sub GetRecords(ByVal myRSONO As String)

Dim cmd As New OleDbCommand
cmd.Connection = cn
cmd.CommandType = CommandType.Text
cmd.CommandText = "Select * from FinalCustItemRSO where RSONO = " & _txtRSONO.text & "'"
da = New OleDbDataAdapter
da.SelectCommand = cmd
da.Fill(ds, "FinalCustItemRSO")
End Sub

i so, hope you well give me sample..

[jmcilhinney]

What I will do is tell you how you should debug that so you can see what the mistake is that you've made so you can fix it yourself. You are creating a string and that string is wrong. Simply do this:

MessageBox.Show(cmd.CommandText)

and you should see the issue. If you still don't then consider what I posted previously:

If it's numbers the you don't use single quotes.

Did you remove the single quotes from around the value you were inserting?

[edgarbenilde]

yes i remove the single quotes...
still error.
says: Syntax error (missing operator) in query expression 'RSONO='

here is my code

Private Sub GetRecords()

Dim cmd As New OleDbCommand
cmd.Connection = cn
cmd.CommandType = CommandType.Text
cmd.CommandText = "Select * from FinalCustItemRSO where RSONO=" & Me.cboPrintRsoNo.Text & ""
da = New OleDbDataAdapter
da.SelectCommand = cmd
da.Fill(ds, "FinalCustItemRSO")
End Sub

[edgarbenilde]

here is two error..

if i remove the single quotes the error is : Syntax error (missing operator) in query expression 'RSONO='

while using doulble quotes together with single quotes.

i give me error says :"datatype mismacth in creteria expression"

[jmcilhinney]

Ok, you've removed both single quotes now, so that's a start. Now, did you take my other advice?

Also, you may be interested to know that this:

Dim cmd As New OleDbCommand
cmd.Connection = cn
cmd.CommandType = CommandType.Text
cmd.CommandText = "Select * from FinalCustItemRSO where RSONO=" & Me.cboPrintRsoNo.Text & ""
da = New OleDbDataAdapter
da.SelectCommand = cmd

does the same as this:

da = New OleDbDataAdapter("Select * from FinalCustItemRSO where RSONO=" & Me.cboPrintRsoNo.Text & "", cn)

Finally, what use does this have?

"Select * from FinalCustItemRSO where RSONO=" & Me.cboPrintRsoNo.Text & ""

[edgarbenilde]

it is a number data type...

table1 RSONO Primary key NUMBER DATATYPE
table2 RSONO Foriegn Key Number DATATYPE

[edgarbenilde]

By the way this "FinalCustRSO" is a query where the two tables combination of fields...

i have field in
table1
RSONO PK
Customer text
ZeroStock text
NewProduct text
ProjectForecast
WithPO
RequestedBy text
ReceivedBy text
NotedBY text

And Table2
DataID PK Autonumber
RSONO FK Number
Item text
Desc text
Source text
MOQ text
QR text
Remarks text

tha is my table field

[edgarbenilde]

so should it look like this..?

Private Sub GetRecords()

Dim cmd As New OleDbCommand
cmd.Connection = cn
cmd.CommandType = CommandType.Text
cmd.CommandText = "Select * from FinalCustItemRSO"
da = New OleDbDataAdapter("Select * from FinalCustItemRSO where RSONO=" & Me.cboPrintRsoNo.Text & "", cn)
da.Fill(ds, "FinalCustItemRSO")
End Sub

[jmcilhinney]

Look back at my last post. I said that those 6 lines could be replaced with that 1 line. Your original code is still fine, but the other is much more succinct. Now ask yourself, did you replace those six lines with the one, or did you add the one and leave most of the six there? In your last code snippet posted, what purpose are these lines serving?

Dim cmd As New OleDbCommand
cmd.Connection = cn
cmd.CommandType = CommandType.Text
cmd.CommandText = "Select * from FinalCustItemRSO"

Also, can you please start wrapping your code snippets in tags? With over 50 posts you've been around long enough to see them used AND to see the buttons on the editor window.

[edgarbenilde]

I' sorry sir i really wanted help.. but anyway,
sir, are you telling me to make this.

Dim da As OleDbDataAdapter
da = New OleDbDataAdapter("Select * from FinalCustItemRSO where RSONO=" & Me.cboPrintRsoNo.Text & "", cn)
da.Fill(ds, "FinalCustItemRSO")

there is error missing query operator...

[edgarbenilde]

its says query expression 'RSONO='

[jmcilhinney]

You really want help, yet you still seem not to have done as I suggested back in post #5. If you want to know what the issue is then you need to know EXACTLY what string you're passing to the query. I told you back in post #5 to display it in a message box and you've given no indication that you've done it and you certainly haven't shown us the result if you have. My guess is that the Text of your ComboBox is either empty or not a valid number. If you want to know LOOK AT THE STRING.

[edgarbenilde]

ok, i got it.... thank you so much jmcilhinney it display..

now, what my problem now is that, it doesn't filter based on the RSONO i select to to display the report...

based on this codes.....

Dim da As OleDbDataAdapter
da = New OleDbDataAdapter("Select * from FinalCustItemRSO where RSONO=" & _cboPrintRsoNo.Text & "", cn)
da.Fill(ds, "FinalCustItemRSO")

so thank you very much again i'm gonna rate you laterr..
but jmcilhinney help me how could i filter the data? it doesn't honor the code above... because in my crystal report it's display by default even without all this code since it is connected to the database... it will display the first RSONO together with the correponding data beloong too...

now how could i filter it...?

here is my code after loading the form report.






Private Sub GetRecords()

Dim da As OleDbDataAdapter
da = New OleDbDataAdapter("Select * from FinalCustItemRSO where RSONO=" & _cboPrintRsoNo.Text & "", cn)
da.Fill(ds, "FinalCustItemRSO")
End Sub

Private Sub PrintSat_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

If connprice.State = ConnectionState.Closed Then
connprice = New OleDbConnection
connprice.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=\\ph-suman\rso\Rsodb.mdb;User Id=admin;Password=;"
connprice.Open()
End If
If cn.State = ConnectionState.Closed Then
cn = New OleDbConnection
cn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=\\ph-suman\rso\Rsodb.mdb;User Id=admin;Password=;"
cn.Open()
End If

Dim newRep As New CrystalSat
OpenConnection()
GetRecords()
newRep.SetDataSource(ds)
newRep.Refresh()
CrystalReportViewer1.RefreshReport()

End Sub

[edgarbenilde]

If connprice.State = ConnectionState.Closed Then
connprice = New OleDbConnection
connprice.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=\\ph-suman\rso\Rsodb.mdb;User Id=admin;Password=;"
connprice.Open()
End If

this second connection is used to display the RSONO to the cboPrintRsoNo.Text by pressing the print button it will call the FormReport and the value of the combobox that i selected will be use to display the data base on the RSONO selected....

[edgarbenilde]

jmcilhinney help me here..... our code above will be useless if we can't filter the report because that is the major objectives in this program. my teacher told me to generate those reports. and it can be filter that that is the code... but filtering is not working yet...

[edgarbenilde]

Thank you very much jmcilhinney for you help i appreciated it. hope you will help me also in Crystal filter the data...

[RobDog888]

Triplicate threads merged. Please do not create multiple dup threads.