sarah_siu
asked on
Executing SQL Function in VB.NET
Hi Everyone,
I currently have a webform as a front end to capture and insert data into an MS SQL database. I would like to execute a SQL function within my code but have no idea how to go about doing this. the function's name is 'GetChannel' and it decodes the PBR's ID accordingly to get the right channel. I've attached my current VB.NET code to insert data into the database. Would someone be able to help me to call the sql function from my current code?
Much thanks!
I currently have a webform as a front end to capture and insert data into an MS SQL database. I would like to execute a SQL function within my code but have no idea how to go about doing this. the function's name is 'GetChannel' and it decodes the PBR's ID accordingly to get the right channel. I've attached my current VB.NET code to insert data into the database. Would someone be able to help me to call the sql function from my current code?
'Open connection string and insert data into the ST database
Dim ConnectionString As String
Dim Connection As SqlConnection
Dim Adapter As New SqlDataAdapter
Dim sql As String
ConnectionString = "Data Source=00.0.000.00;Initial Catalog=ST;User ID=user;pwd=pwd;"
Connection = New SqlConnection(ConnectionString)
sql = "insert into table (PBR_ID, Others_Details, Contact_Flag, Comments) VALUES (@PBRID, @Others, '" & ddlcontact.SelectedValue & "', @Comments);"
Try
Connection.Open()
Adapter.InsertCommand = New SqlCommand(sql, Connection)
Adapter.InsertCommand.Parameters.AddWithValue("@PBRID", tbpbrid.Text)
Adapter.InsertCommand.Parameters.AddWithValue("@Others", tbneedsothers.Text)
Adapter.InsertCommand.Parameters.AddWithValue("@Comments", tbcomments.Text)
Adapter.InsertCommand.ExecuteNonQuery()
Response.Write("<span style='color: green'>Data Submitted</span>")
Catch ex As Exception
Response.Write("<span style='color: red>Data Not Submitted</span>")
End Try
Much thanks!
ASKER
Thanks Deepaklakkad - i tried what you suggested but it's not inserting. I am actually trying to insert the results from GetChannel Function into another field within the table. I've included the changes... Not sure what i'm doing wrong...
'Open connection string and insert data into the service_Tracking database, ABM Coupon Data table
Dim ConnectionString As String
Dim Connection As SqlConnection
Dim Adapter As New SqlDataAdapter
Dim sql As String
ConnectionString = "Data Source=00.0.000.00;Initial Catalog=ST;User ID=user;pwd=pwd;"
Connection = New SqlConnection(ConnectionString)
sql = "insert into Table(PBR_ID, Others_Details, Contact_Flag, Comments, Channel) VALUES (@PBRID, @Others, '" & ddlcontact.SelectedValue & "', @Comments, dbo.GetChannel(PBR_ID));"
Try
Connection.Open()
Adapter.InsertCommand = New SqlCommand(sql, Connection)
Adapter.InsertCommand.Parameters.AddWithValue("@PBRID", tbpbrid.Text)
Adapter.InsertCommand.Parameters.AddWithValue("@Others", tbneedsothers.Text)
Adapter.InsertCommand.Parameters.AddWithValue("@Comments", tbcomments.Text)
Adapter.InsertCommand.ExecuteNonQuery()
Response.Write("<span style='color: green'>Data Submitted</span>")
Catch ex As Exception
Response.Write("<span style='color: red>Data Not Submitted</span>")
End Try
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I tried the above code and it's not working...
The SQL function is actually stored in the MSSQL database as oppose to a code on my side - does that make a difference?
The SQL function is actually stored in the MSSQL database as oppose to a code on my side - does that make a difference?
what's name of your schema is it dbo? if not pls change it
- Deepak Lakkad
- Deepak Lakkad
Please provide the command type
ASKER
Thanks deepaklakkad - and sorry for the delayed acceptance.
try following code on line no 13
sql = "insert into table (PBR_ID, Others_Details, Contact_Flag, Comments) VALUES (dbo.GetChannel(ARGUMENTS), @Others, '" & ddlcontact.SelectedValue & "', @Comments);"
replace dbo with your original schema and
ARUGMENTS with your real arguments
- Deepak Lakkad