Link to home
Start Free TrialLog in
Avatar of cmdolcet
cmdolcetFlag for United States of America

asked on

How do I return all results from a Stored procedure inside an SQL Connection using .net

I have a VIN number "1N6ED0EA2LN727608 " then I would like to send to my database and run inside a stored procedure.

Inside the SQL stored procedure the data returned in the following (its all commas separated):
"VIN,body_number,eim_code,on_hold,no_build,product_line,Broadcast,int_color,big_eim,ext_color,pdsn,pp30
1N6ED0EA2LN,727608,CLYAL2220HEUNF-D--,0,0,T,  2 A B     F1F26CB RVE A   BT2 C89PKA  D KC  A     26  F42F34  F4M0RPL1TA  M N4  R1            3 S897  D   4BPUM A B M L BPT F CMC G M37 C   03    0 P   B   MN1UM     B3  RPYY  G     C82H  D 6 2 E H F7F3LE9A  2DX5F2    20N5DNS3PGK N6FY  FM  4FB7BJF8Q58FMU          F F   7GL   T0BTY   7 7 R M   D R BK    T   3   HE1BA C                       S P   H                 R   S A S   F2            V F 2 ,W,C6 2TAF,G41,9956,2020-12-14 14:37:00"


I have code inside the my vb.net app that makes the connection to the servers. But how can I run this code below with the scanned vin number as a string to return the stored procedure value the "dbo.usp_get_Vehicle_data" is the stored procedure. 

   Dim cnn As SqlConnection
        Dim connetionString As String

        connetionString = "Data Source=npcn-master-db;Initial Catalog=PMCS;User ID=dbs_user;Password=123"
        cnn = New SqlConnection(connetionString)
        Try
            cnn.Open()
            MsgBox("Connection Open ! ")
            Dim myCommand As New SqlCommand("dbo.usp_get_vehicle_data", cnn)
            myCommand.CommandType = CommandType.StoredProcedure
            Dim reader As SqlDataReader = myCommand.ExecuteReader()
            'Read in the first record and grab the first column
            Dim ValReturn As String
            If reader.Read() Then
                ValReturn = Convert.ToDouble(reader("eim_code"))
            End If
            cnn.Close()
        Catch ex As Exception
            MsgBox("Can not open connection ! ")
        End Try

Open in new window










Avatar of Máté Farkas
Máté Farkas
Flag of Hungary image

Can you please show us and example result of your stored procedure when execute it in SSMS?
Avatar of cmdolcet

ASKER

In my vb.net I am not getting anything to return back. So the code above is what I am using. I think I am not sending the VIN command to the stored procedure correctly and that is what is tripping me up.

Update
If I call the following:
Dim SQLCom As SqlCommand
SQLCom.Parameters.AddWithValue("dbo.usp_get_vehicle_data", "1N6ED0EA2LN727608")

Open in new window

That should call the Stored procedure dbo.usp_vehicle_data and then pass in the VIN which is
"1N6ED0EA2LN727608" and how can I read the return value of the code above?



Mate, the result is the following:
"VIN,body_number,eim_code,on_hold,no_build,product_line,Broadcast,int_color,big_eim,ext_color,pdsn,pp30
1N6ED0EA2LN,727608,CLYAL2220HEUNF-D--,0,0,T,  2 A B     F1F26CB RVE A   BT2 C89PKA  D KC  A     26  F42F34  F4M0RPL1TA  M N4  R1            3 S897  D   4BPUM A B M L BPT F CMC G M37 C   03    0 P   B   MN1UM     B3  RPYY  G     C82H  D 6 2 E H F7F3LE9A  2DX5F2    20N5DNS3PGK N6FY  FM  4FB7BJF8Q58FMU          F F   7GL   T0BTY   7 7 R M   D R BK    T   3   HE1BA C                       S P   H                 R   S A S   F2            V F 2 ,W,C6 2TAF,G41,9956,2020-12-14 14:37:00"

But this is strickly from the SQL Database side.

No, please create a screen shot about SSMS with the result.
Mate
That is what I have been given. The results are separated by comma delimited.


