app.config

[shers]

Hi,

This is my first post in this site.

I'm working in VB 2005 Express and SQL 2005 Express. After changing my connection string to app.config, I cannot insert any records to the table. It does not show any errors. But when I check the tables, nothing is inserted. When the Form Loads, I have given code to display data from two tables and that works fine. That means there is nothing wrong in the connection string or the method I use. The problem is only when I insert rows. Please help.

Thanks

 

[ss7thirty]

Is it possible for you to post your code?

You should try to use a table adapter to insert records into your database, possibly with a store procedure if you could.

You are probably using a dataset or a data table which is an offlice copy of data. In order to actually make changes you will need a tableadapter and a connection. You can use adapter to fill datasets and to update and insert records into the table.

 

[shers]

I'm posting the code I did so far. Please correct me if I've done wrong coding.

app.config

<?xml version="1.0" encoding="utf-8" ?>
<configuration>
    <configSections>
    </configSections>
    <connectionStrings>
        <add name="ER.My.MySettings.ConnectString" connectionString="Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\dbER.mdf;Integrated Security=True;Connect Timeout=30;User Instance=True"
            providerName="System.Data.SqlClient" />
    </connectionStrings>
    <system.diagnostics>
        <sources>
            <!-- This section defines the logging configuration for My.Application.Log -->
            <source name="DefaultSource" switchName="DefaultSwitch">
                <listeners>
                    <add name="FileLog"/>
                    <!-- Uncomment the below section to write to the Application Event Log -->
                    <!--<add name="EventLog"/>-->
                </listeners>
            </source>
        </sources>
        <switches>
            <add name="DefaultSwitch" value="Information" />
        </switches>
        <sharedListeners>
            <add name="FileLog"
                 type="Microsoft.VisualBasic.Logging.FileLogTraceListener, Microsoft.VisualBasic, Version=8.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a, processorArchitecture=MSIL"
                 initializeData="FileLogWriter"/>
            <!-- Uncomment the below section and replace APPLICATION_NAME with the name of your application to write to the Application Event Log -->
            <!--<add name="EventLog" type="System.Diagnostics.EventLogTraceListener" initializeData="APPLICATION_NAME"/> -->
        </sharedListeners>
    </system.diagnostics>
</configuration>

Insert section of the code.

   Private Sub SaveToolStripMenuItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles SaveToolStripMenuItem.Click
        Me.LB.Focus()
        'If bolOpen = False Then
        Dim strCon = My.Settings.ConnectString
        Dim sqlConn As New SqlConnection(strCon)
        Dim DA As New SqlDataAdapter
        Dim DS As New DataSet

        DA.InsertCommand = New SqlCommand("NewPDetails", sqlConn)
        DA.InsertCommand.CommandType = CommandType.StoredProcedure

        Dim iRow As Integer

        Dim Param1 As SqlParameter = DA.InsertCommand.Parameters.Add("@DEPTID", SqlDbType.Int)
        Dim Param3 As SqlParameter = DA.InsertCommand.Parameters.Add("@RFS", SqlDbType.VarChar, 10)
        Dim Param4 As SqlParameter = DA.InsertCommand.Parameters.Add("@PNAME", SqlDbType.VarChar, 50)
        Dim Param5 As SqlParameter = DA.InsertCommand.Parameters.Add("@PHASEID", SqlDbType.Int)
        Dim Param7 As SqlParameter = DA.InsertCommand.Parameters.Add("@SDATE", SqlDbType.DateTime)
        Dim Param8 As SqlParameter = DA.InsertCommand.Parameters.Add("@EDATE", SqlDbType.DateTime)
        Dim Param9 As SqlParameter = DA.InsertCommand.Parameters.Add("@TOTHRS", SqlDbType.Int, 4)
        Dim Param10 As SqlParameter = DA.InsertCommand.Parameters.Add("@PHRS", SqlDbType.Int, 4)
        Dim Param11 As SqlParameter = DA.InsertCommand.Parameters.Add("@THRS", SqlDbType.Int, 4)
        Dim Param12 As SqlParameter = DA.InsertCommand.Parameters.Add("@COMP", SqlDbType.VarChar, 1)

        sqlConn.Open()

        For iRow = 0 To 5
            Param1.Value = (LB.SelectedIndex.ToString) + 1
            Param3.Value = txtRFS.Text
            Param4.Value = txtPName.Text
            Param5.Value = DGV(0, iRow).Value
            Param7.Value = DGV(2, iRow).Value
            Param8.Value = DGV(3, iRow).Value
            Param9.Value = DGV(4, iRow).Value
            Param10.Value = DGV(5, iRow).Value
            Param11.Value = DGV(6, iRow).Value
            If DGV(7, iRow).Value Is Nothing Then
                Param12.Value = "N"
            Else
                Param12.Value = DGV(7, iRow).Value
            End If
            DA.InsertCommand.ExecuteNonQuery()
        Next iRow
        sqlConn.Close()

        'End If
    End Sub

