Alias + stored procedure syntax error

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 2005 Forums

Express Edition and Compact Edition (2005)

Alias + stored procedure syntax error

Author

Topic

angelica
Starting Member

6 Posts

Posted - 2008-06-21 : 14:03:21

Hi,

Im new to this forum and new also to SQL SERVER Edition Express.

Im trying to creat stored procedure. My main problem is that I need to display an alias consisting of 2 fields in a combobox (VB.Net) using also an innerjoin. Can anyone help me find my mistake please

My code is here and the error is :
-----------------------------------------------------------
Msg 156, Level 15, State 1, Procedure LA_suppName, Line 16
Incorrect syntax near the keyword 'INNER'.

-----------------------------------------------------------

@supplierFID int,
@supplierID int,
@LANo nvarchar(15) OUTPUT,
@suppName nvarchar(MAX) OUTPUT

AS

BEGIN
SET NOCOUNT ON;

-- Insert statements for procedure here

SELECT tb_LA.LANo, tb_supplier.suppName AS OrderSupplier
INNER JOIN tb_supplier ON tb_LA.supplierFID=tb_supplier.supplierID
Order by tb_LA.LANo

END

------------------------------------------------------

Eventually I would need to use tb_LA.LANo and make a query to populate the tb_LABooks in another combobox on selectvaluechanged. Is this possible please???

Many thanks

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-06-21 : 14:43:00

You need to specify the table name that matches with supplierID of tb_supplier table
So your select statement should be

SELECT tb_LA.LANo, tb_supplier.suppName AS OrderSupplier FROM
tb_LA INNER JOIN tb_supplier ON tb_LA.supplierFID=tb_supplier.supplierID
Order by tb_LA.LANo

Madhivanan

Failing to plan is Planning to fail

angelica
Starting Member

6 Posts

Posted - 2008-06-22 : 10:41:09

Hi,

Many thanks for the help with the stored proc.

Now Im trying to display my stored procedure. However Im getting no syntax error but nothing is showing in my combobox. What do I need to do ??

Private Sub FillLASP(ByVal cbo As ComboBox)
Dim query As String = "LA_Supp"
Dim da As New SqlDataAdapter(query, MyConn)
Dim dtLASupp As New DataTable

Try
Using cmd As New SqlCommand

MyConn.Open()
cmd.CommandType = CommandType.StoredProcedure
cmd.CommandText = "LA_Supp"
da.Fill(dtLASupp)
cboLASupp.DataSource = dtLASupp
cbo.DisplayMember = "OrderSupplier"
cbo.ValueMember = "LANo"
cmd.ExecuteNonQuery()
End Using
MyConn.Close()
Catch ex As Exception
MessageBox.Show(ex.Message)
End Try

End Sub

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-06-23 : 09:05:16

What is the error?

Madhivanan

Failing to plan is Planning to fail

angelica
Starting Member

6 Posts

Posted - 2008-06-23 : 14:46:44

the error requested paramters. Ive added all the parameters but it seems I am missing out something. Below is my latest code and now the error Im getting is failing to connect to the DB. Also I dont know if I need an adapter or not.

Private Sub FillLASP(ByVal cbo As ComboBox)
Dim query As String = "LA_Supp"
Dim cmd As New SqlCommand(query, MyConn)
Dim dtLASupp As New DataTable
Try

MyConn.Open()
cmd.CommandType = CommandType.StoredProcedure
cmd.CommandText = "LA_Supp"
cmd.Parameters.Add("@suppName", SqlDbType.NVarChar, CInt("MAX"))
cmd.Parameters.Add("@LANo", SqlDbType.NVarChar, 15)
cmd.Parameters.Add("LAID", SqlDbType.Int)
cmd.Parameters("@LANo").Direction = ParameterDirection.Output
cmd.Parameters("@LAID").Direction = ParameterDirection.Output
cmd.ExecuteNonQuery()

cboLASupp.DataSource = dtLASupp
cbo.DisplayMember = "OrderSupplier"
cbo.ValueMember = "LANo"
MyConn.Close()
Catch ex As Exception
MessageBox.Show(ex.Message)
End Try

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-06-23 : 16:15:03

You should post this in ASP.NET forum

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources