How can I edit ADODB to SQL query?

Question

1.00/5 (2 votes)

See more:

Hi, how can I edit this code for sql query? Thank you

What I have tried:

<pre>Dim re2 As New ADODB.Recordset

re1 = cnn.Execute("SELECT TabVyrCS.Mnozstvi From TabVyrCS LEFT OUTER JOIN TabKmenZbozi VKmenStavVC ON VKmenStavVC.ID=(SELECT TabStavSkladu.IDKmenZbozi FROM TabStavSkladu WHERE TabStavSkladu.ID=TabVyrCS.IDStavSkladu) LEFT OUTER JOIN TabVyrCS_EXT WITH(NOLOCK) ON TabVyrCS_EXT.ID=TabVyrCS.ID WHERE TabVyrCS.Nazev1 = '" & TextBox1.Text & "'")

Posted 5-Dec-18 7:46am

Fiínek Cahů

Updated 31-Jan-19 23:51pm

Add a Solution

Comments

ZurdoDev 5-Dec-18 13:48pm

How can you edit it? Just start typing. What's the problem?

Dave Kreskowiak 5-Dec-18 14:05pm

Uhhh....what?

You're going to have to go into a bit more detail about what you're trying to do.

[no name] 6-Dec-18 1:46am

I try to select TabVyrCS.Mnozstvi according to TabVyrCS.Nazev1 which is in textbox1. I try to edit it to this code but not function me.

Public Sub kontrola_kusu() Handles Button1.Click
        'VÝBĚR Z TABULKY STROJE SLOUPEC EVIDENČNÍ ČÍSLO STROJE
        Dim COMMAND As String = "SELECT * FROM TabVyrCS LEFT OUTER JOIN TabKmenZbozi VKmenStavVC ON VKmenStavVC.ID=(SELECT TabStavSkladu.IDKmenZbozi FROM TabStavSkladu WHERE TabStavSkladu.ID=TabVyrCS.IDStavSkladu) LEFT OUTER JOIN TabVyrCS_EXT WITH(NOLOCK) ON TabVyrCS_EXT.ID=TabVyrCS.ID = @TabVyrCS.Nazev1"
        Dim CMD As New SqlCommand(COMMAND, CONNECTION)

        'NÁZEV SLOUCE V TABULCE
        CMD.Parameters.AddWithValue("@TabVyrCS.Nazev1", TextBox1.Text)

        Dim DA As New SqlDataAdapter(CMD)
        Dim TABLE As New DataTable
        DA.Fill(TABLE)
        If TABLE.Rows.Count > 0 Then
            'SLOUPEC Z TABULKY 3
            TextBox2.Text = TABLE.Rows(0)(3).ToString()
            Label1.Text = "ZÁZNAM ÚSPĚŠNĚ VYHLEDÁN"
            Label1.BackColor = Color.LightSkyBlue
        Else
            MsgBox("ZÁZNAM NENALEZEN")
        End If
    End Sub

Dave Kreskowiak 6-Dec-18 14:51pm

First, "IT doesn't work" is NEVER a good problem description. What actually happens? What do you expect to happen? Any error messages?

Next, look at your SQL statement:

SELECT * FROM TabVyrCS
LEFT OUTER JOIN TabKmenZbozi VKmenStavVC 
ON VKmenStavVC.ID=(
    SELECT TabStavSkladu.IDKmenZbozi FROM TabStavSkladu
    WHERE TabStavSkladu.ID=TabVyrCS.IDStavSkladu
)
LEFT OUTER JOIN TabVyrCS_EXT WITH(NOLOCK)
ON TabVyrCS_EXT.ID=TabVyrCS.ID = @TabVyrCS.Nazev1

Look at the last line that starts with "ON". You've got TWO = in there. That's not right.

Richard Deeming 7-Dec-18 11:04am

CMD.Parameters.AddWithValue("@TabVyrCS.Nazev1", TextBox1.Text)

That's not a valid parameter name. Stick to letters, numbers, and underscores.
Database Identifiers | Microsoft Docs[^]

CMD.Parameters.AddWithValue("@TabVyrCS_Nazev1", TextBox1.Text)

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CHill60 · Answer 1 · 2019-01-31T23:52:00

I'm probably a bit late with this but here goes..

You have

SQL

LEFT OUTER JOIN TabKmenZbozi VKmenStavVC

That's a very long alias name for a table! Aliases are meant to make SQL queries easier to read and are usually quite short.

You have

VB

WHERE TabVyrCS.Nazev1 = '" & TextBox1.Text & "'"

I think you have already worked out that there is a risk of SQL Injection because your code in the comments uses parameters. However you are doing something like

VB

CMD.Parameters.AddWithValue("@TabVyrCS.Nazev1", TextBox1.Text)

which is a little confusing - it looks like you are referring to a value on the table. See the comment from @Richard-Deeming - Keep it simple and just use

VB

CMD.Parameters.AddWithValue("@Nazev1", TextBox1.Text)

Now look at that sub-query in your first ON clause -

SQL

(SELECT TabStavSkladu.IDKmenZbozi FROM TabStavSkladu WHERE TabStavSkladu.ID=TabVyrCS.IDStavSkladu)

That is a correlated sub-query because you are referring to an item "outside" the query itself - TabVyrCS.IDStavSkladu

It should be more efficient (and for me, easier to read) if you join to the sub-query instead e.g.

SQL

SELECT TabVyrCS.Mnozstvi 
From TabVyrCS 
INNER JOIN (SELECT IDKmenZbozi, ID FROM TabStavSkladu) SQ ON SQ.ID=TabVyrCS.IDStavSkladu
LEFT OUTER JOIN TabKmenZbozi.VKmenStavVC ON VKmenStavVC.ID=SQ.ID
LEFT OUTER JOIN TabVyrCS_EXT WITH(NOLOCK) ON TabVyrCS_EXT.ID=TabVyrCS.ID 
WHERE TabVyrCS.Nazev1 = @Nazev1

Sorry - I haven't been able to test this query at all so beware minor mistakes.