cmdolcet
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.
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
Can you please show us and example result of your stored procedure when execute it in SSMS?
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.
ASKER
Update
If I call the following:
"1N6ED0EA2LN727608" and how can I read the return value of the code above?
If I call the following:
Dim SQLCom As SqlCommand
SQLCom.Parameters.AddWithValue("dbo.usp_get_vehicle_data", "1N6ED0EA2LN727608")
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?
ASKER
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.
"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.
ASKER
Mate
That is what I have been given. The results are separated by comma delimited.
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
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
ASKER
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
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
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()
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
ASKER
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?
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?
ASKER
So taking the code below.
Any ideas?
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
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 errorAny ideas?
ASKER
Any help would be appreciated in the above request. What am I doing wrong to return selected rows in the stored procedure?
ASKER
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()
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.