Thanks

 

[ss7thirty]

You are never specifying the command text:

exec @storedProcName

That is why your stored procedure i s never running. I prefer to return values from all my stored procedures to make sure that they run properly.

It may be something else but that is what I would try first.

 

[shers]

Could you please tell me how to return values from the stored procedure, when all parameter types are Input type?

Thanks

 

[CoachBarker]

Check the results with returned rows.

 

[shers]

I checked. It returns -1.

 

[cjard]

Hi,

This is my first post in this site.

I'm working in VB 2005 Express and SQL 2005 Express. After changing my connection string to app.config, I cannot insert any records to the table. It does not show any errors. But when I check the tables, nothing is inserted. When the Form Loads, I have given code to display data from two tables and that works fine. That means there is nothing wrong in the connection string or the method I use. The problem is only when I insert rows. Please help.

Thanks

Read the DNU link in my signature

 

[shers]

That worked. It was because the full path to the dbf was not mentioned in the app.config file.

I desperatly need help for some help in the code wher I'm stuck. I tried my maximum to find a solution, but couldn't. The problem is, how do I add data to a two tables in a database from a datagridview with 6 rows, all at once? I have created a stored procedure to do this, but how do I implement it in the code. I tried using dataset. Any experts out there, please help me!

Thanks

 

[cjard]

That worked. It was because the full path to the dbf was not mentioned in the app.config file.

No, i dont think you get it..

If you put the full path in the app config when you release the app it wont work on someone elses machine unless youre darn sure that you put the database at that location.

Its SUPPOSED to work on relative paths, and youre SUPPOSED to copy the database out as a resource of the app just like everything else, and the REASON why people complain that the database doesnt update, is because every time they press play, the clean room version of the db gets copied over the one they just changed!

Read the DNU article again, slower this time. If there's anthing you dont get, i'd appreciate the feedback so i can modify the article

I desperatly need help for some help in the code wher I'm stuck. I tried my maximum to find a solution, but couldn't. The problem is, how do I add data to a two tables in a database from a datagridview with 6 rows, all at once? I have created a stored procedure to do this, but how do I implement it in the code. I tried using dataset. Any experts out there, please help me!

Thanks

Read the DW2 link in my signature.. it covers all of that and more. If youre using a stored proc its very very simple.. just link the sproc to the table adapter, ensure the columns are mapped correctly (the wizard shows you this) and then call Update) on the tableadapter. THe TA is responsible for sending the right data to the sproc and then you can run the sql inside the sproc necessary.

READ THE DW2 LINK CAREFULLY

 

[shers]

No, i dont think you get it..

If you put the full path in the app config when you release the app it wont work on someone elses machine unless youre darn sure that you put the database at that location.

Yes, that's right. But I didn't have any other option, as I was severly searching for a solution and finally got it to work. BTW, this app is only for a single user. So hopefully it should work when I change the path in the app.config.

Its SUPPOSED to work on relative paths, and youre SUPPOSED to copy the database out as a resource of the app just like everything else, and the REASON why people complain that the database doesnt update, is because every time they press play, the clean room version of the db gets copied over the one they just changed!

Do you have a permenant solution for this? If so, please tell me what I can do on it.

Read the DNU article again, slower this time. If there's anthing you dont get, i'd appreciate the feedback so i can modify the article

I will read DNU again and will get back to you.

Read the DW2 link in my signature.. it covers all of that and more. If youre using a stored proc its very very simple.. just link the sproc to the table adapter, ensure the columns are mapped correctly (the wizard shows you this) and then call Update) on the tableadapter. THe TA is responsible for sending the right data to the sproc and then you can run the sql inside the sproc necessary.

READ THE DW2 LINK CAREFULLY

I don't think this is applicable to me, as I have hardcoded all that is explained.

Thanks