I am lost here...
why your sp returns such thing?
columns on one line
values on second line, with comma separated?

can you show what your sp is?
does it accepts any parameter? I don't see anything you pass to this sp
Sorry trying to figure all this out the string he returns from his sales stores procedure is that which I send in the original request

I am trying to setup the call to the procedure and I need to pass the procedure a value of 1N6ED0EA2LN727608 that will hopefully return the long value he is expecting 
try this code!
if youwant just one value/one row

cnn.Open()
Dim myCommand As New SqlCommand("dbo.usp_get_vehicle_data", cnn)
myCommand.CommandType = CommandType.StoredProcedure
myCommand.Parameters.Add(New SqlParameter("VIN", "1N6ED0EA2LN727608 "))
Dim ValReturn As String = myCommand.ExecuteScalar()

Open in new window


 
It looks like you're getting back a CSV of some kind, in which case you need to parse it, or load a datatable:

 
Dim ValReturn as double
Dim dt as NewDataTable
 if reader.Read() then 
    dt.Load(reader)
    ValReturn = Convert.ToDouble(dt.Rows(0)("eim_code"))
 end if 

Open in new window

HainKurt ,

I was able to figure it out. The stored procedure name was incorrectly spelled.
Question how can I return 3 columns (VIN,eim_code,PP30) columns of data instead of the lone VIN column?

If I remove the "Dim ValReturn As String = myCommand.ExecuteScalar()"
Since the ExecuteScalar only returns the first column and first value.

What should I use if I want to return the eim_code along with the VIN and the PP30 what can I use?

So taking the code below.

 Dim connetionString As String
        Dim SQLCom As SqlCommand

        connetionString = "Data Source=npcn-master-db;Initial Catalog=PMCS_Canton;User ID=dbs_user;Password=nissan123"
        cnn = New SqlConnection(connetionString)
        Try
            cnn.Open()
            MsgBox("Connection Open ! ")
            Dim myCommand As New SqlCommand("dbo.usp_get_vehicle_data_LMI", cnn)
            MsgBox("Command Setup ")
            myCommand.CommandType = CommandType.StoredProcedure
            MsgBox("Command Type Setup")
            myCommand.Parameters.Add(New SqlParameter("VIN", "1N6ED0EA2LN727608"))
            Dim ValReturn As String = myCommand.ExecuteScalar()
            Label1.Text = ValReturn
            MsgBox("Connection Open ! ")
            cnn.Close()


        Catch ex As Exception
            MsgBox("Can not open connection ! ")
        End Try

Open in new window

I get a return value for the VIN number. I know the Execute scalar will return a lone value on row 1 and column 1. I need to also return eim_code and the PP30 coumn values. How can I return that with the code above? Obviously I would take out the ExecuteScalar and I was thinking of replacing it with ExcuteReader. But that gave me an error

Any ideas?


Any help would be appreciated in the above request. What am I doing wrong to return selected rows in the stored procedure?
Ok solved it using some examples

   cnn = New SqlConnection(connetionString)
        Try
            Dim myCommand As New SqlCommand("dbo.usp_get_vehicle_data_LMI", cnn)
            ' MsgBox("Command Setup ")
            myCommand.CommandType = CommandType.StoredProcedure
            'MsgBox("Command Type Setup")

            'myCommand.Parameters.Add(New SqlParameter("VIN", "1N6ED0EA2LN727608"))
            ' Dim ValReturn As String = myCommand.ExecuteScalar
            'Label1.Text = ValReturn

            
            myCommand.Parameters.Add(New SqlParameter("VIN", "1N6ED0EA2LN727608"))

            cnn.Open()

            Using sdr As SqlDataReader = myCommand.ExecuteReader()
                sdr.Read()
                Label1.Text = sdr("VIN").ToString()
                Label2.Text = sdr("eim_code").ToString()
                Label3.Text = sdr("pp30").ToString()
            End Using



            'MsgBox("Data Returned ")


            cnn.Close()

Open in new window

ASKER CERTIFIED SOLUTION
Avatar of HainKurt
HainKurt
Flag of Canada image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial