Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Calling SQL Stored procedure from codebehind problems

Posted on 2012-04-07
5
Medium Priority
?
392 Views
Last Modified: 2012-05-12
hello, I am having a bit problems with this one and I dont know why, it should work.

The stored procedure works fine when I execute, but when I use codebehind to call it, thats where it does not work.

codebehind (vb .net)
Function ssRadioStatistik(ByVal ChannelName As String, ByVal AlbumTitle As String, ByVal TrackTitle As String, ByVal DisplayArtist As String, ByVal StartTime As String)
        Try
            If DisplayArtist.ToLower.Contains("wrd1") = True And DisplayArtist.ToLower.Contains("wrd2") = True Then

'tested with a msgbox to see if it comes to here and it does...

                Dim myConnectionString As String = ConfigurationManager.ConnectionStrings("ConnectionString").ConnectionString

                Response.Write(myConnectionString)

                Dim myConnection As SqlClient.SqlConnection = New SqlClient.SqlConnection(myConnectionString)
                myConnection.Open()
                Try
                    Dim command As SqlClient.SqlCommand = New SqlClient.SqlCommand("ErDetsStoredProcedure", myConnection)
                    command.CommandType = CommandType.StoredProcedure
                    command.Parameters.AddWithValue("@ChannelName", ChannelName)
                    command.Parameters.AddWithValue("@AlbumTitle", AlbumTitle)
                    command.Parameters.AddWithValue("@TrackTitle", TrackTitle)
                    command.Parameters.AddWithValue("@DisplayArtist", DisplayArtist)
                    command.Parameters.AddWithValue("@StartTime", StartTime)
                Finally
                    myConnection.Close()
                End Try

            End If
        Catch ex As Exception
        End Try
    End Function

Open in new window


Stored procedure code(works fine when executed)
ALTER PROCEDURE [Joachim_ErDets].[ErDetBieberStoredProcedure]
	@ChannelName nvarchar(50),
    @AlbumTitle nvarchar(50),
	@TrackTitle nvarchar(50),
	@DisplayArtist nvarchar(50),
	@StartTime datetime
AS

IF NOT EXISTS (SELECT * FROM ErDetsTable WHERE StartTime = @StartTime AND ChannelName = @ChannelName)
BEGIN
    INSERT INTO ErDetsTable (ChannelName, AlbumTitle, TrackTitle, DisplayArtist, StartTime, DatetimeStamp)
    VALUES (@ChannelName, @AlbumTitle, @TrackTitle, @DisplayArtist, @StartTime, GETDATE())
END

Open in new window


it gives no error when I try the use the codebehind to execute it, but it does not write anything in table. (tested the stored procedure by executing it, works great)

Thanks in advance, I have no clue why this does not work.
0
Comment
Question by:JoachimPetersen
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
5 Comments
 
LVL 36

Accepted Solution

by:
Miguel Oz earned 1000 total points
ID: 37818599
You need to pass StartTime as date time parameter.
Replace line 20:
command.Parameters.AddWithValue("@StartTime", StartTime)
with:
'Add the parameter.
Dim parameter As SqlParameter= command.Parameters.Add("@dt",  System.Data.SqlDbType.DateTime);
'Set the value.
parameter.Value = DateTime.Now;
' Execute the proc
command.ExecuteNonQuery();

Check:
http://stackoverflow.com/questions/425870/using-datetime-in-a-sqlparameter-for-stored-procedure-format-error
0
 
LVL 83

Expert Comment

by:CodeCruiser
ID: 37819699
>thats where it does not work.

Error?
0
 
LVL 14

Expert Comment

by:dejaanbu
ID: 37822414
you are missing

 command.ExecuteNonQuery() 

Open in new window


again if u face issues, add

.....
 Catch ex As Exception
response.write ex.toString()  'For Testing only
        End Try

Open in new window

0
 
LVL 36

Expert Comment

by:Miguel Oz
ID: 37825745
I already mentioned command.ExecuteNonQuery() as part of my previous post.
0
 
LVL 14

Expert Comment

by:dejaanbu
ID: 37826334
@mas_oz2003,

Sorry about that..... truly, i didn't read your comment properly..
0

Featured Post

Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I have a large data set and a SSIS package. How can I load this file in multi threading?
A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

721 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question